Bottom line: Learn why the number formatting for new rows of a table doesn't always get copied down and how to fix it.

Skill level: Beginner

Watch on YouTube and give it a thumbs up.YouTube Thumbs Up LikeYouTube Subscribe Logo Excel Campus

Number Formatting Not Copying Down

I stumbled on some weird table behavior recently and had to investigate.  When adding new data to the bottom of my table, the number formatting from the cell above wasn't getting copied down.

All of the cells in the column had the same number formatting applied, so I expected the cells below in the new row to inherit the number format.  However, that wasn't the case.

Excel Table Number Formatting Not Copied Down for All Columns

All Cells in the Column Must Be Formatted at the Same Time

The reason this happens is because the number formatting was NOT applied to all of the cells in the column at the same time.

Table Number Formatting Not Applied to All Cells in Column at Same Time

If you apply number formatting to one cell, then apply the same format to the rest of the cells in the column later, the Table does NOT set that as the formatting for the entire column.  So, we don't get the same formatting copied down for new rows.

There is no way that I've found to check if a number format has been applied to all cells in the column at the same time, so it is a bit confusing.

How to Fix It

The solution is pretty simple here.  We just have to apply the number formatting to all cells in the column at the SAME TIME.

This can be a time consuming task if your Table has a lot of columns, so I want to share two ways to make this process easier.

Tip #1: Shortcuts to Select Entire Table Columns

The first tip is pretty simple and really requires two steps.

  1. Select all of the cells in the column.
  2. Re-apply the number formatting.

How to Select All Cells in a Table Column

If your Table has a lot of rows, this task can be time consuming.  Here are two shortcuts to select all cells in the data body range of the column.  The data body range is the range of cells below the Table header and above the Total Row.

Use the Mouse

Select Entire Table Column with the Mouse 2

To select all the cells in the column with the mouse:

  1. Hover the mouse cursor over the top half of the header cell until the cursor turns into a down arrow.
  2. Left-click once to select all cells in the data body range.

Use a Keyboard Shortcut

Keyboard Shortcut to Select All Cells in Column Table - Ctrl+Space

The keyboard shortcut to select a Table column is Ctrl+Space.

  1. Select any cell inside the column.
  2. Press Ctrl+Space to select all cells in the data body range.

Checkout my other articles on 5 Keyboard Shortcuts for Rows and Columns and 2 Keyboard Shortcuts to Select Columns with Blank Cells to learn some additional tips.

Apply the Number Formatting

After the cells are selected, apply the number formatting to the entire column.  This will set the number formatting for the column and all new rows should inherit the column's number format.

Apply Number Formatting with All Cells Selected in the Column

Important Note: If you are using the Format Cells window to re-apply number formatting, then you might have to first change the number format to a different format, then change it back to your original format.  The Format Cells window does NOT reset the formatting for the entire column if all the cells in the column already contain that number format.  See the video above for more details.

Tip #2: Table Number Formatting Macro

If your Table has a lot of columns, or your workbook has a lot of Tables, then this task could take a long time to do manually.  So I wrote a few macros that automate the process.

You can download the file that contains the VBA code.

Table Number Formatting Macro.xlsm (21.5 KB)

I explain how these macros work in the video above.   So make sure to watch it if you are going to use the macros in your projects.

Macro #1: Fix All Columns in a Specific Table

The first macro loops through all of the columns in a Table and sets the number formatting for the entire column.  It uses the number formatting from the first cell in the column as the basis.  However, you can change the code to use the cell in the last row of the column instead.

Sub Table_Number_Formatting()
'Copy number formatting down each table column

Dim lo As ListObject
Dim lc As ListColumn

  'Set a reference to a table
  Set lo = ActiveSheet.ListObjects(1)
  'Set lo = ThisWorkbook.Worksheets("Data").ListObjects("Table1")
  'Loop through all list columns in the table
  For Each lc In lo.ListColumns
    'Set the number formatting of the entire column to the number formatting in the first row
    lc.DataBodyRange.NumberFormat = lc.DataBodyRange(1, 1).NumberFormat
  Next lc

End Sub

Macro #2: Fix All Columns in the Selected Table

This macro will run on the Table the user has selected.  Just select a cell inside a Table before running it.

Sub Table_Number_Formatting_Selected_Table()
'Copy number formatting down each table column on the selected table.

Dim lo As ListObject
Dim lc As ListColumn

  'Set a reference to the selected table
  On Error Resume Next
    Set lo = Selection.ListObject
  On Error GoTo 0
  'If a table is selected then format it
  If Not lo Is Nothing Then
    'Loop through all list columns in the table
    For Each lc In lo.ListColumns
      'Set the number formatting of the entire column to the number formatting in the first row
      lc.DataBodyRange.NumberFormat = lc.DataBodyRange(1, 1).NumberFormat
      'Use the line below to set it to the last row in the table
      'lc.DataBodyRange.NumberFormat = lc.DataBodyRange(lo.DataBodyRange.Rows.Count, 1).NumberFormat
    Next lc
  Else 'Cell in table not selected
    MsgBox "Please select a cell inside a Table first.", vbOKOnly, "Table Formatting Macro"
  End If
End Sub

Macro #3: Fix All Tables in the Workbook

The Table_Number_Formatting_All_Tables macro will fix the number formatting in all Tables in a workbook.  It loops through each sheet in the workbook, then loops through all tables on the sheet, and finally loops through all columns in each Table.

This is nice if you have a workbook with a lot of Tables and want to make sure you or your users don't run into this formatting issue.

Sub Table_Number_Formatting_All_Tables()
'Copy number formatting down each entire table column
'For each table in the workbook

Dim lo As ListObject
Dim lc As ListColumn
Dim ws As Worksheet

  'Loop through each sheet in the active workbook
  For Each ws In ActiveWorkbook.Worksheets
    'Loop through all tables on the sheet
    For Each lo In ws.ListObjects
      'Loop through all list columns in the table
      For Each lc In lo.ListColumns
        'Set the number formatting of the entire column to the number formatting in the first row
        lc.DataBodyRange.NumberFormat = lc.DataBodyRange(1, 1).NumberFormat
        'Use the line below to set it to the last row in the table
        'lc.DataBodyRange.NumberFormat = lc.DataBodyRange(lo.DataBodyRange.Rows.Count, 1).NumberFormat
      Next lc
    Next lo
  Next ws
  MsgBox "Number Formatting has been updated for all Tables in the active workbook.", vbOKOnly, "Table Number Formatting Macro"

End Sub

The Macros ONLY Change Number Formatting

It's important to note that these macros will apply the Number Formatting to all cells in the column for EACH column.  If you have some columns with mixed number formatting, then you might want to go with the manual approach explained in Tip #1.

The macro is ONLY applying number formatting.  It will NOT change any other formatting properties like font/fill color, borders, conditional formatting, etc.  It ONLY changes number formatting.

We can't undo the changes made by these macros, so make sure to save a backup copy first.

We could also modify the macro to only run on columns that contain numbers, dates, or even specific number formats.  Please leave a comment below if you are interested in that and I will share some code.

Add the Macros to Your Personal Macro Workbook

Any of these macros can be added to your Personal Macro Workbook to run on any open Excel file.  You can also add a macro button to the ribbon to make it easy to run the macro at any time.

Checkout my 4-part video series on the Personal Macro Workbook to learn how to set this up.

Is this behavior a bug?

What do you think about this behavior?  My initial thought is that if all the cells in the column have the same formatting, any new cells should inherit that same formatting, even if formatting is not applied at the same time.

I can see that we might not want that behavior if there are different number formats used in the column.  However, if the entire column has the same number format, then it makes sense that any new Table rows will inherit the formatting from above.

Are there any cases where you wouldn't want this to happen?  If so, please leave a comment below.  Thank you! 🙂


Your email address will not be published. Required fields are marked *

  • To answer the question you ask, I say it shouldn’t arise at all. If pressed, I would say NO, the next cell down should not look to all the cells above for a format, just the cell directly above it (with a moment on Excel’s part to ask, when that format might be changed, if it should become the format for the entire column and going forward, and if NO is chosen, should it be used going forward.

    That way one can easily change a format for the entire column, or if that is forbidden for some good reasons (legal ones for example), change it for future records.

    I have a larger issue here and that’s also a part of the above answer. My issue is that I have a Table which needs a column’s formula changed periodically (thanks Fed and your ruinous interest rises). However, NOTHING I do makes the new formula the default. Nothing Table-oriented anyway. I have seen sites that say if you change the four or six cells above it to the same formula, then it takes the idea so you can create that many new rows, change those formulas, then see… that it doesn’t work.

    Only changing it to a Named Range, changing the formula, and changing back to a Table does the trick.

    Seeing this article lit a bulb in my thinking bubble (have to love cartoons). I’m betting changing the formula used will only be picked up if I change ALL the cells… just what I cannot do, both because then all the above ones would be wrong, but also for legal reasons.

    If Excel changed the behavior I’m imagining, so that it asked you as mentioned early above, then the same engine might be at work for changes in formulas and ask the same questions so that I WOULD see it used going forward.

    Right now my only good hope, since the Table to NR to Table approach has a LOT of drawbacks, or more accurately, obnoxiousness, and fails now and then so I have to dump the work and start over, is to try to put the changing portion of the formula into a Named Range formula so the changes and magic can happen there.

    Pretty obnoxious that one has to redo good work for something that shouldn’t need forseen to begin with.

  • Hello Jon, Thank you so much for your videos and expertise. I am very interest in formatting columns that only have Dates in my worksheets. I very much like the format “All Tables” macro. Will you please modify the code from numbers to dates format mm/dd/yyyy? Also I get a “Object variable or With block variable not set” error at this line of code: lc.DataBodyRange.NumberFormat = lc.DataBodyRange(1, 1).NumberFormat

    Please help – Thank you

  • Hey Jon, I am currently dealing with this exact issue!! I tried the reformatting method several times including removing my tables, reformatting, and recreating my tables. I have tried several approaches with code, but none of them seem to work. I want to try the macro you wrote above to resolve the issue, but I need to incorporate that into my code. I am unsure where to place it. I need some direction.


    • My issue has been resolved. I was missing some “.value” code at the end of my cell references. Once I added that to all of my references, everything worked like a champ.

  • Hey Jon, I just ran into this issue while using a user-form to create and enter data into a new table row on three different tables on three different worksheets within the same workbook. Instead of using a separate macro to accomplish this, is it possible to incorporate your code into mine so that it automatically does this when the new row is created?

  • I currently have this issue in a workbook with over 30 worksheets/tables. I had to reset the number format each time I made a new entry into the tables. What a hassle. Your tip will be very useful and save me time. I am not sure the macro to change all worksheets will work since one the work sheets has charts. Will the macro skip the charts worksheet? LMK.

    Thanks again!

    • Hi Tom,
      The macro will still work if the workbook contains Chart Sheets (sheets where the entire sheet is a chart). The ws variable is declared as a Worksheet, so it will only loop through worksheets and skip the chart sheets.

      I hope that helps.

  • Great tip! Thanks for sharing. It was a problem I just recently had. I manually fixed it as there were not too many lines in the excel sheet I was working on. Now I can apply this tip if I have to do the same thing again.

    Thanks again!

  • Hey Jon, great post as usual! Using your code as a guide I added the following to replicate the font size as well and it worked nicely:

    lc.DataBodyRange.Font.Size = lc.DataBodyRange(2, 1).Font.Size

    What I haven’t been able to figure out though is alignment. (left, right, center)

    I tried:

    lc.DataBodyRange.Orientation = lc.DataBodyRange(2, 1).Orientation


    lc.DataBodyRange.Alignment= lc.DataBodyRange(2, 1).Alignment

    but they didn’t work. Is there a simple solution I’m missing here?


  • Jon,

    Great observation and great tip. You ask if I have had any instances where I would not wish to have the formatting copied if I were adding data below existing data. No, I can’t think of any, but it does remind me of a related pet peeve.

    I often deal with data in tabular form where the body of the array is in, say, dollar format, but the column heading is a plain old un-formatted account number. When I want to strike a dollar total at the bottom of each column, I get an error message telling me that there is data in adjacent cells, and surely I couldn’t be so stupid as to omit that data from the total? Well, Mr. Excel smarty-pants, I know exactly where I want my sum range to begin and end, and STOP TELLING ME IT’S AN ERROR! One solution is to insert a blank row beneath the account number heading and hide the row. That way Excel does not think that my account number column headings need to be included in the dollar totals, but it’s still annoying that Excel just assumes I don’t know what I’m doing, and does not listen when I try to tell it that I do know what I’m doing, thank you very much. Is there any easy way to tell Excel that my behavior is not in error?

  • Great One (As usual). I agree with you, this behaviour looks like a bug. Great patch, keep on sharing your expertise.

  • That was interesting. I seem to run into a problem with tables not bringing down formulas when new data is added. Any thoughts?

    • Hey Mathew,
      I just responded to Jon below with one reason that can cause that. It usually has to do with mixed formulas in a column that is triggered by Undo. There is also a case where Automatic Calculated Columns can be turned off for the Table.

      I’ll do a follow-up post on this topic in the future. Thanks!

  • Thanks for pointing this out, it was a problem I’ve been recently having. In addition to the formatting, I also noticed for one column, the formula was not being copied down when new data was appended to the table (despite every other column working as expected). Presumably I need to apply the formula to all the cells in the column again?

    • Hey Jon,
      Great question! With formulas there are a few more options for calculated columns. If the column contains mixed formulas then you usually get a box appearing after entering a new formula that says, “Overwrite all cells in this column with this formula”. Pressing that button should reset the formula for the column.

      Typically we get mixed formulas in a column when we enter a new formula, then press Undo. Undo will first undo the fill down of the formula. You have to press Undo a second time to undo the formula. If you don’t press Undo a second time then the column ends up with mixed formulas. Another weird quirk of Tables… 🙂

Generic filters
Exact matches only
Filter by Custom Post Type


Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
Plus weekly updates to help you learn Excel.

Download the eBook

About Me

Jon Acampora Profile

Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...