There are many ways to visually explain a list of values with different types of Excel Charts and other trickery.
We’ve already looked at the numerical or statistical ways to summarize a list of values. Now let’s visualize the data in various ways available easily in Excel.
There are various charting options available which can (and should) be changed from their default settings and color scheme.
Another option has nothing to do with Excel’s Chart features at all. It integrates nicely with the existing table.
We have a table called CountbyState and want to see how the values compare.
Bar Chart
There’s a simple bar chart like this:
Just select the entire data table and choose Insert | Recommended Charts
Select the chart nearest to your needs and it’ll be inserted into the worksheet.
But that’s just the start. The default design can be easily changed from Chart Styles tab.
The quick option is the Chart Styles gallery.
To change individual parts of a chart, head to the Format tab.
There are also settings in the Format Chart pane at right. Also clicking elements in the chart itself. The three buttons to the right are very handy.
Chart Elements – turn on/off parts of the charts
Chart Styles – change the style or color scheme
Chart Filters – quickly filter what appears in the chart
The chart might make more sense in value order, quickly do that with the sorting of the table.
A Better Bar Chart – Clustered Bar
The problem with the standard Bar Chart is reading the labels along the bottom. The solution is to swap the axis so the labels are horizontal – Microsoft calls it a Clustered Bar chart.
In the Bar Chart, not all state labels appear because there’s not enough room. We fix that by expanding the height of the chart and changing the ‘Interval between labels’ to 1, look in the Axis Options | Labels.
Bar Chart in cell
A very different type of chart is possible within cells using Conditional Formatting with the Data Bar style.
Calculate the percentages for each value. The Data Bar minimum is 0 and maximum is 1 because the % are stored as fractions (e.g. 40% is saved as 0.4).
Data Bars can appear with the value as we’ve done or click ‘Show Bar only’ to hide the values.
Conditional Formatting
Conditional Formatting can add visual clues within the cells themselves. For example, highlighting the high and low values:
Many, many Conditional Formatting options to choose from like Color Scales
Conditional Formatting defaults only apply to cells with values, which makes for a mismatch with the labels. The result can be pretty ugly, as you can see.
The workaround is to apply the formatting to each row based on the value of a single cell in that row. See Conditional Formatting beyond the defaults for details.
Map Chart
Because these values have locations, US States, a Map Chart is very easy to make. It’s a lot easier than it was even a few years ago.
As long as Excel recognizes the labels as geographic locations, it will offer a Map Chart as a Recommended Chart.
All the chart formatting options for Bar Chart are also available for Map Charts.
There are some special map options like:
Map Projection – Automatic seems to choose well. For maps like USA it selects Albers which is best for regions.
Map Area – again Automatic is OK, but try out the options if you’d like something a little different..
Map Labels – defaults to none. All is possible but often very crowded. Best Fit only works well.
Series Color – to change the color display. There are 2-color and 3-color options. Diverging 3-color is good but the defaults don’t give a wide range of shades. We change the ‘Lowest Value’ color to either White or near White. ‘Highest Value’ can be changed to one shade darker.
Pareto Chart
A relatively new chart is Pareto. It’s similar to a Bar Chart but includes a cumulative line showing how the values add up to 100% starting with the largest values.
In this example, we can see the largest values make up almost 50% with many states contributing little to the overall total.
Pie Chart
Another option for this kind of list is the Pie Chart. It’s not on Excel’s Recommended Charts for our example table … for good reason.
For a smaller list of values, Pie Chart can be a good choice, but not when there’s fifty values!
Eight easy statistics in Excel
Understand and use Excel’s Standard Error of the mean
Standard Deviation is easy to use and understand in Excel
Excel’s effect on economic policy
