Charts

A table of numbers tells you the facts; a chart tells you the story. Six months of sales in a column of numbers take a minute to read, but a line chart shows in one second that sales dipped in June and climbed after that. Charts are also what your manager, your client or your bank will look at first.

In this lesson you will choose the right chart for your question, create and edit charts, combine two kinds of data in one chart, link a chart title to a cell, and add tiny in-cell charts called sparklines.

The sample sheet

A café in Kochi tracks its monthly figures for April to September 2026:

Columns D and E are formulas, filled down from row 2:

=B2-C2
=D2/B2

Choosing the right chart

Start with the question you want the chart to answer:

When unsure, a column or line chart is almost always readable. Avoid 3-D effects: they make bars look bigger or smaller than they are.

Creating a chart

1. Select the data including the headings: here A1:C7.
2. Choose Insert > Recommended Charts. Excel suggests charts that suit the data and shows a preview of each.
3. Pick Clustered Column and click OK.

Two shortcuts are worth knowing: Alt+F1 inserts the default chart on the same sheet straight away, and F11 puts it on a new chart sheet of its own.

Excel uses the headings for the legend ("Sales (₹)", "Expenses (₹)") and the months for the horizontal axis. If the series come out the wrong way round, with months in the legend, use Chart Design > Switch Row/Column.

Editing a chart

Click the chart and three buttons appear at its top-right corner (in Excel for Windows):

• Chart Elements (+): tick Chart Title, Axis Titles, Data Labels, Legend, Gridlines.
• Chart Styles (brush): colour schemes and preset looks.
• Chart Filters (funnel): hide some series or categories without changing the data.

The Chart Design tab adds Change Chart Type, Select Data (to add or remove series, or change the range) and Move Chart. Double-click any part of a chart, such as an axis, to open its Format pane on the right.

A few changes make most charts much clearer:

• Write a title that states the point: "Sales grew 23% from April to September", not just "Chart 1".
• Delete clutter: extra gridlines, a legend for a single series.
• Format the vertical axis with the same Indian number format you use in the sheet. Double-click the axis, open Number, choose Custom, and enter the format from Formatting & Indian Number Format.
• Keep column and bar axes starting at zero, so bar lengths stay honest.

Linking a chart title to a cell

A title can show the result of a formula, so it updates with the data. Put this in H1:

="Café sales, Apr to Sep 2026: ₹" & TEXT(SUM(B2:B7), "[>=10000000]##\,##\,##\,##0;[>=100000]##\,##\,##0;##,##0")

Café sales, Apr to Sep 2026: ₹15,86,000

Click the chart title, type = in the formula bar, click cell H1 and press Enter. The title now reads from the cell. The formula bar shows a reference like this:

=Sheet1!$H$1

Combo charts: two measures in one chart

Sales are in lakhs of rupees, but margin is a percentage around 20%. On one axis, the margin line would be flat along the bottom. A combo chart gives the second measure its own axis:

1. Select A1:B7, hold Ctrl and also select E1:E7.
2. Choose Insert > Recommended Charts > All Charts > Combo.
3. Set Sales to Clustered Column, set Margin to Line, and tick Secondary Axis for Margin.

The columns show the rupee amounts against the left axis and the line shows the margin against a percentage axis on the right. June's dip stands out in both.

Pie charts for parts of a whole

A pie chart shows how a total splits up. September's expenses of ₹2,18,500 broke down into Rent 60000, Salaries 95000, Ingredients 48500 and Electricity 15000. Put those four rows in a small table, select it, and choose Insert > Pie. Add Data Labels and, in their format pane, tick Percentage and Category Name, so nobody has to match colours with a legend. Salaries are about 43% of the total.

Keep pie charts to a few slices. With ten categories, a sorted bar chart is far easier to read.

Charts that grow with the data

If the chart's data is an Excel table, adding October as a new row extends the chart automatically. With a plain range, you would have to change the range through Select Data each month. This is one of the best reasons to keep chart data in tables.

Sparklines: tiny charts in cells

A sparkline is a small chart inside one cell, perfect next to each row of a report. Select B2:B7, choose Insert > Sparklines > Line, and set the Location Range to an empty cell such as B9. The cell shows the six-month sales trend in miniature. On the Sparkline tab you can mark the high and low points.

For a branch report with one row per branch and one column per month, sparklines in the last column show every branch's trend side by side.

Common mistakes

• Selecting without headings. The legend shows "Series1". Include the heading row when you select.
• Pie chart with many slices. Use a bar chart instead, sorted from largest to smallest.
• Two measures of different sizes on one axis. The smaller one becomes a flat line. Use a combo chart with a secondary axis.
• Totals inside the chart range. A "Total" row plotted as a month dwarfs every other bar. Select only the data rows.
• A title that says nothing. Use the title to state what the reader should notice.

Key points

• Pick the chart for the question: column to compare, line for trends over time, pie for a few parts of a whole.
• Select data with headings, then use Insert > Recommended Charts, Alt+F1 or F11.
• Chart Elements, Chart Design and the Format pane control titles, labels, axes and series.
• A combo chart with a secondary axis shows two measures of different sizes together.
• Charts based on tables grow automatically; sparklines show trends inside single cells.

="Best month for profit: " & INDEX(A2:A7, MATCH(MAX(D2:D7), D2:D7, 0))

Best month for profit: Sep