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.

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn

Write a formula to total cumulative sales

Here are the steps to follow for this lesson:

  1. CHALLENGE: Write 1 formula in Column “C” to allow the range of values in Column “B” to expand by 1 cell each time the formula is copied down in Column “C.”
  2. In cell C2, the formula is =SUM(B$2:B2)
  3. Notice the $ in the cell reference on the left side of the range = SUM(B$2…
  4. The $ creates a “Mixed Cell” reference in the formula. This allows the range of cells in our formula to expand when we copy the formula down in Column “C.”
  5. Learn how to clear the “Green Triangle” Smart Tags in a range of cells.

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.

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn

Produce Subtotals for your data

Here are the steps to follow in this lesson:

  1. Sort your data according to the field (column) by which you wish to group your records.
  2. From the DATA menu select SUBTOTALS.
  3. In the Subtotals dialog box confirm the correct field and select the FUNCTION you wish to use. There are 11 possible functions (including SUM, AVERAGE, MIN, MAX)
  4. Check to FIELDS that you want to be subtotaled and then click OK.
  5. Excel automatically OUTLINES your data. Click the Expand and Collapse buttons on the outline to see more or less detail. Click the Outline Levels to see different views of your subtotals.
  6. Explore the other options in the Subtotal dialog box (First choose Data – Subtotals.)
  7. You can remove ALL Subtotals in the Subtotal dialog box.

NEW! Download the Excel Practice file that I use in this video:

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.

Related Video Lessons

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn

Create a Custom List to AutoFill

Custom lists in Excel do two things very well:

  1. They speed-up your data entry
  2. They ensure accurate data entry

In this video lesson, I demonstrate how easy it is to create and employ Custom Lists in Excel.

Here are the Steps to Create a Custom List in Excel:

  1. Type the values for your list in either a column or a row
  2. Select the list of values and Spell Check them (F7)
  3. From the TOOLS Menu select OPTIONS and the CUSTOM LIST in the Dialog Box
  4. Since we already have selected the list of values click IMPORT

You can now use your custom list for data entry in ANY worksheet and ANY workbook on your computer.

NEW! Download the Excel practice file I use in this lesson: create-custom-lists

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.

Related Videos

Related Excel Videos


Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn

Explore AutoFill Options

Here are the steps to follow for today’s lesson:

1) Click the drop-down arrow for AutoFill options.

2) Experiment with the different options e.g. Fill Weekdays only; Fill Formatting only, etc.

3) To increment numbers: establish the increment by typing the values in 2 cells; select both of the cells and then AutoFill

4) If you have a Formula that is adjacent to a series of numbers or text, then Double-Click the AutoFill Handle and Excel will fill the formula into all cells.

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

NEW! I invite you to view a short video introduction to The Company Rocks Excels Blog

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn

Transpose your data

You can turn your Excel data on its side – literally! When you use the Transpose option from the Paste Special dialog box you can turn data from a Horizontal Orientation to Vertical one – or vice versa. It’s easy to do this as you will see in this video.

Here are the steps to follow for today’s tip:

1) Select the data area and copy it to the clipboard

2) Right-click a blank cell in an open area of your worksheet

3) Select “paste special” and check the “transpose” box and then click OK

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

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn

More to Auto Sum than meets the eye

Here are the tips covered in this video lesson:

  1. Take advantage of the Auto Sum drop down menus selections
  2. Group worksheets to speed up data calculations
  3. Learn to use Quick Sum to to see function results on your status bar

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

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

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn

Rename and group worksheets in Excel 2003

Here are the “tips and time-savers” in today’s lesson:

  1. Rename your worksheets – give them a more descriptive name
  2. Insert a new worksheet in your workbook
  3. Use “Tab Color” to better organize your worksheets
  4. Group your worksheets to improve efficiency & accuracy of data entry & formatting
  5. Use “AutoFill” to speed up data entry

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

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

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

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn