• 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

Excel Formulas

Find last occurrence of a string in another string

Find the Last Occurrence of a String In Another String

July 24, 2019 by Philip Treacy

If we want to find whether or not a character or string occurs within another string we can use FIND or SEARCH. But these functions only tell us if a string (or character) exist in another string, they don't tell us if the string we are looking for occurs multiple times. For example, we have …

Read moreFind the Last Occurrence of a String In Another String
calendar_template_formulas_thumb

Excel Calendar Template Date Formulas Explained

June 25, 2019 by Mynda Treacy

There are many Excel Calendar templates available and if you look at them closely, you’ll see they have some mind-boggling date formulas. Adam, one of our members, sent me in a calendar he has been using for 10+ years and asked if I could explain how some of the formulas worked. The original source of …

Read moreExcel Calendar Template Date Formulas Explained
Power Query Get Data from Web by Example

Power Query Get Data from Web by Example

May 14, 2019 by Mynda Treacy

Getting data from the web is possible with Power Query in both Excel and Power BI. However, Excel is a little behind in Power Query features, therefore in this post I’m going to show you a new feature available in Power BI Desktop using Power Query to get data from the web by example. In …

Read morePower Query Get Data from Web by Example
IF Formula Builder

IF Formula Builder

May 3, 2019 by Philip Treacy

One of our most popular blog posts is Excel IF AND OR Functions Explained which has over 800 comments at time of writing. The vast majority of questions are along the lines of I want to multiply a value by a percentage. In some cases, we need to enter the word "Special" instead of the …

Read moreIF Formula Builder
using formula instead of vba

Using a Formula Instead of VBA

April 26, 2019 by Philip Treacy

Sometimes we get requests to provide a VBA solution to a problem. But when we look at the problem, VBA is not the best answer, using a formula is. What this tells me is that people don't know how to use Excel's functions and when they come up against a problem they think is difficult, …

Read moreUsing a Formula Instead of VBA
Excel Sorted Dynamic Unique List

Excel Sorted Dynamic Unique List

January 8, 2019 by Mynda Treacy

I really wanted the title of this post to be “Excel Sorted Dynamic Unique List Ignoring Blanks and Errors”, but I didn’t want to brag 😉 It has never been so easy to extract a unique or distinct list of values in Excel than it is now that we have Dynamic Array formulas*. *Dynamic Array …

Read moreExcel Sorted Dynamic Unique List
excel dynamic arrays

Excel Dynamic Arrays

December 6, 2018 by Mynda Treacy

If you’re familiar with array formulas, then the simplicity of the new Excel Dynamic Arrays will be a breath of fresh air. And if you’ve always shied away from array formulas then Dynamic Arrays are for you. Excel no longer requires you to enter array formulas with CTRL+SHIFT+ENTER and formulas that return multiple results will …

Read moreExcel Dynamic Arrays
Using SUBSTITUTE function to find and count

Using SUBSTITUTE Function to Find and Count Text

August 31, 2018 by Philip Treacy

The SUBSTITUTE function allows you to change text in a string, or a range of strings, but there is more to it than that. By combining it with other functions you can use it to do things like count how many times a section of text appears in a cell or range, or find the …

Read moreUsing SUBSTITUTE Function to Find and Count Text
Excel Functions Translator

Excel Functions Translator

July 19, 2018 by Mynda Treacy

It’s often difficult enough to figure out the correct Excel functions to use, but it’s significantly harder if you also need to translate the function into the language of your localized version of Excel. Introducing the Excel Functions Translator. It’s a free add-in available for Excel Online, Excel 2013, Excel 2016 for Desktop, Office 365, …

Read moreExcel Functions Translator

Relative Named Ranges

April 4, 2018 by Mynda Treacy

Excel Named Ranges is a vast topic that includes some simple techniques that we all can and should use to make our spreadsheets easier to build and maintain. Plus, some more advanced techniques like relative named ranges, which are good to know. Especially for that occasion when you inherit a workbook from an Excel Superuser …

Read moreRelative Named Ranges

Excel ADDRESS Function

March 22, 2018 by Mynda Treacy

The Excel ADDRESS Function returns a cell address for specified row and column coordinates.  Optional arguments allow you to specify the style of address (A1 or R1C1), the sheet name it refers to and whether the reference is absolute or relative. Excel ADDRESS Function Syntax Syntax: =ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text]) Note: Arguments in square …

Read moreExcel ADDRESS Function

Extract Date or Time from a Date and Time

October 25, 2017 by Mynda Treacy

We often need to extract the date or time portion from a date-time serial number for use in other functions. We can use the INT function to extract the date portion, and the MOD function to extract the time portion. See examples below. The INT function returns the integer or whole number portion of the …

Read moreExtract Date or Time from a Date and Time

Excel MOD Function

October 25, 2017 by Mynda Treacy

The Excel MOD Function is probably one of the most confusing to understand, unless you’re a math whizz, which I am not. After all this is why I use Excel. It’s my giant calculator of choice. Anyhow, just because I find MOD confusing, doesn’t mean it’s not one of the most useful and versatile functions …

Read moreExcel MOD Function

Excel DATE Function

October 25, 2017 by Mynda Treacy

The Excel DATE function combines separate year, month and day values and converts them into a date serial number formatted as a date. Excel DATE Function Syntax Syntax: =DATE(year, month, day) year The value of the year argument can include one to four digits from 1900 to 9999. month A positive or negative integer representing …

Read moreExcel DATE Function

Convert Month Names to Numbers

October 25, 2017 by Mynda Treacy

Often, we'll have a list of month names that we want to convert to a date, or even just convert month names to numbers. We can use the MONTH function to do this; see examples below: How it works: By concatenating a 1 to the text month name in column B we provide the MONTH …

Read moreConvert Month Names to Numbers

Convert Dates to Text

October 24, 2017 by Mynda Treacy

If you want to use a date in a chart label, or you want to concatenate a date with some other text, then you'll want to convert dates to text first to ensure it displays correctly. We can use the TEXT function for this. The second argument of the text function allows us to specify …

Read moreConvert Dates to Text

Excel NETWORKDAYS.INTL Function

October 19, 2017 by Mynda Treacy

The Excel NETWORKDAYS.INTL function (new in Excel 2010) returns the number of working days between two date serial numbers, excluding weekends and holidays. Weekend days can be specified. NETWORKDAYS.INTL function syntax: =NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays]) start_date A date serial number or text end_date A date serial number or text [weekend] This optional argument allows you …

Read moreExcel NETWORKDAYS.INTL Function
Highlight Referenced Cells

Highlight Cells Referenced in Excel Formulas

September 19, 2017 by Mynda Treacy

In an ideal spreadsheet our formulas would always reference adjacent cells and columns and it would be obvious which cells contributed to a result, like this: However, in financial modelling and reporting it’s often less obvious, like the example below: Let’s look at some ways we can easily identify which cells contribute to a result. …

Read moreHighlight Cells Referenced in Excel Formulas

Excel Convert Dates to Fiscal Quarters and Years

July 27, 2017 by Mynda Treacy

If your company’s fiscal year runs in line with the calendar year then it’s easy to convert dates into quarters and years for reporting. Convert dates to quarters with this formula (where cell A2 contains your date): =ROUNDUP(MONTH(A2)/3,0) And if you want to prefix it with ‘Q’, then use this formula: ="Q"&ROUNDUP(MONTH(A2)/3,0) Convert dates to …

Read moreExcel Convert Dates to Fiscal Quarters and Years

Excel Dates Displayed in Different Languages

June 1, 2017 by Mynda Treacy

If you share Excel files and reports with users from different countries then you might want to give them a report in their language…and I don’t just mean converting from English to American 😉 It’d be a load of work to create a separate file for each language, so instead we can automate it like …

Read moreExcel Dates Displayed in Different Languages

Excel TEXTJOIN Function

March 22, 2017 by Mynda Treacy

New in Excel 2019* is the TEXTJOIN Function. It's the grown-up sibling of CONCATENATE. Both TEXTJOIN and CONCATENATE join text together, but TEXTJOIN enables you to more easily join a list or range of cells including a delimiter that you want to insert between each text value. Best of all, TEXTJOIN allows you to ignore …

Read moreExcel TEXTJOIN Function

Excel COUNT MATCH Array Formula

November 17, 2016 by Mynda Treacy

Last week I received the following question from André: “I need a formula that will count how many values in cells D4:D7 are listed in B4:B13. I don’t want to count the number of times they appear in column B, I just want to know how many values from column D are in column B“. …

Read moreExcel COUNT MATCH Array Formula

Excel IFS Function

October 18, 2016 by Mynda Treacy

This tutorial is applicable to Excel 2019 onward and with a Microsoft 365 license. In Excel 2019 there are a load of new functions, one of which is the IFS Function. Given last week’s topic, ‘When to say no to Excel Nested IFs’ I thought it was a good time to introduce the Excel IFS …

Read moreExcel IFS Function

Excel SWITCH Function

October 18, 2016 by Mynda Treacy

This tutorial is applicable to Excel 2019 onward. New in Excel 2019 is the SWITCH function. It looks up a value in a list of values, and returns the result corresponding to the first matching value. It’s a great alternative to nested IFs, or even the new IFS function. I liken it to a VLOOKUP, …

Read moreExcel SWITCH Function

Excel Expression Definition

October 14, 2016 by Mynda Treacy

A couple of days ago I sent out a survey about the name of an argument in one of the new Excel 2016 functions. The response was huge and I’m so grateful to the 1300+ members who managed to respond before I closed it. I apologise if you missed out. I had to close the …

Read moreExcel Expression Definition

When to say No to Excel Nested IFs

October 7, 2016 by Mynda Treacy

In Excel 2007, Microsoft thought it would be a good idea to increase the number of times you can nest IF functions from 7 to 64. S I X T Y – F O U R! Are you kidding me? If you need to nest any more than 7 IFs then you should be using …

Read moreWhen to say No to Excel Nested IFs

Excel Table Running Total Formula

September 1, 2016 by Mynda Treacy

There are a few ways to approach a running total formula, but Excel Tables require something special, or you're likely to end up with errors that aren't obvious. Watch the Video Download the workbook Enter your email address below to download the sample workbook. Get Workbook By submitting your email address you agree that we …

Read moreExcel Table Running Total Formula
Excel Dependent Data Validation

Excel Dependent Data Validation

August 16, 2016 by Mynda Treacy

I’ve written about how to create dependent data validation lists before here; Excel Data Validation with dependent lists, and here; Dynamic Dependent Data Validation. However the approach I’m going to cover in this tutorial is probably the best I’ve seen, especially if you have a lot of dependencies as it’s easily scalable. What is Dependent …

Read moreExcel Dependent Data Validation
CHOOSE Function

Excel CHOOSE Function Toggle

May 3, 2016 by Mynda Treacy

At first glance the CHOOSE function isn’t very exciting and typically you have to team it up with other functions to get the party started. Fair enough I suppose, after all, the more the merrier. In this tutorial we’re going to use choose to toggle through different aggregation methods (AVERAGE, MAX, MIN, SUM) as seen …

Read moreExcel CHOOSE Function Toggle
Excel MROUND Function

Excel MROUND Function

April 19, 2016 by Mynda Treacy

Excel MROUND function is perfect for rounding numbers to the nearest multiple. For example, last week Brenda said she was making a timesheet and wanted to round the start and finish times to the nearest 15 minutes while allowing for a 7 minute grace period. The table below has some examples: Rounding these times to …

Read moreExcel MROUND Function
  • Previous
  • Go to page 1
  • Go to page 2
  • Go to page 3
  • Go to page 4
  • Interim pages omitted …
  • Go to page 7
  • Next

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • 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
 

Company

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

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.