If you are using Excel 2007 then I suggest clearing the formats from the
table (select the table then on the Home tab click Clear/Clear Formats) and
converting it to an Excel table. For the latter, select the table (necessary
for the layout they are using in the template), on the Home tab click Format
as Table, select a table style, in the Format as Table dialog box select "My
table has headers", and click OK. Then on the contextual Table tools Design
tab, in the Table Style Options group you can add additional formats such as
for the Last Column, etc. Note the formats that are applied depend on the
table style you are using. Once the data has been converted into an Excel
table you can change the table style at any time without the need to select
the entire table. Click in the table and then select a new style.
Now when you add data to the bottom the table should automatically expand.
The formula in the Balance column will be copied and the formatting will be
duplicated as well, including the alternating row color if you have the
option selected in the Table Style Options.
If you're not using Excel 2007 then other than copy/paste (or using the Fill
Handle to copy) inserting rows in between and manually modifying the shading
there's not a lot you can do. If you want to correct the manual shading then
clear the formats in the table (or just the Shading format), add Conditional
Formatting using this function: =MOD(ROW(),2) to the table range and add
your preferred shading in the Format options. This will at least enable you
to automatically add the banded row shading when you copy or insert rows.
~~~~~~~~~~~~~~~
Beth Melton
Microsoft Office MVP
https://mvp.support.microsoft.com/profile/Melton
What is a Microsoft MVP?
http://mvp.support.microsoft.com/gp/mvpfaqs
Guides for the Office 2007 Interface:
http://office.microsoft.com/en-us/training/HA102295841033.aspx