| Concept | Description |
|---|---|
| Choosing a Chart | |
| Name the Question First | The chart type follows from the question being asked, which is why the question comes first |
| Matching Chart to Question | Column and bar to compare, line for time, scatter for relationships, histogram for distribution |
| Pie and 3D Charts | Pies misread beyond three slices and 3D perspective distorts every value it displays |
| Chart from Summarised Data | Summarise with a PivotTable first; charting 60 raw rows produces an unreadable chart |
| Analytical Charts | |
| Line versus Scatter | A line chart spaces horizontal values evenly regardless of their magnitude; scatter does not |
| Trendlines, Equation, and R-squared | Excel prints the fitted equation and R-squared, but without standard errors or significance |
| Polynomial Order and Overfitting | Raising polynomial order always raises R-squared, eventually fitting nothing but noise |
| Combo Charts and Secondary Axes | A secondary axis makes two scales comparable, and makes the relationship look arbitrary |
| Histograms and Bin Width | Native statistic charts since Excel 2016; the default bin width is rarely the most informative |
| Box and Whisker Plots | Median, quartiles and outliers per group in one chart, showing spread as well as centre |
| Sparklines | Cell-sized charts showing shape only; independent scaling exaggerates flat series |
| Formatting and Export | |
| Axis Baselines and Sorting | Bars need a zero baseline, and should be sorted by value unless the order is natural |
| Titles That State the Finding | A title stating the conclusion does more work than one naming the variables |
| Exporting for a Report | Paste as a picture, and check the PivotTable filter state before exporting |
18 Charts and Visualization
A chart exists to answer one question. Choosing the type is therefore a matter of naming the question first, and most bad charts come from skipping that step and reaching for whatever looked good last time.
The principles here carry over directly into Data Visualization in R, which covers the same decisions with ggplot2. What changes between the two tools is the effort of producing a chart, not the judgement of which one to produce.
18.1 Matching the Chart to the Question
| The question | Chart | Notes |
|---|---|---|
| How do categories compare? | Column or bar | Bar when labels are long or categories exceed about eight |
| How has this changed over time? | Line | Time on the horizontal axis, always |
| Do two numeric variables move together? | Scatter | The only honest choice for a relationship |
| How is one variable distributed? | Histogram | Bin width changes the story; try several |
| How does a distribution compare across groups? | Box plot | Shows median, quartiles, and outliers at once |
| What is the composition of a whole? | Stacked column | A pie only for two or three parts |
| Two measures on different scales? | Combo with secondary axis | Rainfall in mm against yield in quintals |
| How does a value track against a target? | Line or column with a reference line | Add the target as a second series |
Two charts that look tempting and usually mislead:
Pie charts ask the eye to compare angles, which it does poorly. Beyond three or four slices, a sorted bar chart communicates the same data faster and more accurately. A pie with twelve districts in it is decoration, not analysis.
3D charts of every kind distort the values they display. The perspective makes front categories look larger than rear ones of equal size. Excel offers them; there is no analytical situation that calls for one.
18.2 Building a Chart
Select the data including headers, then Insert, then the chart type. Alt + F1 inserts a default chart on the current sheet, F11 puts one on its own sheet.
For a chart from summarised data, build the summary first with a PivotTable and chart that, rather than charting 60 raw rows. A column chart of 60 individual farms is unreadable; a column chart of 5 district averages answers the question.
Three tabs control everything afterwards. Chart Design handles type, data range, and layout. Format handles the appearance of whichever element is selected. The plus icon beside a selected chart toggles individual elements without opening any dialog.
Select a single element before formatting it. Clicking once on a data series selects every point in it; clicking again selects the one point, which is how a single bar gets highlighted in a different colour.
18.2.1 Scatter Plots and Trendlines
For the relationship between two numeric variables, the scatter plot is the only defensible chart, and Excel’s distinction between Line and Scatter matters more than it appears. A Line chart treats the horizontal values as evenly spaced categories regardless of what they actually are. A Scatter chart treats them as numbers. Plotting rainfall against yield on a Line chart spaces the rainfall values evenly and produces a picture of a relationship that does not exist.
Select the two numeric columns, Rainfall and YieldQtPerHa, then Insert, then Scatter.
Right-click any point, then Add Trendline. The Format Trendline pane offers Linear, Polynomial, Logarithmic, Exponential, Moving Average, and Power. Tick Display Equation on chart and Display R-squared value on chart.
On the sample workbook, Excel prints:
y = 0.0199x + 22.67
R² = 0.1549That slope is the regression coefficient, and that R² is the proportion of variation in yield explained by rainfall. About 15 percent here, which is itself the finding: rainfall matters, and it is nowhere near the whole story. Plotting yield against NDVI instead returns an R² of 0.40 on the same 60 farms, and the multiple regression in the next section shows what the two explain together. Both are produced properly, with standard errors, p-values, and confidence intervals, by the regression tool in Statistical Analysis with the Analysis ToolPak. The trendline is the picture; the regression output is the evidence. Reporting an R² from a chart label without the accompanying significance test is a common shortcut and a poor one.
The polynomial option deserves caution. Raising the order will always increase R², right up to the point where the curve passes through every point and describes nothing but the noise. This is overfitting, and it is the same phenomenon that Polynomial Regression treats formally.
18.2.2 Combo Charts
Rainfall in millimetres and yield in quintals per hectare differ by an order of magnitude. Plotted on one axis, the smaller series flattens into a line along the bottom.
Insert, then Combo Chart, then set one series to Line, the other to Clustered Column, and tick Secondary Axis for whichever needs it.
Secondary axes invite misreading, since the apparent relationship between the two series depends entirely on where each axis starts and ends, and those choices are arbitrary. Label both axes clearly, and where the goal is to show that two variables move together, a scatter plot makes the claim honestly and a dual-axis chart only suggests it.
18.2.3 Histograms and Box Plots
Excel 2016 onward includes both as native chart types under Insert, Statistic Chart.
For a histogram, select the single numeric column and insert it. Right-click the horizontal axis and choose Format Axis to set bin width or bin count directly. The default binning is rarely the most informative, and trying two or three widths is worth the thirty seconds: too few bins hides the shape, too many turns it into noise.
The same distribution comes out of a PivotTable with the numeric field grouped into bands, described in PivotTables and PivotCharts. The PivotTable version gives the counts as numbers as well as a picture.
A box and whisker plot shows median, quartiles, and outliers for each group. Select the category column and the numeric column together, then insert it. Comparing yield distributions across four irrigation types is one chart rather than four histograms, and it makes differences in spread visible, not just differences in the middle.
18.2.4 Sparklines
A miniature chart inside a single cell, sized to the cell and intended to sit beside the numbers rather than replace them.
Insert, then Sparklines, then Line, Column, or Win/Loss. Give it the data range and the cell to place it in, then fill down to produce one per row.
On a table of districts with 12 monthly prices each, a column of sparklines shows every district’s trajectory at a glance in the space of one column. They carry no axis and no scale, so they show shape only. Whether each sparkline is scaled independently or all share one scale is set under Sparkline, Axis, Vertical Axis Minimum and Maximum Value, and the answer changes the reading completely. Independent scaling exaggerates small movements in flat series.
18.3 Formatting for Clarity
Excel’s defaults are adequate and can be improved in under a minute.
Start a bar or column axis at zero. Bar length encodes value, so a truncated axis makes a 3 percent difference look like a doubling. Line charts showing change over time are the exception, where a zero baseline can flatten the very variation being examined.
Sort bars by value unless the categories have a natural order such as months or sizes. An alphabetical bar chart forces the reader to do the ranking themselves.
Write the finding as the title. Rainfed farms yield 28 percent less tells the reader what they are looking at. Yield by Irrigation Type makes them work it out. The chart’s own axis labels already name the variables.
Remove what carries no information. Gridlines can usually go, or fade to light grey. Legends are unnecessary with one series, and better replaced with direct labels on the series where there are two or three.
Label the units. Yield (qt/ha) on the axis, not Yield.
Limit colour to meaning. Grey for context and one accent colour for the series being discussed reads far better than six default colours. Check that the result still works in greyscale, since printed course handouts frequently are.
Keep the chart on the sheet where its data lives, or on a dedicated dashboard sheet. Charts scattered across a workbook detach from their sources over time.
18.4 Preparing a Chart for a Report
Right-click a chart and choose Copy, then paste into Word or PowerPoint as a picture rather than as a linked object. A linked chart breaks the moment the workbook moves, and it silently updates if the workbook changes, which is not always wanted in a document that has already been circulated.
Save to a file through right-click, Save as Picture, choosing PNG. Set the chart size on the worksheet before saving, since the export uses the on-sheet dimensions.
A chart built on a PivotTable carries its filter state into the exported image with no indication that a filter was active. Check what the underlying table is currently showing before exporting anything that will be read by someone who cannot see the workbook.