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
Category: Blog
VBA code to select all merged cells
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
Private vs Public Subs, Variables & Functions in VBA
we When writing VBA macros, the concept of Private or Public is important. It defines how VBA code within one module can interact with VBA code in another module. On social media you can to set parts of your profile so that everybody can see it (Public), or only those you allow, such as friends Read More
VBA code draw rectangles around selected cells
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
How to select all unlocked cells in Excel
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
Name an Excel Table when created
I love the Tables feature of Excel, it just makes everything else in the spreadsheet easier to work with. However, one of the biggest frustrations is that there is no option to name the Table at the time of creation. By default, we are given a unique name like Table1 if it is the first Read More
How to ask your boss to pay for Excel training
One of my favorite business quotes is from Peter Baeklund: A CFO asks a CEO: What happens if we invest in developing our people and then they leave us? The CEO responds: What happens if we don’t and they stay? Asking your boss whether the company will pay for Excel training can seem daunting at Read More
2018 Review & 2019 Goals
A new year is always an excellent opportunity to reflect on the previous 12 months and consider goals for the next. But before I do that, I just want to thank you for supporting Excel Off The Grid this past year. It is a privilege to support you in your Excel journey. 2018 Review There Read More
Dynamic arrays in Excel
Excel is about to change… like seriously, change. Every time you used Excel in the past, you have accepted a simple operating rule; one formula one cell. Even with array formulas, it was necessary to have a cell for each formula. But this is about to change; soon Excel will allow a single formula to Read More
SORT Function in Excel
SORT is one of Excel’s new dynamic array functions, which were announced by Microsoft on 24 September 2018. SORT makes use of changes made to the calculation engine, enabling Excel formulas to return (or “spill”, to use the correct terminology) results into multiple cells. As at the time of writing, Microsoft has only made this Read More