Understanding Sorting and Grouping

Understanding sorting and grouping is in many ways one of the first steps toward becoming a proficient report builder. Both allow you to better organize your data!

In this article, we're going to take a bunch of unsorted food items from a restaurant, and using the power of Sorts and Groups, turn it into a professional looking menu.

Step 1: Adding Sorts
Step 2: Putting our Sorted data into Groups
Step 3: Adding Sub-Groups

1. What is Sorting?

Sorting is the act of arranging data in either ascending or descending order. Pretty simple, right? Looking at the spreadsheet below, we can see that it is completely unsorted. No one column is arranged in any ascending or descending pattern.

Designer View: A basic table containing the data for a restaurant menu.

Output: An unsorted table of restaurant menu data.

If we wanted to, though, we could sort this list by either Course Number, Course, Dish, Dish Type, Diet, or Price.

Let's say we wanted to turn this spreadsheet of unsorted data into a restaurant menu. What sort of organization does a restaurant menu typically have? In most cases, the dishes are grouped by course. Appetizers usually come first, followed by main courses, sides, and desserts. To group by course, however, we must first sort by course so we know what order the courses go in and which dishes belong to each one.

Let's sort by putting the courses in ascending order. To do this, we'll have to access the Sorts menu, located in the Report Options dropdown menu.

Accessing the Sorts menu.

Then, we'll have to select the column we wish to sort by. In this case, we would like to sort by Course and have the courses display in ascending order. Let's click 'OK' to save this setting and run the report.

Adding the course column to the Sort By pane.

Upon running the report, you'll notice that the courses are sorted in ascending alphabetical order, but they're not in the correct order for a menu. The letter "D" may come before "E", but that doesn't mean desserts should come before entrees on the menu.

Let's fix this by sorting on Course Number instead. This way, we can be sure that the courses will appear in the correct order. To do this, we will navigate back to the Sorts menu, click the red X to remove our sort on Course and then add Course Number to the Sorts By pane instead. Once again, let's be sure that the column is set to sort in ascending order. After clicking 'OK' and running the report, we can confirm that that the courses now appear in the correct order. Now we can begin to group!

Sorting by Course Number.

Output: Menu data sorted by Course Number in ascending order.

2. What is Grouping?

Grouping is the process of breaking up rows of data into sections that share a common trait. This makes the document easier for the eye to scan, allows for the removal of repeated values in the table, and broadens the types of calculations you can make.

Let's focus on the first two goals for the time being.

Grouping to Improve Readability

Since we've sorted on Course Number, we're now able to group on Course Number, so let's start separating the courses into groups by inserting a line or two of space between each one using a group header.

To add a group header to the report, we click on the section name of any existing report section in the Report Designer grid, hover over "Add Section," and then select "Group Header" from the list of available sections, clicking on that option.

We are then presented with the Group Header menu. It is at this point that we must specify what column we are grouping on by clicking on the dropdown arrow and selecting, in our case, Course Number. Congratulations, you've just created your first group! Let's click OK and take a look at the report designer with that header applied.

Group Header menu.

Looking at the Report Designer, we can see that under "Section", a new item has been added. This is our group header on Course Number. We can also see that a blank row has been inserted into the report for that section.

Designer View: A new Group Header has been added.

Like the headers and footers of a Word document, this header is just a blank space in a specific spot for you to fill with information. A page header, for example, is a blank space at the top of each page of a document, and you might choose to put an author name there or maybe a page number. In our case, this group header is just a blank space at the beginning of each group of course numbers that we can fill with whatever information we want.

Let's see what the report looks like without any information in the group header.

Simply inserting extra space between each group has improved readability! It's easy to see the three sections of dishes. Traditionally, though, menu sections have headers displaying the course name, so let's go back to the Designer and fill our group header spaces with that information.

Designer View: Clicking and dragging to add a data field to the Group Header.

It's worth noting here that even though we've grouped on Course Number, we can use the Course name to label each header. Let's add a little formatting to the header by increasing font size and add a new row above row 4 to create some padding between groups. Now, when we run the report, it has each group of course numbers labeled with the corresponding course name.

This is starting to look like a menu! Now that we have group headers, though, it's clear there's some redundant information in the table, like everything in the Course column. The Desserts header tells us that the Chocolate Souffle and Vanilla ice cream are desserts, so we don't need to repeat that information in line with each dish name.

We could also stand to remove the Course Number column now, since menus don't traditionally number their courses. It's okay to remove a sort column from your report design! The sort still remains saved in the Sorts menu. So let's remove the unnecessary info!

First, we'll drag the Course Name field currently in cell A4 to cell C4 so that we can delete columns A and B without removing the Course Name.

Designer View: Dragging a field from one cell to another.

Then we can hold down the shift key and click both columns A and B to select both columns, right-clicking thereafter to display the Delete Columns option.

Designer View: Deleting columns.

After making these two changes, our report design looks like this, and we're ready to run the report once more.

Designer View: A simplified report.

Output: A simplified report.

This is really starting to look like a restaurant menu, but there's still some repeated information on the table under Dish Type. Anytime you see repeated values in a column (in this case, multiple Breads, multiple Salads, and multiple Soups under Dish Type), you have the opportunity to sort and group on that column to simplify your table! Let's explore what happens when we introduce additional sorts and groups to the data.

3. Multiple Sorts and Groups

Sorts and groups are unique in that they nest inside one another by default. So, when you add a second sort to a table, it doesn't come after the first one--it goes inside the first one. By this we mean that a new sort or group doesn't undo any of the previous sorts or groups. They work in tandem with each other!

Let's see this in action by sorting on Dish Type in our example. To do this, we will return to the Sorts menu and add Dish Type to the Sort By pane as we did with Course Number, making sure again that the Sort Order is set to Ascending.

Sorting by Dish Type.

Let's run the report to see what effect this has on the data.

Output: Two sorts.

As you can see, sorting by Dish Type didn't undo our sort on Course Number; it just added itself to the existing sort.

Similarly, if we now group by Dish Type and add group headers for those groups, they will nest inside the group on Course Number, not replace it. Let's begin by adding a new group header on Dish Type.

Adding a second group header.

Then, we can do what we did with the Course Name and fill the new header with the information we'd like it to display. Let's have it display dish type. Since we know that we only need that information on the report once, let's drag the field over from its spot in cell B7 to the new vacancy in the new group header.

Report Designer: Dragging over a field.

Now we can remove column B from the report altogether, further simplifying the report. Remember, to do this, simply right-click on the column letter you wish to delete, and a menu will appear displaying the Delete Column option. After this step, let's increase the font size of cell A6, italicize it, add bolding to A5, and insert a new row above row 6.

Report Designer: Formatting with two group headers.

We now have a group inside another group!

Output: Two groups nested.

Now that we know how adding additional sorts works, let's complete this menu by sorting on price so that the least expensive dishes come first and the most expensive come last. This is also traditional restaurant menu formatting. Again, note that adding this new sort does not undo previous sorts. With a little currency formatting on Price and a deletion of our original column headers, we have as a final product a triple-sorted, double-grouped table designed to read like a menu.

Sorting by price.

Format Cells: Adding a currency symbol and two decimal places.

Report Designer: Deleting a row.

Output: Two groups with an additional sort on Price.

Key Concepts to Remember

1. You must sort on a column/field before you can group by it.

2. Sorts and groups nest inside one another. Adding a new sort or group does not replace the previous ones.

3. A field doesn't have to exist as a column on the report in order for you to sort on it.

4. You can have a group header on one field and label it with contents from another.

5. Groups are useful for improving report readability, removing redundant data, and broadening your calculation opportunities.