Today's Dueling Excel Podcast, Episode #1369, Looks at creating Data Bars. in Excel 2010 and earlier versions of Excel. Check out today's Dueling Excel Podcast to learn about the =REPT Function to create a Data Bar set in an early version of Excel.
Transcript of the video:
Bill: Hey welcome back, it’s another Dueling Excel podcast. I’m Bill Jelen from MrExcel joined by Mike Girvin from Excel is Fun and we are live here at Highline Community College in scenic Des Moines, Washington. That's right, Washington.
Mike: Washington? There's the Des Moines in Washington?
Bill: There is.
Mike: They even say it with an S here and I don't think you are supposed to.
Bill: Oh look how embarrassing I am.
Mike: All right, today we are going to take some numbers and make data bars. It's as if we take a histogram and turn it on its side.
So I’m going to use conditional formatting so which makes this kind of like a cell chart.
Now I’m going to copy this format, I’m going to use this button right here and then click right there and then with this range still highlighted, in the active cell I’m going to type an equal sign click on that cell and then Ctrl+Enter to populate all those cells with that formula. Now I’m going to highlight this range and simply use Conditional Formatting. Select Data Bars. Now this came in in 2007, solid fill was in seven and now they have these totally cool gradient ones.
I’m going to use this and look what it does.
Data bars, so the relative position by the height of the bar. If I change this to 4 or 5 it will become the tallest one in the set.
All right, throw it over to you MrExcel.
Bill: Ok so those are really cool if you have Excel 2007 but what if you don't have Excel 2007 and you want to do the exact same thing?
Well there's been a way to do this in the past. It's called the REPT function. The REPT function will take a number or a character and I’m going to use the pipe character, the pipe character and I want to repeat that some number of times and looking at the data here I think I want to repeat it about 10 * the value to the left of me so 10*B2.
=REPT(“|”,10*B2) You know that actually might be too wide so maybe what we want to do is *5.
=REPT(“|”,5*B2) We just have to figure out the right scaling for that and you get your data bars right there. Actually if you have plenty of time you can go through wingdings and wingdings 2 and windings and there's a nice little skinny bar that works a little better for that part but I never take the time to find.
Mike: That’s absolutely beautiful.
Bill: Of course this looks a lot better and is much easier but if you're stuck at a company where they're not upgrading it and you're still back may excel 97 or something like that.
Mike: Or you're a stuffy statistician that doesn't like color and wants to see the official plain bars.
Bill: Ah yes there you go, of course and you can use anything in there I mean you could use--what else would make sense?
Mike: Asterix or something.
Bill: Yeah look at that. Actually that looks better doesn't it? Than the pipe.
Mike: Man the repeat function Bill: sweet rept, repeat function.
All right well hey I want to thank everyone for stopping by we're having a lot of funny here I hope you're having just as much fun we'll see you next week for another Dueling Excel podcast for MrExcel Excel is Fun
Mike: Washington? There's the Des Moines in Washington?
Bill: There is.
Mike: They even say it with an S here and I don't think you are supposed to.
Bill: Oh look how embarrassing I am.
Mike: All right, today we are going to take some numbers and make data bars. It's as if we take a histogram and turn it on its side.
So I’m going to use conditional formatting so which makes this kind of like a cell chart.
Now I’m going to copy this format, I’m going to use this button right here and then click right there and then with this range still highlighted, in the active cell I’m going to type an equal sign click on that cell and then Ctrl+Enter to populate all those cells with that formula. Now I’m going to highlight this range and simply use Conditional Formatting. Select Data Bars. Now this came in in 2007, solid fill was in seven and now they have these totally cool gradient ones.
I’m going to use this and look what it does.
Data bars, so the relative position by the height of the bar. If I change this to 4 or 5 it will become the tallest one in the set.
All right, throw it over to you MrExcel.
Bill: Ok so those are really cool if you have Excel 2007 but what if you don't have Excel 2007 and you want to do the exact same thing?
Well there's been a way to do this in the past. It's called the REPT function. The REPT function will take a number or a character and I’m going to use the pipe character, the pipe character and I want to repeat that some number of times and looking at the data here I think I want to repeat it about 10 * the value to the left of me so 10*B2.
=REPT(“|”,10*B2) You know that actually might be too wide so maybe what we want to do is *5.
=REPT(“|”,5*B2) We just have to figure out the right scaling for that and you get your data bars right there. Actually if you have plenty of time you can go through wingdings and wingdings 2 and windings and there's a nice little skinny bar that works a little better for that part but I never take the time to find.
Mike: That’s absolutely beautiful.
Bill: Of course this looks a lot better and is much easier but if you're stuck at a company where they're not upgrading it and you're still back may excel 97 or something like that.
Mike: Or you're a stuffy statistician that doesn't like color and wants to see the official plain bars.
Bill: Ah yes there you go, of course and you can use anything in there I mean you could use--what else would make sense?
Mike: Asterix or something.
Bill: Yeah look at that. Actually that looks better doesn't it? Than the pipe.
Mike: Man the repeat function Bill: sweet rept, repeat function.
All right well hey I want to thank everyone for stopping by we're having a lot of funny here I hope you're having just as much fun we'll see you next week for another Dueling Excel podcast for MrExcel Excel is Fun
