Excel Tip Category Archives

Hello, Excellers. Welcome to another blog post in my Excel series. Today I will show you how to create a fun Excel Waffle Chart. Unfortunately, Excel does not have a Waffle chart in the charting menu. However, it is straightforward to develop in a few steps. Once created, you can re-use the framework over and over again. Tutorial Contents.What Is A Waffle Chart?. An Introduction.What Can I Use The Chart For?Create The Waffle Chart Framework.Create Metric Cell.Apply The Conditional Formatting.Format Cells Less Than Or Equal To The Metric cell.Format Cells Greater Than The Metric Cell.Last Formatting Part- Format The Cell That Is Equal To The Metric Cell.Using Text Boxes To Finish The Look.Multiple Charts Or Multiple Series On The Waffle Chart?.So, What Next? Want More Tips? What Is A Waffle Chart?. An Introduction. A waffle chart is a great and straightforward representation of progress towards a goal. Commonly used to display simple metrics, it is a ten by ten grid, and the whole chart represents 100%. Each square in the grid represents 1% of the entire chart. Below is an example of a waffle chart. The chart is dynamic as it will change as the metric is changed. These waffle charts are straightforward to read and interpret by users. I will show you how to build the waffle chart below. It's cute, isn't it?. What Can I Use The Chart For? Waffle charts can be used to visually display in Excel reports or dashboards how far from a metric is away from a target. They are easy to create, maintain and interpret. If you have a few metrics to display you can also set them side by side to create a great visualization of your metrics on an Excel report or dashboard. They really are versatile. Create The Waffle Chart Framework. The first stage in the process is to create the Waffle framework. It makes up the ten by ten cell grid to give us 100 Excel cells within the chart. The height and width should be the same. The cells should run from 1% to 100%. A speedy way to populate these cells is to complete the first two rows, go ahead and select both rows. Then, finally, drag the numbers to the top of the grid.. Create Metric Cell. Next, a metric cell is required. This cell will contain the value of the target achieved, which will be linked to the chart later on in the process. In my example, I have selected cell M5 to contain the percentage metric. I have also prepared some of the data labels to be near the metric to be used for data labels later in the process. These are "Region" and "of Target Achieved". Apply The Conditional Formatting. This is where the magic starts to happen. Or, where the conditional formatting is applied to the framework created in the previous step. There are two parts to the formatting. The cells that are less than or equal to the metric cell, and the cells that are greater than the metric cell. Finally, the cell in the waffle grid that represents the actual metric value will be conditionally formatted to have some star quality and stand out from the Waffle chart grid. Format Cells Less Than Or Equal To The Metric cell. So, the conditional formatting is simple. Select the whole Waffle chart grid. Then follow the steps below. Home Tab | Styles Group | Conditional FormattingNew Rule | Format cells that containFormat only cells with | Cell Value | Between = 0 | and =$M$5Select Format and choose your preferred formatting typeI have formatted the cell colour the same as the font colour, which nicely hides the grid Format Cells Greater Than The Metric Cell. Home Tab | Styles Group | Conditional FormattingNew Rule | Format cells that containFormat only cells with | Cell Value | > $M$5Select Format and choose your preferred formatting typeAgain I have I have formatted the cell colour the same as the font colour, which nicely hides the grid Last Formatting Part- Format The Cell That Is Equal To The Metric Cell. I believe that this last part of the conditional formatting takes the waffle chart to a new level. The waffle chart gets a little pop by formatting the cell that contains the same value and the metric. So, follow the steps below. Home Tab | Styles Group | Conditional FormattingNew Rule | Format cells that containFormat only cells with | Cell Value | = $M$5Select Format and choose your preferred formatting typeI selected to format the font white in the cell, which matches the metric value Using Text Boxes To Finish The Look. To finish the waffle chart, you can add a text box or two to give the user more information. I have created three text boxes that link to the cells that contain the metric data. In this example, the first text box links to cell M4, which contains the Region name. The second text box I have connected to cell M5, which displays the current metric. The final text box links to cell M6 with clarification to the user the percentage is versus target. To link a text box to a cell in Excel is simple. Follow the steps below. Insert your Text Excel workbook, Insert | Text | TextboxSelect the text box then in formula bar type = and then the cell reference you need Now the text box references the cell. If the value or contents of the cell change, so does the text box. Multiple Charts Or Multiple Series On The Waffle Chart?. Waffle charts are a great way to visualize multiple metrics. Just align a set of them together to create a concise Excel dashboard. Create copies of your first Waffle chart and align your formatting and text cell links to the new metrics. Job done. So, What Next? Want More Tips? Create A Yes No Message Box Before Running An Excel Macro.Change The Default Chart Type In ExcelRe-Order Data Series In An Excel 2013 ChartExcel Video TutorialsMake All Of Your Excel Charts The Same Size Finally, if you want more Excel and VBA tips then sign up for my Monthly Newsletter where I share 3 Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips and check out all of my Formula Friday Blog posts below.

How To Excel At Excel

Tag: Excel Tip Category