Excel Formulas

Excel can now write formulas for you. Simply give it an example or two and it’ll suggest a formula to complete the task. Remember how amazing Flash Fill was when you saw it for the first time? Well, Formula by Example is 10x better because it isn’t a one time and done solution. Being a …

Writing Excel formulas is one of the most important tasks to master in Excel, but it can be a mine field trying to get your head around all the functions available and knowing which one to use for the task at hand. But now with this free AI-aided formula editor you can have it write …

A data analyst gathers, cleans, models, analyses, and interprets data. The gathering and cleaning should be done with Power Query. The modelling and analysis can be done with a combination of PivotTables/Power Pivot and functions. The other day I stumbled upon a post claiming to list the top Excel functions for data analysts. I disagreed …

Excel’s new Advanced Formula Environment (AFE) is still in its early stages of development, but it’s already very useful, particularly if you’re scared to write your own LAMBDA functions, as you’ll see it can write them for you! You may have seen me use it a few times in tutorials lately, but recently it has …

Excel Formula Writing Tips, Tricks and Tools that will change how you work in Excel Formulas are THE MOST IMPORTANT EXCEL SKILL you can master, but when you start nesting functions, things can quickly get out of hand. In this lesson I’m going to cover some little known Excel formula writing tips, tricks and tools …

The new Excel IMAGE function enables you to insert images into cells with a formula. From there you can resize them, sort, filter and more. The IMAGE function supports BMP, JPG/JPEG, GIF, TIFF, PNG, ICO, and WEBP file types. The IMAGE function is currently in the beta testing phase so the function arguments may change …

Excel VSTACK and HSTACK functions are just two of a raft of new text manipulation functions available to Microsoft 365 users. I wrote about text splitting functions, TEXTSPLIT, TEXTBEFORE and TEXTAFTER a while back. The new VSTACK and HSTACK functions work to combine arrays arranged vertically (VSTACK) or horizontally (HSTACK) into a new single array. …

I often get asked how to identify overlapping dates and times in Excel. Excel doesn’t have an ‘overlap’ function, but with SUMPRODUCT we can identify dates or times that fall within the same range. For example, let’s say you have list of appointments scheduled for the month. If you plot them visually in a calendar …

It’s been a long wait, but we finally have some exciting new Excel text functions that are going to make life so much easier. In this tutorial I’m going to focus on TEXTAFTER, TEXTBEFORE and most exciting, TEXTSPLIT. The first two functions are fairly self-explanatory, and so is TEXTSPLIT to a degree. However, in this …

Most Excel users know the basic functions like SUM, COUNT, AVERAGE etc. Therefore, in this tutorial I’m going to cover the 10 most important intermediate level Excel functions every Excel user should know. Watch the video for an introduction to each function, and then dive deeper by following the links below. Note: there’s technically 17 …

I know you’re probably thinking that everyone knows how to use the SUM function, but I’m willing to bet that most people don’t know all the shortcuts and tricks I’m about to show you. These are SUM function tricks you can use every day, and some also apply to all Excel functions. Watch the Video …

If you’ve worked with dynamic array formulas*, you’ll have noticed that they’re able to spill the results to multiple cells. For example, in the image below we can see the UNIQUE function has spilled the results to cells G3:G6 which are highlighted with a blue border:   With the hash sign in Excel formulas we …

The new Excel LAMBDA function that we looked at last week also enables us to write custom recursive LAMBDA functions. Recursion is simply when a routine calls itself. That said, it can be a bit mind bending, so in this tutorial we’ll look at an example that’s easy to follow. Once you understand the structure …

The new Excel LAMBDA function allows you to define your own custom functions using Excel’s familiar formula language. That means we no longer need JavaScript or VBA programming knowledge to create our own functions. This is a huge step forward and for those familiar with the concept of lambdas it’s worth noting that the LAMBDA …

Navigating workbooks with lots of sheets can be tedious. In this tutorial I’m going to show you how to dynamically list Excel sheet names and add some user-friendly hyperlinks to help users easily navigate the file. It requires an old Excel 4.0 Macro Function called GET.WORKBOOK and this means the file must be saved as …

Using a formula to return a reference to a range of cells allows us to generate a reference on the fly based on the shape of the data or criteria we specify. As our data grows these formula generated references can automatically update to include new data. This has huge efficiency advantages over hard coding …

With the new Excel STOCKHISTORY function we can finally get historical price data for stocks, bonds, funds, crypto and foreign currency exchange rates into Excel, including open, high, low, close and volume. The STOCKHISTORY function is currently only available on the Beta channel (previously Insider Channel) to Microsoft 365 users (previously Office 365). Plus, it …

The Excel INDEX function is a treasure trove of functionality, but most of us only know one way to use it. In this post I want to expose some lesser known quirks and ways it can be used.  If the reference/array is a single row, you can put the col_num in the row_num argument’s position …

The Excel XOR function, or ‘exclusive OR’ as it’s known in the programming world, performs a test on an array, range or one or more expressions. It was introduced in Excel 2013. Syntax: =XOR( logical1, [logical2],...) logical1 An expression (a formula that results in a single value), array or reference that evaluates to TRUE or …

Creating a reducing Data Validation list is easy with the new dynamic array formulas. Let’s say we have a list of jobs currently in progress (column B in the image below). Each contractor can work on no more than two jobs and we have four contractors available (column E). As we assign contractors to jobs …

Typically if you need to lookup a value from a table you'd employ VLOOKUP or INDEX/MATCH. However if your data looks like this and you are looking up by Employee name then you'll run into some issues because there are two employees with the name John. Both the VLOOKUP and INDEX/MATCH approach will have problems …

Excel Goal Seek is another tool in the What-if Analysis suite on the Data tab of the ribbon. Goal Seek makes it easy to find the value required to meet a target value you specify. Watch the Excel Goal Seek Video Download Workbook Enter your email address below to download the sample workbook. Get Workbook …

Dates and times are stored in Excel as decimals known as serial numbers. The date is represented by the whole number part of the serial number and the time by the decimal part. In Excel dates start from 1 Jan 1900, which is represented by the number 1. When you see a date or a …

Excel Data Tables are one of the What-if Analysis tools that we have available to aid our decision making. They don't require knowledge of any new fancy formulas and are super quick to build. With Excel Data Tables we can perform what-if analysis with one or two variables, which makes it quick and easy to …