Recent Blog posts
- How to calculate Weighted Average in Excel (4 easy examples)
- How to copy file with VBA in Excel (9 examples)
- How to create multiple folders at once with Excel
- How to automate Excel with reusable Office Scripts
- Don’t trust data validation in Excel!
- Change number format based on a cell value in Excel
- How to create a Jitter Plot in Excel (100% dynamic)
- How to use Table slicers for advanced interactivity in Excel
Downloads
All the example files, cheat sheets, tools and templates used in our articles are available for download. Sign up for our Insider’s program and gain access today.
Topic areas
What do you want to discover today?
Charts, Data Visualization & Dashboards
Change number format based on a cell value
How to create a Jitter Plot in Excel (100% dynamic)
How to use Table slicers for advanced interactivity in Excel
How to use slicers with PIVOTBY, GROUPBY & FILTER in Excel
How to make a Dumbbell Dot Plot in Excel (100% dynamic)
How to create chart data with Power Query
How to make cross filter visuals in Excel (amazing interactive visuals)
How to make an interactive view-only dashboard from Excel
How to make Waffle Charts in Excel that work EVERYWHERE!
How to make Waffle Charts in Excel: The EASIEST way
How to create QR codes in Excel for FREE (3 easy ways)
How to create dynamic chart legends in Excel
How to create a Sankey diagram in Excel
How to show hidden data in Excel chart
How to forecast seasonality in Excel (the easy way)
How to create a step chart in Excel
Switch chart between monthly and quarterly
Create dynamic chart titles with custom formatting
How to set chart axis based on a cell value
Variable width column charts and histograms in Excel
Creating custom Map Charts using shapes and VBA
Highlight specific bars in a bar chart
How to format multiple charts quickly
Create a tolerance chart in Excel
How to color alternate lines in Excel
The fastest way to create Bullet Charts
Using the FORECAST function with seasonality
5 rules for a dashboard color palette
Data Validation
Don’t trust data validation in Excel
How to use Excel Table within a data validation list (3 ways)
How to loop through each item in Data Validation list with VBA
Formulas & Functions
Change number format based on a cell value
How to use Table slicers for advanced interactivity in Excel
How to use slicers with PIVOTBY, GROUPBY & FILTER in Excel
EOMONTH function in Excel (How to + 8 examples)
How to use SUMIFS with arrays (Excel problem solved!)
Calculate quarter from dates in Excel (inc non-standard calendars)
How to spill multiple FILTER functions in Excel
How to FILTER by a list in Excel (including multiple lists)
Automatic commentary writing formula in Excel – Amazing LAMBDA
INDEX MATCH MATCH in Excel (How to do 2-dimension lookup)
Advanced dynamic array formula techniques (3 methods)
How to use dynamic arrays with other features (7 scenarios)
RANDARRAY function in Excel (How to + 4 Examples)
SEQUENCE function in Excel (How to + 5 Examples)
FILTER function in Excel (How to + 8 Examples)
SORTBY function in Excel (How to + 6 Examples)
SORT function in Excel (How to + 6 Examples)
UNIQUE function in Excel (How to + 6 Examples)
Dynamic arrays in Excel – Everything you NEED to know
How to change images based on cell values (3 ways)
How to calculate CAGR in Excel (5 easy ways)
How to remove spaces in Excel (7 simple ways)
How to add double quotes in Excel formula
Excel formulas not calculating? 14 reasons & how to fix it
How to calculate Top 10 with formulas in Excel
VLOOKUP: What does True/False do? (How to avoid errors!)
How to reference another workbook without opening: 5 DANGERS!
How to sum across multiple sheets in Excel – simple method
Excel rounding vs Power Query rounding: WARNING! They are different
GETPIVOTDATA vs CUBE functions
How to Interpolate in Excel: 4 simple ways
How to subtract dates in Excel to get days
Excel can calculate the wrong results: WARNING
Using Slicers with dynamic array formulas in Excel
How to calculate weighted Average in Excel
How to list duplicate values (or unique values) in Excel
Excel’s AVERAGE function – the hidden pitfalls
The real reason INDEX/MATCH is better than VLOOKUP
AGGREGATE: The best Excel function you’re not using
Excel formula to check if a list is sorted
Understanding basic array formulas
VLOOKUP: List all the matching items
VLOOKUP: Lookup the nth item (without helper columns)
Automatically expand the VLOOKUP data range
Automatically run a Macro when opening a workbook
VLOOKUP with multiple criteria
VLOOKUP: Change the column number automatically
Turn a string into a formula with Evaluate
Number formatting
Change number format based on a cell value
How to create dynamic text in Excel (TEXT + Number Formats)
Excel number formats for accounting & finance you NEED to know
How to display fractions in Excel (the easy way)
Office Scripts
How to automate Excel with reusable Office Scripts
How to create QR codes in Excel for FREE (3 easy ways)
Move data between workbooks (Power Automate+Office Scripts)
How to create user forms in Office Scripts (4 ways)
How to create nice looking Office Scripts button (3 ways)
How to email or save Excel images with Power Automate
How to run Power Automate from Excel with Office Scripts or VBA
6 ways to make Excel sheets very hidden (invisible)
Power Automate CSV to Excel (via Office Script: Easy Method)
Office Scripts – Sort sheets alphabetically
Use Office Scripts with Power Automate
Office Scripts – Hide all sheets except one
Office Scripts – Reverse number signs
Office Scripts – Workbook & worksheet protection
Office Scripts – Working with worksheets
PivotTables
How to run Excel macros from Power Automate Desktop
How to create a PivotTable from multiple Tables (easy way)
GETPIVOTDATA vs CUBE functions
Clear old items from a PivotTable filter list
Power Automate (Online & Desktop)
How to run Excel macros from Power Automate Desktop
Move data between workbooks (Power Automate+Office Scripts)
How to email or save Excel images with Power Automate
How to run Power Automate from Excel with Office Scripts or VBA
Use Office Scripts with Power Automate
Power Automate CSV to Excel (via Office Script: Easy Method)
Power Pivot
How to create a PivotTable from multiple Tables (easy way)
GETPIVOTDATA vs CUBE functions
PowerPoint
Controlling Powerpoint from Excel using VBA
Powerpoint VBA Reference Library
Edit links in PowerPoint using VBA
Linking Excel files to PowerPoint
Power Query
How to add manual information to a query (commentary & references)
Automate Reconciliations with Power Query (amazing time saver)
Text functions – Excel & Power Query comparision
Get data from latest file with Power Query
How to create chart data with Power Query
Power Query: How to Combine files based on a list
How to expand columns dynamically in Power Query
Power Query: Get data when sheet/Table names change (2 ways)
How to remove spaces in Power Query
How to filter by a list in Power Query (4 methods)
How to get data from SharePoint List with Power Query
How to remove spaces in Power Query
How to fix the Formula.Firewall error in Power Query (2 ways)
Get data from OneDrive Personal files with Power Query
Get data from OneDrive or SharePoint with Power Query
How to list all possible combinations from multiple lists (2 ways)
How to remove spaces in Excel (7 simple ways)
How to calculate Power Query Percent of total or category
Power Query date format (How to + 5 tricky scenarios)
How to get data into Power Query – 5 common data sources
How to combine rows in Power Query
Common Power Query errors & how to fix them
How to use Power Query Custom Functions
How to use Power Query Group By to summarize data
Power Query If statement: nested ifs & multiple conditions
Power Query formulas (how to use them and pitfalls to avoid)
How to change source data location in Power Query (7 ways)
Power Query: Lookup value in another table with merge
How to unpivot in Excel using Power Query (3 ways)
How to get data from the Current Workbook with Power Query
Get data from folder in Power Query: combine files quickly
List files in a folder & subfolders with Power Query
Power Query Append: Quickly combine many queries into 1
Power Query Parameters: 3 methods + 1 simple example
Common Power Query transformations (50+ powerful transformations explained)
Excel rounding vs Power Query rounding: WARNING! They are different
Get to know Power Query Close & Load options
Use the Power Query editor to update queries
Refresh Power Query in Excel: 4 ways + advanced options
Power Query: Stop Excel Table columns resizing
Get the refresh date & time in Power Query
Stop the auto Changed Type step in Power Query
Rename columns in Power Query when names change
Power Query – Split delimited cells into rows
Change the Power Query source based on a cell value
Power Query – Absolute and relative references
Auto refresh Power Query on parameter change
Power Query – Unstacking data in a column
Power Query – Import Data from the Web
Ranges
Cell ranges: 99% of users don’t know this
When you should merge cells in Excel – 3 scenarios
Hide named ranges in Excel: 4 simple ways
Creating relative named ranges
Tables
How to use Table slicers for advanced interactivity in Excel
How to use slicers with PIVOTBY, GROUPBY & FILTER in Excel
Excel Table absolute reference for cells, columns, and rows
How to use Excel Table within a data validation list (3 ways)
Running total in an Excel Table
Dynamically select any column in an Excel Table
Highlight specific rows in a table
VBA Macros
Don’t trust data validation in Excel
How to easily swap ranges in Excel (1 click)
How to create QR codes in Excel for FREE (3 easy ways)
How to run any macro from one button (magic macro)
Browse for file path with VBA: insert value into cell
How to run Power Automate from Excel with Office Scripts or VBA
Application.CutCopyMode = False (How to use it)
How to run a macro from a macro (from another workbook)
VBA Rename File (How to + 5 code examples)
How to close VBA UserForm: Hide vs Unload
How to automate Goal Seek in Excel with VBA Macro (2 ways)
How to change images based on cell values (3 ways)
Useful VBA codes for Excel (30 example macros + Free ebook)
How to convert color codes (RGB, HSL, HSV, Hex, Long, CMYK)
Private vs Public Subs, Variables & Functions in VBA
VBA protect and unprotect Sheets (25+ examples)
How to loop through each item in Data Validation list with VBA
How to save Excel as PDF with VBA (10 examples)
VBA code to copy, move, delete and manage files
How to Insert, Move & Delete Pictures with VBA
VBA code to loop through files in a folder (and sub folders)
Ultimate Guide: VBA for Charts & Graphs in Excel (100+ examples)
How to remove Excel passwords with VBA (all 5 types)
How to fix “Microsoft has blocked macros” (2 simple ways)
6 ways to make Excel sheets very hidden (invisible)
How to copy files with VBA in Excel
Excel – Create multiple PDFs based on a list
VBA to sort sheets alphabetically
VBA to hide all sheets except one
Loop through selected sheets with VBA
Dynamic arrays and VBA user defined functions (UDFs)
How to add a prefix or suffix to every cell using VBA
Apportion a value across cells with VBA
Resize a UserForm with VBA or Windows API
Hide or disable a VBA UserForm [X] close button
Difference between Sheets and Worksheets in VBA
Convert Merged cells to Centre Across Selection with VBA
VBA code to select all merged cells
VBA code draw rectangles around selected cells
How to select all unlocked cells in Excel
VBA code to contol Form Control checkboxes
Assign macro with arguments to a Form Control button
Create multiple checkboxes instantly with VBA
VBA code to insert, delete and control comments
Create dynamic chart titles with custom formatting
How to set chart axis based on a cell value
VBA Code to Apply and Control AutoFilter in Excel
Calling and using the color picker with VBA
Change tab color based on a cell value in Excel
Excel function to list files in a folder without VBA
VBA Code to control Excel’s Zoom settings
VBA code: check what is currently selected in Excel
Automatic e-mail template for Excel
Manipulating and changing scroll bars with VBA
VBA: Convert centimeters, inches & pixels to points
VBA code to Zip and Unzip files and folders
Powerpoint VBA Reference Library
VBA code to create, delete and manage folders
VBA code to find out if a file is already open
Obtaining the save status of a file using VBA
Creating custom Map Charts using shapes and VBA
Automatically open workbooks when Excel starts
Using VBA to control Custom Lists
Controlling Powerpoint from Excel using VBA
Do you have to use Dim to declare variables?
How to automate Word from Excel using VBA
Using VBA to control other Office applications
Reading document properties with VBA
Edit links in PowerPoint using VBA
VBA Code to Password Protect an Excel file
VBA code to Protect and Unprotect Workbooks
Make a workbook read-only for everybody but me
Selecting a file using the FileDialog
Cell and Range VBA properties and actions
Workbook VBA properties and actions
Worksheet VBA properties and actions
Sorting an array alphabetically with VBA
How to unhide multiple sheets & make sheets invisible
Loop through every worksheet or every workbook
Word
How to automate Word from Excel using VBA
5 quick ways to embed a Word document in Excel
Miscellaneous
How to resize Excel sheet – 4 ways: manual & automatic
How to install or uninstall Excel Add-ins (Windows & Mac)
Excel calculation mode keeps changing: How to fix it!
How to add a drop-down list in Excel (3 easy ways)
How to remove spaces in Excel (7 simple ways)
Excel formulas not calculating? 14 reasons & how to fix it
How to split cells in Excel: 4 simple ways
Resetting the scroll bar in Excel (5 solutions)
8 quick ways to open a new instance of Excel (+1 hard way)
6 ways to make Excel sheets very hidden (invisible)
An introduction to Modern Excel
What the experts wish they had known earlier
How to copy table from PDF to Excel
How to insert Special Characters (Cheat Sheet Download)