Excel for Accounting & Finance

Explore the Excel interface by opening a blank workbook, navigating the title bar, ribbons, and quick access toolbar, and using the name box, formula bar, and cell references.

Explore how to get help in Excel using screen tips, Tell Me More, and the F1 shortcut. Access help tabs, search tools, training resources, and Copilot chat.

Understand how tabs, ribbons, and menus organize Excel commands into groups, with screen tips, shortcuts, the mini toolbar, and options to hide or show the ribbon.

Explore how Excel's quick access toolbar centralizes frequently used commands, customize its position above or below the ribbon, and add items like spelling, undo, redo, and conditional formatting.

Explore Excel's backstage area from the file tab, where you can create, open, save, print, share, export, protect with passwords, inspect metadata and version history, and recover unsaved workbooks.

Explore workbooks and worksheets in Excel, learning the grid of rows and columns, sheet naming and renaming, and how to insert, move, copy, delete, drag and drop, and color-code sheets.

Learn how to save workbooks in Excel, including autosave to OneDrive, save options on the quick access toolbar, and using save as for different file types.

Navigate and select cells, rows, and columns in Excel using keyboard shortcuts for efficiency. Learn to make contiguous and noncontiguous selections, including entire columns, rows, and the full worksheet.

Learn how to enter and edit data in Excel, including numbers, text, dates, and time, using the cell, formula bar, or F2, with auto-fit column width and editing controls.

Apply number formats in Excel—number, percentage, currency, and accounting—from the Home tab. Formatting changes the appearance, not the underlying value, and you can adjust decimals and currency styles.

Learn to apply date and time formats in Excel, from short and long date formats to locale variations, and understand dates as numbers starting from January 1, 1900.

Format cells, rows, and columns in Excel using auto fit and row height, then apply blue headers, Arial font, borders, and US dollar, date, and accounting formats.

Use the format painter in Excel to copy formatting from a source cell to headings and data, including applying bold and using double-click for multiple locations.

Master working with rows and columns in Excel, including inserting and deleting multiple rows or columns, using shortcuts, and hiding, unhiding, and locating blank cells with go to special.

Master cut, copy, and paste in Excel for accounting & finance, using right-click options and keyboard shortcuts (Ctrl C, Ctrl V, Ctrl X) to move or duplicate data efficiently.

Master Excel's paste special to paste values, formulas, formats, and more, and use options like transpose and multiply with shortcuts such as Alt+E+S.

Learn to delete and clear cells in Excel, shift cells left or up, delete rows or columns, and use clear commands for all data, formats, contents, comments, notes, and hyperlinks.

Master how Excel aligns text, numbers, and dates with horizontal and vertical alignment, orientation, indent, wrap text, and merge options to format data efficiently.

Learn to build Excel formulas using cell references, operators, and parentheses, and see why formulas update automatically instead of hard coding numbers.

Learn how Excel follows the standard order of operations (bodmas) to evaluate formulas, from brackets and exponents to division, multiplication, and addition, with step-by-step checks using the Evaluate Formula feature.

Learn to audit formulas with F2 and F5 in Excel to navigate between linked cells across worksheets and highlight references, using double-click and F5 to move and return.

Enter data and formulas efficiently by using enter, ctrl+enter, and tab to control cell navigation, and customize after-enter behavior in Excel options.

Learn how absolute and relative cell referencing in excel shapes formula behavior, including copying formulas with the fill handle and using f4 to fix references for consistent results.

Master Excel's golden rule: put changing inputs in a cell and reference them in formulas to keep forecasts automatic, contrasting hard-coded growth-rate values with cell references.

Explore predefined excel functions, learn syntax and arguments, and see how sum adds ranges like a1 to a10. Excel 365 offers more than 500 built-in functions and insert via fx.

Learn mixed cell referencing in Excel to write efficient formulas with relative, absolute, and mixed references, using F4 to toggle dollars and Ctrl+Enter to fill ranges.

Explore how circular references arise in Excel, how to detect them with error check and the status bar, and how iterative calculations with a 100-iteration limit resolve them.

Use text to columns in Excel to transform data from text documents and PDF files into organized columns, preview, and finish by selecting delimited or fixed width with comma delimiter.

Learn how to use Flash Fill in Excel to extract first and last names, generate emails, format dates, numbers, and capitalization with Ctrl+E, Ctrl+D, noting it's not dynamic.

Learn to use autofill in Excel to quickly populate cells with patterns, months, days, and number series, create custom lists, and customize fill options.

Create drop-down lists in Excel by applying data validation to a cell range, selecting list criteria, and sourcing items to restrict data entry to the listed options.

Learn to use Excel's custom sort to sort by volume from largest to smallest, then by price per unit from largest to smallest, expanding the selection to keep rows intact.

Learn to use Excel's select special to quickly locate cells by criteria, such as formulas, constants, blanks, objects, conditional formatting, and data validation, by selecting a range and pressing F5.

Learn how to use freeze panes in Excel to keep headings visible while scrolling, including freezing the top row, freezing the first column, and unfreezing as needed.

Explore conditional formatting in Excel to visually highlight data by criteria, using rules like greater than, between, top/bottom, and formulas, with options such as data bars, color scales, and duplicates.

Create custom number formats in Excel using hash and zero placeholders to control digits, apply thousand separators, and display negative numbers in red with brackets and zero values as dashes.

Apply custom formatting in Excel to display financial model values with symbols while preserving underlying numbers for calculations; use date and forecast codes such as F, A, and FY.

Learn how to insert and manage hyperlinks in Excel to navigate between sheets, link to files, webpages, and emails, and how to remove hyperlinks.

Master find and replace in Excel to update data and references across worksheets and workbooks using Ctrl+H, with options for specific ranges, matching whole cells, and adjusting formulas.

Learn how to use find and replace to change formatting across an entire workbook in Excel, including selecting from formatting, applying a replacing format, and updating headings to dark blue.

Remove automatic green arrows in Excel by ignoring the error for individual cells or by disabling background error checking in formulas options, keeping your workbook professional.

Learn to use Excel's filter tool from the data tab to filter by date after a given date, text and number filters, and color, with custom filters available.

Create dynamic names in Excel by combining fixed text with input cell references, so headings and currency units update automatically as company name or currency changes.

Define and apply named ranges in Excel to simplify formulas, reduce errors, and reuse across sheets, using name box, define name, and name manager.

Learn to set print areas in Excel to tailor printouts, apply borders, and adjust page size and margins from the page layout tab before printing.

Learn to apply three dimensional formulas in Excel for accounting and finance to sum data across multiple worksheets from the first to the last sheet using a colon.

Master data manipulation shortcuts in Excel, including copy, paste, paste special for values, formulas, and formats, plus autosum, transpose, autofill, and find and replace.

Master data selection and navigation in Excel using ctrl and arrow keys, shift selections, and range shortcuts; insert, delete, and adjust rows and columns with ctrl, alt, and ribbon shortcuts.

Master excel formatting shortcuts to apply context-aware formats quickly. Use Ctrl+1 for format box, Ctrl+Shift+exclamation mark, at sign, hash sign, and dollar sign for numbers, time, date, and accounting formats.

Explore essential Excel editing shortcuts, including F2 edit mode, F4 toggle of dollar signs, Ctrl+Enter for ranges, and Alt+Enter for multiline cells.

Explore essential file and application shortcuts in Excel, including open, new, save, save as, switch between workbooks, and insert new worksheets using Ctrl+O, Ctrl+N, Ctrl+S, F12, Ctrl+Tab, Ctrl+Shift+Tab, and Shift+F11.

Master the sum function and autosum in Excel to total values across ranges, using cell references and values, while ignoring text in ranges and leveraging the shortcut Alt + =.

Explore how the average function in Excel computes the average of a cell range, including ignoring empty cells and text, and apply it across regions and months.

Learn how to use Excel's count function and related functions to count numbers, non-blank cells, and blanks, with dates treated as numbers, and view quick results on the status bar.

Learn to apply the min and max functions in Excel to find the smallest and largest scores in a range, while ignoring text, blanks, and logical values.

Learn to use the large and small functions in Excel to find the third highest and second lowest scores, with syntax, arrays, and the k parameter.

Explore how to use the rank function and new rank functions in Excel to rank scores, handle duplicates with rank.eq and rank.avg, and sort in descending order.

Learn how the Excel aggregate function acts as a multi-tool for 19 operations, including max and sum, with options to ignore hidden rows and errors in real data scenarios.

Learn to use the round function in Excel to round numbers to a chosen digit count. See how it rounds to decimals or to nearest ten, hundred, or thousand.

Learn to use Excel's index function to return a value at a given row and column from one or more arrays, with an area number to select between datasets.

Learn to use the Excel match function to locate a lookup value’s position, with syntax including lookup value, lookup array, and match type: exact, approximate, or wildcard, plus ascending/descending notes.

Explore XMATCH in Excel, an upgraded lookup function that returns a value's position in a range using exact, next largest or smallest, wildcard, and regex modes, plus flexible search.

Learn to perform lookups with the index and match combination, a faster alternative to vlookup, including right-hand and horizontal data scenarios, and building a single formula.

Multiply numbers with the Excel product function and sum products with sumproduct across matching ranges; use addition, subtraction, or division as shown in the wages example.

Demonstrate the Excel if function by performing a logical test and returning true or false values, with nested if for grade allocations and examples in tax and balance sheets.

Explore the IFS function in Excel, upgraded from the traditional if function. See how to set up logical tests, true values, and a final true test for defaults in grading.

Learn how the choose function selects a value from a list by index, enabling inflation scenario switches in financial models with high, medium, and low inputs.

Master sumif, averageif, and countif in Excel for accounting and finance. Set range, criteria, and sum range with examples using names and dates, wildcards, and not equal.

Develop proficiency with sumifs, averageifs, countifs, minifs, and maxifs to compute data by multiple criteria in Excel, with syntax and real sales examples.

Explore excel database functions such as d sum, d count, d max, d mean, and d product to aggregate values by criteria, with database, field, criteria syntax, and or logic.

Learn Excel error types and how to handle them using iferror and ifna, trap errors in formulas, and display helpful messages for common issues.

Master text functions in Excel to clean and manipulate data. Learn to join, format, and extract text using char, trim, exact, concat, text join, left, right, mid, and len.

Explore date functions in Excel for accounting and finance, including date value, day, month, year, text, week, edate, end of month, today, days, date diff, workday, and network days.

Master VLOOKUP and HLOOKUP in Excel to retrieve values from tables by row or column, use exact and approximate matches, return multiple columns, and handle errors with IFERROR.

Learn how the Xlookup function in Excel replaces Vlookup and Hlookup by performing vertical or horizontal lookups with separate lookup and return arrays, exact results, wildcards, and built-in not-found handling.

Explore Excel formula auditing to identify formulas with a true/false test, use error checking, evaluate formulas, and leverage trace dependence and precedence with the watch window.

Master goal seek in Excel to determine break-even and hit profit targets by changing a single variable, such as units sold, while exploring revenue, cost, and tax implications.

Learn to perform sensitivity analysis in Excel with one-way and two-way data tables, tracking net income and revenue as price and cost per unit vary.

Sort data by region, apply subtotals with sum for units and sales, and display a summary below data; explore multi-level subtotals by region and by salesperson across tabs.

Learn to create and name Excel tables, convert data ranges, and use structured references to simplify formulas, auto expand with new data, and connect to charts and pivot tables.

Learn to consolidate data from multiple workbooks in Excel using the consolidate command, with the sum function and headers, and dynamic links to keep results updated.

Learn how to use Excel's time value of money functions, including pv, fv, pmt, rate, ipmt, and input, to analyze loans, compute present and future values, and build amortization schedules.

Use Excel's NPV function to calculate net present value by discounting cash flows at a given rate, handling the initial investment separately in capital budgeting.

Learn the xnpv function to discount irregular cash flows using exact dates, compare it with npv and pv, and set the earliest date as time zero for precise valuation.

Explore the IRR function to compute internal rate of return for regular cash flows, compare it with the discount rate or WACC, and use NPV to decide on projects.

Apply the XIRR function to irregular cash flows by supplying actual dates, even if unordered, and compare its result to NPV to verify zero net present value.

Explore the MIRR function in Excel by applying a finance rate for borrowing and a reinvestment rate for reinvested cash flows to obtain a more accurate rate than IRR.

Compute the payback period in Excel by analyzing cumulative cash flows, the initial investment, breakeven point, and using formulas such as sum, match, and offset to automate the calculation.

Explore how Excel's depreciation functions—straight-line, declining balance, double declining balance, sum-of-the-years-digits, and variable declining balance—calculate yearly depreciation using cost, salvage value, life, and period.

Learn pivot tables in Excel to summarize and analyze data, filter results, group by categories, and create charts. Understand their flexibility and time savings, plus performance and customization limits.

Create pivot tables in Excel to summarize sales data by dragging fields into rows, columns, and values, then format numbers for dynamic, expandable reports.

Learn to format pivot tables in Excel by applying built-in designs or creating custom pivot table styles, and adjust layout options such as subtotals, grand totals, and report formats.

Modify and pivot fields in pivot tables to customize layout, refresh data after source changes, and rename value fields for clear sales and units reporting.

Learn to use slicers in pivot tables to filter data by fields like customer and region, and insert slicers via the pivot table analyze tab for easy, user-friendly filtering.

Learn how the GetPivotData function retrieves specific data from a pivot table by field and item criteria, adapts to pivot structure changes, and supports cell references.

Learn how to insert and customize charts in Excel by selecting data, choosing from recommended or all chart types, and changing chart types to best describe your data.

Learn to edit charts in Excel using the chart design and format tools, add axis titles, data labels, legends, and switch rows and columns to update the data range.

Master chart formatting in Excel by customizing fonts, chart area fills, borders, grid lines, data labels, bar colors, gap width, and axis scaling to create a professional, readable financial chart.

Create a donut chart in Excel to visualize 2024 sales for three products, then adjust the donut hole size, colors, and data labels.

Learn to create area charts in Excel, including 2D stacked charts, by selecting year data, inserting the chart, and formatting colors, fonts, and the chart area.

Learn to create a combined area and line chart in Excel, displaying revenues on the area series and gross margin on a line series with a secondary axis.

Create a column stack chart with a secondary line axis in Excel to display net sales, COGS, and net income, with gross margin as a line.

Learn to create bullet charts in Excel to compare actual versus target values by using a clustered column chart with a secondary axis and precise formatting.

Learn to create a Gantt chart in Excel for project management, using a stacked bar chart, and formatting axes, reversing categories, and formatting dates.

Create bridge charts in Excel to visualize the change from EBITDA FY21 to FY22 with revenue, variable costs, and OpEx, using a waterfall format.

Build a football field chart in Excel to compare valuation methods—52 week trading range, equity research, DCF, and comparables—using min, max, and a current price line.

Create treemap charts in Excel to visualize revenues by country and city in USD million. Insert a hierarchy chart, customize the title, and format the treemap.

Insert sparklines in Excel to show trends inside cells, choosing line, column, or win-loss types, and highlight high/low points and negative values with adjustable markers and colors.

Power Query in Excel transforms and cleans data from multiple sources, enabling ETL through getting and transforming data, and loading results into Excel, PowerPivot, or Power BI.

Master Power Query in Excel to pull data from web, PDF, and folders, transform and clean it, refresh updates, and combine multi-year data for reporting.