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
Category: Blog
Sorting ListBoxes with VBA
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: UserForm Read More
Create your own reusable Excel templates
Whenever we open a new session of Excel, we are presented with a variety of templates to use. There is plenty in there to look at, such as back to school planners, gantt charts and expense calculators, to name just a few. Depending on the version of Excel you’ve got, you may have some other Read More
Using an Excel Table within a Data Validation list
Excel Tables expand automatically whenever new data is added to the bottom. This feature alone makes Tables one of the most powerful tools within the Excel users 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 Read More
VBA code to contol Form Control checkboxes
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
List unique or duplicate values with Excel formulas
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 Read More
Spreadsheet Day 2018
Happy Spreadsheet Day! 17th of October is Spreadsheet Day, the day each year when we celebrate… you guessed it… spreadsheets. VisiCalc, the first spreadsheet application, shipped on 17 October 1979. Originally it was only compatible with the Apple II and cost $99. I don’t know if VisiCalc had formulas and functions. Sometimes I wonder what Read More
Assign macro with arguments to a Form Control button
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
Excel Off The Grid’s 2nd Birthday
Excel Off The Grid is now two years old. With approximately 65,000 words over 50 new posts it has certainly been a busy year. One of my goals for 2018 was to write more in-depth content, which I believe I have done. In the first year, the average content length was approx 800 words. During Read More
Excel Tables – Absolute cell & column references
One of the first things we learn in Excel is the magic of the $ symbol. It freezes the row or column, so when copying a formula, the cell reference does not change. With the introduction of Tables came a different (and more semantic) way to reference cells, called structured references. However, structured references don’t Read More