How to make a graph in Excel: column, line and pie charts step by step

A chart turns a column of numbers into something people see in a second: which region sold the most, whether sales are going up, how big a share one product has. Excel calls them charts, many people search for graphs, and they're the same thing. You make one in three steps: get the data into a small, tidy table, click in it, and pick a chart type on the Insert tab.

The hard part is rarely the button. It's the table behind the chart and the choice of type. This guide covers both: which chart answers which question, how to build column, bar, line, pie and combo charts, how to make a chart that updates itself, and how to fix the problems that make a chart look wrong. Along the way you'll see the formulas that prepare the data, since a good chart usually starts with a summary table.

Make a chart in three steps

1. Put the data in a small table. Labels in the first column, a header in the first row, and numbers in the columns to the right. No blank rows and no total row.
2. Click a cell in the table. You don't have to select all of it. Excel finds the edges of the table itself, as long as empty cells surround it.
3. Choose Insert > Recommended Charts. Excel suggests a few chart types for your data and shows a preview of each. Pick one and click OK.

The chart lands on the sheet next to the data, and it stays linked to the cells. Change a number, and the bar or the line moves with it. Drag the chart by its edge to move it, and by a corner to resize it.

If you already know the type, the buttons next to Recommended Charts go straight to it: Insert Column or Bar Chart, Insert Line or Area Chart, Insert Pie or Doughnut Chart and so on. All Charts, the second tab in the Recommended Charts dialog, lists every type with its variants.

There are two shortcuts. Alt+F1 makes a chart of the selected data in the default type, usually a column chart, right on the sheet. F11 does the same on a separate chart sheet that holds nothing but the chart, which is handy for printing or presenting.

Which chart for which question?

Start with the question the chart should answer. The type follows from it.

Column charts are the safe default. Almost every question about comparing a handful of things can be answered with one, and people read the length of a bar more accurately than the angle of a pie slice. When in doubt, use columns.

Get the data ready

A chart draws exactly what the cells say, so most chart problems are table problems. Before you insert anything, check the table:

• One header row. The headers become the series names in the legend.
• Labels in the first column. Regions, months or products. They become the labels along the axis.
• Numbers that are real numbers. A number stored as text plots as zero or not at all. Numbers that sit at the left edge of their cells are usually text, and the guide to cleaning data shows how to convert them.
• No total row. A total inside the selection becomes a bar that dwarfs all the others. Leave totals out of the chart range.
• One row per bar. If the raw data has a row per sale, summarize it first.

That last point is where formulas come in. A sales list with one row per order can't go straight into a chart of sales per region, because the chart would draw a bar per order. Build a small summary table next to it instead, with one row per region and a SUMIF that adds up the sales for each:

Select the summary table, insert a column chart, and you have one bar per region. Add a sale to the list, and both the total and the bar grow. Total per region, ready to fill down is the exercise for exactly this formula, and the topic page on summary tables has the variants with COUNTIF, AVERAGEIF and two criteria. A pivot table does the same job with clicks instead of formulas, and it has its own kind of chart, covered further down.

Column and bar charts

A column chart has vertical bars, and a bar chart has horizontal ones. Choose Insert > Insert Column or Bar Chart and pick Clustered Column or Clustered Bar.

Use columns when there are only a few categories, or when the categories have an order, like months or years. Use bars when the labels are long, like product names, or when there are many categories, since horizontal labels don't have to be tilted to fit.

Bars are easier to read in order, largest first. Sort the summary table before you make the chart, or let a formula sort it, so the chart reorders itself when the numbers change:

SORT sorts the table by its second column, and -1 means largest first. Point the chart at the sorted copy, and the longest bar always comes first. There is one catch: a bar chart draws the first row at the bottom. To get the largest bar at the top, right-click the vertical axis, choose Format Axis and tick Categories in reverse order.

With more than one column of numbers, Excel draws a group of bars per category, one bar for each column. A table with regions in column A and two years in columns B and C gives two bars per region. If Excel groups them the wrong way, with one group per year, choose Chart Design > Switch Row/Column.

Stacked Column puts the parts of each category on top of each other, so the full column shows the total. It works when the total matters more than the parts, since only the bottom part has a straight baseline to compare against.

Line charts

A line chart shows change over time. Put the dates or months in the first column and the values next to them, and choose Insert > Insert Line or Area Chart > Line. Excel draws one line per column of numbers.

Keep the time steps even, with one row per month and no months left out. A missing month makes the line jump without showing the gap. If you use real dates instead of month names, Excel spaces them by date along the axis. Read the guide to dates in Excel if your dates turn out to be text.

Monthly figures go up and down, and the noise can hide the trend. A moving average smooths it out. Put it in a column next to the figures and add it to the chart as a second line:

The average starts in the third month, since that's the first with three months behind it. The range has no $ signs, so it moves down one row at a time when you fill it down. The AVERAGE exercises train the function itself.

Excel can also draw a moving average without a helper column. Click the line and choose Chart Design > Add Chart Element > Trendline > Moving Average. The helper column takes more work, but it shows the numbers, and you can check them.

Pie charts

A pie chart shows how a whole splits into parts. It only works with one series of numbers that add up to something meaningful, such as sales per product for one year. Select the labels and the numbers, and choose Insert > Insert Pie or Doughnut Chart > Pie.

Excel works out the shares itself, but it helps to have them in the table too, so you can check the chart and quote the numbers in a report. Divide each part by the locked total:

Format the column as a percentage with Ctrl+Shift+5, and 0.45 shows as 45%. The $ signs keep the total on B2:B5 when you fill down, which is the exercise in each region's share of the total.

Pie charts go wrong quickly. A few rules keep them readable:

• Five or six slices at most. Group the small parts into one Other slice.
• Biggest slice first. Sort the table by value, largest first, so the slices run clockwise from the top in order of size.
• No 3-D. A tilted pie makes the slices in front look bigger than they are.
• Labels on the slices, not in a legend. Readers shouldn't have to match colors.

For the labels, click the chart, click the Chart Elements button, the plus next to the chart, and tick Data Labels. Under More Options you can show the category name and the percentage. If you want full control over the text, build the label in a cell:

TEXT turns the share into text in the format 0%, and & joins it to the name. Use the label column as the chart's category labels, and every slice says what it is and how big it is. The page on the TEXT function has more formats.

If the parts don't add up to a whole, for example a survey where people could pick more than one answer, a pie chart is the wrong choice. Use a bar chart.

Show growth, not just size

A column chart of sales per region shows that the biggest region is the biggest. Often the more interesting question is which region grew the most, and for that you chart the change in percent:

Fill it down, format it as a percentage and chart the growth column on its own. South sold far more than North, but it only grew 10%, so its bar is now the shorter one. That is the fair comparison when regions differ in size. The formula is the same as in percent change from last year, just written as C2/B2-1 instead of (C2-B2)/B2.

Sales against target with a combo chart

To show sales against a target, draw the sales as columns and the target as a line across them. That's a combo chart. First give the target its own column, with the same value on every row:

The $ signs keep the formula on E1 when you fill it down, so changing E1 moves the whole line. Then select the table and choose Insert > Insert Combo Chart > Clustered Column - Line. Excel draws the first series as columns and the second as a line.

If you have already made a column chart, choose Chart Design > Change Chart Type, pick Combo and set the target series to Line. The same dialog has a Secondary Axis box for each series. Tick it only when the two series have different units, such as sales in dollars and a margin in percent. Two axes for the same unit make a chart misleading, because the two scales don't match.

Gaps in the data

A blank cell leaves a gap in a line chart. That's often right, but it gets harder when the numbers come from formulas. A formula that returns "" for a missing month isn't blank, and Excel plots the text as zero, so the line dives to the bottom and back up.

The fix is to return #N/A instead. A line chart doesn't plot #N/A, so the line runs straight past the missing point:

To decide what happens to blank cells, right-click the chart, choose Select Data and then Hidden and Empty Cells. There you pick whether empty cells show as gaps, as zero, or with the line connected across them. The same dialog decides whether rows you've hidden still appear in the chart. By default they don't, so a chart loses data when someone hides rows or filters the table.

Titles, labels and legends

A chart needs a title that says what it shows, and often not much more. Click the chart and use the Chart Elements button, the plus next to it, to turn the parts on and off: Chart Title, Axis Titles, Data Labels, Gridlines and Legend. On a Mac, and for more options, use Chart Design > Add Chart Element.

A few habits make charts easier to read:

• Write the point in the title. "Sales by region" says what the chart is. "South sold the most in 2026" says what to look for.
• Drop the legend when there's only one series. It only repeats the title.
• Label the bars instead of adding gridlines when there are few bars. The reader gets the exact number without looking across.
• Start the value axis at zero for columns and bars. A bar's length is its value, so a cut axis makes small differences look big. A line chart may start higher, as long as the axis is labeled.

The title can come from a cell, which is useful when it should show a number that changes. Build the text with a formula:

Then click the chart title, type = in the formula bar, click the cell and press Enter. The title now shows whatever the cell says, and it updates when the data changes.

A chart that grows with the data

A chart points at a fixed range. Type a new row below the data, and the chart ignores it. The simplest fix is to turn the data into a table first: click in the data and press Ctrl+T. A chart made from a table grows when you add a row at the bottom, and so do formulas that refer to the table's columns.

The same goes for the summary tables above. If the source list is a table and the summary formulas refer to it, the totals pick up new sales by themselves, and so does the chart.

Charts from pivot tables

A pivot table summarizes a list with clicks, and a PivotChart draws it. Click inside the pivot table and choose PivotTable Analyze > PivotChart, or choose Insert > PivotChart to make both at once. The chart follows the pivot table: filter or regroup the pivot table, and the chart changes with it, which makes it quick to explore data.

The price is control. A PivotChart can't be pointed at other cells, and some types, such as scatter charts, aren't available. For a chart that goes into a report, many people build a formula summary table like the one above and chart that instead. The guide to pivot tables covers both routes and the formulas that give the same numbers.

Charts without a chart

Sometimes a whole chart is too much. Two features put a small picture inside the cells themselves:

• Data bars fill each cell with a bar as long as its value. They're part of conditional formatting, and the guide to conditional formatting shows how to add them.
• Sparklines are tiny line or column charts inside one cell, one per row. Select the numbers and choose Insert > Sparklines > Line. They show a trend per row in a long table, such as monthly sales per product.

Both update with the data and print with the cells.

When a chart looks wrong

• The years show up as a line at the bottom. A column of years holds numbers, so Excel draws it as a series. Delete the header above the years so the top-left cell is empty, and Excel uses them as labels. Or choose Select Data and set the years as the horizontal axis labels.
• The chart has a bar for every row of raw data. Summarize the data first, with SUMIF or a pivot table.
• A bar is missing or zero. The number is probably stored as text. Convert it to a number.
• The series are grouped the wrong way. Choose Chart Design > Switch Row/Column.
• The line drops to zero. A formula returns "" or 0 for a missing value. Return #N/A instead, as shown above.
• New rows don't appear. The chart points at a fixed range. Turn the data into a table with Ctrl+T, or right-click the chart, choose Select Data and extend the range.
• Data disappeared when rows were hidden. Hidden rows are left out by default. Change it under Select Data > Hidden and Empty Cells.
• The bars are upside down. A bar chart draws the first row at the bottom. Tick Categories in reverse order under Format Axis.

Copy a chart to PowerPoint or Word

Click the edge of the chart, press Ctrl+C and paste it into PowerPoint or Word with Ctrl+V. The paste options that appear let you match the design of the new document or keep the chart's own, link the chart to the workbook so it updates, or paste a picture that can't change but can't break either. For a chart you'll make again, right-click it and choose Save as Template. The template then shows up under Templates in All Charts.

Charts in Google Sheets

Google Sheets works the same way with different menus. Select the data and choose Insert > Chart. Sheets picks a type and opens the chart editor on the right, where the Setup tab sets the type and the data range, and the Customize tab sets titles, colors, labels and axes. The formulas in this guide, SUMIF, SORT, AVERAGE and TEXT, work unchanged in Sheets. Read more about the differences in Google Sheets or Excel.

Charts in Excel tests

Employer Excel tests sometimes ask for a chart, and when they do, the task is usually short: make a column chart of the totals, add a title, change the type. The points are mostly in the summary table behind it. Build the summary with SUMIF first, check one total by hand, and then insert the chart with Alt+F1 and change the type if the task asks for another one. Give the chart a title and axis titles, since graders look for them. The guide to Excel for job interviews has more on what the tests check.

In short

To make a chart, put the data in a small table with labels in the first column and numbers next to them, click inside it and choose Insert > Recommended Charts, or press Alt+F1. Use columns or bars to compare, a line for change over time, a pie only for parts of a whole, and a combo chart for sales against a target. Most charts that look wrong have a table problem behind them: raw rows instead of totals, numbers stored as text, or a range that doesn't grow.

The formulas behind a good chart are the ones that build its table: SUMIF for the totals, the share of a total and the percent change. The exercises below drill them on small sheets with instant feedback. Try the first one for free, and build the table your next chart needs.