20

I have an excel table and all rows are formatted the same. When I add a new row, the formatting does not match the rows above - so every time I add a row I have to select the above row and copy/paste special>format to get it to look right.

My question is twofold.
1) How can I get it to format correctly when adding a new row?
2) Where do tables pull their formatting from, as I'm not understanding why it formats so odd.

*I can take a video and upload it to youtube if you need to see the behavior I am talking about.
**This is happening in multiple workbooks, so not isolated to a single workbook or single table.

CBRF23
  • 515
  • 2
  • 4
  • 18

10 Answers10

12

I had the same problem for one column, it used to set forecolor of one column to red for each new row I added to the table.

To resolve this click on the header of a column then right click and choose "Format Cells" option and set the desired format. After this new row will use this format for selected column. You can specify the format for other / all columns like this.

Faisal
  • 236
  • 2
  • 5
  • 1
    Format cells did it; formats are inheriting correctly now - nice simple solution. Thanks. – CBRF23 Feb 10 '16 at 13:05
  • 1
    This solution did not work for me when pasting unformatted data into a table. Oddly, some of my tables pass formatting (espec. cell type) to new cells in a column as expected using the same paste method. It isn't clear why these tables behave differently. Do we have any information about how Excel determines the formatting for a table column? – Christopher Harwood Apr 06 '17 at 14:36
9

This happened to me as well using the TAB key to insert a new table row. What I found is that Excel is using the format of the row when the table was created not it's current state. So to address this, I converted the table to a range, ensured the formatting was how I wanted it, then converted it back to a table.

Aaron
  • 91
  • 1
  • 2
  • I didn't want to lose custom formatting so I built on your solution by (1) converting to range, (2) adding a new row at the top, formatted correctly, (3) created a table using only headers and that top row, (4) copied all format from my table to a blank space, (5) expanded the table to the whole range, (6) copied the custom formats back in. If you use a striped table, the formats will be included so change the table style to an empty table first. – claytond Oct 22 '21 at 18:21
  • Legend! Was egtting so annoyed by this and wa sonly on my 5th of 40+ rows of data inputting!.. Thank you – Liam Wheldon Aug 02 '22 at 10:12
4

It sounds like the range to which you are adding rows to is not actually formatted as a table.

Option 1: Highlight the cells that you want to format and select "Format as Table" on the Home tab. Once you do that, any new rows and columns will be formatted consistently.

Option 2: After you insert a new row, you should see a little formatting icon pop up next to the new row. Click that drop-down menu to see options for "Format Same as Above/Below" as shown in the screenshot.

enter image description here

PFitz
  • 2,478
  • 1
  • 17
  • 24
  • @P Fitz - I'd already done this, and just did it again. No change - still does not inherit the formatting of the rest of the table. I also do not get any popup giving me the "format same as above/below" option. – CBRF23 Mar 04 '15 at 21:29
  • What version of Excel? – PFitz Mar 04 '15 at 21:30
  • @CBRF23 Are you getting the rest of the table behavior as expected? (Pull down menu in each column header, etc.) – Adam Mar 05 '15 at 16:56
  • @PFitz I'm on Excel Home and Business 2010 – CBRF23 Mar 05 '15 at 19:31
  • @Adam Everything else in the table works as expected - pull downs, calculated columns, etc. - just not getting the right formatting. – CBRF23 Mar 05 '15 at 19:31
  • Exactly what elements are not formatted correctly? Fill/text color, borders, and/or number format? – PFitz Mar 09 '15 at 18:54
  • @PFitz Fill color, text color, borders, text alignment. (sorry for the delay, I was out of town on business) – CBRF23 Mar 11 '15 at 15:55
  • Can you go ahead and upload screenshots? Many of the preset table styles change the fill color of the rows so that they alternate between two colors (even rows blue, odd rows white, for example). It maintains this pattern even as new rows are added to the table. If, however, you change the fill color of a row before you a apply the table formatting, the alternating fill colors won't be applied to these rows. This may be the source of your problem/confusion. Had you changed the fill color of any cells prior to formatting the range as a table? – PFitz Mar 18 '15 at 18:13
  • @PFitz the cells are all conditionally formatted to be of a fill color based on some criteria. I don't remember if this was set before the table was created or not. Probably was, as I was working from an existing workbook. I have found that inserting new rows ABOVE will get the right format. It is only rows below that are bad. I can take screenshots- what would you like screenshots of? I tried to take a video, but youtube's compression makes it useless. – CBRF23 Mar 19 '15 at 20:44
  • It sounds like the Conditional Formatting rules are casing the problem. You most likely have an absolute (dollar sign) in the "Applies to" field causing unexpected formatting when rows are added. To apply the rules consistently: go into the Conditional Formatting rules manager and change the "Applies to" field to the exact range you want formatted according to the conditions. Depending on your needs, you may find it easier to use the Format Painter for this. – PFitz Mar 19 '15 at 20:54
  • @PFitz Well, in one document the conditional formatting copies just fine, while in another it does not. I've managed to get a video uploaded - but it's taking forever to "process" . I've got to run, but here is the link - hopefully will be live soon. http://youtu.be/DVrcZho3ksY I'll check in on this in the AM. Thanks! – CBRF23 Mar 19 '15 at 20:57
3

To solve this problem, follow the steps:

  1. Click the table which you want to be main format table.
  2. click design.
  3. Go to "Properties"
  4. Click "Resize table"
  5. In the range, enter the entire range from start of the table to the cell you want to be included in the format of the table.
  6. Click OK
  7. VOILA....its magically done.
sagar kansal
  • 31
  • 1
0

I was just having trouble with this as well, and the above answers really did not work for me. But I believe I found out what to do. It is close to the current best answer but a little more nuanced.

Hover your cursor over the table header in the column where the format refuses to auto-fill. Once it turns into a down arrow left click, and the entire column will be selected. Then right click, select format cells and change the formats. Now try adding a new row to the table and the formats should Auto-fill.

David Mason
  • 19
  • 2
  • 2
    This doesn't really answer the question. He asked about adding new rows, not columns, and getting the sheet to automatically format correctly. He knows how to manually format the row. – Josh Oct 12 '17 at 16:14
  • This does not add new columns, it formats the cells in the table column so that when you do add a new row that new row has the same formatting as the prior rows. If you have multiple columns you will have to do this action for each column where the formatting is not auto-filling correctly. – David Mason Oct 12 '17 at 17:02
0

I had this same problem, but for mine- it's because I had created a table on top of existing formatted cells. (I like a dark BG, not a white BG). The solution I found was:

  1. highlight my table (the whole thing)
  2. right click
  3. format cells
  4. set the desired format (in my case, "No Fill" BG)

and then new rows take on the new format thereafter. :)

LuminaryXion
  • 1
  • Welcome and thank you for your answer, but to me this looks like a duplicate of the accepted answer. Or do you disagree? – Edward Mar 27 '18 at 07:40
0

A special case occurs if you're inserting before the first row. Once inserted, you have the option to pick "Same as Above", "Same as Below" or "Same as Column" (which is typically selected by default). To make sure the "Same as Column" acts as expected, you need to make sure to apply the formatting to the column header also, so that it is applicable.

Islam Tawfik
  • 1
  • 2
0

it worked for me thanks to Aarons answer.

I had a table with date columns, time columns, number columns and text columns. To keep the correct format on each column when adding a new row, I did the following:

  1. Copy the entire table
  2. Paste only the formulas a few rows below the existing table
  3. Delete the rows of the initial table, so you are left with only the dataset not as a table.
  4. Make sure to format every column like you want it.
  5. Create table

The new table remembers the formatting it had when created.

Tbez
  • 1
  • 2
0

I have had this issue before. This seems to be an issue caused by having a Totals row, but that's only a guess. This will still clear the problem of formatting.

  1. Select the entire table, except the headers, but including the Totals row.
  2. On the Home tab, click the small dropdown arrow next to the Clear button on the Editing submenu, then choose Clear Formats from the list.
  3. With the formats cleared, select the last cell not in the Totals row and press Tab to create a new row. It should be in the General format.
  4. Select the column to be formatting, excluding header, but including cell from Totals row.
  5. Format as desired by pressing Ctrl + 1 or using Format Cells from context menu.
  6. Add new row again. It should be in your desired format.
  7. Change the Total cell in that row to its desired format.
  8. Final test, add another new row. It should still be in the correct format from step #6, while the Total cell is in its correct format as well.

Table Formatting

Donald Duck
  • 2,473
  • 10
  • 29
  • 45
Starnes Student
  • 101
  • 1
  • 1
  • 9
0

I had the same issue and this is what I found.

The issue happens only when inserting new rows moving to the end of the last row and pressing TAB.

If I add the new row using the menu option "Insert -> Table Row Below" the format is inherited properly to the new row.

Looks like a bug in Excel when adding rows using TAB.

Hope it helps.

aroldo
  • 1