In Episode 1150, I wanted to add data bars to a series of words. Episode 1150 shows you how.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Basically, you start out with massive amounts of data, and say “How we're going to analyze this?” Well, let's fire up a Pivot table and see if we can solve this problem!
Hey alright, welcome back, it’s another MrExcel netcast, I'm Bill Jelen.
Working with this new data bar concept in Excel 2007, I have some topics there, and some percentages, I tried the data bar.
You know, it just wasn't what I wanted, I wanted to actually have the data bars up here on the words, not on the numbers.
I didn't think this was a good way to go and, you know, there's really nothing you can do if you make the numbers too small, it's not going to overwrite.
So I came up with this idea, I said “You know, what if we would go to that number and Ctrl+1 to Format Cells, create a custom number format, where I actually don't have any number showing up at all?” In quotes I put, for example, the word “Intro”.
Alright now, we have 0.5 in that cell, but it's displaying Intro, see where I'm headed with this?
I'm going to choose the rest of these cells or a quick little macro here, three lines!
For Each cell In selection, we're going to go to the cell, one column to the right, so the Offset, set up a number format within quotes, the cell.Value.
Now I have to put 4 quotation marks there, that's the VBA way to get a single quote to appear.
First quote says “We're starting a literal, next two quotes puts a quotation mark in, and then finally that last quote ends it.
So, let's run that macro, we'll go back and check this out.
Now we have numbers over there in column B, but instead of having numbers, the words actually appear, so let's left-justify that.
I can actually get rid of all the words back here in column A, control… I'll just press Delete, and then I can build this into a nice little block of, maybe, 10 rows by 4 columns, so, let's go grab data starting there, Ctrl+X Ctrl+V, good, Ctrl+X Ctrl+V, and then Ctrl+X Ctrl+V. Alright, so now this looks like a block of words, but when I apply the Conditional Formatting, the conditional formatting is going to use the numbers underneath, alright, check that out.
Very cool way to build a nice, concise, list of topics, get a data bar on each one, and we can see how much change we have in those various topics.
I think, a much better way than this presentation here, which is going to take up a whole page and, really, people's eyes are going to glaze over a lot of information being communicated in a very small space here.
Using a custom number format to replace the numbers with the actual words, to allow the data bar to go.
Hey, want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
Well thanks for stopping by, we'll see you next time for another netcast from MrExcel!
Basically, you start out with massive amounts of data, and say “How we're going to analyze this?” Well, let's fire up a Pivot table and see if we can solve this problem!
Hey alright, welcome back, it’s another MrExcel netcast, I'm Bill Jelen.
Working with this new data bar concept in Excel 2007, I have some topics there, and some percentages, I tried the data bar.
You know, it just wasn't what I wanted, I wanted to actually have the data bars up here on the words, not on the numbers.
I didn't think this was a good way to go and, you know, there's really nothing you can do if you make the numbers too small, it's not going to overwrite.
So I came up with this idea, I said “You know, what if we would go to that number and Ctrl+1 to Format Cells, create a custom number format, where I actually don't have any number showing up at all?” In quotes I put, for example, the word “Intro”.
Alright now, we have 0.5 in that cell, but it's displaying Intro, see where I'm headed with this?
I'm going to choose the rest of these cells or a quick little macro here, three lines!
For Each cell In selection, we're going to go to the cell, one column to the right, so the Offset, set up a number format within quotes, the cell.Value.
Now I have to put 4 quotation marks there, that's the VBA way to get a single quote to appear.
First quote says “We're starting a literal, next two quotes puts a quotation mark in, and then finally that last quote ends it.
So, let's run that macro, we'll go back and check this out.
Now we have numbers over there in column B, but instead of having numbers, the words actually appear, so let's left-justify that.
I can actually get rid of all the words back here in column A, control… I'll just press Delete, and then I can build this into a nice little block of, maybe, 10 rows by 4 columns, so, let's go grab data starting there, Ctrl+X Ctrl+V, good, Ctrl+X Ctrl+V, and then Ctrl+X Ctrl+V. Alright, so now this looks like a block of words, but when I apply the Conditional Formatting, the conditional formatting is going to use the numbers underneath, alright, check that out.
Very cool way to build a nice, concise, list of topics, get a data bar on each one, and we can see how much change we have in those various topics.
I think, a much better way than this presentation here, which is going to take up a whole page and, really, people's eyes are going to glaze over a lot of information being communicated in a very small space here.
Using a custom number format to replace the numbers with the actual words, to allow the data bar to go.
Hey, want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
Well thanks for stopping by, we'll see you next time for another netcast from MrExcel!
