• 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
    • 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

Excel XLOOKUP Function

January 8, 2020 by Mynda Treacy

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

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
DGET Function

Excel DGET Function

October 9, 2019 by Philip Treacy

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 …

Read moreExcel DGET Function
Excel Goal Seek

Excel Goal Seek

October 2, 2019 by Mynda Treacy

Excel Goal Seek is another tool in the What-if Analysis suite on the Data tab of the ribbon. Goal Seek will find the value required to meet a target value you specify. For example, let’s say your 13 year old son wants to save $10,000 to buy a car when they graduate school. They have …

Read moreExcel Goal Seek
Extract Date and Time from DateTime Value

Extracting Date and Time from a DateTime Value

September 24, 2019 by Philip Treacy

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 …

Read moreExtracting Date and Time from a DateTime Value
excel data tables

Excel Data Tables

September 19, 2019 by Mynda Treacy

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 …

Read moreExcel Data Tables
Excel CONCAT Function

Excel CONCAT Function

September 12, 2019 by Mynda Treacy

The Excel CONCAT function, new in Excel 2019, replaces the legacy CONCATENATE function*. It’s a simple function that joins the values from two or more cells together into a single text string. Syntax: =CONCAT(text1, [text2], …) Where text1 is the contents of a cell or cells containing text, numbers, references to other cells, formulas, or …

Read moreExcel CONCAT Function
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
  • Previous
  • Go to page 1
  • Go to page 2
  • Go to page 3
  • Go to page 4
  • Go to page 5
  • Interim pages omitted …
  • Go to page 19
  • Next
  • 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
 
  • About My Online Training Hub
  • Contact
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

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.