• 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

Blog

You are here: Home / Blog
dynamic dependent data validation

Dynamic Dependent Data Validation

August 26, 2021 by Mynda Treacy

Before we had the luxury of dynamic array functions, creating dependent data validation lists typically required using multiple tables and named ranges, as shown here. Setting them up was laborious, however now that we have dynamic array functions, creating dynamic dependent data validation lists is much easier. Note: this technique requires a Microsoft 365 license …

Read moreDynamic Dependent Data Validation
try otherwise power query iferror

IFERROR in Power Query Using TRY OTHERWISE

August 19, 2021 by Philip Treacy

In Excel we can use IFERROR to if our calculation results in an error, and we can then tell Excel to produce a different result, instead of the error. Power Query doesn't have IFERROR but it does have a way of checking for errors and replacing that error with a default answer, it's called try …

Read moreIFERROR in Power Query Using TRY OTHERWISE
easily compare multiple tables in power query using list functions

Easily Compare Multiple Tables in Power Query

August 5, 2021 by Philip Treacy

Comparing table columns in Excel is a common task. You may need to identify items that are the same, different, or missing from these columns. In Power Query, table columns are lists and you can compare these lists using table merges. But merging can only be done on two tables at a time. If you …

Read moreEasily Compare Multiple Tables in Power Query
vlookup in power query using list functions

VLOOKUP in Power Query Using List Functions

July 29, 2021 by Philip Treacy

If you've done lookups in Power Query to pull values from one table into another, you may have used a query merge to do this. Mynda has written previously on how to do an Exact Match in Power Query and an Approximate Match in Power Query Here I'll be showing you how to use List …

Read moreVLOOKUP in Power Query Using List Functions

Auto Refresh PivotTables

July 22, 2021 by Mynda Treacy

In this tutorial we’re going to look at the options we have available to auto refresh PivotTables in Excel without using VBA. The process differs depending on whether you use Power Query to get the data or not. I’ll also show you the VBA method required for regular PivotTables. Beware because not all PivotTables based …

Read moreAuto Refresh PivotTables
power pivot running total

Power Pivot Running Total

July 15, 2021 by Mynda Treacy

In this tutorial we’ll look at how to create a Power Pivot running total that allows you to see the aggregation at the month, quarter and year levels enabling you to create charts like this: Note: parts of this tutorial require Power Pivot in Excel or Power BI. Check if your version of Excel has …

Read morePower Pivot Running Total

Secret Power Query Function List

July 8, 2021 by Mynda Treacy

Hidden inside both Excel and Power BI is a secret Power Query function list that you can bring up while working in the Power Query editor window…that’s if you know the magic word. You can use this list much like you might use the Excel Function wizard or to create a custom function. Clicking the …

Read moreSecret Power Query Function List

Show Items with no Data in PivotTables

June 16, 2021 by Mynda Treacy

Show Items with no Data in PivotTables is a handy setting that allows you to show items in the PivotTable even if there isn’t any data for them. For example, the PivotTable and chart below includes the East and West regions even though the Sum of Count is blank: Watch the Video Download Workbook Enter …

Read moreShow Items with no Data in PivotTables
fuzzy matching in power query

Fuzzy Matching in Power Query

June 10, 2021 by Philip Treacy

Fuzzy matching is the ability to match non-identical text based on how similar one string is compared to the other. You may have text that has been entered as the answer to a survey question, in which case you can't control what the respondent types. This can result in misspelling of words, for example, take …

Read moreFuzzy Matching in Power Query
QAT

Excel Quick Access Toolbar

June 2, 2021 by Mynda Treacy

The Excel Quick Access Toolbar or QAT as it’s also known is not only a handy place for commonly used icons, but it also enables some super easy keyboard shortcuts for your favourite tools. The QAT sits either above or below the ribbon. I prefer to place it above the ribbon as this takes up …

Read moreExcel Quick Access Toolbar
handling http errors in power query and power bi

Handling HTTP Errors in Power Query and Power BI

May 26, 2021 by Philip Treacy

If you are working with web servers, either because you are trying to scrape data or you are using a web based API, you will be sending and receiving data via HTTP. HTTP is the Hypertext Transport Protocol - it's just the name of the system used by web sites to transfer data. You use …

Read moreHandling HTTP Errors in Power Query and Power BI

Non-financial Excel Dashboards

May 20, 2021 by Mynda Treacy

Dashboards give a visual snapshot of your business performance. They typically show data spread over time and grouped by category. The most common dashboard examples are based on financial data, so in this video tutorial I’ll show you how to build non-financial Excel dashboards. I’ll be including some techniques I haven’t demoed in any of …

Read moreNon-financial Excel Dashboards
extract characters from strings in power query using text select and text extract

Extract Letters, Numbers, Symbols from Strings in Power Query with Text.Select and Text.Remove

May 13, 2021 by Philip Treacy

In this post I'm going to use the Text.Remove and Text.Select functions in PQ to extract characters from text strings. I'll show you how to extract letters, either uppercase or lowercase, and a mixture of both, and how to extract numbers, and I'll show you a really cool way to remove a wide range of …

Read moreExtract Letters, Numbers, Symbols from Strings in Power Query with Text.Select and Text.Remove

Share and Collaborate in Excel

May 5, 2021 by Mynda Treacy

We’ve been able to share and collaborate in Excel files simultaneously with our co-workers for quite some time. However, recently there have been some significant improvements to the Excel co-authoring experience that will blow you away. All you need to co-author in Excel is a Microsoft 365 subscription and an internet connection. You can even …

Read moreShare and Collaborate in Excel
highlighting data in power bi visuals

Highlighting Data in Power BI Visuals

April 29, 2021 by Philip Treacy

In this post I'll be looking at ways to highlight or label values that may be of special interest in visuals. Perhaps you are interested in seeing the minimum and maximum values, or some data points of significance like when your business held a promotion. Or you might want to highlight points that fall within …

Read moreHighlighting Data in Power BI Visuals

Secrets to Building Excel Dashboards in Under 15 Minutes

April 27, 2021 by Mynda Treacy

Building Excel Dashboards doesn't need to be a slow process. When your data is structured correctly you can use the built in tools (that you probably already know), to build dashboards super fast. Let me show you how in the video below. Download Workbook Enter your email address below to download the sample workbook. Get …

Read moreSecrets to Building Excel Dashboards in Under 15 Minutes
Power BI Organizational Data Types

Power BI Organizational Data Types in Excel

April 21, 2021 by Mynda Treacy

Power BI Organizational Data Types in Excel allow you to store attributes about your data in a single cell. This rich data can then be referenced with the formulas we know and love. Imagine having a single cell that contains everything you need to know about a product or employee or a company, to name …

Read morePower BI Organizational Data Types in Excel
shape maps in power bi

Shape Maps in Power BI

April 15, 2021 by Philip Treacy

In this post I'm going to show you how to use Shape Maps in Power BI, which are currently a preview feature. Typically, Shape Maps are used to illustrate the variation of a variable across a geographic area like a country or state. But you can also create custom shape maps for things like buildings, …

Read moreShape Maps in Power BI
Workbook Protection

Excel Workbook Protection

April 7, 2021 by Mynda Treacy

When you spend days crafting your latest report, the last thing you need are well meaning users messing with your file. In this tutorial I’m going to show you how to use Excel workbook protection which will allow your users to experience the report as you intended. They can: Click Slicer buttons Refresh data connections …

Read moreExcel Workbook Protection
static data tables

Static Tables in Power Query, Power Pivot and Power BI

March 31, 2021 by Philip Treacy

Ordinarily when you want to create a table in Power Query, Power Pivot or PBI, you'd write a query to load it from an external source. But any time you have data that won't change (or changes rarely), you can use a static table. That is, a table that doesn't need a data source, it …

Read moreStatic Tables in Power Query, Power Pivot and Power BI
Power Query change type using locale

Change Type Using Locale with Power Query

March 25, 2021 by Mynda Treacy

One of the most common issues I help people fix is data imported to Excel or Power BI using Power Query where the regional settings of the PC differ from that of the source data. Usually, the problem arises with dates. For example, data formatted dd/mm/yyyy imported on a PC with a mm/dd/yyyy date format. …

Read moreChange Type Using Locale with Power Query
get data from sharepoint or OneDrive

Get Data from OneDrive or SharePoint with Power Query

March 18, 2021 by Mynda Treacy

Nowadays many of us are saving our files to the cloud using OneDrive for Business or SharePoint Online. Unfortunately, it’s not straight forward to get data form OneDrive or SharePoint with Power Query, so in this tutorial I’m going to step you through the three scenarios: getting data from an individual file on OneDrive or …

Read moreGet Data from OneDrive or SharePoint with Power Query

Converting Decimal Time to Days, Hours, Minutes, Seconds in Power BI

March 11, 2021 by Philip Treacy

When you deal with elapsed time in Power Query you can make use of the Duration data type. Howevere when you load this into the Data model in either Power Pivot or Power BI, these durations are converted to decimal values. This post looks at how to use DAX to convert these decimals into human …

Read moreConverting Decimal Time to Days, Hours, Minutes, Seconds in Power BI
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
sort by column in power bi

Sort-By Columns in Power BI

February 18, 2021 by Philip Treacy

Normal sorting behaviour is to sort based on a numeric value, either higher to lower or lower to higher. But what if you want to have a custom sort order, or you are trying to sort categorical data in a specific way. How do you do that? Let's look at an example. The school our …

Read moreSort-By Columns in Power BI
excel pyramid chart

Excel Pyramid Charts

February 11, 2021 by Mynda Treacy

Excel pyramid charts are easy to build using bar charts or Conditional Formatting, but there are a few rules you must follow depending on the approach you choose. Before we look at the steps, keep in mind that these charts aren’t limited to visualising gender splits. They’re also handy for any type of demographic data …

Read moreExcel Pyramid Charts
personal share portfolio dashboard

Personal Stock Portfolio Dashboard

February 4, 2021 by Mynda Treacy

Keeping track of your share trading is easy with this personal stock portfolio dashboard. It uses the latest Excel tools* including Data Types and the STOCKHISTORY function to create a report that’s connected to live data and updates at the click of a button. *Note: requires Microsoft 365. Not suitable for Excel 2019 and earlier. …

Read morePersonal Stock Portfolio Dashboard
custom formatting strings in power bi

Custom Formatting Strings in Power BI

January 28, 2021 by Philip Treacy

You may be familiar with Custom Format Strings in Excel, if you are not then check this Excel Custom Number Format Guide. What you might not know is that you can also use custom number formats in Power BI, and not only can you format columns of data, these custom formats can be applied to …

Read moreCustom Formatting Strings in Power BI

Highlighting Periods in Excel Charts

January 21, 2021 by Mynda Treacy

Shading or highlighting periods in Excel charts can help users more quickly interpret them and identify patterns. In the chart below I’ve highlighted every second month to give a quick visual indication of each period, which allows the user to focus on the line instead of having to refer back and forth to the horizontal …

Read moreHighlighting Periods in Excel Charts
  • 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 22
  • Next

Sidebar

More results...

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

239 Excel Keyboard Shortcuts

Download Free PDF

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

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.