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
Category: Blog
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
VBA Code to insert, move, delete and control pictures
It is not often that we have to control pictures or images within Excel. This post serves as a reference to cover most of the scenarios we’re likely to encounter when we do need to. This code can be used in conjunction with the user defined function to insert and change pictures based on a 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 Read More
2.6 Loops (part 3)
Previous Part: 2.5 Using Logic within VBA Loops recap In section 2.2 Loops (part 1) we covered the For loop, which loops through a piece of code a specified number of times. In section 2.4 Loops (part 2) we covered the For Each loop, which loops through each item within a collection of objects Read More
Excel’s AVERAGE function – the hidden pitfalls
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
Create dynamic chart titles with custom formatting
Titles are an overlooked aspect of many charts. A bland title misses out on so much rich information which could be used to enhance a users understanding of the report. Here is an example of bad and good chart titles: The 2nd chart title adds additional insight and provides a much richer experience for the Read More