In Power Query, when we click the button to combine all the files in a folder, it automatically creates a Transform Sample File for us. Any changes made to the Transform Sample File are applied to all the other files in the folder before they are combined into a single table. This is a really … Read more
There is nothing more frustrating than finding out a previous employee has used passwords to protect an Excel file, and it turns out nobody else knows the password. Or maybe it’s worse when it’s your file, your password and you’ve forgotten it. Either way… you’re stuffed! However, before you give in, let me share some ideas on how … Read more
I’m sure you’ve not got this far without encountering your fair share of Power Query errors. Just like Excel and other applications, Power Query has its own unique error messages. You’ve probably forgotten the first time you encountered the #NAME? or #VALUE! errors in Excel, but over time you hopefully worked out what to do when they … Read more
One thing that continually causes problems for all data professionals is dates. Over time, Microsoft has given us a plethora of tools and Excel functions for handling dates, yet they still catch us out. Power Query is a tool for manipulating and cleaning data, and it has a lot of features for managing dates. The Power Query … Read more
Titles are an overlooked aspect of most charts. Bland titles miss out on so much rich information that could enhance a user’s understanding. So, in this post, we look at how to create dynamic chart titles in Excel. Here is an example of bad and good chart titles: Hopefully, you agree the second chart provides a … Read more
I encountered a situation recently where I needed to convert text to a formula in Excel. I was expecting to find a function that would achieve this. We can convert text to a range using the INDIRECT function, so there has got to be a function to convert text to a formula… right? Well… no… yes, … Read more
Using named ranges in Excel is a core skill. Normally, we refer to a specific range. However, named ranges have a secret feature… the ability to use a relative named range. They don’t sound too exciting, but they open up a new world of opportunity, which you may have never considered before. Let’s start with … Read more
Things change, and life moves on. Therefore, as organizational IT systems evolve, the Power Query solution you built previously might no longer use the right connector. Common examples are local Excel workbooks now stored on SharePoint, or SQL databases replacing CSV files. Previously, we looked at changing the location of the source data, but for … Read more
Excel Tables expand automatically whenever new data is added. This feature alone makes Tables one of the most powerful tools within the Excel user’s toolkit. A Table can be used as the source data for a chart and within a named range, both of which benefit from the auto-expand feature. Data validation lists would also benefit … Read more
Most Excel users love keyboard shortcuts; they help us perform tasks faster. Constantly switching between the mouse and keyboard adds a few minutes to our work day. So, if we want to make the most of this, wouldn’t it be great to create our own keyboard shortcuts for common tasks we perform? Well… we can. … Read more