Recently, a colleague asked me how to visualize projections in Excel. Their boss needed to present information to a very fancy office. You know, the one that literally oversees the country and has an election for office every 4 years. So they needed to be accurate, informative, and impressive. No pressure.
The hardest part of a projection graph is, well, the actual projections. If you already have that data, like this group did, then making an informative graph is simple with just a few tweaks to the Excel defaults. I’ll walk you through each step below.
The example used here includes historic counts and population projections for children age 0 to 4 in the United States, according to OECD.
Here is what the before and after charts look like.
Step 1: Insert a Default Excel Line Chart
Highlight your data and insert the default Excel line chart. Here’s what the default for a line graph looks like.
Step 2: Trick the Default Settings
But you are too fancy and experienced for the default settings. You want to dazzle your partners with a graph that clearly delineates the projections. The trick is creating two columns of data. One with your actual data and a second with the projected data. You’ll notice that when you do this, you are left with a gap between the year that the actual data ended and when the projected data starts.
If you would like to connect the lines, you need to go one step further and add the data from the last date in the actual (historic) data column into the projected column. In this example, we copy the 19.3 million for 2020 from the historic counts column to the projections column. See the orange highlighted text and the orange highlighted line in the graph.
Step 3: Style Your Chart
Now we can change the line style for the projected data to be a dotted line (or whatever style you prefer). The key is to make the projected data look different from the historic data, through color or a pattern or both, to delineate a change in information.
From here, it’s all about styling your chart to meet your organization’s branding guidelines and stylistic preferences. Some of the tweaks I made were:
- Used color intentionally to distinguish between historic counts and population projections
- Removed the legend
- Added an informative title
- Added direct labels and markers to high and low values
- Increased the font size
- Simplified the x-axis to show every other year
Since the data shows large numbers, consistently in the millions, you might also consider going a step further and presenting the data in millions instead (e.g., 20.2 mil instead of 20,189.578). This helps with speed reading when people are quickly scanning your chart.
Now it’s your turn! Download the data file and see if you can recreate the chart for yourself.