Microsoft Excel – Advanced
Advanced is designed for the participant who wants to leverage on the Advanced functions and features to improve productivity and enhance your spreadsheets to simplify your daily work activities.
Participants learn how to manipulate and analyze large datasets effectively, work with external data sources, and create dynamic reports. Advanced charting techniques are covered, including interactive dashboards, dynamic charts, and sophisticated data visualizations using sparklines, data bars, and conditional formatting.
PREREQUISITES
No previous knowledge required
TARGET GROUP
New Microsoft user
Duration
2 Days (14 hours)
Course Achievement
Certificate of Completion by Training Provider
At the end of the course, you will be able to:
• Use and Manage defined Names. Display & trace Formulas and understand Errors
• Use Advanced functions in Date, Time, Logical, Text and Lookups
• Use Data Validation to control the data being accepted such as controlled text length and drop list.
• Create Scenarios, use of Goal Seek & Solver
• Group and Outline Data & Using Subtotals
• Consolidate Data by Position or Category, and Use Formulas
• Use and format of PivotTable, PivotChart and Slicers
• Record, play and delete a Macro. Adding a Macro to the Quick Access Toolbar
BENEFITS OF Microsoft Excel – Advanced TRAINING
Efficiency and productivity
Professional advancement and specialization
Power Query and Power Pivot
Data visualization mastery
View Course Outline
• Formulas with Multiple Operators
• Inserting and Editing a Function
• AutoCalculate and Manual Calculation
• Cell and Range Names
• Displaying and Tracing Formulas
• Understanding Formula Errors
• Using Financial Functions (PMT)
• Using Logical Functions (IF, AND, OR, Nested IFs)
• Using Lookup Functions (VLOOKUP, HLOOKUP, MATCH & etc)
• Using Text Functions (CONCATENATE, UPPER, LEFT & etc)
• Using Date & Time Functions
• Creating a Custom AutoFill List
• Applying Conditional Formatting
• Create & Manage Conditional Formatting Rules
• Sorting by a Custom List
• Filtering Data
• Creating a Custom AutoFilter
• Using Advanced Filter
• Using Data Validation
• Working with Scenarios & Creating a Scenario Summary Report
• Working with Data Tables
• Using Goal Seek & Solver
• Using Text to Columns
• Grouping and Outlining Data
• Using Subtotal
• Consolidating Data by Position or Category
• Consolidating Data Using Formulas
• Sharing a Workbook
• Track changes
• Compare and Merge Workbooks
• Manage Margins, Page Orientation & Size
• Using Columns & Page Breaks
• Working with the Page Background, Cover Page, Page numbers, Headers & Footers
• Create and Specifying PivotTable Data
• Changing a PivotTable’s Calculation
• Using Multiple PivotTable Fields
• Filtering & Sorting a PivotTable
• Working with PivotTable Layout
• Grouping PivotTable Items
• Update & Format a PivotTable
• Create a PivotChart
• Using Slicers & Sharing Slicers Between PivotTables
• Inserting a Hyperlink
• Importing Data from a Text File and an Access Database
• Importing Data from the Web Working with Existing Data Connections
• Recording a Macro
• Playing and Deleting a Macro
• Adding a Macro to the Quick Access Toolbar
