What you will learn?

Advanced Formatting

• Considerations in Designing a Spreadsheet
• Working with Cell Styles & Document Themes
• Apply, Create & Manage Conditional Formatting
• Creating a Custom Number Format

Working with Tables

• Create a Table & working with the Total Row
• Sorting & Filtering a Table
• Formatting the Table
• Converting to a Range

 Advanced Functions and Formulas

• Formulas with Multiple Operators
• Using and Managing Defined Names
• Using Mixed Cell Referencing
• Date and Time Functions
• Using Logical Functions (IF, AND and OR)
• Using Lookup Functions
• Mathematical and Statistical Functions
• Using Financial, Text & Database Functions
• Using 3D Reference within SUM Function

Validating Data and Auditing Worksheets

• Using Data Validation
• Auditing Tools
• Displaying and Tracing Formulas
• Understanding Formula Errors

Managing Charts

• Editing, Adding, and Removing Chart Data
• Changing Chart Data
• Change Scales and Display Unit of the Axis
• Add Secondary Axis & Change Series Chart Type
• Changing and Aligning Data Labels and Chart Legend

Working with Data Ranges

• Sorting by a Custom List
• Creating a Custom AutoFilter
• Using Advanced Filter

Working with PivotTables

• Creating a PivotTable
• Specifying PivotTable Data
• Changing a PivotTable’s Calculation
• Filtering and Sorting a PivotTable
• Working with PivotTable Layout
• Grouping PivotTable Items
• Updating & Formatting a PivotTable
• Creating a PivotChart
• Using Slicers between PivotTables

Analyzing Data

• Creating Scenarios
• Working with Data Tables
• Using Goal Seek
• Using Text to Columns
• Grouping and Outlining Data
• Using Subtotals
• Consolidating Data by Position or Category

Managing Templates

Linking and Importing Data

• Inserting a Hyperlink
• Linking Data & Managing External Links
• Importing Data
• Working with Existing Data Connections

Working with Macros

• Recording a Macro
• Playing and Deleting a Macro
• Adding a Macro to the Quick Access Toolbar

Managing Collaborative Editing

• Working with Comments to Cells
• Tracking Changes
• Protecting Worksheets and Worksheet Elements
• Protecting a Workbook