Welcome to your weekly 3 tip Friday update, it’s just a short dose of Excel help before you start the weekend. Each week we’ll cover a tip, a shortcut and a function - making it super easy for you to get better at Excel.
A quick win you can learn today -
Have you ever tried to find a file at work and found yourself in a sea of badly named files, feeling like you’re trying to find a needle in a haystack? There is an easy way around this - use good file naming conventions.
The first approach I recommend is to date stamp all of your files at the start of the file name, using the date format YYYYMMDD. Using this format means that your files appear in date order when ordered alphabetically, and it gives you a reference point as to when the file was created.
The second approach I use is to add version numbers - e.g. v1, or if your working with a file that will have many versions, use a decimal place e.g. v1.1. Never, ever, end your file names with the word FINAL (you’ll find it’s never the final version, and you’ll end up with names like FINAL4 and so on!)
If you use just these two methods, along with a descriptive file name, you’ll find it much easier to locate the correct file, and reduce the risk of old files being used.
A keyboard shortcut I'm adding to my list -
When you’re setting up a page to print correctly, I often find I have to click through to print preview several times to get it right… and each time you do this takes several clicks. Use the shortcut Ctrl +F2 to go straight to the Print Preview menu without all the clicks!
A function I've found super useful this week -
We recently covered the function SUMIF; an easy way to sum only the items that meet a certain criteria. There’s another similar function, AVERAGEIF, which will average cells in a range that meet a certain criteria. Even better, the function AVERAGEIFS allows you to add multiple criteria.
Say you have a table of shirt sales, with column A containing the colour, B containing the size, and C containing the sales value of the order.
For AVERAGEIF, there’s 3 components - the column containing the criteria you want to check, the criteria, and the range to average when the criteria is met. AVERAGEIFS is a little different, it starts with the range to average, and is followed by the criteria range and criteria - for as many criteria as you need to add.
You could find the average value of all blue shirts with:
And you could find the average value of all blue, large shirts with:
Until next time,
East Lothian, Scotland