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 9 examples of how to achieve this. Table of Contents BasicSyntax Examples Example 1: Copy a file Example 2: Copy a file using variables Example 3: Copy a file based on cell values Example 4: Check file existence before VBA copy Example 5: Avoiding errors when copying files Example 6: Selecting file to copy Example 7: Copy file with function ..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 can execute the script. It may be better than manually creating folders, but I believe there is a much easier way to create folders with Excel. In this post, we will work through a small project to create a tool that creates multiple folders at once. Or, if you just want the tool, you can d ..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 our own input forms. But until then (and I don’t know if that will ever happen), this gives us a good start. To illustrate this capability, let’s look at a common example of unprotecting all sheets in an Excel workbook. We will work step-by-step t ..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 easy it is to enter invalid values. I’m not talking about malicious actions here. I’m talking about simple everyday methods that circumvent data validation without users realizing it. If you are ready to find out what the issue is… and possible ways to resolve it, then let’s go! Table of Contents ..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 chart automatically updates for new data. In each stage, there are many approaches we could have used. This post contains one method; therefore, you may decide to take an alternative approach for creating your jitter plot in Excel. Table of Contents Example Calculations Sections and labels Chart values H ..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 Download the example file: Click the button below to join the Insiders program and gain access to the example file used for this post. File name: 0198 Table slicers for user interactivity.zip Get access Watch the video Watch the video ..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 apply the same approach to the FILTER function too. Notes: At the time of writing, GROUPBY and PIVOTBY are only available to those on the Microsoft 365 beta channel. Table of Contents Data How to insert a slicer PIVOTBY function Connecting the slicer BYROW/LAMBDA/SUB ..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 stands for “End Of MONTH”, suddenly its purpose becomes much clearer. This function has a few quirks, so in this post, we look at the EOMONTH function in Excel and work through 8 examples. Note: All dates in this post are shown in a dd-mmm-yyyy format (e.g., 15-Nov-2024). Table o ..read more

Dumbbell dot plots are an excellent chart style for presenting comparative data. These chart styles quickly show the difference or progress between two data points. Often, Excel tutorials for dumbbell dot plots create charts that require manual updating for new data. But using the right Excel techniques these charts can be fully dynamic. So, in this post, we will create a dumbbell dot plot in Excel that updates automatically when we add new data. The final chart looks like this: Table of Contents Data Calculations Named Ranges Create the chart Add data to the dumbbell dot plot chart Chart e ..read more

Over the past months, the topic of using SUMIFS with arrays has come up a few times, so I thought I would write a post to help anyone with the same question. While I’m using SUMIFS throughout this post, it equally applies to SUMIF, COUNTIF, COUNTIFS, MAXIF, MAXIFS, MINIF, MINIFS, AVERAGEIF, and AVERAGEIFS. Table of Contents Example The problem with the SUMIFS function Using range functions to solve the problem Using FILTER to solve the problem How to simulate spilling with SUMIFS GROUPBY – the ultimate solution? Conclusion – SUMIFS with arrays Download the example file: Click the butto ..read more