21

Why Table Number Formatting Doesn’t Copy Down and How to Fix It

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
'Source: https://www.excelcampus.com/tables/table-number-formatting/

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.
'Source: https://www.excelcampus.com/tables/table-number-formatting/

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
'Source: https://www.excelcampus.com/tables/table-number-formatting/

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! 🙂

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I'm not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 21 comments
Robert - May 21, 2018

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.

Thanks

Reply
    Robert - May 24, 2018

    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.

    Reply
Robert - May 18, 2018

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?

Reply
tom herrington - April 10, 2018

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!
Tom

Reply
    Jon Acampora - April 12, 2018

    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.

    Reply
Annie - March 12, 2018

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!

Reply
Louise - March 12, 2018

Great tip, thanks as always!

Reply
Manuel - March 12, 2018

Thank you for sharing this tip.

Reply
Jeff C - March 9, 2018

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

and

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

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

thanks!

Reply
Steve Stojowski - March 9, 2018

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?

Reply
    Jon Acampora - March 14, 2018

    Hey Steve,
    I’m not sure I fully understand. When does this error message appear? What action are you taking or feature are you using?

    Reply
Florence - March 9, 2018

Hi Jon,

Thank you for sharing this tip.

-Florence

Reply
Al1 - March 9, 2018

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

Reply
Mathew - March 9, 2018

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

Reply
    Jon Acampora - March 9, 2018

    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!

    Reply
Jon - March 8, 2018

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?

Reply
    Jon Acampora - March 9, 2018

    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… 🙂

    Reply

Leave a Reply: