Sometimes we need to group data, because they are so many and difficult to manage. The Numbers Category function is able to do this in a simple way, while also performing operations with that grouped data.
For the example we will use the electricity consumption recorded by an Eve Energy socket, which provides us with data at 10 minute intervals, so in a single day we have 144 values. From Eve's application we can download an.xlsx file that we can open from Numbers without any problems.
Although it is not necessary, I will change the format of the date and time to have it in Spanish (dd / mm / yyyy hh: mm: ss) by clicking on the header of column A and displaying the right sidebar of Format, with which we can change the format of the entire column in one go.
While it's not necessary, I'll set the first row as the header. So Numbers will know what each column is called, will know it's a title and are not values, will always keep it in view, etc… To do this I display the Format sidebar and select 1 row as the header (there may be more).
We will now generate a category by clicking on the “Add Category” button in the top bar. It will give us the possibility to choose on which column we want to create the category and in this case we will tell you the "Date" (or "A" if we have not set the first row as a header).
In the “Organize” panel, on the right, we can choose why we want to group. In this case I have chosen "Day".
As you can see, a blank column appeared on the left and a darker row showing the grouped day. The values still appear below separately, but then we can hide them if we want.
In the grouped row, in column B, there is no value. We can click on the cell and a cogwheel will appear on the right, which will allow us to choose which value we want to show for the group.
In the case of the example, I want to add all the values of the same day, so I will choose “Subtotal”. If I wanted to know how many rows (records) I have in each group I would use "Count", or if I wanted to know the average, minimum or maximum value of the group, it would be enough to change the value of the summary.
Now we can see the sum of all the values of the same day within the category row.
Finally, we may be interested in hiding all details and only seeing the group. To do this, just click on the triangle to the left of the category (in this case the grouped date) and everything that hangs from that specific summary will be compressed.
If we want to compress all days at once, just hit the ⌥ option while clicking on that triangle and all categories will be collapsed.
If you want, you can copy the values from column B and bring them to another table or file and only what you are seeing will be copied, not the compressed (hidden) values.
While there are many screenshots in this article, all of this (from opening the original file) can take up to 20 seconds at most.