Like line charts, area charts are a good way to show trends over time. However, when plotting multiple data series, you must pay attention to the order in which the data series are plotted. Here we have some US census population data for several states. Let’s plot this data in an area chart. To start off, I’ll just select data for Arizona, and create a new area chart. Now, if I try Recommended Charts, we’ll see an area option that looks good. But notice it’s actually a stacked area chart, not a basic area chart. I’ll come back to this in a minute, but for now, to avoid confusion, I’ll switch to All Charts and select a basic area chart. The resulting chart clearly shows Arizona’s population has grown rapidly. Next I’ll add data for Florida. Now you can see why plot order matters. When plotted second, Florida completely obscures Arizona. If I add more states…we see Iowa, since Iowa plots after Florida. But California blocks out everything else. One way to fix this is to rearrange the plot order with Select Data. If I plot the data from largest to smallest, we can see all of the series in the chart. Notice however when you customize plot order, Excel no longer highlights source data. Now another way to fix things is to sort the source data first, and then make the chart. If I convert the data to a Table first, it will be easier to sort. This gives us a readable area chart on the first try, and it also preserves the source data highlighting feature. To simplify the vertical axis, I’ll set display units to millions. Now let’s look again at the stacked area chart option. I’ll duplicate this chart, then switch the second chart to stacked area. Notice data series are plotted in the same order, starting with California, and ending with Arizona. However, in the second chart, data series are stacked, one on top of the other. While stacking has a key benefit of keeping all series visible all the time, it has consequences. First, we can no longer easily read populations for each state. For example, in the basic area chart, we can see that Florida was about 5 million in 1960, and over 15 million by 2010. In the stacked area chart, this is hard to see. Second, a stacked area chart implies a part-to-whole relationship. In other words, showing the population of each state as a proportion of the four state total, suggests a relationship that may not have any meaning. The bottom line is a stacked area chart only makes sense when you want to show a part-to-whole relationship.
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.
