The idea
An Excel chart is a picture of a table. It reads a block of cells, takes the labels from the first column and the numbers from the next, and draws one mark per row: a bar, a point on a line, a slice. It does not group, add up or filter anything. If the block has twelve rows, you get twelve marks.
That sounds obvious, and it explains most chart trouble. When a chart looks wrong, the chart is rarely the culprit. The table behind it holds too many rows, the wrong rows, or numbers that were never summed. Good chart work happens before Insert → Charts: you decide the one question the chart answers, and you build the small table that answers it.
Figure 1
From twelve order lines to the three numbers a chart can draw
| order_id | channel | revenue |
|---|---|---|
| 9933 | web | 44.99 |
| 9933 | web | 379.00 |
| 9934 | web | 440.30 |
| 9935 | marketplace | 17.99 |
| 9936 | web | 11.99 |
| 9937 | app | 756.00 |
| 9937 | app | 28.99 |
| 9938 | web | 323.94 |
| 9938 | web | 349.00 |
| 9938 | web | 155.94 |
| 9939 | web | 17.99 |
| 9940 | app | 322.15 |
| channel | revenue |
|---|---|
| web | 1723.15 |
| app | 1107.14 |
| marketplace | 17.99 |
- Twelve order lines. If you select
revenueand insert a column chart from here, you get twelve bars: one per line. Nobody asked for that. - The question is "how much did each channel sell?" That is three numbers. The app lines are 756.00, 28.99 and 322.15.
=SUMIF($B$2:$B$13, A2, $C$2:$C$13)adds up the lines of one channel, so the app lines become 1,107.14. One formula, filled down, gives one row per channel.- This small table is what the chart draws: three labels, three numbers, three bars. The chart is the table made visible, and it is only as good as the table.
Why it exists
A column of forty numbers is hard to read. A bar chart of six numbers is easy: your eyes compare lengths faster than they compare digits. That is the value of a chart, and it only holds when the picture is simple. Six bars you can compare. Sixty bars you cannot.
So the job is summarising first and drawing second. You already know the tools for the first part:
SUMIF for one criterion, SUMIFS for two, COUNTIFS for counting, or a PivotTable that does
the grouping for you. The chart then makes the result readable at a glance, to someone who has
two seconds and no time for your formulas.
How it works under the hood
One question, one chart type
The shape of the question picks the chart.
- Compare categories ("which category sells most?"): a column or bar chart. Sort the bars from largest to smallest unless the categories have a natural order.
- Follow a number over time ("how did spend move during the week?"): a line chart, with the dates in order on the x-axis.
- Show the spread of one number ("how many products sit in each price band?"): a histogram, which is a column chart of counts per band, with no gaps between the bars.
- Relate two numbers ("do more sessions mean more orders?"): a scatter plot, one dot per row.
- Show shares of a whole ("what part of revenue is each channel?"): a pie, but only with about three slices. Beyond that, a bar chart is easier to compare.
Figure 2
Revenue by category, all completed orders
- One bar per category, sorted from largest to smallest. With the bars in order, the ranking reads without looking at a single number.
- Furniture sells the most, $1.39M. Three categories (Furniture, Outdoor, Lighting) are above the $1M line.
- Textiles is the smallest at $580K, less than half of Furniture. The bars start at zero, so the lengths compare honestly: a bar twice as tall means twice the revenue.
What the chart is built from
Select the label column and the number column together, then Insert → Charts, and Excel creates
the chart with the first column as the axis. Each column of numbers becomes a series. Two
series give two bars per category, or two lines. The headings of the number columns become the
legend. Because the chart is linked to the cells, it moves when the table moves. Change a
SUMIF input and the bar changes with it. If the table is a PivotTable, Insert → PivotChart gives
a chart that follows the pivot's buckets, which is the quickest way to chart a pivot without
copying numbers out.
Reading a trend
A line chart connects consecutive points. That tells the eye the points are related, and it is the right message for days, weeks and months.
Figure 3
Kitchen revenue per day, June 1 to 14
- Time on the x-axis, one point per day, joined by a line. A line chart says "these points follow each other", which is why it suits days and months and not categories.
- June 7 stands out at $2,364, nearly four times the 14-day mean. A line chart makes a spike like this obvious, and then you go and check the orders behind it.
- June 11 is the opposite: $20. A single bad day is not a trend. Read the whole line, not just the highest and lowest points.
Do not use a line across categories like Decor, Kitchen and Textiles. The line would suggest a slope from one to the next, and there is none.
Honest axes
Start with the labels. A chart without a title makes the reader guess, and a legend that says "Series1" is a sign the table had no headings. Put the heading in the first row of every column and Excel uses it.
The axis is part of the message. A column chart starts at zero because the length of the bar is the number. Cut the axis at 700,000 and Textiles (580K) vanishes while Kitchen (799K) looks a seventh the size of Furniture. The values are the same, but the picture lies. A line chart is different, because the position of the points carries the message, so a non-zero axis can be fine. Whatever you choose, label the axis, give the chart a title that says what it shows ("Revenue by category, all completed orders"), and delete anything that does not help the reader, such as gridline clutter and 3-D effects.
Common misconceptions
"I can chart the raw data and Excel will sort it out"
Excel draws exactly the cells you give it. Twelve order lines make twelve bars, and a repeated category shows up once for each line. Build the summary table, or a PivotTable, and chart that. A fast check: count the marks on the chart and compare with the number of things you meant to compare. If you wanted three channels and see twelve bars, the table is wrong.
"A bar chart can start the axis wherever it looks best"
The bar's length is the data. Starting the axis above zero makes small differences look huge, and it is the oldest trick in misleading charts. If the differences are small and the reader needs to see them, show the numbers in labels, or use a line or a dot plot, where position carries the message.
"More chart types means a better report"
A report with a pie, a radar and a 3-D bar is harder to read than one with a clear column chart. Each chart should answer one question that you can state in a sentence. If you cannot say what the chart answers, delete it. Three simple charts that each answer a question make a better daily dashboard than eight that look impressive.
Check yourself
One question. Answering it ticks this lesson off on the map; it never locks a level.
Not quite. A chart never groups. It draws one point per cell it is given, so grouping is something you do before, with SUMIF or a PivotTable.Try another answer.
Not quite. Excel does not add anything up for a chart. Every cell in the range becomes its own bar.Try another answer.
Not quite. Excel proposes a column chart for a single column of numbers, and the data would not suit a pie anyway. The problem is the twelve unsummed lines.Try another answer.
Right. Every cell in the range becomes a bar, so twelve lines give twelve bars. To compare channels, build the three-row table first.
Lesson done: it is ticked on the map.