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
Use an Excel Table If You're Continually Adding New Rows to Your Dataset
Lecture content locked
If you're already enrolled,
you'll need to login
.
Enroll in Course to Unlock