We have all experienced it; for whatever reason, the formulas in Excel aren’t calculating as expected. Most of the time, it is something simple that we have never thought of. But, once you know the most likely reasons, it is easier to troubleshoot the problem. So, in this post, we are looking at the most Read More
Category: Formulas
Calculate CAGR in Excel (Compound Annual Growth Rate)
In this post, we are looking 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 the growth is compounded each year. The compound annual growth rate is often used in finance to understand how different investments have performed Read More
How to forecast seasonality in Excel
I have previously written about how to forecast seasonality in Excel using the FORECAST function. However, with Excel 2016, Microsoft gave us an even easier method for forecasting seasonality. Rather than using the FORECAST function with a lot of trickery, we can simply use the FORECAST.ETS function all by itself. If you are using Excel Read More
Sum across multiple sheets in Excel
Have you ever had to sum the same cell across multiple sheets? This often occurs where information is held in numerous sheets in a consistent format. For example, it could be a monthly report with a tab for each month (see screenshot below as an example). Watch the video Watch the video on YouTube. I Read More
GETPIVOTDATA vs CUBE functions
We’ve been able to create PivotTables in Excel since the mid-1990s. Ever since then, people have been asking how to extract data from PivotTables using formulas. As great as PivotTables are for analysis, they are not always the best for presentation, which is why extracting data using a formula is so useful. We currently have Read More
How to split cells in Excel
There are many circumstances where we receive information with multiple data points inside a single cell. This often occurs when the data’s original intention is slightly different from how we intend to use it. In these circumstances, we often need to split a cell into its constituent parts. This post will look at solving this Read More
Insert double quotes in an Excel formula
Within Excel formulas, double quotes are used to denote the start and end of a text string. Look at the following formula as an example: =”Here is some sample text” The double quotes are at the start and end of the text string. But what if we want the text string to include double quotes, Read More
INFO function in Excel
The INFO function in Excel returns information about the current operating environment. In total, there are seven pieces of information available: File path of the current folder Number of worksheets in open workbooks Cell reference to the top-left visible cell of the window Operating system version Excel’s calculation mode Excel version Type of operating system Read More
CHAR function in Excel
The CHAR function in Excel returns a specific character based on a code number from the operating system’s character set. Each operating system has it’s own character set: Windows has the Windows -1251 character set, which is more commonly known as ANSI Mac uses the Mac OS Roman character set. Excel online has a limited Read More
Advanced dynamic array formula techniques
In the final part of this dynamic array series, we’ll look at a few advanced formula techniques. We won’t be covering the individual functions in detail but considering how we can combine them to solve some tricky problems. Many of these techniques have been covered briefly as examples in previous posts, but now we’ll dig Read More
