How to auto-fill lots of columns or cells with the same value in Excel

I had a spreadsheet recently (actually a CSV file which contained numerous columns of data. The values in the cells were either blank (empty) or the letter 'Y'. I needed to change all the blanks to 'N'.

Find and replace didn't work as you can't specify blank as a matching character (at least I couldn't find out how, please comment if you know!) and I didn't have the energy or time to manually update each cell. After some research I found that the answer was the 'Goto -> Special' options hidden within the 'Edit' menu.

Here's how to do it...

• Select the columns and/or cells that you want to perform the special find within (hold down CTRL as you click the column headers or individual cells.


• Click the 'Edit' menu and select 'Go To...':
  
  


• Then click the 'Special...' button:
  
  


• Then click the 'Blanks' option and click 'OK':
  
  

Any cells within your selection that contain blanks will now be selected leaving the other non-blank fields un-selected. If you now type and press ENTER it fills that first cell with what you typed and moves to the next blank cell. BUT here is the trick, if you type what you want in the cell and press CTRL+ENTER then all the blank fields will be filled with the entered values - pretty neat huh!

There are many more options within that Special pop-up and I've not really explored it at all but I have found out how you select all the non-number-only fields:

Remember, the 'Go to -> Special' helps you select the correct cells, the CTRL-ENTER auto fills them all with the value you enter in the first selected cell.

I found this information out from the rather useful Excel section of allexperts.com.

Technorati Tags: Excel, AllExperts, Andrew Beacock