How do you extract meaningful insights from rows and rows (and rows) of data?
The truth is, as humans we're not very good at dealing with lots of detail - the average person can only hold 7 items in their working memory at once - which spells trouble when you have to deal with datasets containing hundreds, even thousands, of items.
Imagine your boss asking for a report on last month's sales figures, and you reading out the details of every individual sale... You get the picture.
To make any sense of large amounts of information, you need to collapse it down until it's a manageable size.
There are a number of ways of doing this. One that I've touched upon already in these blogs is filtering (see the post here) - this is a great way of throwing out data that you're not interested in at all.
But what if you want to include all your data? How do you bring things under control?
Often, the answer is grouping. You bring related items together, e.g. transactions from the same day, or all sales of fruit items, and present a summary of the data from each category.
Returning to our sales report analogy - if you can tell your boss the breakdown of total sales by client, or by lead type, or by region, you've got a much better chance of being in their good books!
In this blog, I'll show how you can group your data in Excel - and how Schematiq can make it much easier!
Grouping in Excel
The data we'll be using is an example of some personal transactions, like you might get from a bank statement. The aim is to group the transactions into their categories, so that we can see the total spent on each one - and where all the money is disappearing to...
Note: You can get hold of the spreadsheet here, and follow along.
In Excel, this takes two steps:
- Sort the data - so that transactions of the same category are together.
- Add subtotals - so that we can see the totals for each category block.
Step 1 - Sort by category
First, we need to collect together the transactions from each category. We can do this by sorting it by the column "Category":
- Highlight the data (including headings).
- Click "Data" to open the data toolbar.
- Click "Sort" to open the sort dialog.
- Sort by column "Category", and press "OK".
Step 2 - Subtotal (category, sum, amount)
Now that the table's in the right layout, we can add subtotals for each category:
- Highlight the data again.
- Within the "Data" toolbar, click "Subtotal" to open the subtotal dialog.
- At each change in "Category"...
- Use function "Sum"...
- Add subtotal to "Amount".
And there were go! We can see straightaway the total spend for each category, and we can even collapse the data to the totals only by clicking the " - " buttons on the left hand side:
Grouping using Schematiq
Now that you know how to group your data in Excel, let me show you how to do the same using Schematiq. This only takes two steps:
- Create a Schematiq table - so that we can use Schematiq's add-in functions.
- Group the table - using the tbl.Group function.
Step 1 - Create a Schematiq table
In cell D20, we create a Schematiq table from the data:
= tbl.Create(B5:E18, B4:E4)
= tbl.Create([data], [columnNames])
Step 2 - Apply tbl.Group
Schematiq has a function for this exact situation - tbl.Group. We give it the Schematiq table, the column to group by ("Category"), and the column to sum for each category ("Amount"):
= tbl.Group(D20, "Category", "Amount")
Tada! The transactions have been grouped together under their categories, just as we wanted.
Alternative - Using Schematiq's Viewer Wizard
If you prefer buttons to formulae, Schematiq's Viewer provides another way of grouping the table.
With the cell containing the Schematiq table (D20) selected, we can click on the little menu button () in the Viewer to open the table menu.
Selecting "Group" opens up two boxes - one for columns to group, and another for columns to aggregate (sum). We drag the "Category" and "Amount" headings into their respective boxes, and the Viewer gives us a preview of the result.
Once we're happy with the preview, we click "Apply" - and voila, there's our grouped table, together with a formula to recreate it!
PRO-Tip: Group without losing details using tbl.Pack
Having the totals for each category is great, but sometimes you need the details to hand as well. Let's see how we can do this with Schematiq...
Step 1 - Group table using tbl.Pack
We'll need another of Schematiq's add-in functions: tbl.Pack. This separates the rows into groups, like tbl.Group, but instead of summing values within the group, it gives us all the group's rows - a table within a table!
To see how this works, let's call tbl.Pack and tell it to group by column "Category", and call the new column "Transactions":
= tbl.Pack(D20, "Category", , "Transactions")
Looks weird, doesn't it? Each of the new cells is a table in its own right, within the cell of a table...
Step 2 - Expand subtables
To get to the data inside the subtables, we click the little arrow within the cell.
So we've managed to group the table - without losing any information!
Try it yourself
As with most things, Schematiq is best learnt by trying examples. You can download a copy of the spreadsheet used in this article here - the Schematiq functions like tbl.Group will require an active subscription, and you can request a trial by contacting us.
To see the other ways that Schematiq can help you to work with data in Excel, have a look at some of our other articles: