Home Archives for Absolute cell reference
October 25, 2010 Leave a Comment

How to Use the VLOOKUP Function in Excel

The VLOOKUP Function is one of the essential Excel functions that you need to understand in order to work with data tables, invoices and product listings. In response to numerous email requests, I created this video lesson to walk you through each step.

I share my best practices which include:

  • Use a “named cell range” for your “array.” The “array” is the table that contains the information that you are looking up.
  • Make sure that the “Leftmost” column in your array is sorted in ascending order and that this column contains the values that you are looking for.
  • Use “FALSE” as the optional fourth argument when you need to find an “exact” match in the Leftmost Column.

Work with Excel Workbook Used in This Lesson

Purchase DVD-ROM

You can learn more about essential Excel Functions on my DVD, “The 50 Best Tips for Excel 2007.” You can buy it now!

Learn how to “Master Excel in Minutes – Not Months!”

How to Use the VLOOKUP Function in Excel [ 5:36 ] Play Now | Play in Popup | Download (1006)
Link to this post!
Filed Under: Excel Functions, Excel Training, Interactive Webpages, Lookup Functions in Excel, Podcast Tips & Timesavers Tagged: , , , , , ,
September 13, 2010 Leave a Comment

When to Use Absolute, Relative & Mixed Cell References in Formulas

In my experience, I find that many Excel users get easily confused when it comes time to use an Absolute or a Mixed Cell Reference in a formula. Let me demonstrate how and when to use the proper cell references, so that you always get the correct result.

Here is what each type of cell reference looks like in a formula:

  • Relative Cell Reference – =A2*B2 when copied down one row becomes = A3*B3
  • Absolute Cell Reference – =A2*$B$2 when copied down one row becomes =A3*$B$2 (Notice the $B$2 Absolute cell reference in the formula)
  • Mixed Cell Reference – =$A2*B$3 when copied down one row becomes = $A3*B$3 and when copied one cell to the right becomes =$A*C$3 (Notice how one part of the cell reference is relative – it moves; and the other part of the reference is Absolute – it remains fixed in place)

Learn how to “Master Excel in Minutes – Not Months!”

How to Use Absolute, Relative & Mixed Cell References in Formulas [ 5:09 ] Play Now | Play in Popup | Download (788)
Link to this post!
Filed Under: Excel Basics, Excel Formulas, Excel Training, Podcast Tips & Timesavers Tagged: , , , , , ,
November 1, 2009 8 Comments

Build an Accounts Receivable Aging Report

Here is another request for help from a viewer. How to create a report that shows “the aging” of past due invoices (1 – 30 days past due, etc.).

Here are the Functions that I use in this report:

  • =IF()
  • =AND()
  • =WEEKDAY()

Looking for a specific Excel video? Take advantage of my Index of Excel Video Lessons to find the video that you want to watch.

Related Videos

You can watch this Excel Video Lesson on YouTube

Invitation to Visit My Online Shopping Site

Want to learn the best Tips for Excel? You can purchase my DVD, “The 50 Best Tips, Tricks & Techniques for Excel 2007

I use a secure shopping cart at http://shop.thecompanyrocks.com – I invite you to pay a visit today!

Click here to see a Listing of “The 50 Best Tips for Excel 2007”

Link to this post!
Filed Under: Excel Financial Tools, Excel Functions, Excel Training, Video Response to Viewers Tagged: , , , , ,
August 13, 2008 7 Comments

3 reasons to use named cells and ranges in Excel forumlas

I use named cells, ranges and constants in my Excel formulas and workbooks for 3 reasons:

  1. They are easier to explain (to others and to myself 6 months after I create a formula.)
  2. They are easier to adapt (to other locations in the workbook – absolute cell references not required!)
  3. They are easier to update (especially named constants in formulas when e.g. rates change.)

These are the steps to follow in this Excel training video:

  1. Select the cells that you want to name and also the labels in the left column and top row.
  2. Choose, Insert, Name, Create. Make sure that Left column and top row are selected.
  3. To verify that your names have been created, click the Name box drop down list of names. Select a name and Excel takes you directly to that cell or range.
  4. To substitute Name cells and ranges in existing formulas: Choose Insert, Name, Apply, OK
  5. When you are writing a new formulas and you want to use a name, use the F3 “Paste Name” shortcut combination to select the name you want in the formula and click OK.
  6. A “Named Constant” does not refer to a cell. Select Insert, Name, Define and then Name the Constant and in the “Refers to” part of the dialog box type in e.g. =0.0825 if you want your Named Constant to refer to a Sales Tax rate of 8.25%

Find the Excel Video Training 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.

Related Video Training Topics

Link to this post!
Filed Under: Excel Formulas, Excel Training, MS Excel Training, Tips and Time Savers Tagged: , ,
July 20, 2008 1 Comment

Name a cell and use it in a formula

Here are the steps to follow in this lesson:

  1. A “named cell” creates an Absolute cell reference.
  2. One way to name a cell is to select the cell and type the name you want in the “Name Box” (Do not use any spaces) and hit Enter
  3. To apply the Named Cell in an existing formula, highlight the part of the formula and select Insert, Name, Paste (the named cell) and click OK.
  4. Consider creating a “Named Constant” for numbers you use frequently in a formula e.g. Sales Tax or Inflation Rate.
  5. To create a Named Constant choose Insert, Name, Define and in the “Refers to” box type (for example) = 0.0715
  6. Follow the same procedure to use the Constant in your formula: Insert, Name, Paste (named Constant) and click OK.
  7. Edit the Named Constant if e.g. the Sales Tax Rate changes. All formulas that use the Named Constant (in this workbook) will update automatically!

Click Here to Find the Excel Training Video in My Index

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.

Link to this post!
Filed Under: Excel Functions, Excel Training, Tips and Time Savers Tagged: , , , ,
July 19, 2008 1 Comment

Use absolute cell reference in % of total formula

Here are the steps to follow in this lesson:

  1. Make the right side of your formula – the reference to the “Total” – an Absolute Cell Reference.
  2. Change =C2/G1 to =C2/$G$1 by using the F4 Shortcut key.
  3. To reveal all Formulas in a worksheet, use CTRL + ~ (This is a toggle.)

Find the video lesson that you want – Index to all Excel Topics

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.

Link to this post!
Filed Under: Excel Functions, Excel Training Tagged: , ,
« Previous Page