Web Site of

Ronald R. Tidd, Ph.D., CPA

Professor, Accounting, Master of Professional Accountancy
College of Business
 

 | Bio | Home | Useful Links |

Excel Computer Tutorial- Customizing Your Windows Desktop

| Objectives | Process |

Objectives

Excel seems to have an infinite number of tools and it provides at least two or three different ways to access each one. If you are like most people, you are only using a few of those tools and it is highly likely that you use some Excel tools repeatedly.

The objective of this exercise is to help you learn how to customize your Excel workspace so that you can more readily access those tools that you most frequently use and work with your spreadsheets more effectively and efficiently:

  1. Create customized toolbars.
    Such toolbars provide quick access to the features and functions that you use most when working with Excel. Consider developing a customized toolbar anytime you find yourself going to the menu bar repeatedly to do exactly the same thing and there are no shortcut keys for the task. (For example, it is easier to use the short cut CTRL-C to copy a selected object on a worksheet, then it is to use the copy command from a toolbar or menu bar.)
     

  2. Freeze (and unfreeze) panes.
    This allows you to keep the titles in rows and columns stationary while you navigate (scroll) through the data in a spreadsheet. (Alternatively, you could use the Window-Split commands on the Excel menu bar to freeze either a row or a column.)

Note: Excel saves your customized toolbars to a file that has an .xlb filename extension (e.g., Excel11.xlb in Excel 2003). The exact name of the file varies with the version of Excel that you are using, but it will be the only file with the .xlb extension. You should use the Windows Search utility to find it, then copy it. You can then use that copy as a backup or put it on a different computer so that you have access to your customized toolbars on every computer that you (or your colleagues) use. But you must make sure that you replace only the .xlb file in your account on the other computer and that both computers are using the same version of Excel.

Also refer to Backing Up Your Customized Toolbars by Allen Wyatt.

Process

 
Task

Procedures - (Useful Shortcut Keystrokes)
1. Prepare a customized toolbar in Excel.

Click on an icon to see a short video demonstration of the procedure.

  Windows Media Player Version

Flash Media Version

  1. Open the Call Detail Records file (Call Detail Records.xls) or a new file.

  2. On the Excel menu bar, click on Tools-Customize.

  3. In the Customize dialog box, click on the Toolbars tab.

  4. In the Toolbars list, select New and label your new toolbar in an appropriate manner- e.g., with the name of the task that it is designed for (CDR Analysis).

  5. In the Customize dialog box, select the Commands tab.

  6. In turn, select each of the Categories and drag the appropriate item from the Command to your new toolbar so that you replicate the toolbar below.

  7. Save your file and close it.

Brief explanations of the
components of this toolbar:

 

 

 

  1. Properties- opens a dialog box that let's you record and review the properties of the Excel file that is open, such as when it was created, which case it was for, and who created it.

  2. 100%- opens a drop-down list that lets you change the display size of the displayed Excel worksheet.

  3. Arial- opens a drop-down list that lets you change the font of the text in  selected cells.

  4. 10- opens a drop-down list that lets you change the size (points) of the text or  numbers in selected cells.

  5. A- opens a drop-down palette that lets you change the color of the text or  numbers in selected cells.

  6. Pail- opens a drop-down palette let's you change the color used to fill selected cells.

  7. Conditional formatting- opens a dialog box that allows you to set the parameters for conditional formatting of selected cells.

  8. Borders- a drop-down palette that lets you set the borders for selected cells.

  9. Row insert- inserts a row above the selected row.

  10. Row delete- deletes the row of the selected cell or row.

  11. Column insert- inserts a column to the left of a selected column.

  12. Column delete- deletes the column of the selected cell or column.

  13. Σ- enters the formula to sum a range of numbers in the selected cell.

  14. Subtotals- opens a dialog box that allows you to set the parameters for subtotals.

  15. Region selector- selects the entire table of data in which the selected cell exists.

  16. Sort- a dialog box that allows you to select up to three criteria by which to sort a range of data.

  17. Filter- inserts the drop-down lists in the header row of a table of data, to expedite sorting (filtering) the data

  18. Pivot Tables- opens a dialog box that allows you to create Pivot Tables.

  19. Comments- a dialog box that allows you to enter comments (explanations & reminders) into a selected cell, without affecting the cell's numeric or textual content.

If you need to protect data within an Excel worksheet, consider adding one of the "Protection" icons to your toolbar. They are located under the Tools command in the Customize dialog box that you used to create your toolbar above.
2. Freeze (and unfreeze) panes. 

Click on an icon to see a short video demonstration of the procedure.

  Windows Media Player Version

Flash Media Version

  1. Open the Call Detail Records file (Call Detail Records.xls).

  2. Select the cell below the column and to the right of the row that you want to keep displayed while you browse the data (e.g., cell B2).

  3. On the Excel menu bar, click on Window-Freeze Panes.

  4. Scroll down and to the right of the cell that you selected through the data. Note that as you scroll down and data moves, column and row names stay displayed at the top and at the right of the window.

  5. On the Excel menu bar, click on Window-Unfreeze Panes.

| Top |

Ronald R. Tidd, Ph.D., CPA
509.963.2466
© 2001-2008 All rights reserved.