How to use conditional formatting in Excel

In this week’s Excel 3 Tip Friday we take a look at conditional formatting, opening a new workbook using a keyboard shortcut, and the COMBIN function.

If you’ve got ideas for topics, shortcuts or functions that you’d like to see in this newsletter, let us know in the comments below.

 

A quick win you can learn today -

If you’ve never used conditional formatting in Excel, it’s an easy way to automatically change a cell’s colour depending on its content. There’s lots of options on how to use, including highlighting a specific value, or where a value is greater than or less than a value you enter.

One method I like to use is to highlight the top 10 or bottom 10 values in a range. It’s easy to set up, just select the cells that you’d like to apply the formatting to, and click on the Conditional Formatting button on the Home menu.

From here, you can drill into Top/Bottom Rules, and select the option you’d like to apply. From there, just select the colour and format you’d like to use, and once applied, you’ll just see those top or bottom items coloured in.

A keyboard shortcut I'm adding to my list -

Here’s an easy Excel keyboard shortcut to learn this week! Use Ctrl + N to open up a new blank workbook, without having to go into the file menu.

 

A function I've found super useful this week -

This week we cover the lesser known function, COMBIN, which will return the number of combinations for a given number of items in Microsoft Excel spreadsheet. For example, if you had a questionnaire and one question asked you select 2 out of a list of 8 things that you liked, you could work out the total number of possible combinations of 2 that could be chosen.

To use it, simply enter =COMBIN(total number of items, total number to choose), so in the above example that would be =COMBIN(8,2) which returns the number 28.

It’s the kind of function you’ll rarely use but will one day help you out when you need to calculate this kind of thing!

As always, I hope you find these Excel tips useful, and please do reply if you have any comments or questions!

 

- Martyn

 

Edinburgh, Scotland

November 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