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
Category: Blog
Why does Excel’s calculation mode keep changing?
You’re probably already aware that Excel has two main calculation modes: automatic and manual. Automatic calculation – calculates when a value changes or based on specific actions (inserting, deleting, hiding rows/columns, or renaming a worksheet, etc.). Manual calculation – only calculates when explicitly requested by the user: From the ribbon, click Formulas > Calculate Now 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). I see many examples where the user has clicked Read More
Office Scripts – Workbook & worksheet protection
As Excel users, we often find ourselves protecting and unprotecting worksheets and workbooks. While Excel’s protection is not perfect, it does prevent others or even us from accidentally making changes. It can be as simple as one keystroke that turns our worksheet perfection into an error-filled disaster. Unfortunately, protecting and unprotecting is an enormous time Read More
Office Scripts – Working with worksheets
Some of the most common Excel automation actions involve managing worksheets. Now that we have Office Scripts as an automation tool within Excel Online, it’s time to learn how to use worksheets within this new environment. If you have written VBA Macros before, many of the principles will transfer directly to Office Scripts. There is 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
Insert file path into cell with Browse button
In this post, I’ll show you the answer to a question that somebody recently sent me. “If you would want to add a browse button right beside the cell that contains the file path so that you won’t need to manually copy and paste the file path in the cell, how would you do that?” 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 the cell into its constituent parts. This post will look at solving this Read More
Create a Sankey diagram in Excel
Sankey diagrams are used to show flow between two or more categories, where the width of each individual element is proportional to the flow rate. These chart types are available in Power BI, but are not natively available in Excel. However, today I want to show you that it is possible to create Sankey diagrams Read More
All possible combinations from multiple lists in Excel
Imagine a scenario where you have two or more lists in Excel and want to create a single table of all possible combinations. The question might come in a simple form, such as: “How can we get a complete list of all General Ledger and Cost Centre combinations?”. Sounds simple, right? There must be a Read More
