Choosing Excel Chart Types for Query Results That Read
Choose an Excel chart type from the shape of your query result: line, column, bar, stacked, scatter, histogram or table, with rules and the mistakes to avoid.
Opening the chart gallery in Excel and clicking the one that looks nicest is how a clear table becomes an unclear picture. The better habit is to look at the result of your query, ask what the reader should take away and let those two facts choose the chart. This article gives that process for the chart types data people use, with the query shape each one needs and the traps to avoid. It is the choosing step in charts from data pulled out of databases, and sending charts from a query to an Excel sheet shows how to build the result.
Look at the shape of the result
Query results come in a few shapes, and each fits certain charts.
| Shape of the result | Example | Natural chart |
|---|---|---|
| One ordered key (time) and one measure | month, revenue | Line |
| One ordered key and several measures | month, web, store | Multi-line, or stacked column |
| One category key and one measure | region, revenue | Sorted bar |
| Two category keys and one measure | region, channel, revenue | Grouped or stacked bar |
| Two measures per row | order value, days to ship | Scatter |
| One measure, many rows | order value | Histogram |
| A key, a start and an end | task, start, end | Bar-based timeline |
The first question to ask of any result is "what is the key?" If the key is time, the order is fixed and the line shows the shape. If the key is a category, order is a choice you make. If there is no key and each row is an observation, you are looking at a distribution or a relationship.
Change over time: lines and columns
A line chart is the default for time series. It encodes position and slope, which is how people read trends. It works with many points: 24 months, 365 days. It allows a non-zero baseline when the point is the shape, though the axis must be labeled and the scale reasonable.
A column chart (vertical bars) also works for time when there are few periods, say 12 or fewer, and when the individual values matter more than the slope. Columns start at zero. For a monthly count with a handful of periods, columns read slightly better than a line; for two years of daily data, a line is the only sensible choice.
Do not put time on a bar chart with horizontal bars. People expect time to run left to right.
If you have more than four series on one line chart, you are drawing a tangle. Highlight one series in a strong color and show the others in gray, or split into small multiples, a grid of small charts with the same scales. Small multiples compare shapes well because the reader's eye is not asked to follow one line through nine others.
Comparing categories: bars
For "which is biggest", use a horizontal bar chart, sorted by value. Horizontal bars give category names room to breathe, which matters for long labels like product names, and the sort makes the ranking readable at a glance. Vertical columns suit short labels and few categories.
Rules for bars:
Start at zero. The length encodes the value; truncating the axis lies about the ratios.
Sort by value, unless the categories have a natural order (age bands, size S to XL, weekdays).
Cap the count. Past 15 or 20 categories, show the top ten and group the rest as "Other", computed in SQL.
Keep one color unless color encodes something. A rainbow of bars adds noise.
For two category dimensions, a grouped bar puts bars side by side per group, which compares across groups within a category. A stacked bar puts the groups on top of each other, which shows the total and the composition. Stacked bars make the bottom segment easy to compare and the others hard, because they do not share a baseline. Use stacked bars when the total matters and there are three or four groups; use grouped bars or small multiples when comparing groups matters. A 100% stacked bar shows composition independent of total: the share of each channel per month, with the totals hidden.
Parts of a whole
A pie chart shows share, and humans judge angles poorly. A pie works for two to four slices where one is clearly larger, such as a 70/20/10 split. Beyond that, a sorted bar chart of shares is easier to read. Never use 3D pies, exploded pies or pies with a dozen slices. A donut chart has the same problems with a hole in the middle.
A treemap shows hierarchical shares as nested rectangles and suits many categories where only the biggest matter, like spend by department and team. Readers still cannot compare areas precisely, so label the values. A waterfall chart shows how a starting value becomes an ending value through positive and negative steps, and is the right chart for a profit bridge or a variance walk. Recent Excel versions have a native waterfall; libraries without it build one from stacked columns with an invisible base series.
Relationships and distributions
A scatter plot shows how two measures relate: each row is a point. Use it for order value against days to ship, or marketing spend against signups. Add a trend line only if you can say what it means, and do not read causation into a correlation. When thousands of points overlap, make them semi-transparent or bin them.
A histogram shows how a single measure is distributed: the count of orders in each value range. The bin width is a choice that changes the look; try two or three. Compute the bins in SQL with width_bucket or floor(value / width) so the chart reads a small table, not a hundred thousand rows. A box plot summarizes distributions across categories with median and quartiles, and Excel supports it as a chart type, but many readers do not know how to read one; add a note.
A bubble chart adds a third measure as size. It is hard to read accurately and best for a small number of points with labels.
Geography and others
A filled map works when geography is the point and the regions are about equal in importance. Large regions with small populations dominate the picture, so a map of revenue by US state mostly shows land area. A sorted bar of the same data is usually clearer. If you do use a map, normalize by population or another base, and label it.
Gauges and speedometers show a single value against a target and waste space. A number with a conditional color or a bullet chart (a bar against a target marker) says the same in less room. Radar charts compare several measures across a few items and are hard to read; use a table with conditional formatting.
When a table is the better answer
If the reader needs exact numbers, or there are too many rows to draw, a table wins. A table with a data bar or a color scale in one column, or sparklines in another, gives the shape and the figure together (sparklines and conditional formatting for quick scans). A common pairing is one chart for the message and a table below for the detail.
Choices that mislead
Three choices cause most of the damage.
3D effects. Perspective changes the apparent size of bars and slices. There is no data reason for it. Turn it off.
Secondary axes. Two scales on one chart let the author pick the relationship. If two measures need comparing, index both to a base of 100, or draw two charts one above the other with a shared time axis.
Truncated bars. Covered in avoiding misleading axes in Excel charts, and the single most common one.
A fourth, less obvious: too many series. If a legend needs a second column, the chart has too much in it.
Producing the right chart from code
With XlsxWriter, the chart type is the first argument: {"type": "line"}, {"type": "column"}, {"type": "bar"}, {"type": "scatter"}, {"type": "pie"}, and a "subtype" such as "stacked" or "percent_stacked". openpyxl has LineChart, BarChart (with type = "bar" or "col" and grouping = "stacked"), ScatterChart and PieChart. Sort the data in SQL before writing it, since the chart draws rows in the order they appear. The XlsxWriter chart type list shows all the options, and the Microsoft overview of chart types describes what each is for.
When no chart type is the right choice
No chart is the right choice when the message is a single number: put it in large type with the comparison beside it. None is right when the data is too thin for a shape, such as three points. None is right when the reader will need to look up values, in which case give them a table and, if useful, conditional formatting. And none is right when the data is wrong: a beautiful chart of an unreconciled number spreads the error further than a table would (building Excel reports from SQL queries people trust covers the checks that come first).
