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
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!
DAX CALCULATE Function
If you'd like to "see" how the DAX CALCULATE function works, then you need this tutorial. It will show you what's happening behind the scenes: How the CALCULATE function works and delivers results.
Charts
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.
Excel Job Interview Questions by Position
In this article, I summarize the MOST COMMON Excel questions that are currently asked during interviews. This covers positions such as business or MIS analyst, Data Analyst, Financial Analyst or more senior positions. You'll be surprised at the level of Excel that's currently required for some of these positions.
Features
Excel Pivot Table Date Grouping
Handling dates in Excel Pivot Tables can get tricky. How can you quickly ungroup the dates? Or get your own custom grouping for fiscal periods? How can you show all the dates including the ones with no values? This tutorial covers it all!
Lock and Unlock Cells in Excel
Lock & Unlock Cells in Microsoft Excel If you create spreadsheets for other people to use, you may have some experience with those people deleting or changing your formulas or other aspects of your workbook. Not only is it frustrating to have someone change your hard work but it can negatively
Excel Advanced Formula Editor Lambda
The new Microsoft Excel Advanced Formula Add-in makes it easy to write and manage LAMBDAs in Excel. It's a "new" version of Name Manager and hopefully it could replace Name Manager in the future.
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 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!).
Excel SCAN Function
Learn how to use the new Excel SCAN function! This function is a Lambda helper function. You can use it to "scan" a range and apply a calculation to each cell. In this example you'll learn how to use SCAN to get to-date values and how you can tweak it to return YTD values.