SharePoint is becoming a common data source for many organizations. With the increase of users on Microsoft 365 licenses, I think we can assume this will continue to grow. Teams, OneDrive, and Lists are all built on the SharePoint platform; therefore, it is easy to interact with SharePoint without knowing it. Given this increase, I Read More
Category: Blog
Automatic commentary writing formula in Excel – Amazing LAMBDA
For a finance professional in industry, nothing is more annoying than writing the period-end or forecast commentary. (OK, maybe the words “last-minute change to provisions” is more annoying, but the commentary is right up there). You’ve created the reports, found the reasons for the variances, and now you need to write it as a text Read More
Browse for file path with VBA: insert value into cell
In this post, we look at how to browse for file path with VBA, and then insert the selected filepath into a cell. This post is inspired by a question I received: “If you would want to add a browse button right beside the cell that contains the file path so that you won’t need Read More
VBA Rename File (How to + 5 code examples)
VBA in Excel does not restrict us to just a single application. VBA gives us access to the windows environment too. With this we can perform a lot of common file-based actions. One of the most common is to rename a file. In this post, we will look at 5 examples of renaming files with Read More
How to remove spaces in Power Query
In a previous post, we looked at various methods to remove additional spaces in Excel. One of the methods involved creating a custom function in Power Query. As this is a more advanced method, I wanted to go a bit deeper and describe the method to remove spaces in Power Query. Excel TRIM vs. Power Read More
How to create user forms in Office Scripts (4 ways)
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
How to create nice looking Office Scripts button (3 ways)
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
How to email or save Excel images with Power Automate
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
How to create dynamic chart legends in Excel
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
How to run Power Automate from Excel with Office Scripts or VBA
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