If you need to fill a cell with a character, either with or without numbers, there are a couple of interesting alternatives. Episode 574 shows you how.
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.
Today I have a cool trick that's left over from the days of Lotus 1-2-3.
If you remember in Lotus, you could put in a backslash in any character and excel would fill the cell with that character.
No matter whether it got wider or narrower or anything like that.
You could even put in let's say a backslash and then an underscore and a dash and it would fill with an interesting little pattern.
Well, it turns out that in Excel this only works for the people that have transitioned navigation keys turned on.
Tools, Options, Transition, if you have navigation keys turned on, it works otherwise it doesn't work and I think it's a cool trick.
But, not cool enough for you to have to go back and learn the old navigation keys well to solve this problem in Excel without the navigation keys.
We could use what I call the check writers format take a look at the cell it has leading asterisks and as the cell gets wider and narrow the asterisk expand.
The way we do that is under Format Cells, to put in front of the 0.00, the asterisk, asterisk.
The way that works is the first asterisk says we're going to repeat the next character, the second asterisk of course is the character to repeat.
Now, sometimes you might want to repeat something else.
Maybe, like a series of dots, you put asterisk dot, I guess a series of dashes.
I'm not sure that would make a lot of sense before a number people would think was negative, but, whatever you needed to do.
So, if we needed to replicate the old Lotus 1-2-3 trick, what we actually have to do is put in the number 0 somewhere and then format the cell.
This time with just two asterisks, that will tell Excel to repeat the asterisks character and not show the number.
Basically, does exact same trick.
So, there you have it.
Filling a cell with asterisks or any character you need.
Hey, thanks for stopping by.
We'll see you next time for another netcast from MrExcel Yeah, it turns out. I actually can't say that word asterisk, asterisk.
I think we should just call it a Nathan sign.
Why is it Nathan sign?
What after Nathan Hale, you know, when they were about to hang him for treason his final last words were.
I regret that I have a one asterisk for my country.
I'm Bill Jelen.
Today I have a cool trick that's left over from the days of Lotus 1-2-3.
If you remember in Lotus, you could put in a backslash in any character and excel would fill the cell with that character.
No matter whether it got wider or narrower or anything like that.
You could even put in let's say a backslash and then an underscore and a dash and it would fill with an interesting little pattern.
Well, it turns out that in Excel this only works for the people that have transitioned navigation keys turned on.
Tools, Options, Transition, if you have navigation keys turned on, it works otherwise it doesn't work and I think it's a cool trick.
But, not cool enough for you to have to go back and learn the old navigation keys well to solve this problem in Excel without the navigation keys.
We could use what I call the check writers format take a look at the cell it has leading asterisks and as the cell gets wider and narrow the asterisk expand.
The way we do that is under Format Cells, to put in front of the 0.00, the asterisk, asterisk.
The way that works is the first asterisk says we're going to repeat the next character, the second asterisk of course is the character to repeat.
Now, sometimes you might want to repeat something else.
Maybe, like a series of dots, you put asterisk dot, I guess a series of dashes.
I'm not sure that would make a lot of sense before a number people would think was negative, but, whatever you needed to do.
So, if we needed to replicate the old Lotus 1-2-3 trick, what we actually have to do is put in the number 0 somewhere and then format the cell.
This time with just two asterisks, that will tell Excel to repeat the asterisks character and not show the number.
Basically, does exact same trick.
So, there you have it.
Filling a cell with asterisks or any character you need.
Hey, thanks for stopping by.
We'll see you next time for another netcast from MrExcel Yeah, it turns out. I actually can't say that word asterisk, asterisk.
I think we should just call it a Nathan sign.
Why is it Nathan sign?
What after Nathan Hale, you know, when they were about to hang him for treason his final last words were.
I regret that I have a one asterisk for my country.
