2 simple steps to represent a hierarchy in Excel

With the advent of Big Data and large data tables, we are sometimes faced with the challenge of representing hierarchical data that makes sense. Take for example the table below

You can create a pie chart to represent the sales in the four regions, however, having both regions and subregions in the same pie chart may prove challenging with the pie chart proposed in Excel. Excel 2016 proposes a new set of charts called hierarchy charts, starting with the treemap.

The Treemap

The treemap is a classic to people in Business Intelligence as it’s a simple way to represent a hierarchy and have a view of the top hierarchy members to each other as well as the child members relative to each other with the same parent. Take the above table and turn it into a treemap and you obtain the below:

Creating a hierarchy chart

To create a treemap, follow these 2 simple steps as for any other charts:

  1. Select all the data, including the title row
  2. Click on Insert, chose Insert Hierarchy Chart, and click Treemap.

You can customize your treemap by displaying values, hiding legends, etc. like any other charts.

The Sunburst

Now, if you like pie charts and want two pie charts (or more) in one, the Sunburst is your friend. Same procedure to follow as for the Treemap, but chose Sunburst instead. Now, in the example below, based on the same table, regions are in the inner circle and subregions in the outer circle.

Both charts work pretty well with any hierarchy, and are particularly well suited for extracts from databases where generally, information is flat and repetitive row from row. For instance, each row could represent a sale with the name of the sales person, the product sold, the name of the customer, the city, and the amount. You can easily create a hierarchical chart by sales person, city and product for instance or move things in reverse order. A great way to present better your next sales meeting.

Leave a Reply