Excel Off The Grid 
VBA in Excel gives us access to parts of the Windows environment. Therefore, there are many file-based activities we can achieve with VBA Macrs in Excel. One common method is using VBA to copy files from one folder to another. In this post, we look at 10 examples of how to achieve this. Table of … Read more
A weighted average (also known as the weighted mean) is a calculation that provides an average where values have different levels of importance. In certain scenarios, Excel’s AVERAGE function is not the right chose. It can provide misleading results as it assumes each value has the same impact on the result. Thankfully, we can easily … Read more
Over the past few months, I have seen many videos showing how to create multiple folders at once using a .bat file (also known as a batch script). Often these videos leverage Excel to create the folder paths. Then, they undertake various manual actions to get the .bat file into the correct location, so they … Read more
Recently, Office Scripts gained the ability to run scripts using parameters. This means we can provide custom values to change the outcome of the Office Script. This suddenly makes Office Scripts significantly more useful as we can create more reusable Office Scripts. We are still hoping Office Scripts will gain functionality that enables creation of … Read more
Excel has a dirty little secret – The data validation does not stop a user from entering an incorrect value. That’s pretty shocking, as that’s the whole point of data validation. 😲 Many of us rely on data validation to ensure other users only provide valid data entry. However, many are not aware of how … Read more
You’ve built an amazing dynamic report; a user selects a business unit to view, and everything updates automatically – it’s beautiful. The only issue is that Company A has sales of $30,000,000, Company B has sales of $30,000. Displaying values in 1 decimal place millions makes sense for Company A. $30,000,000 becomes $30.0m. But it … Read more
At this year’s Global Excel Summit, two presentations included Jitter plots. So, this got me thinking about how I would create one in Excel. If you’re not sure what a jitter plot is, this is the final chart we are creating in this post: I decided to make the jitter plot 100% dynamic so the … Read more
Having looked at how to use slicers with PIVOTBY and FILTER in a previous post. Let’s take this a step further and discover how we can use Table slicers for more advanced user interactivity. Table of Contents Example Getting the table slicer selections Changing and applying table slicer selections Disconnected slicers Slicer selections LAMBDA Conclusion … Read more
Microsoft recently announced the GROUPBY and PIVOTBY functions for Excel. Because these new functions provide PivotTable-type functionality, many have asked if we can use slicers with these functions. The answer is Yes! So, in this post, we look at how to use slicers with PIVOTBY in Excel. But, we also go one step further and … Read more
One of Excel’s most useful functions for accountants, financial modellers and data analysts is the EOMONTH function. This function calculates the last day of a month, based on a specified number of months in the past or future. From the name, EOMONTH, it may not be clear what the function does. Once we realize EOMONTH … Read more