Set default cell value in Excel

Other than the top row, which is a header, I'd like one column to always have a default value, ideally added when that row is not empty... so I don't have an infinite number of rows with just the default value.

I couldn't find a way to set default values at all - I do not want the user able to edit this.

microsoft-excel-2007

Mr. Boy

Posted 2010-09-17T08:51:55.477

Reputation: 3 710

1Is this default value the same for every row? Or does it vary based on the contents of the row? – DMA57361 – 2010-09-17T09:05:55.447

Answers

You can fill that column with formula =IF(D4="";"";"default value"), assuming you're in row 4 and column D will be filled in later on. (Use =IF(D4="","","default value") if your version of Excel separates function arguments with commas.)

Translation:

If the row 4 is not filled in, then the result is blank so nothing is shown.
If the row 4 is filled and the user has entered a value in column D, then your default value is shown.
The user can fill in any other value instead of this formula, thereby simply overwriting the formula which isn't needed anymore anyway.
You can protect the column if you don't want the user to overwrite your default value.

Torben Gundtofte-Bruun

Posted 2010-09-17T08:51:55.477

Reputation: 16 308

-1

the hard but more convenient way is to give the default value in the first case and then copy them to all other vertical cells by using the cursor at the right hand corner till there is a cross and pull it down to the last cell. this is useful only in relatively small samples. very useful if you are calculating in the cell by applying a formula in the first cell. Smsv

Smsv

Posted 2010-09-17T08:51:55.477

Reputation: 1