The Basics for Creating and Copying Formulas and Functions in Excel

Relative and Absolute Cell References

Relative and Absolute Cell References

Formulas and Functions are the “core elements” of Excel. It is vital that you have a solid grounding in understanding how formulas and functions work; especially when you need to copy and paste them into other cells.

Relative and Absolute Cell References

When you use Relative Cell References – the default setting in Excel – the Row numbers and Column letters adjust automatically when you copy and paste a formula.

There are, however, situations where you need to “freeze in place” part of an Excel Formula. For example, you need to “freeze” or use an Absolute Cell Reference to the cell with “Total Sales,” when creating and copying a formula to determine Product Sales as a Percentage of Total Sales.

Copying Excel Formulas

In this tutorial, I demonstrate two methods for copying and pasting formulas and functions:

  • Standard Practice is to select the cell with the formula and use the Ctrl + C Keyboard Shortcut to place the formula cell on the Excel clipboard. Then, after selecting the destination cell(s), use the Ctrl + V Keyboard Shortcut to paste the formula in the new location(s)
  • AutoFill Tool.If you are copying the formula cell into adjacent cells, use the AutoFill tool to do this quickly and accurately!

Tips that You May Not Know

In my experience, many Excel veterans are not familiar with these tips and tricks which I demonstrate in this tutorial:

  • The Ctrl + ~ (tilde) Keyboard Shortcut to “toggle” the Show Formulas view for the active Excel Worksheet.
  • The Alt + Enter Keyboard Shortcut to automatically use the =SUM() Function – for adjacent cells.
  • The F4 Key to automatically add Absolute Cell Reference when creating or editing a formula. For example, converts A1 to $A$1.

Learn More Excel Tips and Tricks

50 Best Tips for Excel 2007

50 Best Tips DVD-ROM

If you enjoy the tips and techniques that I demonstrate in this lesson, then you will really benefit from purchasing my best-selling DVD-ROM, “The 50 Best Tips for Excel 2007.” You can learn more about the resources that I offer by visiting my secure online shopping website – http://shop.thecompanyrocks.com

Watch Tutorial in High Definition

Follow this link to view this Excel tutorial in High Definition on my YouTube Channel – DannyRocksExcels

Play Video Now

Simplify Data Lookups in Excel

In this Excel Video Lesson I demonstrate a great tip that dramatically simplifies data lookups!

Yes, learning how to use the Lookup Functions (VLOOKUP, HLOOKUP, MATCH, INDEX) is important. But, there is an easier way to answer some simple questions about you data.

In this video, I show you how to use the combination of “Named Ranges” and the “Intersection Operator” – the SPACE – to return the value at the intersection of a specific column and specific row. This “tip” is guaranteed to save you time and simplify your data lookups!

Here are the steps to follow in this lesson:

  1. Create “Named Ranges” for your data set.
  2. Use the “Intersection Operator” – the SPACE – in your formula. e.g. =Region_2 Week_16
  3. It’s that simple!

NEW! Download the Excel file that I used in this video lesson:

simplify-data-lookups

Looking for the answer to your Excel Questions? Use my Index of Excel Topics to find the right video

News! My DVD, “The 50 Best Tips for Excel 2007” is now available to purchase. I invite you to visit my online bookstore for more details.

Related Video Lessons