Microsoft Excel is one of the most versatile tools for data analysis and data visualization. Since its initial release in 1987 (about 35 years ago), Excel has remained the industry-standard spreadsheet tool and its application is pervasive across all industries. I literally do not know of any company that doesn’t use Excel or at least Google Sheets, but that’s another story. In this data digest tutorial, I will be showing you how to extend the data visualization capabilities of Excel to design novel charts that will improve your storytelling.

If you prefer to watch me viz, this video details how to arrive at the final custom visuals. Otherwise, you can follow the step-by-step tutorial below.

Instructor: Tobi Williams Babatunde (LinkedIn)

In this tutorial, you will learn how to design three (3) charts to supercharge your data storytelling in Excel. These data visualization hacks include:

1. Percentage (%) of total
2. Highlight the largest value in a sample or population chart
3. Multilayer line chart

To follow along easily with the creation of these fantastic charts in Excel, you can download the dataset here.

📁 Download dataset

1. Percentage of total chart in Excel

The final visual you will create in this tutorial looks like the image below. I find this chart very cool because it can easily illustrate the information that you’re trying to pass across. You have to think deeply about the end goal - begin with an end in mind - before applying this hack to your visual.

For my use case, I want to show the amount of energy that has been used up out of a given, fixed volume available within a month. The opposite of this is the amount of energy remaining. They both mean the same thing but we’ll focus on the first definition.

Dataset

Let’s begin with the dataset. The dataset for this simply contains the:

1. month in which the energy was used
2. energy used within the month
3. total available energy
4. percentage of energy used up which is gotten by dividing energy used by energy available.

Assets

You will require some assets - additional icons, preferably PNG or SVG format - to pull this magic trick. Because the message I’m trying to pass across is energy-related, what better way to easily draw the audience’s attention than with a battery? In my case, I designed the battery icons in PowerPoint and exported them to PNG format. You can choose to use any image editing or graphic software such as Figma or Adobe Illustrator.

The assets are separate. The first is the battery shell which represents the energy available. This is rigid and doesn’t change shape. The second represents the energy used up which is dynamic and moves up or down.

Now that you have an understanding of the dataset and the assets required to make this work, let’s jump into designing the visuals that will supercharge your data storytelling in Excel.

Designing the percentage of total chart

a. Create a pivot table

We begin by creating a pivot table using all the fields in the dataset except the month. The month will be used in a slicer to filter the data.

Ensure to add all the fields as measures to the values pane. Then change the value field settings/aggregation to average in all instances.

So now you have a single row pivot table that needs to be filtered by the month. Ensure you’re selecting the pivot table so that the PivotTable Analyze options show up. From here you can insert a slicer for the month field.

Ensure that the month slicer accurately filters the pivot table.

b. Create a clustered column chart

You’re more than halfway there. Using the pivot table created, the next step is to insert a PivotChart. You want to select the clustered column chart.

Ensure to uncheck all the options in the field buttons to reduce cluttering the visual. You should also delete the legend as that will not be useful for this custom visual.

c. Copy and paste assets

The next thing you want to do is to copy and paste the assets. Simply copy the battery asset (in my case), click on the energy available bar (the blue bar), and paste using Ctrl + V keyboard combinations.

Repeat this process for the energy available (orange) bar chart.

d. Format data series

The final step is to right-click on the chart and click on the Format Data Series option. In this menu which appears on the right-hand pane, you want to set Series Overlap all the way to 100%.

Voila! And that does the trick.

Note: In case the arrangement of the asset causes the energy used up bar to appear behind the energy available bar, you can simply drag the measures in the values pane of the PivotTable field to rearrange them accordingly.

e. Add data label and title

The final thing to do to really create a knock-off visual is to add your dynamic data label. You want to add this to the energy used up bar chart.

Ensure you select the energy used up bar, then click on the “+” icon at the top right of the chart. Navigate to data labels and select “More Options”.

You want to select “Value From Cells” as the only label option. Ensure to point to the PivotTable field that contains the % energy used as the range for this.

After this, you should add a title, perhaps change your font and size accordingly, and that’s pretty much it. My final visual looks like this.

2. Highlight the largest value in a population

I like this twist to a bar chart because it can easily light up your visualization and storytelling in Excel. Imagine giving a presentation to your sales team and you want to highlight the top sales performers in a striking way without employing the help of a graphics designer. This Excel hack definitely fulfills that requirement.

The final visual that highlights the largest value in a chart in Excel looks like this. Here we’re highlighting the top sales agent using the star above the bar and also differentiating using the colour of the bar. You can’t miss identifying the top agent!

Dataset

The dataset for my specific use case is a sales dataset containing aggregated monthly sales figures for each sales agent.

Assets

I will take it that you’re now familiar with how assets work and the format that you require them in - SVG or PNG. I am going to use a star for this tutorial. In the corresponding video, a light bulb was used. Feel free to tailor your asset to your specific use case.

Designing the Excel chart - highlight highest value in a population

a. Create a pivot table

We also begin this process by creating a pivot table and inserting a slicer on the month field. The measure in the values will be the Sum of Sales so that when you filter by month, it will show the accurate amount of sales for each agent by month.

b. Create auxiliary calculation table

Creating this table is very important because it is from this table that the chart will be designed.

Sales Agent: This field is simply pointing to the Row Labels in the pivot table. I just typed “=” and clicked on “Angela” in the pivot table then copied the formula down until “Shola”.

Sales: Again, I pointed to the Sum of Sales field in the pivot table, however, I made a small edit to the formula.

If you point directly to the sales value, you will get this formula:

=GETPIVOTDATA("Sales",$K$3,"Sales Agent","Angela")

This fixes the value on “Angela” as a sort of absolute reference. If you copy the formula downwards, you will notice that the sales amount doesn’t change. Instead of fixing on “Angela”, I edited it to the cell reference of the sales amount.

=GETPIVOTDATA("Sales",$K$3,"Sales Agent",N4)

Now, if you copy the formula downwards, you will get the accurate sales figure for each agent.

Topping: This field is meant to be a placeholder for the star asset. We simply want to divide the sales amount by 4. Note that this value is arbitrary and I chose to divide by 4 after trial and error to find what best fits.

= Sales / 4

Highlight max: This formula is meant to highlight the maximum value(s) within the sample dataset.

=IF(MAX($L$4:$L$8)=L4,L4,"")

Note the absolute reference on the sales range $L$4:$L$8. Your range might have different cell references but it should point to the sales amounts.

Max topping: Here, we divide the highlight max by 4, similar to what we did with the topping field. We go further by wrapping the formula around an error handler IFERROR.

=IFERROR(Highlight max/4,"")

Nice! Now we’re done with the formulas. Before you design the chart, confirm that when you change the value in the month slicer, the values in the auxiliary table also change.

c. Design combo stacked column chart

You should begin by selecting the Sales Agent, Sales, and Topping fields and inserting a stacked column chart.

Remember I mentioned that the topping field is only a placeholder? Well, at this point you can select the topping series and remove the colour.

The best way to select an entire series in the chart is to select the chart, then from the format pane, locate the drop-down in the chart area and select the topping series. Of course you can click directly on the orange bar in the chart but it’s possible to run into some issues sometimes.

After select the topping series, change the colour to “No fill”.

Next you want to add the other series. From the chart design menu, click on “Select data” and add new series.

Here you want to add the “Highlight max” and “Max topping” as series to the chart. Also ensure to change the horizontal (Category) Axis Labels to match the initial series.

After adding the new series, the chart should look like this.

From here you want to change the chart type. From the chart design menu, click on “Change Chart Type” and configure to a combo chart with the following parameters. You can also right-click on the chart to access the change chart type menu.

Note that the “Highlight max” and “Max topping” series are on the secondary axis. This aspect is very important. You’re nearly done at this point.

d. Add icon asset to the Excel chart

The final tweaks to the chart involve copying the star asset (in my case) and pasting on the “Max topping” series. I also changed the colour of the “Highlight max” series; added a chart title, removed gridlines, and included data labels.

My final visual looks like this.

3. Multilayer line chart in Excel

The multilayer line chart is one of my favourites because it can really bring to life the story you’re trying to tell in Excel. The hack doesn’t involve any assets or external resources, just good ol’ Excel.

The final visual looks like this.

Dataset

The dataset for this contains daily sales data aggregated in a cumulative way. The cumulative sales allow us to show whether sales figures are growing over time or remaining stable. Two other fields are event-based fields and can be seen as binary i.e. whether the event happened or not.

Promoter sale: Here I want to indicate whether a sale was made on that day by a promoter. It’s possible that other types of agents other than promoters are making sales.

New plans introduced: Here I want to show the days on which a new payment plan for sales was introduced.

You will notice that the values for the promoter sale and new plans introduced are the same as the cumulative sales. We’re simply referencing the sales values on the days when these events occurred. For this hack, the structure of the dataset is probably the important thing to understand.

Create multi-line chart in Excel

a. Create a line chart

Begin by creating a line chart with the date and cumulative sales fields. Increase the thickness of this line chart considerably and optionally change the colour.

Then add promoter sales and new plans introduced as new series to the same line chart while keeping all the values on the same date axis. No need for a combo chart here.

For the promoter sales, change the series to a marker and remove the line. I chose a circle market and changed the colour.

While for the new plans introduced series, I also used a marker in place of a line. I changed the marker type to “X”. You can think of this process as layering.

The cumulative sales is the base layer; the line weight is very thick. Followed by the promoter sales and finally the new plans introduced field. And that’s pretty much it.

b. Format chart and design legend

Don’t forget to format your axes titles and include a chart title.

Oh, one final thing. You need to create the legend by inserting a circle object and a “X” object. Change the colours accordingly and add a text box to indicate what the markers mean.

We do hope that you found this episode of the Data digest series exciting and insightful, for more access to such quality content, kindly sign up to the Resa platform by clicking here.

Thank you for learning with Us!