Join Dennis Taylor for an in-depth discussion in this video Copying column formulas instantly, part of Excel 2016: Advanced Formulas and Functions.
- If you're working with lists of data and you're writing formulas, it's not uncommon to essentially write one formula and then copy it down a column. In cell E2 is a formula. I'll double click this cell. Using a function called DATEDIF, we're tabulating the difference between two days. And as I record this in early December of 2015, today's date. Compare with the date here, is almost 14, but not quite 14 years away. So the DATEDIF function actually calculates years of service in the same way that we would calculate birthdays or anniversary dates, coming up with the answer 13.
And I want to do this for every cell down the column. Momentarily here, I'm going to double click the bottom edge of one of these cells, like this one here. Double click. To show you this data goes down to row 742. Double click the top edge, back up top. So we want to copy this formula down to row 742. Now, copying and pasting is not the way to go here. Because it's a much lengthier process than is necessary. We can drag from the lower right hand corner, and many times when you're writing formulas if you only have to drag down 10 or 20 rows, this is certainly fast enough.
But I'm going to double click. Certainly one of Excel's best short cuts. And the formula goes all the way down. Or does it? How do you know? We could scroll up and down to verify that, but it's much faster to press CTRL + . Now, if we knew ahead of time that 742 was the bottom, and let's say we did in this case. We're all set. If we're not, we'll scroll just a little bit beyond that and we see what's happening. When you press CTRL + . , the active cell just moves around the corners of a selected range. And when we do copy a formula by double clicking, it keeps the data highlighted.
So it's a great short cut. Unfortunately, we have no corresponding short cut to quickly copy formulas across a row. Here's on more example. I'm going to write a formula here in cell I2 that calculates a new salary. And everybody's going to get the same salary increase out of cell K2. =G2 times this amount here. We want this to be an absolute address so we'll either have typed in the dollar signs or simply press the function key F4 to make sure that that absolute address gets copied all the way down the column.
Plus and then cell G2. Now, in completing the formula, I would normally press enter, but if I do, the active cell will move downward. Nothing terribly wrong with that, but by pressing CTRL + Enter, the formula is complete and the active cell stays in place. As it does here. I'll double click. Formula gets copied down the column. Once again CTRL + . just to make sure. Yep, down to row 742. CTRL + ., right back up top. So every one of these formulas uses that reference to K1 because it's an absolute address.
And copying the formula down the column was painless. Extremely fast with that double clicking technique.
Author
Updated
3/1/2016Released
1/28/2016- Displaying and highlighting formulas
- Converting formulas to values
- Tabulating data from multiple sheets
- Understanding the hierarchy of operations in formulas
- Using absolute and relative references
- Creating and expanding nested IF statements
- Looking up information with VLOOKUP, MATCH, and INDEX
- Using the powerful COUNTIF family of functions
- Analyzing data with statistical functions
- Calculating dates and times
- Analyzing data with array formulas and functions
- Extracting data with text function
Skill Level Advanced
Duration
Views
Q: This course was updated on 03/01/2016. What changed?
A: We added one tutorial about the new formulas in Excel 2016.
Related Courses
-
Excel 2016 Essential Training
with Dennis Taylor8h 53m Beginner -
Excel 2016: Pivot Tables in Depth
with Curt Frye3h 42m Intermediate -
Excel Tips Weekly
with Dennis Taylor22h 40m Appropriate for all
-
Introduction
-
Welcome50s
-
-
1. Formula and Function Tips and Shortcuts
-
Using Auditing tools6m 15s
-
2. Formula and Function Tools
-
3. IF and Related Functions
-
IF logical tests4m 27s
-
-
4. Lookup and Reference Functions
-
Nesting lookup functions4m 12s
-
5. Power Functions
-
Using MAXIFS and MINIFS4m 14s
-
Preventing double counting4m 48s
-
6. Statistical Functions
-
7. Math Functions
-
8. Date and Time Functions
-
9. Array Formulas and Functions
-
10. Reference Functions
-
11. Text Functions
-
12. Information Functions
-
Conclusion
-
Next steps33s
-
- 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.
CancelTake 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.
Share this video
Embed this video
Video: Copying column formulas instantly