Becoming a certified Microsoft Office Specialist (MOS) Expert demonstrates your mastery of Microsoft Office products. This course prepares you for the Microsoft Office Specialist Expert certification exam for Microsoft Excel.
Requirements:
Hardware Requirements:
- This course must be taken on a PC. Macs and Chromebooks are not compatible.
Software Requirements:
- PC: Windows 10 or later.
- Browser: The latest version of Google Chrome or Mozilla Firefox are preferred. Microsoft Edge is also compatible.
- Microsoft Office 365, 2021 or 2019 (not included in enrollment). While you can use an older version of Microsoft Office, if you do, there will be some differences between your version and what you see in the course.
- Adobe Acrobat Reader.
- Software must be installed and fully operational before the course begins.
Other:
- Email capabilities and access to a personal email account.
Instructional Material Requirements:
The instructional materials required for this course are included in enrollment.
Get the Excel training you need to achieve success so you can manipulate data faster and more efficiently in most workplace situations. If your organization uses lists of any kind, you need to know how to use Microsoft Excel. Earning the Microsoft Office Specialist Excel Expert certification sets your professional skill set apart from other Excel users. The course will prepare you for the Microsoft Office Specialist: Microsoft Excel Expert exam.
In this online Excel course, you will first learn to create, modify, and format Excel worksheets, perform calculations, and print Excel workbooks. The course then moves on to teach you how to use advanced formulas, work with lists, work with illustrations and charts, and use advanced formatting techniques. You will also learn Excel's advanced features, such as pivot tables, audit worksheets, data tools, macros, and collaboration methods.
Upon completion of this course, you will be prepared for the Microsoft Excel Expert certification exam, Exam MO-201 (for Microsoft Office 2019/2021 users), or Exam MO-211 (for Microsoft Office 365 users.) This course offers enrollment with or without a voucher. The voucher is prepaid access to sit for the certifying exam upon eligibility. Proctor fees may apply, which are not included.
Microsoft Excel Certification Training
Part 1 - Introduction to Microsoft Excel Training
Lesson 1 - Microsoft Office Basics
- Logging in to Microsoft 365
- Installing Applications
- Creating New Files and AutoSaving
- Protected View
- File Sharing
- File Collaboration
- Version History
- Getting Updates
- Mac Versions
Lesson 2 - Creating a Microsoft Excel Workbook
- Starting Microsoft Excel
- Creating a Workbook
- Saving a Workbook
- The Status Bar
- Adding and Deleting Worksheets
- Copying and Moving Worksheets
- View Options for the Worksheet
- Closing a Workbook
Lesson 3 - The Ribbon
- Tabs
- Groups and Commands
- Microsoft Search Box
- Customizing the Ribbon
Lesson 4 - The Backstage View (The File Menu)
- Introduction to the Backstage View
- Opening a Workbook
- New Workbooks and Excel Templates
- Printing Worksheets
- Personalizing Microsoft Office
Lesson 5 - The Quick Access Toolbar
- Getting Started
- Adding Common Commands
- Adding Additional Commands with the Customize Dialog
- Adding Ribbon Commands or Groups
Lesson 6 - Entering Data in Microsoft Excel Worksheets
- Entering Text
- Inserting and Deleting Cells
- Inserting Hyperlinks
- Inserting WordArt
- Using AutoComplete
- Entering Numbers and Dates
- Using the Fill Handle
Lesson 7 - Formatting Microsoft Excel Worksheets
- Selecting Ranges of Cells
- Hiding Worksheets
- Adding Color to Worksheet Tabs
- Adding Themes to Workbooks
- Adding a Watermark
- The Font Group
- The Alignment Group
- The Number Group
Lesson 8 - Editing Worksheets
- Find
- Find and Replace
- Using the Clipboard
- Moving Columns / Rows
Lesson 9 - Working with Rows and Columns
- Inserting and Deleting Rows, Columns, and Cells
- Transposing Rows and Columns
- Row Height and Column Width
- Hiding and Unhiding Rows and Columns
- Freezing Panes
Lesson 10 - Using Formulas in Microsoft Excel
- Math Operators and the Order of Operations
- Entering Formulas
- AutoSum (and Other Common Auto-Formulas)
- Copying Formulas
- Relative, Absolute, and Mixed Cell References
Lesson 11 - Finalizing Microsoft Excel Worksheets
- Setting Margins
- Setting Page Orientation
- Setting the Print Area
- Print Scaling (Fit Sheet on One Page)
- Repeating Headings
- Headers and Footers
Lesson 12 - Using Copilot in Microsoft Excel
- Copilot in Microsoft 365 Excel
- Creating Columns
- Analyzing Your Data
- Importing and Framing Data
Part 2 - Intermediate Microsoft Excel Training
Lesson 1 - Common Functions
- Using Named Ranges in Formulas
- Using Formulas That Span Multiple Worksheets
- Functions: IF and IFS
- Functions: AND and OR
- Functions: NOT
- Functions: SWITCH
- Functions: COUNTIF, AVERAGEIF, and SUMIF
- Functions: PMT and NPER
- Functions: TEXTJOIN and CONCAT
- Text Functions
- Date and Time Functions
Lesson 2 - Working with Lists
- What is a List of Data?
- Removing Duplicates from a List
- Sorting Data in a List
- Filtering Data in a List
- Adding Subtotals to a List
Lesson 3 - Visualizing Your Data
- Chart Basics
- Tools for Editing Charts
- The Format Tool Tab
- Using the Three Chart Buttons
- The Format Task Pane
- Useful Charts
- Line and Area Charts
- Hierarchy Charts
- Statistic Charts
- Other Charts
- Combo Charts
- Using Recommended Charts
- Sparklines
- Add and Format Objects
- Working with Shapes
- Working with Icons
- Working with SmartArt
- Using the Quick Analysis Tool
Lesson 4 - Working with Tables
- Format Data as a Table
- Table Design Tool Tab
- Formatting Individual Cells
- Selecting Table Rows and Columns
- Structured References
- Convert a Table to a Range
Lesson 5 - Advanced Formatting
- Cell Styles
- Conditional Formatting
- Conditional Formatting: Highlight Cells Rules
- Conditional Formatting: Top/Bottom Rules
- Conditional Formatting: Data Bars, Color Scales, and Icon Sets
- Conditional Formatting: Create a New Rule
- Removing Conditional Formatting
Part 3 - Advanced Microsoft Excel Training
Lesson 1 - Using PivotTables
- How PivotTables Work
- Timeline Filters
- Inserting Slicers
- Grouping Data
- Calculated Fields
- PivotCharts
Lesson 2 - Advanced Functions
- Function Syntax
- ROWS, COLUMNS, INDEX, and XMATCH
- Arrays and Array Formulas
- SORT, FILTER, and SORTBY
- Lookup Functions
- The LET Function
- The TRANSPOSE Function
Lesson 3 - Auditing Workbooks
- Inspecting a Workbook
- Tracing Precedents and Dependents
- Watch Window
- Evaluating Formulas
- Error Checking
Lesson 4 - Data Tools
- Importing Data from Online Source
- Converting Text to Columns
- Importing Files
- Linking to External Data
- Controlling Calculation Options
- Data Validation
- Consolidating Data
- What-If Analysis
Lesson 5 - Recording and Using Macros
- Recording Macros
- Running Macros
- Editing Macros
- Adding Macros to the Quick Access Toolbar
Lesson 6 - Working with Others
- Comments and Notes
- Protecting Worksheets and Workbooks
- Marking a Workbook as Final
- Other Sharing Concerns
What you will learn
- Modify and format data in Excel to create clear, presentable worksheets
- Perform calculations, write formulas, manage workbooks, and save time with Excel shortcuts
- Use Excel database functions and logic functions to work with information in large datasets, and leverage Excel's statistical functions to analyze data
- Visualize your data using charts to show trends, create comparisons, and demonstrate other meaningful insights
- Convert, sort, filter, and manage lists to keep your data organized
- Insert and modify illustrations, logos, or shapes to create professional reports
- Emphasize interesting and unusual data with conditional formatting and save time by using styles to apply formatting instantly
- Create PivotTables and charts to quickly summarize large amounts of data
- Learn to trace precedents and dependents to learn about the data connected to your active cell
- Convert blocks of text, analyze data using tables, and leverage validation tools to control the data entered into your worksheet. Consolidate data from various sources into one master worksheet for easy summary and review
- How to protect your worksheets and workbooks for safe and secure collaboration
- Work with other applications by importing and exporting data, charts, and files
- Use AI and Microsoft Copilot in Excel to create columns, analyze data, and import or frame data more efficiently
How you will benefit
- Enhance your resume with widely-recognized and in-demand Microsoft Excel skills, making you more attractive to potential employers
- Leverage Excel's powerful data analysis capabilities to make informed data-driven decisions
- Use Excel to automate repetitive tasks, saving you valuable time and reducing the risk of manual errors
- Generate sophisticated reports and visualizations using Excel to better understand your data and communicate your findings
- Master Excel's tools and shortcuts to boost your efficiency and. you to complete your tasks more quickly
- Improve spreadsheet productivity by using AI and Microsoft Copilot to speed up data analysis, support content creation, and streamline common Excel tasks
Tracy Berry
Tracy Berry has been a senior graphic designer/programmer, instructor, and consultant since 1993 and has developed hundreds of logos, marketing materials, websites, and multimedia solutions for customers worldwide. She was also involved in several large corporate software rollouts. She has helped many organizations optimize and streamline data solutions. She teaches both onsite and online courses and has her CTT (Certified Technical Trainer) certification. Tracy specializes in teaching graphics, desktop publishing, web design, and reporting/productivity applications.