Excel Advanced Course Content
On this page you can find Excel Advanced Course content for offline training called Excel Advanced. The training typicaly lasts for 2 days. Some topics may be skipped up to customer demand.
Excel Course by BlueNumbers may have Workshop session - Questions and Answers with Solutions directly usefull for course attendants in company.
For arranging Excel Training, please Contact BlueNumbers Team.Interactive Content menu
Intro to Excel Changes Over Years
- Excel development milestones
- Creation of Excel (1985 for Mac, 1987 for Windows)
- Emergence of Google Sheets (2006)
- Excel 2007: introduction of the new .xlsx format (replacing the legacy .xls format)
- Microsoft 365 subscriptions (introduced in 2011, renamed from Office 365 in 2020)
- Open-source alternatives to Excel: OpenOffice (2000), LibreOffice Calc (2010), ONLYOFFICE (2009/2014)
- The future of Excel in the AI era
- The ideal goal: Excel files with low maintenance, short development time, and fast performance
Excel Advanced Course Content
- Excel Shortcuts
- Standard shortcuts (CTRL+C, CTRL+V, Shift, Control...)
- CTRL+Shift+Arrows and CTRL+Arrows for navigation and quick cell range selecting
- F4 shortcut for fixed ranges
- CTRL+TAB for Excel Windows Change
- ALT for activating menu shortcuts
- Cell Range creation
- Absoulte Fixation of cells by F4
- Relative Fixation for columns or rows by F4
- Usage of ranges like A:A and A:C
- Named ranges
- Usage of any ranges in formulas
- Excel formulas
- Basic functions (SUM, AVERAGE, MIN, MAX, COUNT, COUNTA,)
- Logical functions (IF, AND, OR, IFERROR)
- Numerical Logical functions (COUNTIF, SUMIF, AVERAGEIF)
- Text functions (LEFT, RIGHT, MID, LEN, CONCATENATE, CONCAT)
- Search functions (VLOOKUP, XLOOKUP, INDEX/MATCH, SEARCH/MATCH)
- Special functions (VALUE, TEXT)
- Work with large tables
- Data Sorting
- Base row filtering and autofilter
- Advanced filters and Subtotal Function
- Freeze Panes and Freeze top row
- Delete duplicates
- Group and Ungroup rows
- Cleaning Data
- Text to Columns function
- Number saved as Text or Numerical Value
- Saving as .csv vs. .xlsx and .xls
- Conditional Formatting
- Pivot Tables
- Base Pivot Tables
- Advanced Pivot Tables
- Various formatting of Pivot Tables
- Pivot table in Tabular Form
- Copying data from Pivot Tables
- Formulas for extraction of values
- Pivot Charts
- Slicers - Button and Timeline
- Refresh Pivot Tables
- Graphs and Charts
- Creation of Graphs / Charts
- Graphical formatting of Charts
- Sparklines and Conditional Formatting
- Power Query and Power Pivot
- Load data using Power Query
- Understanding Power Query Logic
- Power Query editor
- Refresh Power Query
- Macros and VBA
- Difference Macros vs. Power Query
- Record Macros
- Own VBA functions
- VBA editor
- VBA code creation by AI
- Other Tips
- Data Validation Rules
- Dropdown box creation
- Pasting data as Values, as Formulas, as Formatting, and other choices
- Sharepoint / Office 365 issues
- Size and Speed Limits, Make Excel files work quicker
- Shortcuts and Formulas Across Sheets and Excel Files
- HTML shortcuts in Excel
- Safety and Password Protection
Download Course Training Materials
Author: Robert Durec
Editor: BlueNumbers Team
Back to HomePage
