The topics categorized as Advanced Excel have been classed as such based on the curriculum and scope of several Excel certifying courses.
If there are other Advanced Excel topics that you would like to read about let me know.
Also, check out the Beyond Advanced Excel posts which go a step further (at least) than Advanced Excel.
Advanced Excel Topics:
Excel Tips
- & Let us begin – something to keep in mind when working with Excel
- The best way, or just another way?
- An Excel problem solving tip
- When a shortcut isn’t a shortcut
- Ctrl, Shift and arrow keys
- Brackets & Commas
- Using Wingdings
- The Scroll button
- Context Menus
- Alt Key shortcuts
Functions & Formulas
- SUBSTITUTE Function
- 3D SUM Function
- IF Function
- UPPER, LOWER & PROPER Functions
- IFERROR Function
- SUMPRODUCT
- SUMIF
- COUNTIF
- VLOOKUP
- LEN, LEFT, RIGHT & MID Functions
- Create a Planner in Excel
- Formula to get the filename
- Absolute, Relative & Mixed Reference challenge
- Absolute, Relative & Mixed Reference solution
- Wildcards in Formula
Analysis
- Advanced Filter
- Custom Sort
- Sort from Left to Right
- PivotTables
- Pivot Charts for better analysis
- Scenario Manager
- Using Charts to help with problem solving
Validating & Auditing
- Data Validation list Introduction
- Data Validation list based on range of cells
- Data Validation list to automatically update the source range
- Data Validation list based on criteria
- Dependents & Precedents – Avoid #REF!
Charts
Enhancing Productivity
VBA Macros
- Making Office work for you
- Introduction to macros
- Recording your first macro
- Understanding your first macro
- How to change text in a cell
- Format text
- Your first macro formula
- Ctrl and arrow keys
- OFFSET
- Variables
- Message Boxes & User Input
- Finishing our macro – comments, borders & column width
- Working with multiple files
- Debugging errors
- Macro Demo
- Active X Option Buttons