r/excel • u/BGKY_Sparky • 21h ago

solved How make a range of cells reference a single cell?

I want to make a range of cells (G30:G508) automatically reference the value of a single cell (B3).

Iā€™m working on a spreadsheet that displays energy consumption of a group of machines across an 8-hour shift. I have columns with the raw data from various machines, and a column with the totals. These are referenced in a graph that shows energy consumption as a fluctuating line with time as the x axis and kilowatts as the y axis. I also have a single cell (B3) that displays the average of all the values in the totals column. I would like to make a new column that shows the value in B3 across the entire range (G30:G508) so that the average energy use for the shift appears as a straight line across the graph.

I know I can do this by manually copying the data from B3 and pasting it to G30:G508. But I would like to make the new range automatically update if the value of B3 changes. If I put =B3 in the formula bar, it works for the first cell, but if I drag it down the rest of the range populates as =B4, =B5, etc which is not what I want. I essentially want every cell in the G30:G508 range to have the =B3 formula without having to type ā€œ=B3ā€ 478 times.

Can anybody help me with this?

2 Upvotes

75% Upvoted

8 comments sorted by

6

u/MayukhBhattacharya 1267 21h ago edited 21h ago

You need to select the range of cells G30:G508 and enter =$B$3 and then hit CTRL + ENTER together all at once.

Edit added a demo:

2

u/BGKY_Sparky 21h ago

Thank you! That solved it.

1

u/MayukhBhattacharya 1267 21h ago

Sounds Great. Hope you don't mind replying to my comment directly as Solution Verified! Thanks and have a great day ahead!

2

u/BGKY_Sparky 21h ago

Solution Verified!

1

u/reputatorbot 21h ago

You have awarded 1 point to MayukhBhattacharya.

I am a bot - please contact the mods with any questions

1

u/MayukhBhattacharya 1267 21h ago

Thank You Very Much!

1

u/diesSaturni 71 21h ago

like u/MayukhBhattacharya mentions, you lock the reference with the dollar sign in front of column and row. Hitting F4 while editing the formula will cycle you through all options; 'none', columnrow, column, row.

Alternatively, my preference, name the cell e.g. "AvgAll" , which makes formulas easier to read. Perhaps a bit over the top for a single reference, but handy when formulas get built from multiple variables.

2

u/mistertinker 3 21h ago

The search term to look up is 'cell absolute reference'

Basically a reference is 2 parts, the row and the column. If you put a $ in front of either, it becomes absolute and doesn't change when you drag formulas. So if you type =$b$3 then drag that down, it will stay $b$3.

The more user friendly way to get there is when you type = then click your cell (b3), press f4 to cycle through the reference options.