Excel 2007 offers a new visualization called an Icon Set. I usually hate this feature, because my numbers are right justified and the icons are left justified. However, in Episode 879, Joanna from Columbus, Ohio offers a cool tip to get the numbers and icons next to each other.
Transcript of the video:
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Boy, a great tip today from Joanna-- Joanna in Columbus, Ohio.
I always talk about on the new Excel-- Excel 2007-- the data bars, which are cool; the color scales, which I like; but then I'm never big on the icon sets.
And the reason that I don't like the icon sets is they always left-justify the icon.
So right here, this green actually applies to the 897 number instead of the 915 number.
And so you have some pretty high numbers here like the 862 that appears to have a red icon next to it, and there's no way to get that icon off the left edge.
And so I always complain that I have to either Center my numbers-- which I despise centering the numbers, they just look horrible.
And I was doing a seminar about Excel 2007, and Joanna raised her hand.
She said, "Well, wait a second, there's a setting called Right Indent." And I don't know how I'd missed this, but sure enough, if we go into Format Cells, there are settings here to use right-indent and then we can control the number of characters we want to indent.
So if I take those numbers, and then indent them a certain number of characters-- maybe three-- what happens is, I move the numbers over.
Now, they still continue to justify against some imaginary right edge, and so it still looks like they're right-justified but the number now appears next to the label.
So using the number of characters to right-indent will now allow the numbers to kind of float over closer to the icons, and actually create a very nice visualization of the data with the traffic lights and the numbers right next to each other.
So I want to thank Joanna for that great idea-- using the Right Indent right there under Format Cells.
That's been there all the time, I guess never really realized exactly what it did-- but a great little trick.
And I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
Boy, a great tip today from Joanna-- Joanna in Columbus, Ohio.
I always talk about on the new Excel-- Excel 2007-- the data bars, which are cool; the color scales, which I like; but then I'm never big on the icon sets.
And the reason that I don't like the icon sets is they always left-justify the icon.
So right here, this green actually applies to the 897 number instead of the 915 number.
And so you have some pretty high numbers here like the 862 that appears to have a red icon next to it, and there's no way to get that icon off the left edge.
And so I always complain that I have to either Center my numbers-- which I despise centering the numbers, they just look horrible.
And I was doing a seminar about Excel 2007, and Joanna raised her hand.
She said, "Well, wait a second, there's a setting called Right Indent." And I don't know how I'd missed this, but sure enough, if we go into Format Cells, there are settings here to use right-indent and then we can control the number of characters we want to indent.
So if I take those numbers, and then indent them a certain number of characters-- maybe three-- what happens is, I move the numbers over.
Now, they still continue to justify against some imaginary right edge, and so it still looks like they're right-justified but the number now appears next to the label.
So using the number of characters to right-indent will now allow the numbers to kind of float over closer to the icons, and actually create a very nice visualization of the data with the traffic lights and the numbers right next to each other.
So I want to thank Joanna for that great idea-- using the Right Indent right there under Format Cells.
That's been there all the time, I guess never really realized exactly what it did-- but a great little trick.
And I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
