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 COUNTIF, SUMIF, and AVERAGEIF
• 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 IF, SUMIF, and AVERAGEIF
• Use nested IF functions
• Use the SWITCH function
• Use IFS, SUMIFS, and AVERAGEIFS
• Use named ranges in logical functions
• Use cross-worksheet references in logical functions
• Use COUNTIF, COUNTIFS, MAXIFS, and MINIFS

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