When I first transitioned from the classroom to the central office, I did not know much about spreadsheets. As a new testing coordinator, I quickly learned that I would be using spreadsheets often, and I needed to get on really friendly terms with them. Fourteen years of central office life later and I can say that rarely do I have a day when I am not involved in some type of spreadsheet.
At first, it can be daunting to create one or manipulate one that you have downloaded from a program that your district uses. I get it. Excel is a sophisticated program, and I learn how to do new things in it every year.
Increase Efficiency
So from time to time, I will be sharing my favorite excel functions and formulas – the ones that I help me more efficient and effective. I am not a spreadsheet expert. I have not taken a class. I am just sharing what I have learned on my own. These are the functions and formulas that I use to make me better at my work and save my sanity (not kidding).
Today, I am going to share with you a basic excel function that I use when I need quick information. It is the Excel SORT function. Those of you who are spreadsheet wizards, well bye-bye. I know this tutorial won’t interest you. But for those of you who are not familiar with spreadsheets, stay with me!
A Quick Tutorial
Okay, let’s get started! Click on the video below for the SORT tutorial.
In case you prefer written instructions and screenshots, then I have included the tutorial steps I demonstrate in the video below. The script explores three examples or scenarios. Keep reading!
Script:
Let’s take scenario 1. I have a data file with many student records. The data file contains several fields including student last names, first names, scores, teacher names, etc. Let’s say I have a need for these student records to be sorted alphabetically. Not doing that manually. No way. So here is what we do.
Step 1: Click on the Sort and Filter icon on the ribbon. Choose Custom Sort.
Step 2: In the dialogue box, you want to make sure that the ‘My data has headers’ box is CHECKED.
Step 3: Under Column, Sort by, click on the dropdown arrow and choose Last Name.
Step 4: Click OK.
Now I have a sorted sheet, but notice the I have a few students with the same last name, and I want the sheet to be alphabetized by first name, too. No problem.
Step 5: Go back to the Sort and Filter icon on the ribbon and choose Custom Sort.
Step 6: Click Add Level and notice that a Then by was added under your Sort by Last Name.
Step 7: Use the dropdown arrow for Then by to add First Name.
Step 8: Click OK.
You can use these steps to keep finessing your data. For example, let’s say that although you still need the data alphabetized by name, you also need it sorted by grade level first. You can do this easily by changing your dropdown options.
Step 9: Sort and Filter > Custom Filter.
Step 10: In the first dropdown, change Last Name to Grade.
Step 11: In the second dropdown, change First Name to Last Name.
Step 12: Click Add Level and then choose First Name in the dropdown.
Step 13: Click OK.
And the list is now (wonderfully!) sorted first by grade, then last name, then first name.
Scenario 2 is a variation on the last example. In this scenario, let’s say that we need to see the data sorted by teacher. Maybe we still want the data sorted by grade and name, too.
Step 1: Sort & Filter > Custom Sort
Step 2: Change the Sort by Grade to Sort by Teacher_LN.
Step 3: If you don’t need the students sorted by grade, click OK. Student names are still alphabetized.
OR
If you want to keep the sorting by Grade, Last Name, First Name, then follow Steps 10-13 above (scenario 1).
Scenario 3 is a bit different, but the sorting steps remain the same. Instead of dealing with names, what if we needed our data sorted by score. Let’s use the Reading achievement level as our example. That is the column named Read AL.
We start the same way.
Step 1: Sort & Filter > Custom Sort.
Step 2: Change Sort by Teacher_LN to ReadAL.
Step 3: In the Order column on the right, you can choose how you want the data sorted. I will choose Largest to Smallest.
Step 4: I can click OK now, if I want to and preserve my other levels of sort. Or I can delete those levels easily by moving to that level and then choosing Delete Level and then OK.
If you want a handy reference, bookmark this page or download the Excel SORT tutorial PDF.
Let me know if you found this tutorial helpful by sharing in the Comments section. What spreadsheet functions and formulas do you use? What other tutorials would you like to see? Let me know in the Comments section!
Recent Comments