Data comes in all shapes, sizes and forms, so it’s not uncommon to find a dataset with a lot of blank rows. Sometimes it’s not an issue; other times, it causes big problems. Therefore, the best practice in Excel, is to remove blank rows. If there are only a few blanks, we could do this … Read more
It’s now time to bring this Power Query series to a close. And where better place to end than with some tips and tricks to help you to succeed. Change the default Close & Load options Power Query’s default Close & Load options assume we want to load the data into an Excel table. As … Read more
As we continue through this Power Query series, we have now reached a common and frustrating data format; data stacked in a column. In this post, we will investigate one basic method for unstacking data. While the example we are looking at below would most likely be created by a person who didn’t know how … Read more
The web is a vast place with so much information. However, just because the data is there does not make it easy to get at In this post, we’ll look at how we can load web data into Power Query. While you might get excited at the thought of absorbing data from everywhere with the … Read more
When creating a VBA UserForm, we generally set it to a specific size. Most other forms and windows within the Excel and Windows environments do not have a fixed sized; they can be resized by the user. With a bit of coding magic, we can achieve a similar resizing effect for our VBA UserForms. This … Read more
While working on a project recently, I thought about hiding the [X] close button which is at the top of the VBA UserForm. I managed to identify three options for achieving this. If you are thinking about the same thing, then this post will give you the answers you’re looking for. Download the example file: Join … Read more
I use the terms Sheet and Worksheet interchangeably when talking about Excel, I think most users do. Google also appears to think they are the same thing; If I search for “How to loop through Sheets with Excel VBA”, all the results returned on the first page refer to Worksheets. Yet, Sheets and Worksheets from … Read more
To merge or not to merge? That is the question. And it’s a questions which divides the Excel community. We find the Merge & Center button is on the Ribbon, so it appears to be the only option. But soon we are greeted by these types of annoying error messages: Excel has a secret alternative … Read more
Merged cells can be frustrating when using VBA code, as they just don’t operate in the same way as normal cells. Even when using Excel without VBA, merged cells cause a whole host of frustrating error messages. Even if you don’t use them, lots of other users do. So you will encounter merged cells in … Read more
When creating reports, it is sometimes necessary to highlight cells to draw attention to them. The most obvious method to achieve this is to change a Cell’s border color, I did that for many years. It’s quick to apply, but the pain comes when it’s time to remove the color, the formatting of every cell … Read more