How to create a waffle chart in Excel
Waffle charts are useful for visualising certain kinds of data. Like edible waffles, they are divided into sections, each representing something or a percentage of the whole.
Unfortunately, they are not a native part of Excel's chart offerings, but there is a workaround.
In the video example, data is sourced from https://lnkd.in/euwCkjir, containing the composition of the UK Parliamentary House of Commons. A table in A1:B17 is set up to house each party along with their number of seats.
From this point, the steps are:
1️⃣ Enter =𝚂𝙴𝚀𝚄𝙴𝙽𝙲𝙴(𝟺𝟺,𝟷𝟻) in D1 to create a 44-row and 15-column array from 1 to 660.
2️⃣ Select the range and go to Home ＞ Conditional Formatting ＞ Manage Rules.
3️⃣ Select New Rule ＞ Use a formula to determine which cells to format.
4️⃣ Enter =𝙳𝟷﹤=𝚂𝚄𝙼($𝙱$𝟸:$𝙱$𝟸) as the rule formula to check if each cell value is less than or equal to 352 (Conservative seats).
5️⃣ Select Format and choose blue as the fill colour.
6️⃣ Select OK twice to confirm the rule.
7️⃣ Select Duplicate Rule to use the first as a template for the 14 other parties.
8️⃣ Change the colour accordingly, and in the formula =𝙳𝟷﹤=𝚂𝚄𝙼($𝙱$𝟸:$𝙱$𝟸), the last cell in the range reference must bump up by one row for each new rule. E.g. $B$2, $B$3, $B$4.
9️⃣ Once finished, select OK to confirm the rules.
🔟 Press Ctrl + 1 to open the Format Cells window. On the Number tab, create a new custom format of ';;' to hide the waffle chart numbers.
For greater flexibility, you can turn the waffle chart into an image. Select and copy it (Ctrl + C), right-click elsewhere, hover over Paste Special and choose Linked Picture.
You're now able to freely move, resize, and rotate the waffle chart!
#exceleration #excel #microsoftexcel #excelformulas #exceltips #exceltricks #globalexcelsummit
---
The Global Excel Summit is the world's largest gathering of Microsoft Excel users and experts.
Learn more at https://globalexcelsummit.com/.
Find us on:
👔 LinkedIn: / global-excel-summit
🐤 Twitter: / excelsummit
🟦 Facebook: / globalexcelsummit
📸 Instagram: / globalexcelsummit
🤳 TikTok: / globalexcelsummit