New Excel Curriculum

This curriculum is designed to be customized to the needs of students. What is taught is completely up to the teacher past Day 1. Possible lesson plans are available for days 2-4, but reliance on these is not required nor recommended.

Following are possible topics and outlines that can be chosen and adapted as the teacher sees fit to create a lesson plans that will best help their students.

Please feel free to give feedback on sections or files that need improving, potential subjects, timing corrections, or any other aspect of this page. Feedback can be sent to aaronpage124@gmail.com.

How to use this curriculum

This curriculum is designed to be more flexible than the old curriculum, allowing teachers to teach more to the needs and speeds of different classes. After teaching Day 1 and the survey is completed, teachers can assess the ability and interest of the students in different topics. See the “Using the Survey” section for more guidance in retrieving and analyzing the data. At this point the teacher should create a rough plan for the next 3 days of class, containing the major subjects that should be covered. A specific plan for the next day based on the students’ interests and needs. This plan should follow traditional format, with objectives, outlines, questions, and a conclusion.

Lessons can be created with as much content as the teacher desires. It is a good idea to plan extra content to teach, but also to be prepared to not teach everything planned to remain flexible to class learning speeds. Most of the beginning topics can be taught together in a single day, but later topics will require more time to teach and should be planed carefully.

Prerequisites

Prerequisites are given for more advanced topics to ensure students have the necessary building blocks for each subject. It is not necessary to fully teach each prerequisite beforehand if students already have an understanding of the subjects, though a quick review is often recommended.

Files

Files used for each skill are linked in each section. Some files are used in more than one skill, and often files are a continuation of a previous file. Make sure to check files for differences before teaching to verify if a re-download will be required. Multiple files are given for some topics, which can be used as in-class activities.

Using the Survey

To get results from the survey, log in to the survey as byummlab@gmail.com, then click on results, or find the Excel Topics Survey (Responses) spreadsheet in the Google Drive. Analysis can be seen on the second worksheet in the same workbook.

Here is the link to the survey:

tiny.cc/ExcelSurvey

If that link ever breaks for some reason, here is the full link:

docs.google.com/forms/d/e/1FAIpQLScErirppVOsAEzlga_hcsRTmJMOMtqho9CTZdy0scjKI31ZGw/viewform

Day 1 survey

tiny.cc/ExcelSurvey

Survey Results

Day 1 lesson outline

Possible Day 2 lesson topics

Import and Format Data

Data Cleaning

Number Formats

Conditional Formatting

Function/Formula Syntax

Entering Functions

Named Ranges

Possible Day 3 lesson topics

IF Functions

SUMIF/AVERAGEIF/COUNTIF Functions

Named Ranged

Data Validation

Boolean Logic

VLOOKUP Function

Possible Day 4 lesson topics

Function Review (VLOOKUP)

INDEX/MATCH Functions

Additional Functions

Pivot Tables

Advanced Searching

Dynamic Named Ranges

Topics and Outlines

Beginning Topics

Import and Format Data

Files:
  1. 1. Importing Data.txt
Outline:
  • Explain different options in import wizard
  • Explain what delimited means
  • Import Data

Number Formats

Files:

2. Census Income Summary.xlsx

3. Sales Data 1.xlsx

Outline:
  • Format phone number, SSN, and sales numbers
  • Custom format the BYU ID to be the same format that is on the student ID card
  • Format numbers as text (Format menu or Apostrophe)

Function/Formula Syntax

Files:

No Files

Outline:
  • Difference between a function and a formula
  • Parts/sections of a function
  • =FUNCTIONNAME (Press tab to auto-start the function)

Data Cleaning

Files:

3. Sales Data 1.xlsx

Outline:

Conditional Formatting

Files:

4. Sales Data 2.xlsx

Outline:

Create and Manipulate Tables

Files:

4. Sales Data 2.xlsx

Outline:

Intermediate Topics

Entering Functions

Prerequisite:

Function/Formula Syntax

Files:

Any File Usable

Outline:
  • Formula bar
  • Straight into the cell
  • Formula tab, by category
  • Insert function button
  • Cover where to find more information about additional functions and how to use them

Can be taught as a part of the two sections below
IF Functions

Prerequisite:

Entering Functions

Files:

5. Gradebook IF.xlsx

Outline:
  • IF(logical test, if true, if false)
  • Basic if test scenarios for understanding
  • Nested IF functions
    • Follows a decision path

SUMIF/AVERAGEIF/COUNTIF Functions

Prerequisite:

IF Functions

Files:

6. Gradebook COUNTIF.xlsx (Continuation of 
5. Gradebook IF.xlsx)

Outline:
  • Syntax and usage
  • Using absolute reference to increase efficiency

Mac Shortcuts

This could be taught as its own section or as you teach the associated skills.

Navigation Shortcuts
  • Cmd + Arrow key
  • Enter/Shift + Enter – Changes selected cell vertically
  • Tab/Shift + Tab – Changes selected cell horizontally
  • Shift + Arrow key – Select cells
  • Shift + Space – Select entire row
  • Ctrl + Space – Select entire column
Other Shortcuts
  • Cmd + T – Insert Table
  • Cmd + D – Fill down
    • Cmd + ‘ – Lets you edit the copied formula
    • Cmd + Shift + ” – Copies down value
  • Cmd + R – Fill right
  • Cmd + Return (While editing a cell) – Fill Selection
  • Cmd + ‘-‘  – Delete Cells
  • Cmd + Shift + ‘+’  – Insert Cells
  • Cmd + 1 – Open format dialog

Named Ranges

Prerequisite:

Function/Formula Syntax

Files:

Same as search files

7. Timesheet COUNTIF.xlsx

Outline:
  • Use to replace ranges of cells in functions
  • Changing the function can be done by changing the range
  • Functions become easier to read and write

Data Validation

Files:

8. Person Data Search.xlsx

Outline:
  • Data tab>Data Validation
  • Different categories of validation, with List being most common
  • List allows for the selection of cells containing valid text – great for lookup functions

Recommended to teach along with a Search Function or with If Functions

Freezing and Printing

Files:

Any file usable
2. Census Income Summary.xlsx

Outline:
  • Viewing options
    • Freeze Panes
    • Split Screen
  • Printing
    • Changing Page size
    • Page Break Preview
    • Page Layout Settings (Orientation, Margins, etc.)

Boolean Logic

Files:

No files

Outline:
  • Conditions in Excel are based on booleans – true or false
  • Four logical modifier formulas in Excel:
    • AND(condition 1, cond 2)
    • OR(cond 1, cond 2)
    • XOR(cond 1, cond 2)
    • NOT(cond)
  • AND()
    • If both conditions are true, the formula will be true
  • OR()
    • If either or both conditions are true, the formula will be true
  • XOR() – stands for “exclusive or”
    • If one condition is true, the formula will be true
    • False if both conditions are true
  • NOT()
    • True if condition is False
    • False if condition is True

Recommended to teach along with If Functions

Advanced topics

VLOOKUP Function

Prerequisite:

Entering Functions

Recommended:

IF Functions

Data Validation

Files:

8. Person Data Search.xlsx

Outline:
  • Syntax and arguments
  • Use when you want to look up information without searching through your data
  • Using data validation in conjunction with vlookup is useful and powerful

INDEX/MATCH Functions

Prerequisite:

Entering Functions

Recommended:

VLOOKUP Function

Files:

8. Person Data Search.xlsx

Outline:
  •  Pros:
    • Faster
    • Can search in any column
    • Can search horizontally or vertically
  • Index: Returns a value of a cell reference defined by row/column numbers
  • Match: Looks up a value in a range and returns the relative position (row/column number)

Matching Chart Types to Data

Outline:
  • Scatter-plots are good for finding relationships between variables
  • Pi charts, bar graphs, column graphs are good for comparing data
  • Histograms are good for finding the distribution of data

Additional Functions

Prerequisites:

Entering Functions

INDEX/MATCH Functions

Files:

No files

Outline:

TODAY()

Booleans – AND(),NOT(),OR(),XOR()

HYPERLINK()

ISERROR()

RANDOM()

Pivot Tables

Prerequisites:

Create and Manipulate Tables

Files:
Outline:
  • Select Data, then go to Insert>Pivot Table
    • Fields can be added by checking the field name at the top
    • Fields are moved by dragging from one category to another
  •  Filters
    • Create menus that allow for the selection of the data for display
  • Columns
    • Will be set to Values if Values field is not empty.
    • Further manipulation is possible, but often not needed
  • Rows
    • Create categories and subcategories for the values data
    • Order is very important – determines the hierarchy of categories
    • Subtotal can be changed from displaying local sums by clicking the i logo on the right
  • Values
    • Set to display Sum of field by default
    • Can be changed from sum by clicking the i logo on the right
    • This menu allows for many different display options and statistics

Dynamic Named Ranges

Prerequisite:

Named Ranges
Function/Formula Syntax
Students need a good understanding of Functions and how they work

Files:

13 Dynamic Ranges.xlsx

Outline:
  • Open the Name Manager under Formulas
  • Create a new Named Range
  • In “Refers to”, instead of specifying a range we will insert a formula:
  •  =OFFSET(reference, rows, cols, [height], [width])
    • OFFSET creates a range based on it’s inputs 
    • Reference will be a single cell, the starting point for the range
    • rows and cols are if you want to offset your starting point from the Reference. In this application, both will typically be 1
    • height and width will be the height and width of the range you want to create
  • =COUNTA(value1, [value2], …)
    • COUNTA returns the number of non-blank ceslls in a range 
    • COUNTA will be used for the height and/or width in OFFSET
    • value1 – An item, cell reference, or range
  • Example function for dynamic height, width of 5:
     =OFFSET($A$1,1,1,COUNTA($A:$A),5)
  • Example function for height of 5, dynamic width:
    =OFFSET($A$1,1,1,5,COUNTA($1:$1))
  • These functions can be used also to create Dynamically changing graphs, functions, searches, or anything else that takes a range as an input.
  • Tutorial

Column/Line graphs

Prerequisites:
Files:
Outline:

Scatter-Plots

Prerequisites:

Matching Chart Types to Data

Files:

9. DPs Cones.xlsx

Outline:
  • In the class file you have 3 tabs. The first is used to demonstrate and learn about scatter-plots.
  • Scenario: You started your own snow cone shack and called it DP’s Cones. You have been open for a few years and decided to create a survey to determine if you are charging the right price for your large snow cone. In the survey, the respondent will answer how many times they would buy a snow cone each month given a certain price. The law of demand says that as you charge higher prices, the demand for the product will go down. From the 30 responses you recieved, is this true? We are currently charging $1.50. Are we charging the right price?
  • Paste Special (Transpose)
    • Use paste special to paste the values, transposed, down the empty slots labeled “Price” and “Quantity”. So it looks like this:

capture

  • Insert a scatter-plot
    • Highlight the table you just created and insert a scatter-plot.
  • Add a trendline
    • Click on the data points and then right click and select “Add Trendline…”
  • Display equation on Chart
    • Check “Display equation on chart”
  • Display R-squared
    • The closer to 1 the R-squared is, the more the variation can be explained by the independent variables.
  • Switching the Y and X variables on a scatter-plot (2 methods)
    • After inserting graph, go to Chart Design tab
    • Click “Switch Row/Column”

//Should this be removed?

  • Revenue graph
    • Revenue = Price * Quantity
    • So use a function to calculate the revenue at each price.
    • Create a scatter plot that shows revenue at each price. Revenue needs to be on the y axis.
    • Add a trend-line, this time a polynomial should work.
    • Change major units in the axis options to say “.5 instead of 1. What does that do?
    • What is the equation? What is the R-squared?
    • Estimate the price at which revenue will be maximized.
      • Answer: About $2.50
    • So we need to be charging a whole dollar more!

Pie Charts

Prerequisites:

Matching Chart Types to Data

Files:

9. DPs Cones.xlsx

Outline:
  • Scenario: We implemented a rewards program last year and had 200 people sign up! We kept track of their First Name, Last Name, Age, Gender, and Phone Number
  • Countif formula to fill out the Gender Chart
  • Make a pie chart
    • Select the gender data and insert a pie chart.
    • Add a data callout so it shows the percentages outside the pie.

Histograms

Prerequisites:

Matching Chart Types to Data

Files:

9. DPs Cones.xlsx

Outline:
  • We want to see what age group signed up for our rewards program.
  • You will need the data analysis tool pak add-in to create a Histogram.
  • Underneath the cell with “Ages” in it you will need to add the increments you want to show up on your histogram.
    • I chose increments of 5… 10, 15, 20, 25, 30, 35
  • Click on the data tab. In the Analyze group, click on “Data Analysis.”
  • Find histogram, click it, and press ok.
    • Input range: the input range is the range of data you want to analyze… the range E16:E215.
    • Bin range: the range of increments we set up… J35:J40
    • Output range: where do you want to output the table? L34 works
    • Chart output: check this to get the chart as well as the table

Advanced Searching

Searching with multiple criteria

Prerequisite:

INDEX/MATCH Functions

Files:

11. Timesheet Advanced Search.xlsx

Outline:
  • Used when a single searched column has duplicate values
    • A second column can be searched to further specify the rows
  • Array Functions
    • Differences between Normal and Array Functions
    • Entered by pressing Ctrl+Shift+Enter (Mac and PC)
  • Setting up your function
    • {=INDEX( lookup_array, MATCH(1,(Search_array_1 = Search_value_1) * (Search_array_2 = Search_value_1) * … , 0), Column_number)}
    • Curly Braces inserted automatically when entered as array function

Online Tutorial
Macros

Macros in Excel are about creating automated processes. They can perform any number of functions or patterns. If ever you find yourself performing a repeated set of actions, especially formatting, creating a macro can save you a lot of time.

Prerequisites:

No specific prerequisites, but make sure students are comfortable in Excel before teaching macros.

Files:

14. Macros.xlsx

Outline:
  • Under the view tab, select “Record Macro” and then toggle “Use Relative References”
  • Give it a name, and a shortcut if desired
    • Many of the shortcuts that are Option + Cmd + _ are reserved by the system, even if sometimes it doesn’t tell you. If you hold Shift while pressing a letter, the command will change to Control + Shift + _, and there are many more of those available
  • Perform any sequence of actions
  • Click “Stop Recording” (Back in the View tab)
  • Now you can activate this macro on any cell and it will repeat the same actions
  • To view/edit a previously made macro, click on View Macros
    • Options button lets you edit the name and shortcut
    • If you’re not afraid of playing with some code, the Edit button will take you to the VBA editor where you can make even more powerful macros
  • If you want to save your macro to use when editing later, you will have to save as a macro-enabled workbook (.xlsm instead of .xlsx)

Tutorial