Sort data in spreadsheets

Sort sheet

• All of the data in a spreadsheet is sorted by the ranking of a specific sorted column - data across rows is kept together

Sort range

• Nothing else on the spreadsheet is rearranged besides the specified cells in a column

Customized sort order

• When you sort data in a spreadsheet using multiple conditions

Question

Which of the following statements accurately describe differences between the sort from a spreadsheet's Data tab and a written SORT function? Select all that apply.

• The sort from a spreadsheet's Data tab overwrites the cells containing the unsorted data with the sorted data, while a written SORT function inserts the sorted data in a different cell range.
• The sort from a spreadsheet's Data tab can sort in ascending or descending order, while a written SORT function automatically assumes ascending order.
• The sort from a spreadsheet's Data tab can exclude a header row in the data range from being sorted, while the data range for a written SORT function should never contain a header row.
• The sort from a spreadsheet's Data tab can only sort data by a single sort condition or column, while a written SORT function can sort data by multiple columns.

Correct. The sort from a spreadsheet's Data tab can exclude a header row in the data range from being sorted, while the data range for a written SORT function should never contain a header row.

Sorting and filtering in Sheets and Excel

In this reading, we will describe the sorting and filtering options in Google Sheets and Microsoft Excel. Both offer basic sorting and filtering from set menu options. But, if you need more advanced sorting and filtering capabilities, you can use their respective SORT and FILTER functions.

Sorting and filtering in Sheets

Sorting in Google Sheets helps you quickly spot trends in numbers. One trend might be gross revenue by sales region. In this case, you could sort the gross revenue column in descending (Z to A) order to spot the top performing regions at the top, or sort the gross revenue column in ascending (A-Z) order to spot the lowest performing regions at the top. Although an alphabetical order is implied, these sorting options do sort numbers, as our gross revenue example highlighted.

If you want to learn more about the set menu options for sorting and filtering, start with these resources:

• Sort and filter data (Google Help Center): instructions to sort data in alphabetical or numerical order and create filter views
• Sort data by selecting a range of data in a column: video of steps to achieve the task
• Sort a range of data using sort criteria for multiple columns: technical tip video to sort data across multiple columns

In addition to the standard menu options, there is a **SORT** function for more advanced sorting. Use this function to create a custom sort. You can sort the rows of a given range of data by the values in one or more columns. And you get to set the sort criteria per column. Refer to the **SORT** function page for the syntax.

And like the **SORT** function, you can use the FILTER function to filter by any matching criteria you like. This creates a custom filter.

You might recall that you can filter data and then sort the filtered results. Using the **FILTER** and **SORT** functions together in a range of cells can programmatically and automatically achieve these results for you.

Sorting and filtering in Excel

You can also sort in ascending (A-Z) and descending (Z-A) order in Microsoft Excel. Excel offers Smallest to Largest and Largest to Smallest sorting when you are working with numbers.

Similar to the SORT function in Google Sheets, Excel includes custom sort capabilities that are available from the menu. After you select the data range, click the Sort & Filter button to select the criteria for sorting. You can even sort by the data in rows instead of by the data in columns if you select Sort left to right under Options. (Sort top to bottom is the default setting to sort the data in columns.)

If you want to learn more about sorting and filtering in Excel, start with these resources:

• Sort data in a range or table (Microsoft Support): instructions and video to perform sorting in 11 different use cases
• Excel training: sort and filter data (Microsoft Support): sorting and filtering videos with transcripts
• Excel: sorting data: video of how to use the Sort & Filter and Data menu options for sorting

Excel also has SORT, SORTBY, and FILTER functions. Explore how you can use these functions to automatically sort and filter your data in spreadsheets without having to select any menu options at all.

---

Test your knowledge on sorting in spreadsheets

TOTAL POINTS 3

Question 1

Which spreadsheet menu function is used to sort all data in a spreadsheet by the ranking of a specific sorted column?

• Sort Range
• Sort Sheet
• Sort By Rank
• Sort Data

Correct. Sort Sheet is used to sort all data in a spreadsheet by the ranking of a specific sorted column.

Question 2

In spreadsheets, data analysts can sort a range from the Data tab in the menu or by typing a function directly into an empty cell.

• True
• False

Correct. Sorting a range and sorting a sheet can both be done from the menu and written as a function. Analysts can work from the Data tab in the menu or type a function directly into an empty cell.

Question 3

An analyst uses **=SORT** to sort spreadsheet data in descending order. What do they type at the end of their sort function?

• Z-A
• DESCEND
• FALSE
• TRUE

Correct. To sort a spreadsheet in descending order using the SORT function, the analyst types FALSE at the end of their sort function.

---

Sort data using SQL

Test your knowledge on sorting in SQL

TOTAL POINTS 3

Question 1

A data analyst wants to sort a list of greenhouse shrubs by price from least expensive to most expensive. Which statement should they use?

• WHERE shrub_price
• WHERE shrub_price ASC
• ORDER BY shrub_price DESC
• ORDER BY shrub_price

Correct. To sort a list of greenhouse shrubs by price from least expensive to most expensive, they should use ORDER BY shrub_price.

Question 2

You are working with a database table that contains data about music genres. You want to sort the genres by name in ascending order. The genres are listed in the genre_name column.

You write the SQL query below. Add an ORDER BY clause that will sort the genres by name in ascending order.

SELECT
	*
FROM
	genre
ORDER BY 
	genre_name

+----------+--------------------+
| genre_id | genre_name         |
+----------+--------------------+
|       23 | Alternative        |
|        4 | Alternative & Punk |
|        6 | Blues              |
|       11 | Bossa Nova         |
|       24 | Classical          |
|       22 | Comedy             |
|       21 | Drama              |
|       12 | Easy Listening     |
|       15 | Electronica/Dance  |
|       13 | Heavy Metal        |
|       17 | Hip Hop/Rap        |
|        2 | Jazz               |
|        7 | Latin              |
|        3 | Metal              |
|       25 | Opera              |
|        9 | Pop                |
|       14 | R&B/Soul           |
|        8 | Reggae             |
|        1 | Rock               |
|        5 | Rock And Roll      |
|       20 | Sci Fi & Fantasy   |
|       18 | Science Fiction    |
|       10 | Soundtrack         |
|       19 | TV Shows           |
|       16 | World              |
+----------+--------------------+

• Alternative
• Classical
• Blues
• Easy Listening

Correct. The clause ORDER BY genre_name will sort the genres by name in ascending order. The complete query is SELECT * FROM genre ORDER BY genre_name*. The* ORDER BY clause tells the database how to organize the data it returns. The ORDER BY clause sorts data in ascending order by default. The Blues genre appears in row 3 of your query result.

Question 3

You are working with a database table that contains employee data. You want to sort the employees by hire date in descending order. The hire dates are listed in the hire_date column.

You write the SQL query below. Add an ORDER BY clause that will sort the employees by hire date in descending order.

SELECT
	*
FROM
	employee
ORDER BY 
	hire_date DESC

+-------------+-----------+------------+---------------------+------------+---------------------+---------------------+-----------------------------+------------+-------+---------+-------------+-------------------+-------------------+--------------------------+
| employee_id | last_name | first_name | title               | reports_to | birth_date          | hire_date           | address                     | city       | state | country | postal_code | phone             | fax               | email                    |
+-------------+-----------+------------+---------------------+------------+---------------------+---------------------+-----------------------------+------------+-------+---------+-------------+-------------------+-------------------+--------------------------+
|           8 | Callahan  | Laura      | IT Staff            |          6 | 1968-01-09 00:00:00 | 2004-03-04 00:00:00 | 923 7 ST NW                 | Lethbridge | AB    | Canada  | T1H 1Y8     | +1 (403) 467-3351 | +1 (403) 467-8772 | [email protected]    |
|           7 | King      | Robert     | IT Staff            |          6 | 1970-05-29 00:00:00 | 2004-01-02 00:00:00 | 590 Columbia Boulevard West | Lethbridge | AB    | Canada  | T1K 5N8     | +1 (403) 456-9986 | +1 (403) 456-8485 | [email protected]   |
|           5 | Johnson   | Steve      | Sales Support Agent |          2 | 1965-03-03 00:00:00 | 2003-10-17 00:00:00 | 7727B 41 Ave                | Calgary    | AB    | Canada  | T3B 1Y7     | 1 (780) 836-9987  | 1 (780) 836-9543  | [email protected]    |
|           6 | Mitchell  | Michael    | IT Manager          |          1 | 1973-07-01 00:00:00 | 2003-10-17 00:00:00 | 5827 Bowness Road NW        | Calgary    | AB    | Canada  | T3B 0C5     | +1 (403) 246-9887 | +1 (403) 246-9899 | [email protected]  |
|           4 | Park      | Margaret   | Sales Support Agent |          2 | 1947-09-19 00:00:00 | 2003-05-03 00:00:00 | 683 10 Street SW            | Calgary    | AB    | Canada  | T2P 5G3     | +1 (403) 263-4423 | +1 (403) 263-4289 | [email protected] |
|           1 | Adams     | Andrew     | General Manager     |       None | 1962-02-18 00:00:00 | 2002-08-14 00:00:00 | 11120 Jasper Ave NW         | Edmonton   | AB    | Canada  | T5K 2N1     | +1 (780) 428-9482 | +1 (780) 428-3457 | [email protected]   |
|           2 | Edwards   | Nancy      | Sales Manager       |          1 | 1958-12-08 00:00:00 | 2002-05-01 00:00:00 | 825 8 Ave SW                | Calgary    | AB    | Canada  | T2P 2T3     | +1 (403) 262-3443 | +1 (403) 262-3322 | [email protected]    |
|           3 | Peacock   | Jane       | Sales Support Agent |          2 | 1973-08-29 00:00:00 | 2002-04-01 00:00:00 | 1111 6 Ave SW               | Calgary    | AB    | Canada  | T2P 5M5     | +1 (403) 262-3443 | +1 (403) 262-6712 | [email protected]     |
+-------------+-----------+------------+---------------------+------------+---------------------+---------------------+-----------------------------+------------+-------+---------+-------------+-------------------+-------------------+--------------------------+

• Margaret Park
• Nancy Edwards
• Laura Callahan
• Robert King

Correct. The clause ORDER BY hire_date DESC will sort the employees by hire date in descending order. The complete query is SELECT * FROM employee ORDER BY hire_date DESC*. The* ORDER BY clause tells the database how to organize the data it returns. The ORDER BY clause sorts data in ascending order by default. The DESC command is used to sort data in descending order. The employee Laura Callahan appears in row 1 of your query result.