Home Archives for Excel Training
August 6, 2008 1 Comment

Learn to filter and copy unique records in an Excel list

Frequently, you need to find and copy the unique records in an Excel data list. For example, you might be preparing to send out a direct mail piece and you want to clean you list first.

Having the unique records copied to another location might help you to spot glaring errors from faulty data entry.

Here are the steps to follow in this video lesson:

  1. Highlight the data that contains the records you wish to filter. This could be the entire data lis or just one column / field.
  2. Select Data, Filter, Advanced Filter. Be sure to select “Copy to another location and to check the “Unique records only” box.
  3. Click the starting cell where you want to place the copied “Unique records” and then click OK.
  4. You may wish to use the AutoFilter to select the data entry errors for correction.

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 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
Link to this post!
Filed Under: Excel Data Tables and Lists, Excel Training Tagged: , ,
August 6, 2008 1 Comment

Create Advanced Filters for your data list

In this Excel Video Lesson we will teach you to use Advanced Filtering of your data list.

Here are the steps to follow in this video lesson:

  1. Apart from your data table write your Criteria. Criteria must consist of at least 2 rows – a Header and at least 1 row of Criteria.
  2. With an active cell selected in the data table, click Data, Filter, Advanced Filter.
  3. Accept the default setting to Filter the Data in-place and also the range for the data list. Select the cells for your Criteria and click OK.
  4. The Advanced Filter applies the Criteria to show only those records that meet your Criteria.
  5. When you write your Criteria on 1 row, ALL of the criteria must be met – i.e. the “AND” option.
  6. When you write your Criteria on multiple rows, each criteria is met independently – i.e. the “OR” option.

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 available to purchase. I invite you to visit my online bookstore for more details.

Related Video Lessons

  • AutoFilter your list
  • Apply Custom Filters to your data list
Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn
Link to this post!
Filed Under: Data Filters and Queries, Excel Data Tables and Lists, Excel Training Tagged: , , , ,
August 5, 2008 3 Comments

Identify your Excel data as a list

Identify your data as a list in Excel so that you can get automatic subtotals and dynamically expand your list.


Here are the steps to follow in this video lesson:

  1. Select one cell in your data and then click Data, List, Create List. The keyboard shortcut is Ctrl+L.
  2. Verify that Excel has identified the range of cells for your list and click OK.
  3. Notice that Excel has drawn a blue border around your list; added drop-down filter menu arrows; and added a floating List Toolbar.
  4. Scroll down to see that there is an * in the 1st blank row beneath your last record in the list. You can add additional records here and Excel expands your list dynamically.
  5. You can also add a new column / field and your list expands to include it.
  6. The Toggle Total Rows button adds an AutoSum to the last column. You can change the Function that it uses by clicking the drop down arrow next to any cell in the Total row.
  7. You can have multiple “lists” in the same worksheet when you identify your data as a list.

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 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
Link to this post!
Filed Under: Excel Data Tables and Lists, Excel Training Tagged: , , ,
August 5, 2008 Leave a Comment

Apply a custom filter to a data list

In this video lesson you will learn how to apply a Custom AutoFilter to your Excel data list.

Here are the steps to follow in this video lesson:

  1. With one cell selected in your data list choose Data, Filter, AutoFilter.
  2. From the  drop-down menus of your selected field choose “Custom.”
  3. In the “Custom AutoFilter” dialog box choose the operator and type in your filters.
  4. Note the distiction between the AND and the OR options.
  5. You can then apply a Custom Filter to another field – i.e. filter another field within the existing Custom Filter you applied to the 1st field.
  6. To remove the Custom Filter, select “All” for each field that you filtered.

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 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
Link to this post!
Filed Under: Excel Data Tables and Lists, Excel Training Tagged: , ,
August 4, 2008 3 Comments

Learn how to create nested subtotals

Here are the steps to follow in this video lesson:

  1. Sort your data before creating your 1st Subtotal (Data, Subtotals, etc.)
  2. Create your 2nd (“nested”) Subtotal – remember to choose a different field for “At each change in:”
  3. Uncheck the “Replace current subtotals” box.
  4. Before you can “copy and paste” your 2nd level subtotal outlined view click – Edit, Go to, Special, Visible cells only” – or use the Keyboard Shortcut Alr+;

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 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
Link to this post!
Filed Under: Excel Data Tables and Lists, Excel Training Tagged: , ,
August 3, 2008 Leave a Comment

Most popular Excel Video Lessons in July 2008

Here is a list of the five most popular Excel Video Lessons viewed during the month of July, 2008:

  1. Learn to AutoFilter a data list
  2. Sort data using a Custom List
  3. How to calculate the percentage of discount
  4. Keyboard Shortcuts – Part 1
  5. Explore AutoFill Options

This new website is now one month old – er, YOUNG! I want to thank all of my friends and colleagues who provided valuable feedback to me as I launched this site.

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 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
Link to this post!
Filed Under: Excel Training, Most Viewed Excel Videos Tagged: ,
August 3, 2008 1 Comment

Format your Subtotal title lines

Here are the stepes to follow for this video lesson:

  1. Create your subtotals – remember to SORT your data first!
  2. Choose Subtotal Outline 2 – in most cases this shows the Subtotal Title Lines only.
  3. Select the data that you wish to format.
  4. Click Edit, Go to, Special, Visible Cells Only (Or the keyboard shortcut Alt+;) and apply your formatting.

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 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
Link to this post!
Filed Under: Excel Data Tables and Lists, Excel Training, Tips and Time Savers Tagged: , ,
August 2, 2008 3 Comments

Perform "What-if" analysis with a 2 input Data Table

Here are the steps to follow in this video lesson:

  1. Create a formula that references cells – e.g. =PMT(B4/12,B5,B3)
  2. To the right of this formula cell, enter a series of “substitute” values – e.g. 36, 48, 72 months.
  3. Directly below the formula cell, enter another series of “substitute” values – 4%, 4.5%, 5%, etc.
  4. Select the range of cells for the Data Table – starting with the formula cell.
  5. From the Excel Menu, choose Data, Table.
  6. For the “Row Input Cell,” click the cell in the original formula for which you want to “substitute” the row values in your Data Table.
  7. For the “Column Input Cell,” click the cell in the original formula for which you want to “substitute” the column values in your Data Table. Then click OK.

NEW! Download the Excel Workbook 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.

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn
Link to this post!
Filed Under: Excel Training, MS Excel Training, Tips and Time Savers Tagged: , , , ,
August 1, 2008 4 Comments

Goal Seek to optimize the results of the =PMT() function

Here are the steps to follow in this video lesson:

  1. Calculate your monthly payment on a loan using =PMT() function
  2. =PMT() requires three arguments: “Interest Rate” / # of payments per year, “# of payments” over the course of your loan, the “Amount Borrowed.”
  3. For Goal Seek: Select the cell that contains your formula and then choose Tools – Goal Seek.
  4. In the “To Value:” box type in your GOAL – e.g. The amount that you want your =PMT() to return.
  5. In the “By Changing Cell:” select a cell that is referenced in your =PMT() formula.

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

“The 50 Best Tips for Excel 2007” DVD 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
Link to this post!
Filed Under: Excel Functions, Excel Training, Tips and Time Savers Tagged: , , , , ,
July 31, 2008 1 Comment

How to reveal the formulas in all cells

Here are the steps to follow for this lesson:

  1. To reveal the formula in a single cell, double-click the cell or press the F2 key.
  2. To reveal all of the formulas in the worksheet use Ctrl + Tilde (Ctrl+˜) – This is a “toggle.”
  3. There are 4 ways to write a formula to show the discounted price:
    1. If the cell containing the discount % is a negative (-20%) number =B5+(B5*$D$2)
    2. If the cell containing the discount % is a positive (20%) number =B5-(B5*$G$2)
    3. If the cell containing the discount % is a negative (-20%) number =B5*(1+$D$2)
    4. If the cell containing the discount % is a positive (20%) number =B5*(1-$G$2)

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

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn
Link to this post!
Filed Under: Everyday Calculations in Excel, Excel Training, Tips and Time Savers Tagged: , , ,
« Previous Page
Next Page »