How to get the absolute value of any cell in Excel

How to get the absolute value of any cell in Excel

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.

Click here to sign up for a free Excel mini-course

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.

Click here to sign up for a free Excel mini-course

Hope these were useful, until next time all the best. 

- Martyn

Edinburgh, Scotland

June 2018

Get More Exclusive Content

Sent Directly to your inbox with Excel 3 Tip Friday. A weekly dose of Functions shortcuts and tips for getting more out of Excel.

We won't send you spam. Unsubscribe at any time. Powered by ConvertKit