Data Analysis - Sort
Course description
This course dives deep into array formulas in Excel, enabling complex
calculations and data analysis. Learn to create and manipulate arrays, and
use array functions to process large volumes of data efficiently.
Data Analysis - Sort
Part 1 - Sort1:49 min
"When our table gets large, it might be essential to sort our data by a
particular column, such as name, ID, price, score or similar. This session
looks at how to sort data based on one or multiple criteria. After completing
this session, you will be able to quickly sort data based on a single column or
multiple columns. Learning objectives; • Sort text data from A to Z • Sort
numeric data in ascending and descending order • Create an additional
sorting level to sort data based on multiple columns"
How do you sort a list of suppliers alphabetically by name from A to Z in Excel?
a. Select the cell of the first supplier, go to the Data tab, and click the Z to
A command
b. Select the cell of the first supplier, go to the Data tab, and click the A to
Z command
c. Select the cell of the first supplier, go to the Home tab, and click the Sort
command
How can you sort suppliers by cost in descending order?
a. Select the cell of the first supplier, go to the Data tab, and click the A to
Z command
b. Select the cell of the first supplier, go to the Data tab, and click the Z to
A command
c. Select the cell of the first supplier, go to the Home tab, and click Sort
Descending
How do you perform a multi-level sort, first by location and then by name within each location?
a. Select the cell of the first supplier, go to the Data tab, click Sort, specify
the first sort level by location, add a level, specify the second sort level
by name, and click OK
b. Select the cell of the first supplier, go to the Home tab, click Sort,
specify the first sort level by name, add a level, specify the second sort
level by location, and click OK
c. Select the cell of the first supplier, go to the Data tab, click Sort, specify
the first sort level by name, add a level, specify the second sort level by
location, and click OK
Part 2 - Sort by Colour1:20 min
"We often use different colours for different rows in our tables to visually
convey information about various records. This session looks at how to sort
data based on cell colours. After completing this session, you will be able to
sort data by cell colours using single or multiple criteria. Learning objectives; •
Sort data by a single colour so that all rows of a particular colour appear on
top • Add another sorting level • Sort data by multiple colours"
How do you start sorting a dataset by cell color in Excel?
a. Select any cell within the dataset, open the Home tab, and click the Sort
command
b. Select any cell within the dataset, open the Data tab, and click the Sort
command
c. Select any cell within the dataset, open the View tab, and click the Sort
command
After selecting 'Sort on Cell Color,' how do you specify the order of colors?
a. By choosing 'Sort by Font Color'
b. By specifying the first color to be sorted at the top in the dropdown
menu
c. By clicking 'Sort by Conditional Formatting'
How do you add another level to sort additional colors after the first one?
a. Click the Home tab and use the Conditional Formatting command
b. Click the Sort command again and add another level, specifying the next
color to be sorted
c. Right-click a cell and choose 'Add Sort Level'
Part 3 - Custom Sort Order1:17 min
"Sometimes the default sort order offered in Excel does not match our
requirements. In such cases, we may need to create a custom order to sort
our data. This session looks at how to create a custom sort order. After
completing this session, you will be able to sort data based on a custom sort
order. Learning objectives • Sort data using a default order • Set up a custom
sort order • Sort data based on a custom sort order"
What is the first step to sort data in a custom order in Excel?
a. Select the column you want to sort and click Conditional Formatting
b. Select the column you want to sort and click Sort on the Data tab
c. Select the column you want to sort and click Filter on the Home tab
How do you create a custom list for sorting in Excel?
a. Type the desired order directly into the cells
b. Use the Filter command and specify the order manually
c. In the Sort dialog box, select the column, then click on the Custom List
option to enter the desired order
What should you do after typing the custom order list in the Custom Lists dialog box?
a. Click Apply and then Close
b. Click OK to save the custom list and then OK again to sort the data
c. Click Filter and then Sort
Part 4 - Reverse List 1:02 min
"A common task in Excel is to reverse a list. However, there is no simple
function to reverse a list quickly. This session covers how to use Excel’s
sorting feature to reverse a list. After completing this session, you will be able
to set up the sort feature to reverse a list in Excel. Learning objectives •
Enumerate a list using the auto-fill feature • Apply the sort in reverse order
command to the numbers • Expand the selection to reverse the original list"
Why won’t the standard A to Z sorting order work for sorting grades like A+, A, A-, B+, etc.?
a. The standard A to Z sorting order doesn't recognize special characters
like '+' and '-'
b. The standard A to Z sorting order treats all grades with 'A' at the
beginning as the same
c. The standard A to Z sorting order sorts grades in alphabetical order, not
the logical order of grades
What do you need to do to create a custom order for sorting grades in Excel?
a. Use the Filter command to specify the order manually
b. Open the Data tab, click Sort, select the Grade column, and create a
custom list with the desired order
c. Type the desired order directly into the cells
After typing the custom order list in the Custom Lists dialog box, what is the final step to sort the
data in the desired order?
a. Click Apply and then Close
b. Click OK to save the custom list and then OK again to sort the data
c. Click Filter and then Sort
Part 5 - Randomise List1:16 min
"Sometimes we may need to randomise, or in other words, shuffle a list in
Excel. There is no function to randomise a list. This session looks at how to
use Excel’s sorting feature to randomise a list. After completing this session,
you will be able to set up the sort feature to randomise a list in Excel. Learning
objectives • Link a random value to each record on the list • Apply the sort
command to the random numbers • Expand the selection to shuffle the
original list"
Which function is used to generate random values between zero and 1 in Excel?
a. RAND
b. RANDOM
c. RND
After generating random values and copying them to other cells, what is the next step to
randomize the list?
a. Use the Filter command
b. Use the Sort command
c. Use the Data Validation command
How can you prevent the random values from changing every time the worksheet is calculated?
a. Copy the random values and paste them as values
b. Lock the cells containing the random values
c. Save the workbook
Part 6 - SORT function2:20 min
"It might be useful to sort our data using a function instead of relying on
Excel’s sort and filter features. This session covers how to use the SORT
function to sort data. After completing this session, you will be able to sort
data by a specific column, choose the sort order, and set up multi-level sort
rules using the SORT function. Learning objectives • Apply the SORT function
to a dataset to sort data by the first column in ascending order • Change the
sort column and sort column • Set up multi-level sort rules by introducing an
array constant to the SORT function"
What is the default sort order when using the Sort function in Excel?
a. Descending
b. Ascending
c. Custom
Which argument specifies the column by which you want to sort your data when using the Sort
function?
a. First argument
b. Second argument
c. Third argument
How do you specify a descending sort order in the Sort function?
a. Enter the value 1
b. Enter the value 0
c. Enter the value -1