# Working with Spreadsheets

*… 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.

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

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 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 I1 – 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 II1 – 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.