How to denote data with different characteristics in Excel

Welcome to your weekly 3 tip Friday update, it’s just a short article of Excel tips before the weekend. Each week we go over a tip, a shortcut and a function - making it easy for you to get better at Excel.

I’ve received a few great content suggestions recently - please keep them coming, let me know what you’d like to see covered in these updates. 

 

A quick win you can learn today -

When you’re producing or editing a table, it can be tempting to use colours to denote data with different characteristics - those past a certain date or above a certain number. This can be useful for presentation, but it’s awful for data analysis. 

Microsoft clearly know people do this out of habit and in recent versions have added the ability to sort by colour - but I would strongly recommend that you avoid doing this for data, save it for presentation only. 

The reason for this is that it can make functions harder to write, and makes your table prone to formatting errors (what if someone else uses the wrong shade of green?). A better solution is to add an additional column to denote the cells with that characteristic. 

 

A keyboard shortcut I'm adding to my list - 

One of the best known functions on Windows, let alone Excel, is Ctrl+S to save your work. 

Until recently I didn’t know there is actually a shortcut for Save As too - which is a huge time saver for me. Hit F12 to bring up the Save As dialog. 

 

A function I've found super useful this week - 

It’s simple enough to sort a table of numerical data from smallest to largest, but what if you want to sort by another column, but still show the smallest to largest rank?

The RANK function allows you to do this by showing the numerical rank of a value within a dataset. To use it, there are 3 components to the function:

  • Number: what value do you want to rank

  • Ref: the range of values to rank against

  • Order: enter 0 to rank in descending order or 1 to rank in ascending order.

It’s super easy to use and a useful column to have in your data - it will work no matter what order the data is in. 

 

Hope this week’s tips 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