Auditing the Document

To audit a document you need to first view the Auditing Toolbar

When you click on a cell you can click either the Trace Precedents or Trace Dependents buttons to see a visual marking indicating what cells were used in the formula or what cells are depending on that cell. Clicking again will trace further precedents or dependents. Very helpful for a worksheet you've inherited or one that's not working correctly!

top of page

Adding Buttons to the Toolbar

Almost every command in the Excel menus can be a button on the toolbar. The advantage of this is obvious -- it takes less time to click a button than it does to access a menu. It's easy to do, try it.

1. From the Tools menu choose Customize...
2. Make sure the Options tab is chosen
3. Click the category of the button
4. When you find the button in the right window, drag the button up to the toolbar at the top of the screen. Release the mouse when your mouse is positioned on the toolbar.
5. Your new tool is in place. If you need to move it left or right, hold down the mouse button and drag it to a new location.

NOTE: You can remove buttons at any time by holding down the Alt key and dragging them down off the toolbar.

top of page

Freezing Panes

Have you ever had difficulty entering data into an Excel worksheet because you can't see "A" column at the same time you are looking at "P" column? Do you also have trouble viewing headings in row 3 while entering data in row 42? Well here's the answer -- Freezing Panes

Select the cell just below the headings and to the right of the row headings (see figure 1). Choose Freeze Panes from the Window menu. As you scroll to the right as well as down the document the column headings and row headings are frozen in place (see figure 2).

Figure 1 - Select the cell below the column headings and to the right of the row headings (cell B4 in the example)

Figure 2 - Note the column and row headings remain stationary while the rest of the document scrolls.

top of page

Taking Pictures of Your Spreadsheet 

This cool feature of Excel used to be on the toolbar but it apparently fell out of favor with the Excel developers. Everyone I've shown this to has a use for it.

1. Add the camera tool to the toolbar (see "Adding Buttons to the Toolbar" for help on this. The camera tool is in the Tools category, way down the list.
2. Select a section of the worksheet or workbook and click the camera tool.
3. Go to the worksheet or workbook where you would like the picture displayed and click. The picture will be displayed on top of the current worksheet.
4. To remove the picture, click the picture once and press the Delete key.

NOTE: This is a dynamic picture of your work area. As you make changes in the worksheet that affect this pictured area, the picture changes.

top of page

Data entry using a “pick list”

Excel enables you to restrict the values you can enter into certain cells. By restricting values, you ensure that your worksheet entries are valid and consistent.

 Creating a Pick List

1. Type the entries you want in the pick list into adjacent cells in a column
2. Select the list
3. From the Insert menu choose Name and Define
4. Type a name for the list
5. Click OK
6. Select the cells or columns where the Pick List will be displayed
7. From the Data menu choose Validation
8. Click List
9. Type = and the name you defined in step 4
10. Click OK
    A drop-down list appears to the right of each cell. To enter data in the cell, click the drop-down menu and click a value

top of page

Printing Gridlines

By default, gridlines do not print on an Excel worksheet. Seems like they should but I'm not in charge! Also seems like "landscape" should be the default paper orientation but that's another pet peeve. Here's how to turn gridlines on.

1. From the File menu choose Page Setup
2. Click on the "sheet" tab
3. Click the checkbox for "gridlines"

There, wasn't that simple?

top of page

Data Validation

Excel allows you to restrict the type of information that is allowed in a cell. This can be useful if a number must be in a range or have another restriction

Defining Data Validation

1. Select the cells or columns where you want to use data validation
2. From the Data menu choose Validation
3. Use the Allow pull down to identify the type of data you want to define
4. Click the Input Message tab to create a message that will be displayed when the user hovers on the cells or columns where validation has been defined
5. Click the Error Alert tab to create a message that will be displayed when the user enters incorrect data
6. Click OK

Note: Steps 4 and 5 are optional entries

top of page

Outlining

The outlining feature allows you to quickly hide/show columns of data. Excel can automatically set up an outline using formulas or you can define your own outlines. This is best used on a worksheet that has formulas that create levels such as months feed into quarters and quarters feed into years. To view such a worksheet, click here.

Using Auto Outlining

1. Open a worksheet
2. From the Data menu choose Group and Outline and AutoOutline

Above your worksheet and down the left side you will see a gray area. At the top of that area you will see a series of small numbers in boxes. By clicking on the boxes Excel will hide columns and rows.

top of page

User Defined Value Lists

When you type a month name in a column and use the fill handle, Excel fills in the rest of the month series for you. This is because the programmers have defined the months of the year as a value list. You can define lists that you use frequently. These may include a list of offices, schools, employees, etc.

Defining Value Lists

1. Type the list in your document or open a document that has the list.
2. Select the list entries
3. From the Tools menu choose Options
4. Select the Custom Lists tab
5. Click the Import button

Now when you type one of the entries in a cell in any Excel document you can use the fill handle to fill your series. Cool!

top of page

Displaying/Hiding Formulas

Have you ever inherited a worksheet and want to look at the formulas? You can certainly do that one at a time with the formula bar but this trick allows you to view all the formulas at once.

To View Formulas

1. Hold down the Ctrl key and press the accent key...the accent key is the key to the left of the number 1 key on the standard keyboard (not the numeric keypad).

top of page

AutoSum on the Fly

Need to find the sum of a group of numbers but don't want to bother writing a formula? AutoSum on the fly is for you.

AutoSum on the Fly

1. Select a cell or group of cells. Remember you can use the Ctrl key to select non-adjacent cells.
2. Look in the Status Area on the lower right side of the screen, just above the task bar.

You should see "Sum=xxxx". Now didn't I tell you it would be easy! Right click on the Status Area where the sum is displayed and you'll see other functions that can be used.