Microsoft Excel Class in Boston on December 2, 2020 Advanced


Register for Course


This advanced Excel course teaches functions necessary for working with complext data sets and performing advanced analysis. Take Pivot Tables to the next level, and learn to automate tasks using macros. You’ll also learn advanced Excel functions and formatting, advanced analytical tools, and close out the course with a comprehensive project. Attend this advanced Excel training learn: Advanced Cell Locking: Learn about mixed cell referencing, which locks either a row or column. This is especially useful when dragging formulas across multiple rows and columns. Cell Auditing: Review your formulas for errors, and trace dependents and precedents. Conditional Formatting with Formulas: Learn how to make conditional formatting more powerful in Excel by using formulas. For example, format cells that are greater than the value in cell in D55 with a light red fill. Nested IF Statements: Basic IF statements in Excel restrict you to two options: TRUE or FALSE. With nested IFs, you can put an IF statement inside another IF statement to allow for multiple options. For example, if A1=2, then show “Low”, if A1=4 then show “Medium”, if A1=6 then show “High.” IF Statements with AND/OR: This allows you to write logical statements in Excel that meet one of multiple conditions or meet multiple conditions. The AND function allows us to specify that two or more conditions are met. The OR function says that one of two or more conditions need to be met. Goal Seek: This powerful Excel tool allows the user to find a specific value that creates the desired output. For example, if we have a set of grades and want to know what score we need to get on the final exam to reach a certain grade level, then we can use the goal seek tool to solve. Data Tables: With Data Tables, you can create sensitivity analysis and see how changing one or two inputs impact the specified output. For example, if we want to see how various investment amounts will grow at various rates, we can use an Excel Data Table. Pivot Charts: Once data is summarized in a Pivot Table, we can use Excel’s Pivot Charts to seamlessly create a visualization based on our selected data. VLOOKUP-MATCH: Make the Excel VLOOKUP function more dynamic by adding the MATCH function to find the column input INDEX-MATCH: A more flexible lookup function, allowing users to “lookup backward” which the VLOOKUP does not. INDEX- Double MATCH: Use two MATCH’s in the INDEX function to create the ultimate dynamic Excel formula to extract data from tables. Recording Macros: Record macros in Excel to automate a series repeated actions. For example, if every time you receive a specific report, you need to move certain columns, filter based on a specific criteria, and then create a chart based on the data, you can record a macro to automate this workflow. Hot Keys: Work with Excel without using the mouse by activating Hot Keys. Any command in Excel can be reached with a series of keyboard strokes, which can substantially expedite workflow. For example, center align text with ALT + H + AC.