Old Excel Day 2: Formatting

Objectives

General:

• Data importing
• General data formatting
• Conditional formatting

Specific:

• Import and format data from text files
• Use basic data cleaning techniques
• Use general and custom data formats
• Create and manipulate tables
• Apply conditional formatting to facilitate data analysis

Outline

Hook: Show the class what the text file looks like and then show them what the same data looks like organized in Excel

Introduction:

• Names, positions, goals for class, etc.

Import and format data from text files

• Explain different options in import wizard
• Explain what delimited means
• Import Data

Use basic data cleaning techniques

• Garbage in Garbage out
  • This refers to the idea that if you have bad input, your spreadsheet will not give you useful results. It is important that you clean up your data to ensure you get the right totals/analyses.
• Text to columns
• Flash fill
• Format painter
• Remove Duplicates
• Trim Function
• Proper Function
• Clean Function

Use general and custom data formats

• Format phone number, SSN, and sales numbers
• Custom format the BYU ID to be the same format that is on the student ID card

Create and manipulate tables

• Shortcuts (Cmd+Shift+Over/Down)
• Format as a Table
• Themes
• Sorting and Multilevel Sorting
• Filtering and Filtering by Multiple Columns
• Totals Row

Apply conditional formatting

• Highlight rules
• Data Bars
• Spark lines

Conclusion

• Recap:
  • Import and format data from text files
  • Use basic data cleaning techniques
  • Use general and custom data formats
  • Create and manipulate tables
  • Apply conditional formatting to facilitate data analysis
• Next time:
  • Functions/Formulas
• Office/Facebook

Example Hook

Here’s a complete Google Sheet with the same data: Completed File

Example Questions

Why is the trim function useful?

Can someone give an example of how we could use more custom formats?

What is “Garbage in, Garbage out”?

What are some other delimiters we could use?

In what other scenarios would we use a text to column delimiter?

Example Activities and Files

Have students download Formatting Data.txt and follow along as you show them how to import a text file into a workbook

Have students use the imported data from Formatting Data.txt and follow along as you show them how to apply general formatting

Have students use the imported data from Formatting Data.txt and follow along as you show them how to use flash fill

Have students use the imported data from Formatting Data.txt and follow along as you show them how to create and manipulate a table

Have students use the imported data from Formatting Data.txt and follow along as you show them how to apply conditional formatting

Other Resources

Old Page for Excel Day 2