My Online Training Hub
Learn Dashboards, Excel, Power BI, Power Query, Power Pivot
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, …
If you use Excel Filters then you’ll love this time saving tip. This tip was sent in by Mohammed Mustafa of Jeddah, Saudi Arabia. Once you’ve set up your filters it requires at least a few clicks to apply them, but not anymore. With AutoFilter you can do it in two clicks! Taking the example …
Got a cool Excel trick you're proud of? I thought it'd be fun to hold an Excel Factor competition. Think X-factor (the TV show) but instead of massive fame and prize money (sorry, the budget doesn't extend that far yet), the prize is that you get to 'share the knowledge' with your Excel community and …
Did you know you can jazz-up your Excel Comments using Shapes, or even insert a Picture that conveys your message? Just take these weather themed comments I created: How to Change a Comment Shape In Excel 2007 and 2010 you first need to add a command to your Quick Access Toolbar. Note: I’m going to …
A few weeks ago one of our members, Perrin asked; “Is there a way to force a workbook to always open on a specific sheet? I have a workbook that has a directory and I would like users to always be directed to this sheet first when opening the workbook.” The answer is yes, but …
I can’t believe I haven’t already written a tutorial about Excel’s Go To Special feature. It is one of my favourite (a.k.a. favorite if you’re in the U.S.) closely behind VLOOKUP. If you import data from other systems you’ll invariably have to manipulate it to get it the way you need before you can analyse …
What if my side eBay bike business sold an extra 10%, what if I increased my price by $15 per bike, what if my costs increased by $5000 per year? What-if analysis is commonly done in Excel by saving different versions of the same workbook, or having a different sheet for each scenario which then …
