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
Category: Tips & Tricks
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
3 ways to change or insert images based cell values
From stock parts to holiday villas, from employees to logos, there are plenty of reasons to insert and automatically change an image based on a cell value. Or you may think if this a formula which changes a picture, rather than a number. Ultimately, the purpose is to link an image or picture to a Read More
Removing / cracking Excel passwords with VBA
There is nothing more frustrating than finding out a previous employee has used passwords to protect an Excel file, or maybe you set the password and have now forgotten it. Either way… you’re stuffed! However, before you give in, let me share some of the methods I use to remove passwords. When messing around with 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’s Speak Cells feature
It’s late; everyone else has left the office. And you can leave as soon as you get a specific report finished. But there are differences between two schedules, and you just can’t find them. You’ve been down the lists multiple times, you’ve ticked the numbers, but still, you just can’t find them. Does this sound Read More
Excel: Stop wasting time with bottom border!
How do you format totals in Excel? Most users will include a line between the list of values and the total. Look at the image below, nothing wrong with that, right? But, the key question is how would you apply this line? If you use the bottom border then you’re missing a trick. Understanding borders Read More