Q: I have an Excel spreadsheet. I want to replace all the blank cells with the word NONE. I did what you suggested in your last column, but it doesn’t work. Why?
A: It’s because those cells aren’t truly blank. There is a space in there for some reason. Do the following to make sure this is your problem:
Working in a cell you know for certain is blank (let’s say that is B45), type =CODE(B8) where B8 is the cell you're suspecting is not truly blank. If you receive 32 as the answer in Cell B45, then Cell B8 does indeed have a space in it.
So now you need to replace all these spaces with nothing, correct? Do the following:
1. Select all the cells you suspect have spaces instead of blanks.
2. Press Ctrl-h to open the Search and Replace dialog box.
3. Click Options and make sure "Match Entire Cell Contents" is selected.
4. In the Find box, type a space.
5. Leave the Replace box empty.
6. Click the "Replace All" button.
Now go ahead and try the trick of pressing F5/Special/Blanks that I mentioned in my last column.
Q: I often have several files open when I use Microsoft Office. Previous versions of Office had a Close All command. Is there any way to get this command back?
A: Many Microsoft Office users miss the Close All command that was present in previous versions of Office. Fortunately, the command is still available using the Quick Access Toolbar (QAT). The QAT is located in the upper left corner of any Office 2010 application. Here’s how to add this command to the QAT:
1. Click the QAT dropdown arrow and choose More Commands.
2. From the Choose Commands From dropdown, choose Commands Not In The Ribbon.
3. Scroll down the list until you see Close All and select it.
4. Click Add and then OK.
You can use this technique to add any favorite or pre-ribbon command to the QAT for quick access.