A Key Advantage of Tables
I often tell people I am training on Excel that there are usually at least four different ways to do most things with Excel. That being the case, we are often called on to choose one method over another. In this post I would like to draw attention to a key advantage of using tables when they are a viable option. In later posts I will expand on this by applying it to specific settings.
Only rarely when working with spreadsheets does the amount of data you are working with stay the same or get smaller, it usually grows. And then you have the challenge of extending the carefully crafted formulas and/or formatting that you have set up. Herein lies one key advantage of tables that it is worth keeping in mind – they automatically grow as you add data.
Let’s look at a simple example to demonstrate this concept and show why it is useful. In the screenshot below is a very simple bit of data, with a very simple formula.
But of course, data in spreadsheets grow.
We could simply copy the formula in cell E4 down (or use autofill – hover over the lower right corner of the cell and double click on the + sign), but what if you want to add 5 or 10 columns of formulas and special formatting to go with them? What if we knew that we are going to be periodically adding more data? What if we now have 50,000 lines of data and want to zero in on only certain items? Wouldn’t it be nice to have our data automatically grow with us and to be able to see only certain parts of it when desired? Enter the versatile table.
To convert our data to a table, with our cursor somewhere in the data go to the Insert tab and select Tables. In the pop-up box that appears, ensure the box “My table has headers” is checked, since our data does have a header row, and hit OK.
Note, by the way, in the upper left corner of the second screen shot that it has given the table a very imaginative name, Table4, since this is the fourth table I created in the spreadsheet. Naming tables makes it very easy to refer to the contents of the entire table, however big it has grown, which will become important when referencing the table in future uses of it, such as if the table is the basis of a pivot table or a dropdown list.
Now go to cell E4, hit F2 to go to edit mode, and hit enter, and voila, the formula has filled down to all of the rows.
But wait, this is just the beginning. Let’s add some more data (and here is an important point to remember when working with tables). If you enter data in the row immediately below or to the right of the table, the table will automatically extend to include your new data. To demonstrate, in the screenshot of our sample spreadsheet below I entered “B2” into cell A9 to begin adding another row of data, then tabbed over to E9 to show that the formula has been copied down automatically and the table has been extended to include row 9 (as also evidenced by the blue line under columns A through E of row 9).
A nice feature about this is that it holds true whether I manually enter one line at a time or if I copy and paste data into the table, even thousands or lines (use Paste Special – Values so as not to bring formatting or other settings with you).
To further illustrate this advantage of tables, lets add another column to our table to mark up our price by 5%. We will go to F3 (the column immediately to the right of the table as mentioned before) and enter New Cost. As shown below, the table has extended itself to include the new column.
In cell F4, let’s enter the formula =E4*1.05, using the left arrow key to point to E4. In the screenshot below (taken before I hit Enter) notice that instead of showing E4 it shows [@Total]. This is because it refers not just to E4, but to the table field Total and will apply this to each row of the table.
After I hit Enter it applies the formula to each row, using the data from the Total column for that row.
I hope that you begin to see some interesting possibilities of working with tables compared to just data in cells. As mentioned above, this becomes especially significant if the table is the source of data for other uses, whether in this spreadsheet or another.
Before we conclude, let’s draw attention to one more very useful feature of tables, which is that they are easily filtered using the automatically inserted dropdown arrows by each header. For example, perhaps in the table above we only want to look at left widgets for a moment. Simply click on the dropdown arrow in the Description header cell, deselect “(Select All)”, select the two left widgets, and hit OK to see only those items. (Notice the missing row numbers, which indicates that these have been hidden based on our selection.)
Notice also the funnel icon in place of the dropdown arrow for the Description header now. This is an indicator to you that you are not seeing all of the data, a key visual clue especially useful when you are working with a large table or someone else’s data and wondering why you aren’t seeing everything you expected to see.
I hope this brief introduction piques your curiosity about other possible uses of tables. Another day we will discuss how to simplify reports built on a changing dataset and changes to data validation options by using tables as the source of the information for these.
Please feel free to reach out to me via the Contact page to discuss how we can assist you with applying tables or other simple solutions to a problem you are facing.
Pingback: Elementor #443 - sparrowsolutions.ca
Pingback: Multiple Criteria Lookup Formula, Or How To Create Complicated Formulas When Required - Sparrowsolutions.ca