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
Category: Blog
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. Adapting the code to your needs It is unlikely that any of the code will meet your exact requirements. 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
VBA Code to Apply and Control AutoFilter in Excel
AutoFilters are a great feature in Excel. Often they are a quicker way of sorting and filtering data than looping through each cell in a range. This post provides the main lines of code to apply and control the AutoFilter settings with VBA. Adapting the code to your needs Every code snippet below is Read More
Set chart axis min and max based on a cell value
“Not again…” I think to myself every time I change the minimum and maximum values of a chart axis. It’s such a tedious task, and I know I’ll be doing it again at a future point. It only takes a few seconds, but all that time starts to add up. There are various chart objects Read More