Learn Dashboards, Excel, Power BI, Power Query, Power Pivot
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 …
Do you ever want to extract a list of unique records from a massive list like the one in column A below which has over 900 records? It would be tiresome to copy and paste the list and then delete the duplicate lines. Of course we could use the Remove Duplicates tool on the Data …
It's ok, no zebras get hurt in this process, it's actually all built in to Excel's Conditional Formatting 🙂 Conditional Formatting - Shade Alternate Rows To create this effect we need to create a new rule which uses a formula to determine which cells to format. Conditional Formatting Using a Formula Select the cells you …
We just arrived back from two weeks travelling around New Zealand with 3 other families. While there we managed to see some of the Rugby World Cup matches and do quite a bit of site seeing around the North Island. Our adventure started in Auckland where each family picked up a campervan which were to …
The other day I was in an Excel forum helping answer some questions. I like to help, because….well, I like to help, plus I like a challenge and it gives me ideas for things I can share with you. Anyway, I came across this tip (posted by someone else) and I had to read it …
If you work in large Excel files, Word documents or even e-mail, you may find it takes a long time to navigate and find the location you’re after. Hyperlinks are like a time machine in that they can take you there in an instant (but unfortunately they can’t take you back in time), and the …
If you find yourself opening the same workbooks each day when you start work, or when you do a particular job then you can save time by grouping these workbooks as a Workspace. It’s dead easy to do. Note: the Workspace feature was discontinued in Excel 2013 onwards. This only applies to Excel 2010 and …
