Home Archives for Video lessons for Excel
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: , ,
July 18, 2008 Leave a Comment

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.

Link to this post!
Filed Under: Excel Training, Tips and Time Savers Tagged: , , ,
July 16, 2008 4 Comments

Learn the powerful SUMIF Function

Here are the steps to follow in this lesson:

  1. Type =sumif( and then click the FUNCTION WIZARD to enter the ARGUMENTS.
  2. Enter the range of data that you wish to SUM as the 1st Argument.
  3. Identify the Criteria to evaluate e.g. “>500″ – (enclose the Argument inside ” “) as the 2nd Argument.
  4. The 3rd Argument is OPTIONAL. You can leave this empty if you want to SUM the same range of data from your 1st Argument.

NEW! Download the Practice File from this Lesson:

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

Link to this post!
Filed Under: Excel Functions, Excel Training Tagged: , , ,
July 15, 2008 2 Comments

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

Link to this post!
Filed Under: Excel Training, Tips and Time Savers Tagged: ,
July 13, 2008 6 Comments

Learn to AutoFilter a data list

In this lesson you will learn how to use Excel’s AutoFilter to reveal select data records that match your filter criteria.

Here are the steps to follow in this lesson:

1) Organize your list into Columns / Fields that contain a single type of data e.g. Territory

2) Apply a different format to identify the Top Row of your list as the Column Headers

3) Select one cell in the list then choose DATA – FILTER – AUTOFILTER

4) Use the “drop-down” arrow for a field to select your filter

5) The Top 10 Filter is a generic term. e.g. You can filter to Bottom 15items / percent

6) Custom filters allow you to see records that are <, <=, etc.

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 Video Lessons

Link to this post!
Filed Under: Excel Data Tables and Lists, Excel Training, Most Viewed Excel Videos Tagged: , , ,
July 9, 2008 1 Comment

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


Link to this post!
Filed Under: Excel Data Tables and Lists, Excel Training, Tips and Time Savers Tagged: , ,
July 8, 2008 Leave a Comment

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

Link to this post!
Filed Under: Excel Training, Tips and Time Savers Tagged: ,
July 7, 2008 3 Comments

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

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

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.

Link to this post!
Filed Under: Excel Training, Tips and Time Savers Tagged: , , ,
July 4, 2008 Leave a Comment

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.”

Link to this post!
Filed Under: Excel Training, Tips and Time Savers Tagged: , , , ,
« Previous Page
Next Page »