Working with Spreadsheets

… a core topic in Analytic Methods 

Excel trainingTopic description

This topic covers the basic knowledge and skills of using spreadsheet software, such as Excel, to organize, analyze, and present data.

Topic learning outcome

Appropriately utilize, interpret, and present spreadsheet analyses of public management problems.

Core concepts (micro-skills) associated with this topic
15 ESSENTIAL SKILLS FOR WORKING WITH SPREADSHEETS
Excel Basics – The Interface

Excel Data Formatting, Grouping, Showing & Hiding

Excel Data Inputting

Excel Data Referencing – Absolute & Relative

Excel Data Functions – Mathematical, Statistical

Excel Data Generation – Time, Date, Random

Excel Data Comparison – Logical & Conditional Formatting

Excel Data Seeking – Reference, Search

Excel Text Functions

Excel Data Determination – Goal Seek, What-If

Excel Working with Complex Formula – Tracing

Excel Data Checking & Validation

Excel Presenting Data – Formatting

Excel Presenting Data – Tables

Excel Presenting Data Visually

Recommended readings for 10 hours of study

This is one of the topics on the Atlas most suited to outside-the-classroom instruction  using open-access online resources. It would be easy to imagine a curriculum where this topic was required and the principal role of the instructor is to assign and mark assessment exercises to test students’ mastery of the core micro-skills. An example of an open access training course is Excel Exposure, A University of Reddit Course, at http://excelexposure.com/, accessed 13 February 2015.

Ben Currier, Excel Exposure – Lesson Guide, at http://excelexposure.com/lesson-guide/, accessed 13 February 2016.

Ben Currier, Excel Exposure – Master Workbook, Version 2013-04-30, uploaded from http://excelexposure.com/workbook/ on 13 February 2016 and found on the Atlas at http://www.atlas101.ca/pm/wp-content/uploads/2016/02/excel-exposure-master-workbook-4-30-13.xlsx. This document is described by the author as “an all-in-one Excel Cheat Sheet” and includes:

  • List of all Excel functions/formulas with explanation, syntax, category, and experience level ranking.
  • List of all Excel shortcuts with explanation, shortcut method, category, and experience level ranking.
  • Outline of Overall Lesson Plan, this will evolve over time but has many of the areas I plan to cover.

As can be seen in the table below, this consists of approximately 4 hours of watching and reading (about 200 minutes of video lessons and 5 non-video quick tips and lessons).This, plus time for practice, would seem to be constitute approximately one course-week of study.

Excel Exposure, A University of Reddit Course
(beginner and intermediate lessons)
Beginner’s Introduction to Excel [19:44]

Basic Formatting Techniques [08:06]

Absolute and Relative Cell Referencing (i.e. the $ sign) [07:44]

Math & Statistical Functions (e.g. SUM, AVERAGE) [13:53]

Date & Time Functions (e.g. TODAY, MONTH) [13:25]

Text Functions (e.g. LEN, CONCATENATE) [14:34]

Group / Ungroup Rows & Columns [Non-Video Quick Tip]

Alternative to Merged Cells (Includes Removing Gridlines) [Non-Video Quick Tip]

Extract List From Data With Duplicate Values Removed [Non-Video Quick Tip]

Logical Functions (e.g. IF, AND) [14:56]

Lookup & Reference Functions (e.g. VLOOKUP, MATCH) [14:04]

Naming Ranges and Cells [09:58]

Data Validation with In-Cell Drop-Down List [08:54]

Conditional Formatting [09:11]

Introduction to Pivot Tables [06:57]

Advanced Pivot Tables: Round 1 [08:18]

Advanced Pivot Tables: Round 2 [11:19]

Auto-Filter and Advanced Data Filtering [06:34]

Creating and Designing Charts (including Sparklines) [14:48]

Error Checking and Data Monitoring [16:52]

Introduction to Tables [Non-Video Guest Lesson]

Protecting and Hiding Information and Formulas [Non-Video Guest Post]

A shorter set of course videos is provided at no cost by a pair of young Danes, Kasper and Mikkel, at Spreadsheeto.com (at http://spreadsheeto.com/, accessed 16 June 2016). The seven lessons are shown below.

Seven Microsoft Excel Tutorials from Spredsheeto.com
(links to be added)
Lesson 1: Introduction and Smartcuts (10-minute video

Lesson 2: Formatting (9-minute video)

Lesson 3: Functions

Lesson 4: Advanced functions

Lesson 5: Big data

Lesson 6: Conditional formatting

Lesson 7: Visualizing data

Another example of Excel lesson organization is from the outline of two one-day in-person training sessions (at about $300 each) offered in Toronto by New Horizons Computer Learning Centers at http://www.nhtorontocanada.com/training-and-certifications/microsoft-office/excel/gclid/cjwkeaiaxfu1brdf2cfnopyb9jesjadf-mdj5lgsagdtwwthse7rwiyakb7npllrl_1mcu40vqk6nhocjdhw_wcb#!/category/34, accessed 13 February 2016.

Outline of two full-day training sessions by New Horizons
Part I

1 – Getting Started with Microsoft Office Excel 2016

A: Navigate the Excel User Interface

B: Use Excel Commands

C: Create and Save a Basic Workbook

D: Enter Cell Data

E: Use Excel Help

2 – Performing Calculations

A: Create Worksheet Formulas

B: Insert Functions

C: Reuse Formulas and Functions

3 – Modifying a Worksheet

A: Insert, Delete, and Adjust Cells, Columns, and Rows

B: Search for and Replace Data

C: Use Proofing and Research Tools

4 – Formatting a Worksheet

A: Apply Text Formats

B: Apply Number Formats

C: Align Cell Contents

D: Apply Styles and Themes

E: Apply Basic Conditional Formatting

F: Create and Use Templates

5 – Printing Workbooks

A: Preview and Print a Workbook

B: Set Up the Page Layout

C: Configure Headers and Footers

Part II

1 – Working with Functions

A: Work with Ranges

B: Use Specialized Functions

C: Work with Logical Functions

D: Work with Date & Time Functions

E: Work with Text Functions

2 – Working with Lists

A: Sort Data

B: Filter Data

C: Query Data with Database Functions

D: Outline and Subtotal Data

3 – Analyzing Data

A: Create and Modify Tables

B: Apply Intermediate Conditional Formatting

C: Apply Advanced Conditional Formatting

4 – Visualizing Data with Charts

A: Create Charts

B: Modify and Format Charts

C: Use Advanced Chart Features

5 – Analyzing Data with PivotTables and PivotCharts

A: Create a PivotTable

B: Analyze PivotTable Data

C: Present Data with PivotCharts

D: Filter Data by Using Timelines and Slicers

Recommended readings in MPP and MPA courses 

[TO COME]

Page created by: Ian Clark, last modified on 16 June 2016.

Image: New Horizons, at http://www.nhtorontocanada.com/training-and-certifications/microsoft-office/excel/gclid/cjwkeaiaxfu1brdf2cfnopyb9jesjadf-mdj5lgsagdtwwthse7rwiyakb7npllrl_1mcu40vqk6nhocjdhw_wcb#!/category/34, accessed 13 February 2016.