EXCEL TUTORIALS
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.
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.
Excel tips & tricks – stop doing these 7 things in Excel
Don't merge cells in Excel - Don't Copy Excel sheets without updating links - Don't hide rows and columns! There are better ways of doing these things. Find out 7 things you're doing wrong in Excel and how to do it correctly.
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!
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!
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.
Excel tips & tricks – stop doing these 7 things in Excel
Don't merge cells in Excel - Don't Copy Excel sheets without updating links - Don't hide rows and columns! There are better ways of doing these things. Find out 7 things you're doing wrong in Excel and how to do it correctly.
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.
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
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.