Excel Tools: Summarizing Data

• Summary
• Target Audience
• Course Overview
• Goals
• Key Takeaways
• Schedule
• Exercise Descriptions

Summary

A one-day course exploring the use of Microsoft Excel to quickly and correctly summarize data for making decisions in the context of city operations. The course involves hands-on practice with open data and applications of key features of Excel for clear, concise, and actionable insights from the wealth of data collected by city agencies.

back to top

Target Audience

Managers, supervisors, and team leaders with a need to leverage basic and advanced features of Excel for extracting meaning from data, as well as communicating those findings in a clear and concise way for key decision makers.

back to top

Course Overview

This course will address one of the greatest challenges managers face today: making sense of the data they already have. Being able to quickly and efficiently summarize and analyze data is essential to making better business decisions. Using Excel 2010, participants will be taught some of the most effective techniques in summarizing and displaying data to extract actionable intelligence quickly and accurately.

back to top

Goals

• Provide hands-on experience using Excel to clean and summarize data, including useful tips and tricks to working with city data
• Cover best practices when analyzing and visualizing data
• Introduce advanced functionality within Excel as it relates to summarizing data

back to top

Key Takeaways

• Participants will be more proficient using Excel for cleaning, analyzing, and visualizing data
• Participants will have a better understanding of the analytics process
• Participants will be familiar with Excel functions and other advanced features of Excel for analyzing data
• Participants will be familiar with fundamental best practices for visualizing data in Excel

back to top

Schedule

• Introduction to Excel (9:00 - 9:50) - Objective: Load data into Excel and format it properly
  • Overview of Excel
  • Excel shortcuts
  • Basic formulas and functions
  • Basic math functions (SUM, COUNT, COUNTA) and Subtotaling
  • Subtotaling
• Break (9:50 - 10:00)
• Working with Data in Excel (10:00 - 10:50) - Objective: Perform basic data manipulations in Excel
  • Task: Work with NYC population data 1970 - 2010
  • Working with ranges and cell references in Excel
  • Basic charts
• Break (10:50 - 11:00)
• (11:00 – 12:00) - Objective: Use Excel functions to transform data
  • Task: Work with 311 Service Requests
  • Filtering data
  • Conditional Formatting
  • PivotTables
  • Exercise 1: Explore 311 Service Requests by borough to find patterns
• Lunch (12:00 – 1:00)
• Visualizing Data (1:00 - 1:30) - Objective: Create clear and meaningful data visualizations
  • Overview of chart design and layout
  • Practical exercise with laying out data in charts
• Advanced Functions (1:30 – 1:50) - Obj: Use functions to transform data
  • Basic date functions (HOUR)
  • Using documentation
• Break (1:50 - 2:00)
• More Advanced Functions (2:00 - 2:50) - Obj: Use functions to transform data
  • More basic date functions (WEEKDAY and DAY)
  • Using the CHOOSE function
  • Basic text functions (FIND, MID, CONCATENATE)
  • Exercise 2: Vision Zero (dB)
• Putting it all together (3:00 – 4:00) - Obj: Demonstrate knowledge through practical exercise
  • Task: Combine 311 Service Request data with NYC Population data to find service requests per 1000 people for a given area
  • Nesting functions
  • Relational functions (VLOOKUP) and Linking Spreadsheets
  • Macros
• Wrap-up discussion (4:00 – 4:30)
  • Brief overview of Open Data
  • Demonstration of data sources for exercise
• Course Evaluations and Dismissal (4:30 - 5:00)

back to top

Exercise Descriptions

Exercise 1: Explore 311 Service Requests by borough to find patterns

• Task to participants
  • In small groups, explore the complaints for your assigned borough
  • What are the common types of complaints?
  • Which Community Districts have the most complaints?
  • Identify any other meaningful patterns to the noise complaints
  • Describe your findings to the class
• Desired outcomes
  • Participants will be familiar with the basic steps in exploratory data analysis
  • Participants will demonstrate basic proficiency with Excel

back to schedule | back to top

Exercise 2: Vision Zero (dB)

• Task to participants
  • Given 311 noise complaint data, assist enforcement efforts by identifying community districts that have a high volume of noise complaints and the time frame enforcement resources should be deployed to combat the noise issue at its peak
  • Identify the prevalent types of noise complaints in these areas to guide enforcement in each community district
• Desired outcomes
  • Participants will be familiar with the analytics process, using NYC-specific data to make policy and program decisions
  • Participants will be more familiar with the tools of basic data analysis and understand the types of questions that can be answered with data

back to schedule | back to top