Charts and Conditional Formatting
A chart answers one question. Decide the question first, and then choose the chart.
Which chart for which question
| Question | Chart |
|---|---|
| How do categories compare? | Column or bar |
| How has a value changed over time? | Line |
| How are two numbers related? | Scatter |
| What share does each part have? | Pie, for a few categories only; otherwise a bar |
| How are values distributed? | Histogram |
Use a bar chart when category names are long, because horizontal labels are easier to read.
Create a chart
Select the summary you want to plot, not the raw rows, and choose Insert > Recommended Charts. A summary built with SUMIFS or a PivotTable is the usual source.
Make it readable
- Give it a title that states the point, such as “North leads revenue in Q1”.
- Sort bars from largest to smallest unless the categories have a natural order.
- Start the value axis of a column or bar chart at zero. A shortened axis exaggerates differences.
- Remove gridlines, borders and legends that are not needed. Label the data directly where you can.
- Use one colour, and a second colour only to highlight the item you want noticed.
- Avoid 3-D effects. They distort the values.
Combination charts
To show revenue as columns and a percentage as a line, select the chart and choose Chart Design > Change Chart Type > Combo, then put the line on the secondary axis.
Sparklines
A sparkline is a tiny chart inside one cell. Select a row of monthly values and choose Insert > Sparklines > Line. Sparklines are useful beside each row of a summary table.
Conditional formatting
Conditional formatting changes how a cell looks based on its value. It is on the Home tab.
| Type | Use |
|---|---|
| Highlight Cells Rules | Mark values above, below or equal to a number |
| Top/Bottom Rules | Mark the top 10 items or the bottom 10% |
| Data Bars | A bar inside each cell, proportional to its value |
| Color Scales | A gradient from low to high |
| Icon Sets | Arrows or traffic lights for bands |
Formatting a whole row with a formula
Select the data rows, choose Conditional Formatting > New Rule > Use a formula, and enter:
=$I2>=1000The $ fixes the column, so every cell in the row tests the Revenue in column I. The whole row is highlighted when revenue is at least 1000.
Practice
Chart revenue by region as a sorted bar chart, and add data bars to the Revenue column of the table.