Another Friday rolls around and the summer is going fast! 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.
If you find this useful or have requests or ideas for future editions, send me a message or comment at the bottom of this post! Now, on with the tips.
A quick win you can learn today -
There are several different types of Excel file that you can use to save your work - the default is .xlsx, and if you’re using macros you’ll need to use .xlsm (a macro enabled workbook) to save your code with your work.
There’s also a third main type of Excel file - a Binary Workbook, .xlsb, which can be used to compress large data files.
The advantages of using a binary workbook with a large file are:
Loading time is faster
Formulas calculate faster
File size is smaller (often by around 30%)
Macros are still supported
More secure - when password protected, these files are harder to decrypt.
A keyboard shortcut I'm adding to my list -
I’m always looking for ways to save time in Excel! One function I only recently learned adds the SUM function to the end of a table automatically. Use Alt + = to enter the SUM() function (AutoSum) to sum the adjacent block of cells. Just make sure you check that Excel selects the right range in the function!
A function I've found super useful this week -
Sometimes you can have cells which can contain either text or a number (For example, a sales column might have ‘Not applicable’ written in some cells. So it can be useful to apply a function to determine if a cell contains a number or not. Use =ISNUMBER to return TRUE if the value is a number - you could combine this with an IF to perform an action on the cell depending on whether or not it contains a number.
An added bonus - as Excel also sees dates as numbers, you can use this function to determine if a cell contains a readable date.
I hope these tips were useful, keep the suggestions coming on the facebook group here, be sure to join if you're not already a member.