Excel 2007 Part 3
Instructor Outline
Excel 2007 Part 3 Instructor Outline covers …XXX


© 2012 Michael J. Walk

General Outline

1.  Data management

  1. Filtering data
  2. Finding data
  3. Replace
  4. Naming constants

2.  Data analysis

  1. Pivot tables
  2. Pivot charts
  3. Conditional formatting
  4. Data Analysis tool kit

3.  Data manipulation

  1. Advanced formulas (beyond math)
  2. If, Then
  3. Or, and
  4. If Error
  5. Concatenate
  6. Left, mid, right
  7. Upper, lower, proper
  8. Date functions
  9. Weekday
  10. Month
  11. Year
  12. Day
  13. Today
  14. Now
  15. Date
  16. Lookups (V and H)
  17. Data cleaning
  18. Remove duplicates
  19. Data validation
  20. Importing
  21. From text
  22. From Access
  23. Text to-columns

4.  Managing workbooks

  1. Password protection
  2. Locking sheets or ranges
  3. Document properties

5.  Macros

6.  Capstone

Naming Constant Values

Open file NAMING CONSTANTS

  1. In the Defined Names tab, under the Formulas category, click Define Name.
  2. Select Define Name from the list.
  3. In the Name field, type bonus.
  4. In the Refers to field, type .25.
  5. In cell D4 type =C4*bonus. Press Enter.
  6. Copy this formula from D5:D11.
  1. Add a column called Taxes
  2. Add the Defined Name tax and the value .10.
  3. Calculate the taxes for each employee using the tax Defined Name.
  1. Write a formula for the first employee that calculates the employee’s total salary (including bonus), using the Defined Name bonus, and adds this bonus back into the employee’s salary and then takes away the taxes, giving a total pay amount.
  2. Copy and paste this formula into the remaining cells in the column.

Working with Cell Ranges

1.  New blank workbook = play around

2.  Selecting cell ranges

  1. Click-and-drag method
  2. Shift-click method
  3. Shift-arrows method

3.  Naming cell ranges

4.  Selecting non-adjacent cells

Cell Data Formats

1.  All data in your excel worksheet is a number (at heart), regardless whether you are looking at a date, a %, money, etc. The ONLY EXCEPTION IS TEXT.

2.  Excel tries to guess, based on what you type in each cell, what kind of data you are putting in.

3.  [[OPEN A NEW WORKBOOK, TYPE 10 IN CELL B3]]

4.  Now, let’s change data type using the drop-down box on Home à Number. The default is general (guessed format). LOOK AT THE DROP-DOWN, YOU CAN SEE A PREVIEW!!!!

  1. Number: this is the basic format for numerical data. You can increase or decrease the number of decimal places by using the decimal increase/decrease buttons on Home à Number
  2. Many options available (including options for how to display negatives)
  3. Currency: uses a standard decimal place setting (2) and adds $ to front (can use different currency types)
  4. Accounting: uses a standard decimal place setting and adds $ to front… also aligns decimal places and currency symbols in the same column (try it… at $10,000.00 in B4)
  5. Short date: interprets the numerical value as a date in mm/dd/yyyy format. 1/1/1900 is 1
  6. Long date: interprets the numerical value as a date in DAYNAME, Monthname dd, yyyy format
  7. DATES HAVE MANY OPTIONS FOR DISPLAY
  8. [[pull open the full format menu – number tab… scroll through the different date formats ]]]
  9. Time: switching your numeric data to a time uses the fraction as the time (fraction of a day)
  10. [[WHAT WOULD THE DECIMAL HAVE TO BE TO HAVE A TIME OF 12:00 pm?]]
  11. Percentage: converts the value as typed to a %
  12. Fraction: displays the value as a fraction
  13. Scientific: YY x 10^??
  14. Text: turns whatever you have into text (even if it looks like a number)
  15. Others in the menu:
  16. Special: zip code, social security number, etc.

5.  You can control the data type selected by Excel by typing values in different formats try typing this:

  1. $10
  2. 1/10/2012
  3. 1:10 pm
  4. $10.00
  5. 1E4
  6. 10 ½
  7. 10%
  8. ’10 (converts number to text)

6.  EXERCISE 1 (CHANGING DATA FORMATS) -- need online file only

SORTING

1.  Many times we have a lot of data that we want in a particular order. Sorting allows us to do this.

2.  If we have multiple columns of data (e.g., a table), we can sort that entire table for 1 or more columns.

3.  Create a new empty workbook. In column A, type a column name of “Sort Test” and then type the letters a through I in the cells below—one letter per row.

4.  To sort your data, simply make sure you have clicked in one of the cells, then use the sort button on Home à Editing à Sort &Filter à Sort Z to A (this is descending).

  1. By default, the sort function sorts all data adjacent to the selected column and assumes your data has a header row.

5.  Let’s do this again, but instead, select Custom Sort. De-select the “my data has headers” and then sort the data. See what happens (Sort Test is included in the list of data to sort). UNDO

6.  ADD a column called Num Col, and put the numbers 1 – 9 in it (1 should be in same row as A)

7.  PUT cursor in Num Col, do a Custom Sort and make sure the “my data has headers” box is selected. Sort Num Col in descending order (largest to smallest). WHAT DO YOU SEE? – Excel has sorted the entire table of data based on the value of Num Col (1 is in the same row as A still). [[treats each row as a case or a record of data]]

8.  TO SORT ONLY THE SINGLE COLUMN : select the column you want to sort alone. When you select the sort, you will be asked whether you want to expand the selection or not.

  1. OR, put space between the column you want to sort and the rest of the data
  2. INSERT column between Num Col and Sort Test. Then, try to sort the Num Col. See what happens.

9.  EXERCISE 2 (sorting data)

  1. May 1998, Feb. 1999, June 2000
  2. Aug. 98, May 99, Sept. 99
  3. Jan 98, Jan 2000, Nov 98
  4. 456, 487, 765

Filling Series

1.  We often want to fill cells with a pattern of values. For example, simply counting up by 1 is useful. Or, we may want to create a list of weeks or months. Rather than typing them in individually, we can use Excel’s fill series feature. This is done in one of two ways:

2.  Using the fill handle

  1. OPEN A NEW WORKBOOK and type 1 into cell A1
  2. Place mouse over lower right corner of cell – get the cross-hair pointer – click and drag down
  3. What happened? Copied the cell values.
  4. Try dragging cell A1 fill handle to the right. – same thing
  5. **[[The fill handle fills the selected pattern. Since only one cell selected, there is no pattern.]]**
  6. Erase all the values except the first 1…. Add a 2 under the one. Select both cells, and use the fill handle and drag down.
  7. Add a 2 to the right of the 1…. Select both and fill to the right
  8. Erase all values. Change the 1 to a 10 and then add a 20 below the 10.
  9. Use the fill handle to drag down. What happens?
  10. Erase all values. Type today’s date. In the cell below, type the date a week from now. Use the fill handle. What happens?
  11. Erase all values. Type in Jan. 1, 2012. Cell below = Feb. 1, 2012. Use fill handle
  12. Erase all values. Type in “Item 1” Cell below = “Item 2”. Use fill handle

3.  Using Fill Series

  1. Sometimes you have a lot of filling to do… like, number the rows from 1 to 10,000. Dragging the fill handle can take a long time.
  2. Type first value in cell. Select cell. Home à Editing à fill à Series…
  3. Determine whether you want to fill down the column or across the row.
  4. Select an increment value
  5. Enter your ending value
  6. OK

4.  EXERCISE 3: CREATE A DATA WORKSHEET (follow instructions in handout)

The Auto Calculate Feature

This feature displays information, on the Status Bar, on a highlighted range. Right-Click on the Status Bar to activate the Auto Calculate features.

·  Sum

·  Average

·  Count

·  Numerical Count (ignores cells with text)

·  Minimum

·  Maximum

Start from a blank New worksheet.

Type a column of any five numbers in Column A.

Highlight your range of numbers.

View the Status Bar on your screen. Across the bar, on the right, you will see displayed the Auto Calculate information about your range.

Introduction to Formulas

1.  Reason formulae are useful: Allow us to make cell values that are interactive (not just static). These formulas can be based on the values of other cells or on mathematical calculations.

2.  Formulas are created in a cell by starting the cell with =

3.  Two kinds of formulae – independent (no external cell references) and dependent (use values in other cells)

4.  Create new blank workbook

5.  Basic math symbols are: *, /, +, -, ^, ()

6.  Excel also have built-in formulas for complex math (SUM, AVERAGE, STDEV, MAX, MIN, etc.)

7.  Basic independent formula: how many seconds in a week?

  1. How many seconds are in a week? =7*24*60*60

8.  Dependent

  1. Calculate seconds as a in X days
  2. 2 cells needed à one that holds the days, one that will calculate the seconds
  3. =cell*24*60*60 [[WALK THROUGH HOW TO REFERENCE AN EXTERNAL CELL IN A FORMULA]]

9.  Play with this by changing the number of days values

Order of Operations in Formulas

Excel calculates formulas in a specific sequence (a set of rules). This is called the Order of Operations. The Order of Operations works as algebra works, by calculating the elements of a formula in this order:

  1. Parentheses: Any computation within parentheses is performed first, inner parentheses to outer parentheses.
  2. Exponents: Any computation involving an exponent is performed next. If there is more than one exponent in a formula, Excel calculates them in order from left to right.
  3. Multiplication or Division: These operations are performed next, in order from left to right.
  4. Addition or Subtraction: These operations are performed last, in order from left to right.

*Note: Within parentheses, Excel also follows the Order of Operations, working from left to right.

Remember the Order of Operations with this sentence:

Please Excuse My Dear Aunt Sally.

Follow the example for a clearer picture:

=(20/4) * 100 + 15*2^2

Steps to follow in the order of operation:

1. 20/4 = 5 5*100 + 15 * 2^2

2. 2^2 = 4 5*100 + 15 * 4

3. 5*100 = 500 & 15*4 = 60 500 + 60

4. 500 + 60 = 560 560

Practice with the Order of Operations

=1+2*3+4/5-6

=1*2+3+4/5-6

=1*2*(3+4)/5-6

=1+2^2*4-6

=(1+2)^2*4/6

=(1+2*3)/5*(6-7)

=1+2*2-2*-1+2/2^2

=1+(2*2-(2*-1)+2)/2^2

=1+(2*2-(2*-1))+2/2^2

=1-(1256.78+45/908.22^(4-8.5))*0

=1/0

Dependent (reference-based formulas)

Now let’s try the same example with cell references. Use the file Formula Practice to get the average figures for Last Year Totals

=((B11 + C11 + D11 + E11)/4)/12

Steps to follow in the order of operation:

1. 7232 + 6434 + 9089 + 5824 = $28,579 (28,579/4)/12

2. 28,579/4 = 7,145 7,145/12

3. 7,145/12 = 595 595

1.  EXERCISE 4 (creating a method to calculate the total)

Introduction to Functions

Functions begin with an equals sign (=) and include the function name or an abbreviation of that name and the data range or arguments, enclosed in parentheses.

For example:

·  =SUM(A2:E12)

·  =AVERAGE(A2:E12)

·  =MIN(A2:E12)

Functions can be entered in at least two ways:

·  Type the entire function directly into the cell manually.

·  Type the function name and the left parenthesis, then use the mouse to select the range, separating multiple ranges with commas.

Open File FUNCTION PRACTICE.

  1. Select B8 and type =SUM(B2:B7); press Enter.

or

  1. Select C8 and type =SUM(
  2. With your mouse, select C2 through C7. As you highlight, Excel enters the range into your formula. Press Enter.

Using either of these two methods, write the formulas for AVERAGE, MIN, and MAX to complete the worksheet.

Combining Ranges in a Formula

  1. In cell A14, type Total Points.

2.  In cell B14, type =sum(

3.  Highlight B2:B7

  1. Enter a comma after this range.
  2. Highlight C2:C7. Enter a comma.
  3. Highlight D2:D7. Enter a comma.
  4. Highlight E2:E7.
  5. Press Enter.

Note: To see a list of all Excel Functions, go to the Functions Library tab under the Formulas category.

Using Formulas across Worksheets

You can have the answer to a formula appear in other cells or in other worksheets:

1.  Add a Sheet 2 to your workbook.

2.  Go to Sheet 2, Cell A1.

3.  Type =Sheet1!B10.

4.  Select Sheet 2, Cell A2.

5.  Type =Sheet1!B8+Sheet2A1.

You can also combine functions from different worksheets, apply a new function, and place the answer on another worksheet.

  1. Add a Sheet 3 to your workbook.
  2. Select Cell A1.
  3. Type =SUM(Sheet1!B2:B7)-AVERAGE(Sheet2!A1:A2).

Note: Sheet references follow this format: (Sheet Name; !) You may also highlight ranges instead of typing them.

AutoSum

  1. Delete the totals from the previous exercise.
  2. Place the active cell where you want an answer.
  3. Click the AutoSum button.
  4. Repeat the process for all of the remaining columns, using Average, Minimum, and Maximum.

Note: Notice that Excel guesses the entire column above your formula for Average, Minimum, and Maximum incorrectly. Use your mouse to select the correct range.

Copying and Pasting Formulas

  1. Delete the totals from the previous exercise.
  2. Calculate a Sum in B8 using the method of your choice. Press Enter.
  3. Copy this cell, highlight C8:E8, and Paste.

You may also use the Fill Series Feature to copy and paste formulas:

  1. Calculate an Average in B10 using the method of your choice. Press Enter.
  2. Select this cell, and using the Fill Series Handle, drag the cell into C10:E8.

Absolute and Relative Cell References

You may have noticed that when you copy a formula or function from one cell to another, Excel converts the cell reference to correspond to the row or column you have copied the formula or function to. For example, if you were copying the function =SUM(A1:A5) from cell A6 to cell B6 the formula would read =SUM(B1:B5) because it would adjust the cell references automatically. Therefore, any cell reference is a Relative Cell Reference.