Blog

Recent Blog posts


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)

Create Slopegraphs in Excel

Create a fan chart in Excel

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

How to use Excel Table within a data validation list

Don’t trust data validation in Excel

How to loop through each item in Data Validation list with VBA


Formulas & Functions

How to convert text to a formula in Excel

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

INFO function in Excel

CHAR function 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

Advanced VLOOKUP Cheat Sheet

VLOOKUP: List all the matching items

VLOOKUP: Lookup the nth item (without helper columns)

Automatically expand the VLOOKUP data range

How to VLOOKUP row and column

Automatically run a Macro when opening a workbook

Using wildcards with VLOOKUP

VLOOKUP with multiple criteria

VLOOKUP: Change the column number automatically

How to use VLOOKUP


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

Auto refresh 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

Introduction to Power Query

Power Query: Stop Excel Table columns resizing

Filter All in Power Query

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

Power Query – Running Total

Power Query – Tips and Tricks

Power Query – Unstacking data in a column

Power Query – Import Data from the Web


Ranges

Creating relative named 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


Tables

How to use Excel Table within a data validation list

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

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)

VBA Tables and ListObjects

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

Excel VBA for Pivot Tables

Loop through selected sheets with VBA

Dynamic arrays and VBA user defined functions (UDFs)

VBA Arrays

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

Sorting ListBoxes with VBA

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

Using Excel 4 Macro Functions

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 Word using 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 link Excel to Word

Edit links in Word using VBA

How to automate Word from Excel using VBA

5 quick ways to embed a Word document in Excel


Miscellaneous

Create your own keyboard shortcuts in Excel

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)

Remove blank rows in Excel

Excel Quick Tips [3]

Quick Excel Tips [2]

Quick Excel Tips [1]

An introduction to Modern Excel

Spreadsheet Day 2017

Using Custom Lists in 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)

Spreadsheet Day – 17th October

Excel’s hidden camera tool