Excel Tables - how to update?

Graemea

Board Regular
Joined
Oct 30, 2015
Messages
93
Office Version
  1. 365
Platform
  1. Windows
Hi,

i have a simple workbook that consists of 6 worksheets, 5 of which are populated with reports that are pasted in from an Excel extract from 3rd party software, and the other one uses VLOOKUPs to extract and consolidate the data from the data sheets.

I thought that it might be a good idea to convert the data in the 5 data sheets into Tables (Ctrl+T). However, the data in these sheets is updated each month by pasting over new data, which may contain more (or fewer) columns and rows than the previous month.

Are Excel Tables able to accommodate (e.g. expand/contract) pasting over with new data or would I need to paste in the data each month and then convert the ranges to Tables?

Thanks!
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
882
Office Version
  1. 365
Platform
  1. Windows
Tables will automatically update. A message will appear as you paste data which is more than the previous data.
 

Graemea

Board Regular
Joined
Oct 30, 2015
Messages
93
Office Version
  1. 365
Platform
  1. Windows
Thanks.

I did a quick test and it seems to work when I paste over more data than was there before (the Table range automatically expands, though no message appeared) but the Table range doesn't contract when I paste over less data than was there before; the extra data seems to remain?
 

Graemea

Board Regular
Joined
Oct 30, 2015
Messages
93
Office Version
  1. 365
Platform
  1. Windows
Does that mean that this is a known problem with Tables?
 

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
882
Office Version
  1. 365
Platform
  1. Windows
If there is excess data in table and if you paste less rows or column of data it wont clear the excess itself. You have to first clear the data and then paste.

VBA might help i suppose to remove all the data before you paste it.!!!!
 

Watch MrExcel Video

Forum statistics

Threads
1,114,519
Messages
5,548,523
Members
410,844
Latest member
Juno49
Top