Microsoft Excel 2016 Beginners & Intermediate Excel Training

Explore the structure of Excel sheets, including the ribbon, workspace, cells and rows, column headers a to z, and the name box and formula bar for editing.

Explore the seven Excel 2016 ribbon tabs from home to view. Use the tell me what you want to do search and learn basics like pivot tables and charts.

Learn to work with rows and columns in Excel by selecting, resizing by dragging, inserting or deleting with right-click, and using Ctrl+Space for columns and Shift+Space for rows.

Learn to enter, edit, and delete text and numbers in Excel by selecting cells, using enter or tab to move, canceling with escape, and using F2 to edit.

Format cells in Excel 2016 from the Home tab or right-click, adjusting fonts, sizes, borders, fills, font color, and alignment to emphasize the title row and create a neat table.

Learn to create formulas in Excel using numbers or cell references with the equals or plus sign, and use plus, minus, star, forward slash, and parentheses.

Learn how to insert and use Excel functions, focusing on the IF function, using the wizard or formula bar, and define the logical test with positive and negative outcomes.

Learn to copy, cut, and paste in Excel using Ctrl+C, Ctrl+V, Ctrl+X, or right-click options. Paste into the top-left cell to place copied cells, with marching ends signaling actions.

Discover how to format cells in Excel 2016 using number, currency, and date formats, apply decimals and colors, and master the Format Cells feature.

Learn how to use paste special to paste formulas, values, and formats from copied cells, and explore the paste special dialog options.

Format a new Excel sheet professionally by setting a white background, Arial 9 font, a narrowed first column, and a bold dark blue title in cell B1.

Master fast scrolling in Excel by using control+arrow to reach the last non-blank cell and shift+control+arrow to quickly select large ranges, enabling efficient navigation through large spreadsheets.

Learn how to fix cell references in Excel with dollar signs to keep values constant when copying formulas across cells or down rows. This improves modeling accuracy and sheet organization.

learn to insert a line break in a single excel 2016 cell with alt+enter, placing the second part on a new line to save space and organize your sheet.

Learn to organize data in Excel with the text to columns tool, choosing delimited or fixed width criteria to split cells into columns, creating clean, usable data.

Learn to format text in Excel using the wrap text feature and auto-adjust row height to keep content neatly inside cell borders.

Set a print area in Excel to create printable documents by selecting which parts of each sheet to print, using page layout and the set print area option.

Explore how to find and select special types of cells with the select special dialog (F5) in Excel, choosing blanks, formulas, constants, or comments, then paste not available into blanks.

Learn to assign dynamic names to cells in a financial model by linking company names and text to the input page, using the and function to update when inputs change.

Name cell ranges such as sales 12 and sales 13, then use these named ranges in formulas to compute year totals.

Master custom formatting in Excel 2016 by applying thousands separators, displaying negative numbers in brackets, converting zeros to dashes, and using # vs 0 placeholders across four format positions.

Assign custom formats to numbers in Excel to display multiples while keeping cells numeric, using format cells and selecting custom, and typing a format like 0.0 x.

Enable the developer tab, record macros on a single sheet, and use the recorded formatting macro on new sheets to save time.

Create a drop-down list in Excel 2016 using data validation to restrict entries in a cell range, with validation criteria and error alerts.

Learn to sort a table by a custom criterion using Excel's custom sort, selecting the whole table, choosing the volume column, and sorting from largest to smallest.

Create a professional index page at the beginning of a financial model by inserting internal hyperlinks that jump between sheets using the insert tab.

Learn how to freeze the title row in Excel using freeze panes to keep column headers visible as you scroll, and how to unfreeze when needed.

Discover how to use the freeze panes command and quickly locate it in Excel 2016 with the Tell Me box by typing freeze panes.

Master keyboard shortcuts in Excel to save time by using universal Windows shortcuts such as Ctrl+C, Ctrl+V, Ctrl+X, and explore Alt shortcuts and the Quick Access Toolbar.

Master key Excel count functions, count, counta, countif, and countifs, using ranges and conditions to count numbers, text, and entries such as teams with more than 60 points.

Learn how to use Excel's sum, sumif, and sumifs formulas to add numbers in a range and apply criteria for Italian and English teams in the Champions League.

Learn to use the average and averageif functions to compute the arithmetic mean and conditional means. Apply ranges, criteria, and result ranges to analyze teams and Champions League participation.

Learn to manipulate text in Excel using left, right, and mid to extract characters from strings. Convert case with upper, lower, and proper, and concatenate multiple text strings efficiently.

Use the max and min functions to identify the highest and lowest values in a range, demonstrated with a 4 to 12 range and scores like 90 and 27.

Learn to use the round formula in Excel 2016 to round numbers to a chosen number of digits for modeling purposes, understanding the two arguments: the number and the digits.

Learn how to use VLOOKUP and HLOOKUP to transfer data between tables, including exact and closest matches, and fix references when the lookup value sits in the first column.

Master index and match as a powerful substitute for VLOOKUP, using array lookups to find exact positions and retrieve values from tables.

Explore why vlookup falls short and how xlookup offers a cleaner, flexible replacement in Office 365, comparing lookup value, lookup array, and return array with exact and closest match options.

Learn how to use the IFERROR function to handle missing data in a percentage calculation, displaying not available when errors occur, and applying it across a range.

Learn how to create dynamic pivot tables in Excel 2016 to summarize large data sets, counting and totaling volumes by year and product group.

Use the choose function in Excel to build flexible financial models and scenarios. Switch among optimistic, base, and worst-case revenues by changing a single index.

Explore how Goal Seek, a what-if analysis tool in Excel, finds a variable cost value to achieve a desired gross margin, demonstrated with a simple revenues example.

Explore how to use data tables in Excel 2016 to perform sensitivity analysis, showing how interest rate and loan term affect the amount repaid in a model.

Create charts in Excel 2016 to visualize and understand numbers, explore new charts like waterfall, tree map, sunburst, and histograms, and learn to create combined charts from basics.

Insert charts in Excel 2016 by selecting data, using the insert tab and recommended charts to visualize data with line, column, bar, and pie types, and change chart types.

Customize Excel 2016 charts using the chart tools ribbon, design and format tabs, and the add chart element options for titles, data labels, legends, and trend lines.

Learn to format Excel charts in Excel 2016 by customizing fonts, colors, grid lines, format data series, and axis scales to create professional, visually appealing charts.

Create bridge charts (waterfall charts) in Excel 2016 with a simple step-by-step: select data, insert a waterfall chart, and set the final data point as total to reveal EBIDTA.

Learn to create a tree map chart in Excel 2016 using two data categories country and city to visualize tourists across cities with steps from selecting data to inserting the chart.

Explore spark lines to visualize data trends within a single cell, choosing line or column types, customizing markers and styles, and quickly assessing sales patterns across agents.

Explore building a profit and loss statement from raw data and applying core financial modeling concepts in Excel 2016. Learn formatting to showcase work in corporate finance.

Open the FY2016 sheet to examine P&L items, track partner company numbers and external transactions, note revenue negative and costs positive, and verify year-to-year data alignment for consolidation.

Order the worksheet and apply filters to structure data; rename columns as P&L account, name of partner company, amounts, and account number, align left and remove total rows.

Learn to create a unique code by combining P&L account and partner company in Excel 2016, copy formulas down, paste values, and organize data across three years.

Consolidate codes from 2016, 2017, and 2018 into a single sheet, then remove duplicates in column b to avoid double counting when using sumif for profit and loss categorization.

Apply vlookup to transfer P&L codes across years, fix source table references, and copy formulas to populate the P&L account, partner company number, and partner name for 2016–2018.

Use sumif to populate FY16–FY18 P&L amounts in three year columns using codes as criteria with reversed signs. Verify each year totals to zero and note net income code changes.

Learn how to replace vlookup with index and match in excel, preserving sheet layout while performing exact-match lookups across fy16 to fy18 p&l accounts.

Master XLOOKUP as a replacement for VLOOKUP and INDEX/MATCH in Office 365, able to look up values to the right and across multiple years, with NA handling.

Map database line items to P&L categories, creating concise, coherent profit and loss statements by grouping smaller accounts into macro categories across three years in a single database.

Learn how to build a P&L statement by organizing categories, removing duplicates, and calculating revenues, gross margin, operating expenses, EBITDA, D&A, EBIT, EBT, and net income.

learn how to make a p&l look clear in excel by applying borders, colors, and bold headings, emphasizing ebitda and net income, and aligning sums for accurate reporting.

Use the sumif function to populate the p&l sheet from the database, fixing the criteria range for year-by-year copying and converting figures to millions.

Use COUNTIF to verify your SUMIF results by comparing the P&L range against the database, identify zero lines among mapping lines, and copy the right classification word to fix discrepancies.

Set up two columns for year-on-year percentage variations, copy a universal formula, apply paste special formats, and calculate percentage incidences on total revenues for gross margin, EBITDA, and EBIT.

Open the exercise Excel file, review input data like period, client details, and revenues, and use the right sheets to create tables and charts answering each task.

Learn to apply sumifs in practice with large transaction data, building multi-criteria revenue tables by product group, period, and store type, using fixed references for efficient Excel analysis.