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

Duration: 2 Days

Locations for Training:

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

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. 

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.