Microsoft Excel Advanced for Office 365/2021: Master Data Analysis with PivotTables Live Hands-On Instructor-Led Training Class

Duration & Times: 2 Days – 8:30 AM – 3:00 PM EST

Locations for Training:

• Virtually Online Live (VOLT™) From Anywhere!
• Upon Request: Custom Private Training!

Course Description:  In today’s data-driven world, mastering advanced data analysis techniques in Microsoft® Excel® is crucial. Whether you’re using Office 365 or Office 2021, this hands-on, two-day course empowers you to go beyond basic data summaries with PivotTables. You’ll learn to prepare and analyze data effectively, creating insightful PivotTables and PivotCharts that drive informed business decisions and give you a competitive edge. This course builds on foundational Excel knowledge, focusing on automating tasks, collaborating on workbooks, and using advanced functions seamlessly across desktop and web versions of Excel.

Course Objectives:  Upon completing this course, you will be able to:

• Master advanced PivotTable techniques to summarize and analyze data from multiple sources.
• Automate workbook tasks using advanced Excel functions and macros.
• Forecast data trends and create compelling visualizations like sparklines and data maps.
• Securely share and protect workbooks, leveraging cloud-based collaboration tools.
• Integrate Excel for the Web and OneDrive, understanding key differences from the desktop version.

Target Student: This course is ideal for experienced Excel users who want to take their skills to the next level. Perfect for those who regularly manage complex workbooks, need to automate tasks, collaborate on workbook data, and use advanced Excel functions. If you’re looking to elevate your data analysis capabilities by mastering PivotTables and tackling intricate datasets, this course is for you.

Prerequisites: To succeed in this course, students should have practical experience with Excel, including tasks like creating formulas, sorting and filtering data, and working with PivotTables and charts. If you’re not yet comfortable with these skills, it is recommended that you complete the Microsoft Excel for Office 365/2021 Introduction course first. 

Required Technical Set-Up:
• Primary computer connected to a second monitor OR second computer to view instructor live
• Reliable High-speed internet connection
• Web Browser: Google Chrome Preferred (Alternative: Microsoft Edge)
• 3-Button Computer mouse
• Audio Speaker/Microphone (built-in to computer or external)

Microsoft Excel Advanced for Office 365/2021: Master Data Analysis with PivotTables Course Outline

1. Managing Multiple Worksheets and Workbooks

• Use Links and External References: Establish dynamic connections between workbooks.
• Use 3-D References: Reference and integrate data across multiple worksheets.
• Consolidate Data: Combine and summarize data from various sources efficiently.

2. Collaborating and Protecting Workbooks

• Collaborate on a Workbook: Share, edit, and manage workbooks with team members.
• Protect Worksheets and Workbooks: Secure data with protection settings to restrict access and editing.

3. Automating Workbook Tasks

• Apply Data Validation: Maintain data integrity by enforcing input rules and restrictions.
• Search for Invalid Data and Formulas with Errors: Identify and correct data issues and formula errors.
• Work with Macros: Automate repetitive tasks by creating and managing custom macros.

4. Lookup Functions and Formula Auditing

• Implement Lookup Functions: Use VLOOKUP, HLOOKUP, and XLOOKUP to retrieve specific data points.
• Trace Cells: Track and visualize data dependencies and relationships within formulas.
• Watch and Evaluate Formulas: Monitor and troubleshoot complex formulas for accuracy.

5. Forecasting and Analyzing Data

• Determine Potential Outcomes Using Data Tables: Explore and analyze multiple outcomes based on different input scenarios.
• Determine Potential Outcomes Using Scenarios: Compare various scenarios to make data-driven decisions.
• Use the Goal Seek Feature: Adjust variables to achieve specific objectives in your data analysis.
• Forecast Data Trends: Predict and visualize future data trends based on historical data.

6. Visualizing Data with Sparklines and Maps

• Create Sparklines: Use mini-charts to represent data trends within single cells.
• Map Data: Create geographic data visualizations to identify patterns and trends.

7. Preparing and Creating PivotTables

• Prepare Data: Clean and organize data to ensure effective PivotTable creation.
• Create a PivotTable from a Local Data Source: Summarize and analyze data from local files.
• Create a PivotTable from Multiple Local Data Sources: Combine and analyze data from various sources within a single PivotTable.
• Create a PivotTable from an External Data Source: Import and work with data from external databases and online sources.

8. Analyzing Data with PivotTables

• Summarize PivotTable Data: Aggregate data to extract key insights and metrics.
• Organize PivotTable Data: Group, sort, and structure data for meaningful analysis.
• Filter PivotTable Data: Isolate specific data points for deeper examination.
• Format a PivotTable: Apply formatting techniques to improve data presentation and readability.
• Refresh and Change PivotTable Data: Update and modify data sources as needed to keep your analysis current.

9. Creating and Customizing PivotCharts

• Create a PivotChart: Visualize your PivotTable data with dynamic charts.
• Manipulate PivotChart Data: Adjust and customize chart elements to highlight important information.
• Format a PivotChart: Enhance your PivotChart’s appearance for clarity and impact.

Disclaimer: All course objectives and outlines are used as a guideline and are subject to change to ensure the latest information is covered to support real-world use of the technology.

Software Versions: ThinkB!G open enrollment classes are taught on the most current version of software whenever possible. To request a specific version, please Contact Us or call us at 301-362-2250.