The Microsoft Excel Course: Advanced Excel Training

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.

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

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.

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.

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.

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.

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.

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.