MS Excel - JumpStart to Analyst

Discover how relative and absolute references work in Excel as you copy formulas, fix rows or columns with dollar signs, and toggle reference types with the keyboard.

Learn three methods to compute average and count in Excel: insert function, manual typing, and the equals shortcut. Build a foundation with shortcuts and verify results on the status bar.

Master grouping and ungrouping of rows and columns, expand and collapse groups, and freeze panes to keep headers visible while building readable financial models.

Create a cool looking table in Excel quickly with format as table, using Ctrl+T, presets, and a total row, and convert the table back to a range when needed.

Learn best practices for adding and managing comments in Excel workbooks, including inserting, positioning, printing comments, and setting move and size with the cells.

Master the if formula in Excel, using a logical test to categorize transactions and label them as large, median, or small, including nested scenarios.

Learn to use sumif and sumifs in Excel to total amounts by multiple criteria. Understand text criteria with quotes, numeric criteria, and the roles of criteria and sum ranges.

Learn how the lookup formula replaces nested ifs by searching the left column and returning the right-hand value, with array and vector modes.

Upgrade from look up to vlookup to fetch names and amounts by invoice number in a table. Use column index and range lookup, and prefer exact match over approximate.

Use data validation to create a dynamic list from your database's invoice numbers, updating automatically as the database changes.

Explore how hlookup performs horizontal lookups to retrieve invoice numbers and amounts from a table, using row index numbers, exact matches, and data validation to build a dynamic table.

Learn how to replace vlookup with index and match in Excel to overcome leftmost-column limits, using index, match inputs, exact match, and practical examples to retrieve invoice numbers and amounts.

Discover how pivot tables reveal insights by rearranging data: drag fields into rows, columns, and values; compare region, size, and invoice amounts, and compute averages.

Explore subtotals in pivot tables by region, using design options and field settings to show bottom subtotals and switch among sum, average, and maximum with formatting.

Learn to create calculated columns within pivot tables, using percent of grand total, running total, and differences to analyze regional invoice amounts.

Learn two methods to create calculated columns, including a ratio of invoice amount to days overdue, defined in the calculate field dialog and named and formatted for analysis.

Learn how slicers and timelines filter pivot tables in Excel 2010 and later, and how to insert, format, and use timelines for date-based dashboards.

Apply simple dashboard design principles to make every graph tell a clear story in seconds; tailor charts to your boss’s needs, using pivot charts and concise pie data.

Explore sensitivity analysis in Excel by building one- and two-variable data tables to see how net present value changes with varying discount rates, using what-if analysis and practical steps.

Explore sensitivity analysis by reading the table to see how net present value varies with interest rate and first year payback, highlighting best and worst project scenarios.

Learn to build a two-variable sensitivity analysis table in Excel, linking input cells to the net present value using a data table and what-if analysis.

Explore sensitivity analysis using data tables and scenario manager in Excel to compare base, worst, and best cases. Track changing variables and assess net present value across scenarios.

Learn how to use Excel's goal seek function to solve for unknown inputs in a mortgage scenario, using the payment formula, what-if analysis, and adjusting input and output cells.