Software

Use stacked Excel charts to compare data over time

When you need to present two or more Excel charts of similar items, rather than present them separately, stack them together so that they are easier to compare. Follow these steps to create a stacked chart that compares information over time.

When you need to present two or more Excel charts of similar items, rather than present them separately, stack them together so that they are easier to compare. For example, you're tracking customer service calls for two of your products in a separate spreadsheet. Follow these steps to create a stacked chart that compares the number of calls pertaining to each product over the last five days:

  1. Enter Day 1 in A2.
  2. Enter Day 2 in A3.
  3. Enter Day 3 in A4.
  4. Enter Day 4 in A5.
  5. Enter Day 5 in A6.
  6. Enter Product 1 Calls in B1.
  7. Enter 100 in B2.
  8. Enter 120 in B3.
  9. Enter 80 in B4.
  10. Enter 76 in B5.
  11. Enter 70 in B6.
  12. Enter Product 2 Calls in C1.
  13. Enter 30 in C2.
  14. Enter 40 in C3.
  15. Enter 80 in C4.
  16. Enter 78 in C5.
  17. Enter 86 in C6.
  18. Select A1:B6.
  19. Click the Chart Wizard button.
  20. Under Chart types, click Line.
  21. Click Next twice.
  22. Under the Titles tab, delete the title from the Chart Title box.
  23. Click the Axis tab.
  24. Clear the Category (X) axis check box.
  25. Click the Legend tab.
  26. Click Left under Placement.
  27. Click Next and then click Finish.
  28. Right-click the Chart Area.
  29. Select Format Chart Area.
  30. Click the Patterns tab.
  31. Under Border, click None. Click Dark Grey under Area.
  32. Click OK.
  33. Select A1:A6.
  34. Press [Ctrl] and select C1:C6.
  35. Click the Chart Wizard button.
  36. Under Chart Types, click Line.
  37. Click Next twice.
  38. Click the Titles tab.
  39. Delete the Title in the Chart Title box.
  40. Click the Legend tab.
  41. Click Left under Placement.
  42. Click Next and then click Finish.
  43. Right-click the Chart Area.
  44. Select Format Chart Area.
  45. Click the Patterns tab.
  46. Under Border, click None. Click Dark Grey under Area.
  47. Right-click the Y-Value Axis of Product 2 Calls.
  48. Click Format Axis.
  49. Click the Scale tab and set the Maximum value to 140.
  50. Click OK.
  51. Resize and reposition the Product 2 Calls chart so that the top of the chart is directly below and slightly overlaps the bottom of the Product 1 Calls chart.
  52. Right-click the Standard toolbar and select Drawing.
  53. Click the Product 1 Calls chart.
  54. Press [Ctrl] and then click the Product 2 Calls chart.
  55. Click the Draw Button in the Drawing Toolbar and then point to Align Or Distribute.
  56. Click Align Left.

By having only one X-Value axis for both charts, you can see at a glance how one product compares with the other.

Miss an Excel 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.

8 comments
qualityworks
qualityworks

This is bizarrely complicated method for comparing two data points per day.

Arcturus16a
Arcturus16a

Or even just a graphic showing what the chart should look like?

satyajit4
satyajit4

Thank you very much. My requirement was different; but going through this example gave me many clues using which I could complete the graph as per my requirement.

AtCollege
AtCollege

I can compare the two data series easily by putting them in one chart. Make the maximum Y axis value 140, each line is a different color. But maybe I am missing the point.

SheaJeff
SheaJeff

I too, was looking for a screenshot or sample file. I didn't even bother to read the text! Waaaay to much text to try before I can see results

Webguy007
Webguy007

A screenshot of the final product would be helpful, too.

Editor's Picks