• 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

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
  • Previous
  • Go to page 1
  • Go to page 2
  • Go to page 3
  • Go to page 4
  • 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.