Autoplay
Autocomplete
HTML5
Flash
Player
Speed
Previous Lecture
Complete and continue
Simple Spreadsheets: From Spreadsheet Stress to Superstardom
Welcome
What We're Covering
Share Your Mailing Address (So I Can Send You Some Dataviz Swag!)
Introduce Yourself in the Private Facebook Community
The Absolute Beginner's Guide to Spreadsheets
Intro
Learn Spreadsheet Lingo (Tabs, Sheets, Rows, Columns, Cells, Workbooks) (3:34)
Format Text (Bold, Italics, Underlines) (1:50)
Format Colors (Fill Color, Font Color) (2:04)
Add or Remove Gridlines and Borders (2:56)
Insert Images (Logos, Photos, etc.) (2:43)
Insert Icons* (13:01)
Explain Complicated Concepts with Diagrams (3:12)
Insert Hyperlinks (to Websites or Folders) (2:11)
Auto-Fill (123, abc, Jan Feb Mar)* (2:02)
Do Basic Math (Addition, Subtraction, Multiplication, and Division)* (3:58)
Get Started with Common Formulas (Average, Sum)* (3:54)
Navigate Your Spreadsheets
Check the File Type (2:05)
Give the File a Descriptive Name (2:15)
Get Acquainted with the Dataset (7:09)
Insert New Sheets* (0:30)
Delete Sheets You No Longer Need (1:27)
Hide and Unhide Sheets to Temporarily Remove Them from View (0:57)
Re-Name Sheets (2:48)
Code-Color Sheets (2:23)
Password-Protect Sheets (4:42)
Freeze Panes* (6:51)
Sort Data* (4:24)
Filter Data* (6:15)
Re-Size Your Column Widths and Row Heights (4:31)
Wrap Text (2:17)
Make the Column Headers Stand Out (1:21)
Set Up Excel Tables (9:47)
Merge Variables Together or Pull Them Apart
Intro & Materials
Combine Two or More Spreadsheets Together with Lookup Functions: Example 1* (13:07)
Combine Two or More Spreadsheets Together with Lookup Functions: Example 2 (15:24)
Combine Content from Several Cells into a Single Cell with Concatenate and &* (5:18)
Concatenate Months and Days (1:37)
Concatenate First Names and Last Names (2:58)
Concatenate Phone Numbers (2:31)
Parse Data from a Single Cell into Many Cells: Text to Columns* (7:15)
Transpose (Flip) Rows and Columns in a Table* (2:14)
Format, Clean, and Recode the Data: Numbers
Intro & Materials
Add Commas to Large Numbers (0:57)
Round Numbers with Decimal Places Up or Down to the Nearest Whole Number* (2:18)
Generate Random Numbers with Randbetween (3:56)
Format, Clean, and Recode the Data: Dates
Auto-Fill Dates Down a Column or Across a Row (1:31)
Transform Full Dates into Just Months, Days, and Years* (2:24)
Select Your Preferred Date Format (0:43)
Automatically Enter Today’s Date and Time in Your Spreadsheet (1:09)
Transform Birthdates into Ages* (3:16)
Calculate the Length of Time Between Two Dates (3:25)
Format, Clean, and Recode the Data: Currency
Select Your Preferred Money Format (Currency or Accounting) (1:33)
Round Pennies Up or Down to the Nearest Whole Dollar (2:28)
Format, Clean, and Recode the Data: Text
Place First and Last Names into Separate Columns* (2:47)
Peel Apart Letters and Numbers with Left, Right, and Mid* (5:32)
Transform Lowercase and Uppercase Letters with Lower, Upper, and Proper* (1:29)
Put It All Together! Use Left, Mid, Right, Lower, Upper, Proper, and Concatenate* (4:22)
Format, Clean, and Recode the Data: Categorize Values
Intro & Materials
Recategorize Values with If* (4:24)
Recategorize Values with Vlookup* (3:49)
Format, Clean, and Recode the Data: Check for Duplicates and Missing Data
Intro & Materials
Spot the Duplicate Entries in a Different Color* (3:41)
Remove the Duplicate Entries (Manually or with Remove Duplicates)* (7:27)
Spot the Blank Cells in a Different Color* (4:05)
Find the Counts and Blanks with Counta and Countblank* (6:32)
Explore the Data with Sparklines
Intro & Materials
Insert Miniature Line Graphs Called Sparklines* (2:32)
Insert Miniature Column Charts Called Column Sparklines (1:56)
Insert Win/Loss Sparklines (2:25)
Adjust the Vertical Y-Axis (6:37)
Adjust the Color (6:14)
Add Markers (5:26)
Add New Data and Update the Data Source (4:16)
Adjust the Data Source Even More (Two Advanced Nuances) (2:26)
Change Your Mind? Remove (Clear) the Spark Lines Altogether (1:55)
Explore the Data with Data Bars
Intro & Materials
Insert Miniature Bar Charts Called Data Bars* (2:15)
Data Bars are Helpful Down Columns (But Not Across Rows) (4:10)
Data Bars are Helpful for Both Positive and Negative Numbers (1:08)
Adjust the Label Placement (i.e., Don't Place the Number on Top of the Bar) (15:39)
Adjust the Horizontal X-Axis (4:29)
Adjust the Color (7:34)
Adjust the Background Shading (i.e., Create a Stacked Bar Chart) (4:11)
Change Your Mind? Remove the Data Bars Altogether
Summarize Numbers with Formulas
Intro & Materials
Mean, Median, and Mode* (7:48)
Min, Max, and Range* (3:06)
Standard Deviation and Variance (6:39)
Kurtosis (2:44)
Quartiles (4:43)
Outliers (11:18)
Summarize Categories with Formulas
Intro & Materials
Count & Sum* (3:18)
Countif (8:09)
Countifs (11:13)
Sumif (4:38)
Sumifs (9:28)
Sumif or Sumifs? Always Use Sumifs!!! (6:19)
Summarize Data with Pivot Tables
What's a Pivot Table?! Intro & Materials* (5:28)
Get Acquainted with the Dataset (5:24)
What Are You Trying to Learn? (2:17)
Store Raw Data Separately from Clean Data (3:41)
Freeze the Top Row, Insert Filters, and Re-Size Columns (3:32)
Recode Full Dates into Years (1:36)
Recategorize with If (4:37)
Explore the Data with Sparklines, Data Bars, and/or Heat Tables (5:36)
Ensure that Pivot Table Prerequisites Are Met* (4:44)
Insert a Pivot Table* (2:14)
Explore the New Pivot Sheet* (4:19)
Drag and Drop Your Variables* (5:49)
Drag and Drop Your Variables (Even More) (7:23)
Filter Out Certain Variables (2:04)
Group Variables into Categories (1:52)
Double-Click Anything that Looks Fishy (1:58)
Refresh the Pivot Table If You Make Small Changes to Your Dataset (2:31)
Use an Excel Table If You're Continually Adding New Rows to Your Dataset (6:23)
Change Your Mind? Undo the Excel Table (1:59)
Putting It All Together: Demographic Data
Intro & Materials
Step 1: Navigate Your Spreadsheets (16:32)
Step 2: Merge Datasets Together (10:44)
Step 3: Format, Clean, and Recode the Data: Check for Duplicates (4:18)
Step 3: Format, Clean, and Recode the Data: Check for Missing Data (7:32)
Step 3: Format, Clean, and Recode the Data: Recode/Recategorize Variables (6:50)
Step 4: Explore the Data with Sparklines, Data Bars, Heat Tables, and/or Additional Color-Coding (9:55)
Step 5: Summarize Data with Formulas (Overall) (15:36)
Step 5: Summarize Data with Formulas (by Subgroup) (17:19)
Step 5: Or, Summarize Data with Pivot Tables (25:07)
Putting It All Together: Satisfaction Surveys
Step 1: Navigate Your Spreadsheets (11:01)
Step 2: Merge Datasets Together (1:17)
Step 3: Format, Clean, and Recode the Data (13:38)
Step 4: Explore the Data with Sparklines, Data Bars, Heat Tables, and/or Additional Color-Coding (6:01)
Step 5: Summarize Data with Formulas (10:51)
Step 5: Or, Summarize Data with Pivot Tables (13:59)
Share the Results in a Table (11:54)
Share the Results in a Graph (27:00)
Putting It All Together: Pre-Post Assessments
Step 1: Navigate Your Spreadsheets (14:29)
Step 2: Merge Datasets Together (13:09)
Step 3: Format, Clean, and Recode the Data (14:02)
Step 4: Explore the Data with Sparklines, Data Bars, Heat Tables, and Additional Color-Coding (2:40)
Step 5: Summarize Data with Formulas (18:27)
Step 5: Or, Summarize Data with Pivot Tables (9:49)
Share the Results in a Table or Graph (6:27)
Bonus! Getting Started with Charts
Set Up Your Table and Insert a Brand New Chart (5:37)
Declutter the Chart (7:52)
Add Numeric Labels to a Chart (3:08)
Re-Order the Bars (3:33)
Reduce the Awkward White Space Between the Bars (2:36)
Brand with Custom Fonts (5:03)
Brand with Custom Colors (7:04)
Re-Size the Chart (and Text) (4:39)
Tell a Story with Dark Colors (4:28)
Tell a Story with a Title, Subtitle, and/or Annotation (7:25)
Move the Chart From Excel into Word or PowerPoint (6:15)
Bonus! The Excel Test
The Excel Test (28:58)
Bonus! How to Enter Cleaner Data
How to Enter Cleaner Data AND Automate the Entire Analysis and Visualization Process (37:47)
Next Steps
Recommended Resources
Course Evaluation Survey
Format Text (Bold, Italics, Underlines)
Lecture content locked
If you're already enrolled,
you'll need to login
.
Enroll in Course to Unlock