In this course, you’ll build your Excel skills from the ground up and grow into advanced techniques for data analysis, visualization, and automation. You’ll learn how to enter and format data, create formulas, and build charts and tables. As you progress, you’ll explore tools like PivotTables, SmartArt, Goal Seek, and Solver to evaluate complex data. You’ll import data from external sources, apply conditional formatting, and use lookup functions to streamline your workflow. By the end of the course, you’ll be able to customize Excel’s interface, record macros, use Power Query and Power Pivot, and create professional, interactive workbooks for a variety of personal and professional tasks. 

Students will:

• Navigate the Excel interface, including the ribbon, worksheet views, and help tools.
• Enter, format, and organize data using cell styles, themes, and conditional formatting.
• Create and modify formulas and functions, including statistical, logical, and financial functions.
• Develop and format a variety of charts and sparklines to visually represent data.
• Manage data across multiple worksheets and workbooks using 3-D references and consolidation tools.
• Create and sort tables, apply filters, and use lookup and database functions for data analysis.
• Import and transform external data sources, and enhance worksheets with SmartArt, images, and hyperlinks.
• Build and customize PivotTables and PivotCharts, including slicers, trendlines, and calculated fields.
• Apply data validation, formula auditing, and advanced analysis tools like Solver, Goal Seek, and Scenario Manager.
• Automate tasks using macros, explore Power Query and Power Pivot, and customize Excel through VBA and collaboration features.

This course prepares students to take the Microsoft Office Suite (MOS) Excel 2019 national certification exam.

Course Outline:

Lesson 1: Creating a Worksheet and a Chart

In this lesson, you’ll get started with Excel by learning how to navigate the ribbon, enter and format data, and use basic functions. You’ll create a pie chart, apply cell styles, rename worksheets, preview and print your work, and access built-in help tools to support your learning.

Lesson 2: Formulas, Functions, and Formatting

In this lesson, you’ll build on your Excel skills by creating formulas with Flash Fill, keyboard input, and Point mode. You’ll use functions like MAX, MIN, and AVERAGE, apply themes and date formats, adjust layout settings, and add conditional formatting to create polished, functional spreadsheets.

Lesson 3: Formulas, Functions, and Formatting

In this lesson, you’ll work with advanced Excel tools to analyze and present data. You’ll apply formulas with absolute and mixed references, use IF functions, create sparkline and column charts, and answer what-if questions with Goal Seek. You’ll also explore formatting, chart customization, and accessibility features.

Lesson 4: Financial Functions, Data Tables, and Amortization Schedules

In this lesson, you’ll apply financial functions like PMT, PV, and FV to perform loan calculations and data analysis. You’ll name cells, build data tables, create an amortization schedule, and format borders. You’ll also protect worksheet data, adjust print settings, and manage worksheet visibility.

Lesson 5: Working with Multiple Worksheets and Workbooks

In this lesson, you’ll consolidate and manage data across multiple worksheets and workbooks. You’ll use date, time, and rounding functions, apply custom formats, and create formulas with 3-D references. You’ll also format 3-D pie charts, use the Paste gallery, and save or view worksheets as separate files.

Lesson 6: Creating, Sorting, and Querying a Table

In this lesson, you’ll work with Excel tables to organize and analyze data. You’ll use lookup functions like VLOOKUP and XLOOKUP, apply conditional formatting, sort and filter data, and calculate totals. You’ll also explore database and statistical functions, outline tools, and create a treemap chart for visual analysis.

Lesson 7: Creating Templates, Importing Data, and Working with SmartArt, Images, and Screenshots

In this lesson, you’ll import data from various sources and transform it using Excel tools like text functions, Paste options, and Convert Text to Columns. You’ll also enhance your worksheets with shapes, SmartArt, bar charts, and images, while exploring linking, embedding, alt text, and hyperlinked screenshots.

Lesson 8: Working with Trendlines, PivotTables, PivotCharts, and Slicers

In this lesson, you’ll analyze data using PivotTables and PivotCharts. You’ll apply filters, summary functions, calculated fields, and slicers to customize your reports. You’ll also explore trendlines, drill-down features, and statistical charts like Box and Whisker to deepen your data insights and presentation skills.

Lesson 9: Formula Auditing, Data Validation, and Complex Problem Solving

In this lesson, you’ll strengthen your problem-solving skills in Excel using formula auditing tools, error checking, and data validation. You’ll apply Goal Seek, Solver, and Scenario Manager to test assumptions and solve complex problems, then summarize results with Scenario reports and PivotTables for deeper analysis.

Lesson 10: Data Analysis with Power Tools and Creating Macros

In this lesson, you’ll explore Excel’s power tools for advanced data analysis. You’ll use Power Query to transform data, build PivotTables with Power Pivot, and create measures and cube functions. You’ll also work with 3D Maps, record and run macros, and customize the ribbon for streamlined access.

Lesson 11: User Interfaces, Visual Basic for Applications (VBA), and Collaboration Features

In this lesson, you’ll personalize Excel with custom themes, cell styles, and worksheet backgrounds. You’ll use form controls to capture input, explore VBA and event-driven programming, and manage workbook protection and collaboration. You’ll also enhance charts, add comments, apply digital signatures, and save custom worksheet views.

All necessary course materials are included.

Certification(s):

This course prepares students to take the Microsoft Office Suite (MOS) Excel 2019 national certification exam.  At this time, there is not a scheduled release date for Microsoft Office 2021 national certification exams.