• 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

real time data in excel

Real Time Data in Excel

May 24, 2018 by Philip Treacy

Updated April 2020 Excel does support getting real time data into the worksheet with the RTD function, but this requires programming a COM server with which the RTD function communicates. Power BI is able to make use of streaming data via API connections or PubNub, but what if I just want to pull some live …

Read moreReal Time Data in Excel
ALT codes for symbols and characters

ALT Codes for Symbols and Characters

March 29, 2018 by Philip Treacy

On IBM compatible PC's, many characters that can't be typed on a keyboard can be entered by holding down the ALT key and then using the numeric keypad to type the ASCII or Unicode code for that character. For example, holding down ALT and then typing 184 on the numeric keypad, prints ©, the copyright …

Read moreALT Codes for Symbols and Characters
essential excel shortcuts

23 Essential Excel Keyboard Shortcuts

January 16, 2018 by Philip Treacy

There are hundreds of Excel keyboard shortcuts but we don't use them all frequently. There'll be some that you use more often than me, but in this post I'm listing the keyboard shortcuts I use all the time. It was hard to pick just a few, it was like being asked to pick your favourite …

Read more23 Essential Excel Keyboard Shortcuts
Fix Excel dates formatted as text

Fix Excel Dates Formatted as Text

October 23, 2017 by Mynda Treacy

Dates incorrectly entered or imported as text are a showstopper. We need our dates entered as date serial numbers to be able to leverage the Date Functions, use them in math and lookups, or even use them in PivotTables. So, often the first thing we need to do is fix Excel dates formatted as text. …

Read moreFix Excel Dates Formatted as Text
Excel Date and Time Formatting

Excel Date and Time Formatting

October 20, 2017 by Mynda Treacy

Even though dates and time are actually stored as a regular number known as the date serial number, we can make use of extensive Excel date and time formatting options to display them just the way we want. We can access some quick date and time formats from the Home tab > in the Number …

Read moreExcel Date and Time Formatting
excel date and time

Excel Date and Time

October 17, 2017 by Mynda Treacy

The objective of this post is to teach you how Excel handles date and time and provide you with all the tools you will need. It’s designed to be read in conjunction with the accompanying Excel file, which you can download below. Download the Files Enter your email address below to download the comprehensive Excel …

Read moreExcel Date and Time

Excel Extract a Unique List

August 22, 2017 by Mynda Treacy

As with most things in Excel, there are many ways to extract a list of unique or distinct values from a column of data. We’ll look at the options, and the pros and cons of each. Download the Workbook Enter your email address below to download the sample workbook. Get Workbook By submitting your email …

Read moreExcel Extract a Unique List

Excel Find and Remove Duplicates

August 15, 2017 by Mynda Treacy

Sometimes we want to find and remove duplicates in Excel, other times we just want to identify or highlight duplicates. There are loads of ways to tackle this in Excel, so let’s look at the options. Table of Contents Find and Remove Duplicates Video Download Excel Workbook Highlight Duplicates with Conditional Formatting Identify Duplicates with …

Read moreExcel Find and Remove Duplicates
Script Lab Logo

Script Lab – Explore the Office JavaScript API & Learn to Build Add-Ins

June 6, 2017 by Philip Treacy

Microsoft recently announced a project called Script Lab, previously known as 'Add-In Playground', which aims to help you learn how to write add-ins for Office. Script Lab is actually an add-in itself and runs in Excel, Word or PowerPoint and can be installed from the Office Store. Script Lab will run in Excel, Word, or …

Read moreScript Lab – Explore the Office JavaScript API & Learn to Build Add-Ins
Excel Themes

Excel Themes

May 17, 2017 by Mynda Treacy

Excel Themes are a bundle of default colors, font styles and Shape effects. There are lots of built in Excel Themes available from the Page Layout menu: Hovering over a theme will give you a preview of the effect on your workbook. You’ll notice that different fonts will have an impact on your workbook layout. …

Read moreExcel Themes
Row/Column Shortcuts

Excel Row and Column Shortcuts

April 20, 2017 by Mynda Treacy

Inserting, copying, moving, or cutting and pasting rows and columns is something we do every day in Excel. And there are loads of ways we can perform these repetitive tasks. Inserting Rows, Columns and Cells We can use the icons in the Insert menu on the Home tab: Or even the keyboard shortcut: Ctrl+Shift+= (or …

Read moreExcel Row and Column Shortcuts

Excel Conditional Formatting Highlight Matches in List

February 22, 2017 by Mynda Treacy

In this tutorial we’re going to look at how we can use Excel Conditional Formatting to highlight rows in a table where a field matches any item in a list. Let’s look at an example, below is our table of data: And we want to highlight the rows that contain any of the categories in …

Read moreExcel Conditional Formatting Highlight Matches in List

Excel Custom Number Format Guide

February 17, 2017 by Mynda Treacy

I’ve put together this Excel custom number format guide as a resource for our members. There are loads of ways to apply custom number formats and as a result I find myself answering questions that are covered in this post on a daily basis. To be clear, number formatting in Excel is used to specify …

Read moreExcel Custom Number Format Guide
Excel Conditional Formatting Thumb

Excel Conditional Formatting to Highlight Matches

February 9, 2017 by Mynda Treacy

Using Excel Conditional Formatting to highlight matches is easy when you team it up with a data validation list like this: Download the Excel File Enter your email address below to download the sample workbook. Get Workbook By submitting your email address you agree that we can email you our Excel newsletter. Please enter a …

Read moreExcel Conditional Formatting to Highlight Matches
locating values in large excel tables

Locating Values in Large Excel Tables

January 17, 2017 by Mynda Treacy

By Harold Graycar XL Numerics Developer of Excel for Customer Service Professionals The previous post on this topic included a method for finding the cell address for the maximum or minimum number in a table. Thanks for the correspondence  --  and especially that from Hervé, Saskia and Trevor for the questions about what happens if …

Read moreLocating Values in Large Excel Tables

Finding a Needle in an Excel Haystack

January 12, 2017 by Mynda Treacy

By Harold Graycar XL Numerics Developer of Excel for Customer Service Professionals When dealing with large tables containing ‘buckets’ of numbers, we often need to find the location of the largest number, the smallest number, etc. There are a couple of techniques in Excel for doing this: By eye: using conditional formatting etc. By formula: using …

Read moreFinding a Needle in an Excel Haystack
Toggle Conditional Formatting on/off

Toggle Excel Conditional Formatting On and Off

January 5, 2017 by Mynda Treacy

One of the nice features of Excel Tables is the banded row formatting, which makes it easier to read and scan your data. Unfortunately Excel Tables aren’t efficient with large data sets (over 100k rows), but we can replicate the banded rows with Conditional Formatting, and we can toggle it on and off at the …

Read moreToggle Excel Conditional Formatting On and Off
Excel Icons

Excel Icons

December 29, 2016 by Mynda Treacy

An awesome new feature available in Office 365 are Icons. You’ll find Excel Icons in the Insert tab of the ribbon in the Illustrations group: There’s a gallery of icons available and they’re grouped into categories to make them easily searchable: Update 1: You can use ALT Codes to enter 1000's of symbols too. Download …

Read moreExcel Icons

Excel Styles

September 28, 2016 by Mynda Treacy

Using Excel Styles in your workbooks can not only make formatting them quicker and easier, but also result in more professional and easier to use workbooks. What are Excel Styles Excel Styles are simply a group of formatting settings that can be applied in one fell swoop at the click of a single button. Those …

Read moreExcel Styles
Excel Clipboard

Excel Clipboard

July 12, 2016 by Mynda Treacy

The Excel Clipboard is an extremely useful tool that’s very well hidden. Most of us are familiar with being able to paste the last item copied as many times as we like until we press ESC, or copy something else. But few of us know that you can actually access the last 24 items copied …

Read moreExcel Clipboard
Excel Advent Calendar

Excel Advent Calendar 2015

November 24, 2015 by Mynda Treacy

Around this time last Year I created an Advent Calendar in Excel: To Count Up or To Count Down? Now, for me an Advent Calendar had always counted down to Christmas day, but to my surprise some people counted up, or rather counted the dates. That is they read the numbers on the calendar as …

Read moreExcel Advent Calendar 2015
Excel User Voice

Excel User Voice

November 10, 2015 by Mynda Treacy

I bet there's been the odd time you've said to yourself "wouldn't it be nice if Excel had..." Or maybe even "I really hate how Excel...." Well, now you can have your ideas heard by the very people responsible for developing Excel. Excel User Voice is where you can make suggestions for new features you’d …

Read moreExcel User Voice
Excel Flash Fill

Excel Flash Fill

August 25, 2015 by Mynda Treacy

Excel’s Flash Fill tool is so cool that you’ll be looking for reasons to use it! Introduced in Excel 2013 to rave reviews, Flash Fill uses a form of machine-learning techniques to reformat data automatically based on one or two examples you give it. No more complex formulas or VBA, with Flash Fill you'll be …

Read moreExcel Flash Fill
Excel Fill Series Tool

Excel Fill Series

August 11, 2015 by Mynda Treacy

The Excel Fill Series tool is like having a bundle of tricks at your fingertips. It can help with one of the most common tasks we do in Excel, which is to create a list of dates. Whether it’s a list of days, months or years, the Fill Series tool can easily handle them all …

Read moreExcel Fill Series
Excel Copy and Paste Visible Cells Only

Excel Copy and Paste Visible Cells Only

August 4, 2015 by Mynda Treacy

Have you ever wanted to only copy or paste visible cells? For example, below I have a table containing outstanding customer invoices. I want to insert a formula in column E to insert an overdue fee for invoices outstanding longer than 31 days: However, I don’t want the formula on the subtotal rows (row 5, …

Read moreExcel Copy and Paste Visible Cells Only
Apps for Office in Excel

Apps for Office In Excel

June 17, 2015 by Philip Treacy

I want to be clear that when I say apps for Office in Excel, what I am talking about are the apps that you insert into your worksheet from the Excel ribbon. Microsoft have stated that they are moving away from the term Apps for Office though and will be using Office add-ins instead. I’m …

Read moreApps for Office In Excel
Excel custom blank workbook

Customize Default Excel Workbook

June 9, 2015 by Mynda Treacy

Did you know that when Excel opens a new workbook it uses a template called ‘Book’ to create the new file? This means we can create a custom default Excel workbook by replacing the template file called ‘Book’ with one containing our own formatting. How to Customize the Default Excel Workbook It’s easy to do. …

Read moreCustomize Default Excel Workbook
Excel Fill Down IF

Excel Fill Value Down Rows based on Criteria

June 2, 2015 by Mynda Treacy

A few weeks ago one of our members, Kylie, sent me this question:   “How can I fill column E with the text ‘CURRENT’, if the Position Title in column D matches the first instance of ‘CURRENT’ in column E?” See example data below; Kylie wants to fill the cells bordered in orange: Note: I …

Read moreExcel Fill Value Down Rows based on Criteria
Excel Worksheet Protection

Excel Worksheet Protection

April 20, 2015 by Mynda Treacy

Excel worksheet protection is a great way to prevent people accidentally messing up the reports you’ve spent hours creating. However, it is not going to keep confidential data a secret. If someone really wants to get to your data, or see how your model was put together and all the amazing formulas you’ve used then …

Read moreExcel Worksheet Protection
Excel Filters

Excel Filters 4 Ways

March 10, 2015 by Mynda Treacy

Filters are one of my most used Excel tools. They’re not fancy, but they sure do earn their keep. I’ve even bestowed them with some prime real estate in my QAT: Filters allow you to quickly hide rows containing data that don’t meet your criteria. You can also apply multiple filters to one or more …

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