Last week I showed how you can use a custom list to order the months in a year for rows in a pivot table. Perhaps you already guessed that the same technique works for column labels as well. But did any of you try to sort using the 3 character month names rather than the full month names? If you did, you found that it did not work. Were you surprised and why? The answer is that the custom sort list on works on cells that have exactly the same values. Since the original data displayed the full month names, all Excel can do is match the cell values with the values in the list to define the sort order. It does not know that August is the same as Aug or that Dec is the same as December. Sort of makes sense now doesn’t it?
So what if you have to sort data that is not in one of the existing custom lists. After all, Excel only includes 4 custom lists out of the box, two for days of the week and two for month names. What if you have other labels that you want to sort in a custom order other than alphabetical? A recent example is a pivot table I created using some school data. (Before anyone thinks that I’m revealing confidential school/student data, just stop. The data in this example as well as the example has been totally revised.) In the figure below, I’m showing the first few rows of absence data collected by teachers of a fictitious elementary school.
The first six columns were ‘loaded’ from an application that collects absences from each teacher for each grade for each day. The remaining columns were calculated using the following formulas:
These additional columns will allow us to ‘group’ data by date ranges in the pivot table using natural groupings such as month, week, or week number. You may be familiar with all of these formulas except for the last one. The TEXT() function allows you to display different parts of a date/time either as integer or character data. When we reference a date cell and use a parameter like “mmmm”, the function return the full month name of the date. On the other hand, a parameter like “mmm” will return the 3-letter abbreviation of the month name.
Given, these columns, you can select all of the data, including the header row, and select Pivot Table from the Insert ribbon. Displaying the new pivot table in a new worksheet, I proceeded to define my pivot table.
I dragged the Absences field to the Values area and accepted the default which sums the absences. Right off the top, I now know how many absences there were in the entire time period for all grades at the school.
I then added Grade to the Row area and under grade I added LastName because the natural hierarchy is to have teachers within a grade, not grades within a teacher.
Finally I added MonthLable, one of my calculated columns to the column area. The resulting pivot table which Excel immediately calculated is shown in the following figure.
This report would be great and I could print it and say I was done except that the grades appear to be in a random order. Of course, the order is not random. It is alphabetical. However, alphabetical is not the natural order of grades in an elementary school. So how can I change the order of the rows to display the grades in their natural order?
The problem is that the available custom lists do not include a list that orders elementary school grade names. The natural question is whether you can add a new custom list to the four existing ones. Fortunately, the answer is yes and it only requires a few steps.
If you are using Microsoft Office 2010, click on the File tab above the ribbons in Excel. In the Back Stage area (which is really what the page that appears is called, select Options from the left menu. Excel has many options. More options than can appear in a single dialog. So Microsoft developed a paged version of the Excel options where each page is identified by a an option group name shown in the column on the left of the Options page as shown below.
Select the Advanced group of option and scroll down through the options if necessary until you find one that reads: Create lists for use in sorts and fill sequences. Click the button after this label with the caption: Edit Custom Lists…
This displays the Custom List dialog box that displays two lists controls along with several buttons and a text box. Initially, the Custom Lists displays the four lists we saw last week, but it also has an option at the top that reads: NEW LIST. If this list option is selected, the second list box should appear empty because if you are creating a new list, it needs to begin with an empty list. In this second list, type in each value you want in the list in the order that you want sorted items to appear. Each value must be entered on a separate line with no blank lines between values.
When you have finished entering all of the list values, simply click Add to create the new list. The new list now appears at the bottom of the first list while the value continues to display in the second list. You can edit the values, delete values, or change values and click Add a second time to save any changes you made as shown in the figure below. Do not use the Delete button to delete individual values. This button is meant to delete the entire selected list. If you have no additional changes, clicking OK closes the dialog.
Now we can use our new list just like we used the default custom lists last week. Go to the pivot table and click the dropdown button to the immediate right of Row Labels. Because there is more than one field in the row labels area, you must first select which field you want to customize the sort order. For this example, I needed to make sure that Grade was selected. In the dropdown, select More Sort Options.
In the sort dialog, define whether you want to use the custom list sorted ascending or descending. For this example, I will select Ascending because I want the lower grades at the top of the list. Make sure that the dropdown beneath the sort direction continues to point the field that you want to sort, Grade in this case.
Click the More Options button to display the dialog that lets you choose which custom list you want to use. In the More Sort Options dialog shown below, deselect the AutoSort option and then select the custom sort list you want to use. The figure below shows the grade sort beginning with Pre-K selected as the custom list to apply to the row field.
Click OK to complete your selection and close the More Sort Options dialog. You will also have to click OK on the Sort dialog. Returning now to the pivot table, you should see that the grades have been resorted based on the order defined by the custom list. It is that easy. You can perform this same procedure for any number of rows fields, or column fields, or both. The column below shows the resorted pivot table.
After creating the first pivot table, you can go back to the data and create a second pivot table, perhaps using different row or column values such as the one shown below which added a day-of-the-week field and then used a custom list order to insure that the days are displayed chronologically across the top of the pivot table.
Next time for our weekly technical discussion, I will switch over to SharePoint 2010 to look at the new Chart web part. See you then.