Category: AccountingWEB Excel articles

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 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