conditional formatting colour

Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Conditional formatting with Colour

Conditional formatting with Colour

resolvedResolved · Medium Priority · Version 2007

Anni has attended:
Excel Intermediate course
Excel Advanced course

Conditional formatting with Colour

I would like to know what the format would beif I want to format and calculate percentages. I.e. one square is 20%, I have 5 diff colours and can have any combination of colours to make up 100%

RE: Conditional formatting with Colour

Hi Anni

Thank you for your question; and welcome to the forum.

You can create conditional formats for a group of cells that contain percentages by going to the Home Ribbon and selecting the Conditional Formatting button from within the Styles group. If you go to the Highlight Cell Rules option and select the Value between option, this will allow you to set different colours for different ranges, e.g. 0-20% is red; 21-40% is orange - so on and so forth. You will need to set these rules one at a time.

I hope this helps.
Amanda

RE: Conditional formatting with Colour

Thanks for that Amanda, that's very useful, however not quite what I'm after - I think I didn't express myself correctly enough :-)

What I work on is a weekly planner, so five squares = 5 days of the week (20% per day).

Someone might work 3 days on a billable project, so 3 squares are coloured green (horizontally) and then have 2 days where they are available, say red.

I need to do weekly forecasts of how many days people are working or free or on training etc.

Can that be done - that I count coloured squares basically

(the answer for the above example would then say 60% green and 40% red)

Thanks!!

RE: Conditional formatting with Colour

Hi Anni

Thanks for clarifying.

I think you could only count based on the formatting of a cell if you did some VBA coding.

You can however count the number of times a word appears within a range, so if you had unavailable and available (or even U and A for short), you can use COUNTIF to count how many times each appears; then you could divide this by the total number of days to get a percentage.

Then you could apply conditional formatting to colour in addition if you wanted (e.g. if cell contains U, formatting is green; if cell contains A, formatting is red).

I have attached an example for you of how this could work. I hope this helps.

Amanda

Attached files...

Anni Excel example.xls


 

Excel tip:

Deleting a range of cells using the autofill handle

Firstly, select the range of cells for which you would like to clear the contents. Then drag the autofill handle to the the top left corner of the selection whilst holding down the shift key. Your selected contents should then be deleted.

View all Excel hints and tips


Server loaded in 0.1 secs.