Tuesday, April 29, 2014

Menu Monday - Sort


Using the "Sort" functionality in Excel is an excellent way to help organize your data set.  It's very useful in sorting either alphabetical values or numeric values.  You can also get fancy and sort by things such as cell color, font color or cell icon :-)  The sort functions can be found on both the Home and Data tabs.

 Home Tab:

Data Tab:
 

On each of these tabs, Excel gives 3 different options (icons) to use for sorting
1. Sort Lowest to Highest.  Excel will automatically sort the data using the column your cell is in as the column to sort by.  It will automatically determine the data set to be used.
 
2. Sort Highest to Lowest.  Excel will automatically sort the data using the column your cell is in as the column to sort by.  It will automatically determine the data set to be used.
 
3. Custom Sort. When you click on this icon, a window will pop up to let you customize what and how the data is to be sorted.  Using this option will let you sort by more than one column which can be very beneficial.

Example 1:
- In this scenario, I want to sort the data by the Contribution Amount and I want the values sorted lowest to highest.
- Click on any cell in column F, within the data set.  I chose to click on cell F3.

- Click on the Data tab
- Click on Sort Smallest to Largest (Lowest to Highest) icon.
- Excel automatically assumes that I want to sort the data in cells A1 through G10 and uses column F as the column to sort by from the smallest value on top to the highest value on the bottom.
- The data is now sorted based on column F
 

Example 2:
- In this scenario, I want to sort the data by Group and I want the values sorted highest to lowest as I want to see the Standard group first.
 
- Click on any cell in column G, within the data set.  I chose to click on cell G6.
- Click on the Data tab
- Click on Sort Z to A (Highest to Lowest) icon.
=
- Excel automatically assumes that I want to sort the data in cells A1 through G10 and uses column G as the column to sort by from the highest value on top to the lowest value on the bottom.
- The data is now sorted based on column G
 

Example 3:
- In this scenario, I want to sort the data by Name and I want the values sorted lowest to highest.  In order to do this I will need to sort on both the Last Name and First Name columns.

- Highlight cells A1 through G10

- Click on the Data tab
- Click on the Sort icon.
 
- A new window will pop up to allow customization of sorting

1. In the drop down under Column, choose Last Name
2. In the drop down under Sort On, choose Values
3. In the drop down under Order, choose A to Z
4. Click on Add Level to add another column to sort by
5. In the drop down under Column, choose First Name
6. In the drop down under Sort On, choose Values
7. In the drop down under Order, choose A to Z
8. Click OK

 

- The data is now sorted by Last Name and then by First Name
  

 Excel ya later!

No comments:

Post a Comment