In this tutorial, you’ll learn different ways to calculate the CAGR in Excel: Using Operators Using the POWER function. Using RATE function. Using the IRR Function. But before we dive into how to calculate CAGR in Excel, let’s first understand what it means (feel free to skip this section if you already know what it is). What is CAGR? CAGR or the Compound Annual Growth Rate tells us the growth rate at which our investments have grown on an annual basis....

One of the common tasks for people working with text data is to extract a substring in Excel (i.e., get psrt of the text from a cell). Unfortunately, there is no substring function in Excel that can do this easily. However, this could still be done using text formulas as well as some other in-built Excel features. Let’s first have a look at some of the text functions we will be using in this tutorial....

But this tutorial is not about that range. A ‘Range’ is also a mathematical term that refers to the range in a data set (i.e., the range between the minimum and the maximum value in a given dataset) In this tutorial, I will show you really simple ways to calculate the range in Excel. What is a Range? In a given data set, the range of that data set would be the spread of values in that data set....

Descriptive Statistic quickly summarizes your data and gives you a few data points that you can use to quickly understand the entire data set. While you can also calculate each of these statistical values individually, using the descriptive statistics option in Excel quickly gives you all this data in one single place (and it’s a lot faster than using different formulas to calculate different values). In this short tutorial, I will show you how to get Descriptive Statistics in Excel....

And one of those options allows you to sort your data based on the color of the cell. For example, in the below dataset, you can sort by color to get the names of all the students who scored above 80 together at the top and all the students who scored less than 35 together at the bottom. With the sorting feature in Excel, you can sort based on the color in the cell....

When to use Excel LEFT Function LEFT function can be used to extract text from left of the string. What it Returns It returns the specified number of characters from the left of the string. Syntax =LEFT(text, [num_chars]) Input Arguments text – The text string that contains the characters you want to extract. [num_chars] – (optional) the number of characters to extract from the left of the text string. If you omit this, is defaults to 1....

If you work with large datasets, there can be a need to create a search functionality that allows you to quickly highlight cells/rows for the searched term. While there is no direct way to do this in Excel, you can create search functionality using Conditional Formatting. For example, suppose you have a dataset as shown below (in the image). It has columns for Product Name, Sales Rep, and Country. Now you can use conditional formatting to search for a keyword (by entering it in cell C2) and highlight all the cells that have that keyword....

In this tutorial, I will cover all there is to know about the VBA Editor and some useful options that you should know when coding in Excel VBA. What is Visual Basic Editor in Excel? Visual Basic Editor is a separate application that is a part of Excel and opens whenever you open an Excel workbook. By default, it’s hidden and to access it, you need to activate it. VB Editor is the place where you keep the VB code....

Pivot Tables are fairly easy to use, and most Excel users get the hang of it after using it a couple of times. So if you’re a fan of Pivot Tables and use them quite often, it would be helpful to learn some common Pivot Table keyboard shortcuts that you can use to speed up your work. In this article, I will give you some common Pivot Table shortcuts that you can learn to become even more efficient....

But then, I decided to write one myself (the fact that I was somewhat of a statistics nerd in college also played a minor role). Now, before I tell you how to calculate moving average in Excel, let me quickly give you an overview of what moving average mean and what types of moving averages are there. In case you want to jump to the part where I show how to calculate moving average in Excel, click here....

Excel MINUTE Function – Overview Excel MINUTE function can be used when you want to get the MINUTE integer value from a specified time value. What it Returns It returns a value between 0 and 59 depending on the time value used as the input. Syntax =MINUTE(serial_number) serial_number – The value that contains the time that needs to be determined. Excel Minute Function – Examples Here are some practical examples of using the Excel MINUTE function....

There is a TRIM function in VBA as well (and it does exactly the same thing – remove extra spaces). Why do we need a TRIM function in VBA too? Fair question! While you can work with the inbuilt TRIM function in Excel worksheets to get rid of spaces. in some cases, you may need to do this using VBA. This could be as a part of a bigger code....

Can we look-up and return multiple values in one cell in Excel (separated by comma or space)? I have been asked this question multiple times by many of my colleagues and readers. Excel has some amazing lookup formulas, such as VLOOKUP, INDEX/MATCH (and now XLOOKUP), but none of these offer a way to return multiple matching values. All of these work by identifying the first match and return that. So I did a bit of VBA coding to come up with a custom function (also called a User Defined Function) in Excel....

While there are some existing formulas in Excel that can help you calculate growth rates, for calculating AAGR and CAGR, you would need a little bit of workaround (nothing too complex though). In this tutorial, I will cover how to calculate the Average Annual Growth Rate (AAGR) in Excel. But before I get into how to calculate AAGR in Excel, let me quickly cover what it is and what does it mean....

When you see a date in a cell in Excel, in the back end it is still stored as a number. In some cases, you may not want to show the date and instead show the serial number it represents. For example, instead of showing 31 Dec 2022 in a cell, you may want to show its numerical value, which is 44926. In this tutorial, I will show you some simple ways you can use to convert a date into a serial number in Excel....

In Excel, you can create a macro by recording it or by writing code in the VB editor. Once created, you need to run the macro. In this tutorial, I’ll show you different ways to run a macro in Excel. If you’re interested in learning VBA the easy way, check out my Online Excel VBA Training. How to Run a Macro in Excel For the purpose of this tutorial, let’s say we have a macro named ‘ColorCell’ with the following code: This one line code would fill the cell A1 of the active sheet with red color....

Simple calculation such as adding the values in a range of cells or counting the values in reach of cells is something that you would have to do when working with data in Excel. And in some cases, you may have to count only those cells that meet a specific criterion. And you can easily do that with the COUNTIF function in Excel In this tutorial, I will show you how the Excel COUNTIF function works with simple examples add a detailed explanation Let’s first look at the syntax of the COUNTIF function:...

When to use Excel FIND Function Excel FIND function can be used when you want to locate a text string within another text string and find its position. What it Returns It returns a number that represents the starting position of the string you are finding in another string. Syntax =FIND(find_text, within_text, [start_num]) Input Arguments find_text – the text or string that you need to find. within_text – the text within which you want to find the find_text argument....

While the impact on the performance may be negligible when there is less data, it becomes more profound as you add more and more data/calculations to the workbook. 9 out of 10 times, an Excel user would complain about the slow Excel spreadsheets. And there is hardly anything you can do about it. Well, that’s NOT completely true. The way Excel has been made, it does get slow with large data sets....

Comments in VBA could be really useful for beginners, where you can add a comment to a line of code (or a block of code) that explains what it does. So the next time you come back to the code, you’ll not be completely lost and would have some context because of the comments. Even for advanced Excel VBA programmers, once the code starts to get beyond a few lines, it’s a good idea to add context using comments (especially if there is a chance that someone else may have to work on the code in the future) And since it’s a comment, VBA ignores it while executing the code....