A top 10 list is a common form for displaying information, especially on dashboards and summarized reports. It is easy to create the list when working with sorted data, just cell link to the top 10 items in the list… easy! It’s also relatively simple when using Auto Filter, Tables and Pivot Tables, as it Read More
Category: Blog
Power Query – Absolute and relative references
In Excel, we can easily create absolute and relative references. A simple $ symbol here and there is all it takes. But Power Query is less flexible, therefore getting values from other rows is not as simple. So, in this post, we cover how to get Power Query absolute and relative references. The filename for Read More
Auto refresh Power Query on parameter change
A few days ago, a reader asked a question about using parameters in Power Query; they wanted to auto-refresh Power Query whenever a parameter changes. “One question, how do you set the query to refresh automatically when a value changes.” I’ve not tried this before, but I can understand the benefits. It reduces the need Read More
Crack Excel passwords with VBA
There is nothing more frustrating than finding out a previous employee has used passwords to protect an Excel file, and it turns out nobody else knows the password. Or maybe it’s worse when it’s your file, your password and you’ve forgotten it. Either way… you’re stuffed! However, before you give in, let me share some Read More
Swap ranges, rows and columns in Excel
Copying, pasting and moving cells are simple everyday tasks for an Excel user. But what if something already occupies the area we want to paste to? We have to move things into temporary locations before putting them into their final destination. Wouldn’t it be better if there were a simple way to swap ranges, rows Read More
Power Query – Percent of total or category
In this post, I want to cover how to calculate the percent of a total in Power Query. We’ll also take this one step further, to consider how to calculate the percent of a category. In standard Excel, these calculations are simple, as we are so used to the formulas to achieve this. But for Read More
VBA Tables and ListObjects
Tables are one of the most powerful features of Excel. Controlling them using VBA provides a way to automate that power, which generates a double benefit 🙂 Excel likes to store data within tables. The basic structural rules, such as (a) headings must be unique (b) only one header row allowed, make tables compatible with Read More
Power Query – Running Total
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
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
