Very basic Excel question but need this tonight

November 7, 2022 12:46 PM

So this is a very very basic Excel question but I've never really used Excel and have no idea how to search for this. So column A is a list of names, and columns D to J are numerical grades that the people in column A have given to parts of a course. Now I want to calculate the average grade that each user has given to the course. I looked up how to calculate an average but only found how to do it cell by cell.

I know how to write =AVERAGE(D2:J2) to calculate the average course eval for person 2 but I cannot figure out how to automatically populate the remaining cells of column K, so that I don't have to write AVERAGE(Dn:Jn) n times.
Can you tell me how to do it and how to search for this in the future.

It's late over here and my brain is all frazzled already and I just want to do this and go to bed already. Help?

posted by M. to Computers & Internet (15 answers total)

Best answer: You should be able to grab the lower right corner of the active cell (K2) and pull it downward as many rows as you need; this should autopopulate each cell in column K with the correct results for each row.
posted by sencha at 12:51 PM on November 7, 2022 [3 favorites]

Best answer: Assuming K2 is the cell where your formula =AVERAGE(D2:J2) is located, hover your mouse pointer over the lower-right of K2 until it becomes a solid black + sign. Left-click and drag down the column, down to the last student's row.
posted by CheesesOfBrazil at 12:52 PM on November 7, 2022 [1 favorite]

Best answer: Here’s the explanation from Microsoft. (It says for Mac, but also works in Windows).
posted by scorbet at 12:52 PM on November 7, 2022

Best answer: Click on the cell where you typed the formula. It will be highlighted. In the bottom right corner of the highlight is a small square. Click on that box and hold while you drag down the column. This will automatically fill the formula and adjust it as it fills the column.

There are other ways to do this if you have many many rows to fill.
posted by jacquilynne at 12:53 PM on November 7, 2022

Best answer: If you hover over the cell K2 you should see a black cross in the bottom right hand corner. Pull that down
posted by koahiatamadl at 12:53 PM on November 7, 2022

Best answer: Excel Basics: How to calculate the average of rows and columns using the AVERAGE function - YouTube
posted by zengargoyle at 12:54 PM on November 7, 2022

Best answer: I would do it the following way but there maybe other quicker ways.
In the cell with the formula hover your mouse over the bottom right corner of the cell, click on that corner and then drag your mouse (while holding the button down) down the column to the bottom cell. Let go of the mouse button and the cells should fill with the formula.
You can check everything is correct by randomly clicking on a cell and checking the formula matches what you expect.
If this explanation doesn't help, I would suggest going to you tube and searching something like 'appling formula to multiple cells via dragging'
posted by daffodil at 12:55 PM on November 7, 2022

Response by poster: Thanks, y'all!

There are other ways to do this if you have many many rows to fill.

I have almost 800 rows :(
I'd very much like to know if there is an even easier way!
posted by M. at 12:55 PM on November 7, 2022

Best answer: For an easier way to manage this kind of task, select all of your data, create table (Ctrl + T), indicate whether it has headers at the top or not, then it becomes an "official" table. Then, whenever you add a formula in a column, it will automatically populate throughout the whole table. Putting data in a table also makes it easier to reference in other parts of your workbook, if you later need to do that.
posted by lookoutbelow at 12:59 PM on November 7, 2022 [4 favorites]

Best answer:

Simply do the following: Select the cell with the formula and the adjacent cells you want to fill. Click Home > Fill, and choose either Down, Right, Up, or Left. Keyboard shortcut: You can also press Ctrl+D to fill the formula down in a column, or Ctrl+R to fill the formula to the right in a row.

Fill a formula down into adjacent cells
posted by zengargoyle at 12:59 PM on November 7, 2022 [1 favorite]

Best answer: For easier explanations of Excel things, try searching YouTube for the task you want to do + beginner. There are lots of good resources.
posted by lookoutbelow at 1:00 PM on November 7, 2022

Best answer: Oh also - try pressing Ctrl + A once, when you have one cell selected, to select all of the data if it is all contiguous (no totally empty columns or rows).
posted by lookoutbelow at 1:02 PM on November 7, 2022

Response by poster: Ha. I dragged it all the way down and it worked! For some reason this nice little trick was not included in the formulas tutorial on Microsoft.

800 rows are not so many after all :)

Thank you for all your fast responses - I watched a few tutorials but couldn't not find this.
I'll re-read this thread tomorrow when my brain is less fried and maybe I can start getting the hang of this Excel thing.

Truly grateful for Metafilter!
posted by M. at 1:04 PM on November 7, 2022 [3 favorites]

Best answer: If you hover over the cell K2 you should see a black cross in the bottom right hand corner. Pull that down

You can autofill column K by double clicking that black cross. If you have no blanks in J, it should fill all the way to the bottom of your data.
posted by soelo at 1:15 PM on November 7, 2022 [5 favorites]

Best answer: You can also just copy the formula down the range! Select the cell with the formula, Ctrl+C, then select all the cells where you want the formula, and press Ctrl+V. It will automatically adjust the cell references to the right cells, i.e. in row 50 the formula will reference J50, in row 62 it will reference J52, etc.

Note that this would NOT work if you had dollar signs in your cell references to make them absolute rather than relative. If you copy a formula with a $A$2 cell reference, it will refer to cell A2 in all of the cells you copy it to.

However, long term you should definitely make your whole range into a structured table (Ctrl-T as described above), because then you don't even need to copy the formula down. Structured tables and table notation for formulas will change your life!!
posted by Cimrmanova at 1:56 AM on November 8, 2022