How Good Are You With Excel?
By: Will Fleenor, CPA, Ph.D.
So you think you are pretty good with Excel. Have you ever had any formal Excel training? Or, are you self-taught? If you are self-taught, you are likely not as good as you think you are.
Doubt that last statement, do you? How about a simple test? If you know and fully understand the three simple (but very powerful and useful) features covered in this article, and, you have never attended any formal training, then I concede, your Excel knowledge is above average for someone in your situation.
Technique #1 – Custom Fill Lists
Just as the Fill Handle can be used to enter series of values, labels, and dates, it can also be used to fill cells with custom fill lists. For example, if users find themselves entering department names – sales, marketing, production, production scheduling, shipping and receiving, accounting and finance, human resources, etc. – in worksheet after worksheet, they can create a Custom List to use with the Fill Handle to automate the task. Users of Excel 2010 can create Custom Lists by clicking the File tab of the Ribbon, followed by Options, Advanced, and Edit Custom Lists to open the Custom Lists dialog box shown in Figure 1.
Figure 1 – Custom List Dialog Box
Users of Excel 2007 can create Custom Lists by clicking the Office Button, selecting Excel Options and then choosing Popular in the Navigation Pane on the left. In the pane on the right, click Edit Custom Lists.
There are several ways of creating a Custom List. Perhaps, the easiest way is to use an existing row or column of labels. Highlight the row or column that contains the labels that will make up the Custom List. Click Import in the Custom Lists dialog box, then click OK to complete the process, as shown in Figure 2.
Figure 2 – Creating a Custom List for Use with the Fill Handle
To use the Custom List, simply enter into a cell one of the labels included in the list and then drag the Fill Handle down or across adjacent cells.
Technique #2 – Convert Negative Numbers
Quite often, accountants export data from a general ledger for reporting and analysis. Some of the data, such as sales revenue or cash payments, export as negative numbers. For example, check amounts are reported as negative numbers in the QuickBooks Missing Check report. To convert these amounts to positive numbers for analysis and reporting, simply use the Paste Special command to multiply them by minus one (-1). There is no need to use the commonly employed convoluted process of building formulas, copying them down, pasting their values, and deleting the column of original data.
To convert negative numbers to positive values using Paste Special, do the following:
- Enter minus one (-1) in an unused cell of the worksheet.
- With the cursor still in the cell where minus one (-1) was entered, copy its contents to the clipboard by pressing CTRL + C.
- Now, highlight the cells containing the negative numbers to be converted, and, from the Home tab of the Ribbon, click Paste, Paste Special.
- In the Operation section of the Paste Special dialog box, select Multiply and click OK as shown in Figure 3.
Figure 3 – Using Paste Special to Alter the Default Pasting Rules
- Delete the contents of the cell that contains minus one (-1) to complete the process.
Other types of special pastes are available from the Paste Special dialog box. From that dialog box, users can paste column widths, formats, or comments, or they can perform mathematical operations.
Technique #3 – View All Formulas
The prior tip allows us to identify all of the cells in a worksheet that contain formulas or other special characteristics, but this tip displays all of the formulas. Once Excel displays the formulas, users can examine the worksheet for inconsistencies and errors or print the worksheet for documentation purposes. All formulas can be displayed by selecting Show Formulas from the Formulas tab of the Ribbon or from the keyboard by pressing CTRL + ` (the grave symbol), as shown in Figure 4.
Figure 4 – Displaying All Formulas in a Worksheet
Once someone attends technology training, they return repeatedly. That’s because they realize how little they really know about the products, like Excel, that they use daily. More importantly, they now see the potential for saving time and improving the final product that a better understanding of these technology tools provides.
