Simple Sectional Chart
Imagine that you need to visualize for a report or presentation the following table with sales data in different regions-branches of the company:
The first thought is usually, not particularly bothering, to highlight the columns with cities and numbers and build a regular histogram. It usually turns out something like this:
The result, as it is easy to figure out, does not shine with clarity. Cities merge into a single whole, making it very difficult to perceive and compare countries with each other. How to improve the picture?
To begin with, it is better to transform the original table a bit:
What changed:
- Country names from column A are copied to column headings
- Data from one common column is posted to different ones – each country is now separate
It seems to be trifles, but if we now select the entire table (A1: G20) and build a stacked histogram on the tab Inset — Histogram (Insert — Stacked Chart), the output picture is quite different:
The city columns of each country are now different data series for the chart and are displayed in different colors accordingly. And highlighting countries along with cities gives us beautiful grouped two-level x-axis labels.
Chart with horizontal columns (Лrimmed or Bar Chart) also looks nice in this version:
Similarly, you can easily visualize the dynamics, for example, by year for different products:
Summary Section Chart
If your original table does not contain final totals, but individual transactions and is more like this:
… then you will first need to build a pivot table of a similar type, and only then build a chart on it.
Let’s go for a little trick: duplicate the column with countries and give it a name, for example, Page 2 (because Excel does not allow you to use the same column names when building PivotTable reports to distinguish columns from each other):
Now let’s build a pivot table according to our data on the tab Insert – PivotTable (Insert — Pivot Table):
- Country и City put in the line area (Row Labels)
- Page 2 to column area (Column Labels)
- Sales – to the range (Values)
As you might guess, at the output we get the same type of table we need with data “steps” for each country. Just go to the tab Parameters (Options) or Analysis (Analyze) and build a stacked histogram similar to those described above:
- How to build a bar chart with variable width columns
- What is a tornado chart and how to build it in Excel
- Several options for constructing plan-fact charts