Create Excel charts that show data comparisons at a glance

by Mary Ann Richardson | May 15, 2007 7:00:00 AM

Tags: Microsoft Office, Mary Ann Richardson, Microsoft Excel, Microsoft Office Suite Excel Tips Newsletter

Takeaway: If you or your users need data from certain time periods, you can create an Excel chart that makes the specified dates or times contiguous on a stacked column. Here's how to make an easy graphic visualization of information from what may be scattered times.

An Excel chart's data order can make a big difference on the chart's impact, as well as make it easier to read.

Here's an example: Your company is thinking about expanding the service end of its business and selling fewer products. When you create an Excel chart of monthly sales of products and services since Q1 of 2006, it shows an increase in service sales in Q1 2007 over last year. You could chart the data as entered over time -- January 2006 through March 2007 -- but your audience would have to pick out the first quarter months for 2006 and relate each one with its corresponding month for 2007. An alternative is to rearrange the data so that when you chart it, the corresponding months are contiguous. Follow these steps to arrange the information in this way:

1. Open a blank worksheet.
2. Enter Jan-06 in A2.
3. Enter Jan-07 in A3.
4. Enter Feb-06 in A5.
5. Enter Feb-07 in A6.
6. Enter Mar-06 in A8.
7. Enter Mar 07 in A9.
8. Enter Service Sales in B1.
9. Enter Product Sales in C1.
10. Enter the corresponding data in B2:C9.
11. Select A1:C9.
12. Click the Chart Wizard button.
13. Select Stacked Column and then click Next.
14. Complete the Chart Wizard and locate your chart on the worksheet.
15. Right-click any of the columns on the chart.
16. Select Format Data Series.
17. Click the Options tab and enter 0 in the Gap Width text box.
18. Click OK.

Miss a tip?

Check out the Microsoft Excel archive, and catch up on our most recent Excel tips.

Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

Print/View all Posts Comments on this article

It didn't work like I thought for Excel 2003 jennyh1234 | 05/16/07

does not work phansalker.g@... | 05/16/07

I got it to work. Here's how... crawk | 05/16/07

Changing Date to Text Worked! jennyh1234 | 05/16/07

RE: Create Excel charts that show data comparisons at a glance umesh_devs@... | 06/19/08