When a number is too large to fit in the width of a column, Excel will display pound signs. Episode 402 shows a unique way to select all of the cells that are currently displaying as pound signs. This trick would seem to be useful for quickly formatting all of the cells that match some certain condition.
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.
A really obscure trick, today I have a data set here, and we have lots of numbers that are too large for the cell.
OK, now normally, we would just make this column wider by double-clicking the area between column D and column E. However, let's say that, for some reason, we wanted to take all those numbers that were too large, and format them with a different decimal format, or something like that.
An amazing way to select all of the cells that are evaluating to pound signs, if I select the cells and use Edit, Find, I'll have to click the Options button so I can see more options.
I basically want to say that I want to look for every occurrence of 5 pound signs, and I want to make sure to look in values.
Instead of just saying Find Next, I'm going to ask Excel to Find All, and it'll give you a list of all of the cells that are evaluating the pound signs right now.
If I hit Ctrl+A to select all, it selects all the items on the right-hand list, and you'll see that now I've selected just the cells where the number is too big for the column.
And maybe I can go through and decrease the decimal in order To make all of those cells fit.
Now again, most of the time, you probably would have just gone through and double-click the border there between D and E to make the column wider.
However, thought this was really interesting, first of all, that Excel would find the pound signs, I thought that was really unusual.
And then also the trick of using Find All, and then Ctrl+A to basically select all of the cells that match your Find.
Hey, thanks for stopping by, we'll see you next time for another netcast from MrExcel!
A really obscure trick, today I have a data set here, and we have lots of numbers that are too large for the cell.
OK, now normally, we would just make this column wider by double-clicking the area between column D and column E. However, let's say that, for some reason, we wanted to take all those numbers that were too large, and format them with a different decimal format, or something like that.
An amazing way to select all of the cells that are evaluating to pound signs, if I select the cells and use Edit, Find, I'll have to click the Options button so I can see more options.
I basically want to say that I want to look for every occurrence of 5 pound signs, and I want to make sure to look in values.
Instead of just saying Find Next, I'm going to ask Excel to Find All, and it'll give you a list of all of the cells that are evaluating the pound signs right now.
If I hit Ctrl+A to select all, it selects all the items on the right-hand list, and you'll see that now I've selected just the cells where the number is too big for the column.
And maybe I can go through and decrease the decimal in order To make all of those cells fit.
Now again, most of the time, you probably would have just gone through and double-click the border there between D and E to make the column wider.
However, thought this was really interesting, first of all, that Excel would find the pound signs, I thought that was really unusual.
And then also the trick of using Find All, and then Ctrl+A to basically select all of the cells that match your Find.
Hey, thanks for stopping by, we'll see you next time for another netcast from MrExcel!
