TOPIC 6 – ANALYSING DATA WITH TABLES
TRANSFORMING RANGE OF DATA INTO TABLE
Task 1: To transform the range of cells in an Excel worksheet into a Table.
https://tinyurl.com/excel-tissa-2019
SORTING A TABLE
Basic Sort
Task 2: Sort the order data by (i) OrderDate (Most recent orders first) and (ii) Amount (highest
amount on the top).
Performing More Complex Sort
Task 3: To sort the data table by Region (Ascending order), SalesRepresentative (Ascending
order) and Amount (Largest to Smallest).
FILTERING DATA TABLE
Single Column Filter
Task 4: To filter the order records prepared by Lim Chi Chi (Sales representative).
Multiple Columns Filter
Task 5: Filter the order records that refer to (i) Central region and (ii) assigned to Musalmah
(sales representative).
Quick Filter Option
Task 6: Filter the order records for orders took place between June 1, 2017, to December 31,
2017 (both date inclusive).
Task 7: Filter the order records for orders took place between June 1, 2017, to December 31,
2017 (both date inclusive) with the amount is between RM500 to RM1,000 (both inclusive).
Using Complex Criteria for Filtering Table
Task 8: Produce a list of order records for 2017 onwards that are greater than RM3,000.
Using Compound Criteria
Task 9: Filter the order records from Central region that has an amount of less than RM200 OR
orders from the East region with the amount of greater than RM3,000.
Copying Filtered Data to a Different Range
Task 10: To filter order records in 2017 with a minimum amount of RM2,000. Extract the
filtered records into a new worksheet and sort the records by OrderDate.
REFERENCING TABLE IN FORMULAS
Using Table Specifier
Purpose Specifier
To refer to the entire table (including the column header and #All
total row)
To limit the reference only to the data (excluding the column #Data
header and total row)
To refer to the table’s column header #Headers
To refer to the table’s total row #Total
To refer to the table row in which the formula appears @
To refer data on a specific column header [Column Header Name]
Task 11: To compute the sales price of each item, assuming the profit margin imposed is 25% of
the unit price.
EXCEL TABLE FUNCTIONS
Functions without Criteria Range
Function Syntax Description of Purpose
COUNTIF ( ) COUNTIF (range, criteria) To count the number of cells within
given ranges that meet specified
criteria
SUMIF ( ) SUMIF (ranges, criteria, sum To sum/total up to ranges of
range) specified cells that meet the specified
criteria
AVERAGEIF ( ) AVERAGEIF (range, criteria, To calculate the average value of the
average range) data in a given range of cells based on
the given criteria specified.
COUNTIFS ( ) COUNTIFS (range 1, criteria 1, Similar to the COUNTIF function.
range 2, criteria 2…. ) However, it allows the user to specify
multiple criteria/conditions
SUMIFS ( ) COUNTIFS (sum range, range Similar to SUM function with more
1, criteria 1, range 2, criteria than one criterion being specified.
2…. )
AVERAGEIFS ( ) AVERAGEIFS (average_range, Works exactly as AVERAGEIF
range 1, criteria 1, range 2, function but with multiple criteria.
criteria 2)
Task 12: Compute selected quick facts related to order data by applying various Excel functions
without criteria range.
Functions Require Criteria Range
Function Description of Purpose
DCOUNT Count number of records that matched the criteria specified.
DCOUNTA Count number of records that matched the criteria specified that are not
blank.
Usually will be for non-numeric field.
DAVERAGE Produce an average value of the specified field that matched the criteria
specified.
DGET Produce a value of the specified field for a single matching record.
DMAX Produce the highest value of the specified field that matched the criteria
specified.
DMIN Produce the lowest value of the specified field that matched the criteria
specified.
DSUM Produce a sum of the value of the specified field for a matching record
DPRODUCT Produce the product (multiplication) of the values of the specified field
that matched the criteria specified.
DSTDEV Produce the estimated standard deviation value of the specified field if
the matched records are a sample of the population.
Task 13: Produce results for each of the requirements given in the following table (Table 4-7)
using appropriate Excel’s functions (with criteria range).
Requirement Formula Output
a. Count number of
transactions amount that
are greater than 2,500.
b. Count number of
transactions in East
region
c. Produce average Ordered
Amount for Blouse (item)
d. Amount of order for Dress
(item) by Kadayya (Sales
representative) and
located in West (region)
e. The highest number of
Units ordered for Kurung
(item) in 2017.
f. The lowest Units Ordered
for Pant (item) in 2016.
g. Total ordered Amount for
Central region in 2018
h. The product of all Units
sold by Qalif in Central
region.
Average Units of order
located in the West
region.
f. Estimated Standard
Deviation (SD) for Units of
order located in the West
region.
