You are a busy VA – that’s a given! A client has asked you to show her what her data means. What is the top value in the list of sales? Can you see values that meet a condition? Conditional Formatting will show the way.

What is Conditional Formatting?

Conditional formatting is exactly that, a format applied that matches a condition.

There are 5 kinds of Conditional Formatting to choose from:

  • Highlight Cells Rules – pick this to show cells that match a particular number/text value/date.
  • Top/Bottom Rules – choose this, for example, to find the Top 10, Top 10%, Bottom 10 or Bottom 10%.
  • Data Bars – these shade the cells based on top and bottom values, so the greater the value the more shading in the cell. You can pick the colour scheme to suit.
  • Colour Scales – use Colour Scales to apply a scale from smallest to largest and set what each colour means.
  • Icon Sets – Use Icon Sets to show crosses and ticks, traffic lights, etc., as symbols in the highlighted cells.

Create your own rules

You can also create and apply your own rules to automatically change the formatting of cells based on the contents.

Where does Conditional Formatting live?

You will find Conditional Formatting on the Home Ribbon. To use it, first select the cells you want to apply the condition to and then choose the type of condition to set, followed by the format you would like to see.

  1. Select the data
  2. Click on Conditional Formatting
  3. Select the type of formatting

Once you have decided which of the formats to use, you can then drill down and specify the exact format you want.

For example, if I want to see Total Sales at Koffee Island above £900, I will choose the Highlight Cells Rules and then Greater than, adding in the amount I want to look for and setting the format.

Have a play with all the different types of Conditional Formatting, you will be amazed at how easy it is to spot patterns and get information from your data when you can visualise it.

Change the background fill of a cell

Suppose your client has asked you to create a workbook or worksheet for her. She wants the background colour of some of the cells to change based on the text typed in or a number entered. For this, you will need to create a custom rule.

Use a formula to control conditional formatting

Let’s look at adding conditional formatting to show a range of colours – let’s say I have a list of rooms named Red, Yellow and Green and I would like the fill colour to change according to the name of the room. This is handy if, for example, you need to keep a visual track of room bookings.

Change the colour fill

I applied three formats to the cells one on top of the other.

If A2:A10 contain the word Red apply Red formatting

If A2:A10 contain the word Yellow apply Yellow formatting

If A2:A10 contain the word Green apply Green formatting

Apply the first condition

Select the cells to which you want to apply the conditional formatting and then click on Conditional Formatting and select New Rule.

Format cells that contain

In my example, I selected Format only cells that contain

Then picked Specific Text from the drop-down list

Added the word Green in this case

Set the format by clicking Format and going to the Fill tab to pick the colour.

Repeat for the other conditions

Go back and add the other two rules to the selected cells. Remember to change the criteria for each accordingly.

Now test your rules! Type one of the words into one of the cells that you selected when you started and watch the background colour change! Magic!

Just think of how else you can use this – to highlight expiry or renewal dates, to flag where stock quantities exceed or drop below a given level, or to show duplicates in a list. I’m sure you can think of other examples. And what’s even better, these flagged areas of your sheet will stand out from the rest so they aren’t overlooked.

Have a look at your data and see how this can help you. I’m sure you’ll find lots of ways.

To learn more about Microsoft Excel and how you can speed up routine tasks, take a look at my Microsoft Excel courses for Windows and Mac.

Or take a look at the HUB and have access to ALL my courses including any new ones that are added. Always the best value.

The session was well structured and the explanation was very clear. It was amazing how Shelley managed to squeeze so much info in just one hour and a half! Colin’s support and contributions were also very valuable, and so were “Virtual Veronica”‘s. Thank you for a most interesting and useful training session at the Miss Jones Virtual Summit!

Laura Carizzo- Royal Norwegian Embassy in Argentina

I haven’t used PowerPoint in 20 years, this has really boosted my confidence.

Lyn Parker

Course is packed with so much valuable information in a easy-to-understand and follow language, even for a complete beginner. I can respect the fact that sometimes it’s very hard for an expert to break the information down to basics. but Shelley has done it brilliantly. I was sure that I knew quite a bit but Shelley has proven me wrong even at the very beginning of the basics of PowerPoint. Lessons are short and precise which allowed me to easily transfer the new skill into practice without being overwhelmed and to carry on adding on more with each lesson. I had so many ‘Aha!’ moments and for most of them I couldn’t believe I used to pay graphic designer to do it for me, now, thanks to Shelley, I am looking forward to creating, designing, converting my presentations all by myself!

Iva Freeman – Vertex Human Capital

Shelley was very thorough and knew a lot of tip’s for using Teams, thank you 🙂

James O’Connor – Cavannah Homes

Very informative, constantly getting everyone in the meeting involved, loved every bit of it!

Dylan Teal Hopkins – Cavannah Homes
Fantastic and informative training and great to see all of the exciting functions with Microsoft Teams.
Laura Rigby – The Apprenticeship College
Shelley was really clear in her teaching and went at a pace that was helpful to us. We covered all we needed to know to get us started, as a small team, using MS Teams. She was also very helpful in the run up to the session, with check in calls and ensuring our platform was set up correctly. I would highly recommend Shelley to anyone wanting to learn to navigate their way around MS Teams.
Leigh Dowling – Innuous

Microsoft Teams One to One Training Session

Shelley was approachable, easy to work with, and agile with answering questions to apply the session to my specific needs

Melissa Marshall – Present Your Science

A course on Outlook had been on the top of my list when Shelley asked VA’s what they wanted to learn.

It was a real case of ‘so that’s how you do it’ or ‘I never knew that’. I have to say that I learnt so many great tips it has definitely improved the way I work in Outlook and my inbox is looking colourful and organised and I can’t wait to share this knowledge with my clients.

Jacqueline Leake – JLeake VA Services – Outlook

A great course with lots of examples and step by step instructions. I can now confidently create infographics and as a bonus, I learnt a lot of new PowerPoint functionality that will enhance all my PowerPoint work and save me so much time.

Jessica Bailey – Integral Resource

I really like Shelley’s courses. She has a clear and easy to follow teaching style. In ‘How to Create Fillable Forms’ I’ve learned about the functions of the Developer ribbon which will mean I’ll be able to create a bespoke Returns Form for a client who has an online shop

Freya Henderson – Virtual Office Orkney

Shelley was really clear in her teaching and went at a pace that was helpful to us. We covered all we needed to know to get us started, as a small team, using MS Teams. She was also very helpful in the run up to the session, with check in calls and ensuring our platform was set up correctly. I would highly recommend Shelley to anyone wanting to learn to navigate their way around MS Teams.

Sarah Clements – Inuous

Absolutely superb course, easy to follow and to refer back whilst putting it all into practice! I was unaware of the extent you can customise a presentation and it’s just made me want to know more – what’s next Shelley?

Denise Williams

I thought I had a pretty good grasp of Outlook until I did this course! In her videos, Shelley is clear and concise, and the language she uses is straightforward and jargon-free. Each module is broken down into manageable chunks, which helps keep your attention and means the course is easy to fit in around other commitments. I’ve created email templates and implemented rules that are already saving me time, and as all the other things I’ve learnt start to become second nature, the positive impact on my efficiency is fast becoming clear. This course has been incredibly beneficial and I would highly recommend it to anyone looking to strengthen their skills and boost productivity.

Pip Doleman VA

I thought I knew Powerpoint really well, but I wanted to specifically learn how to use Powerpoint to create better infographics, easily and quickly. Shelley explains everything in a very logical and clear way, so I understood everything the first time I heard it. The most useful thing I learnt was that Powerpoint can do so much more than I thought it could. By also using the short cuts that Shelley showed me I am much quicker than I have ever been and I thought I was already quick!

Susan Marot

Shelley Fishel of Tomorrow’s VA has a wealth of experience in Microsoft Office. I met her in person 2 years ago at the PA Show in London. I also saw her in action in a fab workshop and knew she was the whizz that everyone had told me she was. I have since bought a lifetime access to her HUB and all the courses in there are a MUST if you are a VA on a mission. The courses will give you confidence in all the Office 365 components and will catapult you to dizzy heights

Alex Hughes