How to Make a Population Graph in Excel

Creating a population pyramid is a powerful way to visualize the age and gender distribution of a specific group, and Excel has all the tools you need to build one. It might look complex, but with a simple data formatting trick, you can quickly turn your demographic data into a clear and insightful chart. In this tutorial, we'll walk you through the entire process, from setting up your data to customizing your graph for a professional finish.

What is a Population Pyramid, Anyway?

A population pyramid, also known as an age-sex graph, is a pair of back-to-back horizontal bar charts, with one side representing males and the other representing females. The vertical axis shows age groups (or cohorts), typically in five-year increments, while the horizontal axis displays the population in numbers or percentages.

These charts are incredibly useful for social scientists, demographers, marketers, and public health officials because they provide a quick snapshot of a population's structure. You can instantly see things like:

• Whether a population is growing, shrinking, or staying stable.

• The ratio of young dependents to the working-age population.

• Potential future challenges, such as a large aging population needing more healthcare or a "youth bulge" requiring more jobs and education.

The shape tells the story. A wide base indicates a young, growing population, while a narrow base suggests an aging population with low birth rates.

Step 1: Get Your Data Ready for Excel

The single most important step in creating a population graph in Excel is structuring your data correctly. The chart won't work if your data isn't set up properly. You'll need three columns:

1. Age Group: Your categories (e.g., 0-4, 5-9, 10-14).

2. Male Population: The count for males in each age group.

3. Female Population: The count for females in each age group.

Here’s the trick: To make the bars for one gender extend to the left of the center axis, you need to make its values negative. It doesn’t matter which gender you choose, but for consistency, we’ll make the male population negative in this example.

So, your data in Excel should look something like this:

(Example Data for a Fictional City)

If you already have your male population data as positive numbers, you can quickly convert them. In a blank column next to your male data, enter a formula like =B2*-1 (assuming B2 is your first data point) and drag the formula down. Then, copy this new column and use Paste Special > Values to replace the original positive numbers.

Step 2: Create the Bar Chart

Once your data is formatted, you can create the basic chart.

1. Select Your Data

Highlight the entire data range, including the headers (Age Group, Male, Female).

2. Insert a Stacked Bar Chart

Navigate to the Insert tab on the Ribbon. In the Charts section, click the Insert Column or Bar Chart icon. From the dropdown menu, select the 2-D Stacked Bar chart. You'll immediately see a chart that is starting to look like a pyramid, but it needs some serious adjustments.

Step 3: Format the Chart Axes

Now it's time to fix the layout. We need to move the age groups to the center and format the horizontal axis to show proper population numbers without the confusing negative signs.

1. Move the Age Group Axis to the Left

Right-click the vertical axis (the one with the Age Groups). Click Format Axis. This will open a formatting pane on the right side of your screen.

Under Axis Options, scroll down and check the box that says "Categories in reverse order." This will flip your chart so the youngest age group (0-4) is at the bottom.

Now, find the Labels section. Click it to expand the options. Under Label Position, select "Low" from the dropdown menu. This moves the age group labels out of the middle and places them neatly on the left side of your chart.

2. Clean Up the Horizontal Population Axis

The horizontal axis is showing negative numbers on the left, which isn’t how a population pyramid should be read. We can hide the minus signs with a clever custom number format.

Right-click the horizontal axis (the numbers at the bottom of the chart). Click Format Axis.

In the formatting pane, scroll down to the Number section and expand it.

In the Format Code box, type the following code exactly as shown: #,##0,#,##0 This tells Excel to use the same format (a number with a thousand separator) for both positive numbers and negative numbers, effectively hiding the minus sign.

Click the Add button to apply the format. Your axis should instantly update to show positive population numbers on both sides.

Step 4: Refine Your Chart's Appearance

Your chart now has the correct structure of a population pyramid, but we can make it look much cleaner and more professional with a few final tweaks.

1. Tighten the Gaps Between Bars

To give your chart that classic pyramid look, you want to reduce the empty space between the age bars.

Right-click on any of the data bars (either male or female). Select Format Data Series.

In the formatting pane, under Series Options, you'll see two sliders: Series Overlap and Gap Width.

• Keep the Series Overlap at 100%.

• Decrease the Gap Width. A value between 0% and 15% usually looks best. Changing this for one series (e.g., Female) will automatically apply it to the other (Male).

2. Add a Title and Customize Colors

All graphs need a clear title and legend. You can also change the colors to make the data pop.

• Title: Click on "Chart Title" at the top of your chart and give it a descriptive name, like "Population Distribution of Example City, 2023."

• Legend: The legend should already be there, but you can click on it to move it (to the top, bottom, etc.) or format the text.

• Colors: Right-click on a data series (like all the blue female bars) and select Fill to choose a new color. Repeat for the other series. Traditional pyramids often use blue for males and red or pink for females, but feel free to use colors that match your presentation's theme.

Interpreting Your Population Pyramid

Now that you've built your graph, what does it mean? The overall shape tells you a story about the population's past, present, and future.

• Expansive Pyramid (Classic Triangle): Has a wide base and narrow top. This indicates a high birth rate and a young population. It suggests rapid population growth is likely.

• Constrictive Pyramid (Narrow Base): Tighter at the bottom than in the middle. This indicates a low birth rate and an aging population. It suggests the population may be shrinking or will soon start in the future.

• Stationary Pyramid (Boxy or Dome-Shaped): The shape is more rectangular and consistent through the age groups, tapering only at the top. This indicates low and stable birth and death rates, typical of a population with slow or zero growth.

Final Thoughts

Creating a population pyramid in Excel is a fantastic analytical skill. Once you master the trick of converting one set of data to negative values and fixing the axis numbers with a custom format, you can produce a clear and insightful chart in just a few minutes, turning a simple table of data into a powerful demographic story.

While Excel is great for these one-off visualizations, building comprehensive reports by pulling sales and marketing data from different platforms can quickly become a tedious chore. Connecting your Google Analytics, Shopify, Facebook Ads, and Salesforce accounts and trying to stitch the data together in spreadsheets is often a manual, time-consuming process. We built Graphed because we believe getting answers from your data shouldn't be that hard. You can connect your marketing and sales sources in seconds and use simple, natural language prompts to instantly build the real-time dashboards and reports you need, helping your whole team make better, faster decisions without the data drudgery.