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

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

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,577
Messages
5,548,861
Members
410,881
Latest member
toonces
Top