Charts Tell the Real Story

Add pizzazz—and clarity—to a financial report.

Key to Instructions
To help readers follow the instructions in this article, we use two different typefaces.
Boldface type is used to identify the names of icons, agendas and URLs.
Sans serif type indicates commands and instructions that users should type into the computer and the names of files.

ne of the most critical tasks of accountants is translating those dry columns of financial numbers into information that management can quickly comprehend. One effective tool is the chart, which graphically demonstrates what the numbers mean. But, as good as charts are, the traditional ones have an inherent weakness. For example, if you want to show how spiraling marketing costs, spent to boost sales, will deplete profits, then the traditional chart probably lacks the punch you’re trying to achieve. In other words, if the sales numbers are large and growing fast while the profits are relatively small and shrinking, a conventional chart of their relationship will not illustrate your point.

The good news: That weakness is correctable. With a few adjustments, you can transform a lackluster chart into one that commands attention. Follow along and see how.

Consider the chart in exhibit 1 , at right, which compares soaring Sales (the blue line) with ebbing Operating income (the pink line). While it’s clear that sales are rising, that slightly dipping pink line certainly isn’t making the point that profits are collapsing.

Fortunately, Excel, which can easily convert tables into charts, has special ways to address such a situation. To illustrate we’ll create a chart with the underlying spreadsheet data shown in exhibit 2 , below. You may want to duplicate the chart and work along with me.

Once you’ve created the worksheet, hold down the Ctrl key while highlighting the header (row 4), Sales (row 5) and Operating income (row 9). When highlighted, they are shaded gray.

Click on Insert on the toolbar, then Chart to bring up the Chart Wizard ( exhibit 3 , below). Select the Custom Types tab and scroll down to Lines on 2 Axes .

Click twice on Next to get to Step 3 and then click on the Titles tab. Enter Snick’s Snack Sales as the Chart title . Enter Sales in the Value (Y) axis field and Operating income in the Second value (Y) axis field ( exhibit 4 , below).

The default placement of the legend is to the right of the chart. To move it to another location in the chart, click on the Legend tab, click on another selection and wait a few seconds for the legend to appear. You can test each placement until you are satisfied with the results ( exhibit 5 , below right). Now press Next .

To place the finished chart on the same worksheet as the data, simply click on Finish . If you wish to place it on a separate sheet, enter a new worksheet name, such as 2003 Chart , and then click on Finish ( exhibit 6 , below).

If you want to change its location after you have placed the chart on the data sheet, right-click inside the chart once and then click on Location from the menu. This will bring up the Chart Location window. But if you originally had placed the chart in its own worksheet and now you want to move it, you don’t have to click on the chart; simply go to the menu bar at the top and select Chart and then Location to get the Chart Location window.

To change a line color, right-click on it and choose Format Data Series ( exhibit 7 , above). Click on the Patterns tab and change the line color from Automatic and the Foreground marker color to the desired colors. Increase the thickness ( Weight ) of the line by clicking on the down arrow and by selecting a heavier line. Then click on OK .

You also can add data points for emphasis along the chart lines ( exhibit 8 , at right). To do that right-click on the Sales (blue) line and choose Format Data Series . Select the Data Labels tab and check the Value box.


If necessary, you can reposition the data labels by right-clicking on one of them. Then select Format Data Labels and click on the Alignment tab and change the label position.

To enhance the emphasis, you also can change the scale on one or both Y-axes. Compare the difference between exhibit 8 and exhibit 9 , at right. To do that, right-click on the primary (left vertical) Y-axis and select Format Axis from the shortcut menu. Choose the Scale tab and change Minimum from the default of 0 to 20,000 .

As you can see, you can transform numbers into effective, easy-to-understand pictures. So get familiar with Excel’s chart functions to send the message you want.

TECH Conference
May 2–5, 2004
Venetian, Las Vegas

TERRYANN GLANDON, CPA, PhD, is an assistant professor of accounting at the University of Texas at El Paso. Her e-mail address is .


How to make the most of a negotiation

Negotiators are made, not born. In this sponsored report, we cover strategies and tactics to help you head into 2017 ready to take on business deals, salary discussions and more.


Will the Affordable Care Act be repealed?

The results of the 2016 presidential election are likely to have a big impact on federal tax policy in the coming years. Eddie Adkins, CPA, a partner in the Washington National Tax Office at Grant Thornton, discusses what parts of the ACA might survive the repeal of most of the law.


News quiz: Scam email plagues tax professionals—again

Even as the IRS reported on success in reducing tax return identity theft in the 2016 season, the Service also warned tax professionals about yet another email phishing scam. See how much you know about recent news with this short quiz.