|
1 Hour
|
- Recognize the differences between formulas and Pivot Tables and identify the pros and cons of using Pivot Tables.
- Discover essential skills and intermediate techniques for effective Pivot Table construction and advanced data handling.
- Implement best practices for constructing Pivot Tables to enhance data analysis and reporting.
|
|
1 Hour
|
- Resolve formula errors and apply intermediate skills for calculations in Pivot Tables.
- Customize Pivot Table layouts and displays for improved data presentation.
- Master advanced data management techniques and enhance productivity with keyboard shortcuts in Excel.
|
|
1 Hour
|
- Analyze Pivot Table data to uncover insights and address reporting needs.
- Customize calculations and layouts to improve data clarity and presentation.
- Apply advanced techniques and utilize shortcuts to streamline workflow and solve complex data challenges.
|
|
1 Hour
|
- Utilize Power Pivot to enhance Business Intelligence and reporting within Excel.
- Create Pivot Tables from related data sources, including external and Excel tables.
- Manage relationships using the Data Model and implement powerful calculated fields in Pivot Tables.
|
|
4 Hours
|
- Identify key Pivot Table commands such as Subtotals and Field List within Excel’s ribbon interface.
- Recognize Pivot Table indicators for filtering and the menus that appear or disappear during interactions.
- Utilize mouse actions to reconstruct data and manage the GETPIVOTTABLE feature in Excel.
|
|
1 Hour
|
- Differentiate between simple and dynamic formulas and identify key elements that make a formula dynamic.
- Explore top dynamic formulas and advanced techniques that simplify Excel modeling.
- Discover new dynamic array formula features introduced in Excel 365.
|
|
1 Hour
|
- Understand VBA macros as the 4th generation of Excel modeling and identify key elements of a VBA macro.
- Recognize when to use VBA macros and explore best practices for building effective macros.
- Discover the steps to create a reporting-generating Bot and key elements of the VBA environment.
|
|
1 Hour
|
- Automate data entries and convert PDF data to Excel.
- Use Excel functions to connect and refresh online tables.
- Master text formulas for formatting and integrate accounting system data into Excel.
|
|
0.5 Hour
|
- Identify key features of API reporting and methods for reporting from financial systems.
- Differentiate between extracted and attached Excel reports for better reporting clarity.
- Explore technology for writing back data and unique features of Office Connect in dynamic API reporting.
|
|
1 Hour
|
- Build interactive Excel budgeting models and create editable PivotTables for efficient reporting.
- Use Power Query to consolidate and integrate data from multiple sources.
- Generate integrated reports that combine actuals, budgets, and forecasts for streamlined analysis.
|
|
1.5 Hours
|
- Explore formulas and methods for efficient data management, including custom cell formatting and dynamic named ranges.
- Analyze formulas in PivotTables to enhance data analysis and streamline decision-making.
- Understand the basics of VBA, M language, and DAX functions for advanced data manipulation and reporting.
|
|
1.5 Hours
|
- Discuss popular Excel charts and discover essential charting techniques for clarity.
- Analyze key formatting techniques and explore rarely used charts for enhanced visualization.
- Implement best practices to create impactful and effective charts in Excel.
|
|
1.5 Hours
|
- Identify and construct Gantt Charts, from basic to advanced with overlapping tasks.
- Apply conditional formatting techniques in Excel to improve data visualization.
- Enhance data entry efficiency using Excel apps for streamlined workflows.
|
|
1.5 Hours
|
- Discuss principles of data analysis and the most commonly used charts, Power Pivots, and queries.
- Create custom charts in Excel and manage data connections and models.
- Generate interactive dashboards and explore the process for creating data-driven interactivity.
|
|
1 Hour
|
- Analyze dashboard design principles and create interactive, visually engaging dashboards in Excel.
- Apply best practices in data visualization to enhance clarity and impact.
- Leverage dashboards to inform and guide strategic business decisions.
|
|
1.5 Hours
|
- Use Excel for disciplined financial analysis and develop projections for income statements, balance sheets, and cash flows.
- Analyze methods to maintain model integrity and credibility throughout the development process.
- Prepare accurate financial projections that guide decision-making and strategic planning.
|
|
4 Hours
|
- Explain how to use Power Query to extract text from cells, unpivot reports, and import data.
- Identify key Power Query commands for appending queries and sending data to PivotTables.
- Navigate the Power Query interface to locate essential commands and improve data analysis workflows.
|
|
1 Hour
|
- Analyze how Power Query/Get and Transform saves time and enhances data processing efficiency.
- Identify the differences between Excel functionality and Power Query/Get and Transform.
- Discuss the benefits and speed advantages of using Power Query/Get and Transform for data work.
|
|
1 Hour
|
- Analyze how to create additional columns without complex formulas.
- Explain how to combine, merge, and unpivot unusable data to make it report-ready.
- Discuss the advanced features of the Power Query Editor.
|
|
4 Hours
|
- Analyze concepts like data visualization, integration, and the use of Power Pivot, Power Map, and Power Excel.
- Explore DAX, measurements, and how to perform data binding and formatting.
- Master Power BI Embedded and Workspace Collection for advanced data analysis and reporting.
|
|
1.5 Hours
|
- Explain DAX and how to leverage its functionality for advanced data analysis.
- Explore Measures and the CALCULATE function to enhance data modeling.
- Discuss how Power Pivot provides Business Intelligence and reporting capabilities within Excel.
|
|
1 Hour
|
- Discuss the functionalities of the Query Editor tools in Excel and Power BI for creating calculated columns.
- Create and manipulate calculated columns using DAX functions to summarize data and uncover insights.
- Troubleshoot common issues encountered when working with calculated columns in Excel and Power BI.
|
|
1.5 Hours
|
- Build reports using Excel formulas such as VLOOKUP, MATCH, and INDEX, and create Pivot Tables.
- Review model reports with GETPIVOTDATA to streamline data extraction.
- Identify essential VBA macros to automate reporting processes for "one-click reporting."
|
|
1 Hour
|
- Analyze how to synchronize financials from EXCEL to WORD and PowerPoint.
- Explain how to set up Parameters and Proofs in financial report modeling.
- Discuss building an interactive dashboard with clickable buttons for detail drill-down, and identify three essential elements in financial modeling.
|
|
1 Hour
|
- Identify critical components for building integrated financial documents and presentations in Excel, Word, and PowerPoint.
- List essential formulas for designing automated narratives and synchronizing financial data between Word, PowerPoint, and Excel.
- Analyze methods to customize charts for reports and ensure effective data presentation.
|
|
1 Hour
|
- Analyze the VLOOKUP function for looking up data, performing approximate matches, and handling potential #N/A errors.
- Compare INDEX and MATCH with VLOOKUP/HLOOKUP, and evaluate the use of the SUMIFS function for summing values based on multiple criteria.
- Explore the new XLOOKUP function in Office 365 and identify the ideal data for analysis with Excel's PivotTable feature.
|
|
2 Hours
|
- Identify the purpose of Excel’s XLOOKUP function and the column_index_num argument within VLOOKUP.
- Explore the MAXIFS function and the number of criteria pairs you can specify.
- Understand the MATCH function’s return value and explore Excel for Mac’s Function ScreenTips for enhanced functionality.
|
|
2 Hours
|
- Explain the menu in Excel where the Table feature resides.
- Analyze dynamic array function from a list of worksheet functions.
- Discuss how to use Power Query to automate repetitive analysis.
|
|
2 Hours
|
- Recall which ribbon tab contains the Data Validation feature.
- Identify which input is valid for a data validation rule limiting inputs to whole numbers between 0 and 100.
- Explain the ribbon tab the Table feature appears within.
|
|
2 Hours
|
- Recall the location of the menu command to determine if a workbook contains links and identify the setting for Enable Iterative Calculations.
- Transform cell references into range names and implement the Watch Window to track changes in workbooks.
- Explore automatic vs. manual calculation in Excel and learn how to trace dependents, precedents, and circular references.
|
|
1 Hour
|
- Discuss utilizing VLOOKUP and XLOOKUP to search for specific data across multiple sheets and compile information effectively.
- Apply the VSTACK function to combine arrays vertically and use Power Query for advanced merging and appending data from various formats.
- Analyze data models to enhance data analysis, ensuring accuracy and efficiency in decision-making processes.
|
|
1 Hour
|
- Grasp the basics of Excel Tables, including creation, formatting, and organizing data efficiently.
- Master sorting and filtering techniques, including custom sorting and applying multiple filters, while utilizing slicers for interactive filtering.
- Utilize table-specific formulas and structured references to enhance data analysis within tables.
|
|
1 Hour
|
- Identify and differentiate between the four main depreciation methods and understand their applications.
- Apply procedures to calculate depreciation using each method and utilize Excel templates for efficient implementation.
- Gain practical insights into managing depreciation for long-lived assets in accounting practices.
|
|
1 Hour
|
- Analyze solutions for handling changes to Print Preview in Word 2010 and later versions.
- Recover and explore the From Text command in Excel 2019 and Microsoft 365, and examine the Threaded Comments feature in Microsoft 365.
- Analyze and apply the new XLOOKUP worksheet function in Microsoft 365 for more efficient data lookups.
|
|
2 Hours
|
- Append data from multiple worksheets into a self-updating list with Power Query.
- Optimize data by transposing or unpivoting reports and removing total rows.
- Automate extraction using INDIRECT and simplify navigation with the Navigation task pane.
|
|
2 Hours
|
- Analyze the concept of waterfall calculations in Excel.
- Recall keyboard shortcuts to transfer cell contents and toggle bold settings.
- Identify the location of the Format Painter command within Excel's menu structure.
|
|
5 Hours
|
- Explain keyboard shortcuts for navigating to the last used cell and counting rows in Excel.
- Discuss the use of "With" in Excel macros and methods for displaying a UserForm in VBA.
- Identify how to modify the Properties Window and manage items within a ListBox in VBA.
|