Share via

Default values in table cells

Anonymous
2011-10-05T12:15:11+00:00

This is for Excel 2011.  In a table, whenever a new row is started, can Excel automatically enter a default value such as zero or 1 into cells that I designate for every row (which of course do not contain a formula)? I looked through number formatting and data validation but nothing there can create default values in new table cells.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. Anonymous
    2011-10-05T16:23:39+00:00

    One way:

    You could enter something like this formula in your designated columns. For example in row 1:

    =IF(A1<>"", 0,"")

    which would display a null string (appear blank) until you entered a value in column A . Copy down as far as needed. Then as you enter values in column A, the default value will show up in your target column(s). You can then overwrite the default formulas with other values if desired.

    Another way would be to use an event macro. For example, you could put this in your Worksheet Code Module (Ctrl- or right-click the sheet tab and choose View Code):

    Private Sub Worksheet_Change(ByVal Target As Range)

    Dim vDefaultArray As Variant

    'put default values for columns B, C, etc...

    'Leave blank between commas if no default for that column

    vDefaultArray = Array(, 1, 0, , , , 0, 1)

    With Target

    If .Column = 1 Then _

    If Application.WorksheetFunction.CountA(.EntireRow) = 1 Then _

    .Offset(0, 1).Resize(1, UBound(vDefaultArray) + 1).Value = vDefaultArray

    End With

    End Sub

    9 people found this answer helpful.
    0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Bob Jones AKA CyberTaz MVP 435K Reputation points
    2011-10-05T13:31:05+00:00

    Realistically, no. There is no 'Default Value' option & the few suggested workarounds I've seen would not provide what you're asking for.

    Regards,

    Bob J.

    3 people found this answer helpful.
    0 comments
  2. Anonymous
    2011-10-06T12:44:26+00:00

    Thank you, this really helps.

    I realized after posting this question that in Excel 2008 I used to specify default values in the list formatting for lists, but that is now gone as far as I can see, so these approaches will be really helpful. Thanks again.

    1 person found this answer helpful.
    0 comments