MrExcel's Learn Excel #402 - Find #####

If you like this content, please consider visiting the video on YouTube and subscribe to the MrExcel Channel to show your support!
This video has been published on Oct 5, 2009.
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!
maxresdefault.jpg


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!
 

Forum statistics

Threads
1,213,527
Messages
6,114,140
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top