A few weeks ago, a reader asked a question in our forum. Unfortunately, I can’t find this question! Nevertheless, I’ll answer it in this article. The reader was wondering how to create a bar chart that was both clustered and stacked in Excel.
When to use both clustered and stacked charts?
You might need both a clustered and a stacked chart when you want to compare two series of data, but each of them is made up of different elements. For example, here we’re going to compare a company’s current sales and budgeted sales (clustered chart), but each of these amounts must also be illustrated by subsidiaries (stacked chart), for 3 separate years.
Preparing data for both clustered and stacked charts
To do this, you need to prepare your initial data carefully. Ideally, the initial data will be the result of automated formulas you’ve set up, to avoid manual work when updating the data. In the end, your original data should be structured as shown below. The idea is to create 4 separate sets of data, while taking care to present the Current data with the same line label and the Budget data with the same line label (but this line must not be the same for Budget as for Current). In doing so, Excel will understand that it needs to plot 4 series of data and that side-by-side data can be stacked.
Vous devez consolider des données, les analyser, les interpréter et présenter des conclusions utiles pour des fins de prise de décision ? Apprenez à développer des solutions robustes et automatisées avec nos formations en Tableaux de bord avec Excel. |
|---|
Creation of both clustered and stacked charts
If you opt for the clustered graph, you’ll get this:
We recommend starting with a stacked graph:
Be careful not to use the line labels (the years) when creating your graph. Instead, add them later, via the “Select data source” menu. Note that Years could also be placed on other rows of the table. It’s up to you to decide which location you prefer.
Refinement of the charted graph, both clustered and stacked
Then all you have to do is format your graph. First, make sure you reduce the width of the inverval between your data series to 0%.
You’ll get a result like the following:
All you have to do is modify the format to make your data easier to understand. Note below that I’ve intentionally used green for Branch 1 (F1) and blue for Branch 2 (F2). However, the color is dark for the Current and pale for the Budget. This makes it easier to understand the data.
VIP support file for download
To download the file used in this tutorial, become a VIP member of CFO Masqué.
Are you interested in this kind of technique?
We cover a wide range of techniques for creating relevant dashboards in Excel, in our training courses:
Here are a few comments from learners who have taken the Excel – Dashboards (Level 1) course:
La mission du CFO masqué est de développer les compétences techniques des analystes et des contrôleurs de gestion en informatique décisionnelle avec Excel et Power BI et favoriser l’atteinte de leur plein potentiel, en stimulant leur autonomie, leur curiosité, leur raisonnement logique, leur esprit critique et leur créativité.
