In this post, I want to cover a common reporting requirement, which is a Power Query running total. I have previously covered a similar topic in a post about running totals in tables; however, those methods don’t work in Power Query. So, we’ll need to find an alternative approach. Both Tables and Power Query were Read More
Category: Blog
How to add a prefix or suffix to every cell using VBA
We can often find spreadsheets with cells containing codes that have been shortened. So, rather than Proj003412, the cell value has been reduced to just 3412. It’s not a big problem to extend the code back to the full version; we could achieve that with a simple formula, then copy and paste special values. If Read More
Convert color codes (RGB, HSL, Hex, Long, CMYK)
I often find myself converting between different formats of color codes; especially from hex to RGB and back again. Rather than firing up Google and searching for color conversion tools online, I decided to write my own User Defined Functions in Excel, now I can calculate the result within a worksheet instead. The functions in Read More
Apportion a value across cells with VBA
Today, I want to share a really useful time-saving macro with you. It’s a macro to apportion a value across cells. Now that I’ve written it, I’m starting to gain the benefits, and hopefully, you can too. The filename for this post is 0005 Apportion value across cells.zip. When to use the macro? Let’s say you’ve Read More
3 ways to change or insert images based cell values
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
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
