Copying Formatting without reference

Question

Hey,&nbsp;I am not sure how to explain my issue but i attached pictures that show the problem that i am facing.So, i have some conditional formatting on some of my cells. I want to copy that formatting while every cell formatting affect the same cell it self. The issue is that some time the original cell is still the reference, and when I solved that the issue when I try to copy the formatting to all the other cells it the condition for each cell affects all the cells before it in line. So, my only solution is to copy the formatting and only pasting it to one cell, which is not convenient since I am trying to apply it to a table of more than 20000 cells. I hope that was clear.The attached pictures should show an example of what I meanThanks&nbsp;

mtarler · Answer

95saal&nbsp;glad it is working for you and sure I can explain it.&nbsp; I'm guessing you know some of much of it but since you didn't specify what part of the formula I will try to go over all of it but if there is a particular part let me know:=AND(ISNUMBER(F1),TODAY()&gt;=F1+3,ISODD(ROW(F1)))so it is checking if 3 conditions are all true (AND):ISNUMBER(F1) checks if the cell is a number (i.e. not text)TODAY()&gt;=F1+3&nbsp; checks if TODAY() is at least 3 days after the value in the cellISODD(ROW(F1))&nbsp; checks if the cell is in an odd numbered row&nbsp;Now the 'tricky' part is that it all uses the cell 'F1' in the formula.&nbsp; So in excel there are relative references and absolute references:&nbsp;&nbsp;So lets say in G1 we enter the formula =F1.&nbsp; Then I copy or drag G1 down to G2.&nbsp; Then in G2 it will have the formula =F2.&nbsp; If I copy it from G1 and G2 to H1 and H2 then the formulas in H1 &amp; H2 will be =G1 and =G2 respectively.&nbsp; (You might need to actually do this to see what I mean).&nbsp; So that is RELATIVE reference because in G1 we entered =F1 but what Excel sees is 'look at the cell 1 column to the left'.&nbsp; So when you copy or drag&nbsp;(using the little dot/plus in the bottom right of the cell when highlighted) or fill down/right then Excel will adjust the reference to always point to 'the cell 1 column to the left'.&nbsp; NOTE: if you cut and paste or literally drag the cell itself to a new location it will NOT change the reference (i.e. the formula will no longer be present in the previous cell).&nbsp; &nbsp;Now lets say in G1 we entered the formula =$F$1 instead.&nbsp; Then it doesn't matter what you do in terms of copying, dragging, filling etc... the new cell will always have the formula =$F$1.&nbsp; That is because this is and ABSOLUTE reference.Lastly you can also have hybrid like $F1 or F$1.&nbsp; So what ever has the $ in front of it is absolute and what doesn't is relative.&nbsp; So F$1 will always point at row 1 but the column can change.&nbsp; If you copy down it will always point at F$1; if you copy to the right then it will point to G$1, H$1, etc...&nbsp;So finally back to the original conditional formatting formula that all referenced 'F1'.&nbsp; The conditional formatting uses the upper left corner of the area it is applied to as the starting/reference point.&nbsp; Any relative references are based on that upper left corner cell.&nbsp; So after it finished with that 1st cell (in this case F1) it moves to the next cell and updates the relative reference accordingly.&nbsp; So by using the upper left cell in the formula it tell Excel to apply that formula to every cell in the 'applied to' range based on the value in its own cell.&nbsp;I do hope that explanation helps and please feel free to like my post(s) and mark as best response if so.Best of luck 🙂&nbsp;

ramiz_assaf · Answer

95saal&nbsp;It is really hard to follow, please explain better

mtarler · Answer

95saal&nbsp;as&nbsp;Ramiz_Assaf&nbsp; mentioned it is still hard to understand what you are saying.&nbsp; But from the images it appears you have conditional formatting specifically applied to A2 and other conditional formatting applied to A1:B2.&nbsp; So as you copy A1:B2 across the row you would expect the formatting applied to entire copy area to be applied to the entire paste area and the formatting applied to only the bottom left to get applied to every other cell just like you pasted 4 cells, moved over pasted another 4 cells and so on across the row.&nbsp; Just a thought... IF you wanted all the formatting to always be based on cell A2 after all the copy-paste then you need to use custom formula under the conditional formatting.&nbsp; If you are and the reference inside that custom formula is changing then you can use:a) =INDIRECT("A2")b) Define a name (click A2 and click Formulas-&gt;Define Name and call is something like CellA2.)&nbsp; Then in the conditional formatting you can use&nbsp;= CellA2&nbsp;But you were highlighting the RANGE that custom formatting was being applied to so that makes no sense to always refer to A2 (formatting that cell once is enough)&nbsp;

95saal · Answer

Thanks for your reply.I know its not clear as I am struggling on how to explain it.The reference for me is not the issue, cause i need every cell to be its own reference as shown in the picture. My main issue iwant to copy the formatting from the original cell to all the other cells while each cells formatting is only affecting it self. I don't know if thats clear but let me explain what i am trying to do.So, I am creating a tracker the top row to show how many pieces of product is repaired, and the row below it is the date for the latest update.The first condition i have is on the bottom row is that if the date has not changed in 3 days the color will change for example A1 is how many pieces and A2 is the date i used this fourmla( =A2-TODAY()&lt;=-3 )Also, the condition is if the too row has the text done in it to change the fill color to green, so even after 3 days it will stay green, the fourmla for this one is this:(=A1="DONE")As you can see these fourmlas both have to have the same cells that they are applied to be the reference.My issue is i have 20000 cell in this tracker i want to apply this formatting for each one individually but the only way i can find is by the format printer, but when i pull on all cells it applies the rule on all cells before starting with the top left cell.I attached a picture showing what happens when I use the format printer on all the cells at once and how it just applies to all the cells before it, and in the other picture if copy the formatting and paste on each cell individually and it works, but i can not do that with 20000 cells

mtarler · Answer

95saal&nbsp;maybe i still don't understand.&nbsp; did you try it and it doesn't give the expected results?&nbsp; conditional formatting can be a little hard to understand but the custom formula is applied as a relative reference based on the upper left most cell in the 'applied to' range.&nbsp; so if the formula says = A1="DONE" and the applied to range is A2:Z2 then every cell will look at the cell 1 row above it so Z2 will have conditional formatting applied if Z1="DONE" even though the formula says A1.&nbsp; Does that help?

Forum Discussion

Copying Formatting without reference

10 Replies

Resources