Excelling with Excel

1
Excelling with Excel

• LBP 5
• CSCI A110
• Craig Shue, AI

2
Importing Data

• Data -gt Import External Data -gt Import Data
• Browse to file to import. Double click it.
• On first screen, choose delimited. Click next.
• Checkmark comma, click next.
• Click finish.
• Choose ok.

3
Formatting Data

• Highlight heading cells. Click bold and click
  Fill Color (paint bucket) and choose yellow.
• Select column, right click, format cells. Choose
  special category and click SSN.
• Rename a worksheet by right clicking the sheets
  label and choosing rename.

4
Using Find and Replace

• Highlight the cells you want find and replace to
  work on
• Edit -gt Replace
• Fill in the boxes and click Replace All.
• Click OK and choose to close the find and replace
  window.

5
Labels, Data, and Equations

• Labels are regular text (left aligned)
• Data is represented as numbers (right aligned)
• Data can be made into a label using a prefix
• Equations begin with an

6
The AND equation

• AND( conditional , conditional )
• A conditional is something that has either a true
  or false value
• The conditional 5 lt 7 would be TRUE
• The conditional 4 gt 5 would be FALSE
• AND(6 gt1, 5lt4 ) would be FALSE
• AND(1lt2, 2lt3) would be TRUE

7
Referencing other cells

• You can reference the value of cells by simply
  including their column and row number
• Putting F2 would place whatever is in cell F2
  into the current cell

8
Putting it together

• You could do AND(F3lt3, C2gt5)This would return
  TRUE if the value in F3 is less than 3 AND the
  value in C2 is 5 or greater.

9
Applying our formula elsewhere

• Click on the cell containing the formula.
• Grab the black box in the lower left hand corner
  of that cell and pull over the range you want
  that formula applied.
• Cell references WILL BE UPDATED! For example, if
  you have (A2/4) in cell A3, when you apply that
  equation to cell B3, it will contain (B3/4)
  automatically!

10
Conditional Formatting

• Highlight range to apply conditional formatting
  on.
• Format -gt Conditional Formatting
• Fill out the form as you logically need to.
• ExampleCell value is not equal to purple

11
Hiding (and unhiding) Cells

• Select a cell in the row or column you wish to
  hide.
• Format -gt Row (or column) -gt Hide
• To unhide a row or column, you have to select
  cells that cross the hidden row or column, then
  go to Format-gt Row (or column) -gt Unhide
• Example if column N is hidden, I would have to
  highlight cells in columns M and O to unhide that
  column.

12
Percent Change

• A percent change from A to B can be characterized
  as (B-A)/A
• Example 100 to 150(150-100)/100 50/100 50
• You can use this formula in Excel, replacing A
  and B with cell names

13
Using other functions

• AVERAGE Returns the mean of a range
• MEDIAN Returns the median in a range
• STDEV Returns standard deviation
• MIN Returns the minimum value
• MAX Returns the maximum

14
Specifying a range

• Start with the first cell, use a , and specify
  the last cell
• Example A1A30 The range from A1 to A30
• Example (in a forumula) AVERAGE(A1A19)returns
  the average of all the numbers from A1 to A19