I'm trying to move my training data from a table to a more aesthetic view for my boss. Currently I'm using a formula in the pretty view to grab data for the first person, but I have to modify each person afterwards, because copying the first block of cells (A3:U10) results in getting data for the person ~7 rows down. Considering I have ~90 students to keep track of, this is less than desirable.
Table example:
1Name|Enrolled A|Score A|Enrolled B|Score B
2Name|Enrolled A|Score A|Enrolled B|Score B
...
7Name|Enrolled A|Score A|Enrolled B|Score B
Aesthetic example:
1NAME|Other|Other| | A | B |
Other| Other|Other| | Other | Other | Other | Other |
Other| Other|Other| |Enrolled A| Score A|Enrolled B| Score B|
7NAME|Other|Other| | A | B |
Other| Other|Other| | Other | Other | Other | Other |
Other| Other|Other| |Enrolled A| Score A|Enrolled B| Score B|
18NAME|Other|Other| | A | B |
Other | Other|Other| | Other | Other | Other | Other |
Other | Other|Other| |Enrolled A| Score A|Enrolled B| Score B|
If you want to copy just one cell, simply click once on that cell. IF you want to copy multiple cells, click and drag until all the cells you intend to copy are selected.
2. Copy the Cell or Cells
The next step is to actually copy the cells. You can do this by pressing control+C on a PC (command+C on a Mac), or by clicking the Copy button in the ribbon at the top of the Excel window. You’ll know you’ve copied a cell or cells when you see a spinning dotted line surrounding the copied area.
3. Choose Where You Want the Copied Cells to Go
Next, click once on the cell where you want the copied cells to go. If you’ve copied multiple cells, click the cell where you want the top left cell in your selection to go.
4. Paste the Copied Content into the New Location
Now that you’ve selected the cell where you want your copied content to appear, use the paste function to place the copied content in the new location. To do this, click on the Paste button in the ribbon (near the Copy button), or press control+V (command+V on a Mac). The material you had copied will now appear in the new location. If the copied material was in a cell that contained a formula, the formula will be copied, too.
Please note that if you’ve selected a location that already has content in it, the old content will be overwritten by the new content. Usually, that’s exactly what people are intending to do, but it’s important to note how this works.
Advanced Techniques
Now that you understand the basics, check out these two advanced techniques.
Paste Options
If your copy and pasting doesn’t work as planned, try this. Click on the clipboard next to the cell you just pasted into. Hover over the buttons and choose the one that sounds like what you’re trying to do.
Smart Fill
Select some data. See the green square at the bottom right? Try dragging that square down or sideways. With some data, Excel can intelligently populate either data or formulas to help you work more quickly.
Drag the fill handle down, up, or across the cells that you want to fill. In this example, the figure here shows dragging the fill handle down:
When you let go, the formula gets automatically filled to the other cells:
To change how you want to fill cells, click the Auto Fill Options button
For more information about copying formulas, see Copy and paste a formula to another cell or worksheet.
Tips:
You can also press Ctrl+D to fill the formula down in a column. First select the cell that has the formula you want to fill, then select the cells underneath it, and then press Ctrl+D.
You can also press Ctrl+R to fill the formula to the right in a row. First select the cell that has the formula you want to fill, then select the cells to the right of it, and then press Ctrl+R.
When filling doesn't work
Sometimes filling works fine, and other times it doesn't work as you expect. That's why it's important to check the cell references of the first cell.
Cell references are simply pointers to other cells. For example let's say the first cell has this formula:
=SUM(A1,B1)
A1 and B1 are relative references. This means simply that when you fill the formula down, the references will change incrementally from A1, B1 to A2, B2 and so on, like this:
=SUM(A1,B1)
=SUM(A2,B2)
=SUM(A3,B3)
Other times, you may not want the references to other cells to change. For example, let's say that you wanted the first reference, A1, to stay fixed and B1 to change as you drag the fill handle. If this is the case, you'd enter a dollar sign ($) in the first reference: =SUM($A$1,B1). A dollar sign forces Excel to keep pointing to A1 as you fill other cells. This is what it would look like:
=SUM($A$1,B1)
=SUM($A$1,B2)
=SUM($A$3,B3)
References with dollar signs ($) are called absolute references. When you fill down, the reference to A1 stays fixed but Excel changes the reference of B1, to B2 and B3.