How to determine what type of data is stored in an Excel cell

dataminded

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 -

There are many chart types in Excel, but one missing type is a ‘bullet’ chart. These are a useful chart type that can be used to show several different metrics in a compact and meaningful manner. I first learned about this chart type in Stephen Few’s great book, ‘Information Dashboard Design’.

In this example, the bullet chart can show the current value, the target, and high/medium/low values. The only issue is they are extremely fiddly to set up! There is a guide here, which is worth working through if you find the chart type useful. I've created a simple template you can download and use here.

A keyboard shortcut I'm adding to my list - 

It’s easy to make spelling mistakes in Excel, because they are not automatically highlighted like they are in Word. You can use the F7 Shortcut to show the spell check menu, so that you can quickly check your work for typos and mis-spellings.

A function I've found super useful this week - 

Sometimes it can be useful to determine what type of data is stored in a cell - whether that be the colour of the cell, the width of the column, the address of the cell and so on. The CELL function can do all of these things, and more, it can be an extremely useful tool.

  • To return the address of cell A1, use =CELL(“address”,A1)

  • To return the column or row of cell A1, use =CELL(“col”,A1) or =CELL(“row”,A1)

  • To return the colour code of cell A1, use =CELL(“colour”,A1)

  • To return the number format of cell A1, use =CELL(“format”,A1) [this will return a code, there’s a table containing these codes here]

  • To return the type of cell A1, use =CELL(“address”,A1) [this will return b for blank, l for label/text, and v for value/number/date]

There's even more you can do with this function - see the microsoft article for more information.

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

- Martyn

Edinburgh, Scotland

July 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