This post looks at how to calculate CAGR in Excel. CAGR stands for Compound Annual Growth Rate; it calculates the annual percentage growth over a period of time, where growth is compounded each year. CAGR is often used in finance to understand how different investments have performed over time. Excel does not have its own … Read more
The space character for an Excel user can be a real problem. When exporting data from various IT systems, that export will often include extra spaces around values. Finding a way to remove spaces in Excel is a crucial skill for every user. We often can’t see the space characters, but Excel sees them. They … Read more
We come across drop-down lists on the internet and other software applications all the time. They are useful because they limit the options available to the user, as only pre-validated items can be selected. In this post, we’ll look at how to add a drop-down list in Excel. In Excel, there are three different types of … Read more
OK, let’s get started with Power Query. The first thing we need to do is get data into Power Query. In this series, it is our first real look at Power Query; we are also going to use this as an opportunity to explore the user interface. This post is structured into two main parts: … Read more
When using Excel, we often display numbers as decimals. However, that is not necessarily the best way for users to read the information; fractions can be a better option. We could enter fractions as text, but then we can’t use them in calculations. So, in this post, we’ll look at how to display fractions in Excel. Table … Read more
In this post, we look at how to combine rows with Power Query. The user interface gives us a way to merge columns, but there is no Power Query merge rows transformation. Therefore, I will show you how to achieve it in this post. There are two common scenarios in which we need to merge … Read more
One of the first things we learn in Excel is the magic of the $ symbol. It freezes the row or column, so the cell reference does not change when copying a formula. This is known as an absolute reference. With the introduction of Tables came a different (and more semantic) way to reference cells, called structured … Read more
With macros, we can automate Excel and save time; big tasks or small tasks, it doesn’t matter. All that matters is that we’ve become more efficient. In this post, I share 30 of the most useful VBA codes for Excel that you can use today. If you’ve never used VBA before, that’s fine. Part 1 … Read more
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 this … Read more
Within Excel formulas, double quotes are used to denote the start and end of a text string. Therefore, the obvious question is: how to add double quotes in Excel? Look at the following formula as an example: The double quotes are at the start and end of the text string. But what if we want the … Read more