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 -
When preparing a data table for analysis, pay close attention to how you treat blank cells - as they can profoundly impact your analysis.
For example, if calculating an average or a count, a 0 would be included in the calculation, and a blank would not.
With text fields, consider how to distinguish between a missing value, and an unknown value - perhaps it’s worth adding an ‘unknown’ category to your data to distinguish between them.
Also, keep your blank cells in the same format as the rest of the column - so if you have a numerical field, either leave your blanks empty or enter a 0 - don’t fill with the word ‘N/A’ or ‘NULL’ for example, as this can make functions, charts and pivot tables malfunction.
A keyboard shortcut I'm adding to my list -
Newer versions of Excel have much more useful menu bars than the old style back in Excel 2003, but I still find myself using the ‘Format Cells’ dialog to create detailed cell. Borders, or edit cell protection. Usually this is done with a right click, and the option is at the bottom of the menu... but there is a shortcut for this. Use Ctrl + 1 to make the format cells dialog box pop up.
A function I've found super useful this week -
Sometimes a numerical function can output both positive and negative values, but for analysis you want to know the absolute value. For example, you have a profit column, and also a column which states whether a profit or loss was made. Losses show as a negative number, but for chart purposes you want to show it as a positive number, but only show losses in your chart.
The function ABS will return the absolute value of any cell, as a positive number.
Hope these were useful, until next time all the best.