From stock parts to holiday villas, from employees to logos, there are plenty of reasons to insert and automatically change an image based on a cell. You may think of this as a lookup function that returns a picture, rather than a value. Ultimately, the purpose is to link an image or picture to a Read More
Category: Blog
Remove blank rows in Excel
Data comes in all shapes, sizes and forms, so it’s not uncommon to find a dataset with a lot of blank rows. Sometimes it’s not an issue; other times, it causes big problems. Therefore, the best practice in Excel, is to remove blank rows. If there are only a few blanks, we could do this Read More
Application.CutCopyMode = False
If you use copy and paste while recording a Macro, you will see the code which refers to Application.CutCopyMode = False. You may wonder why it’s there? The recorded code looks a bit like this: Range(“A1:A10”).Select Selection.Copy Range(“D1”).Select ActiveSheet.Paste Application.CutCopyMode = False If you use VBA to copy and paste without that line of code, Read More
INDEX MATCH MATCH in Excel: 2 dimension lookup
In this post, I want to cover one of the most powerful lookup functions available in Excel, INDEX MATCH MATCH. Actually, to call it a function is poor terminology, as it’s three functions used together within a formula. It allows us to return a result based on a lookup from rows and columns at the same Read More
Power Query – Tips and Tricks
It’s now time to bring this Power Query series to a close. And where better place to end than with some tips and tricks to help you to succeed. Change the default Close & Load options Power Query’s default Close & Load options assume we want to load the data into an Excel table. As Read More
Power Query – Common Errors & How to Fix Them
I’m sure you’ve not got this far without encountering your fair share of Power Query errors. Just like Excel and other applications, Power Query has it’s own unique error messages. You’ve probably forgotten the first time you encountered the #NAME? or #VALUE! errors in Excel, but over time you hopefully worked out what to do Read More
Power Query – Custom Functions
In this post, we’are going to explore the topic of custom functions; What are they? When should we use them? And how to create them? At the time of writing, there are over 600 standard functions already in Power Query. Yet, those functions won’t always do what we need, or in the most efficient way, Read More
Power Query – Grouping and Summarizing Data
Looking back at this Power Query series, I can see that I have omitted to post about one of the more common and more powerful features; which is Group By. We have used it a few times already, but in this post, I wanted to give it a more detailed look. In Excel, there are Read More
Power Query – If Statements for Conditional Logic
In a previous post, we briefly looked at the if statement in Power Query, now we’re going to dig a bit deeper and understand how it really works. In Excel, IF is a core function, it’s one of the first ones we learn. Last week, we looked at Functions in Power Query, but we didn’t Read More
Power Query – Formulas
Formulas are the lifeblood of Excel; they are essential to achieve even basic tasks. Power Query, on the other hand, has been designed so that most transformations are accessed through the intuitive user interface. But Power Query has a formula language, with over 700 functions. It is there so that we can tackle some of Read More
