Microsoft Excel Advanced 2019 Practice Exam

Microsoft Excel Advanced 2019 is a software program designed for spreadsheet management and data analysis. It builds upon the basic functionalities of Excel by offering more advanced features for users to manipulate and analyze data effectively. With Excel Advanced 2019, users can create complex formulas and functions, perform advanced data analysis and modeling, and visualize data using charts and graphs. Additionally, it provides tools for data validation, conditional formatting, and collaboration, making it a powerful tool for professionals in various industries to manage and analyze large datasets efficiently.
Why is Microsoft Excel Advanced 2019 important?

• Data Analysis: Excel Advanced 2019 offers powerful tools for data analysis, including pivot tables, advanced functions, and statistical analysis tools, making it invaluable for professionals in data-driven roles.
• Financial Modeling: Excel Advanced 2019 allows for complex financial modeling, including scenario analysis, goal seeking, and sensitivity analysis, which is essential for finance professionals.
• Reporting: The software enables the creation of detailed and visually appealing reports, incorporating charts, graphs, and tables, which is crucial for presenting data to stakeholders.
• Automation: Excel Advanced 2019 includes features like macros and VBA (Visual Basic for Applications) for automating repetitive tasks, increasing efficiency in data management and analysis.
• Decision Making: With its advanced analysis tools, Excel helps in making informed decisions based on data insights, making it relevant for professionals across various industries.
• Project Management: Excel's advanced features can be used for project planning, tracking, and reporting, aiding project managers in managing tasks and resources effectively.
• Budgeting and Forecasting: The software allows for the creation of complex budgets and forecasts, helping businesses plan and manage their finances more effectively.
• Data Visualization: Excel Advanced 2019 includes tools for creating visually appealing charts and graphs, enhancing data presentation and understanding.
• Collaboration: Excel's collaboration features, such as sharing and co-authoring, enable teams to work together on spreadsheets in real-time, improving productivity and collaboration.

Who should take the Microsoft Excel Advanced 2019 Exam?

• Data Analysts
• Financial Analysts
• Business Analysts
• Project Managers
• Accountants
• Operations Managers
• Business Intelligence Analysts
• Financial Planners
• Marketing Analysts
• Supply Chain Analysts
• Human Resources Managers
• Sales Managers
• Researchers
• Educators

Skills Evaluated

Candidates taking the certification exam on Microsoft Excel Advanced 2019 are typically evaluated for the following skills:

• Advanced Formulas and Functions
• Data Analysis
• Data Visualization
• Advanced Data Management
• Automation and Macros
• Advanced Data Modeling
• Collaboration and Sharing
• Advanced Charting Techniques
• Data Security and Protection
• Troubleshooting and Problem-Solving
• Best Practices and Efficiency

Microsoft Excel Advanced 2019 Certification Course Outline

1. Advanced Formulas and Functions:

   • Nested Functions
   • Array Formulas
   • Logical Functions (IF, AND, OR)
   • Lookup and Reference Functions (VLOOKUP, HLOOKUP, INDEX, MATCH)
   • Statistical Functions (AVERAGEIF, SUMIF, COUNTIF)
   • Date and Time Functions
   • Text Functions (LEFT, RIGHT, MID, CONCATENATE)

2. Data Analysis Tools:

   • PivotTables and PivotCharts
   • Data Tables
   • Data Analysis with Power Query
   • Advanced Filtering and Sorting
   • Subtotal and Database Functions
   • What-If Analysis (Scenario Manager, Goal Seek, Data Tables)
   • Forecasting Techniques

3. Advanced Data Visualization:

   • Creating Dynamic Charts (Sparklines, Data Bars, Icon Sets)
   • Using Slicers and Timelines
   • Customizing Charts and Graphs
   • Using Trendlines and Error Bars
   • Creating Interactive Dashboards

4. Data Validation and Protection:

   • Data Validation Rules
   • Protecting Worksheets and Workbooks
   • Sharing and Collaborating on Workbooks
   • Tracking Changes and Comments

5. Advanced Data Management:

   • Consolidating Data from Multiple Sources
   • Using External Data Sources (SQL Server, Access, Web Queries)
   • Importing and Exporting Data
   • Cleaning and Transforming Data
   • Managing Named Ranges and Tables

6. Automation and Macros:

   • Recording and Editing Macros
   • Using VBA (Visual Basic for Applications)
   • Creating User-Defined Functions
   • Automating Repetitive Tasks

7. Advanced Formatting Techniques:

   • Conditional Formatting
   • Custom Number Formats
   • Using Styles and Themes
   • Formatting Tables and Cells

8. Collaboration and Sharing:

   • Sharing Workbooks
   • Co-Authoring in Excel Online
   • Using OneDrive for Business
   • Working with SharePoint

9. Data Analysis with Power Pivot:

   • Introduction to Power Pivot
   • Creating Relationships between Tables
   • Creating Calculated Columns and Measures
   • Building PivotTables and PivotCharts with Power Pivot Data

10. Troubleshooting and Optimization:

    • Auditing Worksheets
    • Tracing Precedents and Dependents
    • Error Checking and Correcting
    • Improving Workbook Performance

11. Integration with Other Applications:

    • Integrating Excel with Other Microsoft Office Applications (Word, PowerPoint, Outlook)
    • Importing and Exporting Data to and from Other Applications
    • Using Excel Add-Ins and Third-Party Tools

12. Advanced Charting Techniques:

    • Using Advanced Chart Types (Gantt Charts, Radar Charts, etc.)
    • Formatting Chart Elements (Axes, Labels, Titles)
    • Creating Combination Charts
    • Using 3D Charts

13. Advanced PivotTable Techniques:

    • Creating Calculated Fields and Items
    • Grouping and Ungrouping Data
    • Using Slicers and Timelines
    • Working with Multiple PivotTables

14. Advanced Data Modeling:

    • Creating Data Models
    • Managing Relationships between Tables
    • Using DAX (Data Analysis Expressions) for Advanced Calculations
    • Building Hierarchies and KPIs

15. Advanced Excel Functions:

    • Using Financial Functions (PV, FV, NPV, IRR)
    • Using Statistical Functions (STDEV, VAR, CORREL, RANK)
    • Using Lookup and Reference Functions (OFFSET, INDIRECT)
    • Using Text Functions (LEN, SEARCH, SUBSTITUTE)

16. Best Practices and Efficiency Tips:

    • Excel Best Practices
    • Keyboard Shortcuts
    • Tips for Improving Productivity