Table ballooning in size!

[Mr2017]

Hi

I have a table in Excel that goes up to 20,000 rows.

The range is $A$2:$O$20000.

The table is in a tab where new data is added on a monthly basis.

The new data is taken from a report where the original report covered 1st March 2015 to 31st July 2017.

When new data is added, the end date of the report is simply changed to the last day of the previous month eg now that we're in September, the report would be run from 1st March 2015 - 31st August 2017 (instead of 31st July 2017).

Now here's the issue:

When I paste the latest report (which has 6,105 rows) into the table, Excel says

"This table inserted rows into your worksheet. This may cause data in cells below the table to shift down."

I click 'Ok' to this message, but instead of the table staying at 20,000 rows (I only have 6,105 rows of data), it expands to ALL rows in the spreadsheet! Up until row 1,048,576 (the last row!).

Any ideas why?

I'm sure someone out there knows!

If you know the answer, I hope you win the lottery!

Thanks in advance!

 

[Mr2017]

I found the answer!

When I originally created the report, I would export a CSV file of the data then highlight columns A - H and paste them into my Excel report template.

This worked fine when there wasn't a table.

However, I was asked to add a table in the tab where the data is pasted.

After the table was added, pasting columns A - H would add hundreds of thousands of rows (until row 1,048,576) which would make the spreadsheet increase from 1MB to 50MB!