Home Archives for Excel Data Tables and Lists
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 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: , ,
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

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, 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


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: , ,
« Previous Page