Have you ever experienced the issue where Excel’s scroll bars become tiny? A small movement in the mouse can change the view by hundreds of rows or columns. This makes the worksheet exceptionally difficult to navigate around. The problem might reveal itself with an error message when trying to insert new rows or columns. In Read More
Category: Tips & Tricks
Crack Excel passwords with VBA
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 Read More
Remove blank rows in Excel
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
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
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
Create multiple checkboxes instantly with VBA
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
Create a running total in an Excel Table
Excel Tables are one of the best features of Excel. Tables were originally introduced as part of Excel 2003, under the name of Lists, but were expanded and renamed to Tables in Excel 2007. Whilst it is possible to use the standard cell referencing with a Table, they have their own referencing style. Using this Read More
Getting values from a closed Excel workbook
One of the things which scares me in Excel is seeing this message: So many things can go wrong with linked files, yet unsuspecting users tend to use them with pride. “Look, I’ve linked this so that I don’t have to update a cell, isn’t this cool!”. If they knew the risks they might not Read More
Excel Quick Tips [3]
We’re back for another set of quick Excel tips. These tips featured on Twitter initially, so there is only a short description with each video, but it is incredible what can be achieved with a small animated image and 280 characters or less. Quick Excel Tip #21 Insert a line break inside a Custom Number Read More
How to open multiple instances of Excel
In this post, I want to share the answer to a question I received from a reader. They had recently been upgraded to Excel 2016 from Excel 2010 and were struggling to open multiple instances of the Excel application. Each instance of Excel can have it’s own application settings (such as calculation modes). This is Read More