Excel Charting Archives

How To Create A Waffle Chart In Excel.

August 9, 2021 by Barbara

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 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 Formatting
New Rule | Format cells that contain
Format only cells with | Cell Value | Between = 0 | and =$M$5
Select Format and choose your preferred formatting type
I 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 Formatting
New Rule | Format cells that contain
Format only cells with | Cell Value | > $M$5
Select Format and choose your preferred formatting type
Again 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 Formatting
New Rule | Format cells that contain
Format only cells with | Cell Value | = $M$5
Select Format and choose your preferred formatting type
I 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 | Textbox
Select 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?

Video Tutorial On Creating Self Expanding Charts

October 7, 2020 by Barbara

Hello, Excellers. Want to know how to create a Self Expanding Chart. Video tutorials are a great way to learn Excel. If you want to learn Excel you then I invite you to subscribe to my YouTube channels for lots of Tips and Tutorials.

Here is a free tutorial on creating self-expanding charts. It is one tip that readers liked very much so I have recorded a video tutorial. This is a simple and effective this technique is to add new data to Excel charts automatically.

So, in Excel 2007 onwards we can use a new table feature of Excel tables was introduced. By converting your data range to a table, the data for your Excel charts becomes dynamic. This means once new data is added to the table, any charts linked to that table also automatically update also.

Create A Self Expanding Chart.

If you prefer to read the corresponding blog post to this video, then click on the link below.

Create A Self Expanding Chart In Excel

So, if you want more Excel and VBA tips then sign up for my Monthly Newsletter. I share 3 Tips on the first Wednesday of the month. You will receive my free Ebook.

Create A Self Expanding Chart In Excel

September 23, 2020 by Barbara

Hello Excellers, welcome back to another Excel tip today. I will show you how to create a self-expanding chart. This tip is also a YouTube Video. Why not check it out in the link below. So, it is really easy to create a self-expanding chart in Excel. Is it?. YES!. Normally a chart is set up to only display what is currently on the datasheet. But, by a few tweaks and Excel magic, we can set up a chart differently. We can allow a chart to automatically accommodate new data as it is added.

So, in Excel 2007 we can use a new table feature if you need the workbook to be backward compatible with previous Excel 2003 versions you need to do a few more clicks to get the same result. Let’s look at Excel 2007 and Excel 2003 first.

Create A Self Expanding Chart.

To create your self expanding chart in Excel 2007 and Excel 2003 do the following

Create your chart as usual ( you can use the sample data below)
Select any cell in the range that contains data that is used by the chart
Choose Insert- Tables Tables to convert the range to a table

self expanding chart in Excel

Note, if you are using Excel 2003 in Step 3 above choose Data- List-Create List.

Believe it or not, that’s all there is to it!!. Excel recognises the data as a table and that any data that is added to it, is automatically updated in the chart.

So, as you can see in the screenshot above Northern has been added in automatically after I typed it then tabbed along to the Widget Sales ready for the figure to be entered. A great way to create a self-expanding chart in Excel.

Finally, give this charting technique a go, it a good one to have in your Excel magic box of tricks.

Want To Watch The Video?

Sizzling Hot Excel Dashboard Course.

August 12, 2019 by Barbara

I really do not recommend too many Excel products or courses (as you can probably see from my site), and I have a few rules that I apply when paying for either an Excel product or a Course for myself.

That is why I am going to take the unusual step and recommend this Dashboard Course by Mynda Treacy at My Online Training Hub.Com

A few rules I go by (just to let you know) before would recommend any online products to anyone.

1. I always read the reviews first, as many as I can find, I ‘m spending my hard earned money, I want to know what I am getting.

2. Emailing the provider and asking some questions is always good. Did they respond and is there support during and after the course?. This is really important for any questions and clarification points.

3. Is there course materials you can take away?, an option for downloadable files to keep?, and what about any online training videos, can I have them too to keep too please??

4. This is last but maybe the most important. I ALWAYS complete the course myself.

As I said, I really have a few products I would recommend, but I have completed the course myself, and it really is awesome. I am really impressed at the quality of videos and great content for the price it is offered at. There are some really solid design principles and then display principles discussed before you get into how to make your dashboard interactive and automated. Good Stuff. I have learned so much, and have already implemented some changes into my work dashboards.

So, I am really excited that My Online Training Hub.Com has offered a 20% discount on their Excel Dashboard Training Course. They have extended this special price until May 16, 2019, and registration is open until August 15, 2019.

I suggest you have a read more about it right here. Here you will find some FREE WEBINARS. I suggest you attend. Even if you do not take the course. you WILL learn something new.

So Have You Ever Had An Excel Moment Like this?

“My Excel reports take way too long to update each month/week”

“I spend hours collating and cleaning data, updating formulas and charts and then no one reads my reports anyway, what’s the point?”

“If I could just impress the boss I’d get that pay rise/promotion I deserve”

“I need to get my Excel skills up to date so I can stand out from the crowd of other job applicants”

“I’m not sure how to approach setting up my Excel workbook the right way, so it’s easy to build and maintain”

Free Excel Dashboard Webinars

If you’re not sure what a dashboard is, or how these skills might be relevant to you and help you overcome the challenges above, then you can attend one or both of Mynda’s FREE Excel Dashboard Webinars.

Webinar 1 – Excel Dashboard for Excel 2007/2010/2013/2016/2019/Office 365

The first webinar will teach you how to use Excel to build this interactive dashboard below, no additional software or add-ins required, just plain old Excel and some data.

Click here to register for webinar 1.

Webinar 2 – Excel Dashboard for Excel 2010/2013/2016/2019/Office 365 using Power Query and Power Pivot

Excel is evolving; in the last couple of years, Microsoft has added new tools like Power Query and Power Pivot, to name a couple.

Embracing these new tools will not only enable you to get your work done more quickly but also since less than 1% of Excel users know these tools you’re going to have a huge competitive edge in the workplace.

It’s easy to ignore these developments and continue doing things the way you always have, but that won’t get you ahead in your career, but you know that right?

If you have Excel 2010, 2013, 2016, 2019 or Office 365* then this webinar will showcase how you can use Excel’s Power Query tool to get data from multiple sources, mash it up with Power Pivot, analyze it in PivotTables to create this interactive Excel Dashboard.

*Power Query is available with all versions of Excel 2010, 2013, 2016, 2019 and Office 365. Power Pivot is available with the desktop version of Excel 2010 and Excel 2013/2016 Office Professional, Office 2019, Office 365, or in the standalone versions of Excel 2013/2016. Power Query and Power Pivot are not available for Mac.

Click here to register for webinar 2.

What people are saying about the webinars

Over 35,000 people have attended Mynda’s Excel Dashboard webinars and this is what some of them had to say:

“
“Thank you! This webinar has already saved tremendous time and effort. I tried some of the techniques you illustrated for a cumbersome report update that I can see will cut our preparation time by 2/3’s or more. ….awesome! Many thanks!!!!”

Cynthia Tashjian

“
“WOW, just WOW
Can’t type more now – am trying to create a dashboard. Thank you Mynda!”

Susan

“
“The webinar was fantastic! I really enjoyed every moment. As you said, it was fast paced, but I am so excited to go back and review the presentation. We have been trying to get a dashboard started for my department and this really motivated me to take the lead. Thank you so much!”

Kathryn Puskar

“
“Oh. My. God. I know nothing! My jaw dropped a couple of times as you casually mentioned a way to manipulate PivotTable data. That’s the problem with being largely self-taught – there are huge gaps in my knowledge.”

Ann

“
“I presented my first dashboard to the CEO yesterday and he was ecstatic. Since we are downsizing I was very worried about my job, but after seeing his reaction…I understand that my job is secure (for now). Thanks!”

Iris Stein

Why Not Invite a Friend

Feel free to forward this email to your friends and colleagues and invite them to attend Mynda’s free Dashboard webinar too. They’ll thank you for it.

Dashboard Course Format

If you’ve seen Mynda’s free Dashboard Webinars and you’re ready to take your dashboard skills to the next level then check out Mynda’s Online Excel Dashboard course.

It will teach you how to build amazing interactive dashboards that impress your boss, get you noticed and help you stand out from the crowd, like this incredible le Tour de France dashboard she made, which is also included in the course:

The best part, aside from the praise from your boss and colleagues ;-), is you’ll also learn loads of tips you can use in your everyday Excel work to get things done more efficiently.

What to Expect in the course:

- The course is video based and available online 24/7

- It comes with comprehensive Excel workbooks complete with notes

- 5 example Excel dashboards to keep

- There’s also an option to download the videos,

- Plus Mynda personally provides support for the first 6 weeks of the 12-month membership.

By the way, the 6 weeks of personal support is the reason the course is only open for a limited time (registration closes May 23). Mynda limits the number of classes to allow time for other projects and a break every now and again. That’s not to say she won’t support you after the first 6 weeks, it’s just that it might take her a few days or a week to reply to you if she’s out of town or busy working on another course etc.

Dashboards are an incredibly valuable tool in today’s market for consultants, analysts and managers. And when you know the tricks it can be straightforward to build highly professional and interactive Excel dashboards. That’s why this type of training is so worthwhile.

What members are saying about the course:

The previous classes have been a huge hit with many people saying how they love the cool techniques and how they’ve been able to impress their colleagues and clients by using them in all sorts of reports, not just dashboards.

“
“An excellent course, Mynda. You have the gift of making the complex simple to understand.
I thoroughly enjoyed your teaching. I have applied nearly everything taught.

Thank you”

Charlie.

“
“This was my first online training course and I was a bit dubious about how much I was going to learn. I have to say, minute for minute I have learnt more on this course than any other course I have been on. The way the course is delivered is exceptional and the learning material and exercises really cement the learning.

This course has without a doubt furthered my career and has allowed me to secure a permanent job based on the skills I have acquired from this course. I have already signed up to another course!

Thanks”

Ryan Cunningham

I highly recommend the course, but don’t take my word for it. You can read further comments from past students and find out more here.

Good luck and enjoy becoming awesome at Dashboards! Click below to claim your 20% discount.

Dashboards are HOT!

Keep Excelling!

Barbara

Disclosure: I make a small commission for students who join Mynda’s course, but as you know I don’t just recommend anything and everything. It has to be of outstanding quality and value, and something I can genuinely recommend. After all, if it doesn’t live up to what I’ve promised you’ll think poorly of me too and I don’t want that. Oh, and just watching the course videos won’t transform your career, you have to actually put it into practice, but then you know that.

Create A Micro Chart From A Normal Excel Chart

March 22, 2016 by Barbara

Here is a really great way of displaying a micro (or easily resizeable) chart in Excel. It makes use of the Camera Tool. Sometimes hidden, Camera is a tool that is really useful but not that many Excel users either exists or see how they could use it productively.

The Excel Camera tool lets you take a live picture of a range cells (a chart for example) and everytime that range of cells change the picture updates dynamically as well to reflect these exact changes. That could be useful right?.

The Camera tool is not in the usual default Excel Ribbon set up, but it is easily added to the Quick Access Toolbar (QAT) in Excel with a few clicks like this-

Click on the Customise Quick Access Toolbar
Select More Commands
Select All Commands Or Commands Not In The Ribbon
Search for and select Camera
Click Add to add to the QAT
Your Camera Tool is added to your QAT and ready to start snapping those images!

So we have out Camera Tool, let’s start creating some dynamic micro pictures. In my example below I have a simple Pivot Table, and want to take a picture of it to put on an Excel dashboard with limited space, so I want a smaller version of the chart. ie. a micro chart.

Here is my standard Pivot Table. Let’s use Camera Tool to take its picture then reduce its size to put on our dashboard.

Select the cells that you want in the picture. In this case I highlight the chart itself.

Go the Camera Tool and click.
Excel takes a picture of the area.
Click where you the picture to reside, in this case I have put it on the same worksheet as the original to compare the new image and the original.

You can go ahead and resize to a micro chart at this stage.
If you click on the new picture and use the Drawing Tools options, you can edit this image more by adding or removing the border for example.

How To Create A Waffle Chart In Excel.

Tutorial Contents.