I'm trying to insert a simple conditional formatting rule - icon sets according to rule percentages I've selected (Green > 90%; Yellow < 90% but > 80%; < 80% red). The conditional formatting does not work once i select okay. For example. The data set shows 95% as red, 83% as red, it's just not working for me. I've tried opening a new workbook, new worksheet and nothing seems to work. I've been able to do insert this rule based on "number" setting but unsuccessful with percentages.
Any way to import and use icon sets for conditional formatting other than the ones provided in excel 2007? I would like to have some circles and shapes in colors other than just yellow, green, red, and gray.
I have a column of cells with values - 0.2%, 0.32%, 0.22, 0.5 etc. The cells with % symbol are in ' Percentage, 2 decimal' format while the plain numbers are in 'General' format i.e. column contains cells in either of these formats. I need a macro where I can specify the column and it will select the cells with the % format, convert it to 'General' and multiply the result by 100 eg. 0.2% converted to 'General' becomes 0.002. When multiplied by 100, result is 0.2 i.e. is displayed without the % symbol.
I am trying to apply icon conditional formatting in a cell. The cell contains the following formula: =VLOOKUP(D20,'owssvr(1)'!O:W,9,FALSE The formula results in a "2", "1", "0" or "-1" in the cell. The icon conditional formatting is not working at all (no icon appears). I have the conditional formatting setup as numbers Green 2, Yellow 1,0 etc based on value. If I delete the formula and just type in any of those numbers directly, it works. I have changed my cells to "number" and it still does not work.
I have a workbook that I've built for a project. I've attached a sample workbook. What I'm trying to do, for the entire sheet or workbook if possible, is turn any Cell with a percentage of 30% to 49% yellow and any cell with a percentage of 50% or more Red. I would also like to move the ID's of the variable cells, for example Id number 9922, to the cells beside the description of the rows, Affected would be an example, if the information contained in the same row as the ID meets with a set of variables.
For example I only want the ID's moved if they correspond with IDsub 1-25. One more thing, the people who will be using this spreadsheet will be copying data from a website when it is imported it does not insert the values as numbers. I would like to format the cells, in example workbook they would be any of the cells labeled ID IDsub Variable or Number, so that anything put in that cell will automatically be converted to a number.
I should also probably add that the formatting will be done on approximately 80 rows a sheet with 47 sheets.
I need to send out an order form (spreadsheet) to 100's of people that need to complete the form and email back to me as an attachment. If I was completing the order form myself I would use the "email" icon that I have pinned to my Quick Access Toolbar (QAT). However, most of the recipients don't even know the Toolbar exists.
Is there a way I can insert an icon / hyperlink in the spreadsheet that does the same thing as the QAT icon. I can insert text to say "click here to email your order" (or similar).
I need to keep it in an excel format and an icon is so much better that asking them to save to their hard-drive and attach to an email, etc.
The QAT icon is exactly what is needed but I need to provide a spreadsheet that works for folk who haven't got the icon.
I have a list containing blocks of stock price values with each block representing a series of values at a given time e.g
1400 156 1400 99 1400 74 1400 86 1400 256
The number of entries may vary for each block but is never more than 60. the next block may be e.g.
14:05 15 14:05 42 etc
and down a very long list of around 65000 entries. I'm looking for a routine that will add up all the values in each block and calculate the total for that time block. I then need to fill in an adjacent column with the perecentage that each value is of the total. A completed block may look like;..............
Is there any way of formatting a cell to show a percentage with a fraction? For example, I'd like to show: 3 1/3%, 1 2/3%, 2 1/2%, etc. I'm showing data to a client who will pull out his calculator and add up the decimals on his calculator and be unhappy that the numbers don't add up to 100%.
I can force the output by using TEXT() or multiple columns, but ideally, I'd like a custom format if possible.
Using conditional formatting to make all the cells that contain numbers and are formatted to percentage format make display in red color? let's say I want to set this conditional formatting rule initially after just opening the excel book and afterwards when I type in values/text etc. into the cells I want the cells where I have input numbers and chose the percentage format to come out in red color? and all the rest stay in black color?
I would like to do the following: * Format numeric cells (cells with a percent sign) to zero decimal places. I have a set of macros that I am combining to clean up a data set and these are two of many (removing blank lines, duplicates, blue/bold, etc.)
Using conditional formatting i am trying to write a formula that changes colour on the percentage difference of another cell.
An example would be if cell A1 contained a target of 5% in cell A2 would be an actual. If cell A2 is equal to A1 or better then it would be green. If cell A2 was within 10% less of A1 (i.e 4.5%-4.99%) then amber and red for the target minus 10% (i.e <4.49%)
I want to do this as a conditional formatting formula as the target figure will change but 10% difference will remain the same.
I have a Sheet ( Named "Summary" for Example ) of about 4,000 Rows that has a LOT of Conditional Formatting.
I Added Another 100 Rows this Morning and when I Tried Saving it a Message Saying that Not All the Formatting for the New Data that had Been Added had Been Saved. Is there a Macro or Something I can Run that will Make the Conditionally Formatted Cells Stay the Colour that they are but Delete the Conditional Formatting Part of it Achieved Using "Format" & "Conditional Format" from the Menu Please.
Ideally I would like to be Able to Enter the Number of Rows ( From Row ? to Row ? ) that I want this to Apply to.
I currently have a thermometer chart in Excel 2013 that I set up with percentages along the Y-Axis. The thermometers are showing spending based on a budgeted number. Currently, the project is over-budget so I have adjusted the thermometer scale to go far beyond 100%.
I would like to have the percentages past 100% show in red along the lefthand side.
I need to have a conditional format that will adjust based on percentage ranges.
1% to 25% would be shaded red 26% to 50% would be shaded Orange 51% to 100% would be shaded Green
I was hoping to reference another cell and have the formatted cell reflect the conditional format. I dont think that's possible, so I can include a percentage in another cell and have that highlighted.
I have five fields that have a conditional format applied to them: (see attached). Columns J, K, L, N and O. These conditionals highlight if a minimum number is entered. Ex. Minimum pushups for a 30 y/o male is 27, if a 26 is entered it highlights red. The total score (where I need this to perform) is calculated in column Q. It will format red if the total score is below 75 but what I can't figure out is how to make it format if any of the previous minimums have not been met.
Ex. In cell L4, his crunches were 25 (a automatic failure -- red formatting). Now the total score is above 75 so it calculates as passing (green), but I need it to highlight red regardless because of the minimum not met in cell L4.
I have a range which has conditional formats based on other cells. I want to copy this range into another sheet and retain the current formats as fixed formats without copying the conditional formulae. ideally in VBA.
I am trying to get a row of cells to highlight a percentage based on a date range
Below is an example of what my spreadsheet will look like, very simple for managers to read and understand but I am stuck on how i can get this to display the right way.
In the example i would need the Jan column to colour for a certain percent for 21 days and continue to feb for 26 days. Im not sure if this makes sense but this is what they are asking for. Colour bars to simple show the percent of days off each month.
Name Start Date End Date Jan Feb Mar
Dale 11/01/14 26/02/14 21 days 26 days
I have attached the spreadsheet for an example : Book1.xlsx
I need to run a loop through a column of values (attachment col B) and when it finds a "J" it will apply conditional formatting to a row of 4 cells directly adjacent. The attachment is a theoretical before & after.
I have a tab called SAP DUMP where we copy our raw data to, from here we manually copy and paste into another sheet called ORDER TRACKING SHEET. When we do a fresh dump (overwrite) of raw data into the SAP DUMP tap we need to see if the there is a new entry which needs to be copied across to the ORDER TRACKING SHEET. I've got a vlookup to show this. The one I can't work out is that on the ORDER TRACKING SHEET I also need to see if there are any entries are now no longer on the SAP DUMP tab. This is where I thought a condition format might work, color the line red if it's no longer on the SAP DUMP tap. Can't make it work.....
I have a workbook with 12 worksheets corresponding to months. Each is formatted in A1 with the month (Date, Jan-01) and all rows below, starting at A5, are the days of the month (Custom, ddd d). All cells are linked so they change accordingly with A1.
I'm trying to apply conditional formatting so that all Fridays will have light grey shading across the row. I can get it to shade every 7th row using the MOD & ROW functions, but the shaded row changes with subsequent months and years (not necessarily Fridays). Can I get it to recognize every Friday through the year.