|Excel and Office
Automatically fill blank or empty cells in Excel
Q: I have a spreadsheet consisting of approximately 35,000 rows of 140 columns. Every cell needs to have a value. If there is data for the field, great but how can I fill blank cells with a text character if there is no value?
A: First, make sure the last cell on the sheet is not blank. Get there quickly with CTRL+down arrow, CTRL+right arrow. Put an entry there. Now select the whole sheet (use the corner block in the top left or CTRL+A).
From the Edit menu, select Go to, then at the dialogue, hit the 'Special' button and select 'Blanks'. Go and make some tea. It will take a minute or so to highlight every blank cell.
When it has, type your dummy string or character, then instead of pressing ENTER, press CTRL+ENTER. Time for another cup at his point. When you come back, every blank cell will now contain your text.
Response: [NB I don't normally show responses, but I've shown this one since it includes information from the user which might help if you run into the same problem.] Tea? Pah...coffee man, burnt black and lots of it! This worked great! I NEVER wooda figgered it out. Thanks. FYI, highlighting the entire worksheet gave me a selection too large error. I did it by doing 25 columns at a time. Took a while but b-i-n-g-o...no blank cells. AWESOME.