How to Make a Chart in Excel: Create, Choose the Type and Keep It Updated
To make a chart in Excel, select your data including the header row (for example A1:C7), then click Insert > Recommended Charts. Excel suggests several charts that suit your data: pick one and click OK. The chart appears in the middle of the sheet, and you can drag it and resize it with the mouse.
Even faster: select the data and press Alt + F1. Excel inserts a column chart on the same sheet straight away. F11 does the same, but on a new sheet that holds only the chart.
The example table
Monthly sales for two shops, January to June:
| A | B | C | |
|---|---|---|---|
| 1 | Month | Leeds | Bristol |
| 2 | January | 12,400 | 9,800 |
| 3 | February | 11,200 | 10,300 |
| 4 | March | 13,900 | 11,700 |
| 5 | April | 14,600 | 12,100 |
| 6 | May | 15,300 | 13,400 |
| 7 | June | 16,100 | 12,800 |
The layout matters: a first column of labels (the months), a header row naming each series (Leeds, Bristol), and numbers everywhere else. No blank rows and no total row in the middle.
Create the chart step by step
- Select
A1:C7, headers included. If the table has no blank rows or columns, clicking a single cell is enough: Excel picks up the whole block. - On the Insert tab, in the Charts group, click Recommended Charts.
- In the Insert Chart dialog, click the suggestions on the left to preview them. If none fits, the All Charts tab lists every type.
- Choose Clustered Column, then OK.
Result: for each month, two vertical bars side by side, one per shop, with a Leeds / Bristol legend. Excel has read the months as horizontal axis labels and each column as a series.
If you wanted it the other way round (one series per month, shops along the axis), click the chart, then Chart Design > Switch Row/Column. The Chart Design tab only appears while the chart is selected.
Which chart type should you use?
The type depends on the question the chart has to answer, not on looks:
| You want to show | Chart type | In the example |
|---|---|---|
| A comparison between categories | Column (vertical bars) | Leeds against Bristol, month by month |
| A change over time | Line | Leeds sales from January to June |
| Each item's share of a total | Pie | Each department's share of sales |
| A comparison with long labels | Bar (horizontal) | Product or sales rep names |
| Two measures in different units | Combo | Sales in pounds and number of orders |
Line chart: show a trend
With the same table, select A1:C7, then Insert > Recommended Charts > All Charts > Line. Each shop becomes a line. It shows more clearly than the column chart that Leeds has grown every month since February, while Bristol dropped in June.
To change the type of an existing chart, there is no need to start again: right-click the chart and choose Change Chart Type.
Pie chart: each item's share
A pie chart shows a single series. Here are the Leeds shop's half-year sales by department (the total, £83,500, matches cells B2:B7 of the first table):
| A | B | |
|---|---|---|
| 1 | Department | Leeds sales |
| 2 | Clothing | 38,200 |
| 3 | Footwear | 24,500 |
| 4 | Accessories | 13,800 |
| 5 | Home | 7,000 |
Select A1:B5, then Insert > Recommended Charts > All Charts > Pie. To show percentages on the slices, click the chart, then the + button (Chart Elements) to its right, tick Data Labels, and from the arrow next to it choose More Options: tick Percentage and untick Value. Excel shows 46%, 29%, 17% and 8%.
Beyond five or six slices, the small ones become unreadable: a column or bar chart sorted from largest to smallest is easier to read.
Combo chart: two units on one chart
Sales run into thousands of pounds, orders into hundreds. On a shared axis, the orders series would be squashed along the bottom of the chart. The fix is a combo chart with a secondary axis.
| A | B | C | |
|---|---|---|---|
| 1 | Month | Leeds sales | Orders |
| 2 | January | 12,400 | 310 |
| 3 | February | 11,200 | 280 |
| 4 | March | 13,900 | 340 |
| 5 | April | 14,600 | 350 |
| 6 | May | 15,300 | 360 |
| 7 | June | 16,100 | 375 |
- Select
A1:C7, then Insert > Recommended Charts. - Go to the All Charts tab and pick Combo (at the bottom of the list).
- In the series list at the bottom of the window: Leeds sales as Clustered Column, Orders as Line, and tick Secondary Axis on the Orders row.
- Click OK.
Sales are read on the left axis, orders on a second axis on the right. Give each axis a title (see below), otherwise readers cannot tell which axis belongs to which series.
Change the title, axes and labels
Every chart element is added or removed in the same place: click the chart, then the + button that appears at its top right. The same options are under Chart Design > Add Chart Element.
- Chart Title: click the "Chart Title" placeholder and type your own. To make it follow a cell, select the title, type
=in the formula bar, click the cell, then press Enter. - Axis Titles: tick the box, then replace "Axis Title" with the unit ("Sales (£)", "Number of orders").
- Data Labels: shows the value on each bar or point. On a column chart with twelve bars this gets crowded quickly: keep them for one series or for the last point.
- Legend: useful as soon as there are two series. With a single series, remove it and put the information in the title.
Change the vertical axis scale
Excel picks the axis minimum and maximum on its own. To set them, right-click the numbers on the vertical axis, choose Format Axis, then under Axis Options fill in Minimum and Maximum in the Bounds section.
On a column chart, keep the minimum at 0. An axis starting at 9,000 in our example would make Leeds' June sales (16,100) look more than three times higher than February's (11,200), when the real gap is 44%. On a line chart, starting above 0 is acceptable to make the changes easier to see.
A chart that updates itself
If you add a "July" row under an ordinary range, the chart ignores it: its source stays A1:C7. There are two ways round this.
Turn the data into an Excel table (recommended)
- Click inside the data, then Home > Format as Table (or Ctrl + T).
- Check the range, leave My table has headers ticked, then click OK.
- Create the chart from this table, as above.
Now type "July" in A8 and figures in B8 and C8: the table grows by one row and the chart shows the seventh month with no further action. This also works for a chart created before the conversion, as long as its source matched the table exactly.
Change the source range by hand
Click the chart: its source range is outlined in colour on the sheet. Drag the corner handle to take in the new row. Alternatively, use Chart Design > Select Data and correct the Chart data range. You will have to do it again every time you add a row.
Common problems
Years show up as a series
If column A holds years (2023, 2024, 2025) and A1 says "Year", Excel sees numbers and plots them as an extra series, with tiny bars. Clear cell A1 before creating the chart: Excel then treats the column as labels. On an existing chart, open Select Data, remove the "Year" series, then click Edit under the horizontal axis labels and select the years.
A line with a gap, or one that drops to zero
An empty cell breaks the line. A cell containing 0 makes it dive. To choose the behaviour, open Select Data > Hidden and Empty Cells: you can connect the points on either side of the empty cell. Hidden rows also disappear from the chart, unless you tick Show data in hidden rows and columns in the same window.
The total row dwarfs everything
A "Total" row included in the selection becomes a bar far taller than the rest (83,500, more than five times the best month) and flattens the whole chart. Select the range without it.
To summarise long lists before charting them, use a PivotTable. To bring several charts and key figures together on one page, see how to build an Excel dashboard. If you want values to stand out inside the cells rather than in a chart, conditional formatting (data bars, colour scales) is lighter. Microsoft reference: Create a chart from start to finish.
