Spreadsheets are easy to break. All it takes is one press of a key and suddenly every formula is displaying in an error message. That’s not good! This is why we apply worksheet protection, to prevent user error. Some think that worksheet protection is secure, however password protected worksheets can easily be unlocked with a … Read more
Data comes in all shapes and sizes, and since Excel is not a database, we face those shapes and sizes on a daily basis. One advantage of Tables in Excel is that they force us to apply some standards to our data structure, such as: There can only be one header row The headers must … Read more
I received a question from a reader recently, who wanted to sort a Listbox using VBA. As this seemed like a common problem which many users could face, I decided to dedicate a bit of time to create a more reusable solution. In this post, we will look at two different types of Listboxes: This … Read more
Checkboxes are one of the easiest ways to create interaction with users. Controlling them with VBA makes them more flexible and more useful in Excel applications. Here is a collection of the most common code snippets and settings. Create Check Boxes The following code will create a checkbox Sub CreateCheckBoxes() ‘Create variable Dim chkBox As … Read more
The Excel team recently announced new dynamic array formulas, which can create unique lists, sort and filter with a simple formula. These new formulas are being rolled out to Office 365 subscribers over the next few months. However, not everybody has the subscription and will upgrade when it is sensible for their business, often combining it with … Read more
One of the most popular methods of running a macro is having a button on the face of the worksheet. These are simple enough to create with the basic Form Controls found on the Developer Ribbon, which is what makes them a popular option. But what if you had lots of buttons, all of which … Read more
Checkboxes placed onto the face of the worksheet are an excellent way for users to interact with a spreadsheet. Each checkbox can be linked to a cell, so that when clicked the value in the cell changes between TRUE and FALSE. But if you have a lot of checkboxes to create it becomes a real … Read more
Some Excel users love comments; they put comments all over their workbooks. Other users despise them as they are unsightly and annoying. Whatever your preference, you can control them with VBA; add, delete, list on a worksheet – it can all be done with VBA. Adapting the code to your needs It is unlikely that … Read more
Back in 2007, the Excel data revolution started. At the time we saw a few new features, a new menu layout, a new file format, and 983,040 additional rows. We had no idea of the plans Microsoft had for Excel, but it was all laying the groundwork for what was to come. In the following … Read more
AVERAGE is one of those easy functions in Excel. Like, super easy! It has just one argument, the cells, or numbers to be averaged. So what’s the problem? It’s too easy to get wrong, yet we might not notice for weeks, months, years or ever. Look at the screenshot below. The result of each method … Read more