Excel Data Analysis
Part 1
Lecture 4 1
Data Sorting
Ranges can be sorted using the Sort Ascending and Sort Descending commands
Sort Ascending: from smallest to largest
Sort Descending: from largest to smallest
The sort commands work for text too
2
Data Sorting
The commands are found in the Ribbon under the Sort & Filter menu
3
Sort (numbers)
Select the range, i.e., A2:A21
4
Sort (Text)
Select the range
5
Sort (Text): Note
A:23, Text, is not included as it is the header for the column
This is the row that is dedicated to the filter
Including it will blend it with the rest
6
Sort (Text): Note
A:23, Text, is not included as it is the header for the column
This is the row that is dedicated to the filter
Including it will blend it with the rest
7
A Non-Working Example
Sorting one column in a range
In this example, we have two columns with related data
8
A Non-Working Example
The attempt to sort results in a warning
It is not recommended to sort the names alone because it will break the relationship between
the columns
9
A Non-Working Example
10
Sorting More Than One Column
Select the whole range when sorting ranges with more than one column
Note: When sorting multiple columns, it will always sort by the first column (leftmost)
11
Sorting More Than One Column
12
Data Filtering
13
Data Filtering
Filters can be applied to sort and hide data
It makes data analysis easier
Note: Filter is similar to formatting a table, but it can be applied and deactivated
14
Data Filtering
The menu is accessed in the default Ribbon view or in the Data section in the navigation bar
15
Applying Filter
Filters are applied by selecting a range and clicking the Filter command
It is important to have a row of headers when applying filters
Filters are applied to the top row in a range
The filter keeps the relationship intact between the columns while sorting and filtering
16
Applying Filter
17
Filter options
"Items" are the different categories in that column
All items are checked by default
The checked items are the ones that are shown
Uncheck to hide
Note: The unchecked rows are hidden, not deleted!
18
Advanced Filtering
"Items" are the different categories in that column
All items are checked by default
The checked items are the ones that are shown
Uncheck to hide
Note: The unchecked rows are hidden, not deleted!
19
Filter Function
Purpose
Filters range with given criteria
Return value
An array of filtered values (without heading)
Syntax
=FILTER(array, include, [if_empty])
20
Filter Function
Arguments
array - Range or array to filter
include - Boolean array, supplied as criteria
if_empty - [optional] Value to return when no results are returned
21
Outline (group-Ungroup) data
22
Outline data
If you have a list of data you want to group and summarize, you can create an outline of up to
eight levels
Each inner level, represented by a higher number in the outline symbols
It displays detailed data for the preceding outer level, represented by a lower number in the
outline symbols
23
Outline data
Use an outline to quickly display
summary rows or columns
Or to reveal the detailed data for each
group
You can create an outline of rows, an
outline of columns, or an outline of both
rows and columns
24
How to group?
Make sure that each column (or row) of the data that you want to outline has a label in the
first row (or column), contains similar facts in each column (or row), and that the range has no
blank rows or columns
Select the data (including any summary rows or columns)
On the Data tab, in the Outline group, click Group > Group Rows or Group Columns
Continue selecting and grouping inner rows or columns until you have created all of the levels
that you want in the outline
25
Ungroup rows or columns
To ungroup, select the rows or columns, and then on the Data tab, in
the Outline group, click Ungroup and select Ungroup Rows or Ungroup Columns
26
Subtotals
Subtotals are calculated with a summary function, such as Sum or Average, by using the
SUBTOTAL function
You can display more than one type of summary function for each column
27
Grand totals
Grand totals are derived from detailed data, not from the values in the subtotals
For example, if you use the Average summary function, the grand total row displays an average
of all of the detail rows in the list, not an average of the values in the subtotal rows
28
Note
If the workbook is set to automatically calculate formulas, the Subtotal command recalculates
subtotal and grand total values automatically as you edit the detailed data
The Subtotal command also outlines the list so that you can display and hide the detail rows
for each subtotal
29
Insert Subtotals
Make sure that each column in a range of data for which you want to calculate subtotals has a
label in the first row, contains similar facts in each column, and that the range does not include
any blank rows or columns
Select a cell in the range
Insert one level of subtotals
30
Insert Subtotals
To sort the column that contains the data you want to group by, select that column, and then
on the Data tab, in the Sort & Filter group, click Sort A to Z or Sort Z to A
On the Data tab, in the Outline group, click Subtotal
31
Insert Subtotals
Make sure that each column in a range of data for which you want to calculate subtotals has a
label in the first row, contains similar facts in each column, and that the range does not include
any blank rows or columns
Select a cell in the range
Insert one level of subtotals
32
Conditional Formatting
33
Conditional Formatting
Conditional formatting is used to change the appearance of
cells in a range based on your specified conditions
The conditions are rules based on specified numerical
values or matching text
Changing the appearance of cells can visually highlight
interesting data points for analysis
34
Conditional Formatting
Note: The web browser version of Excel only offers a
selection of built-in conditional formatting options
The Excel application has the option of creating fully
customized conditional formatting rules
35
Colour Scale Formatting
Highlight with Colour Scales
Click on the "Green - Yellow - Red Colour Scale" icon
Dark green is used for the highest values and dark red for
the lowest values
36
Highlight Cell Rules
Highlight Cell Rules are the predefined conditional
formats to change the appearance of cells
The Rules are based on specified numerical values,
matching text, calendar dates, duplicated and unique
values etc
37
Appearance Options
Light Red Fill with Dark Red Text
Yellow Fill with Dark Yellow Text
Green Fill with Dark Green Text
Light Red Fill
Red Text
Red Border
38
Cell Rule Types
Greater Than A Date Occurring
Less Than Duplicate/Unique Values
Between
Equal To
Text That Contains
39
Text to Columns
40
Text to Columns
Select the cell or column that contains the text you want to split
Select Data > Text to Columns
In the Convert Text to Columns Wizard, select Delimited > Next
41
Text to Columns
Select the Delimiters for your data.; For example, Commas and Space
You can see a preview of your data in the Data preview window
Select Next
Select the Destination in your worksheet which is where you want the
split data to appear
Select Finish
42
What-if Analysis
43
What-if Analysis
It is a very powerful tool to perform complex mathematical calculations
This feature can help you experiment and answer questions with your data
Even when the data is incomplete
44
Goal Seek
Whenever you create a formula or function in Excel, you put various parts
together to calculate a result
Goal Seek works in the opposite way!
It lets you start with the desired result, and it calculates the input value that will
give you that result
45
Scenarios
Scenarios let you substitute values for multiple cells (up to 32) at the same time
You can create as many scenarios as you want and then compare them without
changing the values manually
46
Data tables
Data tables allow you to take one or two variables in a formula and replace them
with as many different values as you want
Then view the results in a table
This option is especially powerful because it shows multiple results at the same
time, unlike scenarios or Goal Seek
47
