Excel for Data Analysis II

layout:true

<div class="header" style="display:block; text-align: left; color:gray; font-size:1em; position: fixed; top: 0px; left: 0px; height: 30px;vertical-align:middle;margin:7px 0 0 0;width:100%;background: #fcfcfc;
background: -moz-linear-gradient(top, #fcfcfc 0%, #d1d1d1 100%); 
background: -webkit-linear-gradient(top, #fcfcfc 0%,#d1d1d1 100%);
background: linear-gradient(to bottom, #fcfcfc 0%,#d1d1d1 100%);
filter: progid:DXImageTransform.Microsoft.gradient( startColorstr='#fcfcfc', endColorstr='#d1d1d1',GradientType=0 );">
 <img src="images/datapolitan_transparent_small.png" style="position: fixed;top: 2px; left: 5px; width: 14%;">
 Excel for Data Analysis II - NYC Parks and Recreation
</div>
<div class="footer" style="display:block;color:gray;position:fixed;bottom: 0px;left:0px;height:30px;vertical-align:middle;margin:0 0 0 0;width:100%;background: #fcfcfc;background: -moz-linear-gradient(top, #fcfcfc 0%, #d1d1d1 100%);background: -webkit-linear-gradient(top, #fcfcfc 0%,#d1d1d1 100%);background: linear-gradient(to bottom, #fcfcfc 0%,#d1d1d1 100%);sfilter: progid:DXImageTransform.Microsoft.gradient( startColorstr='#fcfcfc', endColorstr='#d1d1d1',GradientType=0 );">
 
 Excel for Data Analysis II by <a xmlns:cc="http://creativecommons.org/ns#" href="http://www.datapolitan.com" target="_blank" property="cc:attributionName" rel="cc:attributionURL">Richard Dunks</a>, Elizabeth DiLuzio, and Mark Yarish is licensed under a <a rel="license" href="http://creativecommons.org/licenses/by-sa/4.0/">Creative Commons Attribution-ShareAlike 4.0 International License</a>.<a rel="license" href="http://creativecommons.org/licenses/by-sa/4.0/"><img alt="Creative-Commons-License" style="border-width:0" src="https://i.creativecommons.org/l/by-sa/4.0/80x15.png" /></a>
 
</div>

class:center, middle
![img-center-50](images/datapolitan.png)

# Excel for Data Analysis II City of New York Parks and Recreation

- - -

## Instructors: Richard Dunks and Elizabeth DiLuzio
## Teaching Assistant: Mark Yarish
### Follow along at: http://bit.ly/excel-analysis-ii
#### See the code at: http://bit.ly/excel-analysis-ii-code

---

class:center,middle

# Welcome
???
+ Facilitators and TA introduce themselves, establishing mastery but also creating openings for vulnerability and awareness

---

# [Zoom Drill](https://www.loom.com/share/c00ec8e6feb74226a0283175cba23e64)
???
+ Facilitators will cover the following skills: muting themselves, stopping their video, typing in chat box, raising their hand, sharing their screen

+ Mute and Unmute your microphone
--

+ Start and Stop your video
--

+ Post a message in the Chat window with your name, computer operating system (Windows or MacOS), and Excel version
--

+ Click the Participants window and Raise your hand 
--

+ Share and Unshare your screen

---

# A Few Ground Rules
???
+ Facilitators establish the intention we have for the culture of the classroom

+ Step up, step back
--

+ One mic
--

+ Be curious and ask questions
--

+ Assume noble regard and positive intent
--

+ Be present (phone, email, social media, etc.)

---

# Introduce Yourself to Your Neighbor

+ Who are you?
+ Where do you work?
+ What has been the proudest moment in your job?
+ If you were an Excel function, which one would you be?

???
+ An opportunity for participants to get to know each other and settle into the class
+ We bring them into a positive space related to their work
+ Facilitator reminds them the data isn't the end but the means to achieve the actions that make us feel good about our jobs

---

# What to Expect Today

???
+ An opportunity to set expectations about the topics covered today

--
+ Practice with group ideation and process mapping
--

+ Practice with more advanced Excel skills
--

+ Exploration of key data visualization concepts
--

+ Useful practice with NYC Parks data

---

exclude: true

# Housekeeping
+ We’ll have one 15 minute break in the morning
+ We’ll have an hour for lunch
+ We’ll have a 15 minute break in the afternoon
+ Class will start promptly after breaks
+ Feel free to step away from your computer if you need during class
+ Please take any phone conversations into the hall to not disrupt the class

???
+ Facilitator sets expectations with the students
+ Establishes the "contract" for the class

---

# Housekeeping
???
+ Facilitator sets expectations with the students
+ Establishes the "contract" for the class

+ We’ll have one 15 minute break in the morning
--

+ We’ll have an hour for lunch
--

+ We’ll have a 15 minute break in the afternoon
--

+ Instruction will start promptly after breaks
--

+ Feel free to step away from your computer if you need during instruction
--

+ Please remember to keep yourself muted and type any questions you have into the chat box

---
exclude:true
name:datp-review
class:center,middle
# A Review of [Introduction to Data Analytics](http://training.datapolitan.com/parks-intro-analytics/#1) and [Excel for Data Analysis I](http://training.datapolitan.com/parks-excel-analysis-i/#1)

---
name:objectives
class:center,middle
# What do you hope to learn today?

## Solo jot

---

# [PollEverywhere](https://polleverywhere.com)

+ Online polling tool
--

+ Can respond via text or online
--

+ VIA TEXT: Open a new text message to the number 22333. In the text message body, type LUCKYHAT776 and hit send.
![img-center-40](images/polleverywheretext.png)
--
 
+ VIA WEB: Visit [PollEv.com/luckyhat776](https://pollev.com/luckyhat776)

---
name:objectives
# What do you hope to learn today?

## Type one thing you'd like to learn at a time into the text box and click "Submit"
![img-right-40](images/polleverywhereupvote1.png)
---

# What do you hope to learn today?

## Read through other people's entries and upvote or downvote
![img-right-40](images/polleverywhereupvote2.png)

---

exclude: true
# What Do You Remember Learning In These Classes?
+ 3 minutes to brainstorm on your own
+ 3 minutes to share in your groups
+ 7 minutes to collaborate as a class

???
+ Walk through the steps, answer questions, then take 7 minutes to gather answers and map them out in a concept map with "Data-Driven Culture" in the center
+ Note in the class shareout where students are feeling comfortable vs uncomfortable, what they remember vs forget. Let this guide the upcoming Excel review.

---

# Every Good Analysis Begins with a Question
![img-center-90](images/valuechain.png)

---

# Which Boro Has the Most Inspections?
???
+ Have class vote and share their hypotheses
+ How do we get there?

![img-center-100](images/inspectionsbyboro_rev.png)

---

class:center,middle
# Let's Get Started
## Download [Parks Inspection Program Data](data/PIPData.xlsx)

---

# Parks Inspection Program (PIP)
???
+ Ask what is known about the dataset.
+ Download the dataset by clicking on the link. Do a quick datawalk. 
--

+ Random, detailed inspections of NYC Parks facilities
--

+ Inspectors conduct approximately 6,000 inspections/year
--

+ Each site receives rating of "Acceptable" or "Unacceptable" based on condition of park features
--

## For more information, [click here](https://www.nycgovparks.org/park-features/parks-inspection-program)

---
name:short-boro
# Create "Short Boro" Column
--

+ We will use the <a href="https://support.office.com/en-us/article/LEFT-LEFTB-functions-9203d2d2-7960-479b-84c6-1ea52b99640c" target="_blank">LEFT function</a>
--

+ Useful for extracting a portion of a cell's contents
--

+ We'll use the <a href="https://www.howtogeek.com/school/microsoft-excel-formulas-and-functions/lesson2/" target="_blank">Function Wizard</a> to guide us through
--

![img-center-100](images/formulabuilder.png)

---

# Create "Short Boro" Column
![img-right-50](images/leftfunction.png)
## Syntax 
### =LEFT(text, [num_chars])
--

## What it Says 
### =LEFT ( "use this value" , "extract the first XX characters" )

---

# Create "Short Boro" Column

![img-center-100](images/leftfunctionaction.png)

---

# Create a Pivot Table
![img-center-100](images/pt1.png)
--

![img-center-60](images/pt2.png)

---

# Create a Pivot Table
![img-left-35](images/pt3_rev.png)
--

![img-right-40](images/pt4_box.png)
--

![img-right-55](images/pt5_box.png)

---

# Create a Chart
+ Retype the `Row Labels` to give the chart good labels

![img-center-100](images/pt6_rev.png)

---

# 5 Analytic Tasks
???
+ Ground participants in the 5 analytic tasks with a quick review showing them the task in the context of the PIP data 
+ Focus on the things we did in Excel I in the latter three categories.
+ Use the concept map as a guide to scaffold on existing knowledge
--

+ Sort
???
+ Note the dates won't sort in order -> some 2004-2012 are in as 1904-1912
+ Good thing to point out and discuss dates and times in Excel
--

+ Filter
--

+ Aggregate
--

+ Transform
--

+ Visualize

---

exclude: true
# What questions do you have about the data?
???
+ Walk participants through the stages
+ When it comes time to decide on the question, remind them that, to make the day more interesting, they should chose a more complex problem with multiple steps

+ 3 minutes to brainstorm questions you have on your own

+ 5 minutes to share and consolidate your questions with your group

+ 5 minutes to decide on who will answer which questions

---

# What questions do you have about the data?

+ 3 minutes to brainstorm questions you have on your own
--

+ [Share out](pollEv.com/luckyhat776)
--

+ Sign up for your question for the day (no more than 4 people per question)

---

exclude:true

# What are the steps you will need to take to answer your questions?
+ 3 minutes to brainstorm steps on your own
+ 3 minutes to share and consolidate the steps with your group
+ 3 minutes to arrange the steps you will take to do the analysis

???
+ Walk participants through the stages
+ When it comes time to decide on the question, remind them that, to make the day more interesting, they should chose a more complex problem with multiple steps

---

class:center,middle

# WRAP-UP

![img-center-100](images/imp_guide.png)

???
+ Facilitator reflects on material with students to get a view on what we've covered
+ Facilitator discussed the Implementation Guide and invites them to use it in the course of the class

---

class:center,middle
# 15 MIN BREAK

---

# Documenting Your Work
???
+ Facilitator discusses the importance of documentation and why we do it as a refresher from previous classes
+ Have participants name their styles for documenting and what they remember from last class before going through the list
--

+ Important for communicating what you have done with others
--

+ Create a new tab with a running list
--

+ Add comments to a blank column at the end of your dataset
--

+ Other ways?

---

# Let's get back into the data
--

## Remember
--

![img-right-10](images/chat.png)
+ If you have a question, type it in the chat window
--

+  If you need help, raise your hand

![img-center-30](images/part_rh.png)

---

# Functions in Excel
???
+ Facilitator reviews functions by soliciting examples from participants on the use of functions in their work
--

+ Allow you to do calculations in Excel
--

+ Also allow you to manipulate text
--

+ <a href="https://support.office.com/en-ie/article/excel-functions-by-category-5f91f4e9-7b42-46d2-9bd1-63f26a86c0eb" target="_blank">And a whole lot more...</a>

---

class:center,middle
# What if we wanted to have the full boro name in our data?

---

class:center,middle
# First of all...

---

name:conditional
# Conditional Formulas
--

+ Useful when the output of a formula needs to change depending on the input
--

+ Creates more flexibility
--

+ Often used with some kind of logical test (usually an `IF`)

---

# Morning vs Afternoon Inspections
+ Create a new column "Time of Day"
--

![img-center-90](images/tod1.png)
--
 
![img-center-90](images/tod2.png)
???
+ Note there are 4 rows with errors because times aren't formatted properly (text instead of serial)
--

## What are we telling Excel to do?
---

# Morning vs Afternoon Inspections

![img-center-70](images/tod3.PNG)

---

# Logical Functions
--

+ `IF` is an example of a logical function
--

+ Syntax =IF(logical_test, [value_if_true], [value_if_false])
???
+ Play the formula out. What happens when it's not a B?
+ What solutions can the group think of?
--

+ What it Says =IF ( "test this statement I'm telling you" , "if it's true, return this value", "if it's false, return this other value" )
--

+ Logical functions return the value of TRUE or FALSE depending on the test you construct

---
name:cf-examples
# Examples of Other Logical Functions
???
+ Facilitator describes examples of logical functions and polls for usage among participants

+ [AND](https://support.office.com/en-ie/article/and-function-5f19b2e8-e1df-4408-897a-ce285a19e9d9): All conditions must be TRUE or this will return FALSE
--

+ [OR](https://support.office.com/en-ie/article/or-function-7d17ad14-8700-4281-b308-00b131e22af0): If at least one condition is TRUE, this will return TRUE
--

+ [NOT](https://support.office.com/en-ie/article/not-function-9cfc6011-a054-40c7-a140-cd4ba2d87d77): Makes TRUE into FALSE and FALSE into TRUE
--

+ [COUNTIF](https://support.office.com/en-us/article/COUNTIF-function-E0DE10C6-F885-4E71-ABB4-1F464816DF34): Count all the numbers in a specified range if they meet a specified criteria
--

+ [SUMIF](https://support.office.com/en-us/article/SUMIF-function-169B8C99-C05C-4483-A712-1697A653039B): Sum all the numbers in a specified range if they meet a specified criteria

---
exclude:true
name:logic-fn
# Logical Functions
+ IF is an example of a logical function
+ Logical functions perform a test, returning one value for TRUE and another if FALSE
+ Pro: they help you to create new data by drawing simple conclusions
+ Con: They are binary, so they are limited in power

---

# Nesting Functions
--

+ Notice we didn't rely on just one function to accomplish this task
--

![img-center-100](images/nest1.png)

+ In this case, we used the output from the `HOUR` function as part of the input to our `IF` function
--

+ This is called "nesting" and helps us create more complex formulas

---
exclude:true
# Create "Full Boro" Column

+ We will use the IF function
![img-right-45](images/iffunction.png)
???
+ Facilitator polls class for understanding and usage of the `IF` function before proceeding

---
name:full-boro

class:middle
# Back to our question:
# What if we wanted to have the full boro name in our data?

---

# Create "Full Boro" Column

![img-center-100](images/iffunctionaction.png)

---

# Nested IF Statement Example

## Syntax
### =IF(logical_test,[value_if_true],IF(logical_test,[value_if_true],IF(logical_test,[value_if_true],[value_if_false])
--

## What it Says
### =IF ( "test this statement I'm telling you" , "if it's true, return this value", "if it's false..." "test this statement I'm telling you" , "if it's true, return this value", "if it's false..." "test this statement I'm telling you" , "if it's true, return this value", "if it's false, return this other value" )

---

# Create "Full Boro" Column

![img-center-100](images/nestedifs.png)

???

+ The formula is: 
```
=IF(B2="B","Brooklyn",IF(B2="M","Manhattan",IF(B2="Q","Queens",IF(B2="X","Bronx",IF(B2="R","Staten Island","Other")))))
```

### =IF ( "test to see if B4 is equal to "B"" , "if it's true, return Brooklyn", "if it's false..." "test to see if B4 is equal to "Q"" , "if it's true, return Queens", "if it's false..." "test to see if B4 is equal to "M"" , "if it's true, return Manhattan", "if it's false..." "test to see if B4 is equal to "X"" , "if it's true, return Bronx", "if it's false... "test to see if B4 is equal to "R"" , "if it's true, return Staten Island", "if it's false, return "Other")

---

# IF Function - Bonus Round
--

+ Nesting `LEFT` into `IF` combines the "Short Boro" and "Full Boro" columns
--

+ The output from `LEFT` becomes part of the logical test for the `IF` statement
--

+ Try it yourself in a new column titled "Boro"
--

![img-center-100](images/ifbonus.png)

## ...is this useful?

???
+ Code for this is:

```
=IF(LEFT(A2)="B","Brooklyn",IF(LEFT(A2)="M","Manhattan",IF(LEFT(A2)="Q","Queens",IF(LEFT(A2)="X","Bronx",IF(LEFT(A2)="R","Staten Island","Other")))))
```

---

class:center,middle
# What did we just do?

???
+ Debrief nested formulas before moving on. What are their reactions? What are the pros and cons?

---

# Inspections by Boro

## Let's finish up by making our chart of inspections by boro

![img-center-100](images/pivotinspectionboro.PNG)

---

# Your Turn
--

+ You will be working in small groups on the question you chose this morning
--

+ Document your work
--

+ You will be presenting your analysis at the end of the day
--

+ We will be in and out of the breakout rooms to help
--

+ If you need something, raise your hand
--

![img-center-100](images/quote.png)

---
exclude: true
# Your Turn

+ Follow the process you outlined

+ Document your work

+ You will be presenting your analysis at the end of the day

+ We'll be here to help

![img-center-80](images/quote.png)

---

class:center,middle
# WRAP-UP

???
+ Facilitator reflects with the participants on the foregoing learning
+ Ensure any questions are answered and the paticipants are aware of the material to be covered in next sections

---

class:center,middle
# LUNCH

---

class:center, middle
# WELCOME BACK!

---

# Inspections by Boro - Identifying Top Parks

???
+ Explore what it would take to look at the next level of detail. PROPID is not very friendly. What does it represent? What if we were able to identify the park name from the PROPID?

![img-center-90](images/topparks.PNG)

---

name:vlookup
class:center,middle
# How often do you need to combine data from different spreadsheets?

---

# [VLOOKUP](https://support.office.com/en-us/article/VLOOKUP-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1) Explained
--

+ Useful for referencing data from another sheet (reference tables)
--

+ Syntax =VLOOKUP ( value, table, col_index, [range_lookup])
--

+ What it Says =VLOOKUP( “Find this value”, “In this table”, “Once you find it, return its corresponding value in this column number”, “FALSE always”)
--

## TRUE does a fuzzy match, FALSE is an exact match

---

name: vlookup-tips
# Tips for a Successful [VLOOKUP](https://support.office.com/en-us/article/VLOOKUP-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1)
--

+ You will need a common column of data (a reference value)
--

+ Create and title a column for the new data
--

+ Don't trust the Fuzzy Match

???
+ Before going over the tips, ask the group what tips or best practices do they use when joining?
+ Then, go over the tips
---

class:center,middle
# Let's pull the park names from [PIP All Sites](data/PIP_AllSites.xlsx)

???
+ Download All Sites
+ Complete the tips to prepare from last slide: identify common column of data
+ transfer data into same workbook
+ create column for new data

---

# Looking Up Park Name
![img-center-100](images/vlookupfunction.png)

---

# Looking Up Park Name
![img-center-100](images/vlookup_ex_rev.png)

```
=VLOOKUP(A2,'[PIP_AllSites.xlsx]PIP All Sites'!$B$1:$G$6670,6,FALSE)
```

## Breaking This Down
--

+ Lookup the value in cell A2
--

+ Match the value in PIP_AllSites Column B
--

+ Return the value in the 6th column of the range
--

+ Don't do any fuzzy matching

???

`=VLOOKUP(A2,'[PIP_AllSites.xlsx]PIP All Sites'!$B$1:$G$6670,6,FALSE)`
---

class:center,middle
# Now we can look at the parks with the most inspections

---

# Parks by Number of Inspections

--
## We could create a pivot chart of all parks

--
![img-center-95](images/pivotparkname.PNG)

--
## ...but that's a lot of information

---

# Identifying Top 3 Parks
--

![img-center-90](images/filtertop10.png)

--

![img-center-90](images/filtertop3.png)

---

# Identifying Top 3 Parks

## Which doesn't give us much information.

![img-center-100](images/top3overall.png)

---

class:center,middle
# What does the data look like if we filter the top 3 parks inspected in each boro?

---

# Stacked Values
--

+ You can stack values in rows or columns of a PivotTable
--

+ Purpose: to show more information

---

# Top 3 Parks by Boro
--

## Place Boro above Park Name in the Rows quadrant

![img-center-100](images/stackedboropark.PNG)

---

# Top 3 Parks by Boro

## Sort largest to smallest for readability

![img-center-100](images/sortdata.png)

---

# Top 3 Parks by Boro

![img-center-100](images/pivottopparks.PNG)

---

class:center,middle
# In case you got a little lost...
# [Click to download Excel Workbook with the formulas we showed](data/PIPData_Formulas.xlsx)

---

class:center,middle
# Any questions?

---

# Data Visualization

> There are two goals when presenting data: convey your story and establish credibility.

##-Edward Tufte

---

# Napoleon's March

![img-center-100](images/tufte.png)
.caption[Image Credit: [https://www.edwardtufte.com](https://www.edwardtufte.com), [CC BY-SA 2.0](https://creativecommons.org/licenses/by-sa/2.0/)]

???
+ What is this visualization showing? 
+ What's your critique?

---

class:middle
> Never underestimate your audience. It’s the most common mistake made by presenters. It is not about you anymore. It’s about your audience’s relationship with your content.

##-Edward Tufte

---

# Key Elements to Consider When Designing a Visual

--
+ Type of chart
--

+ Amount of data
--

+ Title
--

+ Use of color
--

+ Data-Ink Ratio

---

# Choosing Your Chart

![img-center-85](images/chartsuggestions.png)
.caption[Image Credit: [Extreme Presentations](http://extremepresentation.com/wp-content/uploads/6a00d8341bfd2e53ef0148c699cc96970c.jpg), [CC BY-SA 2.0](https://creativecommons.org/licenses/by-sa/2.0/)]

---

# Choosing Your Chart

![img-center-85](images/chart_chooser.png)
.caption[Image Credit: [Chart Guide](www.chart.guide), [CC BY-SA 2.0](https://creativecommons.org/licenses/by-sa/2.0/)]

---
exclude:true
class:center,middle
# Let's take a look at some of the more popular types

---
exclude:true
# Type of Chart by Data Type

![img-center-100](images/datatypechart1.png)

???
+ In this series of photos, talk the students through the way we chart categorical vs numerical data
+ Point out why bar and pie charts are the most commonly seen (you can use them with any type of data)

---
exclude:true

# Type of Chart by Data Type

![img-center-100](images/datatypechart2.png)

---
exclude:true

# Type of Chart by Data Type

![img-center-100](images/datatypechart3.png)

---
exclude:true

# Type of Chart by Data Type

![img-center-100](images/datatypechart4.png)

## Bar and pie charts can be used to visualize both categorical and numerical data

---
exclude:true

# Type of Chart by Data Type

![img-center-100](images/datatypechart5.png)

## Line charts and dot plots are best for numerical data

---
exclude:true

# Type of Chart by Data Type

![img-center-100](images/datatypechart6.png)

## Tree maps are best for categorical data

---

class:center,middle
# Let's see them in action

---

exclude:true
# Type of Chart - One Data Point

![img-center-90](images/drinkingage.png)
.caption[Image Credit: [https://www.cbc.ca](https://www.cbc.ca/news/canada/saskatchewan/drinking-age-will-remain-19-in-saskatchewan-1.1350523), [CC BY-SA 2.0](https://creativecommons.org/licenses/by-sa/2.0/)]

???
+ Thoughts on chart?
+ Better way to show drinking ages across Canada? (map with 2 colors for 2 ages represented)
+ Better way to show main point of article?

---
exclude:true
# Type of Chart - One Data Point

![img-center-50](images/bignumber.png)

???
+ This is an alternative to the chart on the previous slide

---

# Type of Chart - One Data Point

![img-center-100](images/unitchart.png)

???
+ There are other ways to represent one number

---

# Type of Chart - One Data Point

![img-center-100](images/donutnumber.png)

???
+ There are other ways to represent one number

---

# Type of Chart - Two or More Data Points

![img-center-85](images/mentalhealthbeds.png)
.caption[Image Credit: [https://www.samhsa.gov](https://www.samhsa.gov/data/sites/default/files/2016_National_Mental_Health_Services_Survey.pdf), [CC BY-SA 2.0](https://creativecommons.org/licenses/by-sa/2.0/)]

???
+ Thoughts on chart? Constructive criticism?

---

# Type of Chart - Two or More Data Points

![img-center-100](images/sidebysidebeds.PNG)

---

# Type of Chart - Two or More Data Points

![img-center-85](images/piebeds.png)

---

class:middle
> There is no data that can be displayed in a pie chart, that cannot be displayed better in some other type of chart.

##-John Tukey

---

# Type of Chart - Two or More Data Points

![img-center-85](images/stackedbeds.png)

---

# Type of Chart - Change Over Time

![img-center-80](images/linegraph.png)

???
+ Constructive feedback?

---

# Amount of Data

--
+ Remove data that doesn't support the story
--

![img-center-90](images/slopechart.png)

---

# Amount of Data

![img-center-70](images/indiaeats.png)
.caption[Image Credit: [Huffington Post India](https://www.huffingtonpost.in/2016/06/14/how-india-eats_n_10434374.html), [CC BY-SA 2.0](https://creativecommons.org/licenses/by-sa/2.0/)]

???
+ Thoughts on chart? Constructive criticism?

---
exclude:true
#  Amount of Data

![img-center-60](images/baseball.png)
.caption[Image Credit: [Sports Center](https://www.facebook.com/SportsCenter/photos/a.166942843324566/2173658729319624/?type=3&theater), [CC BY-SA 2.0](https://creativecommons.org/licenses/by-sa/2.0/)]

???
+ Thoughts on chart? Constructive criticism?

---

# Title and Annotations

##Use the title and annotations to direct the reader's analysis
--

+ Ask a question that the data answers
--

+ Tell the reader the conclusion of the visualization
--

+ Point out the trend or pattern you'd like the audience to notice
--

+ Don't get too wordy

---

# Title and Annotations

![img-center-100](images/bedstitle.PNG)

???
+ Bringing back an old chart.

---
exclude:true
# Title and Annotations

![img-center-80](images/foodorigin.png)
.caption[Image Credit: [Bloomberg Markets](https://www.bloomberg.com/markets), [CC BY-SA 2.0](https://creativecommons.org/licenses/by-sa/2.0/)]

???
+ Thoughts on chart? Constructive criticism?

---

# Title and Annotations

![img-center-90](images/obamatitle.png)
.caption[Image Credit: [US DOE](https://twitter.com/obamawhitehouse/status/677242822920151045), [CC BY-SA 2.0](https://creativecommons.org/licenses/by-sa/2.0/)]

???
+ Thoughts on chart? Constructive criticism?

---

# Use of Color

## Use color intentionally to make comprehension and interpretation easier. 
---

# Use of Color

## **For Sequential Numbers**
--

+ Use color scales with the darkest color representing the highest number and the lightest color representing the lowest number
--

+ Consider using two hues running light to dark for even more clarity

---

# Use of Color

## **For Categorical Data**
--

+ Each category gets its own color
---

# Use of Color

## **For Any Type of Data**
--

+ Consider grey to be the most important color. Use it to show the less important data while accentuating value(s) of interest in a color.

---

# Use of Color

![img-center-70](images/spaghetti.png)
.caption[Image Credit: [Bloomberg](https://www.bloomberg.com/news/features/2018-07-26/she-launches-spaceships-sells-rockets-and-deals-with-elon-musk), [CC BY-SA 2.0](https://creativecommons.org/licenses/by-sa/2.0/)]

---

# Use of Color

![img-center-80](images/obamadivided.png)
.caption[Image Credit: [Wall Street Journal](https://www.wsj.com/articles/SB10001424127887324439804578103470239517336), [CC BY-SA 2.0](https://creativecommons.org/licenses/by-sa/2.0/)]

---

# Use of Color

![img-center-100](images/mhbedscolor.PNG)

???
+ Bringing back an old chart.

---

# Tips for Using Color
--

+ Be sure there's a sufficient difference between your colors
--

+ Color scales are worth exploring
--

+ Watch out for the connotation of certain colors (ex: red)
--

+ Avoid patterns
--

+ Don't use more than 6 colors
--

+ Resource for color scales: [Color Brewer](http://colorbrewer2.org)

---

class:middle
>Clutter and confusion are failures of design, not attributes of information.

##-Edward Tufte

???
+ Class's thoughts on this? What does it mean? Do you agree?

---

# Data-Ink Ratio
--

+ Ink used to describe the data / ink used to describe everything else
--

+ Ideally, we want to minimize "ink" that's representing something other than the data (the "everything else")

--

+ Tufte calls the "everything else" chartjunk

---

# Common Types of Chartjunk
--

+ Dark gridlines
--

+ Overuse of bright, bold colors
--

+ Unnecessary use of all uppercase
--

+ Unnecessary legends
--

+ Background color

---

#Data-Ink Ratio

![img-center-90](images/baconbefore.png)

???
+ Thoughts on how to declutter

---

# Data-Ink Ratio

![img-center-100](images/baconafter.png)

---

class:center,middle
# How do you learn to create good visualizations?
#

---

class:center,middle
# How do you learn to create good visualizations?
# ...Make a lot of bad visualizations

---

# Your Turn
--

+ Graphically represent the analyses you have done so far
--

+ Incorporate data viz concepts we have discussed

???
+ Give students approx 20 minutes

---
exclude:true
# .center[15 Minute Break]

+ Leave up on your monitor one chart you'd like feedback on

+ During break, visit 3 computers

+ Leave feedback for each chart on a Post-It stuck to the monitor

+ Remember to honor the assumption of noble regard and positive intent (i.e. don't be a jerk)

---
# Peer Feedback
--

+ We're about to break you into small groups
--

+ Take turns sharing one visualization each
--

+ Give feedback to each other based on the 5 elements of a good chart
--

+ Remember to honor the assumption of noble regard and positive intent (i.e. don't be a jerk)

---
class:center,middle
15 Minute Break
---

# Your Turn
--

+ Incorporate the feedback you received on your chart
--

+ Create a quick presentation of your data story to share with the class
--

+ We'll be here to help
--

+ Make sure you've updated your documentation
--

+ [Click to submit your work](https://script.google.com/a/macros/datapolitan.com/s/AKfycbwMcE5pcJwZHdbSCN_2epwaXnRLSRPaLbHCAhxaZJ79UXaRpQ_l/exec)

???
+ Give class approx 40 minutes to create their presentations and submit their work

---

class:center,middle
# Presentations

---

class:center,middle
# WRAP-UP

---

# What We've Covered Today

+ Let's do one more [PollEverywhere](PollEv.com/luckyhat776) activity
--

In summary...:
--

+ More complex functions
--

+ More complex analyses
--

+ Ways to adapt your data visualization for your audience
--

+ What else?

---

class:center,middle
# What's to Come

???
+ Remind students that you got the data from today via hyperlink. Next class they will explore how to pull the data themselves via SQL
+ Show where that is accessed in Excel

---

# Resources

+ [Microsoft Excel keyboard shortcuts](https://support.office.com/en-us/article/keyboard-shortcuts-in-excel-for-windows-1798d9d5-842a-42b8-9c99-9b7213f0040f)
--

+ [Microsoft Office Community](https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel?sort=LastReplyDate&dir=Desc&tab=All&status=all&mod=&modAge=&advFil=&postedAfter=&postedBefore=&threadType=All&isFilterExpanded=false&page=1) - Community Q&A Board
--

+ [7 essential Excel tricks every office worker needs to know](https://www.microsofttraining.net/sharing/11-seven-essential-excel-tricks-every-office-worker-needs-to-know.html) (we covered 6 of the 7 today)
--

+ [11 Places That Can Turn You Into A Microsoft Excel Power User](https://medium.com/the-mission/eleven-places-that-can-turn-you-into-a-microsoft-excel-power-user-c119f18e138c)

--

+ [INDEX and MATCH as a VLOOKUP Alternative](https://www.got-it.ai/solutions/excel-chat/excel-tutorial/vlookup/index-match)
--

+ [Copy of today's handout](workbook.pdf)
--

+ [Datapolitan Training Classes](http://training.datapolitan.com)

---
name:contact

# .center[Contact Information]

## Elizabeth DiLuzio
+ Email: Elizabeth[at]datapolitan[dot]com
+ Twitter: [@lizdiluzio](https://twitter.com/lizdiluzio)

## Richard Dunks
+ Email: richard[at]datapolitan[dot]com
+ Website: http://www.datapolitan.com
+ Twitter: [@datapolitan](https://twitter.com/datapolitan)

---

class:center, middle
# THANK YOU!

# Please take a moment to [provide your feedback](https://docs.google.com/forms/d/e/1FAIpQLScqxFDbN09n67Od5Cj5hIWatMIwnqBoQhLzTnMn4JT5hcLSBw/viewform?usp=sf_link)

## And checkout [the dashboard of your responses](https://datastudio.google.com/s/kSGuvoygzjg)

---

exclude:true

# Create the Table
--

exclude:true
![img-center-90](images/emptytable.png)

???
+ Make new tab and create table in it

---

exclude:true

# Number of Inspections
--

exclude:true
+ We will use the COUNTIF function
--

exclude:true
+ Useful when you want to count the number of cells that fit a certain criteria

---

exclude:true

# Number of Inspections

### Syntax
### =COUNTIF(range,criteria)
--
exclude:true

### What it Says
### =COUNTIF ("search here", "count every time you find this value" )
--
exclude:true
![img-center-50](images/countiffunction.png)

---

exclude:true

# COUNTIF
--
exclude:true
![img-center-90](images/countiffunctionaction.png)

---

exclude:true

# Number of Hours Worked
--
exclude:true
+ We will use the SUMIF function
--
exclude:true
+ Useful when you want to add together the values in cells aligned with cells that fit a certain criteria
--
exclude:true
+ It can only be used on numbers

---

exclude:true

# Number of Hours Worked

### Syntax
### =SUMIF(range,criteria,[sum_range])
--
exclude:true
### What it Says
### =SUMIF ("look here", "for this value", "when you find it, add the corresponding value from here" )
--
exclude:true
![img-center-40](images/sumiffunction.png)

---

exclude:true

# SUMIF
--
exclude:true
![img-center-100](images/sumiffunctionaction.png)

---

exclude:true

# Check Your Work
--
exclude:true
## Always a good idea to double check when using formulas
--
exclude:true
## You can check...
--
exclude:true
+ With a different set of formulas
--
exclude:true
+ With a PivotTable
--
exclude:true
![img-center-40](images/pivotcheck.png)
---