Home Archives for AutoSum
February 2, 2012 Leave a Comment

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

Link to this post!
Filed Under: Excel Basics, Excel Formulas, Excel Functions, Excel Training, YouTube Videos by Danny Rocks Tagged: , , , , , , , , , , , , , , , ,
September 26, 2008 15 Comments

Excel’s QuickSum Calculator – Get Results Without Writing a Function

MS Excel 2003 has a great feature – The QuickSum Calculator – but you have to know where to find it. It is on the Status Bar – that area at the lower right of your screen. Simply select at least two data cells. They can be contiguous or non-contiguous. But remember where to look for the QuickSum Calculator  – it is on the Status Bar.

Want to change the function? It simple! Just right-click on the name of the Function. You can choose Average, Count, Count Numbers, Min or Max – Excel’s most common Functions.

This is a great way to perform an Ad-hoc Query. I recommend that you use this for spreadsheets with Subtotals. Watch this short video lesson to see how this works – and how you can save time!

Find the Excel Video Lesson that you want – Index of all Excel Topics

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

Link to this post!
Filed Under: Excel Functions, Excel Training, Tips and Time Savers Tagged: , , , , ,
September 23, 2008 2 Comments

The NASCAR Approach to Saving Time in MS Excel

This morning, while I was teaching a class to use MS Excel 2003, a young man in the class called out, “I get it!”

I replied, “That’s great, Ian. What did you get?”

“It’s NASCAR!”

“NASCAR?” I replied – somewhat puzzled.

He explained. “In NASCAR Racing, the pit crews save a second here and a quarter of a second there. That’s how they win the race.”

So, there you go – “It’s NASCAR!” Saving a few seconds here and a few seconds there is how you win the business race. Watch this video to see how to save time when creating multiple formulas.

Here are the steps to follow in this video:

  1. To AutoSum BOTH the Columns AND the Rows with one-click of the mouse:
  2. Select the range of cells containing the values that you want to total PLUS the blank cells immediately to the RIGHT and immediately BELOW.
  3. Click the AutoSum ICON. Amaze your friends.
  4. If you like Keyboard Shortcuts. Instead of clicking the AutoSum Icon, use Alt+=

Find the Excel Video Lesson that you want – Index to all Excel Topics

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

Link to this post!
Filed Under: Excel Training, Tips and Time Savers Tagged: , , , , , ,