When you summarize data in an Excel worksheet, you often find that a formula that you use in one cell can also be used in another cell. In this video, learn how to copy worksheet formulas and have the cell references change, or not change, as you wish.
- [Instructor] When you summarize data in an Excel worksheet you'll often find that a formula you use in one cell can be used in another cell. In this movie I will show you how to copy worksheet formulas and have the cell references change, or not change, as you wish. My sample file is the copy Copy Formulas workbook and you can find it in the chapter five folder of the exercise files collection. In this workbook I have a single worksheet and on it I look at the growth rate for an investment. The growth rate in cell B1 is 4.75% per year, the original investment is 180,000.
And I have a calculation or formula for the year 2019 which multiplies the value in B2 by one plus the growth rate. If I want to find the value for 2020 I can copy the formula from cell B3 to B4. So with cell B3 selected, which it already is for me, I'll press Command + C to copy, click cell B4 and Command + V. And I get my increased value and then I'll press Exit to get rid of the Copy and Paste selection.
You can see that the formula changed. If I want to use the use the fill handle to copy the formula to multiple cells by dragging then I can select the cell that contains my formula, in this case B4, and then move my mouse pointer over the bottom right corner, you can see a small green square, that's the fill handle. I'll move my mouse pointer over it and when it changes into a black crosshair I can drag down. I'll go ahead and drag down two cells. So I have my values for 2021 and 2022.
When I release the left mouse pointer I get the values. If you have a set of labels, and you can see here in column A, I have labels going all the way down to cell A11, which is the year 2027. That means that I can double click the fill handle to extend the formula down. So if I move my mouse pointer over the fill handle, which is at the bottom right corner of cell B6 now, and double click, it extends all the way down.
That is a tremendous time saver. A final way to copy formulas is to simply select the formula on the formula bar and paste it into another cell. In cell D1 you'll notice that I have the label for the year 2022. In cell E1 I'd like to put the calculation for the year 2022, which is in cell B6. So I'll click cell B6 and then on the formula bar I will select the text in the formula, press Command + C, then press Escape to stop editing the cell, click cell E1 and press Command + V to paste and I get the value.
Because I pasted the text of the formula directly the references don't change. I discuss relative and absolute references elsewhere in the course. If I want to only paste the value, instead of the result of the formula, then here's how you do it. With cell E1 selected I'll press Delete, and then I'll go back to cell B6, select it, and press Command + C. Cell B6 is selected so I'll click cell E1, which is my paste target, then on the home tab of the ribbon I will hover my mouse pointer over the Paste button's down arrow, which is at the right edge, click it.
From the list of Paste special options that appear I'll go to the Paste Values section and click the first item which is Paste Values. And there you see I have my value of 216,714.83 but not the formula. You can see the full value, to it's full decimal precision, in the formula bar.
- Creating workbooks
- Manipulating cell data
- Sorting, filtering, and managing worksheets
- Using core functions and formulas
- Formatting worksheet elements
- Creating and managing conditional formats
- Working with charts
- Adding images and shapes
- Working with PivotTables
- Exporting workbooks
Skill Level Beginner
What you should know1m 11s
1. Getting Started with Excel
2. Managing Workbooks
3. Working with Worksheets, Cells, and Cell Data
4. Sorting, Filtering, and Managing Worksheets
5. Summarizing Data Using Formulas and Functions
6. Formatting Worksheet Elements
7. Working with Charts
8. Working with External Data and Objects
9. Exploring PivotTables
10. Reviewing and Sharing Spreadsheets
Further information1m 2s
- Mark as unwatched
- Mark all as unwatched
Are you sure you want to mark all the videos in this course as unwatched?
This will not affect your course history, your reports, or your certificates of completion for this course.Cancel
Take notes with your new membership!
Type in the entry box, then click Enter to save your note.
1:30Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.