EXCEL TUTORIALS
Formulas
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.
New Excel Functions Vstack Textsplit tocol
Excel recently released a set of new functions like VSTACK, TEXTSPLIT and TOCOL that will have you looking forward to working with complex files and data because your life just got more exciting (at least mine did!).
Dashboards
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!
Excel – Interactive Dashboard Trick
With Pivot Charts in Microsoft Excel you can add slicers to add interactive functionality to your dashboards. But what about adding even more interactivity? You could give the user the ability to change the relevant KPI in the chart from the slicer.
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
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.
Forecasting in Excel Made Simple
Did you know you can easily create a forecast in Excel? In the example, I use data from The Home Depot and forecast the next two quarters. It's really easy, even if you need to take seasonality into consideration.
Features
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.
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.
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 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.
New Excel Functions Vstack Textsplit tocol
Excel recently released a set of new functions like VSTACK, TEXTSPLIT and TOCOL that will have you looking forward to working with complex files and data because your life just got more exciting (at least mine did!).