Category: Excel

Buried gold: Excel’s Form command

By David H. Ringstrom, CPA


Many users are not aware that Excel has long had a feature that enables you to browse through a table of data one record at a time. As shown in Figure 1, the Form command provides an easy way to navigate through a table of data. This command is easy to access in Excel 2003 and earlier versions, but is deeply buried in Excel 2007. In this article I’ll show you how to use the Form command to make quick work of reviewing a list of records.


Figure 1: The Form command allows you to navigate through a table of records.


Enable the Form command

It’s easy to use the Form command in Excel 2003 or earlier versions: Select a single cell within a table of data, choose Data, and then Form. While many Excel 2007 users will simply think that the Form command is no longer
available, you can easily uncover it:

  • Right-click anywhere on the Ribbon and choose Customize Quick Access Toolbar.
  • Choose Commands Not In the Ribbon, and then scroll down to Form, as shown in Figure 2, or press the letter F four times after you click on.
  • Click the Add button, and then click OK.

As shown in Figure 3, you can now click the Form button on the Quick Access toolbar to display the Form window shown in Figure 1.




Figure 2: You must manually add the Form command to your Quick Access toolbar in Excel 2007.




Figure 3: The Form command is now available on the Quick Access Toolbar.
As shown in Figure 1, the Form window has several buttons:


  • New: This button allows you to add a new record to the table. Complete the input fields, and then click New again to add a new record to the bottom of your list.
  • Delete: This button deletes the record from the list, and cannot be undone.
  • Restore: If you’ve made edits to a record within the Form window, the Restore button will undo your changes. Otherwise your changes are automatically saved when you move to a new record.
  • Find Prev: This allows you to navigate to the previous record in the list.
  • Find Next: This allows you to navigate to the next record on the list.
  • Criteria: You can filter your list to see just certain records, such as the Atlanta region. Click Criteria, add criteria to the corresponding fields, and then click Form to view only records that meet the specified criteria. To eliminate criteria, click the Criteria button, click Clear, and then Form.
  • Close: Closes the Form window.

Criteria trick: You can use wild cards, like *es to search just for fruit that ends in “es”, like apples, oranges, and mixed berries. You can also use question marks to mask selected characters, like ?o?th GA to view North GA and South GA at the same time, but exclude Mid GA.

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

About the author:

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

Managing Excel Add-Ins

By David H. Ringstrom, CPA


Add-ins are programs that plug-in into Microsoft Excel to add additional functionality. Some add-ins expand Excel’s core functionality, while others allow third-party products to add features to Excel’s environment. In this article I’ll discuss how to enable some of Excel’s hidden features, as well as give you some pointers on what to do when third-party add-ins vanish from Excel.

Hidden Features
Excel ships with several add-ins that you can manually enable. Some of these include:

It’s easy to enable these add-ins:

  • Excel 2007: Click the Office button, choose Excel Options, and then click Add-Ins. Choose Excel Add-Ins from the Manage section at the bottom of the Add-Ins window as shown in Figure 1, and then click Go. As shown in Figure 2, select any add-ins that you wish to include.

Figure 1: Use the Manage section to work with Add-ins in Excel 2007.

 

 

 

Figure 2: You’re just a few mouse clicks away from enabling several hidden Excel features.

 

 

 

  • Excel 2003 and earlier: Choose Tools, Add-Ins, and then select any add-ins you wish to enable, as shown in Figure 2.

In other cases, third-party applications may automatically install add-ins in Excel. For instance, Adobe Acrobat often adds a custom menu or toolbar in Excel. Some of my clients work in the financial industry and rely on add-ins from Bloomberg or Reuters that enable users to return prices and other data on financial products directly into an Excel spreadsheet. Most add-ins are well-behaved, and you may tend to take them for granted — until the day that you launch Excel and find that your add-in functionality has vanished. Much like a mother bear with cubs, Excel is very protective of its operating environment. If Excel determines — rightly or wrongly — that an add-in has caused an Excel crash, the add-in is benched and put on the disabled list. Excel will generally warn you in such cases, but in the case of a shared or public computer, someone else may choose to disable the add-in without you knowing. In such cases, the menu or toolbar that the add-in provides simply vanishes. Fortunately, it’s easy to re-enable the add-ins if you know where to look:

 

 

  • Excel 2007: Click the Office button, choose Excel Options, and then click Add-Ins. Choose Disabled Add-Ins from the Manage section at the bottom of the Add-Ins window (shown in Figure 1), and then click Go. As shown in Figure 3, any disabled add-ins will appear on the Disabled Items list. You can enable such items one at a time, and then click Close.

 

 

Figure 3: Use this window to re-enable disabled Excel add-ins.
 

 

  • Excel 2003 and earlier: Choose Help, and then About Microsoft Office Excel. Click the Disabled Items button shown in Figure 4 to display the Disabled Items window shown in Figure 3.

 

 

 

Figure 4: The Disabled Items button is fairly well hidden in Excel 2003 and earlier.

 

 

Excel 2007 tip: Although most add-ins have a .XLA extension, others are known as COM add-ins, and don’t appear in the Add-Ins list shown in Figure 2. Excel 2007 users can easily disable unwanted COM add-ins: Click the Office button, choose Excel Options, and then click Add-Ins. Choose COM Add-Ins from the Manage section at the bottom of the Add-Ins window as shown in Figure 1, and then click Go. As shown in Figure 5, you can then add or remove COM add-ins as desired. Users of earlier versions of Excel can use the free OfficeIns tool to manage add-ins.

 

 

 

Figure 5: Excel 2007 makes it easy to manage COM add-ins like Google DeskTop Office.

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

About the author:

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

 

Three Excel menus you may have overlooked

By David H. Ringstrom, CPA


Sheet Navigation

Everyone is familiar with using the arrow buttons in the lower left-hand corner of the Excel screen to push worksheet tabs back and forth across the screen. However, try right-clicking on these buttons instead, and you’ll see a menu similar to Figure 1. Simply click on a sheet name to instantly activate that worksheet.

 

 

Figure 1: Many Excel users don’t know that you can right-click on the navigation arrows.


Move Rows or Columns

Sometimes you need to rearrange columns or rows on your worksheet. Many users resort to inserting new, blank columns or rows, cutting the data to be moved, and then pasting it into the new location. Instead, there are two faster ways rearrange your worksheet:
 

  • Select the columns or rows you wish to move
  • Use your right mouse button to grab the border of your selection, and then move the columns or rows to the new location.
  • When you release your mouse, the menu shown in Figure 2 will appear. If you forget and use your left mouse button, you’ll encounter a different prompt. Press Escape and then try again.

Figure 2: This hidden menu appears when you use the right-mouse button to drag a selection of cells.
Of course if you’re just repositioning rows, you can bypass this menu entirely: simply hold down the Shift key while you move the columns or rows into their new position.


Hide and Unhide Worksheets

Most Excel 2007 users know that it’s possible to hide or unhide worksheets:
 

  • Choose the Format icon in the Cells section of the Home menu
  • Choose Hide & Unhide
  • Choose Hide Sheet or Unhide Sheet.

However, there’s an even easier way: As shown in Figure 3, right-click on a worksheet tab, and then choose Hide. You can also right-click on any worksheet tab and choose Unhide to display the Unhide Sheet dialog box. Although you can hide multiple worksheets at once — hold down the Ctrl key as you click on each worksheet, and then issue the Hide command — you must still unhide sheets one at a time. Anyone using Excel 2003 or earlier won’t have this option and instead must choose Format, Sheet, and Hide or Unhide. In addition, you’ll notice a new Protect Sheet command, which toggles to Unprotect Sheet when the worksheet is protected.

 

Figure 3: Excel 2007 has Hide, Unhide, and Protect Sheet commands on the worksheet menu.

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

About the author:

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

 

Free Microsoft Excel-based 1040 form available

By David H. Ringstrom, CPA



Just in time for tax season, Glenn Reeves of Burlington, Kansas has created a free Microsoft Excel-based version of the 2008 U.S. Individual Tax Return, commonly known as Form 1040. The spreadsheet includes both pages of Form 1040, as well as these supplemental schedules:

 

  • Schedule A – Itemized Deductions
  • Schedule B – Ordinary Interest and Ordinary Dividends
  • Schedule C – Profit or Loss from Business
  • Schedule D – Capital Gains and Losses, along with its worksheet
  • Schedule SE – Self-Employment Tax
  • Form 2441 – Childcare and Dependent Care Expenses
  • Form 6251 – Alternative Minimum Tax – Individuals

The spreadsheet also includes several worksheets:

  • Line 10 – State and Local Tax Refund Worksheet
  • Lines 16a and 16b – Simplified Method Worksheet taxable annuities and pension benefits
  • Lines 20a and 20b – Social Security Benefits Worksheet
  • Line 32 – IRA Deduction Worksheet
  • Line 42 – Deductions for Exemptions Worksheet
  • Line 44 – Qualified Dividends and Capital Gain Tax Worksheet
  • Line 52 – Child Tax Credit Worksheet
  • Lines 64a and 64b – Earned Income Credit (EIC)

Six additional worksheets round out the tool:

  • W-2 input forms that support up to 4 employers for each spouse
  • 1099-R Retirement input forms for up to 4 payers for each spouse
  • SSA-1099 input form to record Social Security Benefits
  • A tax table
  • Earned Income Credit table
  • Change log that records revisions to the spreadsheet

All of the worksheets are password-protected, and most of the underlying formulas are hidden, but you’re free to add new worksheets to the file, or create links to other workbooks. As you can see in Figure 1, the form exactly mirrors the official IRS format. The protection also means you can’t add comments to cells within the forms, or make notes out to the right.

 

 

Figure 1: Glenn Reeves created an Excel-based version of IRS Form 1040

The spreadsheet also includes some basic error-checking features, as shown in Figure 2, as well as hints, as shown in Figure 3.

 

 

Figure 2: The spreadsheet contains basic error-checking prompts.

 

 

Figure 3: In some cases the spreadsheet offers helpful tax-planning hints.

The spreadsheet’s author clearly states that the spreadsheet is available for free, but he does accept appreciation contributions. As you might expect for someone so intimately familiar with U.S. tax law, Mr. Reeves will report all contributions as income, but will also donate 10 percent of any proceeds to his church.

This free spreadsheet enables just about anyone to use Microsoft Excel to prepare and print their entire 1040 return. Along with the actual forms, the spreadsheet includes some IRS documentation, as well as links to download official IRS forms and instructions. Excel-based versions of Form 1040 are available for all years from 1996 through 2008. In addition, the 2008 format is also available in Open Document Format, which is compatible with the free Open Office suite.

The spreadsheet is available at www.excel1040.com.

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

About the author:

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

Excel text transformation tricks

By David H. Ringstrom, CPA


Data arrives on your desk from multiple sources, and in many cases it’s not quite in the format that you need. For instance, a user may have left the CAPS LOCK on while typing in a series of addresses. Or you’ve exported data from a program that pads fields with spaces at the beginning or end. Fortunately, there’s no need to retype the list. Excel offers four easy-to-use functions that can quickly transform data into just the format that you need.

UPPER – As shown in Figure 1, this function transforms text into all upper case, so the phrase Old McDonald’s Farm becomes OLD MCDONALD’S FARM. This function only has one argument, so you simply type =UPPER(x), where x is a cell that contains text you wish to convert.

LOWER – This function transforms text into all lower case letters, so Old McDonald’s Farm becomes old mcdonald’s farm. As with the UPPER function, you simply use the form =LOWER(x), where x is a cell that you wish to convert to lower case.

PROPER – This function capitalizes the first letter of each word, so the phrase old mcdonald’s farm becomes Old Mcdonald’S Farm. This function takes the same form as the other functions: =PROPER(x), where x is the cell to convert. However, as you can see in Figure 1, PROPER can present problems in some cases:

 

  • The first D in McDonald’s doesn’t get capitalized.
  • The S in McDonald’s is capitalized — in addition to capitalizing the first letter of a word, PROPER also capitalizes the next letter after an apostrophe or single quote.
  • Other items to look for are acronyms or initials, where ABC COMPANY would become Abc Company or BOB’S TV SERVICE would become Bob’S TV Service

You’ll have to manually clear up problems like ABC or TV being converted to Abc and Tv, but you can use Excel’s Replace function to clear up other problems, as I’ll explain in a moment.

TRIM – Sometimes you’ll inherit data that has extraneous spaces at the beginning, middle, or end of the string, such as ” Old McDonald’s Farm”. In such cases you can use the TRIM function to strip out all extraneous spaces — the function automatically keeps one space between each word. The function takes the form =TRIM(x) where x is a cell reference.

Figure 1: These four functions can quickly transform text without retyping.

Expert tip: Sometimes it’s helpful to have white space that the start of certain words, as shown in Figure 2. Rather than manually enter spaces at the start of cells A3 through A5, try this instead:

 

  1. Press Ctrl-1 to display the Format Cells dialog box.
  2. Click on the Alignment tab, and set the Indent field to 1 or 2.
  3. Click OK and your text will automatically indented, as shown in Figure 3.
  4. You can remove all of the indenting at any time by changing the Indent back to 0.

Figure 2: Use the Indent setting to move text to the right without using the spacebar.

Figure 3: Set the Indent level to 1 or 2 to create a slight indent.

 

Convert Formulas to Values

Typically when you use these functions you’ll have data in one column, such as column A in Figure 1. You’ll enter the formula in column B, and then copy it down the length of your list. Presto, your list is transformed, but don’t rush off and delete column A just yet. You must first copy the formulas in column B to the clipboard, and then use the Paste Special command to convert the formulas to values:

 

  1. Copy the formulas to the clipboard.
  2. Right-click on the first cell that you copied, and then choose Paste Special.
  3. As shown in Figure 4, you can double-click on the word Values, or click once on Values and then click OK.

At this point you can delete the data in column A, because your converted data is in column B as text rather than formulas that refer to column A. If you forget to convert the formulas in column B to values before you delete column A, then you’ll end up with a series of #REF! errors. Simply press Ctrl-Z to undo the column deletion, and then carry out the Paste Special Values command.

Figure 4: The Paste Special Values command converts formulas to text.

 

A PROPER Clean-up

Once you’ve converted your formulas to values, you can use the Replace function to resolve problems like that pesky ‘S in McDonald’S. To do so, press Ctrl-H to display the Replace dialog box. As shown in Figure 5, enter ‘S in the Find field, and ‘s in the Replace With field. Repeat this process for any other issues, like replacing Mcd with McD, Tv with TV, and so on.
Figure 5: Use the Replace command to quickly clean up capitalization problems that PROPER introduces.


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

About the author:

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

 

Excel 2007: Five ways to streamline Excel’s user interface

By David H. Ringstrom, CPA


It’s easy to get caught up in the day-to-day use of Excel 2007 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 2007 tricks. Most involve customizing the user interface so that I can speed through my work without hitting unnecessary bumps:

1. Extend your recently used file list
By default Excel shows the 17 most recent files that you’ve opened, but as shown in Figure 1, you can expand this to a maximum of 50:

 

  • a. Click the Office button, and then click Excel Options.
  • b. Choose Advanced, and then scroll down to the Display section.
  • c. Change the Show This Number of Recent Documents from 17 to a maximum of 50.

Figure 1: You can expand the number of recent documents that appear when click the Office button.

 

Expert tip: You can pin files permanently to the Recent Documents menu: Click the pushpin that appears to the right of the file name. Your document may shift further down the list over time, but will always remain on the Recent Documents menu.

2. Utilize the Places Bar

As shown in Figure 2, 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 desired folder, so that the folder name becomes highlighted. Be sure not to drill down inside the folder, but rather just click once on its name.
  • b. Right-click in the Places Bar and choose Add.

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 2: The Places Bar can give you once-click access to widely-scattered folders.

3. Disable the Paste/Insert Icons

You may have noticed the little clipboard icon, shown in Figure 3, 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 it’s easy to eliminate these prompts:

 

  • a. Click the Office button, and then choose Excel Options.
  • b. As shown in Figure 4, choose Advanced, and then clear the check boxes for Show Paste Options Buttons and Show Insert Options Buttons in the Cut, Copy, and Paste section.

Figure 3: The Paste Options button can be disabled.

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

4. Eliminate the Zoom Slider

As shown in Figure 5, the Zoom Slider appears in the lower right-hand corner of your Excel screen. It’s easy to accidentally hit this when you’re scrolling up and down or right and left. If you click the Zoom Slider, then suddenly your Excel spreadsheet may zoom in or out dramatically, leaving you to reset it again. The Zoom section of the View tab makes it easy to change the size of your text, so you may wish to turn the zoom slider off:

 

  • a. Right-click anywhere on the Status bar at the bottom of your screen.
  • b. Clear the checkbox for Zoom Slider.

Figure 5: Convenience aside, the Zoom Slider makes it easy to inadvertently resize your screen view.

5. Eliminate extraneous worksheets

By default Excel 2007 workbook includes three worksheets, but you often only need one or two tabs. Further, Excel 2007 includes a new Insert Worksheet tab, shown in Figure 6, which allows you to add a new worksheet with just one click. Thus it’s helpful to always start with a single worksheet and then add new sheets as you need them. Here’s how to change the default from 3 to 1:

 

  • a. Click the Office button, and then choose Excel Options.
  • b. As shown in Figure 7, change the Include This Many Sheets setting to 1.

Figure 6: The Insert Worksheet button makes it easy to add new sheets on the fly.

Figure 7: You can change the default number of worksheets from 3 to 1.

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

About the author:

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

 

Excel 2003: Six ways to streamline Excel’s user interface

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 .

About the author:

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

AccountingWEB Webinar: Creating error proof spreadsheets

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.

Excel 2007: Use these three techniques to turbo charge filtering

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.



 

Size Color Quantity
Small Blue 8,038
Medium Blue 4,245
XL Blue 5,241
XXL Blue 8,797
XXXL Blue 5,145
Small Orange 2,571
Medium Orange 5,595
XL Orange 8,825
XXL Orange 1,997
XXXL Orange 6,169
Small Green 6,192
Medium Green 3,380
XL Green 4,024
XXL Green 2,020
XXXL Green 8,295
Small Black 2,841
Medium Black 4,705
XL Black 4,202
XXL Black 4,846
XXXL Black 2,431

 

Table 1: Use this sample t-shirt sales data to explore Filtering in Excel 2007.

 

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 .

About the author:

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

 

Excel 2003: Use These Three Techniques to Turbocharge Filtering

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.

Size Color Quantity
Small Blue 8,038
Medium Blue 4,245
XL Blue 5,241
XXL Blue 8,797
XXXL Blue 5,145
Small Orange 2,571
Medium Orange 5,595
XL Orange 8,825
XXL Orange 1,997
XXXL Orange 6,169
Small Green 6,192
Medium Green 3,380
XL Green 4,024
XXL Green 2,020
XXXL Green 8,295
Small Black 2,841
Medium Black 4,705
XL Black 4,202
XXL Black 4,846
XXXL Black 2,431

Table 1: Use this sample t-shirt sales data to explore Filtering in Excel 2003.

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 .

About the author:

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

 

Excel Filtering 101

By David Ringstrom, CPA


Filtering is a powerful, but often overlooked feature in Excel that enables you to hide unneeded data within a list. In Excel 2003 and earlier, this feature is known as AutoFilter, while Excel 2007 refers to it as simply Filter. In this article I’ll provide a quick overview of how to use this helpful feature.

Filtering Basics

Filtering enables you to collapse a spreadsheet down to display only relevant rows.

1. Copy the data from Table 1 into a blank worksheet, starting at cell A1.


Size Color Quantity
Small Blue 8,038
Medium Blue 4,245
XL Blue 5,241
XXL Blue 8,797
XXXL Blue 5,145
Small Orange 2,571
Medium Orange 5,595
XL Orange 8,825
XXL Orange 1,997
XXXL Orange 6,169
Small Green 6,192
Medium Green 3,380
XL Green 4,024
XXL Green 2,020
XXXL Green 8,295
Small Black 2,841
Medium Black 4,705
XL Black 4,202
XXL Black 4,846
XXXL Black 2,431

Table 1: Use this sample t-shirt sales data to explore filtering.

2. Select cell A1, and then turn on the filtering feature:

Excel 2007: click Sort & Filter in the Editing section of the Home ribbon, and then choose Filter. You can also click the Filter button in the Sort & Filter section of the Data ribbon.

Excel 2003 and earlier versions: Choose Data, Filter, and then AutoFilter.

3. Drop-down arrows will appear in cells A1 through C1, as shown in Figure 1.

Expert technique: The drop-down lists display one of each item within that column. This is a helpful auditing tool that eliminates the need to scroll down through a spreadsheet in search of a specific item.




Figure 1: Filtering places an arrow in the first cell of each column within a list.

4. You can now use any of these techniques to filter the spreadsheet:

 

  • View Small T-Shirt Sales
    Excel 2007: Click the drop-down list in cell A1, choose Select All to clear the existing arrows, and then choose Small. Your list should look similar to Figure 2.
    Excel 2003: Click the drop-down list in cell A1, and then choose Small. Your list should look similar to Figure 2.

Figure 2: The sample data from Table 1, filtered to show only small t-shirt sales.

 

  • View Orange T-Shirt Sales
    Excel 2007: Click the drop-down list in cell A1, and then choose Clear Filter From Size. Next, click the drop-down list in cell B1, choose Select All, and then Orange. Your list should now look similar to Figure 3.
    Excel 2003: Click the drop-down list in cell in cell A1, and then choose (All). Next, click the drop-down list in cell B1, and then choose Orange. Your list should now look similar to Figure 3.



Figure 3: The sample data from Table 1, filtered to show on orange t-shirt sales.

 

  • Create a list of top sellers
    Excel 2007: Click Sort & Filter, and then Clear to eliminate any existing filters. Click the arrow in cell C1, choose Number Filters, and then Greater Than or Equal To. As shown in Figure 4, enter 5000, and then click OK. Your spreadsheet should now look like Figure 5.
    Excel 2003: Choose Data, Filter, and then Show All to eliminate any existing filters. Click the arrow in cell C1, and then choose (Custom…). As shown in Figure 4, choose Greater Than or Equal To, enter 5000, and then click OK. Your spreadsheet should now look like Figure 5.



Figure 4: Custom filters allow you to specify your own criteria.



Figure 5: The sample data from Table 1, filtered to show only t-shirts that sold at least 5,000 units.

Custom filtering: If you dig deeper into the custom filter choice you’ll find that you can craft some sophisticated filters, such as Begins With, Ends With, Contains, Does Not Contain, and so on.

New Filtering Capabilities in Excel 2007

Filtering underwent a significant revamp in Excel 2007. Excel 2003 (and earlier versions) only enable you to choose one item from a drop down list, unless you use custom filtering. Conversely, Excel 2007 allows you to choose as many items as you wish from the drop-down list. Excel 2007 allows you to filter by cell color or font color, as shown in Figure 5. Such filtering is not possible in earlier versions of Excel.



Figure 6: Excel 2007 allows you to filter a list based on cell color or font color.
Now that you’re up to speed on filtering, you may want to read a related article that shows how to make filtering even easier.



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

About the author:

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

 

Excel Chart Advisor Prototype Simplifies Graphs

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.
 

 

Chain Product Cases Sold Total Sales
Fruit R Us Oranges 6,168 61,680
Fruit R Us Apples 6,079 85,106
Fruit R Us Kiwi 6,058 66,638
Fruit R Us Bananas 6,868 75,548
Fruit R Us Mixed Berries 1,996 29,940
Bob’s
Fruit
Oranges 7,818 93,816
Bob’s Fruit Apples 1,565 21,910
Bob’s Fruit Kiwi 9,967 99,670
Bob’s Fruit Bananas 9,842 98,420
Bob’s Fruit Mixed Berries 8,993 89,930

 


Table 1: You can copy and paste this sample data into a blank worksheet for use with the Chart Advisor.

 

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 .

About the author:

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