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.

Before and after image of the population projection line chart.
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.

Default line chart showing historic and projected population counts from 2010 to 2030 for children age 0 to 4 in the United States.
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.

Line chart showing historic and projected population counts from 2010 to 2030 for children age 0 to 4 in the United States, with a gap between where the historic data ends and the projected data begins.

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.

Line chart showing historic and projected population counts from 2010 to 2030 for children age 0 to 4 in the United States.
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
Line chart showing historic and projected population counts from 2010 to 2030 for children age 0 to 4 in the United States, now with stylistic changes to clearly distinguish historic data (solid blue line) from projected data (dashed gray line).

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.