My Online Training Hub
Learn Dashboards, Excel, Power BI, Power Query, Power Pivot
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 …
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 …
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 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 …
Before the introduction of the IFERROR function in Excel 2007, if you wanted to hide errors displayed by some formulas you had to employ a combination of IF and the ISNA functions, which resulted in Excel having to do the calculation twice. Once to establish whether the result was an error, and again if the result …
Excel Absolute References are the backbone of ALL formula writing enabling you to copy and paste formulas with ease. 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 …
In my previous Excel VLOOKUP formula tutorial I mentioned that there are two ways you can use a VLOOKUP but most people know one way or the other, and only a few know both. As promised here’s the second way to use it, and I call it the Sorted List version as it relies on …
The Excel VLOOKUP formula is my favourite! Perhaps because it was one of the first formulas I mastered. It gave me an insight into the power of Excel and how it could help me in my job. Interestingly, there are two ways you can use the VLOOKUP function; exact match and approximate match. However, I …
In this tutorial we’re looking at the COUNTIF and COUNTIFS Formulas, and we'll take a look at a couple of different applications for them. Plus you can download a practice workbook, you'll find the file down below. The COUNTIF/S functions work ALMOST in the same way as the SUMIF/S functions only they’re slightly simpler. So …
SUMIF extends the capabilities of the basic SUM function by allowing you to tell Excel to only SUM items that meet a certain criteria. Whilst the SUMIFS function allows you to stipulate multiple criteria, hence the plural. Enter your email address below to download the sample workbook. Get Workbook By submitting your email address you …
In this tutorial we’re going to explain how to use the Excel IF function (also known as IF Statement), and look at a couple of different applications for it. With the IF statement you can tell Excel to perform different calculations depending on whether the answer to your question is true of false. Watch the …
