Excel Advanced Course Materials



Excel Advanced Skills

Table Of Contents

Using Goal Seek 1

Precision and Multiple Solutions 2

Summarizing Loan Options Using Data Tables 3

One-Way Data Tables 3

Creating a One-Way Data Table – Example One 4

Creating a One-Way Data Table – Example Two 5

Creating a Single Variable Table with More than One Formula 6

Creating a Two-Way Data Table 7

Using Named Ranges 8

Rules for Range Names: 8

Naming a Range Using the Menu 8

Naming a Range Using the Name Box 8

Moving to a Named Range 9

Redefining a Named Range 9

Using Named Ranges in Formulas 10

Creating a List of Range Names 10

Deleting a Named Range 10

Working with Scenarios 11

Name Cells 11

Defining a Scenario 12

Viewing Your Scenarios 15

Editing/Deleting Scenarios 15

Adding a Text Box to Describe the Scenarios 15

Formatting a Text Box 16

Adding Comments 16

Guidelines for Comments or Text Boxes 16

Creating a Scenario Report 17

Adding a Scenarios Toolbar 18

Pivot Tables 19

Summarizing Data 19

Example: Sample Data List 19

Data Field Types 20

Example: Sample Pivot Tables 20

Creating a Pivot Table 21

Showing the Pivot Table Toolbar or Field List 23

Filtering Data in a Pivot Table 24

Filtering Page Fields 24

Filtering Category Fields 24

Modifying a Pivot Table 25

Examples 25

Copying a Pivot Table 26

Customizing a Category Field 26

Changing the Number Format or Function for Summarized Cells 27

Formatting a Pivot Table 28

Refreshing Pivot Table Data 28

Grouping Pivot Table Items 29

Renaming the New Field 30

Renaming the Grouped Items 30

Displaying Group Detail 30

Removing the Original Field 30

Ungrouping Items 30

Setting Pivot Table Options 31

Drilling Down 32

Pivot Charts 33

Using an Existing Pivot Table 33

Creating a Pivot Chart Using the Pivot Table and Pivot Chart Wizard 34

Creating Basic Macros 37

Recording a Macro 37

Absolute and Relative Macros 38

Running a Macro 39

From the Menu 39

From a Shortcut Key 39

Re-running a Macro 39

Viewing and Modifying a Macro 40

Macro Security 40

Assigning Shortcut Keys to Existing Macros 41

Creating a Macro Button 42

Formatting a Macro Button 43

Assigning a Text Box to Run a Macro 43

Deleting Macros 43

Personal Workbook Macros 43

Using Text Functions 44

Using the Trim Function to Eliminate Extra Spaces 44

Working with Values and Formulas 45

Using the Proper Function to Change Text to “Proper” Case 45

Merging Cells 46

Splitting a Column into Multiple Columns 47

Lookup and Reference Functions 48

Appendix 50

Data Validation 50

The Settings Tab 50

Creating a Drop-down List 51

The Input Message Tab 51

The Error Alert Tab 52

Clearing Validation 52

Customizing Toolbars 53

Changing the Location of Toolbars 53

Viewing or Hiding Toolbars 53

Creating a New Toolbar 53

Adding Tools to Toolbars 54

Moving Tools on a Toolbar 54

Tip: Create a Calculate Toolbar 54

Deleting a Toolbar You Have Created 55

Restoring Toolbars and Menus to their Original State 55

Practice File 55

Course Objectives

Topics in this course include:

• Goal Seek

• Data Tables

• Named Ranges

• Scenarios

• Pivot Tables and Pivot Charts

• Basic macros using the Microsoft macro builder

• Text and Lookup Functions

• Appendix

o Data validation

o Customized toolbars

Using Goal Seek

Goal seek is a method for finding the input value that a formula needs to return a given result. When using goal seek, Microsoft Excel varies the value in a cell that you specify until a formula that's dependent on that cell returns the result you want. Follow these steps to use Goal Seek.

1. Enter your data and create your formula to display a result.

1. Select the cell displaying the result.

2. From the menu, select Tools, Goal Seek. The Goal Seek dialog box displays.

3. In the Set cell: field, verify that the cell your want to change is entered.

4. In the To value: field, enter the desired goal. (If you used the payment function, remember that payments display as negative numbers.)

5. In the By changing cell: field, enter the cell that you want to adjust in order to accomplish your goal.

[pic]

6. Click OK. Excel displays the Goal Seek Status dialog box.

[pic]

7. Click OK. The new number displays in the cell you selected to be changed. The “goal” displays in the selected cell.

Precision and Multiple Solutions

Goal Seek performs up to 100 iterations (trial solutions) to find an answer that comes within 0.001 of your specified target value. If you need greater precision than this, adjust the settings in the Tools, Options dialog box from the Calculation tab.

[pic]

Summarizing Loan Options Using Data Tables

Data tables are particularly useful for summarizing various loan options. You can create one-way and two-way data tables.

One-Way Data Tables

Using a one-way data table you can show the results of a number of calculations for different values in a single input cell. The example below shows the various payments for a $200,000 30-year loan based upon several different interest rates. This example required a column input cell (the variable interest rates are displayed in a column).

[pic]

The example below shows various payments based up multiple interest rates for a $10,000 loan. This example required a row input cell (the variable interest rates are displayed in a row). A negative (-) sign was added in the PMT function to display payment results as positive numbers, rather than negative numbers.

[pic]

Note: By default, payments display as negative numbers. Generally, if you are the payee, you want a positive display. If you are the payer, you want a negative display. If you want the numbers to display positively, enter a negative symbol in your payment function as illustrated above.

Creating a One-Way Data Table – Example One

1. Create a worksheet with the inputs (interest rates) you want to test. These may be set up in rows or columns as the examples on the previous page illustrate.

2. Enter a loan amount in a cell outside the data table area.

3. Enter the payment function that uses the input variable. The input variable can be a blank cell or a cell representing one of the values you wish to test. The cell should be located outside the table area.

4. Select the data table range. In the example below, this is B2:C8. In a one-way table cell B2 (upper-left cell of table) should be an empty cell. The formula you want to test should be located within the table area.

[pic]

5. Select Data, Table (from the menu). The Table dialog box displays.

6. Enter the input cell. For this example, the interest rates are displayed in columns, so select a Column Input cell.

[pic]

7. Click OK. The formula results display.

[pic]

Creating a One-Way Data Table – Example Two

1. Create a worksheet with the inputs (interest rates) you want to test. These may be set up in rows or columns as the examples illustrate.

2. Enter a loan amount in a cell outside the data table area.

3. Enter the payment function that uses the input variable. The input variable can be a blank cell or a cell representing one of the values you wish to test.

4. Select the data table range. In a one-way table the upper-left cell of table should be an empty cell. The formula should be located within the table area.

[pic]

5. Select Data, Table (from the menu). The Table dialog box displays.

6. Enter the input cell. For this example, the interest rates are displayed in rows, so select a Row Input cell.

[pic]

7. Click OK to display the formula results.

[pic]

Creating a Single Variable Table with More than One Formula

You can also use a single variable table for more than one formula.

1. Create a worksheet with the inputs (interest rates) you want to test.

2. Enter the loan amounts in cells outside the data table area.

3. Enter the payment functions that use the input variable. The input variable can be a blank cell or a cell representing one of the values you wish to test. In a one-way table the upper-left cell of table should be an empty cell. The formula should be located within the table area.

4. Select the data table range.

[pic]

5. Select Data, Table (from the menu). The Table dialog box displays.

6. Enter the input cell. For this example, the interest rates are displayed in columns, so select a Column Input cell.

[pic]

7. Click OK. The formula results display.

[pic]

Creating a Two-Way Data Table

You can also test multiple variables and values with a single formula. See the example below. A two-way table is set up differently. In a two-way table the upper-left cell of table should contain the formula you want to test.

[pic]

1. Create a worksheet with the inputs (interest rates) you want to test.

2. Enter a loan amount in a cell outside the data table area.

3. Enter the payment function that uses the input variable. In this example, the payment function is in B4. B7 contains the formula to be tested. B7=B4; B4 contains the payment function.

4. Select the data table range.

[pic]

5. Select Data, Table (from the menu). The Table dialog box displays.

Enter the input cells. In this example the percentages are displayed in rows so $B$2 must be the row input cell. The amounts are displayed in columns so $B$1 must be the column input cell.

[pic]

6. Click OK to display the formula results.

Using Named Ranges

It is useful to name ranges to quickly go to a certain part of a workbook, select ranges to specify in menu commands (such as printing), and use in formulas.

[pic]

Rules for Range Names:

• Names can be up to 253 characters long.

• Names must begin with a letter, a backslash (\), or an underscore (_).

• Numbers can be used in range names, but not as the first character.

• The only symbols allowed are period (.), backslash (\), and underscore (_).

• Blank spaces are not allowed. Use periods or, better yet, underscores to create separation in a range name. (E.g.: Sales_Pct or Sales.Pct)

• Names cannot be cell references.

• Names can be single letters except for R and C. (These are used in a particular type of reference. For example, R1C1 is another way of referring to A1.)

Naming a Range Using the Menu

Follow these steps to name a range.

1. Select the range of cells you want to name.

2. Select Insert, Name, Define. The Define Name dialog box displays.

3. Type the appropriate name. Follow the rules listed above for name requirements.

4. Click Add.

5. Click OK.

Naming a Range Using the Name Box

You can also name a range in the Name Box on the left side of the Formula bar.

1. Select the range of cells you want to name.

2. Click in the Name Box. The cell name displays highlighted.

3. Type the appropriate name range, then press Enter.

Moving to a Named Range

You can easily move to a named range from anywhere within the open workbook.

1. Click the down pointing arrow [pic] at the right side of the Name Box.

2. Select the name of the range you want to move to.

Redefining a Named Range

You can change the range of cells to which the named range refers.

1. Select Insert, Name, Define. The Define Name dialog box displays.

[pic]

2. Select the name of the range you want to change.

3. In the Refers to: field, click the Collapse button [pic] to collapse the dialog box.

4. Select the appropriate cell range.

5. Click the Collapse button [pic] again to return to the dialog box.

6. Click OK.

Using Named Ranges in Formulas

Consider all named ranges to be absolute references.

1. Enter the formula up to the point where the range name is required.

or

Move to the part of the dialog box where the range is to be specified.

2. Press F3 (or select Insert, Name, Paste). The Paste Name dialog box displays.

[pic]

3. Select the appropriate range.

4. Click OK.

Creating a List of Range Names

You can display a list of range names if desired. This can be useful when sharing a workbook with others to advise them of the named ranges in the workbook.

1. Move to a new worksheet or an empty range in an existing sheet.

2. Press F3 (or select Insert, Name, Paste).

3. Click Paste List.

Deleting a Named Range

1. Select Insert, Name, Define. The Define Name dialog box displays.

2. Select the name of the range you want to delete.

3. Click Delete.

4. Click OK.

Working with Scenarios

Goal seek works well for modifying one variable. If you want to work with multiple variables, you can use the Scenario Manager. A scenario is a set of values that Microsoft Excel saves and can substitute automatically in your worksheet. You can use scenarios to forecast the outcome of a worksheet model, involving up to 32 variables. You can create and save different groups of values on a worksheet and then switch to any of these new scenarios to view different results.

In the worksheet model below, cells D2:D5 and E8:E14 display recent historic averages for a retail establishment. Column C displays names applied for the relevant cells in columns D and E.

[pic]

For example, using the above data, we can create several forecast scenarios to predict possible increases and decreases in revenue and/or costs.

Name Cells

It is usually a good idea to name the cells you want to use as variables, as well as cells containing formulas that depend on your variables. This helps to make the scenario reports easier to interpret.

Defining a Scenario

Follow these steps to define a scenario. It is usually a good idea to create a “base” or “starting point” scenario first, so that you can always return to that, if necessary.

1. Create your business model of data and formulas.

8. From the menu, select Tools, Scenarios. The Scenario Manager dialog box displays.

[pic]

9. Click the Add button. The Add Scenario dialog box displays.

[pic]

10. In the Scenario name: field, type a name for your scenario.

Tip: It is a good idea to create a “base” scenario before making any changes so that you have saved your “starting point.”

11. In the Changing cells: field, indicate the cells you want to vary. (Either type in the cell references, or click the Collapse Dialog button [pic] to select the desired calls. Do not select cells containing formulas.

Tip: Press and hold Ctrl to select multiple cells.

Note: If desired, you can type the named ranges you created as changing cells.

12. Click OK to create the first scenario. The Scenarios Values dialog box displays. If you have named the cells, as suggested above, the names display to the left as shown below. Otherwise, cell references display in the dialog box. The values are the values currently entered in the worksheet. You can edit these fields, either with a specific number, or with a formula. If creating a “Base” scenario, do not edit any of the fields at this time.

[pic]

13. Click OK to return to the Scenario Manager.

14. If you want to add another scenario, click Add. The Add Scenario dialog box again displays.

15. Enter the name for the scenario and the cells you plan to vary. (These may be the same cells as your previous scenario.)

[pic]

16. Click OK. The Scenarios Values dialog box again displays.

17. Enter the values to adjust your scenario. You can enter a specific value or a formula. For instance, perhaps you want to create a scenario that shows an increase in revenue of 10%. In the beginning of the field, you would enter =1.1* before the text already displayed in the cell. See the example below, which displays various formulas.

[pic]

18. When finished, click OK. If your scenario contains a formula, the following screen displays

[pic]

19. Click OK to return to the Scenario Manager.

[pic]

20. To add more scenarios, click Add, or click Close to close the Scenario Manager dialog box.

Viewing Your Scenarios

Scenarios are saved with the file. You can view your scenarios at any time.

1. From the menu, select Tools, Scenarios. The Scenario Manager dialog box displays.

1. Select the scenario you want to view and click Show.

2. Click Close to close the Scenario Manager dialog box.

Editing/Deleting Scenarios

You can view, edit and delete scenarios. Simply select Tools, Scenarios to view the Scenario Manager dialog box. Click the appropriate button to edit or delete a scenario.

Note: When you edit a scenario, you do not have the option to change a formula or value, you can only edit the scenario name or the changing cells. For other changes, create a new scenario, and delete the old one.

If you modify a scenario, Excel adds your name in the Comment area of the Scenario Manager dialog box.

Adding a Text Box to Describe the Scenarios

You may wish to add a text box to explain or describe what each scenario does. For instance, you may display text indicating that the “Best Case” scenario takes into account historical performance, national economic forecasts, and then list the cells you modified in creating the scenario. Or you may simply want to indicate that the file contains several scenarios and how they may be viewed. To add a text box, you should add the Drawing toolbar. (Click View, Toolbars, Drawing.) Follow these steps to add a text box.

1. Click the Text Box button [pic] from the Drawing toolbar.

21. Position your mouse on the worksheet in the desired location and click and drag to draw the text box.

22. Enter the desired description in the text box.

23. Click outside of the text box to deselect it.

Formatting a Text Box

You may want to format the text box to display a different font or color, etc. Follow these steps to format a text box.

o Right-click on the text box border and select Format Text Box. A Format Text Box dialog box displays.

o Make the desired changes and click OK.

Tip: To move a text box, click and drag the border to the desired location. To resize, click and drag the selection handles of the text box.

Adding Comments

As an alternative, you can use the Comments feature of Excel to describe your scenarios or make other comments about the worksheet.

1. Right-click on the cell to which you want to add a comment, then select Insert, Comment.

2. Enter the desired text.

3. Click outside the comment box.

• To view the comment, rest your mouse button on the cell containing the comment indicator.

• To display the comment, right-click on the cell containing the comment indicator and select Show Comment.

• To hide a comment, right-click on the cell containing the comment indicator and select Hide Comment.

• To edit a comment, right-click on the cell containing the comment indicator and select Edit Comment.

• To delete a comment, right-click on the cell containing the comment indicator and select Delete Comment.

Guidelines for Comments or Text Boxes

Use a text box if your information must be read in order for users to understand the features of the scenarios. A text box is clearly visible to display descriptive information.

Use a comment when you want to have the option of hiding or displaying the descriptive information.

Creating a Scenario Report

You can view your existing scenarios, by using the Tools, Scenarios menu or create a scenario toolbar as described on page 18. If you want a printed report, you can easily create a summary report to display a consolidated view of the results of all scenarios on the worksheet. Follow these steps to create a scenario summary report.

1. Activate the worksheet containing the scenarios you want to summarize.

2. Select Tools, Scenarios. The Scenario Manager dialog box displays.

3. Click [pic]. The Scenario Summary dialog box displays.

[pic]

4. In the Report type area, select Scenario summary.

5. In the Result cells: field, enter the cells you want to display in the report.

6. Click OK. A report is created on a new worksheet that is inserted to the left of the active worksheet.

[pic]

7. Click the Expand/Collapse (+) (-)[pic] buttons on the left and top to display the report differently. If you entered names in your scenario, the names display in the Changing Cells column. If not, the cell reference displays. Your scenarios display as column headings.

Adding a Scenarios Toolbar

You can create a Scenarios toolbar to easily view your scenarios.

1. Select View, Toolbars, Customize.

[pic]

2. From the Toolbars tab, click New. The New Toolbar dialog box displays.

3. In the Toolbar name: field, enter Scenarios.

[pic]

4. Click OK. A new floating toolbar displays with no tools.

5. Activate the Commands tab.

6. From the Categories list, select Tools.

7. From the Commands list, scroll down to find the Scenario box and drag it to your new Scenarios toolbar. The new toolbar will look like this.

[pic]

The Scenarios toolbar displays all the scenarios in the active workbook in the drop-down list. To view a scenario, simply select it from the list.

To move the toolbar, click and drag it to the desired location, or double-click the blue title bar to dock it with the other toolbars at the top of the screen.

8. Click Close to close the Customize dialog box.

Pivot Tables

Subtotals are great for summarizing one column of data. However, if you want to summarize more than one field, you should use Pivot Tables. With a Pivot Table, you can use one or more fields within the data to control how the data displays. When you select a different field to build the table, the information pivots to display the data to emphasize that data. It is a great way to analyze information in different ways.

Summarizing Data

A large data list often provides an abundance of information. However, it is often difficult to analyze or summarize that data. Using a Pivot Table, you can select fields and place them in specific areas to control how the data is summarized. This allows you to filter out any unnecessary data and make it easier to view pertinent information. Pivot Tables are easy to rearrange to provide different kinds of summary information.

Example: Sample Data List

[pic]

Only certain types of databases work well for Pivot Tables. The data should have the following characteristics:

• Each column should have a meaningful heading.

• Each column should contain the same kind of information.

• Some of the data should be common for multiple records. In the example above, there are multiple records for the same Month, the same Branch, the same SoldBy, and the same Account Type. These are the fields that can be summarized.

• The list should not contain any blank rows or blank columns.

• If possible, the list should be on a worksheet by itself. If not, at least one blank row and one blank column should separate the data.

Data Field Types

Pivot Tables use two types of fields in the data list: data fields and category fields.

• Data fields - contain numeric value, which can be calculated. In the example on the previous page, Deposit is a data field.

• Category Fields – describe the data. In the example on the previous page, Month, Branch, SoldBy, and AcctType, are category fields. These fields define the numeric field. This categorical data is alphabetic and cannot be calculated. However, the Count function can be used to determine how many records are in a particular category.

Example: Sample Pivot Tables

The Pivot Table below shows Deposits by Acct Type by Sold By (employee type). This illustrates the total amount of the deposits for the four types of accounts sold by the Personal Banking personnel and by the Tellers.

[pic]

The Pivot Table below shows Count of Acct Types, by Sold By (employee type) per Branch. This illustrates the number (count) of accounts sold by Personal Banking personnel and by Tellers for each Branch.

[pic]

Creating a Pivot Table

You can use the Pivot Table and Pivot Chart Wizard to create a Pivot Table. Follow these steps to use the wizard.

1. Select any cell in the data list.

2. From the menu, select Data, Pivot Table and Pivot Chart Report. The following dialog box displays.

[pic]

3. In the Where is the data that you want to analyze? field, select the appropriate option. The table below provides a brief explanation of options. For this class we will select, Microsoft Excel list or database.

|Option: |Description: |

|Microsoft Excel list or database |Data list exists in an Excel Worksheet. Must be in one worksheet in |

| |contiguous cells. First row should contain field names. Data can include |

| |text, values, and formulas |

|External data source |Data exists in another program (e.g., Access or dBASE). Data is retrieved|

| |using a Query. If Query is not installed, the Wizard will prompt you |

| |through installation. |

|Multiple consolidation ranges |Data exists in multiple Excel worksheet ranges. |

|Another Pivot Table or Pivot Chart |Option is only available if data exists in another Pivot Table in the |

| |active workbook. |

4. In the What kind of report do you want to create? field, select the report type. For this part of the class select Pivot Table.

5. Click [pic]. The next dialog box displays. This will vary, depending upon the options selected in the previous dialog box. In this example, we selected Microsoft Excel list or database in the previous dialog box.

[pic]

6. In the Range: field, verify that the appropriate range is referenced. If the correct range is not displayed, enter the appropriate range.

7. Click [pic]. The following dialog box displays.

[pic]

8. In the Where do you want to put the Pivot Table? field, indicate the desired location. If you select Existing worksheet, indicate the starting cell location.

9. Click [pic] to complete the wizard. A Pivot Table template displays along with the Pivot Table Toolbar and Pivot Table Field List.

[pic]

10. From the Pivot Table Field List, drag and drop the field buttons to create your Pivot Table. For this example, we want to see the data summarized by the type of accounts, who sold them, and amount of deposits. (OR, select the field, and click the Add to button to add it to the desired area.)

• Drag the field buttons you want to display as row headers to the Drop Row Fields Here area (e.g., AcctType). The type of data used here should be a category by which you want the data summarized.

• Drag the field buttons you want to display as the column header(s) to the Drop Column Fields Here area (e.g., SoldBy). The type of data used here should be another category by which you want the data summarized.

• Drop the field buttons representing the data you want to summarize to the Drop Data Items Here area (e.g., Deposit). Excel applies the SUM function to fields in the Data area that contain numeric values. Excel applies the COUNT function to fields in the data area that contain non-numeric values.)

• If desired, drag the field buttons by which you want to filter the data to the Drop Page Fields Here area (e.g., Branch). See page 24 for more information on filtering.

[pic]

Showing the Pivot Table Toolbar or Field List

If the Pivot Table Field List or Pivot Table Toolbar do not display, click on the Pivot Table to activate it. If necessary, right-click on the Pivot Table and select Show Pivot Table Toolbar or Show Field List, as necessary.

Filtering Data in a Pivot Table

You can filter the data to display only specific values from the Category fields or Page fields.

Filtering Page Fields

If you have field(s) in the Page Fields area, you can filter by those fields. By default, (All) data displays. Click the [pic] in the Page Fields area, select the desired option, and click OK.

[pic]

Filtering Category Fields

Click the [pic] at the end of the Category field and deselect those records you do not want to display. Click OK.

[pic]

Modifying a Pivot Table

A Pivot Table is very easy to modify and restructure.

• To rearrange the fields, simply drag and drop the field buttons to a new location. When you drag a column field to a row position, Excel pivots the table to reflect this change.

• To add a field, drag the desired field button from the Pivot Table Field List to the desired location on the Pivot Table.

• To remove a field, drag the field button off the Pivot Table.

Examples

The following examples use the same data list to display different emphasis by modifying the Pivot Table.

[pic]

[pic]

[pic]

Copying a Pivot Table

You can copy and paste a Pivot Table to another location. This is useful if you want to make a copy of the Pivot Table so that you can modify it to emphasize different data. Follow these steps to copy a Pivot Table.

1. Right-click anywhere on the Pivot Table, and choose Select, Entire Table.

2. From the menu, select the Edit, Copy (or click the Copy button [pic] on the Standard toolbar.)

3. Select the cell and location where you want to paste the copy.

4. From the menu, select Edit, Paste (or click the Paste button [pic] on the Standard toolbar.)

5. Modify the copy of the Pivot Table as desired.

Hint: Press Esc to remove the marquee around the copied cells.

Customizing a Category Field

You can change the name of a field or change the way the data is summarized. Follow these steps to customize a category field.

1. From the Pivot Table, double-click the category field (shaded) button you want to change. The Pivot Table Field dialog box displays. This dialog box varies, depending upon the field selected.

[pic]

2. Make the desired changes, then click OK.

Changing the Number Format or Function for Summarized Cells

You can change the number format or the function for cells in a Pivot Table. Number formats from the original data do not display in the Pivot Table by default.

1. Select any data (number) cell in the Pivot Table.

2. Click the Field Settings button [pic] on the Pivot Table toolbar. The Pivot Table Field dialog box displays.

[pic]

3. If you want to change the function used, in the Summarize by: field, select the desired function.

4. If you want to change the number format, click the [pic] button and select the desired option.

Note: For help with the dialog box, click the [pic] in the upper-right corner of the dialog box, then click on the option with which you need help.

5. Click OK.

Formatting a Pivot Table

When you create a Pivot Table, Excel applies a default format. You can apply a different AutoFormat, if desired.

1. Select any cell in the Pivot Table to display the Pivot Table toolbar. (If the toolbar does not display, select View, Toolbars, Pivot Table.)

2. From the Pivot Table toolbar, click the Format Report button [pic]. The AutoFormat dialog box displays.

[pic]

3. Scroll through the available formats to select the desired option.

4. Click OK to apply the format.

Refreshing Pivot Table Data

If you change your source data, you must update the Pivot Table manually. Excel does not do this automatically. Select any cell in the Pivot Table and use any of the following methods to refresh the data.

• From the Pivot Table toolbar, click the Refresh Data button [pic].

• From the menu, select Data, Refresh Data.

• Right-click in the Pivot Table and select Refresh Data.

Grouping Pivot Table Items

You can group items together. For example, you may want to group the months of the year into quarters.

1. Select the cells you want to group. (If the cells are not contiguous, press and hold the Ctrl key as you select the cells.)

[pic]

2. Right-click any selected cell and choose Group and Show Detail, Group. Excel creates a new field containing the grouped items.

[pic]

3. Repeat steps 1 and 2 to group all desired items.

Renaming the New Field

You may want to rename the new field to something more meaningful.

1. Double-click the new field name (shaded button) to display the Pivot Table Field dialog box.

[pic]

2. In the Name: field, type the new name (e.g., Quarters).

3. Click OK.

Renaming the Grouped Items

To make the group names more meaningful, select the cell you want to rename and type a new name (e.g., Quarter 1, Quarter, 2, etc.).

Displaying Group Detail

To hide or display group detail, right-click on the cell name (e.g., Quarter 1), and select Group and Show Detail, Hide Detail, or Show Detail, as desired.

Removing the Original Field

To remove the original field, simply drag the original field name off the Pivot Table.

Ungrouping Items

To ungroup items, right-click the field (shaded) button you want to ungroup. Select Group and Outline, Ungroup.

Setting Pivot Table Options

You can set options to determine the look and function of your Pivot Tables.

1. Right-click any cell in the Pivot Table and select Table Options. The Pivot Table Options dialog box displays.

[pic]

2. Select the desired options. For help with this dialog box, click the [pic] in the upper-right corner of the dialog box, then click on the option with which you need help. Below are some suggestions:

• To make your Pivot Table refresh each time the file is opened, select Refresh on open.

• To display a “0” in empty cells (rather than blank cells), enter a “0” in the For Empty Cells, show: field.

3. Click OK.

Drilling Down

If you want to see details about a particular portion of the Pivot Table, double-click a cell in the area you want to view. A new worksheet displays the detailed results.

The example below displays the count (number) of new accounts sold by Personal Banking representatives and Tellers per branch. To view details about that information, you can drill down.

[pic]

[pic]

Pivot Charts

Using Excel 2000, you can also create a Pivot Chart. This provides a way to display a graphical representation of Pivot Table data. You can create a Pivot Chart from a Pivot Table you have already created or using the Pivot Table and Pivot Chart Wizard.

Using an Existing Pivot Table

Follow these steps to create a Pivot Chart from an existing Pivot Table.

1. Select any cell in the existing Pivot Table.

[pic]

2. From the Pivot Table toolbar, click the Chart Wizard button [pic]. Excel creates a new chart on a new chart sheet in the workbook based on the Pivot Table data. A stacked column chart is the default Pivot Chart.

[pic]

3. To modify the chart, click [pic] again,

OR

You can modify the chart style using the standard charting options (Chart toolbar and/or Chart menu).

Note: If you change the source data, the changes do not automatically update in the Pivot Chart. Click the Refresh Data button [pic] on the Pivot Table toolbar.

Creating a Pivot Chart Using the Pivot Table and Pivot Chart Wizard

You can also create both a Pivot Table and Pivot Chart at the same time using the wizard.

1. Select any cell in the data list.

2. From the menu, select Data, Pivot Table and Pivot Chart Report. The Pivot Table and Pivot Chart Wizard dialog box displays.

[pic]

3. In the Where is the Data that you want to analyze? field, select the location of the data source.

4. In the What kind of report do you want to create? field, select Pivot Chart (with Pivot Table).

5. Click [pic]. The Pivot Table and Pivot Chart Wizard—Step 2 of 3 dialog box displays. The dialog box varies depending on your previous selection.

[pic]

6. Verify that the appropriate data list is entered in the Range: field. If the correct range is not displayed, enter the appropriate range.

7. Click [pic]. The following dialog box displays.

[pic]

8. Indicate the location where you want to display the Pivot Table. The Pivot Chart displays on a new sheet.

9. Click [pic]. A blank Pivot Chart template displays on a new sheet.

[pic]

10. Click and drag the desired fields as described earlier in the Pivot Table sections of this class. The chart displays with the selected fields in the default format. A Pivot Table is also created reflecting the same fields as the Pivot Chart.

[pic]

11. Format the chart as necessary. To format:

• Click the [pic] button again from the Pivot Table toolbar.

• Use the Chart Menu.

• Double-click the item you want to format.

• Use the Charting Toolbar. (If the Charting Toolbar is not displayed, select View, Toolbars, and select Chart.)

[pic]

Creating Basic Macros

A macro is a series of instructions to automate a task. You can perform a series or keystrokes and/or mouse actions with a single command. This class will cover creating macros using Excel’s macro recorder. Excel translates the recorded actions into a Visual Basic for Applications (VBA) subroutine that can be played back (performed) at any time. Those with VBA programming experience may write code directly in the VBA macro language; however, we will not cover that information in this class.

After creating a macro, you can assign it a keyboard shortcut or add a button on your worksheet. You can make a macro available to only one specific workbook or to all workbooks.

You can record simple macros to format text, insert data, perform functions, or move to different areas in a workbook.

Recording a Macro

Follow these steps to record a series of actions into a macro.

1. Position your cursor where you want to be when the macro is played back. The position varies depending on the purpose of your macro.

24. From the menu, select Tools, Macro, Record New Macro. The Record Macro dialog box displays.

[pic]

25. In the Macro name: field, type the name for your macro. Macro names cannot have spaces.

26. If you want to assign a keyboard shortcut to this macro, in the Shortcut key: field, enter the key to be used in conjunction with the Ctrl key. (e.g., Ctrl + k). It is generally recommended to use lower-case letters to use the fewest keystrokes.

Note: Macro keyboard shortcuts override standard keyboard shortcuts. For example, if you assign Ctrl + c to run a macro, it will override the usual “copy” keyboard command.

27. In the Store macro in: field, select where you want to store this macro.

• If you want this macro available for any workbook you open (not recommended for lab computers), select Personal Macro Workbook.

• If you want the macro available only in new workbooks, select New Workbook.

• If you want the macro available only in the active workbook, select This Workbook. This option is recommended for lab users.

28. If desired, add a description in the Description: field.

29. Click OK. The Stop Recording toolbar displays while you are recording the macro.

[pic]

30. Perform the entire process you want to record in the macro. Excel’s macro recorder records each command, including all keystrokes and mouse clicks.

31. When finished, click the Stop Recording button [pic] on the Stop Recording toolbar. (or select Tools, Macro, Stop Recording).

Absolute and Relative Macros

By default, Excel uses absolute macro recording. This means, for instance, if you select a certain range of cells while recording your macro, those cells will always be selected when you run the macro. (Or it will move to cells relative to where you began running the macro.) If you want the macro to run on whatever range of cells you select, you will want to create a relative macro.

You can toggle this option on and/or off by clicking the Relative Reference button [pic] on the Stop Recording toolbar. When viewing the Stop Recording toolbar, it is difficult to determine which way is relative. If the button looks “selected,” it is relative. If not, it is absolute.

Note: It is important to note that the Relative Reference button remains selected, until you click it again to de-select it.

Running a Macro

Follow these steps to run a macro.

From the Menu

You can always run a macro from the menu.

1. Position your cursor in the location where you want to start running the macro.

2. Select Tools, Macro, Macros. The Macro dialog box displays.

[pic]

3. Select the macro you want to run.

4. Click [pic] to execute the selected macro.

From a Shortcut Key

If you have assigned a shortcut key to your macro, follow these steps to run the macro using the keystrokes.

1. Position your cursor in the location where you want to start running the macro.

2. Press and hold the Ctrl key as you press the character assigned to the macro.

Re-running a Macro

To immediately re-run the macro, select Edit, Repeat Macros (or press Ctrl + y).

Viewing and Modifying a Macro

If after running your macro, you want to make some changes to it, you can view and change it directly in the VBA code. However, if the changes are complex, you may decide to re-record the macro. The directions below illustrate how to make minor modifications to a macro in your current workbook.

1. Open the workbook containing the macro.

2. Select Tools, Macro, Macros. The Macro dialog box displays.

3. Select the macro you want to modify and click [pic]. The Microsoft Visual Basic – [Worksheet Name] window opens.

[pic]

4. The module for this macro begins with “Sub” and ends with “End Sub.” The commands and description display describing the macro translated from the macro recorder. To make a simple modification, (change font size from 12 to 14), make the appropriate change.

5. When finished, select File, Close and Return to Microsoft Excel.

6. Run the macro again to verify the change(s).

Macro Security

Office 2003 has high macro security enabled by default. This may result in some macros not running unless you reduce the macro security. Go to Tools, Options. From the Security tab, click the Macro Security button and set a lower security level.

Assigning Shortcut Keys to Existing Macros

If you created a macro without assigning a shortcut key, you can do it later. Shortcut keys provide a quick way to perform macros that you use frequently.

1. Select Tools, Macro, Macros. The Macro dialog box displays.

2. Select the macro to which you want to assign a shortcut key.

3. Click [pic]. The Macro Options dialog box displays.

[pic]

4. In the Shortcut key: field, type the keyboard character (letter) you want to use. This character will be used on conjunction with the Ctrl key.

5. Click OK to close the Macro Options dialog box.

6. Click [pic] to close the Macro dialog box.

Note: You can use the same procedure to change a shortcut key for an existing macro.

Creating a Macro Button

You can create a button directly on your worksheet to activate a particular macro. Follow this procedure to attach an existing macro to a macro button.

1. Open the workbook containing the macro for which you want to create a button.

2. Activate the page where you want the button to display.

3. Right-click any toolbar (or select View, Toolbars) and select Forms. The forms toolbar displays.

[pic]

4. Click the Button button [pic]. Your cursor displays as a crosshair.

5. Click and drag to create a button on your worksheet. When you release the mouse button, the Assign Macro dialog box displays.

[pic]

6. Select the macro you want to assign to the button.

7. Click OK to close the dialog box.

8. Select the text on the button and then type an appropriate label. Format the text, if desired.

[pic]

9. Click outside the button to deselect it.

10. To run the macro, position the cursor in the appropriate cell (if necessary) and click the button.

Formatting a Macro Button

You can format the macro button to display a different font, size, and color. Simply right-click the button and select Format Control. Select the appropriate options and click OK. To resize, click and drag a selection handle. To move, click and drag the border to another location.

Assigning a Text Box to Run a Macro

You can assign a macro to a text box. This is particularly useful when working with scenarios.

1. Right-click on the border of the text box and select Assign Macro. The Assign Macros dialog box displays.

2. Select from the existing macros.

3. Click OK.

Deleting Macros

Follow these steps to delete a macro that is no longer useful to you.

1. Select Tools, Macro, Macros. The Macro dialog box displays.

2. Select the macro you want to delete.

3. Click [pic].

4. Click Yes to delete the macro.

Personal Workbook Macros

If you want a macro available for all workbooks, you can store it in your Personal Macro Workbook. (See page 38.) This is only an option for those always working on the same computer. Personal macros are stored in a hidden Personal Macro Workbook that always loads when you start up Excel. If you want to delete a macro from your Personal Macro Workbook, unhide the workbook by selecting Window, Unhide, then delete following the instructions above.

Using Text Functions

Excel has several text functions. If you ever import data from another application into Excel, that data may not import in the format you prefer. You can use Excel’s text functions to “fix” your data, rather than re-keying the information. We will cover several text functions in this class.

Using the Trim Function to Eliminate Extra Spaces

Follow these steps if you want to remove extra spaces between words in your data.

1. Insert a new column (or move to a blank column) to display a copy of the text without the spaces.

2. Select the first data cell in the new column.

3. Click the Insert Function button [pic] on the Formula bar. The Insert Function dialog box displays.

4. From the Function category: field, select Text.

5. From the Function name: field, select TRIM.

[pic]

6. Click OK to close the Insert Function dialog box and display the TRIM function box. If the function box covers your data, click anywhere on the function box to drag it out of your way (Or click the Collapse Dialog button [pic] to collapse the dialog box. Click the button again to expand the dialog box.)

7. Select the first cell that contains the extra spaces.

8. Click OK to close the function box and display the text without extra spaces.

9. If appropriate, drag (or double-click) the fill handle to copy the formula to the adjacent cells to eliminate the extra spaces from those cells.

Working with Values and Formulas

When you perform the above procedure, you will have two columns of similar data. However, the original column contains values. The new column contains formulas. If you delete the original column, your will get a resulting error in the formula column. Follow these steps to change formulas to values.

1. Select the cells containing the formula you want to convert to a value.

2. Select Edit, Copy, or click the Copy button [pic] on the Standard toolbar. A marquee displays around the copied cells.

3. With the marquee around the copied cells, click the dropdown arrow on the Paste button [pic]. The following options display.

[pic]

4. Select Values.

5. If desired, delete the original column.

Hint: To remove the marquee around the cells, press Esc.

Using the Proper Function to Change Text to “Proper” Case

Follow these steps to change text to proper case (first character of each word is upper case, and the rest of the word is lowercase).

1. Insert a new column (or move to a blank column) to display the “Proper Text” version of your data.

2. Select the first data cell in the new (blank) column.

3. Click the Insert Function button [pic] on the Formula bar. The Insert Function dialog box displays.

4. In the category field, select Text.

5. In the Select a Function: field, select PROPER.

6. Click OK. The PROPER function box displays. If necessary, move or collapse the box out of your way.

7. With the cursor in the Text field, click the first cell that contains the uppercase text.

8. Click OK to close the function box and display the proper text in the cell.

9. If appropriate, auto-fill the formula to copy it to adjacent cells.

Note: This function only changes to proper case -- not to sentence case. The text, TODAY IS THURSDAY becomes Today Is Thursday. If you have other changes to make, you need to make those manually.

Merging Cells

You can merge the contents of two or more cells into one cell. This can be useful when working with names and addresses, for instance. Perhaps one column contains the address number and another column contains the street. You can combine those cells into one cell. Follow these steps to merge data.

1. Insert a new column to contain the merged data.

2. Select the first data cell in the new column.

3. Click the Insert Function button [pic] on the Formula bar. The Insert Function dialog box displays.

4. In the category: field, select Text.

5. In the Select a function: field, select CONCATENATE.

6. Click OK. The CONCATENATE function box displays. If necessary, move or collapse the box out of your way.

7. With the cursor in the Text 1 field, click the first cell to be merged (or type a constant value).

8. Position your cursor in the Text 2 field and either select the next field to be merged, or press the spacebar, if you want a space to display between the first and second fields. You can also type a constant value, if desired.

9. Position your cursor in the Text 3 field and select the next cell to be merged. (Merge additional fields, if desired.)

10. Click OK to close the CONCATENATE function box and display the merged data.

11. If appropriate, auto-fill the formula to copy it to adjacent cells.

Splitting a Column into Multiple Columns

You may, at times, have a column that you want to divide. For instance, both first name and last name may display in the same column, and you want to have a separate column for each. This is not a Function, but is performed from the Data menu. Follow these steps to split a column.

1. Insert the appropriate number of additional columns to the right to of the column you want to split.

2. Select the cells you want to split.

3. From the menu, select Data, Text to Columns. The Convert Text to Columns Wizard displays.

[pic]

4. Select the appropriate options. For our example, select Delimited.

5. Click Next.

6. In the Delimiters area, select the appropriate delimiter. In our example, select Space.

7. Click Next.

8. Click Finish.

Lookup and Reference Functions

Sometimes you may need data that exists in another area of a worksheet, another worksheet, or even another workbook. You can use a Lookup function to look up the information you want. In this class we will use the VLOOKUP (vertical, which uses columns). There is also an HLOOKUP function, which works in a similar manner.

Follow these steps to use the VLOOKUP function.

1. Open the workbook containing the worksheet into which you want to merge additional data.

2. If the additional data is in a different workbook, open that workbook as well.

3. Activate the worksheet into which you want the merged data to display.

4. Insert a column to hold the new data.

5. Select the first data cell in the new column.

6. Click the Insert Function button [pic] on the Formula toolbar. The Insert Function dialog box displays.

7. In the category: field, select Lookup and Reference.

8. In the Select a function: field, select VLOOKUP and click OK.

[pic]

9. Place your cursor in the Lookup_value field, then select the value you want to lookup (e.g., department).

10. Position your cursor in the Table_array field.

11. If the second worksheet is in the same workbook, click the tab of the appropriate worksheet.

(If the second worksheet is in another workbook, display both worksheets by selecting Window, Arrange, Horizontal, then click OK. Click the other workbook to activate it.)

12. From the second worksheet, select the columns (not just the cells) that contain the data you want to look up (e.g., department) along with the column you want to merge into the other worksheet (e.g., department number). The column that contains the matching field (e.g., department) must be the first column selected. This creates the lookup table.

13. In the Col_index_num field, type the number of the column in the lookup table (e.g., 3) that contains the data you want merged into your worksheet.

14. In the Range_lookup field, type the word FALSE. This tells Excel to look for an exact match.

[pic]

15. Click OK to close the VLOOKUP function argument box and display the new value in the new column.

16. If appropriate, auto-fill the formula to the adjacent cells.

Appendix

Data Validation

Excel allows you to specify which data is valid for individual cells or cell ranges to make sure that correct data is entered on a worksheet. Follow these steps to set up data validation.

1. Select the cells you want to validate.

2. Select Data, Validation. The Data Validation dialog box displays.

[pic]

3. Activate each tab and complete the appropriate options. See below for further information.

4. When finished, click OK.

The Settings Tab

You can set criteria on the settings tab for such things as numbers, text length, lists, dates, etc. If someone enters data that does not match the specified criteria, a screen similar to the following displays.

[pic]

Creating a Drop-down List

You can use data validation to create a drop-down list from which to select items when entering data.

1. Select the cells for which you want to create a drop-down list.

2. Select Data, Validation. The Data Validation dialog box displays.

3. Activate the Settings tab.

4. In the Allow area, select List.

5. In the Source area, enter the appropriate list items, separated by a comma.

or

If the items are already entered in the worksheet, click the Collapse button [pic] to select the appropriate cells.

6. When finished, click OK.

Tip: If you want to select from a list of items entered in a column without creating a data validation setting, right-click on the cell beneath a contiguous list in a column. Select Pick From List.

The Input Message Tab

From the Input message tab, you can enter text to describe the type of information you want entered into the validated cell(s). When someone moves to the validated cell(s) a pop-up box displays the text entered in the Input message: field in the dialog box below. This does not restrict the entry, but is used as a guide for people entering data. It can be used in combination with the settings tab, however.

[pic]

The Error Alert Tab

You can create a special error message to display for validated cells when the data entered does not meet the validation settings specified on the Settings tab.

[pic]

Clearing Validation

If you no longer want validation settings you can clear the settings.

1. Select the cells from which you want to remove the validation.

2. Select Data Validation.

3. Click the Clear All button.

Customizing Toolbars

To make them more useful, the toolbars can be repositioned and tools can be added to and deleted from existing or new toolbars.

Changing the Location of Toolbars

Toolbars can be “docked” at any side of the screen or “floating” at any other screen location.

• Drag the Move bar [pic] of a docked toolbar to move it.

• Drag the blue title bar of a floating toolbar to move it.

• Double click on the title bar of a floating toolbar to dock it.

Viewing or Hiding Toolbars

1. Click with the right mouse button on a toolbar to view the toolbar shortcut menu

or

Select View, Toolbars.

2. Select the toolbar name to view or hide it.

Note: The check marks indicate toolbars currently in view.

Creating a New Toolbar

1. Click with the right mouse button on a toolbar to view the toolbar shortcut menu.

or

Select View, Toolbars.

2. Select Customize.

3. Activate the Toolbars tab.

4. Click New.

5. Type the name for the new toolbar.

6. Click OK or press Enter.

7. Click Close to close the Customize dialog box.

Note: The new toolbar will have no tools and be a floating toolbar.

Adding Tools to Toolbars

1. View the toolbar to be altered.

2. Select View, Toolbars.

or

Click with the right mouse button on a toolbar to view the toolbar shortcut menu.

3. Select Customize.

4. Activate the Commands tab.

5. Select the appropriate menu option or other category from the list on the left side of the dialog box. (All buttons are listed under the appropriate menu command.)

6. Locate the appropriate command in the list on the right side.

7. Drag the command from the dialog box to the location on the toolbar.

8. Click Close.

Moving Tools on a Toolbar

1. Click with the right mouse button on a toolbar to view the toolbar shortcut menu.

or

Select View, Toolbars.

2. Select Customize. (You must be in the “Customize” view to move tools on a toolbar.)

3. Drag the tools on a toolbar to a new location.

4. Click Close.

Tip: Create a Calculate Toolbar

You can create a toolbar to assist you with creating formulas. Use this toolbar to select the operators and cells to enter them in the formula, rather than typing in the cell references and punctuation.

1. Click with the right mouse button on a toolbar to view the toolbar shortcut menu.

or

Select View, Toolbars.

2. Select Customize.

3. Activate the Toolbars tab.

4. Click New.

5. Type the name for the new toolbar (e.g., Calculate).

6. Click OK or press Enter.

Note: The new toolbar will have no tools and be a floating toolbar.

7. Activate the Commands tab.

8. Click and drag the following buttons from the Insert menu in the left side of the Customize dialog box to the Calculate toolbar.

[pic]

9. Click Close.

10. To “dock” the toolbar, double-click the blue title bar or drag it to the desired location.

11. Use the buttons when creating a formula. Click =, then click the cell, click the operator or parenthesis, etc. When finished, click the green ( from the Formula bar.

Deleting a Toolbar You Have Created

1. Click with the right mouse button on a toolbar to view the toolbar shortcut menu or select View, Toolbars.

2. Select Customize.

3. Activate the Toolbars tab.

4. Select the toolbar to be deleted.

5. Click Delete.

6. Click Close.

Restoring Toolbars and Menus to their Original State

1. Click with the right mouse button on a toolbar to view the toolbar shortcut menu or select View, Toolbars.

2. Select Customize.

3. Activate the Toolbars tab.

4. Select the toolbar to be reset.

5. Click Reset.

6. Click Close.

Practice File

Go to . Click the Microsoft Excel Advanced link to access these course materials and the practice file used in class.

-----------------------

Formula to be tested:

=PMT(B2/12,B3,-B1)

Input cell, located “outside” table area.

Formula to be tested

Input variable

Cells containing formulas

Empty Cell

To display these results:

All Accounts sold by Tellers at the Stillwater Branch.

Double-click this cell

This Pivot Table displays a “count” of the types of accounts sold. The category field of AcctType was placed in the data area of the Pivot Table template.

PivotTable Template

PivotTable Toolbar

Un-named

Cell

Scenario Names

Expand or Collapse

Named cell

Un-named cell

=PMT(B2/12,B3,-B1)

Formula to be tested:

=PMT(A2/12,360,C1)

=PMT(A2/12,360,C1)

Button button

Input variable

Important!

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download