Working with large datasets and tables 

• Using the AutoFilter feature to quickly find and isolate specific data points
  
• Advanced sorting techniques, including custom sort orders
  
• Using the Subtotal feature to quickly add subtotals and grand totals to data
  
• Creating tables and formatting table elements, including total rows and header rows
  
• Using table features to filter, sort, and subtotal data within a table
  
• Converting a table back to a range and preserving formatting
  
• Using the database functions, such as DSUM and DCOUNT, to summarize data
  
• Using the Power Query feature to import, transform, and merge data from multiple sources

Creating Pivot Tables and pivot charts to summarize and analyses data

• Steps for creating a Pivot Table Reports
  
• Adding fields to a Pivot Table and changing the layout of a Pivot Table
  
• Formatting Pivot Table elements, including row and column headings and cell data
  
• Grouping data in a Pivot Table by date, text, or numeric ranges
  
• Using the Show Values As feature to show data in different ways, such as as a percentage or running total
  
• Creating pivot charts to visualize data in a Pivot Table
  
• Refreshing Pivot Tables to update data and formatting
  
• Using the Slicer feature to filter data in multiple Pivot Tables and pivot charts at the same time

Using conditional formatting to highlight important data

• How to apply a basic formatting rule to a range of cell
  
• Basic formatting rules, such as formatting cells that are above or below a certain value
  
• Advanced formatting rules, such as formatting cells based on the value of another cell or formatting cells that contain specific text
  How to use data bars, color scales, and icon sets in conditional formatting
  
• How to edit and delete basic formatting rules

Using data validation to ensure accurate data entry

• Common uses for data validation
  
• Set up a data validation rule
  
• Perform different types of data validation rules, such as limiting input to a certain number range or list of values
  
• How to customize error messages and input prompts
  
• How to use formulas in data validation rules
  
• How to use data validation to restrict input based on the value of another cell

Creating and using named range 

• How to create a named range from a cell or group of cells
  
• How to create a named range from a formula
  
• How to use named ranges in formulas and functions
  
• How to use named ranges to create dynamic formulas
  
• How to edit and delete named ranges
  
• How to find and locate named ranges in a workbook 

Using the VLOOKUP function to extract data from tables

• Write a VLOOKUP function in a cell
  
• The four arguments of the VLOOKUP function and what they do
  
• Basic VLOOKUP formulas and how they work
  
• Use the VLOOKUP function with exact and approximate match

Using the IF, AND, and OR functions for complex logical calculations

• Write the IF, AND, and OR functions in Excel
  
• Use the IF function to perform simple and nested logical tests
  
• Customize the IF function, including using the TRUE and FALSE arguments
  
• Wrote different types of AND formulas and how they work
  
• Nest the AND function with other functions, such as the IF function
  
• Nest the OR function with other functions, such as the IF function

Using the SUMIF and COUNTIF functions to summarize data with criteria

• Common uses for the SUMIF and COUNTIF functions
  
• Use the SUMIF function to sum values based on a single criterion
  
• Use the COUNTIF function to count cells based on a single criterion
  
• What are the SUMIFS and COUNTIFS functions?
  
• Use the SUMIFS and COUNTIFS functions to sum or count based on multiple criteria

Creating and using macros to automate tasks 

• Recording macros effectively
  
• Edit recorded macros using the Visual Basic Editor
  
• Assign macro to a Button
  
• Enable and disable macros in Excel
  
• Ensuring the security of your macros

Protecting sheets and workbooks with passwords

• Protect a workbook with a password
  
• Protect a sheet with a password
  
• Unprotect a workbook or sheet with a password

Using the data consolidation feature to combine data from multiple sources 

• What is data consolidation and how does it work in Excel
  
• Organizing and formatting data before consolidation
  
• Common data consolidation scenarios, such as combining data from multiple worksheets or workbooks
  
• Use the data consolidation feature to combine data from multiple sources
  
• Types of data consolidation, such as using summary or detail consolidation, or combining data with different layout

Advanced printing options, including setting up custom headers and footers and more    

• Add and customize headers and footers in Excel
  
• Use the Page Setup dialog to manage headers and footers
  
• Setting up the Repeat Header using Rows to repeat
  
• Define the File path and worksheet name in the Header and Footer