Learn Excel 2013 – “100% Visualization in Excel”: Podcast #1652

Learn Excel 2013 – “100% Visualization in Excel”: Podcast #1652

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–

22 thoughts on “Learn Excel 2013 – “100% Visualization in Excel”: Podcast #1652

  1. Bill, this is a great trick. It would be nice it is was rounded to to get only complete rows once a single cell is started in a row.

  2. Did you know you can change multiple column widths at the same time by selecting multiple columns and then change the column width of one of them (by dragging ofcours)? You don't have to type a width value in the column width dialog to change multiple column (or row) widths.

  3. Fantastic.
    Built a few of these in a sheet, then copied and pasted as linked picture into dashboard sheet, and hid the sheet with the graphs and the dashboard looks great..
    Thanks Bill…

  4. Hilarious, I was Just doing this today, but not as smartly. The second I saw you copy-pasting those percentage values, I knew where we we're going. DUH. Conditional formatting. Hey, here's a question: ever seen those nested heat-maps – where each block is its own color spectrum? I've poked around w/ Sparklines for Excel, but haven't seen what I'm looking for yet…

  5. Hi Bill,
    you can use conditional formatting only.

    My grid starts in cell B2. The percentage value is in cell B12.
    The Formula calculates the value of each cell and compares it with B12 without typing a ;;; hidden value into that cell. Thanks for your inspiration. As allways…. thousand ways to do the same thing…

  6. Hi Bill, looking amazing. Thank u a lot!   But is there any possibility to make the changes of colors in cells based on total value. For ex. if value  less than 50% the dark color of  boxes(cells) will be red if the total value is more than 50% it becomes green?

Leave a Reply

Your email address will not be published. Required fields are marked *