Lesson Notes By Weeks and Term v5 - Grade 12

Lesson Video

This page supports the lesson note with a companion video and a short classroom-ready summary.

For class groups and homework, share this lesson page so learners also get the summary, objectives, and full lesson context.

Performance objectives

• Create and use PivotTables to summarize large datasets.
• Apply advanced filtering and sorting techniques.
• Implement lookup functions like VLOOKUP.
• Create charts and graphs to represent data.
• Apply data validation to ensure accuracy.

Lesson summary

This week, we delve into advanced spreadsheet functionalities and data analysis, building upon your existing knowledge of basic spreadsheets. Mastering these skills is crucial in today's data-driven world, and especially relevant in South Africa where effective data analysis can contribute to informed decision-making in various sectors, from business and finance to healthcare and education. For example, analysing crime statistics using spreadsheets can help allocate police resources more effectively, or tracking agricultural yields can inform farmers about optimal planting strategies.

Lesson notes

2. 1.

PivotTables: Summarizing and Analyzing Data PivotTables are powerful tools for summarizing and analyzing large datasets. They allow you to quickly rearrange and aggregate data to extract meaningful insights. What is a PivotTable? A PivotTable is an interactive table that summarizes data from a larger dataset. It allows you to quickly group, filter, and calculate summary statistics (like sum, average, count, etc.) without altering the original data.

Creating a PivotTable: Select your data range (including column headers). Then, go to the "Insert" tab and click "PivotTable." Choose where you want to place the PivotTable (new or existing worksheet).

PivotTable Fields: The PivotTable Fields pane appears. Drag and drop fields to the following areas: Rows: Fields that will appear as row labels in the PivotTable.

Columns: Fields that will appear as column labels in the PivotTable.

Values: Fields that will be aggregated (summed, averaged, etc.) in the PivotTable.

Filters: Fields that you can use to filter the data displayed in the PivotTable.

Grouping Data: You can group data within PivotTables based on numerical ranges or date values. Right-click on a data point in the PivotTable and select "Group." Specify the starting value, ending value, and interval for numerical data, or the grouping increment (e.g., days, months, years) for date data.

Example: Imagine a spreadsheet tracking student performance in different subjects across various schools in Gauteng. Using a PivotTable, you could quickly determine the average Maths score per school or the number of students achieving above 70% in English in each district. 2.

2. Advanced Filtering and Sorting Advanced Filtering: Beyond basic filtering (e.g., selecting all students with a score above 80), advanced filtering allows for more complex criteria using logical operators (AND, OR) and wildcards. For instance, filtering for students who scored above 75 in Maths AND below 60 in English. You can access advanced filtering through the "Data" tab and clicking "Advanced" in the "Sort & Filter" group. Specify your criteria range (containing the column headers and your filter conditions).

Custom Filters: Use custom filters to set more specific criteria. For example, you can filter for dates between two specific dates, or for text that contains a certain word or phrase.

Sorting with Multiple Criteria: You can sort data based on multiple columns. For instance, sort students first by grade (ascending) and then by surname (alphabetical). This is done by selecting the data, clicking "Sort" on the "Data" tab, and adding multiple sorting levels. 2.

3. Lookup Functions: `VLOOKUP`, `HLOOKUP`, `INDEX`, and `MATCH` These functions are used to retrieve data from a table based on a specific lookup value. They are extremely useful for linking data across different worksheets or workbooks. `VLOOKUP` (Vertical Lookup): Searches for a value in the first column of a table and returns a value from the same row in a specified column. `=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])` `lookup_value`: The value you are searching for. `table_array`: The range of cells containing the lookup table. The first column of this table must contain the `lookup_value`. `col_index_num`: The column number (starting from 1) in the `table_array` from which to return a value. `[range_lookup]`: `TRUE` (approximate match) or `FALSE` (exact match). Generally, you want `FALSE` for exact matches.

Example: Imagine you have a table with student IDs and their corresponding names. `VLOOKUP` can be used to retrieve a student's name based on their ID. `HLOOKUP` (Horizontal Lookup): Searches for a value in the first row of a table and returns a value from the same column in a specified row. `=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])` `lookup_value`: The value you are searching for. `table_array`: The range of cells containing the lookup table. The first row of this table must contain the `lookup_value`. `row_index_num`: The row number (starting from 1) in the `table_array` from which to return a value. `[range_lookup]`: `TRUE` (approximate match) or `FALSE` (exact match).

Example: Imagine you have a table with product codes in the first row and their prices in the second row. `HLOOKUP` can be used to retrieve a product's price based on its code. `INDEX`: Returns a value from a specified row and column within a range. `=INDEX(array, row_num, [column_num])` `array`: The range of cells to search in. `row_num`: The row number from which to return a value. `[column_num]`: (Optional) The column number from which to return a value. If omitted, the function returns the entire row.

Example: `INDEX(A1:C10, 5, 2)` returns the value in the 5th row and 2nd column of the range A1:C10. `MATCH`: Returns the relative position of an item in a range that matches a specified value. `=MATCH(lookup_value, lookup_array, [match_type])` `lookup_value`: The value you are searching for.