Autoplay
Autocomplete
HTML5
Flash
Player
Speed
Previous Lecture
Complete and continue
Simple Spreadsheets
Welcome
Important Course Info (1:09)
Webinars for Silver and Gold Tier Students
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)
Week 1: Navigate Your Spreadsheets
Intro
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)
Week 2: 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)
Week 3: Format, Clean, and Recode the Data: Numbers
Intro & Materials
Numbers: Add Commas to Large Numbers (0:57)
Numbers: Round Numbers with Decimal Places Up or Down to the Nearest Whole Number* (2:18)
Numbers: Generate Random Numbers with Randbetween (3:56)
Week 3: Format, Clean, and Recode the Data: Dates
Dates: Auto-Fill Dates Down a Column or Across a Row (1:31)
Dates: Transform Full Dates into Just Months, Days, and Years* (2:24)
Dates: Select Your Preferred Date Format (0:43)
Dates: Automatically Enter Today’s Date and Time in Your Spreadsheet (1:09)
Dates: Transform Birthdates into Ages* (3:16)
Dates: Calculate the Length of Time Between Two Dates (3:25)
Week 3: Format, Clean, and Recode the Data: Currency
Currency: Select Your Preferred Money Format (Currency or Accounting) (1:33)
Currency: Round Pennies Up or Down to the Nearest Whole Dollar (2:28)
Week 3: Format, Clean, and Recode the Data: Text
Text: Place First and Last Names into Separate Columns* (2:47)
Text: Peel Apart Letters and Numbers with Left, Right, and Mid* (5:32)
Text: Transform Lowercase and Uppercase Letters with Lower, Upper, and Proper* (1:29)
Text: Put It All Together! Use Left, Mid, Right, Lower, Upper, Proper, and Concatenate* (4:22)
Week 4: Format, Clean, and Recode the Data: Categorize Values
Intro & Materials
Recategorize Values with If* (4:24)
Recategorize Values with Vlookup* (3:49)
Week 4: 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)
Week 5: 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)
Week 6: 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
Week 7: 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)
Week 8: 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)
Week 9: 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)
Week 10: 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)
Week 11: 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)
Week 12: Putting It All Together: Pre-Post Assessments
Intro & Materials
Step 1: Navigate Your Spreadsheets
Step 2: Merge Datasets Together
Step 3: Format, Clean, and Recode the Data
Step 4: Explore the Data with Sparklines, Data Bars, Heat Tables, and Additional Color-Coding
Step 5: Summarize Data with Formulas and/or Pivot Tables
Bonus! Week 13: Getting Started with Charts
Create a Brand New Chart
Re-Size the Chart
Change the Chart's Font and Font Sizes
Declutter the Chart
Change the Chart's Colors
Add Numeric Labels to a Chart
Add Titles and Subtitles to a Chart
Annotate the Chart
Move the Chart to a Different Location Within Excel
Move the Chart From Excel into Word or PowerPoint
Next Steps
Next Steps
Recommended Resources
Course Evaluation Survey
Format Text (Bold, Italics, Underlines)
Lecture contents locked
Enroll in Course to Unlock
If you're already enrolled,
you'll need to login
.