
Introduces Excel 2021 as a standalone release with features from Microsoft 365, guiding Excel novices through 85 bite-sized lessons across 17 sections, with downloadable course files, exercises, and quizzes.
Watch this video to learn essential information for maximizing your training, including downloadable exercise and instructor files, how to download and unzip them, and adjusting playback.
Compare Excel 2021 and Excel for Microsoft 365 to decide between a one-time purchase and a subscription, covering price, updates, up to five devices, and online access.
Launch Excel from the start menu or taskbar, use the search bar or all apps list, and pin or reorder icons with right-click and drag.
Explore the start screen to access home, new, open, and pinned items, templates, and recent documents. Learn how to customize Excel with options, account settings, and templates for efficient work.
Explore the Excel interface, create a blank workbook, and navigate the ribbons, quick access toolbar, and name box to understand worksheets, cells, and formulas for confident data work.
Explore how tabs, ribbons, and menus organize Excel commands into logical groups, reveal tooltips and keyboard shortcuts, and use contextual menus and the mini toolbar to streamline formatting tasks.
Access the backstage area from the file tab to create, open, save, print, share, export, or publish to Power BI service, and manage protections, metadata, and unsaved recovery in place.
Customize the quick access toolbar in Excel by adding frequently used commands, showing labels, and rearranging items under the ribbons with separators.
Boost your Excel efficiency by mastering essential keyboard shortcuts, from save, cut, copy, paste, undo, and navigate with arrows to using alt to access ribbons.
Learn how to get help in Excel using screen tips, tell me more, and the F1 system, plus Alt Q shortcuts and contextual help for formatting tables and cells.
Launch Excel, pin it to the taskbar, customize Quick Access Toolbar with AutoSum, Wrap Text, and Fill, add a separator, and practice creating a blank workbook with a keyboard shortcut.
Use in-built and personal Excel templates to quickly create invoices and budgets. Browse, preview, customize, and save templates as reusable files with automatic calculations.
Explore how a workbook contains multiple worksheets with cells defined by column letters and row numbers. Rename, insert, move, copy, and color-code tabs to organize worksheets, and use shift f11.
Learn how to save workbooks in Excel 2021, including save vs save as, use the quick access toolbar and Ctrl+S, and review AutoRecover options and unsaved recovery.
Enter and edit text, numbers, dates, and formulas in Excel cells, use the formula bar and keyboard shortcuts, and leverage autofill and copy-paste to populate data.
Master advanced cell, row, and column selections in Excel, including contiguous and non-contiguous ranges, with keyboard shortcuts like ctrl+down arrow, ctrl+shift+down arrow, and ctrl+A for efficient formatting.
Practice creating an invoice template, saving to the default templates folder, then build a multi-sheet workbook with summary, sales data, calculations, and charts, and enter 2020 sales data.
Master formulas and functions in Excel by using cell references, the sum function, and the over 500 functions library to build dynamic calculations with proper order of operations.
Explore the sum function in Excel to add a range of numbers. Use direct typing, the insert function dialog, and auto sum, and understand warnings about omitted adjacent cells.
Master how to use Excel count functions to tally items, count numbers, count all non-empty cells with counta, and count blanks with count blank, illustrated by student scores.
Learn to compute averages with the average function by selecting ranges, using IntelliSense or dialog, and applying Ctrl+Enter. Copy formulas down with Autofill and explore relative, absolute, and mixed referencing.
Master the min and max functions in Excel to identify the lowest and highest values in a range, copy formulas efficiently with Ctrl+D, and analyze monthly sales data.
Learn to identify and fix common Excel formula errors using error indicators, the formula auditing tools, and step-by-step evaluation to keep spreadsheets accurate.
Explore how absolute and relative referencing work in Excel, learn to lock a cell with F4, and apply correct references for calculating bonuses and salaries.
Explore autosum and auto fill in a practical Excel workbook, learn formulas, copy across rows, fill series, dates, and custom lists with smart tag options.
Use Flash Fill in Excel to split and combine data across columns by pattern, introduced in 2013. Explore examples extracting initials, emails, last three characters, and product codes without formulas.
Practice core Excel formula skills: compute total sales and tax from quantity and price, apply absolute referencing, and use average, min, max, and count A on customer data.
Discover how named ranges in Excel assign meaningful names to cell groups, making formulas clearer and navigation quicker across workbooks.
Learn four methods to create named ranges in Excel: name box, from selection, name manager, and define name, with no spaces, underscores for multiword names, and absolute references.
Open the name manager from the formulas tab to view and edit named ranges, change scope to the workbook, and add, delete, or filter ranges by errors or defined names.
Learn to use named ranges in Excel formulas for average, count, min, max, and sum, and use F3 to view names and handle deleted ranges.
Practice creating and using named ranges in a customer data worksheet, including tax rate, then update formulas to use these ranges for totals, averages, minimum price per item, and counts.
Apply number formats in Excel with the home ribbon, including general, number, currency, accounting, and percentage, adjust decimals, and learn how formatting affects display versus the underlying value.
Apply date and time formatting in Excel by converting numbers to short and long dates and times, while adjusting locale settings and preserving the underlying date values.
Format worksheets to improve readability by auto fit row heights and column widths, applying date and currency formatting, and using borders and themed fonts to differentiate headings and data.
Copy formatting quickly across cells with format painter by selecting a formatted cell, toggling the painter on, and painting across dates, descriptions, and amounts, with double-click to reuse repeatedly.
Apply short date, accounting, and percentage formats, compute sales tax and total with formulas, then enhance the worksheet with bold titles, green headings, and dark green borders.
Format worksheets by resizing and auto fitting rows and columns, and inserting, deleting, or hiding them. Use keyboard shortcuts like ctrl+shift+plus and go to special to remove blank rows.
Delete values by selecting cells and pressing delete, or delete a cell to shift up or left. Choose clear options to remove contents, formats, comments, and hyperlinks.
Master how to align numbers, dates, and text in Excel cells using the home ribbon alignment tools, plus wrap text, indent, and merge options for headings and data.
Explore built-in themes on the page layout tab, use live preview to compare theme colors and fonts, and save custom themes for consistent workbooks across new files.
Format the customer data tab to match reference: set column width to 25, apply date and currency formats, center, and add borders; apply theme 7 with Cambria and violet two.
Learn to structure Excel lists as flat data or tabular formats, with rows as records and columns as fields, to enable effective pivot tables, sorts, and filters.
Master single level sorting in Excel: sort a column by text or numbers (A to Z, smallest to largest) and use color or formatting cues to prioritize rows.
Learn to perform a multi-level sort in Excel by arranging region, country, and sales amount. Use the data tab, the sort dialog, add level, and reorder or copy levels.
Learn to sort data with custom lists in Excel, arranging order such as North America, Asia, and Europe, and use the unique function to build country lists for precise sorting.
Apply autofilter to your data via the home or data tabs, filter by column, text, or number (with ctrl+shift+l), and clear all filters to return to the full dataset.
Format data as an Excel table to enable auto expansion, filter and sort, and use the table design ribbon or Ctrl+T for fast setup and analysis.
Learn to add subtotals in Excel by using the data ribbon's subtotal button or the subtotal formula, grouping by region and totaling sales amounts, and control display with collapse options.
Format the customer invoices as invoices_2020, sort by month with a custom list, then by invoice total and status, filter over 3000 with past due, and apply subtotals by month.
discover the difference between cut and copy, learn how cut moves data and copy preserves it, and paste across Excel and other apps using keyboard shortcuts ctrl+x, ctrl+c, and ctrl+v.
Master the full range of paste options in Excel, from paste formulas and paste values to paste formatting, paste column widths, and transpose via paste special, with practical hands-on techniques.
Learn how the Excel clipboard stores up to 24 cut or copied items in a left pane, and paste selectively, paste all, or clear all with auto-show options.
Link data from the same worksheet, other worksheets, and even another workbook using formulas to create a dynamic summary that updates automatically whenever source figures change.
Learn how to perform 3D referencing across multiple worksheets with the same structure to sum or average values, such as monthly sales totals across countries.
Insert hyperlinks to navigate between worksheets and workbooks, forming a table of contents. Use text, screen tips, and icons to jump to UK, USA, Japan, China, and totals.
Practice paste options, preserve column widths, paste values, and transpose data; create subtotals for client profit, link to the summary sheet, and apply 3D referencing for totals.
Learn to use vlookup to search the leftmost column of a table array and return a value from a chosen column with exact-match.
Master vlookup with approximate match for age ranges to return app names and daily hours, using absolute references, named ranges, and converting data to an Excel table.
Wrap vlookup results with iferror and ifna to display part not found or price not found, using a named parts catalog and exact match.
Explore basic logical functions in Excel, including if, and, or, to perform tests that return true or false. Apply these to examples like expense approvals and shipping fee calculations.
Practical guidance on using if statements to compute shipping charges and grand totals, locking references, and copying formulas, with nested ifs and countifs and sumifs for data analysis.
Master text functions in Excel to tidy messy data by extracting left, mid, and right characters, changing case with proper, upper, and lower, trimming spaces, and concatenating values.
Explore essential time and date functions in Excel, including today, now, and hardcoded dates; use workday and networkdays with holidays, then extract day, month, year, and weekday.
Apply vlookup and lookup functions to fetch subject scores and assign grades from a range table; handle missing results with fail; tidy data and extract order numbers with text functions.
**This bundle includes practice exercises, downloadable files, and LIFETIME access**
Let us take you on a journey from being an Excel novice to an Excel expert with this amazing value 8-course training bundle. By the end of this training, you will be able to clean, summarize, and analyze data easily, as well as create PivotTables, charts, macros, and so much more!
We'll take you on a no-nonsense journey to learn specific functions, formulas, and tools that Excel has to help conduct business or data analysis. We'll also look at three advanced Excel features: Power Pivot, Power Query, and DAX. This suite of Excel functions allows you to manipulate, analyze, and evaluate millions of rows of data from Excel or other databases.
This ultimate Excel course bundle is designed for students of all levels. If you are brand new to Microsoft Excel, this course can get you started on your journey. If you already have a good understanding of Excel, you can further your skills with the more advanced courses in this bundle. This is the only Excel training you are ever going to need!
All courses include practice exercises and follow-along instructor files so you can immediately apply what you learn.
What's included?
Excel 2021 for Beginners
Become familiar with what’s new in Excel 2021
Navigate the Excel 2021 interface
Utilize useful keyboard shortcuts to increase productivity
Create your first Excel spreadsheet
Use basic and intermediate Excel formulas and functions
Effectively apply formatting to cells and use conditional formatting
Use Excel lists and master sorting and filtering
Work efficiently by using the cut, copy, and paste options
Link to other worksheets and workbooks
Analyze data using charts
Insert pictures in a spreadsheet
Work with views, zooms, and freezing panes
Set page layout and print options
Protect and share workbooks
Save your workbook in different file formats
Excel 2021 Intermediate
Designing better spreadsheets and controlling user input
How to use logical functions to make better business decisions
Constructing functional and flexible lookup formulas
How to use Excel tables to structure data and make it easy to update
Extracting unique values from a list
Sorting and filtering data using advanced features and new Excel formulas
Working with date and time functions
Extracting data using text functions
Importing data and cleaning it up before analysis
Analyzing data using PivotTables
Representing data visually with PivotCharts
Adding interactions to PivotTables and PivotCharts
Creating an interactive dashboard to present high-level metrics
Auditing formulas and troubleshooting common Excel errors
How to control user input with data validation
Using WhatIf analysis tools to see how changing inputs affect outcomes.
Excel 2021 Advanced
Using the NEW dynamic array functions to perform tasks
Creating advanced and flexible lookup formulas
Using statistical functions to rank data and to calculate the MEDIAN and MODE
Producing accurate results when working with financial data using math functions
Creating variables and functions with LET and LAMBDA
Analyzing data with advanced PivotTable and PivotChart hacks
Creating interactive reports and dashboards by incorporating form controls
Importing and cleaning data using Power Query
Predicting future values using forecast functions and forecast sheets
Recording and running macros to automate repetitive tasks
Understanding and making minor edits to VBA code
Combining functions to create practical formulas to complete specific tasks.
Excel for Business Analysts
How to merge data from different sources using VLOOKUP, HLOOKUP, INDEX MATCH, and XLOOKUP
How to use IF, IFS, IFERROR, SUMIF, and COUNTIF to apply logic to your analysis
How to split data using text functions SEARCH, LEFT, RIGHT, MID
How to standardize and clean data ready for analysis
About using the PivotTable function to perform data analysis
How to use slicers to draw out information
How to display your analysis using Pivot Charts
All about forecasting and using the Forecast Sheets
Conducting a Linear Forecast and Forecast Smoothing
How to use Conditional Formatting to highlight areas of your data
All about Histograms and Regression
How to use Goal Seek, Scenario Manager, and Solver to fill data gaps
Power Pivot, Power Query & DAX
How to get started with Power Query
How to connect Excel to multiple workbooks
How to get data from the web and other sources
How to merge and append queries using Power Query
How the Power Pivot window works
How to set up and manage relationships in a data model
How to create a PivotTable to display your data from the Power Pivot data model
How to add calculated columns using DAX
How to use functions such as CALCULATE, DIVIDE, DATESYTD in DAX
All about creating Pivot Charts and PivotTables and using your data model
How to use slicers to adjust the data you display
PivotTables for Beginners
How to clean and prepare your data
Creating a basic PivotTable
Using the PivotTable fields pane
Adding fields and pivoting the fields
Formatting numbers in PivotTable
Different ways to summarize data
Grouping PivotTable data
Using multiple fields and dimension
The methods of aggregation
How to choose and lock the report layout
Applying PivotTable styles
Sorting data and using filters
Create pivot charts based on PivotTable data
Selecting the right chart for your data
Apply conditional formatting
Add slicers and timelines to your dashboards
Adding new data to the original source dataset
Updating PivotTables and charts
Advanced PivotTables
How to do a PivotTable (a quick refresher)
How to combine data from multiple worksheets for a PivotTable
Grouping, ungrouping, and dealing with errors
How to format a PivotTable, including adjusting styles
How to use the Value Field Settings
Advanced Sorting and Filtering in PivotTables
How to use Slicers, Timelines on multiple tables
How to create a Calculated Field
All about GETPIVOTDATA
How to create a Pivot Chart and add sparklines and slicers
How to use 3D Maps from a PivotTable
How to update your data in a PivotTable and Pivot Chart
All about Conditional Formatting in a PivotTable
How to create amazing-looking dashboards
Interactive Excel Dashboards
About Dashboard architecture and inspiration
How to prepare data for analysis (cleaning data)
Useful formulas for creating dashboards in Excel
How to create and edit Pivot Tables in Excel
How to create Pivot Charts from Pivot Tables
Advanced chart techniques in Excel
How to add interactive elements (form controls) into your dashboards
How to create a Sales Dashboard from scratch
How to create an HR Dashboard from scratch
This bundle includes:
60+ hours of video tutorials
550+ individual video lectures
Course and exercise files to follow along
Certificate of completion