Module 4: Excel Advanced
Elevate your Excel skills with charts, PivotTables, advanced functions, and data protection — building dashboards and analysis tools for real medical office workflows.
What You'll Learn
- How to create professional charts and data visualizations
- How to build PivotTables to analyze large datasets
- How to use advanced functions like VLOOKUP, IF, SUMIF, and COUNTIF
- How to protect data and validate entries to prevent errors
Real-World Scenario
Your supervisor at Lakeside Medical Associates wants a monthly dashboard showing expenses by department, a patient appointment analysis by provider, and automated alerts when supply costs exceed budget.
Lessons
Complete each lesson in order. Watch the video, review the notes, and finish the challenge.
Charts and Data Visualization
Learn to create professional charts in Excel 2019 — choosing the right chart type for your data, formatting chart elements, adding sparklines, and presenting visual data that drives decisions at Lakeside Medical Associates.
PivotTables and PivotCharts
Master Excel's most powerful analysis tool — PivotTables — to summarize, group, and explore large datasets interactively, and connect PivotCharts for dynamic visual analysis.
Advanced Functions: VLOOKUP, IF, SUMIF, COUNTIF
Learn Excel's most powerful analytical functions — VLOOKUP for data lookup, nested IF for conditional logic, SUMIF and COUNTIF for conditional aggregation — applied to real medical office scenarios.
Data Validation and Sheet Protection
Learn to prevent data entry errors with Excel's validation tools, restrict editing with sheet and workbook protection, and create secure, professional spreadsheets for shared use at Lakeside Medical Associates.
Hands-On: Build a Monthly Expense Dashboard
Apply all Excel Advanced skills in a single capstone project — build a complete, interactive monthly expense dashboard for Lakeside Medical Associates with PivotTables, charts, SUMIF analysis, data validation, and formula protection.