Get your MOS Excel 2016 Certification!

The Excel Microsoft Office Specialist (MOS) certification is for those looking to demonstrate proficiency in Excel. There are 10 sections, each with useful resources to help you prepare for the MOS Excel exam and downloadable exercises to help you apply your learning.

You can also test your skills by completing all the tasks in the challenges below. Each challenge comes with a set of files to download and an answer key to check your answers.

Excel

LEARN THE CONTENT

CHALLENGES

Get Started with Excel

IN THIS SECTION

  • What is Excel? (Video Duration: 1:05)
  • Create a Workbook (Video Duration: 0:33)
  • Add, delete or rename worksheets (Video Duration: 0:34)

Watch Videos

What is Excel?

Video Duration: 1:05 | Topics: Spotting trends · Charts and pivot table recommendations · Saving and sharing | View MS Support Page

Create a Workbook

Video Duration: 0:33 | Topics: Create a blank workbook · Create a workbook from a template | View MS Support Page

Add, delete or rename worksheets

Video Duration: 0:34 | Topics: Insert a worksheet · Rename a worksheet · Delete a worksheet | View MS Support Page

Design Workbooks

IN THIS SECTION

  • Hide or unhide columns (Video Duration: 0:37)
  • Insert and remove hyperlinks (Video Duration: 6:08)
  • Freeze top and left panes (Video Duration: 1:27)
  • Move or copy worksheets (Video Duration: 0:51)
  • Apply themes to change the look of a workbook (Video Duration: 6:54)
  • Change worksheet tab color (Video Duration: :24)
  • Modify page setup (Video Duration: 0:41)
  • Insert headers and footers (Video Duration: 8:00)
  • Customize the Quick Access toolbar (Video Duration: 1:41)
  • Change workbook views (Video Duration: 0:50)
  • Change window views (Video Duration: 3:03)
  • Modify document properties (Video Duration: 1:33)
  • Change magnification by using zoom tools (Video Duration: 1:35)
  • Display formulas (Video Duration: 1:08)
  • Set a print area (Video Duration: 2:19)
  • Save workbooks in alternative file formats (Video Duration: 1:13)
  • Print all or part of a workbook (Video Duration: 3:46)
  • Set print scaling (Video Duration: 2:27)
  • Display repeating row and column titles on multipage worksheets (Video Duration: 3:14)
  • Inspect a workbook for hidden properties or personal information (Video Duration: 2:31)
  • Inspect a workbook for accessibility issues (Video Duration: 5:10)
  • Inspect a workbook for compatibility issues (Video Duration: 2:50)

Watch Videos

Hide or unhide columns

Video Duration: 0:37 | Topics: Selecting one or more columns and hiding them · Unhiding columns | View MS Support Page

Insert and remove hyperlinks

Video Duration: 6:08 | Topics: Create hyperlinks · Change or modify hyperlinks · Delete hyperlinks | View MS Support Page

Freeze top and left panes

Video Duration: 1:27 | Topics: Freeze the first column · Freeze the first two columns · Freeze columns and rows | View MS Support Page

Move or copy worksheets

Video Duration: 0:51 | Topics: Move a worksheet within a workbook · Copy a worksheet in the same workbook | View MS Support Page

Apply themes to change the look of a workbook

Video Duration: 6:54 | Topics: Customizing with a pre-defined theme to make data stand out. | View MS Support Page

Change worksheet tab color

Video Duration: :24 | Topics: Change the text color for a cell or range of cells · Format the color of a worksheet tab | View MS Support Page

Modify page setup

Video Duration: 0:41 | Topics: Page setup and layout · Page tab, margin tab, header/footer tab, sheet options | View MS Support Page

Insert headers and footers

Video Duration: 8:00 | Topics: Insert headers/footers | View MS Support Page

Customize the Quick Access toolbar

Video Duration: 1:41 | Topics: Add/remove commands · Change the order of the commands · Group the commands · Move the quick access toolbar | View MS Support Page

Change workbook views

Video Duration: 0:50 | Topics: Switch to full or normal screen view in excel | View MS Support Page

Change window views

Video Duration: 3:03 | Topics: Create, apply or delete a customer view | View MS Support Page

Modify document properties

Video Duration: 1:33 | Topics: View or change the properties of a document | View MS Support Page

Change magnification by using zoom tools

Video Duration: 1:35 | Topics: Zoom in/out of a worksheet | View MS Support Page

Display formulas

Video Duration: 1:08 | Topics: Display or hide formulas · Switch between displaying formulas or hide them | View MS Support Page

Set a print area

Video Duration: 2:19 | Topics: Set or clear a print area on a worksheet · Set one or more print areas · Clear print area | View MS Support Page

Save workbooks in alternative file formats

Video Duration: 1:13 | Topics: Save a workbook in another file format | View MS Support Page

Print all or part of a workbook

Video Duration: 3:46 | Topics: Print one or several worksheets or workbooks · Print all or part of a worksheet · Print an excel table · Print a workbook to a file | View MS Support Page

Set print scaling

Video Duration: 2:27 | Topics: Scale a worksheet · Scale a worksheet to print in a different font size | View MS Support Page

Display repeating row and column titles on multipage worksheets

Video Duration: 3:14 | Topics: Print rows with column headers on top of every page | View MS Support Page

Inspect a workbook for hidden properties or personal information

Video Duration: 2:31 | Topics: Remove hidden data and personal information in office documents | View MS Support Page

Inspect a workbook for accessibility issues

Video Duration: 5:10 | Topics: Use the accessibility checker to find accessibility issues | View MS Support Page

Inspect a workbook for compatibility issues

Video Duration: 2:50 | Topics: Issues that cause a significant loss of functionality · Issues that cause a minor loss of fidelity | View MS Support Page

CHALLENGE 1

Apply your Excel skills by completing the exercises below. Download the practice files and complete the Excel tasks listed. When you’ve completed the tasks, or you find yourself getting stuck on a task, download and view the answer key. The answer key will link the tasks to related videos.

This challenge covers skills from the following sections:
  • Get Started With Excel
  • Design Workbooks

View & Download Practice Files

Challenge 1

Test your skills by completing all the tasks found in the Challenge 1 – Excel 2016 – Task List. Click here to view and download the exercise files. The following files should be downloaded:
  • Challenge 1 – Excel 2016 (.xlsx file)
  • Challenge 1 – Excel 2016 – Task List (.txt file)

Need help completing the tasks, or want to compare your Excel file to our solution? You can view and download our completed Excel here: Challenge 1 Answer Key (.xlsx file). You can also check out the answer key video below:

Click on the    in the top right corner of the video to view larger.

Add Text and Data

IN THIS SECTION

  • Add and edit data (Video Duration: 4:08)
  • Import/export data (Video Duration: 3:43)
  • Search for data (Video Duration: 1:54)
  • Paste data by using special paste options (Video Duration: 3:48)
  • Align text in a cell (Video Duration: 0:40)
  • Merge cells (Video Duration: 0:42)
  • Format numbers in cells (Video Duration: 0:27)
  • Wrap text within cells (Video Duration: 0:40)
  • Change the look of cells (Video Duration: 1:01)
  • Copy cell formatting (Video Duration: 1:01)
  • Conditional formatting (Video Duration: 0:58)
  • Select cell contents (Video Duration: 0:57)
  • Move or copy cell contents (Video Duration: 0:57)
  • Insert or delete rows or columns (Video Duration: 1:00)
  • Resize rows and columns (Video Duration: 0:40)
  • AutoFill time spans (Video Duration: 0:53)
  • Split data (Video Duration: 0:51)
  • Combine data (Video Duration: 1:44)
  • Build custom numeric formats (Video Duration: 0:57)
  • Validate cell data (Video Duration: 1:18)
  • Insert subtotals (Video Duration: 1:23)
  • Remove duplicate records (Video Duration: 1:52)
  • Create reusable lists (Video Duration: 1:13)

Watch Videos

Add and edit data

Video Duration: 4:08 | Topics: Add data · Edit data · Change the active cell | View MS Support Page

Import/export data

Video Duration: 3:43 | Topics: Import/export data from a delimited text file | View MS Support Page

Search for data

Video Duration: 1:54 | Topics: Search for data within a workbook | View MS Support Page

Paste data by using special paste options

Video Duration: 3:48 | Topics: Using paste special when copying from excel · applying mathematical operation to copied data | View MS Support Page

Align text in a cell

Video Duration: 0:40 | Topics: Align a column · Align a row · Align all cells in a workbook | View MS Support Page

Merge cells

Video Duration: 0:42 | Topics: Merge and unmerge cells | View MS Support Page

Format numbers in cells

Video Duration: 0:27 | Topics: Select cells and select format · Find additional format options | View MS Support Page

Wrap text within cells

Video Duration: 0:40 | Topics: Wrap text automatically · Enter a line break | View MS Support Page

Change the look of cells

Video Duration: 1:01 | Topics: Choose cells, select and apply a style · Modify an applied style in cells | View MS Support Page

Copy cell formatting

Video Duration: 1:01 | Topics: Copy cell formatting using copy/paste options, format painter · Dragging techniques · Copy number formatting using the same techniques | View MS Support Page

Conditional formatting

Video Duration: 0:58 | Topics: Create visual cues using conditional formatting for selected data | View MS Support Page

Select cell contents

Video Duration: 0:57 | Topics: Select a cell · Select a cell range · Select non-contiguous cells or ranges · Select a row, column or worksheet · Select non-adjacent columns or rows · Other ways to select cell contents | View MS Support Page

Move or copy cell contents

Video Duration: 0:57 | Topics: Move cells by drag and dropping · Move cells by using cut and paste | View MS Support Page

Insert or delete rows or columns

Video Duration: 1:00 | Topics: Insert a column · Delete a column · Insert a row · Delete a row · Insert a cell, shift cells, insert entire row or entire column | View MS Support Page

Resize rows and columns

Video Duration: 0:40 | Topics: Resize row width · Resize column width · Automatically resize all columns and rows to fit the data | View MS Support Page

AutoFill time spans

Video Duration: 0:53 | Topics: Using the auto fill feature to fill cells with data that follow a pattern or series | View MS Support Page

Split data

Video Duration: 0:51 | Topics: Using the convert text to columns wizard to split data | View MS Support Page

Combine data

Video Duration: 1:44 | Topics: Combine data with the ampersand symbol (&) · Use the concat function to combine data | View MS Support Page

Build custom numeric formats

Video Duration: 0:57 | Topics: Create and build custom numeric format to show numbers as percentages, currency, or dates | View MS Support Page

Validate cell data

Video Duration: 1:18 | Topics: Use data validation to make rules to restrict the type of data or values others enter into a cell | View MS Support Page

Insert subtotals

Video Duration: 1:23 | Topics: Insert subtotals | View MS Support Page

Remove duplicate records

Video Duration: 1:52 | Topics: Remove duplicate records | View MS Support Page

Create reusable lists

Video Duration: 1:13 | Topics: Create and use a custom list · Sort using a custom list | View MS Support Page

CHALLENGE 2

Apply your Excel skills by completing the exercises below. Download the practice files and complete the Excel tasks listed. When you’ve completed the tasks, or you find yourself getting stuck on a task, download and view the answer key. The answer key will link the tasks to related videos.

This challenge covers skills from the following sections:
  • Add Text and Data

View & Download Practice Files

Challenge 2

Test your skills by completing all the tasks found in the Challenge 2 – Excel 2016 – Task List. Click here to view and download the exercise files. The following files should be downloaded:
  • Challenge 2 – Excel 2016 (.xlsx file)
  • Challenge 2 – Excel 2016 – Task List (.txt file)
  • Challenge 2 Data (.txt file)

Need help completing the tasks, or want to compare your Excel file to our solution? You can view and download our completed Excel here: Challenge 2 Answer Key (.xlsx file). You can also check out the answer key video below:

Click on the    in the top right corner of the video to view larger.

Use Formulas and Functions

IN THIS SECTION

  • Create formulas (Video Duration: 1:39)
  • Display formulas (Video Duration: 1:43)
  • Name cells and ranges (Video Duration: 1:46)
  • Use Trace to fix formula errors (Video Duration: 2:28)
  • Locate functions (Video Duration: 5:15)
  • Calculations using functions (Video Duration: 1:39)
  • Perform calculations by using the COUNT function (Video Duration: 1:28)
  • Perform logical operations by using the IF function (Video Duration: 0:57)
  • Perform logical operations by using the SUMIF function (Video Duration: 5:37)
  • Perform statistical operations by using the COUNTIF function (Video Duration: 3:51)

Watch Videos

Create formulas

Video Duration: 1:39 | Topics: What is the formula bar · Create a formula that refers to values in other cells · How to see a formula · Enter a formula that contains a built-in function · Relative references | View MS Support Page

Display formulas

Video Duration: 1:43 | Topics: Display or hide formulas · Switch between displaying formulas or hide them | View MS Support Page

Name cells and ranges

Video Duration: 1:46 | Topics: Naming a cell · Defining names from a selected range · Using named cells in formulas | View MS Support Page

Use Trace to fix formula errors

Video Duration: 2:28 | Topics: Defining trace dependent cells and trace precedent cells · Using trace dependent cells to fix a formula · Using trace precedent cells to fix a formula | View MS Support Page

Locate functions

Video Duration: 5:15 | Topics: Find functions and categories of functions · Using the function library group · Find more information about functions | View MS Support Page

Calculations using functions

Video Duration: 1:39 | Topics: Using functions and specifying data in the function arguments box · Using insert function | View MS Support Page

Perform calculations by using the COUNT function

Video Duration: 1:28 | Topics: Perform calculations | View MS Support Page

Perform logical operations by using the IF function

Video Duration: 0:57 | Topics: Perform calculations | View MS Support Page

Perform logical operations by using the SUMIF function

Video Duration: 5:37 | Topics: Perform calculations | View MS Support Page

Perform statistical operations by using the COUNTIF function

Video Duration: 3:51 | Topics: Perform calculations | View MS Support Page

CHALLENGE 3

Apply your Excel skills by completing the exercises below. Download the practice files and complete the Excel tasks listed. When you’ve completed the tasks, or you find yourself getting stuck on a task, download and view the answer key. The answer key will link the tasks to related videos.

This challenge covers skills from the following sections:
  • Use Formulas and Functions

View & Download Practice Files

Add and Format Tables

IN THIS SECTION

  • Create and format tables (Video Duration: 0:30)
  • Sort data in a table (Video Duration: 1:02)
  • Filter data in a table (Video Duration: 1:03)
  • Add a Total row to a table (Video Duration: 0:34)
  • Filter data with slicers (Video Duration: 0:36)

Watch Videos

Create and format tables

Video Duration: 0:30 | Topics: Format your data as a table | View MS Support Page

Sort data in a table

Video Duration: 1:02 | Topics: Getting ready to sort a table · Sort the data in a range or table · Custom sorting | View MS Support Page

Filter data in a table

Video Duration: 1:03 | Topics: Filter a range of data · Filter data in a table | View MS Support Page

Add a Total row to a table

Video Duration: 0:34 | Topics: Add a total row to a table from the design tab | View MS Support Page

Filter data with slicers

Video Duration: 0:36 | Topics: Convert data into a table · Use a slicer to filter the data | View MS Support Page

Add and Format Charts

IN THIS SECTION

  • Create charts (Video Duration: 0:45)
  • Format Charts (Video Duration: 4:30)
  • Add sparkline charts (Video Duration: 0:39)
  • Add trendlines and drop lines (Video Duration: 3:01)

Watch Videos

Create charts

Video Duration: 0:45 | Topics: Create a chart using recommended charts · Add a trendline | View MS Support Page

Format Charts

Video Duration: 4:30 | Topics: Create a chart · Add and format a chart title · Change the chart layout · Change the chart style · Select a theme style to add color | View MS Support Page

Add sparkline charts

Video Duration: 0:39 | Topics: Add a sparkline to show data trends · Format a sparkline chart | View MS Support Page

Add trendlines and drop lines

Video Duration: 3:01 | Topics: Add and format a trendline · Select a value with forward or backward fields to project your data · Add drop lines for better readability | View MS Support Page

Analyze Chart Data

IN THIS SECTION

  • Quick analysis of data (Video Duration: 0:37)

Watch Videos

Quick analysis of data

Video Duration: 0:37 | Topics: Use the quick analysis feature · Add a chart | View MS Support Page

CHALLENGE 4

Apply your Excel skills by completing the exercises below. Download the practice files and complete the Excel tasks listed. When you’ve completed the tasks, or you find yourself getting stuck on a task, download and view the answer key. The answer key will link the tasks to related videos.

This challenge covers skills from the following sections:
  • Add and Format Tables
  • Add and Format Charts
  • Analyze Chart Data

View & Download Practice Files

Challenge 4

Test your skills by completing all the tasks found in the Challenge 4 – Excel 2016 – Task List. Click here to view and download the exercise files. The following files should be downloaded:
  • Challenge 4 – Excel 2016 (.xlsx file)
  • Challenge 4 – Excel 2016 – Task List (.txt file)

Need help completing the tasks, or want to compare your Excel file to our solution? You can view and download our completed Excel here: Challenge 4 Answer Key (.xlsx file). You can also check out the answer key video below:

Click on the    in the top right corner of the video to view larger.

Create and Format Pivot Tables and Charts

IN THIS SECTION

  • Create Pivot Tables (Video Duration: 0:45)
  • Work with Pivot Tables (Video Duration: 1:30)
  • Group data in Pivot Tables (Video Duration: 1:37)
  • Filter data with Slicers in Pivot Tables (Video Duration: 0:36)
  • Create Pivot Charts (Video Duration: 1:04)

Watch Videos

Create Pivot Tables

Video Duration: 0:45 | Topics: Create and insert a pivot table for a table or range · Build out your pivot table using pivot tables fields pane · Move selected fields in the pane to design your pivot table | View MS Support Page

Work with Pivot Tables

Video Duration: 1:30 | Topics: Arrange the data in your pivot table with pivot table fields pane · Filters, columns, rows and values | View MS Support Page

Group data in Pivot Tables

Video Duration: 1:37 | Topics: Group data · Group selected items · Name a group · Ungroup grouped data | View MS Support Page

Filter data with Slicers in Pivot Tables

Video Duration: 0:36 | Topics: Filter data in a pivot table using slicer · Filter data manually | View MS Support Page

Create Pivot Charts

Video Duration: 1:04 | Topics: Create a pivot chart · Create a chart from a pivot table | View MS Support Page

CHALLENGE 5

Apply your Excel skills by completing the exercises below. Download the practice files and complete the Excel tasks listed. When you’ve completed the tasks, or you find yourself getting stuck on a task, download and view the answer key. The answer key will link the tasks to related videos.

This challenge covers skills from the following sections:
  • Create and Format Pivot Tables and Charts

View & Download Practice Files

Collaborate With Others

IN THIS SECTION

  • Share workbooks (Video Duration: 0:41)
  • Add and review comments (Video Duration: 1:30)

Watch Videos

Share workbooks

Video Duration: 0:41 | Topics: Share a workbook and apply permissions | View MS Support Page

Add and review comments

Video Duration: 1:30 | Topics: Add a comment · Review comments · See all comments at once, show or hide comments · Move a comment | View MS Support Page

Password-Protect Workbooks

IN THIS SECTION

  • Password-protect workbooks (Video Duration: 0:45)
  • Add a watermark (Video Duration: 1:17)

Watch Videos

Password-protect workbooks

Video Duration: 0:45 | Topics: Require a password to open an excel file · Protect workbook with encrypted password | View MS Support Page

Add a watermark

Video Duration: 1:17 | Topics: Use an image or logo to create a watermark with header & footer function | View MS Support Page

CHALLENGE 6

Apply your Excel skills by completing the exercises below. Download the practice files and complete the Excel tasks listed. When you’ve completed the tasks, or you find yourself getting stuck on a task, download and view the answer key. The answer key will link the tasks to related videos.

This challenge covers skills from the following sections:
  • Collaborate with Others
  • Password-Protect Workbooks

View & Download Practice Files

Challenge 6

Test your skills by completing all the tasks found in the Challenge 6 – Excel 2016 – Task List. Click here to view and download the exercise files. The following files should be downloaded:
  • Challenge 6 – Excel 2016 (.xlsx file)
  • Challenge 6 – Excel 2016 – Task List (.txt file)
  • Excel Icon (.jpg file)

Need help completing the tasks, or want to compare your Excel file to our solution? You can view and download our completed Excel here: Challenge 6 Answer Key (.xlsx file). You can also check out the answer key video below:

Click on the    in the top right corner of the video to view larger.