Microsoft Office Training
Last week I had an email from Mike asking how he can lookup a suburb in a range of columns and return the post code from the header row. I imagine his data was a bit like this: And in cell B9 he wants to find the post code for Herston. One way is with [...]
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 [...]
Here you’ll find a list of common Microsoft Excel formulas and Excel functions explained in plain English, and applied to real life examples. You won’t find any of that techno babble you get in the Excel Function Wizard here. The Excel Tutorials are grouped in line with the Function Library so they’re easy to find [...]
Last week I showed you how to create a Gantt chart in Excel by tricking a regular bar chart into thinking it could be a Gantt. At the time I mentioned that another way to create Gantt charts in Excel is with Conditional Formatting, but I said ‘that was a topic for another day’. When [...]
Gantt charts are commonly used in project management and while Excel doesn’t have a native Gantt chart in its chart library we can easily create one. Don’t get too excited, it’s a very basic one. If your job involves complex project management then you probably need a more sophisticated tool like Microsoft Project. However, for [...]
A few weeks ago David T asked me to help him understand a VLOOKUP formula in a workbook he’d inherited from a colleague who had left his company. It was a VLOOKUP formula like nothing I’d ever seen before so I thought I’d share it with you. Drum roll….. =VLOOKUP(E2&F2,IF({1,0},First_Name&Last_Name,Grade),2,FALSE) David’s question was ‘what’s the [...]
A little while ago Brendan asked me how he could check if values in a list fall between two limits. Now, it’d be nice if there was a BETWEEN Function, but there isn’t. Hence why the title of this tutorial isn’t ‘BETWEEN Function’, it’s ‘BETWEEN Formula’. I’ve got two options for you: BETWEEN with MAX [...]
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 [...]
I recently came back from a family skiing vacation in Whistler, Canada, which by the way was fantastic. Here we are at a lookout on a snowmobile trip we took. Anyway I digress…. one of the key pieces of information you want to know as a skier (or snowboarder) is how much snow is on [...]
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 [...]
... I would highly recommend My Online Training Hub for all your Microsoft Office needs .... Geniuses
