One of the biggest complaints about Office Scripts is the lack of UserForms. UserForms exist in VBA and display an additional window for collecting user input. Unfortunately, there is no equivalent feature in Office Scripts. However, there are other methods to obtain user inputs that do work with Office Scripts. So, in this post, we … Read more
When Office Scripts were released, there were only two ways to execute a script (1) through the script editor or (2) through Power Automate. Users screamed out for a better way. Microsoft delivered a clickable Office Scripts button that could be added to the face of a worksheet. However, the default button style is a … Read more
It is common in many businesses to send regular email summaries or reports to senior executives. Typical scenarios include daily sales reports or cash-flow positions. These emails often contain images displaying the key information. With the current cloud technologies, we now have an easy way to automate this. So, in this post, we look at … Read more
Chart legends show us what each Series in a chart represents. These legends are often placed in boxes near the chart. However, research shows that separating the labels from the item in this way requires more brain power from readers and detracts from the key message. So, in this post, we look at how to … Read more
For a long time, I’ve been asking Microsoft for the ability to run a Power Automate flow from an Office Script. Office Scripts are restricted to operate only on objects within the workbook. Therefore, we can’t currently run a script that changes another workbook, or undertakes other activities outside of Excel. This significantly reduces the … Read more
One of the most frustrating errors in Power Query is the Formula.Firewall error. It only ever appears when we try to do something advanced. Plus, the error messages don’t seem to make much sense. However, once you understand what it does, you’ll realize the formula firewall is there to protect us. Therefore, we just need … Read more
If you use copy and paste while recording a Macro, we will see the code which refers to Application.CutCopyMode = False. You may wonder why it’s there? What does it do? In this post, we answer all your question about Application.CutCopyMode. Table of Contents Application.CutCopyMode = False in recorded macros What is Application.CutCopyMode = False for? … Read more
An Excel Add-in is a mini-software application that provides Excel with enhanced functionality and automation features. Whilst they can be created by third-party suppliers, even Microsoft themselves develop add-ins to provide additional functionality. Add-ins can take a number of forms: COM Add-ins and Office Download Store Add-ins will have their own installation processes. Excel Add-ins, however, have … Read more
In this post, I want to cover one of the most powerful lookup functions available in Excel, INDEX MATCH MATCH. Actually, to call it a function is poor terminology, as it’s three functions used together within a formula. It allows us to return a result based on a lookup from rows and columns at the same time. If … Read more
In the final part of this series, we look at a few advanced dynamic array 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 deeper. … Read more