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

Most read articles in July 2008

Here are links to the articles that were read the most during the Month of July, 2008:

Communications / Body Language

 

Career

Thank you for visiting my blog!

I invite you to visit my new website offering “Tips and Time-savers for MS Excel”

 

A lifetime learning

“It’s what you learn after you know it all that counts.”

– John Wooden

I remember my mother telling me, “You have to go to college. You need to acquire the knowledge that will last you for your lifetime.”

That’s the way it used to be. When you left school, you stopped learning. You started to work. And you worked for the same company until you retired. And then you _____.

Not today.

Learning for a lifetime will not equip us to remain vital – and employed. Rather, we need to invest in ourselves – committing to a lifetime of learning. Well, at least a significant part of our lifetime!

But – here’s the key – learning doesn’t stop. We can decide to stop learning. But economic forces, a global economy and a rapidly changing world demand that we continue to learn new skills. To reinvent ourselves periodically. To adapt to change.

I have been devoting some time to developing my new website – filming short video lessons of MS Excel tipsand time savers. During development, I have asked several friends and colleagues to review the videos and to give me feedback. Many write back to say:

“Wow! I didn’t realize that you could do that. I’ve been wasting a lot of time doing it the wrong way.”

“If only I knew this sooner. I could have saved two hours preparing my last report.”

“I didn’t realize what I didn’t know.”

“I knew that I needed to improve my Excel skills, but I didn’t know where to begin.”

I gain a lot of satisfaction from my career as a trainer and speaker. I love to see someone in my class have an “A ha!” moment. I love to see the light bulb of learning flash on. I enjoy helping people to learn.

And… I enjoy learning. I try to live my lifetime learning – and growing.

How about you? What are your thoughts on the topic of continuing education and professional development?

Please share your comments with our readers. Add a few words in the comment box below. I no longer require you to be registered in order to add your 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:

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.

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

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.

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.

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

How to calculate the percentage of discount received

Here are the steps to follow in this lesson:

  1. To calculate the % of discount received: =”Savings”/”Original Price.”
  2. Excel follows an “Order of Precedence” when performing calculations: It performs multiplication and division before performing operations involving addition and subtraction.
  3. Enclose portions of your formula inside () in order to control the order of your calculations.
  4. To determine the “Original Price” when you know the “Sale Price” and the “% of Discount”: =”Sale Price”/ (1-“% of Discount”)

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.