Replace a bar chart with in-cell formulas
Transcript of the video:
MrExcel podcast is sponsored by Excel-XL.
Excel Charts Chapter 9; Repeat Charts.
Hey, welcome back to MrExcel. I'm Bill Jelen.
This is how to create a chart without using a chart at all.
All right now, I know that Excel 2007 introduced the Data Bar concept.
We're going to go really old school here, I wanna create a nice visualization of these numbers.
Here's what I'm gonna do, I'm use equal REPT, this function repeats certain text, a certain number of times.
All right, so I'm going to use the pipe character, the pipe is that thin little vertical bar and how many times I want to repeat it, well, I want to repeat it, the number, so 56 in this case.
Copy that down, what we get is a nice little Bar Chart that is appearing right there in those cells, so now we have a cool Bar Chart.
Now, this works out because my numbers are you know small, if I had larger numbers, here let's change these to be just, we'll do equal RANDBETWEEN, perhaps 200 and 1000.
Now we're gonna have really, really big numbers, it's not going to work out so well, so then in that case, you'll want to use this function but then you do B2 and divide something, so divide, you know, maybe 50, and kind of scale things down and then that will create the correct size Bar Charts.
You can also play with different things.
I've seen Mike Alexander, you know find a, a nice font in Webdings or Wingdings, that kind of provides, you know a thicker Bar.
It actually makes it look like at a solid Bar.
So go through various items there.
So you know this is when we don't even have a chart, we just simply use a formula to...
Hey, I want to thank you for stopping by.
See you next time for another netcast from MrExcel.
Excel Charts Chapter 9; Repeat Charts.
Hey, welcome back to MrExcel. I'm Bill Jelen.
This is how to create a chart without using a chart at all.
All right now, I know that Excel 2007 introduced the Data Bar concept.
We're going to go really old school here, I wanna create a nice visualization of these numbers.
Here's what I'm gonna do, I'm use equal REPT, this function repeats certain text, a certain number of times.
All right, so I'm going to use the pipe character, the pipe is that thin little vertical bar and how many times I want to repeat it, well, I want to repeat it, the number, so 56 in this case.
Copy that down, what we get is a nice little Bar Chart that is appearing right there in those cells, so now we have a cool Bar Chart.
Now, this works out because my numbers are you know small, if I had larger numbers, here let's change these to be just, we'll do equal RANDBETWEEN, perhaps 200 and 1000.
Now we're gonna have really, really big numbers, it's not going to work out so well, so then in that case, you'll want to use this function but then you do B2 and divide something, so divide, you know, maybe 50, and kind of scale things down and then that will create the correct size Bar Charts.
You can also play with different things.
I've seen Mike Alexander, you know find a, a nice font in Webdings or Wingdings, that kind of provides, you know a thicker Bar.
It actually makes it look like at a solid Bar.
So go through various items there.
So you know this is when we don't even have a chart, we just simply use a formula to...
Hey, I want to thank you for stopping by.
See you next time for another netcast from MrExcel.
