EXCEL: AutoSum Short Cut

Leave a comment

Did you know that you DO NOT have to hit the AUTOSUM icon to add the numbers in the row or column.  Instead of clicking the autosum icon, just click ALT+= together.  That simple.

Also, I have seen some people who highlight the table with the headings and numbers and hit AUTOSUM and it will calculate the table for you.

First highlight the table.  I would click anywhere in the table then click CTRL+A to highlight the entire table! See below.

Then use the short cut, ALT+= to add the total row and total column.

Notice that it deselects the column headings and row labels and the totals are calculated for you.  This only works when you are adding numbers.

EXCEL: Difference between Relative and Absolute Addressing

5 Comments

The most important concept that you must master in Excel is Relative and Absolute Addressing.  This is the basic foundation of Excel.  Without understanding this, you will not work with excel with ease.

Formula Philosophy:

  • Excel is defaulted to Relative Addressing!
  • Relative Addressing is when you copy a formula across and it increments the letters in the formula.
  • Relative Addressing is when you copy a formula down and it increments the numbers in the formula.
  • The formula does change but it does not have to do what column or row you are sitting at.  It is irrelevant.
  • Excel’s job is to zoom into the formula based on how you are copying/filling (across or down), it will do exactly what is told to do.  Letters across and numbers down.
    For example: =Sum(F20:F40) Copying across will increment the letter “F” to “G”.
    For example: =Sum (B2:F2) Coping down will increment the row “2” to “3”.
  • Absolute Addressing is opposite of Relative Addressing.  You want to lock in the cell in a formula so that when you copy across or down the particular cell does not change.  It is absolutely locked.  Place the Insert beam in the formula before, in the middle or after the cell that needs to be locked.  Hit F4 key. This will place a $ signs next to the letter and row number.  For example , if you need the value in G2 and it must be used for all the rows, then you must lock it.  Excel does not tell you that it has to be locked.  For example, G2 is the cell I want to use, it should look like this: $G$2.
  • F4 has four different absolute set up.
    • Hitting F4 the 1st time Excel places both dollar signs – $G$2
    • Hitting F4 a 2nd times Excel places a dollar sign in front of the number – G$2
    • Hitting F4 a 3rd time Excel places a dollar sign in front of the letter – $G2
    • Hitting F4 a 4th time Excel keeps the original letter and number – G2
  • Why would I want to use Absolute?  The reason is that if you have a bonus of 15% in a cell and the salaries are going to be multiplied by 15%, then I will write a formula that uses the salaries times the bonus rate that is located in the cell.  I would not type the following formulas:
    • =B2*15%
    • =B3*15%
    • =B4*15% and so on.
      This does not work because if you decide to change the 15% to 20% then you have to retype the formula and copy it down. If the formula is very large, you may make other typing mistakes.
    • Remember, Excel works for you!

Here is an example of an Excel sheet with name, salary, bonus amount, and total salary.  15% is in G2

    • Place 15% percent in the cell G2.  Then type in C3:
    • =B2*G2 and while the insert beam is blinking, hit F4 the formula now become
      =B2*$G$2 hit Enter
    • Copy the formula and the rest is typed in for you leaving G2 intact.
    • =B3*$G$2
      =B4*$G$2

For your information, if you did not place the absolute in G2, when you copy down, Excel zooms into the formula and it is told to increment the row numbers.  Therefore, B3 becomes B4 which then becomes B5 and G2 will become G3 which then becomes G4.  The last 2 formulas will produce a zero values become something times nothing gives you ZERO!

Pressing Ctrl+` keys (` is located to the left of the number 1 key) shows you the formulas.  Pressing those two keys will return back to normal view. It is an On/Off switch.

Practice makes perfect and Absolute is used everywhere in Excel.  You can not hide from this concept!

**Click on the example to see it better.

EXCEL: Navigating Through XL

Leave a comment

Here is the link: http://sdrv.ms/NtrIzJ

EXCEL: Short Cuts

Leave a comment

Click on this link to see chart: http://sdrv.ms/NtrIzJ

WORD: Selecting Using Mouse or Using Keyboard

Leave a comment

Click here for the link: http://sdrv.ms/ODX9U7

WORD: More Short Cuts

1 Comment

WORD: Navigating Through the Document Using the Keyboard

Leave a comment

Older Entries