Lab Video Highlights

Hands-On Labs

Here is the list of tasks found in the hands-on labs that users can practice in TestOut Pro Certified: Microsoft Excel®

Common Office Features (Chapter 2)

Getting Started with Office

  • Compare and contrast different Microsoft Office versions, including Office 365, Office 2019, and Office online
  • Create a new blank file
  • Save a file to the local computer
  • Save a file remotely with OneDrive
  • Save in alternate file formats
  • Enable a downloaded file for editing
  • Open an existing file
  • Edit file properties
  • Collapse and expand the Ribbon

Customizing Views and Options

  • Change document views
  • Use zoom
  • Customize the Quick Access Toolbar
  • Customize the Ribbon
  • Split the window

Printing Files

  • Configure documents to print
  • Print sections of documents
  • Set print scaling
  • Change page setup options
  • Print individual Excel worksheets
  • Print handouts in PowerPoint
  • Configure PowerPoint to print in grayscale
  • Print speaker notes in PowerPoint

Navigating Files

  • Search for text within a document
  • Insert hyperlinks
  • Create bookmarks
  • Use Go To
  • Find and Replace data

Working With Objects

  • Insert textboxes
  • Insert images
  • Add borders, styles, and effects to objects
  • Change object colors
  • Modify object properties
  • Position objects
  • Modify shape backgrounds
  • Apply borders to shapes
  • Insert shapes
  • Create custom shapes
  • Apply styles to objects
  • Resize objects
  • Display gridlines
  • Draw on a document by using digital ink

Using Office Collaboration Features

  • Enable track changes
  • Accept and reject changes
  • Discard changes from specific users
  • Lock and unlock change tracking
  • Protect documents with passwords
  • Mark a document as final
  • Add and manage comments
  • Restrict permissions
  • Use compare and combine on different documents
  • Inspect a document for sharing
  • Remove personal metadata
  • Inspect a document for accessibility
  • Add alternative text for accessibility
  • Embed custom fonts into a document

Excel Basics (Chapter 3)

Creating and Managing Workbooks

  • Open a workbook
  • Create a new blank workbook
  • Create a new workbook using a template
  • Enable editing to exit the Protected View
  • Insert and delete worksheets
  • Navigate between worksheets
  • Save a workbook in Excel format, in a character-separated values format (both tabs and commas), and as a PDF file
  • Rename worksheets
  • Reorder and color worksheet tabs
  • Move and copy a worksheet
  • Import data from a comma-separated text file
  • Import data from various applications into Excel

Organizing and Entering Data

  • Select a single cell and a range of cells
  • Enter worksheet titles
  • Enter column and row titles
  • Freeze column and row titles and freeze panes.
  • Enter text and numbers into cells
  • Use the Copy and Cut commands on cell ranges
  • Use paste options
  • Insert rows and columns into a sheet
  • Insert multiple rows in one operation
  • Delete and clear rows and columns
  • Hide and unhide rows and columns
  • Hide and unhide worksheets
  • Undo and redo actions
  • Manage a worksheet that contains a large data set
  • Insert new data between rows or columns
  • Divide data sets appropriately between worksheets

Changing Properties and Printing Worksheets

  • Lock and unlock cells
  • Protect worksheets and workbooks from changes
  • Edit workbook properties
  • Preview and print a worksheet
  • Set and clear the print area for a worksheet
  • Change worksheet margins
  • Create and modify a worksheet header
  • Create a worksheet footer
  • Change worksheet orientation for printing
  • Scale a worksheet to be printed on a single page
  • Add print titles
  • Prevent particular cells from being modified
  • Print a particular section of worksheet data
  • Add data validation to a worksheet
  • Limit cell input to a list
  • Add a custom input message to a cell
  • Customize the error alert for invalid data
  • Include relevant worksheet properties in a printout
  • Optimize the printing of a worksheet

Formatting Cells

  • Apply font style, size, and color changes to cells
  • Apply cell styles
  • Change the fill color (shading)
  • Merge and center a range of cells
  • Apply number formatting to cells
  • Apply the percent style to cells
  • Increase and decrease decimal spaces
  • Enter and format dates
  • Adjust column widths (including AutoFit)
  • Resolve the ###### error message
  • Adjust row heights
  • Align cell content
  • Rotate cell content
  • Clear cell formatting
  • Apply borders and border colors
  • Enter multiple lines of text (apply Wrap text setting)
  • Format cells as column or row totals
  • Use Format Painter
  • Adjust columns to display both numeric and text data properly
  • Format a range of cells as a worksheet title

Formulas and Functions (Chapter 4)

Entering Simple Formulas

  • Enter a formula using the keyboard
  • Use the mouse to reference cells in a formula
  • Copy a formula using the fill handle
  • Copy a formula using the Clipboard
  • Sum a column or row using AutoSum
  • Calculate the MAX, MIN, AVERAGE, and MEDIAN of a data set
  • Use the RIGHT, LEFT, and MID functions
  • Use the CONCAT and TEXTJOIN functions
  • Use the UPPER, LOWER, and LEN functions
  • Use the COUNTA and COUNTBLANK functions
  • Use arithmetic operations properly in formulas
  • Add columns and rows of data
  • Copy formulas to cells that require similar calculations
  • Perform simple arithmetic that references cell values
  • Use AutoFill to enter data that follow a predictable pattern
  • Use Flash Fill

Using Advanced Functions

  • Enter a function using the Insert Function box
  • Display and hide formulas
  • Enter a formula using absolute references
  • Enter a formula using relative references
  • Enter a formula using mixed references
  • Use the IF function
  • Use functions with multiple arguments
  • Copy a formula with absolute, relative, or mixed references
  • Reference cells on other worksheets
  • Correct or ignore error messages, as appropriate
  • Select appropriate functions to perform conditional operations
  • Determine when to use an absolute reference in a formula
  • Verify that the desired values have been properly referenced within a formula
  • Reference named ranges and named tables in formulas

Simple Data Analysis (Chapter 5)

Displaying Data in Charts

  • Select a data source for a chart
  • Add a data series to an existing chart
  • Insert a clustered column chart
  • Create a pie chart
  • Move a chart on a worksheet
  • Move a chart to its own worksheet
  • Resize a chart
  • Modify a chart style and type
  • Format chart elements
  • Show and hide chart elements
  • Use recommended charts
  • Insert a chart to show changes over time
  • Insert a chart to display the aggregate of a set of values
  • Modify chart layouts to better visualize data

Organizing Data in Tables

  • Create a table
  • Apply table styles
  • Insert table rows and columns
  • Add a total row
  • Sort a table
  • Remove duplicate rows
  • Filter a table using AutoFilter options
  • Apply highlight cell rules for conditional formatting
  • Apply data bars for conditional formatting
  • Analyze data using sparklines
  • Determine when a data set should be converted to a table
  • Sort rows of data based on the values in specified columns
  • Use both text and number filters to display only desired information
  • Conditionally format cells that contain the most important data

Using Office Collaboration Features

  • Enable Track Changes
  • Accept and reject changes
  • Discard changes from specific users
  • Lock and unlock change tracking
  • Protect documents with passwords
  • Mark a document as final
  • Add and manage comments
  • Restrict permissions
  • Use Compare and Combine on different documents
  • Inspect a document for sharing
  • Remove personal metadata
  • Inspect a document for accessibility
  • Add alternative text for accessibility
  • Embed custom fonts into a document

Using Themes, Styles and Templates

  • Create a document from an existing template
  • Remove content controls in a template
  • Modify the theme font
  • Apply heading styles to paragraphs
  • Modify text formatting using Format Painter
  • Change the theme for a document
  • Select a style set for a document
  • Edit an existing style
  • Format a newsletter to achieve both readability and visual appeal
  • Apply font and paragraph styles to an academic paper
  • Create a certificate using a template

Advanced Workbook Options and Settings (Chapter 7)

Managing Workbooks

  • Configure AutoSave settings
  • Change AutoRecover settings, including the location for AutoRecover files
  • Add a language for authoring and proofing
  • Enter a formula using relative references
  • Enter a formula using absolute references
  • Enter a formula using mixed references
  • Enter a formula using a reference to another worksheet
  • Reference named ranges in formulas

Preparing Workbooks for Collaboration

  • Protect and unprotect a worksheet
  • Protect a worksheet with a password
  • Protect the structure of a workbook
  • Lock and unlock cells in a protected worksheet
  • Hide formulas in a range of cells
  • Create and modify edit ranges
  • Modify formula calculation options in a workbook
  • Add, edit, and delete comments in a workbook

Advanced Data Formatting (Chapter 8)

Filling Cells Based on Existing Data

  • Use the fill handle to copy formulas to a range of cells
  • Use Flash Fill to enter names and other information that follow a consistent pattern
  • Use AutoFill options to fill cells with and without formatting
  • Use Fill Series options to forecast data using a linear growth rate
  • Use Fill Series options to stop a series before it reaches a certain value
  • Use Fill Series options for dates

Formatting and Validating Data

  • Define custom number formats
  • Define data validation rules to allow certain data types and data ranges
  • Define data validation rules to display input, warning, and error messages
  • Clear data validation rules
  • Apply and remove subtotals that automatically calculate data
  • Use outline level symbols to expand and collapse data groups
  • Remove duplicate rows of data on a worksheet

Advanced Conditional Formatting and Filtering

  • Apply built-in conditional formatting rules
  • Modify and manage existing conditional formatting rules
  • Create custom conditional formatting rules
  • Use conditional formatting rules with formulas to highlight specific cells or rows
  • Use conditional formatting rules with formulas to apply font formatting to cells or rows
  • Use nested functions in conditional formatting formulas

Advanced Formulas and Macros (Chapter 9)

Performing Logical Operations in Formulas

  • Use nested IF functions
  • Use the SWITCH function
  • Use named ranges in logical functions
  • Use cross-worksheet references in logical functions

Looking Up Data by Using Functions

  • Use the VLOOKUP function to look up information in a table and return a value in the same row
  • Use the HLOOKUP function to look up information in a table and return a value in the same column
  • Use nested VLOOKUP and HLOOKUP functions
  • Use the MATCH and INDEX functions when a lookup value is not in the leftmost column or top row of a table

Using Advanced Date and Time Functions

  • Use the TODAY function to add today's date to a cell
  • Use the NOW function to add today's date and time to a cell
  • Use a formula to add a date or time relative to today's date and time
  • Use a custom date format
  • Use the WEEKDAY function to find the day of the week for a date in a specified cell
  • Use the WORKDAY function to calculate starting and end dates for business projects
  • Create a named range for holidays that can be factored into WORKDAY calculations

Performing Data Analysis

  • Summarize data from multiple ranges using the Consolidate feature
  • Use the PMT function to calculate monthly payments
  • Use the NPER function to determine the number of months to pay off a loan
  • Use the NPER function to estimate the number of months to achieve an investment goal
  • Use Goal Seek to analyze business scenarios with a single variable
  • Use Scenario Manager to analyze business scenarios with multiple variables

Troubleshooting Formulas

  • Trace precedence and dependence in complex formulas
  • Monitor cell values using the Watch Window
  • Use the Evaluate Formula tool to troubleshoot errors in formulas
  • Use error messages to fix formula problems

Creating and Modifying Simple Macros

  • Record and name a simple macro
  • Assign a shortcut key to a macro
  • Run an existing macro
  • Enable macros in a workbook
  • Disable all macros with and without notification
  • Customize the ribbon to include the Developer tab

Advanced Charts and Tables (Chapter 10)

Creating and Modifying Advanced Charts

  • Create and modify common charts such as clustered column, bar, line, and pie charts
  • Apply styles and colors to charts
  • Add or remove data from a chart
  • Modify data labels on a chart
  • Modify the format of horizontal axis labels
  • Create and modify advanced charts such as histograms and funnel charts
  • Use the Recommended Charts feature to select appropriate chart types
  • Create a combo chart with a secondary axis

Creating and Modifying PivotTables

  • Create PivotTables
  • Add row, column, and filter fields to PivotTables
  • Create calculated fields in PivotTables
  • Change the number format of PivotTable values
  • Add slicers to PivotTables
  • Group PivotTable data in increments

Creating and Modifying PivotCharts

  • Create both a PivotChart and a PivotTable from raw data
  • Create a PivotChart from an existing PivotTable
  • Move a PivotChart to a new worksheet
  • Apply styles and colors to PivotCharts
  • Switch PivotChart rows and columns
  • Modify PivotChart data labels
  • Drill down into PivotChart details
  • Add PivotChart slicers

Technology Demos

Within TestOut Pro Certified: Microsoft Word® we use video lessons to introduce many features and explain why we need them. We also provide demo videos that show how to perform required tasks in our skill labs