Excel class - Advanced

Learn to work with complex data sets and perform advanced analysis in this advanced Excel course. Discover more advanced functionality for Pivot Tables, and find ways to automate repetitive tasks. Learn how to use advanced tools for analysis, functions, and formatting.

What you learn in the advanced Excel course

• Locking Cells: Learn to lock rows or columns with mixed cell referencing, which is helpful with formulas that span multiple rows or columns.
• Advanced Conditional Formatting: Discover ways to automatically apply conditional formatting in Excel using formulas, such as all positive values appearing as green and all negative values appearing as red.
• Nested IF Statements: Go beyond the standard IF statements limited to either TRUE or FALSE. Use nested IF statements to include multiple options. For example, a teacher can evaluate cell values and have a range of 90 to 100 report the grade as an "A" while 80-89 would report as a "B".
• AND/OR IF Statements: Create complex logic statements in Excel using AND/OR IF statements. These statements can match either one conditional or multiple conditionals.
• Goal Seek: Find values needed to create a desired output across an Excel sheet or workbook. Use goal seek tool to find values necessary to meet a specific result, such as the amount necessary to meet a savings goal.
• Data Tables: Find out the impact of making changes to a single input impacts a targeted output, or build sensitivity analysis. Data Tables in Excel can show how compound interest can grow, allowing the exploration of various interest rates.
• Pivot Charts: Learn to visualize data compiled into Pivot Tables using Pivot Charts.
• MATCH VLOOKUP: Create a more powerful VLOOKUP by using the the MATCH function, which can locate the column input.
• MATCH INDEX: Find out how to perform a reverse lookup that goes beyond VLOOKUP, and then discover how to combine two MATCH’s within the INDEX for building Excel formulas that are capable of data extraction.
• XLOOKUP: Learn to locate data across a sheet even when it is not well organized using XLOOKUP which provides flexibility and functionality beyond VLOOKUP & HLOOKUP.
• Auditing Cells: Discover options to locate errors within formulas as well as ways to trace for precedent cells referenced in another cell, and dependent cells which reference other cells.
• Hot Keys: If you prefer to work from your keyboard rather than a mouse, learn to create Hot Keys that automate functions and commands with a keystroke or combination of keystrokes. From text formatting to calculations, you can leave your mouse alone and perform Excel functions directly from your keyboard.
• Advanced Table Functions: Leverage advanced table features in Excel, including structured references and automatic formula propagation across tables. Use table designs, advanced sorting, and filtering to streamline your data analysis and create dynamic, self-updating reports.
• Advanced lookups and returns using Using DGET and FILTER: Perform advanced data retrieval and dynamic extraction directly from lists and datasets. Return single database records or extract multiple matching rows dynamically based on specific criteria.
• Advanced Pivot Tables using Calculated Items and Fields: Create custom Calculated Items and Fields for on-the-fly computations inside a Pivot. Customize how data is aggregated using summarization techniques to create more meaningful reports and gather deeper insights.