Mark sends in a question about why Excel underlines the entire cell for currency, but only the digits for percents. Well decode this problem in Episode 618.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Let's take care of a few open items that we have here.
First of all I received a note from Joe Todd up in Amherst.
He says, he and his students watch the podcast every day.
So, I want to send a shout out to all the Steele NCT Students.
Wanna thank you for stopping in and I asked Joe.
If that you had any particular questions, he says no they just love everything.
So, we're good there, also probably week and a half ago I was over in visual basic, and I was very frustrated that I had managed to un-dock these two toolbars and I went through a very long convoluted way to get them re-docked.
I got a note from Jeff Shroff.
Jeff said hey, you completely missed it.
All you have to do is double click, double click on the title bar and they all go back to where they were.
We had a whole room of very smart people and no one could figure that out. So, thanks to Jeff.
I'm sending him one of my Excel master pins in appreciation.
Now, back to today's question this was from Mark.
Marks is a CPA. He said hey, I've set up some data here and every time that I underlined the data single or double underline.
Whether it's a dollar a hundred thousand dollars the underline is the exact same length, but when I have 33 percent it only underlines the 33 is not underlining the whole cell.
He says, how can I solve this?
Well, that initially surprised me a little bit because usually when I type data in.
So, here's a dollar a negative dollar and a hundred thousand and I underline that it only underlines the bit that I've typed in.
So, 8876 I hit the underline it only underlines the 8876, but then I realized that Mark must be using the accounting format.
The accounting Format is easy to come up with because it's the one that's on the formatting toolbar, this currency style creates an accounting format when we underline that sure enough it underlines the whole cell.
So, I said well there's nothing magic about these custom number formats.
Let's see what's going on use Format Cells and I go click on Custom and I notice that here in the format that they have an asterisk in a space, asterisk in the space, that doesn't appear in displayed number at all and then I remembered that the asterisk followed by one character tells Excel to basically, fill the cell with that character.
So, for example, if you're doing the check writing thing, where you wanna asterisks to fill the number you can put asterisk, asterisk.
Well, asterisk faces just Excel's way of saying.
Hey, even though this doesn't fill up the whole cell. We're going to fill it up with spaces.
So, I came here to my number that 33 percent.
I said, I wonder if we can try the exact same trick, so control+1, Format Cells, I'm gonna go to the Custom tab and right now, we have a custom number format of 0% in front of that I'm going to put an asterisk in a space.
In other words tell Excel even though the number is smaller, it's not filling up the whole cell let's fill up the whole cell of spaces, Click OK and now, instantly we have the percentage underlined and it does the entire cell just like all of the other currency formats.
So, great question form Mark there.
Lots of different ways to do things to Excel the way that I usually do things I end up with the Underline just under the numbers.
Mark, using this icon on the toolbar is underlining the whole number.
Which of course was driving him crazy, when he wanted to format the percentage.
So, we actually have either way we have to go into the custom number format to make it work.
So, there you have it.
Thanks for stopping by.
Thanks to those Steele NCT Students.
We'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
Let's take care of a few open items that we have here.
First of all I received a note from Joe Todd up in Amherst.
He says, he and his students watch the podcast every day.
So, I want to send a shout out to all the Steele NCT Students.
Wanna thank you for stopping in and I asked Joe.
If that you had any particular questions, he says no they just love everything.
So, we're good there, also probably week and a half ago I was over in visual basic, and I was very frustrated that I had managed to un-dock these two toolbars and I went through a very long convoluted way to get them re-docked.
I got a note from Jeff Shroff.
Jeff said hey, you completely missed it.
All you have to do is double click, double click on the title bar and they all go back to where they were.
We had a whole room of very smart people and no one could figure that out. So, thanks to Jeff.
I'm sending him one of my Excel master pins in appreciation.
Now, back to today's question this was from Mark.
Marks is a CPA. He said hey, I've set up some data here and every time that I underlined the data single or double underline.
Whether it's a dollar a hundred thousand dollars the underline is the exact same length, but when I have 33 percent it only underlines the 33 is not underlining the whole cell.
He says, how can I solve this?
Well, that initially surprised me a little bit because usually when I type data in.
So, here's a dollar a negative dollar and a hundred thousand and I underline that it only underlines the bit that I've typed in.
So, 8876 I hit the underline it only underlines the 8876, but then I realized that Mark must be using the accounting format.
The accounting Format is easy to come up with because it's the one that's on the formatting toolbar, this currency style creates an accounting format when we underline that sure enough it underlines the whole cell.
So, I said well there's nothing magic about these custom number formats.
Let's see what's going on use Format Cells and I go click on Custom and I notice that here in the format that they have an asterisk in a space, asterisk in the space, that doesn't appear in displayed number at all and then I remembered that the asterisk followed by one character tells Excel to basically, fill the cell with that character.
So, for example, if you're doing the check writing thing, where you wanna asterisks to fill the number you can put asterisk, asterisk.
Well, asterisk faces just Excel's way of saying.
Hey, even though this doesn't fill up the whole cell. We're going to fill it up with spaces.
So, I came here to my number that 33 percent.
I said, I wonder if we can try the exact same trick, so control+1, Format Cells, I'm gonna go to the Custom tab and right now, we have a custom number format of 0% in front of that I'm going to put an asterisk in a space.
In other words tell Excel even though the number is smaller, it's not filling up the whole cell let's fill up the whole cell of spaces, Click OK and now, instantly we have the percentage underlined and it does the entire cell just like all of the other currency formats.
So, great question form Mark there.
Lots of different ways to do things to Excel the way that I usually do things I end up with the Underline just under the numbers.
Mark, using this icon on the toolbar is underlining the whole number.
Which of course was driving him crazy, when he wanted to format the percentage.
So, we actually have either way we have to go into the custom number format to make it work.
So, there you have it.
Thanks for stopping by.
Thanks to those Steele NCT Students.
We'll see you next time for another netcast from MrExcel.
