• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member
  • Login

Excel Formulas

Excel BYROW and BYCOL Functions

Excel BYCOL and BYROW Functions

September 17, 2023 by Mynda Treacy

Excel BYCOL and BYROW functions fundamentally change the way we write formulas that calculate across columns and down rows. They enable us to write a formula once and have it automatically applied to all rows or columns in the range we specify, improving productivity and accuracy. These functions are currently available to Microsoft 365 users. …

Read moreExcel BYCOL and BYROW Functions
python in excel natively

How to Use Python in Excel Natively

August 22, 2023 by Mynda Treacy

You can now use Python in Excel natively! Python runs securely in the cloud, and we write Python in Excel like a formula. You can load Python libraries to Excel including Pandas, NumPy, Seaborn, Matplotlib and more. No need to install any add-ins and no clunky separate windows for writing the code. Note: Python in …

Read moreHow to Use Python in Excel Natively
excel dynamic named ranges

Excel Dynamic Named Ranges

August 15, 2023 by Mynda Treacy

Excel dynamic named ranges bring flexibility and efficiency to your spreadsheets. Unlike static named ranges, dynamic named ranges automatically adjust in size as you add or remove data, ensuring that your formulas, PivotTables and charts always include the most recent information. You can also use them to return different ranges based on selections in drop …

Read moreExcel Dynamic Named Ranges
functions for financial modelling

Excel Functions for Financial Modeling

August 8, 2023 by Mynda Treacy

Excel functions are the backbone of a financial modeler’s day to day work and as such, it’s essential to have a good understanding of the functions that are commonly used to build financial models. Below are the top Excel functions that you must know as a financial modeler grouped by area. Are there any functions …

Read moreExcel Functions for Financial Modeling
excel formula by example

Excel Formula by Example

May 30, 2023 by Mynda Treacy

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 …

Read moreExcel Formula by Example
ai-aided excel formula editor

AI Aided Excel Formula Editor

February 23, 2023 by Mynda Treacy

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 …

Read moreAI Aided Excel Formula Editor
top excel functions for data analysts

Top Excel Functions for Data Analysts

January 12, 2023 by Mynda Treacy

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 …

Read moreTop Excel Functions for Data Analysts
excel advanced formula environment

Excel Labs (Formerly, Advanced Formula Environment)

December 15, 2022 by Mynda Treacy

Excel Labs, formerly called the 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 …

Read moreExcel Labs (Formerly, Advanced Formula Environment)
Pro Excel Formula Writing Tips

Pro Excel Formula Writing Tips

November 30, 2022 by Mynda Treacy

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 …

Read morePro Excel Formula Writing Tips
excel shaping arrays

New Array Shaping Excel Functions

October 6, 2022 by Mynda Treacy

Microsoft recently released 11 new Excel functions for shaping arrays (data). I already covered VSTACK and HSTACK which was super popular, and in this post I’m going to use some of the other new functions to do things that used to require ninja level function wrangling. You can see the individual tutorials covered in the …

Read moreNew Array Shaping Excel Functions
excel nested if functions what not to do

Excel IF Formulas and What Not To Do

September 22, 2022 by Mynda Treacy

We can learn a lot from looking at what others do in Excel, both the good and the not so good. Last week one of my course members asked me to help them understand what a nested IF formula was doing in a file they’d inherited in their new job. It contained 15 nested IF …

Read moreExcel IF Formulas and What Not To Do
excel image function

Excel IMAGE Function

September 8, 2022 by Mynda Treacy

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 …

Read moreExcel IMAGE Function

Excel VSTACK and HSTACK Functions

September 1, 2022 by Mynda Treacy

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. …

Read moreExcel VSTACK and HSTACK Functions
identify overlapping dates and times in excel

Identify overlapping dates and times in Excel

July 21, 2022 by Mynda Treacy

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 …

Read moreIdentify overlapping dates and times in Excel
New Excel Text Functions

TEXTSPLIT, TEXTBEFORE and TEXTAFTER Functions

March 31, 2022 by Mynda Treacy

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 …

Read moreTEXTSPLIT, TEXTBEFORE and TEXTAFTER Functions

Top 10 Intermediate Excel Functions

March 10, 2022 by Mynda Treacy

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 …

Read moreTop 10 Intermediate Excel Functions

5 Excel SUM Function Tricks

January 6, 2022 by Mynda Treacy

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 …

Read more5 Excel SUM Function Tricks
Excel Hash Spill Operator

Hash Sign in Excel Formulas

October 21, 2021 by Mynda Treacy

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 …

Read moreHash Sign in Excel Formulas
Excel LAMBDA Recursion

Recursive LAMBDA Functions

March 4, 2021 by Mynda Treacy

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 …

Read moreRecursive LAMBDA Functions
Excel LAMBDA Function

Excel LAMBDA Function

February 25, 2021 by Mynda Treacy

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 …

Read moreExcel LAMBDA Function
dynamically list excel sheet names

Dynamically List Excel Sheet Names

July 16, 2020 by Mynda Treacy

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 …

Read moreDynamically List Excel Sheet Names
Functions that Return References

Excel Functions that Return References

June 25, 2020 by Mynda Treacy

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 …

Read moreExcel Functions that Return References
Excel STOCKHISTORY Function

Excel STOCKHISTORY Function

June 18, 2020 by Mynda Treacy

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 …

Read moreExcel STOCKHISTORY Function
5 Secret Features of the Excel INDEX Function

5 Excel INDEX Function Secrets

June 11, 2020 by Mynda Treacy

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 …

Read more5 Excel INDEX Function Secrets
Excel LET Function

Excel LET Function

April 16, 2020 by Mynda Treacy

The Excel LET function* enables you to declare variables and intermediate calculations inside of a formula. It’s like the DAX VAR function or ‘let’ inside of Power Query. Those familiar with Power Pivot, Power Query or programming will understand these terms, but don’t be put off. LET is a dead easy function to learn and …

Read moreExcel LET Function
list_first_monday_date_in_each_month

List First Monday Date in Each Month

January 23, 2020 by Mynda Treacy

With Excel formulas we can easily list the first Monday date in each month, or the last Sunday, or whatever day you like. I’ll use the DATE and WEEKDAY functions to generate the list of dates, and then I’ll show you some alternatives, including a dynamic array solution using SEQUENCE. Plus, I’ll connect the formula …

Read moreList First Monday Date in Each Month

Excel XLOOKUP Function

January 8, 2020 by Mynda Treacy

Oh, how I wish I’d had the fortune of the new Excel XLOOKUP function back in my accounting days. The first powerful function I learnt was VLOOKUP. It gave me a sense of power and cemented my love of Excel. XLOOKUP is everything VLOOKUP is and much more. XLOOKUP can look up to the left …

Read moreExcel XLOOKUP Function

Excel XOR Function

December 3, 2019 by Mynda Treacy

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 …

Read moreExcel XOR Function

Extract Values Present in Two Lists

October 22, 2019 by Mynda Treacy

Comparing two lists is easily done with Power Query, but maybe you prefer to use a formula to extract values present in two lists. Before dynamic array formulas this was a daunting formula to write, but the new FILTER function makes it dead easy. In this tutorial I’ll show you both methods. Note: The FILTER …

Read moreExtract Values Present in Two Lists
reducing data validation list

Reducing Data Validation List

October 15, 2019 by Mynda Treacy

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 …

Read moreReducing Data Validation List
  • Go to page 1
  • Go to page 2
  • Go to page 3
  • Interim pages omitted …
  • Go to page 7
  • Next

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel Office Scripts
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook

Sign up to our newsletter and join over 400,000
others who learn Excel and Power BI with us.

 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate
  • Sponsor Our Newsletter

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.