I have some data referring to % that comes in the format of 1.1% for a positive number and (1.1)% for a negative number
How can i get the 1.1% to display green and the (1.1)% to display red?
I am currently using the following format to display numbers in my excel.
_(* #,###,###_);_(* (#,###,###);_(* "-"_);@
The brackets and underscores are used so that the positive and negative numbers align with overhanging brackets.
I want to modify the format such that it is able to display decimals where ever applicable.
For example
1,000 display as 1,000
0 display as a dash "-"
1.265 display as 1.265
-0.51 display as (0.51)
I tried changing it to:
_(* #,###,###.###_);_(* (#,###,###.###);_(* "-"_);@
However it added a "." to all positive and negative numbers regardless of whether there were decimals after it.
e.g.
10 displayed as 10.
-30 displayed as (30.)
In otherwords - I am trying to find the "general" format and modify it to include brackets for negative number, and also modify it so that the positive numbers aligning with the negative numbers with the ) over hanging.
If you have a range of cells with values in them (Q2:AZ2), you can use conditional formatting to identify the top x% of the group and it would essentially treat each cell in comparison to the group. I need to identify if a cell in the group is greater than 3*Stdev(Q2:AZ2). Do I have to create conditional formatting for each individual cell or is there a way to apply a function to the group that would recognize each cell the same way that the canned conditional formatting rules work?
View 1 Replies View RelatedI have many autoshapes in my excel file and I want to use the "VBA version of conditional formatting" one two of these autoshapes. I am saying VBA version because conditional formatting does not work on autoshapes (just found that out). The autoshapes on which I want the conditional formatting to work are "AutoShape 73" and "AutoShape 74" (from Autoshapes > Stars & Banners > Explosion1).
This is how I want the conditional formatting to work:
If the text in the autoshape is "A", fill the autoshape with RED color and bold + white arial 12 font
If the text in the autoshape is "B", fill the autoshape with BLUE color and white arial 12 font
If the text in the autoshape is "C", fill the autoshape with GREEN color and bold + black arial 12 font
I have a spreadsheet with Mobile phone numbers in the following format:
+44(0)77 7296 5210
The spreadsheet has 2500 of these phone numbers.
Is there a way to remove the brackets and the +44 to leave:
07772965210
I am using Microsoft Office for Mac 2011
So I would like to automate the following, as doing it by hand takes a lot of time. I have searched the whole internet and finished empty handed.
Start from cell E10
Select cell
Move 3 cells down
Select cell
Move 3 cells down
Select cell
Move 3 cells down
Select cell
Move 3 cells down
Select cell
Move 3 cells down
Select cell
Move 3 cells down
Select cell
Apply double entry conditional formatting
Move 3 cells down
Repeat above until cell E5000
I have an Excel 2007 Workbook which is refreshed by a Web Query. I have dynamic named range defined as "Manager" which I've confirmed is correctly identified. This range includes only one column and is formatted as text.
When I try to reference this range in my conditional formatting "refers to" formula, all cells are recognized as blank even though the range clearly contains many cells that are not. For example, there are 90 records containing the initials "PD". If I use the formula =Manager="PD" in my conditional formatting, nothing changes. If I change it to =Manager="" then ALL records are formatted, including those that are not blank.
Stranger still, if I enter the formula =COUNTIF(Manager,"PD") into and empty cell in my worksheet those 90 records are counted correctly. Which leads me to believe it isn't about the data. Conversely, =COUNTIF(Manager,"") returns the correct count of only cells that are, in fact, blank.
I've tried using the OFFSET formula defining my range in place of the name itself for my conditional format formula to no avail.
I'm designing a spreadsheet for our department which tracks a students progress throughout their time at the school. There are 4 levelsStatementSchool Action PlusSchool ActionMonitorIn year 7, they may come into our school at Monitor level, but when they get to year 8 they may have moved to Action Plus. I want to highlight that change on my spreadsheet with conditional formatting if possible so if they go up, the cell will turn red, if they move down the list the cell will turn green.
I've created two columns on an options sheet (which holds all my data validation lists) and assigned a number to each of the levels so i can conditionally format my columns using < and > but I don't know how to write the lookup formula
What I'm trying to achieve
ExcelHelp.jpg
My Options sheet
Excelhelp2.jpg
I found out about conditional formatting for five different letters or letter combinations, using VBA, using the code below.
I was wondering if this is also possible with letters or letter combinations, BV, RV, SV, CV, Z and numbers, somewhere between 0 and 9,5.
I have a sheet that has conditional formatting that looks at column D2 for a number and divides that by half. it then colors in a corresponding cell on a row in the sheet that matches that number. the problem i am having is with odd numbers like 23 it gives a result of 11.5 . all my cell numbers are all rounded to whole numbers. i need to round the 11.5 to 12 or 13 it does not matter which one... i just need it to round it up or down. I have tried everything i can think of but have had no luck. I attached my workbook so you can see how it works.
View 4 Replies View RelatedI have a number in column "E" T13245v1
I have a formula in column "D" =RIGHT(E1459, 1)
Which gives me "1" in column "D" (last number in string)
In column "P" I have a number which I want to apply conditional formatting to, to highlight if the numbers in "D" and "P" do not match.
What is happening is that the conditional format is seeing the formula not the number and therefore always applies the condition. How do I correct this.
suppose I have the numbers 1-25 in a column. I want to color the numbers 1,4,7,10,13,16,19,22,25 green, color 2,5,8,11,...25 orange and color 3,6,9,12...25 red. I can not set up the conditional formatting formulas correctly.
View 2 Replies View RelatedI have a spread sheet with 2000 fax numbers in the format:
(555) 123-1234
I am going through and reformatting them to read:
555123-1234 or 5551231234
Is there cell format code or some way to do this quickly rather than going through each cell and deleting the spaces and ()?
I tried to use the find and replace function to delete different numbers from different cells (all in one column) but the problem is that I want to delete different kind of numbers.
Let me give you an example:
Starting point:
Cell A1: Bookrunner: Barclays Capital(368.726m)
Cell A2: Bookrunner: DBS(368m)
Cell A3: Mandated Arranger: Commonwealth Bank of Australia (Singapore)(367.163m)
Cell A4: Mandated Arranger: DnB NOR ASA (Singapore)(367.163m)
I want to reach here:
Cell A1: Bookrunner: Barclays
Cell A2: Bookrunner: DBS
Cell A3: Mandated Arranger: Commonwealth Bank of Australia (Singapore)
Cell A4: Mandated Arranger: DnB NOR ASA (Singapore)
(I don’t want the information in “()” like “(Singapore)” to be deleted! Only the “()” which contains numbers)
I used the “find and replace” function to delete ever single number, but I think it’s a little unprofessional to do so!
I have column A with Different status like Open, closed, available etc. Column B have numbers. i am looking for a formula in condition formatting for column B.
If the cell value in column B is 2 or more, the same cell should be highlighted in Green.
If the cell value in column B is 1, the same cell should be highlighted in Yellow.
If the cell value in column B is 0, the same cell should be highlighted in Amber.
If the cell value in column B is less than 0, the same cell should be highlighted in Red.
If the cell value in column A is "Available", cell in column B should be highlighted in Amber.
I need the conditional formatting to make all numbers that are zero clear (i.e. no fill).
I need it to make all negative numbers to be red, however it doesn't seem to recognize "-1" as a number, and ends up highlight everything red when I say "highlight values < -1 red".
How would I do this?
formula I colored only if three numbers in a list.
attach file
formattazione.xlsx
I want to have Excel highlight one or two numbers/cells in one column based on the value in another cell. I am attempting to do this using Conditional Formatting.
For example, if X1 is BETWEEN A2 and A3, I want to highlight A2 AND A3. I have used various AND and IF functions to achieve this goal.
Also, if X1 is EQUAL to A2, I want A2 to be highlighted. This is easy to do with a simple formula.
But my problem is this: if X1 is equal to A2, I want ONLY A2 highlighted. If X1 is between A2 and A3, I want BOTH A2 and A3 highlighted. I cannot figure out how to achieve both. For example, when X1 = A3, A2 AND A3 are being highlighted, instead of just A3. I would prefer to do this without a macro, if possible. Can this be done?
Example formulas I have tried which easily achieve the highlighting of two cells when the value is between them:
Condition 1=IF(AND($I$35>I8,$I$35<I7),1," ")+IF($I$35<>I7,1," ")
Condition 2=IF(AND($I$35<I6,$I$35>I8),1," ")+IF($I$35<>I7,1," ")
It appears that the +IF, etc. functions are not doing anything.
This is more complicated by the fact that the value of, in the above example, I35 is based on a value from another sheet (I used a function to transfer a value from one sheet to I35 in the other sheet). As data are entered on Sheet1, the value in I35 in Sheet2 changes.
I have a table that has labels of departments. Then each of those departments will be tracked per week on a number. I am looking for a formula for conditional formatting that if cell C5 is less than B5 that cell C5 (with the smaller number) fills green. Then depending on the next weeks number it will have no fill or a green fill if the number has decreased in cell D5.
Is there anyway that I can do an array of cells or do I have to do it individually per column?
What would be the best way to apply conditional formatting where you actually want to apply a background and foreground colour to cells that have many different item numbers, ex:
6201 = Dark blue font, light blue background
6202 = Dark Green font, light green background
6230 = Dark Purple font, light purple background
6245 = ...
6445 = ...
6447 = ...
6448 = ...
and the list goes on for about 200 different colour combinations.
I know I can do this in conditional formatting using the ribbon, but I wonder if there was a faster way to do this
i wish to do some conditional formatting. my target for the first 6 months from date activated is 50,000 after 6 months is 100,000. and for those below target, i will need it to be highlighted in red. thus i have 2 sets of conditions.(calculate the no. of months and the revenue).
View 2 Replies View RelatedHow do I do "conditional formatting" to high light the numbers which bigger than average + 2 * stdev in a data group?
View 4 Replies View RelatedI have a small problem that I may be able to deal with in another way, but if all else fails then I need some assistance.
I have this formula:
=AND(N3<=$U$1,MOD($U$1,N3)=0)
But unfortunately there will be a time when cell U1 will have the contents of perhaps 12(1). Is there any way to get the formula to ignore the brackets and the figures in the brackets?
I m unable to apply conditional formatting with numbers ( font and fill of same color).
like:-
if press 1 , cell and font should be of same color and if 2 with different color and so on
as of now either formula is applying on cell or font but not on both
In Excel we can apply different formatting styles to the "TEXT" contents of a cell while we are in edit mode. For example we can apply "BOLD" to Brown Fox and "UNDERLINE" to lazy in a string "A quick brown fox jump over a lazy dog.
But when the above string is concatenated from the contents of various cells, excel is unable to apply different formatting (as exampled above) in the concatenated cell. Although we can apply bold, underline etc by selecting the concatenated cell but it is applied to whole string and not to the selected parts of string.
Sheet1 *A1Brown2Fox3Lazy4*5A quick Brown Fox jump over a Lazy DogSpreadsheet FormulasCellFormulaA5="A quick "&A1&" "&A2&" jump over a "&A3&" Dog"
Moreover, MS Word's "mail merge" is capable to apply different formatting to different "Fields" in a single paragraph.
Is there any way so we can apply multiple formatting to the contents of a single cell which is not "Text" but result of "concatenate" in Excel, like MS Word's mail merge or as it is applied in custom format within straight brackets like [Red] etc.
There is currently data on each sheet in my workbook. I am trying to get a macro to Copy the formatting of sheet "Statement" and paste the formatting on all other sheets in the workbook, except Sheet.actual
There is also a picture @ the top of sheet statement, that i would like copied and pasted to each sheet except sheet.actual.
Also, on all sheets except for STATEMENT & ACUTAL, i need the formula in cell C11 =vlookup(F14,cardnum,2,false) and in cell A11 = Name :
Excel 2007 Conditional Formatting. I am trying to apply conditional formatting to a wide range of cells. I only want the rule to adjust the color and shading, under the FONT tab in the section below. Specifically, I do NOT want to adjust the number format (I have the user select the scaling and use VB to populate the number formatting). I accomplish this by removing the number formatting by going into the conditional formatting rule, clicking on format, and then on the first tab (Number) -- on the bottom right there is button to Clear.
This resolves the issue perfectly, and I can operate the report exactly as I would expect.
Unfortunately, when closing the report (after a save), and then re-opening, the conditional formatting number format is no longer cleared. Worse yet, because it no longer is cleared, the scaling VB doesn't work, unless I go back into conditional formatting rules, and go to the number tab, and click clear.
Screen shot of after re-opening, no changes:
Report looks terrible (not real data):
A little background on what i am doing. I have a spreadsheet that tracks when i have blown the dust out of our computers. I have set up conditional formatting so that the text turns red after 300 days and the cell turns yellow after 600 days. However, some computers are in high dust environments. I would like the spreadsheet to tell me to blow the dust out of these sooner. see the attached sheet.
dust 1.xlsx
Is it possible to change the format of cell AI3 based on the format of cell C3 and D3? I have C3 and D3 set to turn red based on what is in cell C2 and D2. I would like the following done:
If AI3=C3 & C3 is red, format AI3 blue
If AI3=D3 & D3 is red, format AI3 blue
Otherwise, leave AI3 unformatted.
Possible???
Is there "code" for different formatting in a spreadsheet so you can use an IF statement to do something like:
if(A1=blue background,"Yes","No")
or
if(A1=red text,"Yes","No")
etc.?? But replace "blue background" and "red text" with some sort of number code? I want to compile a list of the items that are formatted with certain background colors and/or text colors and then organize only those items into a chart.
