Here we have some dummy Tables. Rather than adding and removing data, we will use the Table marker in the bottom right-hand corner of each source Table (in blue) to change the number of rows and then columns in use:
If we increase the number of rows in our 3-column source Table, and Refresh our green output Tables, we can see that the top Table expands downwards and automatically moves the 2 column Table beneath down to allow room:
The problem is that, with fewer columns in the top Table, not all the rows in our green 3-column Table are moved, hence the message that the refresh fails because cells would be moved within a Table, rather than the entire Table being displaced. This would also be a problem if the number of rows in the source Table was reduced rather than increased.
We can avoid the issue by changing the Table Properties. Here we have right-clicked in our 2-column Table, chosen Table, External Data Properties… and then selected the second of the options in the section:
Data formatting and layout, If the number of rows in the data range changes upon refresh:
As you can see from the above screenshot, this is not perfect as it causes our 2-column Table to overwrite the blank cells separating our two tables as well as moving our 3-column Table down, but at least it does allow the Tables to refresh successfully.
There are similar problems if we arrange our Tables horizontally rather than vertically and we change the number of columns in the source Table. Tables to the right can be moved when the number of columns increases:
In summary, if you do try and combine refreshable Tables in a single worksheet you do need to think very carefully about what will happen should the number of rows and/or columns change. Generally, the number of columns is likely to be more predictable so you could try and ensure your Tables all have the same number of columns, even if it means padding some of the Tables with empty columns. Otherwise, if the number of rows might vary, you will need to arrange the Tables so that Tables with more columns are positioned above those with fewer columns.
There are also some more creative solutions available to deal with the problem. Here, we have used Power Query to add a description column to each of our Tables, set the option in the Home Ribbon tab, Transform group to 'Use Headers as First Row' then Appended our Tables to create a single Table that includes all our exceptions. In the resulting Excel Table, we have turned off the display of the Header Row in the Table Design Ribbon Tab, Table Style Options group, and used Conditional Formatting to set the font of our heading rows to Bold and with a darker fill colour:
Archive and Knowledge Base
This archive of Excel Community content from the ION platform will allow you to read the content of the articles but the functionality on the pages is limited. The ION search box, tags and navigation buttons on the archived pages will not work. Pages will load more slowly than a live website. You may be able to follow links to other articles but if this does not work, please return to the archive search. You can also search our Knowledge Base for access to all articles, new and archived, organised by topic.