Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!

Subscribe for Free Excel tips & more!

Free Excel Forum

Calculating Percentage Based On Cell Color?

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

I have another task that I have been asked to add to my wonderful training spread sheet. As personnel expire on their annual/biannual/semiannual training the cells now turn red.

Is it possible to calculate a percentage off of this? I need to know a percentage of how many cells are expired or red in the spread sheet. That way I don't have to keep manually calculating a "readiness" percentage everytime they ask for it.

So lets say 10 cells are red (which meand they have expired) and the remaining 130 cells are either green or yellow (which means they are not expired), then we are roughly at 93% ready or complient on training.

View Answers     

Similar Excel Tutorials

Years, Months, Days Between two dates in Excel
I have found a rather convoluted method to Calculate the Number of Years, Months and Days between to Dates in Excel ...
Use Colors to Organize Excel Tabs
Did you know that you can add colors to your tabs in Excel?  This is a great feature that is simple but extremely ...
PMT Function - Get the Payment Due for a Loan in Excel
How to calculate the payment amount for a loan or similar financial instrument that has a series of constant paymen ...
NPER Function - Calculate Number of Periods Needed for a Set of Payments in Excel
How to calculate the number of periods required for an investment in order to get the desired return.  The number o ...

Helpful Excel Macros

Format Cells as a Percentage in Excel Number Formatting
- This free Excel macro formats a selection of cells as a Percentage in Excel. This simply changes a cell's number format
Filter Data to Show the Bottom X Percent of the Data Set in Excel - AutoFilter
- This free Excel macro filters data to display the bottom X percent of the data set in Excel. This is a great macro to u
Highlight Cells with Text or Formulas (non-empty cells)
- This macro will highlight all cells in excel which are not empty. This means that if a cell contains formulas, text, nu
Filter Data in Excel to Show Only the Top X Percent of that Data Set - AutoFilter
- This Excel macro filters a set of data in Excel and displays only the top X percent of that data. This means the highes
Highlight Cells which Contain Formulas
- This macro will highlight all of the cells in a worksheet which contain a formula. The first one listed will highlight

Similar Topics

Hello and thank you for your help in advance! I have a deadline to have this spreadsheet completed so please help!

I have put together a training tracker for my organization. I have figured out (thanks to this forum site ) how to change the cell colors based on the closeness to the expiration date.
My next goal is to have the percentages calculated and (if possible) color coated to show the percentage of people that are still valid in the separate columns E through L.

I would like <90% to have RED background in the cell, 90-99% with yello background, and 100% with green.

For example (please refer to the screenshot I have attached) in cell G73 I want to have the percentage of people whose "IA" training is still valid. I know you can't see the entire column but one person is expired (red) so the percentage would be around 99%. I want cell G73 to show 99% with a yellow background.

When calculating this percentage, ensure that it only reflects those which are valid. In other words, the "EXEMPT" cells which are gray should not be included in the calculation at all. The Green cells and yellow cells are still valid and should be included in the percentage. The red cells should be the only ones that take away from the percentage.

Thank you! let me know if this doesn't make sense.


I have been struggling with using conditional formatting in my training log to identify training that is either non-expired (green), expires in 30 days (yellow) and expired (red). Some of the training expires annually, others every three years and some does not expire. I've been able to turn cells one color or the other, but it doesn't apply correctly to various listed dates.

If anyone can help, I'd greatly appreciate it. I've attached the spreadsheet to give you a better idea what I'm trying to do.

Thank you.

Julie M.Training Log DRAFT.xlsx

I use conditional formatting on a training spreadsheet to change the colors of all cells (based on the actual training date entered into the cell) to green (trained) for due dates that are a year away, yellow (training required soon)for due dates that are 45 days away, and red (training required now) for due dates that are 30 days or less away. I then manually count the number of green cells and yellow/red cells to come up with my "trained" number and "required training" number, and then let Excel do the math ((RT/T)*100=%) to come up with a percentage for overall training complete. Is there a formula or other method that can look at the actual colors of the cells in a column to automatically calculate the percentages in a cell at the bottom of the column?

Thanks in advance for any help!


I need to create some conitional formats, but don't know how. I am used to excel 2007 edition (wasn't a pro by any means) and dont have a clue how to do things on 2010.

I have a spread sheat that tracks annual training and competency for the staff at my work. There is training that is due annually and some due every 2 years. What I am looking to have happen is that I want the cell to turn yellow 30 days before the due date and red when it is expired. Any idea how to set this up? What I will have is the date that the current training was done on.

I.E. Bob did his training on 04/25/2010 so on 03/25/2011 the cell would turn yellow and 04/26/2011 the cell would turn red unless I put in a new date.

Any help would be appreciated!

Is there a way to calculate a percentage based on these conditions
I need to figure a formula to calculate if Product A is sold and the cost is between 199-208, to pay the commission out at 7.5% of this cost.. so if it was sold for 205 then at 7.5% of this cost the amount that is payable as commission would be $15.38, if it was sold for 219 then at 11% of this cost is payable as commission would be $24.09.. etc

To make this even more complicated, I would like the formula to also determine if the customer status is Active (not cancelled) and the comm status is not Expired.

The old way to pay this out was just a flat rate based on the product, if they were Active and if they were not Expired then:


producta expired active 205
productb not-expired active 260
productd not-expired active 115

This is my list of of possible combinations.
If Product A
cost is 239 payable percentage is 15% of cost
cost is 229 payable percentage is 13% of cost
cost is 219 payable percentage is 11% of cost
cost is 209 payable percentage is 10% of cost
cost is 199 payable percentage is 7.5% of cost

If Product B
cost is 289 payable percentage is 15% of cost
cost is 279 payable percentage is 13% of cost
cost is 269 payable percentage is 11% of cost
cost is 259 payable percentage is 10% of cost
cost is 149 payable percentage is 7.5% of cost

If Product C
cost is 319 payable percentage is 15% of cost
cost is 309 payable percentage is 13% of cost
cost is 299 payable percentage is 11% of cost
cost is 289 payable percentage is 10% of cost
cost is 279 payable percentage is 7.5% of cost

If Product D
cost is 139 payable percentage is 15% of cost
cost is 129 payable percentage is 13% of cost
cost is 109 payable percentage is 11% of cost
cost is 99 payable percentage is 10% of cost

This is an example list of data

producta expired active 205
productb not-expired active 260
productd not-expired active 115

This is the formula I have been able to come up with, but it is for only 1 product type. It does work but I need to be able to put all 5 product types as possibilities.


Its been quite a while since i have used excel, so sorry if this is an extremely stupid question. Im in the military and am currently putting together a spreadsheet to keep track of other service members training requirements. basically what i am trying to do is this:

say cell A2 has the date 12-05-10 (day month year) how can i make it so based on the current date cell A2 will automatically change color (i.e. green for training that has been completed, yellow for training that will expire within 3 months, and red for training that is expired based on the current date.)

I appologize if this is a stupid question but ive been with the infantry for quite some time and have not worked with computers for several years.

thank you in advance

Thank goodness I found this! Please help

So here is my question.

I have a list of 25 employees (cells A1:A25) with an additional 5 cells reserved for future employees (A26:A30)

I have a group of 30 cells (B1:B30) that, when certain employee training criteria are met, will have the letter "x" typed into them.

Below these 30 cells is a "total" cell. What I would like for this cell to show is the total percentage of training completion of the employees listed. I would normally use =COUNTA(B1:B30)/the total number of cells, but I do not want the formula in the "total" cell to calculate the blank cells that may or may not exist in cells B1:B30.

Basically I want to be able to put an x in those cells and get a percentage of those cells containing an x without counting blank cells.


John x
Jeff x
Mike x


Can you help me?


Brian Aragon

I have been trying all day to get this to work. Basicly my spreedsheet has peoples names on the left and different training courses up top. I have to track when they are due for annual training. I want the cell to highlight yellow withing 10 days of the training expiring and red when they are expired. I wish the buttons just let me put in = < > signs but they dont so I need a code. Anyone help?

I am trying to create a formula that looks at a spreadsheet containing training percentages for employees. I need to look at these percentages and based on the date that the person started I need to look at certain rules. First it needs to look at what has happened more recently , moving to a new department or promotion. Next it needs to use that info to decide which rule to use. Ex. the person started 90 days ago then they are subject to rule A. The training percentage then needs to be looked at to see what rating they have. EX. percentage < 90% but > 75% gives a rating of 1. percentage than 50% gives rating of 2. percentage < than 50% rating of 3. Rule B would contain different criteria. My problem is that I tried nesting IF statements but I need more than 7. When I tried to do this in VBA I couldn't get it to work. Please help!!

Having an issue with my spreadsheet and i dont have a way to import my spreadsheet like i have seen others here but here is my delima. I have spreadsheet that has training dates for all my employees, than on another sheet i have a print-out sheet that shows the employees if they are good with traing that we can print out. Column AC7 is brought over from the training log, column AA7 is basically whited out as a reference to go ahead 1 year to tell AI7 if expired or not.


05/27/2009 05/27/2008 OK
02/18/2010 02/18/2009 OK
12/30/1900 01/00/1900 EXPIRED

AA7 Formula =IF(AC7="","",+AC7+365)

AC7 Formula =IF(ISNA(VLOOKUP($A$3,trainlog,35,FALSE)),"",VLOOKUP($A$3,trainlog,35,FALSE))

AI7 Formula =IF(AA7="","",IF(AA7>$AF$1,"OK","EXPIRED"))

The N/A is inputed into the Training Log sheet because some individuals dont require some of training, how could i make the values in AA7 and AAI just be blank?

I had asked earlier about my formula in AC7 and recieved 2 great answers but it messes with my dates and make AI7 say Expired when i need to have blanks.

If need be i could even email the file to you to look at.

I am trying to come up with a way to track employees training using a spreadsheet for each employee. On that spreadsheet I have all of the required training each person has for the year and their due date. What would be a good way, if any, for excel to show that a training is due? Perhaps a color change on the cell from green to yellow as it approaches 30 days then to red after the deadline has expired? Also I would need this converted to Julian calendar. Any thoughts or suggestions?

Thank you in advanced!


First time poster to this board so hello all.

I need a bit of help with an excel book I am working on.

I am using excel 2007.

In columns G, H and I i have headers of Yes, No and n/a below these I have blank cells that I will insert a value (lets say an X) the X would only ever go into one column not all three. THis would repeat for each row within these columns. The X would move between yes, no and n/a. I need a celll at the bottom of the sheet that would calculate a percentage out of 100% based on the X's. For example if I had ten rows and under the yes column there were 5 X's then in the percentage box it would say 50%. I need a percentage based on the number of yes's that are populated with an X.

Hope that makes sense.

Thanks for any help that is offered

I have Excel 2007. I track training for my unit. I have a column of 69 dates of which I input. I use a conditional formula in the adjacent column to show green (current), yellow(due within 30 days) and red (overdue). Using the first column or the conditional column, I would like to get a percentage of dates in the column that are current as of today. So everytime I log in I can retrieve a percentage at a quick glance. My first column is O2:O70 or the 69 assigned personnel. Some of the columns may not have a date as new people have taken the class yet. Any help or a start with this would be great.

Hi there, I hope someone can help me and can explain in really simple terms as I am not great with excel at all.

I have a spreadsheet that contains all of our staff members (around 50 going down the "a" column) and the mandatory training that they must complete (10 subjects going across the top). They are a couple of things that I want to do with this sheet but I have no idea how to do this.

1) I want the dates in which people's training expires to change colour to blue, those that is about to expire in a month to go green.

2) Copy the names and their training required onto another worksheet so I can keep a list of who I need to arrange training for. Rather than looking down each column and hand writing the names out.

I also want to know is it possible for the sheet to then email me people who's training has expired or is about too?

Fire safety First aid. Manual handling
John. 12/04/15. 23/08/15. 04/05/15
Frank. 14/05/15. 16/07/15. 08/08/15
Julie. 21/11/15. 11/02/15. 22/10/15
Sharon. 03/04/15. 07/06/15. 12/04/15

Many thanks in advance.

Please assist:

I'm pretty sure I need an "IF" formula in my process...just not sure how to structure it.

Here goes:

I have a training file that I'd like to quickly show a status of G, A, or R (color coded) depending on when course was taken.

G or Green = Good/Current, A or Amber = < 30 days before training expires, R or Red = Expired

I was able to create an "IF" for G & A, but not sure how to flag when training is within 30 days of expiration (e.g. Amber).

Thanks for your help.


I hope that someone can help as I am very much a beginner.

I have a training matrix that tells me when a staff's training is about to expire and it changes the colour of the cell dependant on if it has expired, has 30 days or 6 days. There are also blank columns were people do not have that training.

What I want is to create lists of each competency with the people that require training in that area so that I don't have to individually go down the column and hand write out the staff members name. How do I go about doing this??

I have 2 questions actually.

1. I know nothing about financial formulas. I need a formula for:
If I invest $1000.00 in a CD at 5.35% AYP, how much money would the CD's value be at the end of 12 months? How much would it make daily and monthly?

2. I have a column of dates which represent the day an employee attended training. I was trying to conditionally format the column so that the date would turn blue if it came within 30 days of this specific date a year from now to indicate that the time for re-training was close and red if it were past the date for re-training.
(Ex: 5/31/07......the date would turn red anywhere from 5/1/08 til the date and a different color if past that day - 5/31/08)


I'm trying to calculate percentages with Excel, but I want it to ignore any blank cells. For example, I have 5 cells for data entry-each of the cells will contain a number up to 60. Therefore, the cell that is calculating the percentage of the 5 cells is based on a total of 300. However, if one of the cells is left blank, I want the the total percentage to be based on 240; if two of the cells are left blank, I want the total percentage to be based on 180, etc. I also want an entered zero to be included in the calculation (not changing the total the percentage is based on.)

I've used Excel quite a bit, but mostly for non-technical purposes. Any help would be greatly appreciated!!

Hi all,

I'm new to the site and this question may have been answered in the past but I couldn't see it.

I'm in the middle of making a training matrix for work and I've already placed conditional formatting on the cells to highlight when a training is out of date but now I'm stuck. I'm trying to create a formula that will count the amount of data that is in date and then produce a percentage. I've tried a few things but now I'm just scratching my head!

I've attached a small sample for you to see where I'm up to.

Please help me!!!


The values I need to color are in column P

In a tab named Main there are three lines

One asks for OK percentage
One asks for Warning percentage
One asks for Too High percentage

How do I make it so

A) If the percentage is from OK to .01 below the Warning percentage column P is green
B) If the percentage is from Warning to .01 below the Too High percentage column P is yellow
C) If the percentage is Too High or above column P is Red
D) If the percentage is below OK column P is Grayed out

I have a large spreadsheet that has over 400 people on it and over 60 columns per person. Each of those columns has dates that each person has completed a certain portion of training. The first Colum has Either a 'Yes' or 'No' to indicate if they count towards our group or if they are just temporarily attached to us administratively and do not count towards our training numbers. I need to find the percentage of personel who count towards our training that have completed a certain task. Basically I need a formula that will only count if a cell has data in it if there is a 'Yes' in the colum to the left of it. Any formula I've used so far has included the 'No's in it's calculations, so some fields have been off by over 30%. Any help would be appreciated.

I am doing a color coded training chart
I have annual training and over 150 employees to track.
i would like to know how can i make dates that will be due soon (w/n 30 days) yellow and training that is current green.

IE - I completed training 4/13/2013 i will be current until 4/14/2014 so that should be green, however i did training 9/30/2012 and that i coming due within 30 days on 9/30/2013 this should be yellow

I have been able to accomplish the due/pastdue. Can someone help me, i have attached a sample. It includes what i have been able to accomplish?



Im calculating a percentage using two different cells (i.e. the percentage A1 represents of B1) however if there is no values in the cell, a #Div/0! appears. How can i prevent this from appearing?


I have a 2 part problem and admit to being an excel novice so I need simple step answers please.

The spreadsheet I have created has a column where the date individual staff members complete different training serials is entered. (Each row has a different staff member and different type of training listed).

Firstly, what I need excel to do for me is: in another column add an automatic expiry date for that training: however different types of training have different expiry dates ranging between 1 - 4 years. Is this possible to do and if so how?

Secondly, I need to change the colour of the expiry date cell to: green when it is within 3 months of the expiry date, yellow when it is within 1 month of the expiry and red once the training is expired.

Sorry, I know it's a lot to ask, but is it possible?


I have a spreadsheet that keeps track of staff training. in colum A is the date they completed the training. this training expires in 5 years time. i would like the cell to highlight red when the date has expired. and when i enter the new date in 5 yrs time the cell to return to no fill color.

Help please