Bill was in Washington this week for the Microsoft MVP Summit 2013. He brought back something really cool. Sam Radakovitz - Excel Project Manager at Microsoft - did a presentation that incorporated a '100% Visualization' concept which is the topic of today's Podcast. So, today, in Episode #1652, Bill recreates this technique for us to share and use.
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
Transcript of the video:
MrExcel Podcast is sponsored by Easy-XL.
Learn Excel from MrExcel Podcast, Episode 1652: A Percentage Chart from 100 Tiny Squares.
Hey, I just got back from the MVP summit, and during one of presentations, one of the Excel Project Managers, Sam Radhikas, showed this really cool presentation.
And one of the things that caught my eye, was the series of charts that he was showing.
So, you see, as the percentages down here along the bottom change, the percentage of the chart that's filled changes.
This is kind of like a different take on the thermometer chart, you know, but it was eye catching.
Now, some people are going to say, “Hey, wait a second, you're wasting 120 cells just to show a single number,” but on this dashboard, you know, it looked good.
So, I’d like to ask Sam's permission to show you all how to do it, and he said of course.
So, here's what we do: We take-- start-- with a regular blank spreadsheet; choose 10 rows by 10 columns, so 100 cells; and I'm going to Fill that with a nice light color-- so choose a light yellow here, alright.
And then, we have to add a border to all these cells; so I'm going to press Ctrl+1; and on the Border tab Color, I'm going to choose White as the Color; and then Automatic and Inside.
It’s really important that you put the border-- while Sam was talking, I tried to build my own chart, and without the border it really just doesn't look that good.
Okay, now down here in the lower left corner, we're going to put 1% and then a formula of = that cell + 1%, and we'll copy that across.
Alright, so that gets the numbers from 1 to 10%.
And then = 1% + 0.1, which increments us up 10%, and I'll be able to copy that throughout the whole Range.
Okay, so, now we have those numbers from 1 to 100%.
Good.
I'm going to actually end up hiding those numbers later, but for right now, we'll leave them there so we can see how it's working.
I'm going to select that Range-- actually, we need to have the indicator so this will be some sort of a formula down here that shows the percentage.
I'm just going to use =RANDBETWEEN(1,100)/100.
Alright.
So that gives us our percentage, let's Format it as a percentage.
And I'm going to do something that I tell you never to do: I'm going to use Ctrl+1 I'm going to reluctantly Merge cells.
I tried this with center across selection, and it just doesn't work as well so, you know, there we go.
Change the font color to White, and we'll make it larger, larger, larger, larger, there we go.
Okay, so now that's actually sitting in Cell B13, we're going to select this whole Range of cells; we're going to go to Conditional Formatting-- So Alt+OD for Conditional Formatting; we're going to Create a New Rule; “Use a formula determine which cells to format”-- now this is amazingly simple; B3 is the first cell here, so I'm going to say =B3 --no dollar signs there-- <=$B13 --and we want dollar signs there; and what we're going to do is change the Format to something much darker-- so, I originally used this light yellow, will go to the dark yellow; click OK, and that's all that it takes.
So we're saying, “Hey, if this current value is less than or equal to B13, make it be dark.” So, right now, we have 83%, the first 83 boxes will turn dark yellow.
Click OK.
Alright, so now from here, we're almost done.
I'm going to use Ctrl+1; and on the Number tab; Custom number format of 3 semicolons (;;;), that will hide the numbers so we don't have to see the numbers; and then currently the rows are 20 pixels, so I'll make this one be 20 pixels-- look in the tooltip there, and that is 2.14.
So we'll select that entire range OCW-- Alt+O,C,W-- 2.14, click OK.
So, that way, we have something that looks a little bit square.
And now every time I press the F9 key, it updates and we get the result.
If you like, you can put a title up here, like I call this Manufacturing Line 1, and again, use some formatting there to make it look like a single block: So we could Fill white fonts, larger, and then Merge cells again… actually, you know, this one, since we're not going to be changing this one, let's not Merge these cells… and we will use Horizontal Center Across Selection, click OK.
So there you go, press F9, F9, F9, F9, very interesting-looking, you know, for a dashboard to kind of show progress.
And you can even stack them up, repeating the process to create multiple in a row.
My thanks to Sam Rad for that great trick, and check it out.
Next time you're trying to create a dashboard, use this interesting little-- I'm going to call it percentage chart.
Alright, well I want to thank Sam for that trick, and I want to thank you for stopping by.
I'll see you next time for another netcast from MrExcel.
Learn Excel from MrExcel Podcast, Episode 1652: A Percentage Chart from 100 Tiny Squares.
Hey, I just got back from the MVP summit, and during one of presentations, one of the Excel Project Managers, Sam Radhikas, showed this really cool presentation.
And one of the things that caught my eye, was the series of charts that he was showing.
So, you see, as the percentages down here along the bottom change, the percentage of the chart that's filled changes.
This is kind of like a different take on the thermometer chart, you know, but it was eye catching.
Now, some people are going to say, “Hey, wait a second, you're wasting 120 cells just to show a single number,” but on this dashboard, you know, it looked good.
So, I’d like to ask Sam's permission to show you all how to do it, and he said of course.
So, here's what we do: We take-- start-- with a regular blank spreadsheet; choose 10 rows by 10 columns, so 100 cells; and I'm going to Fill that with a nice light color-- so choose a light yellow here, alright.
And then, we have to add a border to all these cells; so I'm going to press Ctrl+1; and on the Border tab Color, I'm going to choose White as the Color; and then Automatic and Inside.
It’s really important that you put the border-- while Sam was talking, I tried to build my own chart, and without the border it really just doesn't look that good.
Okay, now down here in the lower left corner, we're going to put 1% and then a formula of = that cell + 1%, and we'll copy that across.
Alright, so that gets the numbers from 1 to 10%.
And then = 1% + 0.1, which increments us up 10%, and I'll be able to copy that throughout the whole Range.
Okay, so, now we have those numbers from 1 to 100%.
Good.
I'm going to actually end up hiding those numbers later, but for right now, we'll leave them there so we can see how it's working.
I'm going to select that Range-- actually, we need to have the indicator so this will be some sort of a formula down here that shows the percentage.
I'm just going to use =RANDBETWEEN(1,100)/100.
Alright.
So that gives us our percentage, let's Format it as a percentage.
And I'm going to do something that I tell you never to do: I'm going to use Ctrl+1 I'm going to reluctantly Merge cells.
I tried this with center across selection, and it just doesn't work as well so, you know, there we go.
Change the font color to White, and we'll make it larger, larger, larger, larger, there we go.
Okay, so now that's actually sitting in Cell B13, we're going to select this whole Range of cells; we're going to go to Conditional Formatting-- So Alt+OD for Conditional Formatting; we're going to Create a New Rule; “Use a formula determine which cells to format”-- now this is amazingly simple; B3 is the first cell here, so I'm going to say =B3 --no dollar signs there-- <=$B13 --and we want dollar signs there; and what we're going to do is change the Format to something much darker-- so, I originally used this light yellow, will go to the dark yellow; click OK, and that's all that it takes.
So we're saying, “Hey, if this current value is less than or equal to B13, make it be dark.” So, right now, we have 83%, the first 83 boxes will turn dark yellow.
Click OK.
Alright, so now from here, we're almost done.
I'm going to use Ctrl+1; and on the Number tab; Custom number format of 3 semicolons (;;;), that will hide the numbers so we don't have to see the numbers; and then currently the rows are 20 pixels, so I'll make this one be 20 pixels-- look in the tooltip there, and that is 2.14.
So we'll select that entire range OCW-- Alt+O,C,W-- 2.14, click OK.
So, that way, we have something that looks a little bit square.
And now every time I press the F9 key, it updates and we get the result.
If you like, you can put a title up here, like I call this Manufacturing Line 1, and again, use some formatting there to make it look like a single block: So we could Fill white fonts, larger, and then Merge cells again… actually, you know, this one, since we're not going to be changing this one, let's not Merge these cells… and we will use Horizontal Center Across Selection, click OK.
So there you go, press F9, F9, F9, F9, very interesting-looking, you know, for a dashboard to kind of show progress.
And you can even stack them up, repeating the process to create multiple in a row.
My thanks to Sam Rad for that great trick, and check it out.
Next time you're trying to create a dashboard, use this interesting little-- I'm going to call it percentage chart.
Alright, well I want to thank Sam for that trick, and I want to thank you for stopping by.
I'll see you next time for another netcast from MrExcel.
