Newly inserted rows in a table do not get properly formatted

Question

I have a table (not just a cell range, but a full table with table name, etc.). I have applied striped formatting to it. When I add new blank rows to the end of the table, they don't change colors as the rest of the rows, but use the formatting of the last rows. So if my table has this formatting:

white row
blue row
white row
blue row
white row
blue row

and I add some more rows, they will be all blue (if my last row was white, they would all be white). It doesn't matter how I add them:

dragging the lower right corner of the table;
pressing TAB while being on the lowest right-most table cell;
resizing the table difining a larger range;
pasting new data;

What could be the problem?

Excel does not normally detect (and propagate) patterns of formatting, spread across multiple lines. See whether How can I ... copy formulae but not ... formatting? helps you. — Scott - Слава Україні, Nov 23 '13 at 00:00

score 4 · Answer 1 · answered Jun 04 '14 at 13:06

4

Highlight the messed up area of your table
Select the "Clear" tab in the "editing" tab of "Home"
Select "clear formatting"

This will leave all your data, but will readjust it back the the blue, white, blue, white that it is supposed to have.

answered Jun 04 '14 at 13:06

user329315

41

score 2 · Answer 2 · answered Sep 05 '17 at 21:50

This just worked for me with a similar problem (green and white table that wanted to turn all new columns pink):

Highlight the entire table area.
Click "Format As Table" under the "Design" menu tab.
Right-click on the table design that you are using (or that you want to use).
Click "Apply and Clear Formatting".

New columns are no longer pink! This process got rid of formatting not associated with that design (e.g., cell borders), so I had to redo those. But at least when I put those back, they were then applied to new columns I added as they should be.

I am using Office 365.

Dan Henderson · Accepted Answer · 2015-08-25T20:04:42.087

2

Turn on "Banded Rows"

In the Table Design ribbon, within the Table Style Options group, check the box for "banded rows".

This should make all new rows mimic the formatting of rows 1 and 2 of your table (typically rows 2 and 3 of the spreadsheet).

edited Aug 25 '15 at 20:04

answered Jul 06 '15 at 16:12

Dan Henderson

1,385

score 1 · Answer 4 · answered Nov 25 '13 at 12:37

1

Did you create the TABLE by using the 'Format as Table'? In that case Excel will follow the pattern.

If you did not, then select your TABLE. In the HOME tab, click on the 'Format as Table', click on the desired design. If your table has headers don't forget to select the 'My table has headers' option, click Ok and Voila!

In this table you can go to the last row/column and add extra rows/columns till eternity, they will follow the color pattern.

answered Nov 25 '13 at 12:37

Firee

1,860

Unfortunately that doesn't work. My table is already using "Format as table". I also selected it and applied "Format as table" again and it didn't help :( – Alexander Popov Nov 25 '13 at 12:53
@AlexPopov Is this happening with all your Excel files or this particular one. You could try to create a new file with a formatted table and see how it behaves. If this also does not do it, then you can share the file and let us have a look. – Firee Nov 25 '13 at 13:19
This is happening only with a particular sheet. Thank you for the help and sorry for bothering you with this question. – Alexander Popov Nov 25 '13 at 14:18
This is happening to me too, with an already "formatted as table" region. – Johan Boulé Jun 04 '15 at 13:36

Cool Blue · Answer 5 · 2013-11-29T21:09:08.077

1

Just clear the formatting and the table formatting will re-assert. Select the area with the wrong format, type CONTROL-1, select the Fill tab, select No Fill.

edited Nov 29 '13 at 21:09

answered Nov 25 '13 at 18:07

Cool Blue

201
1
14

1

Please provide step-by-step instructions. – gronostaj Nov 25 '13 at 18:36

score 0 · Answer 6 · answered Aug 11 '20 at 17:13

0

I had success by going to editing (on the far right) under table tools, clicking clear formatting, then re-doing the formatting.

answered Aug 11 '20 at 17:13

Chris

1

score 0 · Answer 7 · answered Jul 08 '15 at 13:52

I had a similar problem, where the table borders (not the banding) did not copy to the added rows. Reconverting the table to a range, then applying the desired formatting (borders), and then reconverting to a table did the trick. So, I guess that applying the formatting prior to the conversion to a table is a good idea here.

score 0 · Answer 8 · answered Jul 08 '15 at 17:52

0

What worked for me was to go to Table Tools and then Resize Table, changing the range to include the new rolls that I added but that didn't have the correct formatting.

answered Jul 08 '15 at 17:52

Randy B.

1

score 0 · Answer 9 · answered Sep 06 '15 at 10:05

I just had this problem. Literally, right now. I converted the entire table into a range again. Then I selected all the cells went to the Home Taskbar, selected the shading and chose No Background Colour. Once all the cells had no background colour I re-created the table and it started to work again. I really don't know why or whether this will be consistent.

score -1 · Answer 10 · answered Feb 08 '16 at 13:58

-1

I had a similar problem with pasted table data from a website. The formatting was half grey and half blue, but would never become white, even when using "clear formatting" or manual color selection.

What worked was to use "clear formatting" first as said by user329315, and then copy the whole table and insert it, choosing "Keep original layout" (first option, shortcut U). After that, you can remove the old table.

(I would have posted this as a comment, but stackoverflow does not let you comment without 50 reputation on each site separately...)

answered Feb 08 '16 at 13:58

user121391

1,393

This is more of a comment then an actual answer. Comments should be kept to the comment section. Comments should not be submitted as answers because your inability to post comments. Comments submitted as answers are subject to removal by the community. Any deleted answers submitted, count against your ability to post answers in the future, stick to questions you can answer without submitting a comment to to earn the require reputation to submit comments. – Ramhound Feb 08 '16 at 15:48
Except you didn't answer the author's question ( or add anything that hasn't already been said before ). there is a good reason for not allowing comments to be submitted by new users. Comments should not actually be used to answer questions. – Ramhound Feb 08 '16 at 16:02
Answers are not to indicate you had a similar problem, or indicate you also have a similar problem, it is to answer the question the author had specifically. If you are not answering the proposed question, then whatever you are writing, is likely not an actual answer to the question. – Ramhound Feb 08 '16 at 16:24
Your answer showed up in a review queue because of the quality. You don't have to agree with my feedback. I am flagging all our commentary to each other as not being constructive. – Ramhound Feb 08 '16 at 22:10

score -1 · Answer 11 · answered May 07 '19 at 15:51

-1

go to the bottom right corner of the table where the format stops working, click and drag the corner down a few rows and see if your formatting correctly extends

answered May 07 '19 at 15:51

Amelia

1

Hi Amelia, please be sure to review a user's troubleshooting steps before posting an answer. Alex included "dragging the lower right corner of the table" as something he had already tried. In this case, I would post a comment to find out where he dragged the table from before posting an answer. – Karen927 May 08 '19 at 00:20

Newly inserted rows in a table do not get properly formatted

11 Answers11