How to create a chart in Excel (and pick the right one)

Applies to Excel for Microsoft 365, Excel 2021 and 2024, Excel for Mac and Excel for the web. The web version has the same Insert menu but fewer formatting options. Most chart problems come from how the data is laid out rather than the chart itself, so the first section below is about preparing the range.

Build a chart step by step

Example data: months in column A (A1 says Month), and sales for two regions in columns B and C with headers in row 1.

Insert the chart

1. Click any cell inside your data. If the data has blank rows or columns in the middle, select the exact range instead, for example A1:C13.
2. Go to Insert > Recommended Charts. Excel previews charts that fit your data.
3. Click a suggestion to preview it full size, or open the All Charts tab to choose any type.
4. Click OK. The chart appears floating over the sheet; drag its border to move it, or drag a corner to resize.

Add the essentials

1. Click the chart, then the + (Chart Elements) button on its right edge (on Mac and web use Chart Design > Add Chart Element).
2. Tick Chart Title and click the title on the chart to type a real one, such as *Monthly sales by region, 2026*.
3. Tick Axis Titles and label both axes with units, for example *Sales (USD)*.
4. Tick Data Labels only if there are few points - labels on 50 bars become unreadable.

Change it later

1. Chart Design > Change Chart Type switches type without rebuilding.
2. Chart Design > Switch Row/Column swaps what is on the axis and what is in the legend.
3. Chart Design > Select Data adds, removes or re-orders series and fixes the axis labels.

Prepare the data so the chart behaves

Excel guesses the chart from the shape of your range, so three habits prevent most surprises:

1. One header row, no merged cells. Merged headers confuse series names; see how to merge and unmerge cells if you inherited a messy sheet.
2. Labels in the first column, numbers to the right. Dates or categories go in column A; each numeric column becomes a series.
3. Turn the range into a Table first (Ctrl+T or Insert > Table). A chart built on a Table grows automatically when you add new rows, so next month's figures appear without editing the chart.

If the top-left cell is empty, Excel treats the first row and column as labels, which is usually what you want. If it contains a header like *Month* and the months are numbers (2024, 2025), Excel may plot the years as a series - convert them to text or use Select Data to fix it.

Which chart answers which question

Pick the chart from the question you want the reader to answer, not from what looks impressive:

Our rule of thumb: if you need a legend with more than six entries, the chart is trying to say too much. Split it, or filter the data first with Filter and Sort.

Make it readable in 60 seconds

• Delete chart junk: click gridlines you don't need and press Delete. Keep one set of light horizontal gridlines at most.
• Start column and bar axes at zero. Right-click the vertical axis > Format Axis and check the Minimum bound. A cut-off axis exaggerates differences.
• Sort bars largest to smallest (sort the source data) so the ranking is obvious.
• Use colour for meaning: grey for everything, one accent colour for the series you want people to notice. Conditional colours in the cells don't carry into charts, so set them in Format Data Series.
• Copy formatting to other charts: format one, right-click it > Save as Template, then pick it from All Charts > Templates.

Fixes for charts that look wrong

The axis shows 1, 2, 3 instead of my dates or names. Excel didn't recognise the label column. Use Select Data > Horizontal (Category) Axis Labels > Edit and select the label range.

One series is a flat line at zero. That column is stored as text. Look for a green triangle in the cells, select them and choose Convert to Number.

Months are out of order. The source isn't sorted, or months are text like "Jan". Sort by a real date column, or use dates and set the axis to Date axis in Format Axis.

Gaps or drops to zero for missing data. Right-click the chart > Select Data > Hidden and Empty Cells and choose Gaps or Connect data points with line.

The chart vanished when I filtered. Charts follow visible cells by default. In the same Hidden and Empty Cells dialog, tick Show data in hidden rows and columns if you want the full series regardless of filters.

Charts from pivot tables

If your data needs grouping first - totals by region and month, for example - build a pivot table and then choose PivotTable Analyze > PivotChart. PivotCharts update when you change the pivot's fields or filters, which is quicker than maintaining a SUMIF summary by hand. To highlight values in the source table rather than in a chart, use conditional formatting.