The Microsoft Excel Course: Advanced Excel Training

Master navigation in Excel using Ctrl+arrow to jump to the last non-blank cell and Ctrl+Shift+arrow to select quickly. Use Alt shortcuts and the quick access toolbar to speed your workflow.

Keep the table titles visible while scrolling with Excel's freeze panes feature. Apply it by selecting the row below the fourth, then view, freeze panes; remove with unfreeze panes.

Split the Excel window into horizontal, vertical, or four panes to view distant cells simultaneously, using the view tab split tool and simple removal gestures.

Learn how to name cell ranges in Excel and apply them in formulas to summarize two years of sales, using named ranges like sales 12 and sales 13.

Learn to create dropdown menus in Excel through data validation, restricting a range to a predefined list of values, and configuring or turning off error alerts as needed.

Master excel f-key: edit with f2, repeat with f4, jump to sheets with f5, navigate panes with f6, select ranges with f8, create charts with f11, evaluate formulas with f9.

Learn to copy and paste only visible cells in Excel by using Go to Special with visible cells only, or the Alt+; shortcut, ensuring hidden columns are ignored.

Master how to fix cell references in Excel by using dollar signs and F4 to lock columns and rows, enabling accurate copying across rows and columns.

Group adjacent columns or rows using Excel's data tab group feature to hide or expand sections. Visual indicators include dots and a minus/plus toggle with a hierarchy of groups.

Group multiple sheets in Excel to edit a consistent income-statement structure across five companies at once, using Ctrl or Shift, then exit group mode to edit individually.

Master find and replace to swap text in cells and formulas, update sheet names across a workbook, and apply replace all with caution.

Master find and replace for formatting in Excel by using the format button to find a format and replace colors, fonts, alignments, and number formats across cells.

Learn how to create printable documents in Excel by selecting a print area, using page layout to set the print area, and printing only the selected area.

Learn how circular references and iterations affect Excel calculations, identify loops, and enable iterative calculation to manage revenue scenarios like bonuses and net revenue.

Learn how intentional circular references in Excel affect depreciation and amortization calculations, using ending PP&E versus beginning PP&E and iterative calculation to produce plausible results.

Explore trace precedents to visualize how input cells like EBT and the tax rate fuel tax, EBIT, and net income, using show formulas and cross-sheet links in Excel.

Master nested functions in Excel by combining if, average, and sum for powerful calculations. Learn to nest up to 64 levels and apply practical examples with sumif and countif.

Explore how to use excel sum, sum if, and sumifs to add numbers in a range, apply criteria, and handle multiple conditions with practical football team examples.

Explore how to use the Excel round functions, including round, roundup, rounddown, and mround, to control digits and multiples, handle negative numbers, and anticipate errors when signs differ.

Apply the iferror function to handle missing points when calculating the percentage of points earned out of the total, displaying not available when an error occurs.

Explore vlookup and hlookup to pull data from tables using exact matches, ensuring the lookup value lies in the first column and refining references and indices for reliable cross-table lookups.

Learn how index and match provide a flexible alternative to vlookup, used separately and together to locate values in an array by row and column, with exact and left-right lookups.

Use index-match-match to retrieve a row and column value from a two-dimensional Excel table, enabling dynamic, exact-match lookups like apartment buildings in Brazil.

Learn how the indirect function returns a cell reference from a text string and how to combine it with vlookup for dynamic lookups across tables, using exact match.

Explore rows and columns as dynamic counters and anchors, then apply them in Vlookup to determine exact column numbers for table lookups.

Learn to nest match in vlookup to retrieve managers and admin personnel for multiple companies, using anchored row and header references. Compare approach with the columns function for adjacent data.

Learn to use choose and vlookup in excel for financial modeling and scenario analysis, building flexible lookups across tables to select revenues and salaries by index.

Learn to use the offset function and its combination with match to reference, move across rows and columns, and nest results in other functions for dynamic Excel analysis.

Explore how to compute present value and future value with fv and pv, compare cash flows using discount rates, and analyze npv and irr in Excel.

Learn to discount cash flows and compute net present value in Excel, using manual discounting and the built-in NPV function to evaluate projects and inform investment decisions.

Analyze the internal rate of return (IRR) and its interpretation, using Excel's IRR function and cash flows to compare against financing costs and the NPV.

Compute a fixed monthly loan payment with Excel PMT and build a full loan schedule showing interest, principal, and remaining debt for a $300,000 loan at 3% over 10 years.

Master Excel date functions to manipulate dates as serial numbers, extract day, month, and year, compute date differences, and use end-of-month (eomonth) and exact-date (edate) calculations for due dates.

Learn excel tips for modeling, navigate formulas with ctrl left bracket and ctrl right bracket, and speed up copying with ctrl enter and ctrl a while hiding the ribbon.

Master how to rearrange tables with shift-drag, keeping formulas intact, and improve print legibility by using conditional formatting to hide duplicate names. Also expand the formula bar to view formulas.

use select special to highlight blanks, formulas, constants, and comments, paste data into the chosen cells, and apply custom formatting like 0.0 x while keeping numbers.

Learn how to manage Excel's background error checking by turning it off for a cleaner worksheet, and use data tables to analyze how interest rate and loan term affect repayment.

Discover how financial models act as a replica of a business to forecast performance and guide decisions by linking debt, interest, P&L, cash flow, and balance sheet with logical relationships.

See how financial models provide a holistic view of revenues, costs, assets, liabilities, and cash impact to help decision makers assess project feasibility.

Learn to avoid common financial modeling mistakes, including embedded constants, multi-workbook models, hidden columns, and duplicated calculations, and always include checks on the balance sheet, P&L, and totals for clarity.

Build flexible models with cell links and no hard-coded inputs. Ensure uniform structure, add sanity checks, use calculation blocks, and label units for print-ready reports.

Explore how financial models facilitate decision-making for managers, bankers, investors, and owners by simulating cash flows across credit analysis, equity investments, valuations, mergers, LBOs, and project finance.

Identify the right level of detail for forecasting in financial models, balancing short- and long-term planning. Emphasize revenues, gross profit, and EBITDA as decision-making inputs across multiple scenarios.

Apply forecasting guidelines that align with historical performance and industry outlook to forecast financials, using assumptions, avoiding hockey stick spikes, and tailoring scenarios to company’s startup, growing, or mature stage.

Build a complete financial model by integrating the P&L, balance sheet, and cash flow across historical and forecast years, linking net income to equity and cash to the balance sheet.

Forecast revenues with a top-down approach using market size and historical performance. Model costs as percentages of revenues and project EBITDA, D&A, interest, taxes, and P&L items.

Learn to forecast balance sheet items using the days technique for trade receivables, inventory, and trade payables, and project other assets and liabilities in line with revenue history.

Forecast the balance sheet items—PP&E, financial liabilities, equity, and cash—to complete a 3-statement model by linking depreciation, capex, debt, equity movements, and cash flow; verify assets equal liabilities.

Build a complete P&L model from scratch in Excel, applying formatting, shortcuts, and a date fix with find and replace and the year function.

Create a mapping column to aggregate P&L categories—revenues, cogs, operating expenses, D&A, interest expenses, and taxes—so you can build a total company level model and output a P&L sheet.

Create an output P&L sheet in Excel by applying a formatting macro, removing duplicates, and building multi-year profits with gross profit, EBITDA, EBIT, EBT, and net income in dollars.

Populate the P&L output sheet with historical financials using Sumifs by type and year, anchoring references for rightward paste and applying negative signs to costs.

Calculate percentage variations in a P&L, format as percentages, and apply conditional formatting with traffic light icons to highlight 2014–2015 and 2015–2016 changes.

Build and format an output balance sheet in excel, consolidating assets, liabilities, and equity from year sheets, applying headers, styles, and a golden rule check.

Use index, match, and match to fill the output balance sheet from multiple source sheets with varying formats, performing a two-dimensional lookup across years.

Create a five-year forecast (2017–2021) by grouping and hiding columns, applying formats and formulas, labeling a 'Forecast' area, and building best/base/worst scenario analyses for revenues, Cogs, and Opex.