Is there a way to have "default" or "placeholder" values in Excel?

Question

I've got a spreadsheet with cells that I want to be user-editable, but that I also want to have "default" or "placeholder" values in, whenever there is no user-entered data. There's a couple good use cases for this:

• Prevent formula errors, while providing reasonable assumptions when a user has not entered (or has deleted) their own value.
  • I could use conditional formatting to alert the user to default values, so as to prevent their ignorance of them - they can then make an informed choice as to whether that value is still appropriate or not for the intended calculations.
• Give a short description of what is intended to be entered in the cell, without having to have a separate "instructions" segment or document. This would also eliminate the need for a nearby "Label" cell, in some cases where it's really not appropriate.

To accomplish what I want, I need some formula, script, or other advanced spreadsheet option that will do the following:

• Show the default value in the cell before user enters data.
• Allow the default value to be found by any formulas referencing the cell, when there is no user-entered data in that cell.
• Allow the user to freely (naturally, exactly as they would do with any "normal" cell) overwrite the displayed value with their own value or formula, and have the user-entered data found by any formulas referencing the cell.
• When cell is blanked by deletion of user input, revert to default value.

Is there a way to do this in Excel, or am I asking too much of a spreadsheet program here?

Answer 1

There is no built in way to do this, you will need to create your own.

One possibility is this:

1. For each cell you want a default for, create a worksheet scoped named range referencing that cell, and in the comment field enter the required default value

2. Add the Workbook module Workbook_SheetChange event

   Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
       Dim nm As Name
       Dim cl As Range
   
       On Error Resume Next
       For Each cl In Target.Cells
           If IsEmpty(cl) Then
               For Each nm In Sh.Names
                   If nm.RefersToRange.Address = cl.Address Then
                       Application.EnableEvents = False
                       If IsNumeric(nm.Comment) Then
                           nm.RefersToRange.Value = Val(nm.Comment)
                       Else
                           nm.RefersToRange.Value = nm.Comment
                       End If
                       Application.EnableEvents = True
                       Exit For
                   End If
               Next
           End If
       Next
   End Sub
   

What this does is:

• Any time a cell is changed the event code is run
• If a cell that changed is empty
  • Look for a Worksheet scoped Named Range that references the cell
  • If found copy the Name comment into the cell value, converting to numeric is appropriate

Answer 2

I use a method that works like this:

-- a simple example for school records:

• col A are the row titles: student ID, last name, first name, birth date, age...
• col B is the ghost column: width 1, left justify, increase indent 1, protected, font color gray, no text wrap
• col C are the entry fields

-- Col B values are loaded with whatever you want to show in col C as the default. Values can come from an accompanying helper sheet, a formula that updates as the cells are filled in, another spreadsheet with default values, error messages...

-- so, when the student ID is entered, the last name, first name, birth date, address, parents, etc immediately show as ghost values in the rows below. There is nothing in any of the col C cells so the ghost values in col B extend into col C. (Numeric and date values must be formatted with a text() function.) After verifying the child's information, the operator continues down to fill in the class schedule, homeroom teacher...

-- If something needs to be changed the cell is typed into, which signals that something for that child is different. (Family name, parent's name, address...)

-- or, the last name and first name could be entered, leaving the ID blank, and the ghost value would show the child ID from the master spreadsheet.

-- A helper sheet could show helper text that disappears once the cell in col C is touched. Or, spreadsheet logic can determine that the helper text is no longer appropriate or should show something different.

The ghost values are searchable, can be gray for default text or red for warnings, and refresh automatically as the accompanying spreadsheets are updated.

A limitation is that the ghost text cannot wrap into col C.