Whether it’s a small list that been manually entered or a large list that’s been imported, Excel is ideal for storing list-based (i.e. row-and-column based) data. Let’s take an example of a membership list for a local tennis club…
The Pros
Storing list-based data in the worksheet cells (well, where else would you store it? Hold that thought!) has several benefits:
Visibility
It goes without saying that because the data is stored in the worksheet cells, it can be seen by anyone who opens the file (let’s ignore the fact that rows, columns and sheets can be hidden).
It’s easy to update it when you spot a typo or someone changes their details, extra rows can be added to the list when someone new joins the club and rows can be deleted when someone relinquishes their membership. That said, if the data has been imported you should really edit the source data and re-import it into Excel.
Sort and Filter
You need the list displayed alphabetically by Surname? You need to display it oldest-to-youngest by date of birth? You need to see only those members who have not paid their 2021 membership fee (subs)? A couple of clicks of the mouse is all it takes to sort or filter the list.
Add Calculated Columns
Need to display the current age or age next birthday of each member? Simply add an extra column, enter the appropriate formula and copy it down.
TIMESAVER TIP: If you convert the list to an Excel Table (Insert > Table or CTRL+T), there’s no need to copy the formula down. It will do it for you!
Easy to Summarise
Need to generate reports…how many members do we have? How much money did we generate from member subscriptions? How many members do we have in different membership levels? This can all be done using formulas or pivot tables.
The Cons
However, there are also some limitations…
One Million Row Limit
An Excel worksheet has 1,048,576 rows. For a small tennis club, I doubt that would be a problem. But if you needed to import a dataset with more than 1,048,575 rows of data (I’m assuming row 1 would contain headings) you’d have a problem.
Performance
Even if the dataset contained a much smaller number of rows, you may hit performance issues. I have seen Excel (even the 64-bit version) “choke” on a table of data containing approximately 80,000 rows. By choke, I mean, the screen goes blank, the blue circle of death/donut of doom appears and the app stops responding for what seems like forever.
Multiple Lists
It’s quite common to store your data in multiple tables/lists with one or more of those tables/lists being used as a “lookup table”.
Consider this scenario… The tennis club has 3 membership levels (juniors, adults and seniors). They charge a different fee for each level. In the screenshot below, the members details are stored in A:F and the membership levels and fees are in a separate table in I:J
To display the fee for each person, an extra column has been added to the members table (G). This column contains VLOOKUP (or XLOOKUP) formulas.
Now that we have all the information in a single table, the members table (A:G) can be used as the source for any pivot tables that are required.
This workaround is required because pivot tables cannot be created from multiple worksheet-based tables.
Additional Calculations in Pivot Tables
The screenshot below contains a pivot table showing total income broken down by membership level. The Finance Officer has requested another column that, for each membership level, displays a figure that is 15% of the total income. This represents the club’s charitable donation for the year.
The extra column (O) has been created using the Calculated Fields feature of pivot tables. Creating a Calculated Field will work in this instance but in general, they have many limitations (too many to cover here).
In Summary
So in summary, storing list-based data in the worksheet has benefits but at the same time there are limitations.
Remember at the start of this post I said “Storing list-based data in the worksheet cells (well, where else would you store it? Hold that thought!)”.
There is actually another place to store list-based data and that is in the Workbook’s Data Model. Storing data in there will get around many of these limitations. But that’s for another post.