Microsoft

Office challenge: How would you enter the same value into numerous non-contiguous cells?

This week, test your Excel skills by answering this simple user question: How can I enter the same value in several non-contiguous cells?

Leaving a cell blank can have unexpected consequences. For instance, the value 0 and a blank cell might visually suggest the same thing - there's no valid entry - but many Excel functions will evaluate them differently. For example, Excel's AVERAGE() function will return 20 when evaluating three cells with the following values: 10, blank, and 30. If you enter a 0 into the blank cell, AVERAGE() returns 13.3. Excel totally ignores the blank cell, but evaluates the 0. For this reason, it's a good idea to use 0s instead of leaving cells blank (unless you have a specific reason for doing so - you might want Excel to ignore the blank cells) in a numeric data range.

Now, let's suppose a user is working with a sheet similar to the one shown below and he needs to fill all the blank cells with the value 0. (It could be any value, but let's stick with the zero example.) In a small sheet, entering all those 0s would be a quick task, but imagine a sheet with hundreds or thousands of blank cells! Can you help this user enter the value 0 into all those blank cells, without entering them one-by-one?

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

17 comments
jbenton
jbenton

because they rely on you scanning for and clicking each relevant cell Much better selecting by using Go to, Special... or Find and Replace (my favourite for this example as it's easily undone or repeated and won't lose your current position/view or empty the clipboard)

derwil
derwil

Select the first cell enter 0, then press enter. Copy the cell. Select other non-contiguous cells using ctrl+left click, then ctrl+v. All the cells then have the same value pasted in them.

AtCollege
AtCollege

Select the non-contiguous cells select 1st one, control click the others Type a number Press Control + Enter All done!

jbenton
jbenton

(using 2003) Ctrl-H to open Replace dialogue leave Find what: blank enter whatever you need in Replace with: open up Options and check Match entire cell contents Alt-A to invoke Replace All NB if no range is initally selected, this will update every blank cell in the active area (ie not to IV65536) of the worksheet

Sherinx
Sherinx

Select the complete range (Ctrl+A) Press Ctrl + G (Goto) Press Alt + S (Special) Press K (for Blanks) and then press enter Press 0 and then press Ctrl + Enter to populate all blanks with zero's Do let me know how it goes!

Marina Martin
Marina Martin

Choice the one of the blank cells Choice each additional blank cell, using ctrl-click enter zero into each of the highlighted cells, then CTRL ENTER

zimmerwoman
zimmerwoman

copy a zero into the clipboard, select all cells in the range and "right-click, paste-special, add". I like the find and replace solution from tink. I just tested and it works like a charm. good one. I might add that there are some functions (don't remember which, they always catch me by surprise) that don't like 0 formatted with a "-". That is what "comma-style" looks like. So, I also format my numerical data with "number-style" which returns "0" for a zero value.

Tink!
Tink!

1) Highlight the cell to be copied and hit CTRL-C, CTRL-Click each cell you want to paste the value into then hit CTRL-V 2) Highlight the cell to be copied and CTRL-C Twice (this automatically opens the Office Clipboard and pastes the value there), then click a cell you want to paste to and click the clipboard. [b]NOT[/b] the best way 3) Select the full area of the sheet to be searched and replaced. (Often Shift-CTRL-END works for this.) Hit ALT-E, E to open Find and Replace. Leave Find blank. Put in 0 in Replace. Hit Replace All Yes, I'm a keyboard navigator so most of my commands are done via keys :D

electronics_md
electronics_md

Highlight the cells in question and perform a FInd and Replace. Put nothing in the Find box and 0 in the Replace box then click on Replace All. If it's just numbers in the cells, and no spaces, you're all set.

damiross
damiross

Select the one of the blank cells Select each additional blank cell using ctrl-click enter 0 into one of the highlighted cells and click CTRL ENTER

Tink!
Tink!

Poor Goto is often forgotten I think. I know personally I have used Goto probably twice in my experience with Office. LOL!

damiross
damiross

That's a great way to go if there's a lot of blank cells. So much quicker than individually ctr-clicking each blank cell.

damiross
damiross

There's no need to enter zero into each of the cells. Just enter it into ONE of highlighted cells then press Ctrl Enter

Who Am I Really
Who Am I Really

returns the following dialog: "Microsoft Office Excel, cannot find any data to replace. Check if your search formatting and criteria are defined correctly. If you are sure that matching data exists in this workbook, it may be on a protected sheet. Excel cannot replace data on a protected worksheet."

zimmerwoman
zimmerwoman

how are you selecting all of the null or non-numeric cells?

damiross
damiross

You'll need to have some data in the range to do the replace When I highlighted an area with no data, it returned the "cannot find any data" message When I placed entered a number into one cell then highlighted a block I got the same message When I placed data in 2 no adjacent cells and highlighted everything between them it worked.