Column and row references do not change when you copy a relative cell address.

A relative cell address has the form ColRow (e.g., A1, Z25). An absolute cell address either has the form $ColRow (e.g., $A1, $Z25), Col$Row (e.g., A$1, Z$25), or $Col$Row (e.g., $A$1, $Z$25). The $ doesn't change the cell reference; it is just to help you build your spreadsheet model faster. That is, A1, $A1, A$1, and $A$1 all refer to the top left cell of the spreadsheet.

Excel attempts to help the user by automatically updating all cell formulas as they are moved or copied from one location to another. If the formula includes relative cell addresses, then all references are changed based on the relative distance between the source (i.e., copied from) and destination (i.e., copied to) locations.

For example, if user copies the formula AVERAGE(A1:A20) from cell C4 to cell D4 (i.e., to the right one column), then Excel will change the formula to AVERAGE(B1:B20). That is, the cell reference will change from A1:A20 to B1:B20 (i.e., to the right one column). If the user copies the same formula from C4 to C14 (i.e., down ten rows), then Excel will change it to AVERAGE(A11:A30).

Most of the time these automatic changes to a copied formula are exactly what the user wants. Sometimes, however, the user does not want to have the cell references changed. This is when absolute addresses should be used. The $ argument simply tells Excel not to change a column or row references if the formula is copied to another location. Cell references of the form $ColRow will not be changed if the cell is copied to the right or left, but will be changed if it is copied up or down. Cell references of the form Col$Row will not be changed if the cell is copied up or down, but will be changed if it is copied to the right or left. Cell references of the form $Row$Col will not be changed for any type of copying.

For example, if user copies the formula AVERAGE($A1:$A20) from cell C4 to cell D4 (i.e., to the right one column), then Excel will keep the formula as AVERAGE($A1:$A20). That is, the column labels will not change since they were preceded with a $. If the user copies the same formula from C4 to C14 (i.e., down ten rows), then Excel will change it to AVERAGE($A11:$A30) because the row numbers were not preceded with a $.

Continuing with this example, if user copies the formula AVERAGE(A$1:A$20) from cell C4 to cell D4 (i.e., to the right one column), then Excel will change the formula to AVERAGE(B$1:B$20). If the user copies the same formula from C4 to C14 (i.e., down ten rows), then Excel will leave the formula as AVERAGE(A$1:A$20).

If used properly, absolute addresses allow the user to type only one "generic" version of each formula, then simply copy it to all of the other cells that need it.

Now when i want to add to the table for the next year, cell D2 should be for 2019, and i try to move with the fill handle (the small cross) from C2 to D2, the date doesn't change, even though i didn't put a $ sign by the date, so what correction do i need to get this done?

pas says:
October 5, 2019 at 5:32 am

First you type the list in the following manner or you can paste it in this style by your origin table, because of computers works then when you give it (computer) perfect quary status.
code marks
301 90
40 92
41 85
30 35
30 35
41 65
301 22
48 25
85 99
30 85
41 92
301 45
65 45
41 36
55 85
40 77
301 76
37 85
55 70
30 55
After it select this table and press Ctrl + L (it helps you to convert it in table style format), then go to design menu (which would be appear after fomat apply by press Ctrl+L). There is in design menu a sub menu tool in which first tool is "Summarize with PivotTable", click it and by default it give you, your data result in next sheet and that sheet would be create by "Pivot Table". Now Press Ok. Step 1 - Drag "Code" into "Row Label". Step 2 - Drag "Marks" into "Values" (when you draged Marks into Value it would be convert into "sum of Marks") Step 3- Click on "Sum of Marks", then click on value field setting, now click on max (instead of "sum"). Here you are find your result that is following :-
30 85
37 85
40 92
41 92
48 25
55 85
65 45
85 99
301 90

I have conditional formatting relying on a date where the cell is merged because I had to add another row in order to have the sub contractors listed separately. For example, I have rows 2 and 3 for a task and columns A, B, C, D, E, F, G, H, I, J, K, L, M, N, O and P are all merged however once you get to column Q, R, S, T, U they are no longer merged to show the distinction between the 2 different sub contractors on the task. Column V is merged again. My problem is Column I which is the expiration date of the task is the condition to turn all the cells to the color requested. Because column I is merged the first subcontractor in Row 2, Column Q, R, S, T, U will turn the color but the second sub contractor in Row 3, Column Q, R, S, T and U will not turn the color requested. It will only happen if I don't merge the Column I which has the expiration date and I put the date in both cells (2 and 3). PLEASE HELP IVE BEEN WORKING ON THIS FOR 3 DAYS!!!!!!

ngha says:
May 6, 2015 at 8:39 am

Hi,
I have a question:
If I have a table as follows:

ROW1: Name Tasks Mon Tue Wed Thu Fri
Row2: A clean 4.5 3.5 8 6 9
Row3 B Clerk 9 4 5.5 7 0
Row4: C Sale 5.5 7 6 8 1
Row5: A Sale 3.5 0.5 0 1 0
Row6: B Clean 0 0 0.5 0 0

How do I use conditional format to set the color to the cells in each column Mon to Fri with the condition as follows?

if total hours of each person in a day <= 4 set cell color to orange to each day's column
If total hours of each person in a day 8 set color to green
In this example, The person A has 8 hours in Mon and Row2 and Row5 of the COl "Mon" will be set to "Blue". The person B has total 9 hours in Mon, therefore, row2 and row6 set to Green. The person C has total 5.5, row4 should set to yellow. The condition format should be used for all rows and columns from Mon to Fri

Charles says:
March 26, 2015 at 2:48 pm

Please answer this question (I have to learn VBA, but for now walk me through baby steps without coding).

I have a list in Column A with about 2500 different names. 1k are highlighted green, 500 red, 500 orange, the remaining blank.

I recieve a different list with some names that match my original column A list. I want to see if the name in the cell in column b, matches with column A, and if it is highlighted(I highlighted manually as I progressed, not using any conditional) I want the new column, column C to show that name but now highlighted.

Currently I use a VLOOKUP(B2,$A:$A,1,FALSE) and that returns the name of column B if it is in A however no color value is shown. I need to know which are not highlighted so I know which ones do not match or need to be "colored in"

Does relative Reference Change row and column?

In which cell referencing the row and column references will not change when you copy the formula to another cell?

Does a relative cell reference change when copied to another cell?

Which of the following cell addresses will change when it is copied?