Excel for Accountants: Volume 1

This chapter discusses my Top 5 favorite Excel shortcuts. We’ll cover others later, but for now, please commit the following five shortcuts to memory and begin using them immediately:

• Arrow keys - Navigate worksheet; Ctrl=Jump; Shift=Extend.
• F2 - Edit
• F4 - Cell Reference
• Doubleclick - Fill Down
• Ctrl + PageUp/PageDown - Next worksheet; Previous Worksheet

An Excel Named Reference is a friendly name that refers to a cell, range, or other item.

The benefits of using names are numerous:

• Easier to navigate (select the name from the Name Box to instantly navigate to the range)
• Easier to write formulas (because you don’t need to navigate to a sheet and select a range)
• Easier to understand formulas (names make it easier to interpret function arguments)
• Faster to insert names into formulas with the paste name (F3) key
• Easier to define a Data Validation list source
• Easier to define the source for PivotTables and charts

The simple way to insert a name is to highlight the cell or range, type the name in the Name Box, and then press the Enter key on your keyboard. Alternatively, you can enter a new name, as well as edit or delete an existing name, by activating the Name Manager.

An Excel Table is essentially any range of data that has been defined as a Table. It is easy to convert a normal range of data into a Table by using the following command:

• Insert > Table

Now that the normal range of data has been converted into a Table, it contains many special properties, including:

• Table Name
• AutoExpansion
• Structured References
• Automatic FillDown
• Automatic Totals
• Column Label Replacement

Excel's Data Validation feature allows you to control what a user can type in to a cell.

To apply Data Validation to a cell (or range), begin by selecting the cell (or range). Next, activate the Data Validation dialog box with:

• Data > Data Validation

You’ll find that the Data Validation dialog box has three tabs, Settings, Input Message, and Error Alert.

• The Settings tab is where you set the rule that defines a valid entry: for example, any whole number between 1 and 1000 or any date between 12/1/12 and 12/31/12.
• The Input Message tab is where you optionally define a message that will pop up when the cell is selected: for example, “Please enter a whole number between 1 and 1000.”
• The Error Alert tab is where you optionally define a message that will pop up when the user enters invalid data, for example: “Error: Please enter a whole number between 1 and 1000.”

Excel's Conditional Formatting feature allows you to control the format of a cell based on the value of the cell. The basic idea is to first identify the cell to be conditionally formatted. Then, define the condition(s), or rule(s). Finally, define the format to apply when the cell value meets a condition.

To apply Conditional Formatting, simply highlight the cell or range of cells, and then activate the Conditional Format dialog box by using the following:

• Home > Conditional Formatting

There is a function in Excel that is better for summing than the SUM function for accountants. In fact, I use it instead of the SUM function almost all of the time.

The SUBTOTAL function excludes other SUBTOTAL functions in its sum range.

SUM no more!

The Skinny Row is any blank row between the data region and the formula row. The row height is decreased so that it is skinny. I typically use a row height of 4, but you can also use other values, such as 3, 2, 1 or .75. Since the row is skinny, it is not noticeable on printed versions of the sheet. After you create the Skinny Row, you must write your formula to include it.

Sometimes, the workbook administrator may include notes, settings, assumptions, or other data that is not relevant to the user. In some cases, the productivity of the user may be increased when this extraneous information is hidden (that is, “out of sight, out of mind”).

It is often convenient to place your admin data on a worksheet, and then hide the worksheet so that it does not appear to a user. Once the sheet is active, navigate to the following command:

• Home > Format > Hide & Unhide > Hide Sheet

Identifying the cells that require a user’s input will result in workbooks that are more efficient, because they will take less time to update. Also, these workbooks will generally be more accurate because formula cells are less likely to be overwritten by user input.

Once the cells have been selected, apply the style by using the following command:

• Home > Cell Styles > Input