How to make a chart in Excel that's clear and updates itself

Spreadsheet tools · Published September 27, 2026

Pick the right chart for your data, build it step by step, combine two chart types, and make it grow automatically when you add new rows.

The short answer

Put your data in a simple block: labels in the first column, one header per series, no blank or merged rows. Turn it into an Excel Table with Ctrl+T, then select it and go to Insert › Recommended Charts. Because the chart is built on a Table, new rows you add at the bottom appear in the chart without any extra work.

Pick the chart type by the question you want to answer, not by what looks impressive. Most charts need only a clear title, labeled axes and a legend when there's more than one series.

Choose the right chart

Lay out the data first

Excel reads the shape of your data to decide what goes on each axis. A clean layout gives you a sensible chart on the first try.

Here is the kind of layout that causes trouble:

To fix it:

• Move the title out of the data. You'll type it as the chart title instead.
• Delete blank rows and unmerge cells.
• Keep units out of the cells. Put them in the header ("Product A (units)") so the values stay numbers.
• Keep totals below the Table or on another sheet so they aren't charted.

If your data comes from another system as a CSV file, convert it to a workbook first so columns such as IDs and dates keep their values. The guide to opening CSV files on any device explains why.

Make the chart

1. Click any cell in your data and press Ctrl+T (or use Insert › Table). Check My table has headers and select OK.
2. With a cell in the Table selected, go to Insert › Recommended Charts.
3. Preview the suggestions, pick one and select OK.
4. Drag the chart where you want it and resize it from a corner.

If Excel puts the months in the legend and the products along the axis, use Chart Design › Switch Row/Column.

Make it clear

Excel for Windows

Select the chart and three buttons appear beside its top-right corner:

• Chart Elements (the + sign): turn on the chart title, axis titles, data labels, legend and gridlines.
• Chart Styles (the paintbrush): pick a style or color set.
• Chart Filters (the funnel): hide a series or category without deleting it.

Excel for Mac and the web

Excel for Mac doesn't show those buttons. Use the Chart Design tab instead: Add Chart Element covers titles, labels and the legend, and the style gallery is next to it. To hide data, use Select Data. In Excel for the web, select the chart and look for the chart options on the ribbon or right-click the chart to format it. The web layout has changed over time, so the names may differ from Windows.

What to add

• A title that says what the reader should see, such as "Product B catching up with A", not just "Sales".
• Axis titles with units, such as "Units sold".
• Data labels only when there are few points. On a busy chart they become clutter.

Change the chart type or combine two

To switch an existing chart, select it and go to Chart Design › Change Chart Type.

A combo chart helps when two series have very different sizes, such as revenue in thousands and a percentage. On Windows:

1. Select the chart and choose Chart Design › Change Chart Type.
2. On the All Charts tab, choose Combo.
3. Pick a chart type for each series, for example Clustered Column and Line.
4. Check Secondary Axis next to the smaller series, then select OK.

On a Mac, click the series you want to move, press Cmd+1 to open Format Data Series and, under Series Options, choose Secondary Axis. Then use Change Chart Type with that series selected to make it a line.

Make it update automatically

A chart built on an Excel Table grows with the Table. Type a new month in the first empty row under the Table and it joins the Table, and the chart picks it up. Changed values update the chart right away in any case.

Without a Table, a chart covers a fixed range. Right-click it, choose Select Data and widen the range each time, or drag the colored range outline on the sheet.

Sparklines: a mini chart in a cell

When you need a quick sense of the trend on every row, a sparkline fits in one cell. Select an empty cell next to a row of numbers, go to Insert › Sparklines, choose Line, Column or Win/Loss, and set the data range. The Sparkline tab lets you add markers for the high and low points. Drag the fill handle to copy it down other rows.

Common mistakes

• A pie with many slices. Small slices are hard to compare. Use a bar chart sorted from largest to smallest, or group small items into "Other".
• A value axis that doesn't start at zero. On a column or bar chart this makes small differences look large. If Excel set a higher minimum, double-click the axis and set Minimum to 0. Line charts can start higher, but say so.
• Dates treated as text. Months typed as text are spaced evenly even when dates are missing, and they won't sort by date. Real dates align to the right of the cell by default; text aligns left.
• A total charted as a category. Leave total rows and columns out of the charted range.

Troubleshooting

• The chart shows one series where you expected two. A header may be missing or a column may hold numbers stored as text. Fix the header, then convert the column to numbers.
• New rows don't show up. The data isn't a Table, or the new row was added after a blank row. Check that the Table's border includes it.
• The years appear as a series. Excel treats a column of numbers as data. Right-click the chart, choose Select Data, remove the Year series, then edit the horizontal axis labels and select the years.

Once your data is tidy, summaries are easier too. The guide to SUMIFS and COUNTIFS shows how to total it by month or category before you chart it.

In short

Keep the source data in a clean block, turn it into a Table and use Recommended Charts as a starting point. Choose the chart by the question, label the axes and title, and use a secondary axis only when two measures truly need different scales. The Table does the rest when you add new rows.