A stacked column chart can show part-to-whole comparisons over time, or across categories. In a stacked column chart, data series are displayed as vertical columns, stacked one on top of the other. In this worksheet, we have quarterly sales in 4 regions: East, West, North, and South. To start off, I’ll place the cursor in any cell, click the column icon next to Recommended Charts, and select Stacked column. The result is a column chart, where each column is built from quarterly sales in each region. In this configuration, the chart is a good visual representation of the data in the table. At a glance, we can see the West region has the highest sales and the South region has the loWest. The breakdown of sales by quarter is harder to interpret. In the West, we can see Q4 is the strongest quarter and Q1 is the weakest. But comparing across regions are more difficult. Now if I use the Switch Row/Column button, we get regional sales grouped by quarter. Now we can see sales increased in all 4 quarters. We can also see that the South has a lower contribution overall. But in general it’s hard to compare the size of bars that make up each column. We might make things a little easier to read by adding data series lines. You won’t find these in the chart elements menu, so instead use the menu on the design tab. Once we have data series lines, I’ll want to remove gridlines to reduce visual clutter. Now comparisons are a little easier. Sales in the South seem to be decreasing while the East seems flat, and the West shows growth. The North is harder to interpret. Since we have plenty of room in this chart, we might be able to improve things by adding data labels. I’ll set the maximum on the vertical axis to 200,0000 to free up a little vertical space. To make the labels easier to read I’ll make the text white. I’ll bump up the size of all text at once, and then use the home tab to change the text to white, one series at a time. Next I’ll apply a more subdued color scheme that works well with the white text. To tidy things up I’ll delete the series lines, beef up the columns, and add a title. Finally, since the labels are providing more detail than we really need, and increasing visual clutter, I’ll apply a custom number format to show sales in thousands.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
