Close Window   
Free Ebook
Got an Excel Question?
Ask it in our Excel forum!
Subscribe for Free Excel tips & more!

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

Thermometer Chart in Excel
How to create a thermometer chart in Excel. This is what we want: Steps to Create a Thermometer Chart in Excel Mak ...
Error bars in Charts in Excel
How to add, manage, and remove error bars in charts in Excel. Error bars allow you to show the potential variance ...
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 ...
Change Minutes from a Decimal to a Percentage of an Hour
Change minutes stored as a decimal into a fraction of an hour. For instance, if time is stored as 8.15, 8 hours and ...

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!

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?

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.

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'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!!!


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 workbook that contains 2 sheets. The first sheet is my results page and contains vlookup formulas the second sheet is where my data is stored.
On my first sheet I have 3 columns one is headed 'type' the next is headed 'complete' and the last one is headed 'refresher due?'.
The vlookup refers to my data sheet and populates my 2nd column which in turn triggers my 3rd column if applicable.

Under type I have various types of traing listed as text. I have used 'countblank to determine the number of training courses that have not been completed and used vlookup to populate a cell within sheet 1.

If the 2nd column is populated with a date and triggers a refresher date some times the refresher date has expired e.g. completed 01.03.06 refresher due 01.03.07. I have used conditional formatting to highlight cell, but is there anyway I could count the number of cells that are expired. I was thinking of creating additional columns in my 'data' sheet and referencing that way, but only way I think that would work would be to highlight the cells and maybe count them that way.

I also have training that is due to expire ( 3 months) for employees would like to count that too.

My 1st sheet 'results' has a list of legends indicating what the coloured formatting refers too.

Thank you.

Hi all... quick question with a probably long complicated answer.

So I am making a spread sheet that is going to track assets that are going into a game engine, we need like a broad overview of where we are at. I have a list up and I want to have the user click each cell to open a box that has RED or GREEN in it, Green yes and Red is no. Based on all of these, I want a percentage generated out of how many cells are marked Green.

I was using a spreadsheet awhile ago that had this set up, and it was nice. Can anyone help or steer me in the right direction? Thanks! Very much appreciated! :D


I am using Excel 2003, I am attempting to set up a data base in excel as follows:
-I have a spread sheet that lists all the colleague I work with
-This spread sheet is updated bi-weekly with new hires
-I want to use excel to enter and track the completion of training sessions
-I need this database spread sheet to be used by 6 different people working in 6 different locations
-I want them to be able to enter in when training sessions have been completed for each of the colleagues on the spread sheet
-I would like to use a "user form" (VBA) to enter the completion of each training session

eg. Joe Blow has completed First-Aid training on Jan.15/2009, I want to have the ability to enter in Joe's colleague number which will then bring up his information from the spread sheet (date hired,position,other training completed) as well as have a spot to enter in the training he has just completed.

I have used Excel for a few years but very new to VBA, I have done allot of searching and it looks like this is possible.Problem is I don't know how to write the code that will make it work.

I have a training spreadsheet for work. I want to, if possible, make this easier to track.

A1 on down are names of employees
B is grade
C- M are the training types

Now I have it set up under the training name ther date and I have all who are current on this training green. Anyone who is due training withing 6 months its yellow. Any training that is a year, or over due/not found are red.

What I would ultimately like to do is to have these color code automatically, if possible, instead of me having to look at every training type and date.

Is there any way to set up a formula using "Today's Date" and having it color code like the above said?

Hopefully its not confusing, if so, I'll screen shot it and show an example of what I have now.

Thanks in advance,


I'm trying to setup a traffic light system to show when employees need to have their training etc

Date of original training e.g. 01/05/08

Renewal period e.g 1 year or 2 years (I need a calculation here that will add one year onto the original training date

Booked on course date (01/09/09)
Traffic light = green/amber/green status

The traffic lights i want is
1. if someone is within the renewal period of their original training date then the cell is green, or
If due for training i.e. renewal period expired and no course booked then red and,
If due for training but date of course renewal has been booked then amber

All help greatly appreciated


Column A contains a list of departments within the company in alpha order. Column B contains the number of employees. Column C contains the number of employees who have completed training. Column D calculates the percentage. I want a formula to tell me which department has the highest percentage of employees who have completed their training.

i am creating an annual training databse. I would like to have it when training due date is within 3 months of last date training to flag in a yellow color. and when the last date trained is overdue i would like to flag red. when training date are outside the 3 month window they cell would flag green. most training is on 12 month bases, with a few 15, 24 months respectively.

Thanks in advance for the help