Category: AccountingWEB articles

By David H. Ringstrom, CPA

It's easy to get caught up in the day-to-day use of Excel 2003 and overlook minor tweaks that can make a significant difference in your use of Excel. In this article I'll review five of my favorite Excel 2003 tricks. Most involve customizing the user interface so that I can speed through my work without hitting unnecessary bumps or screen prompts. These tricks will work equally well in Excel 2002, but only some apply to Excel 97 and 2000:

1. Extend your recently used file list

By default Excel shows the four most recent files that you've opened, but you can expand this list to as many as 9:

• a. Choose Tools, and then Options. 
• b. As shown in Figure 1, click the General tab, and then set the Recently Used File List to a maximum of 9.

Figure 1: Expand your frequently used file list to 9.

 
2. Display the entire menu

By default Excel hides commands that you haven't used recently. The full menu appears after a second or two, but most users prefer to see the same menu every time:

• a. Choose Tools, Customize, and then click on the Options tab. 
• b. Click Always Show Full Menus, and then Click Close.

Figure 2: Set Excel to always give you the same menu every time.

 
3. Disable the Clipboard Task Pane

Excel has always had a strange relationship with the Windows clipboard. Just about every other program keeps items on the clipboard until you cut or copy something else there. Conversely, Excel tends to clear the clipboard as soon as you carry out an action that doesn't involve navigating or pasting. Excel 2003 tried to compensate for this by displaying recently copied items in the Clipboard Task Pane, shown in Figure 3. If you find this annoying, you can easily disable it:

• a. Click the Options button at the bottom of the Clipboard task pane. 
• b. Clear all of the checkboxes, and then close the Clipboard task pane. 
• c. If you change your mind later, choose Edit, and then Office Clipboard to restore the Clipboard task pane.

Figure 3: The Clipboard task pane is intended to compensate for Excel's odd relationship with the clipboard.

 
4. Minimize the Reviewing Toolbar

Even if you turn it off, this toolbar reappears whenever you open an e-mail attachment. You could right-click on it and turn it off, but a better practice is to tuck it way so that only one icon appears, as shown in Figure 4. Simply grab the left-hand edge of the toolbar, and drag it to the right edge of your screen so that only one icon is visible.

Figure 4: Don't let the Reviewing Toolbar take up valuable screen space.

 
5. Disable the Paste/Insert Icons

You may have noticed the little clipboard icon that appears when you paste data into a worksheet. This little icon makes a Paste Options menu available, from which you can make changes to how your data pasted on the worksheet. Some folks may find this helpful, but many find it annoying. Fortunately you can easily disable the Paste Options feature:

• a. Choose Tools, and then Options. 
• b. Choose the Edit tab, and then clear the check boxes for Show Paste Options Buttons and Show Insert Options Buttons.

Figure 5: The Paste Options button can be disabled.

 
Figure 6: Clear the checkboxes shown to disable the Paste and Insert Options buttons.

 
6. Utilize the Places Bar

As shown in Figure 7, the Places Bar is the list of icons on the left-hand side of the Office dialog boxes. You can place shortcuts to frequently used folders here:

• a. Click once on the folder, and then choose Tools, and then Add to My Places, as shown in Figure 7. 
• b. You can right-click on an item in the Places bar and move it up or down one position at a time. This menu also allows you to remove items you've added to the Places Bar, but you cannot remove the default locations like My Recent Documents, My Documents, and so on.

Figure 7: The Places Bar can give you once-click access to widely-scattered folders.

 
A previous version of this article first appeared on www.accountingweb.com .

David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com  or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link

In today's fast-paced business world, spreadsheet users don't always have the luxury of double-checking every element of their work, particularly in complex spreadsheets. Fortunately there are some simple practices you can adopt to help minimize the risk of errors in your spreadsheets. You can also learn how to track down errors in spreadsheets built by others. Take an hour tomorrow and get up to speed during our live Creating Error Proof Spreadsheets Webinar. AccountingWEB staff writer David Ringstrom, CPA, will share his best tips and secrets for improving the integrity of your spreadsheets.

Click here to read the full text of the article at AccountingWEB.com.

By David Ringstrom, CPA

Did you know Excel 2007 has a hidden command that enables you to filter a list with just one mouse click? And that there are two ways to sum just the visible rows in your filtered list? Read on to discover these time saving techniques.

Not sure how to filter? Read Excel Filtering 101.

Filter versus Autofilter

As you're probably aware, the Filter command appears on both the Home and Data tabs of the Excel 2007 ribbon. However, going this route to filter a list usually means multiple mouse clicks. Instead, I recommend that you add the AutoFilter command to your Quick Access Toolbar:

1. Right-click anywhere on the Excel 2007 ribbon and, then choose Customize Quick Access Toolbar.
2. As shown in Figure 1, choose Commands Not In the Ribbon, and then add AutoFilter to your Quick Access toolbar. Close the dialog box shown in Figure 1.

Figure 1: AutoFilter is one of many secret commands that you can add to your Quick Access Toolbar. (Click on image to expand.)

New to the QAT? Learn more about the Quick Access Toolbar via the Microsoft Professional Accountant's Network.

Although it's similar in name to the Filter command, AutoFilter has a special capability: it instantly filters your list based on the contents of the currently selected cell:

1. Copy and paste the sample data from Table 1 into a blank worksheet.
2. Click on cell B14 — which contains the word green — and then click the AutoFilter button that you added to your Quick Access toolbar. As shown in Figure 2, your list should now show only green T-shirt sales.

Caveat: AutoFilter only allows you to filter for a single value, such as the word green, so you'll still need to use the filter drop-down lists for more complex filtering.

Figure 2: Use AutoFilter to create a summary with just two mouse clicks.

Important: Although AutoFilter turns on the arrows, you must still choose Sort & Filter, and then Filter to turn off the filtering arrows.

Analyze Visible Rows

Now let's look at two ways to analyze data as you filter it. First, many users overlook the fact that Excel 2007 automatically displays the total of selected cells in the status bar at the bottom of the screen, as shown in Figure 3. If you don't see this total, right-click on the status bar and enable the Sum option. Excel usually also displays these statistics, too: Average, Count, Numerical Count, Minimum, and Maximum.

Figure 3: The status bar generally gives you an instant total for the selected cells. (Click on image to expand.)

You can also accomplish this by using the SUBTOTAL function. To do so, enter this formula in cell C23:

=SUBTOTAL(9,C1:C21)

The 9 instructs SUBTOTAL that we want to sum. As shown in Figure 4, Excel automatically displays the additional options as you type the formula. SUBTOTAL is similar to SUM, but has a special capability of only tallying visible rows.

Figure 4: The SUBTOTAL function has several capabilities.

For comparison, enter this formula in cell C24:

=SUM(C1:C21)

Now, filter the list for Orange T-shirts. As shown in Figure 5, SUBTOTAL should return 25,157, while SUM always returns 99,559, regardless of how the list is filtered. SUM always tallies all rows, regardless of their hidden status. SUBTOTAL has other interesting capabilities, which I'll explore soon in a separate article.

Figure 5: SUBTOTAL tallies just the visible rows, while SUM tallies all rows.

A previous version of this article first appeared on www.accountingweb.com .

David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com  or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link

By David Ringstrom, CPA

Did you know that you can streamline the AutoFilter feature in Excel 2003 and earlier versions? And that there are two ways to sum only the visible rows in your filtered list? Read on to discover these time saving techniques.

Not sure how to filter? Read Excel Filtering 101.

AutoFilter's Hidden Powers

Most users choose Data, Filter, and then AutoFilter to enable filtering in Excel. However, you can give yourself one-click access to the AutoFilter command:

1. Choose Tools, Customize, and then click on the Commands tab.
2. Choose Data from the Categories list, and then drag AutoFilter onto one of your toolbars, or to the right of the Help command.
3. Drag the Show All command and place it to the right of the AutoFilter icon that you just dragged.
4. Click Close to dismiss the Customize dialog box.

Figure 1: AutoFilter takes on special powers when you add it to a toolbar or your Excel menu.

You can now instantly filter a list based on the contents of the currently selected cell:

1. Copy and paste the sample data from Table 1 into a blank worksheet, starting in Cell A1.
2. Click on cell B14 — which contains the word green — and then click the AutoFilter button that you added above. As shown in Figure 2, your list should now show only green T-shirt sales.
3. You can click the Show All command to keep the filtering arrows in place, but display all rows.

Caveat: AutoFilter only allows you to filter for a single value, such as the word green, so you'll still need to use the filter drop-down lists for more complex filtering. However, you can filter on one column at a time, such as green in column B, and then Medium in column A.

Figure 2: Use AutoFilter to create a summary with just two mouse clicks.

Important: Although the toolbar-based AutoFilter button turns on the arrows, you must still choose Data, Filter, and then AutoFilter to turn off the arrows.

Analyze Visible Rows

Now let's look at two ways to analyze data as you filter it. First, many users overlook the fact that Excel 2003 automatically displays the total of selected cells in the status bar at the bottom of the screen, as shown in Figure 3. If you don't see this total, right-click on the status bar and enable the Sum option. Alternatively you can choose to display another statistic instead: Average, Count, Numerical Count, Minimum, and Maximum.

Figure 3: The status bar generally gives you an instant total for the selected cells.

You can also accomplish this by using the SUBTOTAL function. To do so, enter this formula in cell C23:

=SUBTOTAL(9,C1:C21)

The 9 instructs SUBTOTAL that we want to sum. Consult Excel's online help file for the other numeric codes, although I will mention that 1 will give you an average, while 4 gives you the maximum, or largest value. SUBTOTAL is similar to SUM, but has a special capability of only tallying visible rows.

For comparison, enter this formula in cell C24:

=SUM(C1:C21)

Figure 4: The filtered list.

Now, filter the list for Orange T-shirts. As shown in Figure 4, SUBTOTAL should return 25,157, while SUM always returns 99,559, regardless of how the list is filtered. SUM always tallies all rows, regardless of their hidden status. SUBTOTAL has other interesting capabilities, which I'll explore soon in a separate article.

 
A previous version of this article first appeared on www.accountingweb.com .

David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com  or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link

By David Ringstrom

Excel 2007 users have a new tool in their arsenal: the Chart Advisor from the Microsoft Office Labs — a new proving ground for potential new Office features. Although this tool is a prototype, you may find it helpful in your daily work. This add-in for Excel utilizes an advanced rules engine to analyze your data, and then rank chart suggestions based on their respective score. Remember, this tool only works with U.S. English version of Excel 2007.

Free trial available: You can download a free 60 day trial of Office 2007.

Before you rush off and download the Chart Advisor, do note the caveats listed in Figure 1. You cannot install Chart Advisor without agreeing to these conditions. Also, some early versions of the Chart Advisor caused Excel to hang or crash, but those problems were quickly rectified by the Office Labs team. The tool worked without issue during my testing.

Figure 1: Read the terms of the Chart Advisor closely before proceeding with the installation.

Once you've downloaded the tool and accepted the various conditions and agreements, you'll notice a new Office Labs section within the Insert tab of the ribbon, as shown in Figure 2. The next step is to put some sample data in a blank worksheet, such as Table 1.







Figure 2: A new Chart Advisor icon will appear in the Insert tab of the Excel 2007 ribbon.
 

Once you've done so, click on a single cell within your table of sample data, and then click the Chart Advisor icon shown in Figure 2. The prompt shown in Figure 3 will appear briefly, and then you'll see the chart advisor itself in Figure 4. As you can see, each chart is ranked based on a score that appears in the lower right-hand corner. Hover over each chart to see a larger version.

Figure 3: The Chart Advisor is a proof of concept tool that may later become a formal part of Excel.

Figure 4: The Chart Advisor makes up to five recommendations based upon your data.

Once you select a chart, you can use the Modify section of the Chart Advisor to add or remove fields. As you can see in Figure 5, the tool informs you if you inadvertently remove a critical field from your chart. You can also use the Filter section, shown in Figure 6, to limit the data shown in your chart. Click the Insert Chart button when you're happy with your chart.

Expert tip: Right-click on the chart and choose Move Chart to relocate the chart to another worksheet, or its own chart sheet.

Figure 5: The Chart Advisor helps you head off potential mistakes.

Figure 6: You can limit the data shown in your chart without having to copy a subset to a new location.

A previous version of this article first appeared on www.accountingweb.com .

David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com  or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link