Category: Excel

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.

How to speed-fill cells in Excel

I spend my life in Excel. Over the years, I came to learn quick shortcuts to make spreadsheet creation faster. There are many tweaks in Excel you can use to speed up spreadsheet creation. Sometimes, creating a spreadsheet is boring because it requires copying mutiple times the same value or same formula. Of course, the good old Copy-Paste works like a charm in most situations but I love the shortcut I am going to show you.

Imagine you want to fill a column or a row with the same value or formula. You can input the value in the first cell and copy-paste it in the other cells, or use the fill handle (the black square at the bottom right of a selected cell). However, this would require to move the hands off the keyboards after having types the content of the cell to use the mouse. Now, what if we could duplicate the content you just typed by pressing just one additional key? Well, this is what the following trick makes possible:

  1. Select first the cells you want to fill (note that the selection is not mandatory adjacent, so you could select multiple cells in various locations of your spreadsheet).
    Select Excel Cells
  2. Now, type the value or formula in the first cell. At that point, you should not hit Enter or any of the arrow keys. This would have the effect of unselecting selected cells.
    Input data in first Excel cell
  3. Fianlly hit Ctrl-Enter to validate your input.
    Hitting Ctrl-Enter fill all the Excel cells with the same value

The value or the formula is automatically copied in every selected cells. Simple and efficient! Ctrl-Enter is one of those keyboard shortcut you need to keep fresh in your mind.