EXCEL TUTORIALS

Formulas
Dashboards
POWER QUERY & POWER PIVOT
Charts
Features
VBA
DYNAMIC ARRAYS

Formulas

Excel TEXTBEFORE TEXTAFTER Functions

Learn to properly use the Excel TEXTBEFORE and TEXTAFTER functions. There is a cool trick you can use with these functions to get the outcome you want. Check out the post for more.

Excel LOOKUP Function

The lookup function is the simplest function in Excel. It can return exact or approximate matches and it's super easy to write. Yet many people prefer VLOOKUP because it's easier to understand. In this tutorial, I'll explain the LOOKUP function by using a simple, followed by a more complex example.

Excel TEXTSPLIT Function

Excel's TEXTSPLIT function can split words into separate cells. You can have multiple delimiters, you just have to know how to define these properly. You can also do amazing things like splitting into columns and rows and internally sorting words.

More

Dashboards

Power BI Drill-Through

What do you do when you're presenting a chart to the higher-ups and someone asks: "Show me the Details behind that?" If you've embedded your Power BI report in PowerPoint, you'll be like: "Let me show you!". Drill-through in Power BI is super easy to implement. You just need the right start. This tutorial shows you how.

How to Use Power BI

Get started with Power BI now by creating your first dashboard. Download the free files, follow along and in 20 minutes you'll have setup your interactive dashboard which you've published to the web!

More

POWER QUERY & POWER PIVOT

Bulk Import PDF Files to Excel

Use this button you already have in Excel to bulk combine and import PDF files to Excel. Forget copy-paste, manually typing values or using 3rd party tools. Get it dynamic too so you just need to drop a new PDF in a folder and the PDF data is immediately imported to Excel.

DAX Many to One side calculations

In Power BI or Power Pivot, you often need to create reports that go from the many side of the relationship to the one side. How can you do that without expanding your data model by adding more columns to your table? There is an easy trick you can use to solve this with a single DAX formula.

How to Use Power BI

Get started with Power BI now by creating your first dashboard. Download the free files, follow along and in 20 minutes you'll have setup your interactive dashboard which you've published to the web!

More

Charts

Power BI Drill-Through

What do you do when you're presenting a chart to the higher-ups and someone asks: "Show me the Details behind that?" If you've embedded your Power BI report in PowerPoint, you'll be like: "Let me show you!". Drill-through in Power BI is super easy to implement. You just need the right start. This tutorial shows you how.

Think-Cell in PowerPoint (free trail)

With the think-cell add-in for PowerPoint, you can create beautiful charts with just a few clicks. Some of my favorites are Gantt charts, Mekko charts and adding automatics totals to stacked columns or bars.

More

Features

Excel What If Analysis Data Table

Excel's What-if Analysis Data Table allows you to quickly create simulations. This tutorial takes you through a simple example of how you can create a Data Table from scratch. If you've never used this feature, you need to check it out. It's like magic.

4 Excel Settings

Excel comes with default settings and options. You can easily change these by going to File / Options. Why should you? To support your workflow and be more productive. This post has my list of options you need to review.

Excel Organizational Data Types

Organizational data types allow you to create your own rich custom data types in the Excel Ribbon. Technically you‘d create them in Power BI and have them show up in the Excel Menu of your team members. This tutorial show you what organizational data types are and you can set them up from scratch.

More

VBA

Office Scripts – Macros on the Web

This example gets you started with Office Scripts in Excel. You'll learn how to record a macro and how to modify the script to get a dynamic range. You'll also learn where to find the Office Script and how to save it with your workbook.

Excel VBA vs Office Scripts

What's the difference between Excel VBA macros and Office Scripts? Do you need to switch from VBA to Office Scripts? Find out in this detailed article. I show examples of what you can achieve with VBA and the current status of Office Scripts and explain what each is for.

Learn Excel Macros

Learn Excel Macros in 10 Minutes If you find yourself repeatedly performing the same steps in the same order in Excel, your brain should immediately declare, “I should automate this with an Excel macro!”. Think of the common, repetitive tasks you perform in Excel, like: Formatting downloaded data Creating a report Combining

More

DYNAMIC ARRAYS

Excel TEXTBEFORE TEXTAFTER Functions

Learn to properly use the Excel TEXTBEFORE and TEXTAFTER functions. There is a cool trick you can use with these functions to get the outcome you want. Check out the post for more.

Excel VSTACK Function

This is a real-life example of Excel's VSTACK function. Quickly stack data from multiple sheets (even as many as 15 sheets!), filter out blank cells and even sort the summary table.

Excel TEXTSPLIT Function

Excel's TEXTSPLIT function can split words into separate cells. You can have multiple delimiters, you just have to know how to define these properly. You can also do amazing things like splitting into columns and rows and internally sorting words.

More