My Online Training Hub
Learn Dashboards, Excel, Power BI, Power Query, Power Pivot
Often data imported into Excel from another system isn’t quite in the format we want. Text can be split across columns, for example; the telephone area code below is in column A and the remainder of the number in column B. Here you can use the CONCATENATE function to merge cells together into one cell, …
If you work in Excel with data imported from other databases you’ll often find it doesn’t import it in the format you want. For example, I imported some Google Analytics data about our website traffic and the dates are formatted like this: Tuesday, February 1, 2011 While this looks like a date format, in my …
In my last tutorial I showed you how to insert a Subtotal using the Subtotal tool. If you’ve read that tutorial you will have seen how Excel can also conveniently Group and Outline your data so you can easily group and ungroup the rows to see varying levels of detail. Thankfully you can insert these …
The dashboard in your car is one of the key tools you use to monitor how it’s running, so it’s appropriate that the business world has borrowed the term and applied it to a series of graphs and key information that fits onto one page. A dashboard view of your business’s performance, if done well, …
The Subtotal tool in the Outline group on the Data tab of the ribbon automatically inserts subtotal formulas and grand totals for a column or columns. Excel subtotals can be SUM’s, AVERAGE’s, COUNT’s, MIN, MAX and more. This tool can give you valuable information in a few clicks, transforming your data like this: How to …
A little while ago I showed you how to do a lookup to the left using the INDEX and MATCH functions. In this Excel tutorial I’m going to show you how you can do a lookup to the left with a VLOOKUP formula together with the CHOOSE function as an alternative. Let’s look at our …
Excel Filters are right at home with large tables of data. You can use them to limit the data displayed in your table to only that which meets one or more criteria you specify. When your data is filtered Excel hides the rows that do not meet your criteria. You can apply them to more …
An Excel Pivot Table is one of the most powerful productivity tools available, but some common data layout mistakes can make Pivot Tables difficult, frustrating and downright scary for some users. Thankfully these issues are easily fixed. I've put together a short video that'll get you started with Pivot Tables, and when your data is …
Excel is 25! And to celebrate, Microsoft have put together two short video depicting the beginning, way back in the 80's right through to how Excel has transformed over the years. Watch it: To be inspired Get some ideas Just because you love Excel Give you an excuse to have some cake to celebrate! Your …
Imagine examining hundreds of rows of raw data in Excel in an attempt to find a pattern or trend. You’d go mad! Thankfully one of the tools we can use to make this task simpler is Conditional Formatting. The Conditional Formatting features in Excel 2007 are massively improved and now include many default Conditional Formats, …
Here are some easy solutions to Excel cell formatting frustrations that I get asked about all the time. 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 valid email address. Download the workbook used …
Calculating time in Excel can be very frustrating, especially when all you want to do is sum a column of times to get the total, but for some reason you end up with a random number like in the example below. Let me explain what’s going on and how to calculate time in Excel. Since …
There’s a little known tool in Excel that will convert your text into speech and read your worksheet to you. It’s called Excel Speak Cells and not many people know about it because you won’t find it in any of the tabs on the Ribbon. It sounds like a gimmick, but it’s not just for …
Learning how to customise a cell format in Excel allows you to not only format your data the way you want, but in some instances it can save you time. Before we dive in you need to know that despite how the text appears after you’ve set your custom cell format, the underlying value is …
In this tutorial, we'll dive into the powerful Excel INDEX and MATCH functions, which are essential for manipulating and analyzing large sets of data. We'll start by exploring what these functions do and how they retrieve specific information from a table, and then we'll write INDEX and MATCH formulas together as an alternative to the …
Quite often we want to see a running total or running balance column for our data. There are a few ways we can do this, the technique differs depending on whether your data is in an Excel Table or not. Watch the Video Download the Workbook Enter your email address below to download the sample …
The Excel HLOOKUP formula is just like the VLOOKUP formula, only the table you are looking up is laid out in a horizontal list, rather than vertically. Hence the ‘H’ for horizontal, and ‘V’ for vertical. Just like the VLOOKUP, the HLOOKUP function can be used to find an exact match or used in a sorted …
You may have worked out by now that I am a big fan of Excel, after all that’s what I mostly write about. Even though I’ve been working with Excel for 15 years it wasn’t love at first sight. It was stressful and frustrating at first, but now that I’ve mastered it I enjoy teaching …
The OFFSET function is one of Excel’s best kept secrets. Probably because it’s quite tricky to explain, and can be confusing to understand. In this tutorial I’m going to do my best to keep it simple so you can get your head around it and then I'll cover a few of the most useful ways …
Creating Excel drop down lists, or Data Validation Lists as they're formally known, enables you to control the value entered into a specific cell by limiting the choices to those on a pre-defined list. Excel validates that the entry in the cell matches an item from your pre-defined list, or you can simply click on …
Your email is one of the most important things on your computer and you must have an Outlook backup process in place. Here I will describe two methods to use for Outlook backup, a manual method and an automated method. This article relates to Outlook 2007, and does not deal with Outlook Express. The manual …
I'm sure you like to know handy little things that make your life easier or let you get things done just that little bit quicker. So here are 6 features of Microsoft Office that you should know about and use. AutoCorrect This is a handy little feature and can be used in more than one …
It’s common practice in Excel to format decimal places to get the desired rounding of numbers. But the problem with this approach is it can display totals that don’t appear to add up correctly. Take the screen shot below that shows 2+2+3=8. That’s because the underlying numbers are actually 2.4+2.4+3.2. Excel ROUND Formulas The solution …
If you have an email address, then you will get spam. It’s just a fact of life. I want to share with you some of my top tips to avoid spam. There are many reasons why you get spam but they all boil down to your email address being found by those bad, nasty people …
Excel Pivot Tables are one of the most powerful tools at our disposal, and once you understand how they work, they’re actually quite easy to insert and modify. In this example we’re going to work with a small amount of data (see image below) for illustration purposes, but Pivot Tables are in their element with …
Named Ranges in Excel are an essential tool for simplifying and enhancing the functionality of spreadsheets. They allow you to assign a descriptive name to a cell or range, which you can use in formulas and references instead of cryptic cell addresses like A1 or B2:D10. This makes your formulas easier to read and manage, …
Have you ever wanted your very own interactive Google Map dotted with locations relevant to your business? You might want to plot the locations of your stores or service agents. Or if you’re presenting performance data to management it’s a great visual representation of sales locations and density. These are just a few examples of …
If you think SUM is handy, wait til you meet SUBTOTAL. SUBTOTAL has so many tricks up its sleeve you’ll be mesmerised, but don’t be put off because this formula is simple. If you’ve ever worked with hidden rows you’ve probably wanted to find the total of a column excluding what’s hidden. What about finding …
Excel IF AND OR functions on their own aren’t very exciting, but mix them up with the IF Statement and you’ve got yourself a formula that’s much more powerful. In this tutorial we’re going to take a look at the basics of the AND and OR functions and then put them to work with an …
Did you know we can use a nested IF formula to extend the number of logical tests and therefore, possible outcomes? Simply put, this is multiple IF’s nested in the one formula. Prior to Excel 2007 the limit of IF's you could nest in one formula was 7. Excel 2007 has increased this to an …
