Append data to a Table with the Total Row showing

yah

Working with Tables is great. I mean, did you even see where you’re at? Our name says it all. But sometimes they just don’t work quite like you would think. As in life, we work with what we have. A lot of people work with Tables and just want them to work how they would imagine. You know, intuitively. We do too.

Today we’re focusing on a topic which has had people scratching their head for several years—in fact, since Tables were reinvented in 2007! Adding data below a Table, specifically with the Totals Row showing. You might be asking yourself, “doesn’t Excel already do this?” Well, kind-of-sort-of-but-not-really. As you can see in the below image, with no Total Row, data which is entered directly below a Table will get consumed into that Table no problem. This works as a person would expect, or at least how we would expect.

Appending new rows with no Total Row

tablerows_nototal

Now if you want to use a Total Row, because, well, why not, then it’s not so straight forward, but you can still do it. In the image below you can see where you need to grab the lower-right Table indicator and drag it down to consume those new rows. The new rows will be consumed into the Table and the Total Row will appear in the proper place, switching with those newly consumed rows.

Appending new rows with Total Row

tablerows_totalnative

While the first image above will work automatically as you type, the second image above will not and requires an extra click-and-drag to make it happen. We’re lazy. If things should happen automatically and they don’t, in fact requiring more work from us, then we generally whip out the code and make our lives easier by coding it ourselves. This is what we’ve done here. The code below will automatically add rows of data once entered below your Table when the Total Row is showing.

Note: If your Total Row is not showing, the behavior will be that of native Excel, and rows will be consumed into your Table until it cannot figure out if you want the rows consumed into the Table or not.

Appending new rows with fancy-dancy code

tablerows_totalcode

This code belongs in the Worksheet module of the desired sheet to add this functionality to. If you need to know how to add this code, check out our cool Canadian over at Contextures for assistance.

There are two constants declared at the top of this code.

  1. SingleRowOnly. This specifies whether multiple rows should be included in appending into the Table, or if only a single row should be.
  2. MaxRowCount. As to not go crazy with appending rows to a Table automatically, this is the maximum number of rows to include at any one time. If SingleRowOnly is set to True, this constant is moot.

In the above code we’ve done what we think is good housekeeping. We’ve taken inventory of the Table settings at the start, then set them back when we’re done.

Another solution to this would be to Unlist the Table, then re-create it. We don’t recommend this because if you have calculated columns or dependent references pointing to structured references of the Table, they will get forced to standard cell referencing (absolute) and will not go back to structured referencing automatically. Because of this is why we work within the confines of keeping it a Table.

TL;DR

Copy code above to worksheet module. Enjoy automatically having rows be appended to Table with your Total Row showing.

4 thoughts on “Append data to a Table with the Total Row showing

Leave a Reply

Your email address will not be published. Required fields are marked *