Learning path: Fundamentals of data visualization in Excel

Updated October 27, 2026

Excel makes it easy to insert a chart. Choosing one that answers your question takes a little more thought. This free learning path will help you compare categories, show trends, explore distributions, and make your charts easier to read. You'll work through a small population dataset, with an optional Copilot exercise once you've covered the basics.

You don't need Copilot to follow the core path. Basic familiarity with Excel is enough; the chart and PivotTable commands below are available in recent versions of Excel, although their position can vary between Windows, Mac, and the web. Work through the sections in order, or jump to the chart you need.

Download the workbook + Copilot prompt (.zip)

The download contains the original workbook, a copy-ready Copilot prompt, and a short README. You can also browse the three files on GitHub or download just the original workbook (.xlsx).

Open the data worksheet. Its three columns are Borough, Attribute (the year), and Value (population). We'll use this sheet for the exercises; the other sheets preserve the original source and its link.

A useful detail before charting: this is an older teaching dataset. In the NYC Department of City Planning source report, Table 7, 1950-2000 are census figures, 2010 uses DCP-adjusted census figures, and 2020-2040 are projections. The 2020 rows in this workbook are not final 2020 Census counts. Our main comparison uses 2010, so your results will match the examples below.

Choose a chart that fits your question

Start with what someone needs to understand. Comparing five boroughs calls for a different chart than following one borough over several decades. Excel's Recommended Charts can offer a starting point, but the question should get the deciding vote.

Excel calls these visuals charts; you'll also see the word plots, especially for scatterplots. A univariate analysis focuses on one variable, such as delivery time. A bivariate analysis examines two, such as store size and sales. Those terms describe the analysis, rather than serving as rigid labels for every chart type.

Why visualization belongs in data analysis

I still don't get the disdain toward Excel that you sometimes hear from technically inclined people. It's a useful place to explore an idea, sketch an analysis, and show someone what you're seeing. A chart can reveal an outlier or an unexpected pattern that an average quietly sweeps under the rug.

For a reminder of how much a good visual can communicate, watch Hans Rosling's 200 countries, 200 years, 4 minutes. You don't need his wall of animated bubbles to bring the same curiosity to your spreadsheet. Then try this introduction to the Datasaurus and Anscombe's Quartet to see why similar summary statistics can hide very different patterns.

As you explore, ask what the marks, positions, labels, and colors tell the reader. Use color to direct attention, keep text readable, and include units and time periods. For a broader introduction, the Data Visualization Reader develops these ideas further.

Get to know the elements of an Excel chart

Select a chart and identify its title, axes, plotted series, and legend. The chart area contains the whole object; the plot area contains the marks and axes. Not every chart needs every element. If a label already identifies a series clearly, a separate legend may only make the reader look back and forth.

Use Exceljet's chart anatomy lesson for a tour, or Microsoft's guides to creating a chart and formatting chart elements when you need a command reference.

Visualizing categories with bar charts

To paraphrase Toby Keith, I love this bar chart. Bars compare amounts across categories, whether those amounts are counts, revenue, population, or something else. Horizontal bars are particularly helpful when category names are long. Excel calls the vertical version a column chart.

Let's compare the five boroughs in 2010. A PivotTable gives us a clean summary to chart without rearranging the source data.

1. On the data worksheet, select a cell in A1:C41. Choose Insert > PivotTable, confirm that range, and place the PivotTable on a new worksheet.
2. In the field list, place Borough in Rows, Attribute in Filters, and Value in Values. If Excel shows Count of Value, open Value Field Settings and change it to Sum.
3. Set the Attribute filter to 2010. You should see five boroughs. The Grand Total should be 8,242,624.
4. Sort Sum of Value from largest to smallest. Select the PivotTable and insert a 2-D Clustered Bar chart. If the largest bar appears at the bottom, use Format Axis on the category axis and select Categories in reverse order.
5. Give the chart a meaningful title and identify the figures as 2010 adjusted population. Keep the value axis starting at zero. Use millions for the axis display units if that makes it easier to read.

Your order should be Brooklyn (2,552,911), Queens (2,250,002), Manhattan (1,585,873), Bronx (1,385,108), and Staten Island (468,730). The total is a check on your summary; it should not appear as a sixth borough in the chart.

Quick wins to make your Excel charts easier to read

Excel's first chart is a starting point. Here is a plain column-chart version of the same 2010 borough data. The numbers are fine, but the vague title and unsorted categories make the reader do extra work. Compare it with the sorted horizontal chart below: the improvement comes from decisions about the message, not from adding effects.

• Write a title that helps. Use a factual takeaway when the data supports it, then keep the time period, units, and definitions nearby.
• Make comparisons easy. Sort category bars when rank matters; keep time in chronological order. Start bar and column value axes at zero so lengths remain meaningful.
• Use color deliberately. Highlight the series or category that matters. Use labels or line styles as well, so the message survives grayscale and color-vision differences.
• Remove distractions selectively. Skip 3-D effects and redundant legends. Keep light gridlines when they help readers estimate values.
• Check the chart against the cells. Confirm the selected period, aggregation, units, missing values, and whether totals have been included twice. Read it at the size your audience will actually see.

For a deeper reference, bookmark Peltier Tech's Excel charting dos and don'ts. As the population example shows, a polished chart can still be misleading if a projection is presented as an observed count.

Visualizing changes over time with line charts

A line chart helps a reader follow change in a meaningful order. For this exercise, create another PivotTable from data!A1:C41. Put Attribute in Rows, Borough in Columns, and Value in Values as a Sum. Keep the years 1950 through 2010, ordered from earliest to latest, and select Brooklyn and Queens in the Borough filter. Insert a Line with Markers chart.

These observations are equally spaced at ten-year intervals. If your dates or numeric x-values were irregularly spaced, check how Excel positions them; a scatterplot can represent numeric spacing directly. Notice that Brooklyn and Queens do not follow the same path, even though both end above two million.

For more background, read Storytelling with Data's explanation of line graphs and FreshSpectrum's Excel walkthrough.

Visualizing proportions with pie charts

Pie charts still manage to start arguments at the data visualization dinner table. They can work when you have a small number of categories that make up one whole and the differences are easy to see. If the task is to compare similar values precisely, bars will usually be easier to read.

Try turning your five-borough 2010 summary into a pie chart, excluding the Grand Total. Does it make the ranking easier or harder to judge? Keep it two-dimensional, label the slices directly, and check that all slices refer to the same year and population definition. Depict Data Studio explains when pie charts work, and Contextures provides the Excel steps.

Visualizing distributions with histograms

A bar chart compares named categories. A histogram groups numeric observations into intervals, or bins, so you can see where values cluster, how spread out they are, and whether the distribution is lopsided. Think about delivery times for hundreds of orders, rather than five borough names.

Five boroughs are too few observations for a very revealing histogram, so use a larger numeric dataset for this part. Select the numeric observations and choose Insert > Insert Statistic Chart > Histogram. Try two bin widths and consider whether either hides a useful pattern. Learn how histograms differ from bar charts, follow this Excel histogram tutorial, or explore my approach to flexible histograms with PivotTables. A box-and-whisker chart is another useful way to compare distributions.

Visualizing relationships with scatterplots

A scatterplot gives each observation an x-value and a y-value, both numeric. To keep practicing with this workbook, put Borough in Rows, Attribute in Columns, and Sum of Value in Values in a new PivotTable. Keep 1950 and 2010. Copy the five borough rows, excluding Grand Total, and paste them as values into a separate range. Select only the two population columns and insert an XY Scatter chart with markers.

Use 1950 population on the horizontal axis and 2010 adjusted population on the vertical axis. Each point now represents one borough. Label the axes with their years and units, and add borough labels if you want to identify the points. This is a small practice example, not evidence that population in one year causes population in another. For the mechanics, see Spreadsheeto's Excel scatterplot tutorial.

Stacked and combo charts: use them with a reason

Stacked charts show how components contribute to a total. A 100% stacked chart compares proportions, while an ordinary stacked chart preserves the total amounts. Watch how difficult it becomes to compare segments that do not share a common baseline.

A combo chart combines chart types, often columns and a line. It can help when two related measures need different treatment, but a secondary axis can also make unrelated movements appear closely aligned. Label both axes and check whether two separate charts would communicate the comparison more clearly. Microsoft has a guide to combination charts when you are ready to try one.

Optional: build and check a chart with Copilot

Copilot can help you create a chart from a natural-language request. Your job still includes deciding what question to ask and checking that the result answers it. Availability depends on your Excel version, subscription, and organizational settings; Microsoft's current getting-started guide covers access, and its visualization guide shows chart prompts.

Open a copy of the practice workbook and allow editing in Copilot if you want it to create the chart in the file. The prompt below uses the actual worksheet and column names. It also specifies the year, so Copilot does not quietly choose the projected 2020 values.

Using only the data worksheet, filter Attribute to 2010 and compare Value by Borough. Trim leading spaces from borough labels in the chart, without changing the source data. Create a native Excel horizontal bar chart with all five boroughs, ordered from largest at the top to smallest at the bottom. Start the value axis at zero and show population in millions. Use the title "NYC borough population, 2010 (DCP-adjusted)". Do not include a grand-total bar, 2020 projections, or data from the original wide-format worksheet. Tell me which cells or summary the chart uses.

Check the output against your manual version: five bars, Brooklyn largest, Staten Island smallest, and a total of 8,242,624 in the underlying 2010 summary. Inspect the chart's source and confirm that Excel has summed population rather than counted records. If a result differs, trace the range and filter before asking for prettier colors. A correct-looking chart can be surprisingly persuasive.

Once the values are right, ask Copilot to improve labels or apply consistent styling. My walkthrough on using a data visualization style guide with Copilot shows how to give it clearer design rules. You can also explore branded workbooks with Copilot skills when you need consistent formatting across reports.

Your next step

Choose one chart you use at work. Write down the question it should answer, then improve one thing: the chart type, category order, title, or labels. Check the result against the source data and ask whether someone seeing it for the first time would reach the intended conclusion.

When you're ready for more, my interactive Excel visualization tutorial adds dropdowns, checkboxes, and top-N views. Keep Storytelling with Data, Depict Data Studio, and Peltier Tech handy for design and charting questions. For a broader move from spreadsheets into analysis with Python and R, see my book Advancing into Analytics.

For more practical Excel and AI examples, join my newsletter using the form below.

Join the Excel + AI newsletter

Bringing these skills to colleagues? I lead hands-on workshops that help teams build clearer Excel reports and practice checking AI-generated work. Explore Excel and Copilot training for your team.