## Calculating Percentage Based On Cell Color? |

## Calculating Percentage Based On Cell Color? - Excel |
View Answers |

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.

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.

## Free Excel Help Forum

**- Ask any question about Excel and have it answered in no time.**

## Similar Excel Tutorials

Calculate Percent Change in Excel

How to calculate the percentage amount that a value has changed in Excel. This includes quickly calculating this v ...

How to calculate the percentage amount that a value has changed in Excel. This includes quickly calculating this v ...

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 ...

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 ...

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 ...

I have found a rather convoluted method to Calculate the Number of Years, Months and Days between to Dates in Excel ...

## Subscribe for Weekly Tutorials

### Helpful tutorials delivered to your email!

## 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

- 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

- 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

- 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

- 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

- This macro will highlight all of the cells in a worksheet which contain a formula. The first one listed will highlight

## Similar Topics

I have a spreedsheet that shows some percentages example( 9.98% and 15.87%) I am needing to find a way to hide the percentage sign so that they just show as 9.98 and 15.87. The problem I am having is that are been worked out as a percentage, so changing the format of the cell does not work.

Can anyone help?

Thanks

Chris

Can anyone help?

Thanks

Chris

Hey everyone,

I'm really hoping someone can help me with this...

I need to plot percentages over time in a line graph in excel. I don't want to have to do a percentage equation in the spreadsheet, I just want excel to take two sets of values and display the percentage in the chart. For example, I need B1 as a percentage of B2 for week 1, C1 as a percentage of C2 for week 2, etc....

Can someone please offer a suggestion for how to do this? I would really appreciate it.

Also, would it be possible to link data from other sheets in the workbook into one single chart?

I'm really hoping someone can help me with this...

I need to plot percentages over time in a line graph in excel. I don't want to have to do a percentage equation in the spreadsheet, I just want excel to take two sets of values and display the percentage in the chart. For example, I need B1 as a percentage of B2 for week 1, C1 as a percentage of C2 for week 2, etc....

Can someone please offer a suggestion for how to do this? I would really appreciate it.

Also, would it be possible to link data from other sheets in the workbook into one single chart?

Hello all,

New to the forums.

I'm in the process of creating a new report and I'm stuck.

My report consists of a set target in a field, a score and percentage of target.

e.g Target is 400

score is 450.

Percentage is 1.125 of target. (=score/target)

Now what I would like to do is create a formula that does not go above 100% , but instead work in reverse if you go over the target.

E.g. Target 400 .. Score was 450 ... percentage of target is 87.5%

Thanks in advanced.

New to the forums.

I'm in the process of creating a new report and I'm stuck.

My report consists of a set target in a field, a score and percentage of target.

e.g Target is 400

score is 450.

Percentage is 1.125 of target. (=score/target)

Now what I would like to do is create a formula that does not go above 100% , but instead work in reverse if you go over the target.

E.g. Target 400 .. Score was 450 ... percentage of target is 87.5%

Thanks in advanced.

Hi there

I am trying to calculate our On Time Delivery. I want this as a simple percentage of jobs. I have got this running in the following way:

Column M - Estimated Delivery dates

Column N - Actual Delivery dates

Column O - =IF(SUM(M2-N2)>0,1,0)

Then I have calculated On Time Delivery as: =SUM((SUM(O2:O252))/(COUNTA(N2:N252)))

This seems to work fine. My problem is, if we enter a date in Estimated without a corresponding Actual date, the formula for Column O fills out anyway and improves our On Time Delivery Percentage. How can I set this up so that the formula doesn't calculate if there is no data in Column N?

Any help much appreciated

Thanks,

James

I am trying to calculate our On Time Delivery. I want this as a simple percentage of jobs. I have got this running in the following way:

Column M - Estimated Delivery dates

Column N - Actual Delivery dates

Column O - =IF(SUM(M2-N2)>0,1,0)

Then I have calculated On Time Delivery as: =SUM((SUM(O2:O252))/(COUNTA(N2:N252)))

This seems to work fine. My problem is, if we enter a date in Estimated without a corresponding Actual date, the formula for Column O fills out anyway and improves our On Time Delivery Percentage. How can I set this up so that the formula doesn't calculate if there is no data in Column N?

Any help much appreciated

Thanks,

James

I am trying to correct a formula for a productivity worksheet in excel. The idea is that we would have the ability to input the number of hours worked each day and the number of direct hours (working with clients) per day. Then take the daily percentages and add them then devide them to determine the final productivity percentage for the week. Additionally not everyone works a full week so I would need it to only divide the percentages of the days that were completed. Any suggestions? or does anybody know a formula that would accomplish this already? I am good with basic excel and was able to identify a flaw in the current productivity sheet.

I have cells in a column, some colored yellow, some not. I am trying to use

the SUMIF function to sum only the cells in the range that are colored. Can

this be done? Can I enter something in the "criteria" part of the formula

that can do this?

=SUMIF(D3:D13,"criteria",D3:D13)

I also tried to use the CELL function's color feature, but I couldn't get it

to work right. I don't know how to get Excel to recognize if a cell is

colored in a formula.

=CELL("color",cell)

It might just be that I don't know what this means in Help:

"color" --> 1 if the cell is formatted in color for negative values;

otherwise returns 0 (zero).

Can anyone help?

the SUMIF function to sum only the cells in the range that are colored. Can

this be done? Can I enter something in the "criteria" part of the formula

that can do this?

=SUMIF(D3:D13,"criteria",D3:D13)

I also tried to use the CELL function's color feature, but I couldn't get it

to work right. I don't know how to get Excel to recognize if a cell is

colored in a formula.

=CELL("color",cell)

It might just be that I don't know what this means in Help:

"color" --> 1 if the cell is formatted in color for negative values;

otherwise returns 0 (zero).

Can anyone help?

I saw two threads in this forum that asked this question, with no good answer. I am posting this solution for anybody still struggling with this.

The question:

How can you prevent a cell's contents from overflowing into the next cell?

Of course, you can make the column wider or turn on text wrapping, but you might not want to. Each of those solutions can mess up the layout of your sheet. You may just want to truncate the value.

Some people have suggested putting a space in the next cell. This is unnecessary, a pain in the but, and will mess up any ISBLANK type formulas, among other things.

The solution:

Select the cells in question and turn on text wrapping (Format>Cells>Alignment>Wrap Text).

Now select the row(s) in question and manually set the row height, by right clicking the row number and selecting "Row Height". Check the height of an adjacent row for a good value.

Your cells will now not spill over either horizontally or vertically. They will simply truncate anything that doesn't fit.

Be careful now, because parts of your data may be hidden. This can cause its own set of problems if one or two digits are neatly hidden away. Think ahead if other people might be using this sheet, and not be expecting to have some data hidden.

Tested in Excel 2002

The question:

How can you prevent a cell's contents from overflowing into the next cell?

Of course, you can make the column wider or turn on text wrapping, but you might not want to. Each of those solutions can mess up the layout of your sheet. You may just want to truncate the value.

Some people have suggested putting a space in the next cell. This is unnecessary, a pain in the but, and will mess up any ISBLANK type formulas, among other things.

The solution:

Select the cells in question and turn on text wrapping (Format>Cells>Alignment>Wrap Text).

Now select the row(s) in question and manually set the row height, by right clicking the row number and selecting "Row Height". Check the height of an adjacent row for a good value.

Your cells will now not spill over either horizontally or vertically. They will simply truncate anything that doesn't fit.

Be careful now, because parts of your data may be hidden. This can cause its own set of problems if one or two digits are neatly hidden away. Think ahead if other people might be using this sheet, and not be expecting to have some data hidden.

Tested in Excel 2002

Is there a way to force Excel to stop calculating a formula?

Excel showing "Not Responding" and is grayed out. I've tried to use Task Manager to stop the application, but as soon as open up Excel it goes right back to opening the spreadsheets and starts calculating again. I have spreadsheets saved, so don't have to recover.

thanks...Dean

Excel showing "Not Responding" and is grayed out. I've tried to use Task Manager to stop the application, but as soon as open up Excel it goes right back to opening the spreadsheets and starts calculating again. I have spreadsheets saved, so don't have to recover.

thanks...Dean

How can I calculate a 3% anual rent increase over 15 years (3% over the last year's rent)?

Hi

I have a table with various percentages, and I would like a formula to have an average of these. Please see attached file to get more of an idea.

Thanks

G

I have a table with various percentages, and I would like a formula to have an average of these. Please see attached file to get more of an idea.

Thanks

G

Hello, what I would like to do is take a portion of a sheet..... So the cells I want to mirror are in the proposal sheet cells B32 x F51. And then mirror the exact duplicate copy to another sheet. The other sheet should have those same values at A20 X G39. What I am trying to do is have a sheet that has skus, prices, and so forth and then when I add,delete, merge, color, or do anything to the cell on the "proposal creation" sheet I want it to mirror only a specific cells to another sheet that is "salesman copy" that we will print to clients. So this way ont he proposal copy a salesman can insert rows or even change the color of the cells and then have it mirror exactly the same on the "salesman copy" which is what we will print for clients. Thank you.

How can I stretch a spreadsheet to fit the page? If I try to make it one page by one page, it changes nothing. If I change the percentage, it becomes two wide...I am just trying to change the height so that it can be read on an 8.5 x 11 piece of paper.

I have a spreadsheet with simple (addition/subtraction) formulas. The file is quite large and the formulas are too. All of a sudden, the formulas stopped working except when I double click in the cell containing the actual formula. For example, if I enter "2" in each cell, A2 and B2, cell B3 should reflect "4" because there is a formula in cell B3 which totals cells A2 and B2. Only by double clicking on cell B3 will the program actually calculate. I'm totally perplexed and so is my IT contact. Anyone?

Hi there,

I'm very new to Excel, and I'm having trouble figuring a few things out. Hopefully this will be very easy for you guys!

In Sheet 1, I have a column of cells that I would like to also appear in Sheet 2. If I add a new row to the column in Sheet 1, I would also like it to be updated automatically in Sheet 2. Currently, I can get it to show the contents of individual cells from Sheet 1 in Sheet 2 by using this formula in the formula bar for each cell in Sheet 2:

=Sheet1!A3 (or whichever cell it is)

That's fine, but I'd like to just have a formula that will reproduce the entire column (ie. without a fixed range, as new rows are going to be added to the column).

If anyone could point me in the right direction, I'd be very very grateful. Thanks.

I'm very new to Excel, and I'm having trouble figuring a few things out. Hopefully this will be very easy for you guys!

In Sheet 1, I have a column of cells that I would like to also appear in Sheet 2. If I add a new row to the column in Sheet 1, I would also like it to be updated automatically in Sheet 2. Currently, I can get it to show the contents of individual cells from Sheet 1 in Sheet 2 by using this formula in the formula bar for each cell in Sheet 2:

=Sheet1!A3 (or whichever cell it is)

That's fine, but I'd like to just have a formula that will reproduce the entire column (ie. without a fixed range, as new rows are going to be added to the column).

If anyone could point me in the right direction, I'd be very very grateful. Thanks.

This may be less of an Excel question than a general data display question, but I'll try here.

I have 3 variables that I'm charting. I have one on the primary axis and it's values are in the billions. I have one on the secondary axis and it's a percentage. I need to display a 3rd who's value is in the millions. If I put it on the primary axis, you cannot see modest changes.

Is there a tricky way to get all three on one graph? I know I could split on two graphs, so that's my backup plan.

Thanks,

Dave

I have 3 variables that I'm charting. I have one on the primary axis and it's values are in the billions. I have one on the secondary axis and it's a percentage. I need to display a 3rd who's value is in the millions. If I put it on the primary axis, you cannot see modest changes.

Is there a tricky way to get all three on one graph? I know I could split on two graphs, so that's my backup plan.

Thanks,

Dave

How do I get the colors to change automatically when I use a drop down list.

Per say here is what I am trying to do

If a certain "word" is used from the drop down list I want the background of

that cell to change its color to "green." And if later I change the "word" to

another from the drop down list, it will change it's color to a specified

color.

The drop down list that I use was created from cells that have the colors

already in the "word", but I do not know how to make the list show the colors

so it puts the word & color automatically in the drop down list to the cell

with the drop down (if that makes any sense).

If there is a way, please spell it out simple enough for me to understand,

as I don't know fully the capabilities of formulas or vba.

Per say here is what I am trying to do

If a certain "word" is used from the drop down list I want the background of

that cell to change its color to "green." And if later I change the "word" to

another from the drop down list, it will change it's color to a specified

color.

The drop down list that I use was created from cells that have the colors

already in the "word", but I do not know how to make the list show the colors

so it puts the word & color automatically in the drop down list to the cell

with the drop down (if that makes any sense).

If there is a way, please spell it out simple enough for me to understand,

as I don't know fully the capabilities of formulas or vba.

I'm using some basic code below in an on Workbook Open event to format cells with a value less then 2 and less than 1 with a particular color.

The code works, but it really slows my worksheet down when opening. Is there better way to write this? Thanks!

Code:

The code works, but it really slows my worksheet down when opening. Is there better way to write this? Thanks!

Code:

Dim myRange As Range Dim cell As Range Set myRange = Range("V6:V50000") For Each cell In myRange If cell.Value < 2 Then cell.Font.ColorIndex = 5 If cell.Value < 1 Then cell.Font.ColorIndex = 3 Next

I have a problem sometimes. I will click on a cell to add information.

The cell turns blue and then wherever I move, it highlights those to.

No matter where I go on the page. If I Alt-Tab and work in another

program on my computer, that excel page keeps highlighting wherever

I move even in those other programs (I know this sounds confusing).

When I return to excel thousands and thousands of cells are blue.

The biggest problem is that the highlighting won't turn off, no matter

what. I can't select anything from the tool bars, do any work on the sheet or close the program.

I can close it only with the task manager but when I open it again,

the cursor is still stuck in the highlighting mode and won't perform any

other functions.

Do you think this is a problem with my computer, the excel program? I have changed my mouse and this didn't help.

Is there some shortcut to turn off this highlight feature other than restarting

my computer. Which is the only current way I can get rid of it.

Thanks for any advice,

The cell turns blue and then wherever I move, it highlights those to.

No matter where I go on the page. If I Alt-Tab and work in another

program on my computer, that excel page keeps highlighting wherever

I move even in those other programs (I know this sounds confusing).

When I return to excel thousands and thousands of cells are blue.

The biggest problem is that the highlighting won't turn off, no matter

what. I can't select anything from the tool bars, do any work on the sheet or close the program.

I can close it only with the task manager but when I open it again,

the cursor is still stuck in the highlighting mode and won't perform any

other functions.

Do you think this is a problem with my computer, the excel program? I have changed my mouse and this didn't help.

Is there some shortcut to turn off this highlight feature other than restarting

my computer. Which is the only current way I can get rid of it.

Thanks for any advice,

i have a worksheet with formulas in 15,000 cells; i have set the calculation to maunal so that you have to press F9 to calculate the sheet. \

sometimes, it takes 45 seconds to calc the whole sheet, other times, it takes 5 minutes. how do i clear the cache, or what do i have to do so that it calcs 45 seconds each time.

i don't want the application to think, it would be better if those 15,000 cells can be returned in a fraction-of-a-second. any ideas/tips? thanks.

sometimes, it takes 45 seconds to calc the whole sheet, other times, it takes 5 minutes. how do i clear the cache, or what do i have to do so that it calcs 45 seconds each time.

i don't want the application to think, it would be better if those 15,000 cells can be returned in a fraction-of-a-second. any ideas/tips? thanks.

Hello,

I'm a bit of a newbie with Excel, but...

I have several cells with data that contain both letters and numbers (4H, 8V, 4FH, etc.) What I want to do is remove only the text characters from these cells, and add the remaining number values together among a series of cells with this data type.

If your answer involves using a macro or VB, please provide a link on how to use the formula. I've never used a macro or VB.

Thanks.

I'm a bit of a newbie with Excel, but...

I have several cells with data that contain both letters and numbers (4H, 8V, 4FH, etc.) What I want to do is remove only the text characters from these cells, and add the remaining number values together among a series of cells with this data type.

If your answer involves using a macro or VB, please provide a link on how to use the formula. I've never used a macro or VB.

Thanks.

Hey all,

How do you turn off the blinking border around a cell that you are copying while still having it as an active cell? I don't want to have to press "esc" after everytime I copy an item.

Thanks,

Jeremy

How do you turn off the blinking border around a cell that you are copying while still having it as an active cell? I don't want to have to press "esc" after everytime I copy an item.

Thanks,

Jeremy

Hi,

I have the following code to put data from a VBA userform into Excel

Dim Sh As Worksheet

Dim Rng As Range

Set Sh = ActiveSheet

Set Rng = Sh.Range("A65536").End(xlUp).Offset(1, 0)

With Rng

.Cells(1, 1) = Surname.Text

.Cells(1, 2) = forename.Text

.Cells(1, 3) = datein.Text

.Cells(1, 4) = origin.Text

.Cells(1, 5) = Addressee.Value

.Cells(1, 6) = usual.Value

.Cells(1, 7) = dateto.Text

.Cells(1, 8) = permission.Value

.Cells(1, 9) = dateseen.Text

.Cells(1, 10) = requestview.Value

.Cells(1, 11) = Invoice.Value

.Cells(1, 12) = notes.Text

.Cells(1, 13) = datecompleted.Text

.Cells(1, 14) = holdsend.Value

.Cells(1, 15) = fee.Text

.Cells(1, 16) = notes2.Text

.Cells(1, 17) = dateseen.Text

.Cells(1, 18) = invoicesent.Text

.Cells(1, 19) = Paid.Text

.Cells(1, 20) = Complete.Value

End With

What I want to do next is click on the surname on the speadsheet and call up the userform with the fields complete for that person. I would also like to be able to edit/update the form and update the information on the speadsheet accordingly.

Any help would be greatly appreciated!

Many thanks!

I have the following code to put data from a VBA userform into Excel

Dim Sh As Worksheet

Dim Rng As Range

Set Sh = ActiveSheet

Set Rng = Sh.Range("A65536").End(xlUp).Offset(1, 0)

With Rng

.Cells(1, 1) = Surname.Text

.Cells(1, 2) = forename.Text

.Cells(1, 3) = datein.Text

.Cells(1, 4) = origin.Text

.Cells(1, 5) = Addressee.Value

.Cells(1, 6) = usual.Value

.Cells(1, 7) = dateto.Text

.Cells(1, 8) = permission.Value

.Cells(1, 9) = dateseen.Text

.Cells(1, 10) = requestview.Value

.Cells(1, 11) = Invoice.Value

.Cells(1, 12) = notes.Text

.Cells(1, 13) = datecompleted.Text

.Cells(1, 14) = holdsend.Value

.Cells(1, 15) = fee.Text

.Cells(1, 16) = notes2.Text

.Cells(1, 17) = dateseen.Text

.Cells(1, 18) = invoicesent.Text

.Cells(1, 19) = Paid.Text

.Cells(1, 20) = Complete.Value

End With

What I want to do next is click on the surname on the speadsheet and call up the userform with the fields complete for that person. I would also like to be able to edit/update the form and update the information on the speadsheet accordingly.

Any help would be greatly appreciated!

Many thanks!

I am needing a formula that would cause a currency amount from a calculation to round up or down to the nearest 100.00 mark. The current formula is ie: =E10*F10 (e10 being an amount of money and f10 being a percentage fo it.) I am needing the resulting answer to round up or down to the nearest 100. So if the answer is below the 50 mark it would round down and above it would round up. If that is not possible then just rounding down would be acceptable.

How do i do this??

Thanks

How do i do this??

Thanks

Very new in this board. If I post this question in the wrong forum please bear with me.

There is about 20 staffs in our department and I have made a spreadsheet with 12 tabs for each month of the year Jan-Dec. If each staff have 15 days annual leave each year and how can I make a formular that can automatically calculate the balance of annual leave and entered in the tabs of the following months. For example if staff A used 5 days in Jan how can the balance of 10days be entered in the tabs from Feb-Dec. If he use another 5 days in July the remain balance of 5 days be automatically entered in the tab from Aug to Dec. Likewise if staff A took 2 days sick leave in Jan, and 2 days in Mar how can this sick days be automatically sum up in the months after. I am doing this manually at the moment and it is very time consuming and also very easy to make mistake.

Thanks and sorry for this long questions

There is about 20 staffs in our department and I have made a spreadsheet with 12 tabs for each month of the year Jan-Dec. If each staff have 15 days annual leave each year and how can I make a formular that can automatically calculate the balance of annual leave and entered in the tabs of the following months. For example if staff A used 5 days in Jan how can the balance of 10days be entered in the tabs from Feb-Dec. If he use another 5 days in July the remain balance of 5 days be automatically entered in the tab from Aug to Dec. Likewise if staff A took 2 days sick leave in Jan, and 2 days in Mar how can this sick days be automatically sum up in the months after. I am doing this manually at the moment and it is very time consuming and also very easy to make mistake.

Thanks and sorry for this long questions