When working with Excel, the worksheets order matters. The tabs should reflect the flow which a user can understand. Since there is no native feature to do it, using VBA to short sheets alphabetically can be a useful time-saver. But things are never as straightforward, are they? Sorting can be in standard or reverse order Read More
Category: Blog
Office Scripts – Sort sheets alphabetically
In this post, we are covering how to sort sheets alphabetically using Office Scripts. Actually, the final code will be flexible enough to sort sheets in many different ways. I’ll share the basic script, along with the steps to create a more dynamic and robust solution. By the end, we will have a script that Read More
Use Office Scripts with Power Automate
In this post, we are looking at how to use Office Scripts with Power Automate. They are both relatively new tools in the online world, and when used together, they create a new ecosystem for automation with Excel. Power Automate is a cloud-based low-code / no-code tool that enables automation through connectors into many Microsoft Read More
Office Scripts – Hide all sheets except one
In this post, we’ll cover the Office Script you can use to hide all sheets except one. I’ll share the basic script, along with the options to change the code to your requirements. Since Power Automate can use Office Scripts, I will also share the reusable code block to use with dynamic flows. Example Office Read More
VBA to hide all sheets except one
There is a common question that many Excel users ask: “How can I hide all sheets except one with a macro?”. So, let’s answer that very question. First, I’ll share with you the VBA to hide all sheets except one, and then the process to turn it into a reusable code block. Basic VBA code Read More
Office Scripts – Reverse number signs
We often need to reverse number signs in Excel. For me, this regularly occurs because the accounting signage for debits and credits is opposite to the signage of income and costs. I am sure in your chosen field there are plenty of scenarios also. Using native Excel functionality, there are plenty of ways to change 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
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
