… a core topic in Analytic Methods

### Topic 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.

###### 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] 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] Naming Ranges and Cells [09:58] Conditional Formatting [09:11] Introduction to Pivot Tables [06:57] 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.

 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.

###### 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

###### 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

4 – Visualizing Data with Charts

A: Create Charts

B: Modify and Format Charts

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]