My Online Training Hub
Learn Dashboards, Excel, Power BI, Power Query, Power Pivot
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 …
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 …
Excel’s Custom AutoFilter can perform a myriad of tasks that are often overlooked. Just last week we had a question from Alf: “I have a list of 7000+ words and want to search it with variables such as find all words where the third character is "u", or where the third character is "u" and …
You’ll be Amazed at what you can CTRL+F in Excel Excel’s Find tool is one of the first features you’re likely to learn and for many of us we only use its most basic feature, which is to find a particular word or value in a cell. It is such a diverse tool that I …
Today I’ve got a Christmas treat for you with an Excel Advent Calendar that Phil and I created. Before you get too excited I should say that there aren’t any chocolate/candy treats behind each door, plus you have to do a bit of work to actually open the doors! Don't worry, I'm not the Grinch... …
I arrived back from the 2014 Microsoft MVP Summit in Seattle on Sunday a little tired but bounding with excitement for the future of Excel. It was wonderful to meet my Excel Idols - the other Excel MVP’s and wax lyrical about Excel and other geeky topics. Microsoft took great care to make sure the …
In this tutorial we're going to look at how Santa is using Excel Custom Data Validation to limit the number of presents kids request on their Christmas wish list. With Christmas fast approaching our kids are already talking about what Santa might bring them this year. Little do they know that Santa will be implementing …
Did you know you can set different passwords to protect specific ranges of your Excel worksheets? It’s called Allow Users to Edit Ranges and you’ll find it on the Review tab in the Changes group: Let’s say you have several different users of a workbook and you want to restrict each user’s editing rights to …
Copying and Pasting are two of the most common tasks you’ll perform in Excel. This is especially true for me and I can tell because the shortcut keys (CTRL+C for copy and CTRL+V for paste) on the new keyboard I got just last week are already showing signs of wear. Most of the time I …
One of the most useful features in Excel are custom number formats yet they remain largely underused. In my Excel TV interview this week I shared a tip (at 52min) which uses Excel Custom Number formats inside the Excel TEXT Function, and coincidently Jordan Goldmeier (from the Option Explicit VBA blog) also used Custom Number …
Each year our sons choose a cake they want me to make for their birthday. Now, I am no baker and their cake of choice is usually something elaborate they find on the internet, and from there I talk them down to something I think I can manage. Last weekend our eldest son, Conor, turned …
They say the true Excel fan has uninstalled Word from their PC, and removed the F1 key from their keyboard (accidentally pressing F1 opens ‘Help’ which slows you down). After all, who needs Word when you have everything you need to write a letter right there in Excel? A must know tool for us Excel …
The Scenario You need to change the range referenced by your chart/conditional formatting/named range so you merrily open the Edit dialog box, like the one below for a chart, and start using your arrow keys to move the cursor into position, except all of a sudden the range referenced starts changing before your eyes. …
If you’re frustrated using Excel on a Mac because you don’t know the shortcut keys you’re used to using on a PC, then fret no more. Now you can quickly get up to speed with your Mac keyboard using a KeyCuts Excel keyboard cover. Plus you might learn a few new ones too. It has …
I like to use Excel hyperlink buttons in my Excel workbooks to help with navigation. I link them to a Shape that looks like a button to make it intuitive for the user, and also because I think it looks nicer than a text hyperlink. If I have a lot of worksheets in the file …
Sometimes it’s the little things that make a big difference so today I’m going to share with you a few of my favourite Excel shortcuts. They're not necessarily all keyboard shortcuts either. These are the type of skills that you pick up over the years watching your colleagues wield their Excel swords. So, without further …
Today I want to share with you an Excel secret. It’s so powerful that when you know this your Excel life will become much easier. I say it’s a secret because I get A LOT of questions from people struggling to use Pivot Tables, and functions like SUMIFS, COUNTIFS etc. Most of the time they’re …
The Easy Way In this example I’m going to show you how you can extract the domain name from a URL in Excel. For example, taking the list of URL’s below we want the part in between the http:// or https://, and the next forward slash. So the domain for the first URL below is …
Written by Alan Murray of Computergaga. Form controls such as option buttons, combo boxes and check boxes allow you to add interactivity to your spreadsheets. They are familiar to users whether they are Excel savvy or not, making your forms user friendly, quick and easy to complete. Form controls can be incorporated into your Excel …
Data Validation or Drop Down Lists are one of Excel’s greatest features, but I have one gripe with them, and that is the down arrow is only visible when the cell containing the data validation is selected. This is a pain when you use them in interactive Dashboard Reports because they need to stand out …
I’ve set up a table with some team names that I want to use in a Data Validation list. The reason I formatted my list in an Excel table is because I want the range to dynamically update when I add or remove teams from the list. My table’s name is Table1, as you can …
To be honest I don’t use Advanced Filters much. I tend to use the regular AutoFilter, or if I need more I’ll use the Custom AutoFilter. Not because they’re necessarily better, it’s probably more out of habit than anything else. But Advanced Filters have a couple of advantages over regular filters: The filter …
Moving back and forth between worksheets in a large workbook can be tedious. Of course you can use the shortcut keys CTRL+Page Up or CTRL+Page Down to quickly move from one worksheet to another (and CTRL+Tab to move between workbooks). However if there’s a lot of worksheets in a file the shortcut keys aren’t going …
Note: Excel Survey is now called Forms. A while back Quiara asked me if there was a way she could collect data from over 1000 different sites that was easier than their current process, which was via emails! I don’t know about you but the thought of keying data into Excel from over 1000 sites …
Help! Do you use a non-US-English version of Excel? If so, now is your chance to help out the wider Excel community with some feedback. The Excel versions used around the world essentially have the same core but there are differences evident when used with languages other than its native US-English. Even changes to the …
2012 has been an amazing and rewarding year at My Online Training Hub and we’re excited to see what 2013 will bring. Thanks When we started this website it was primarily because I loved working with Excel and I wanted to help others so they could love it as much as I do. I didn’t …
Back in June this year I had an idea to hold an X-Factor style competition where anyone could submit an Excel tip or trick that they were proud of. I didn't know how it would turn out, especially since there was no prize money. All I was offering was the opportunity to share an idea and be …
This Excel Factor tip was sent in by Marc Joannette of Montreal, Canada. Words by Mynda Treacy. Marc's job involves reissuing airline tickets. Part of this process requires a 'reason code' to be selected from a list. So he built a form where you can select a reason from a data validation list and the …
These Excel Factor tips are from Dr Isaac Gottlieb, professor at Temple University in Philadelphia. Every month Isaac publishes a series of Excel tips at his University website. He offered to share his tips with us….the problem I had was choosing just a few for this post, so feel free to visit his site and …
This Excel Factor entry was sent in by Rob Anderson of Oxfordshire, UK. Project planning and other tracking worksheets can be enhanced with Conditional Formatting. Rob uses a clever trick to quickly colour code cells using conditional formatting like columns F:M below: Remember painting by numbers? It’s a bit like that. Where 1 = blue, …
