Home Archives for Filter a List
September 5, 2011 Leave a Comment

How to Use Criteria in Excel Functions and Filters

Criteria for Advanced Filter

Criteria for Advanced Filter

When you apply a Filter to an Excel data set, you use Criteria. In a filter, only the records that can answer “TRUE” to the criteria that you set will appear. The records that answer “FALSE” are hidden while the filter remains in place.

Logical Functions

The IF(), AND() and OR() Functions belong to a group of Logical Functions. In their function arguments, they evaluate a “Logical Test” and can perform one action is the answer is “TRUE” and a different action if “FALSE.” 

  • The AND() Function – each of the Logical Tests must be met in order to return the answer “TRUE.”
  • The OR() Function – if any condition is met, the answer that is returned is “TRUE.”
  • I frequently “Nest” an AND() Function or an OR() Function inside an IF() Function to test multiple criteria.

Advanced Filters in Excel

While many Excel users are familiar with Filtering, in my experience, I find that many people get confused when it comes to using Advanced Filters. In this video tutorial, I try to eliminate this confusion. Watch and you will learn how to:

  • Write Criteria for Advanced Filters using “AND” criteria and “OR” Criteria.
  • Copy the “Filtered Records” to another location
  • Use the Keyboard Shortcut Ctrl + Shift + L to quickly “toggle” normal filter drop-down menus on or off.

 

Watch Tutorial in High Definition

Follow this link to watch this Excel Tutorial in High Defintion on my YouTube Channel – DannyRocksExcels

Link to this post!
Filed Under: Data Filters and Queries, Excel Functions, Excel Training, YouTube Videos by Danny Rocks Tagged: , , , , , , , , , ,
May 9, 2011 2 Comments

How to Convert a Pivot Table to a Standard List

Watch this video tutorial in High Definition on YouTube

A viewer aksed for my help. She has a boss and a few customers who:

  1. Do not know how to create or use Pivot Tables
  2. Have no desire to learn how to create and use Pivot Tables
Convert Pivot Table to Standard List

Convert Pivot Table to List

Nevertheless, in this example, she is being asked to create a summary report – by Year and by Quarter – from @ 5,000 daily transaction records. The fastest way to create this report is with a Pivot Table. Now, to meet the demands of her boss and clients, she needs to convert this Pivot Table into a standard list that can be Filtered and Subtotaled.

Summarize by Year and Quarter

To create this Pivot Table report:

  • Add the “Date” field to the grid and then select a single date.
  • Group the Date field by Month, Quarter and Year.
  • Add and arrange the Customer and Invoice fields to the Pivot Table Grid.

Convert Pivot Table to Standard List

  1. Remove both the Subtotals and Grand Totals from the Pivot Table Report.
  2. In Excel 2007 & 2010, ensure that you are using either the Outline or Tabular report layouts – NOT the new “default” Compact layout.
  3. Also in Excel 2007 & 2010, remove the Field Headers and the + / – Symbols.
  4. Now, select the entire Pivot talbe (use Ctrl + A) and copy it to the clipboard.
  5. Use Paste Special Values to paste the Pivot Table Results.

Filling the Blank Cells in the List

As you can see, whenever you have “nested rows” in a Pivot Table you get a nice, clean report. However, in order to Filter and Subtotal this new list, you will need to “fill in the blank cells.”

  1. Select all of the values and all of the blanks in Column A.
  2. Use Ctrl + G (or F5) to open the Go To Dialog Box and choose Special.
  3. From the Go To Special choices choose “Blanks.”
  4. With all of the Blank Cells selected use this formula: = ↑
  5. Do NOT press Enter. Rather, press Ctrl + Enter and all of the Blank Cells are now filled in with the Customer Names!
  6. Finally use Copy, Paste Special, Values to complete this process.

Watch This Video on My YouTube Channel – DannyRocksExcels

Since this video tutorial is a little longer than usual, I have decided to post it as a video – in High Definition – on my YouTube Channel. Click here to view it or copy and paste this link into your browser:

Link to YouTube Video:   http://youtu.be/IwReVDJ7BAI?hd=1

Download This Excel Workbook

Link to this post!
Filed Under: Excel Formulas, Excel Training, Pivot Tables, YouTube Videos by Danny Rocks Tagged: , , , , , , , , ,
December 22, 2010 Leave a Comment

I Picked Up a New Tip Today – Filtering Your Excel Data

Yesterday, I posted an Excel Video Lesson on how to Filter your data. I focused on taking advantage of the Date Filters, Text Filters, and Number Filters introduced in Excel 2007. Today, in my email inbox, I received a great tip from Chandoo – whose tag line is “Become Awesome in Excel.”

Chandoo’s Tip – which I did not know – is to Right Mouse Click on any cell in your Excel Data Set and choose Filter – Filter by Selected Cell’s Value. As Chandoo points out, this only works in Excel 2007 and higher versions. I already use this tip in Microsoft Acces. In fact it is one of the Video Tips that I share on my DVD-ROM, “The 50 Best Tips for Access 2007.” So why didn’t I know that this would work the same way in Excel 2007? Because, I needed someone to point this out to me. Thank you Chandoo!

For me, the take-away point is to continue to explore other resources to learn from other expert users. Each – e.g. Mr. Excel – Bill Jelen; Mr. Spreadsheet – John Walkenbach, etc.) – has their own perspective on Excel. Each presents a topic in a slightly different manner. Each day is an opportunity to expand my knowledge base.

I also picked up another tip from Chandoo’s post – use the Ctrl + Shift + L Keyboard Shortcut to automatically add the Filter Drop Down Buttons to your Field Header Labels. I will use this tip on a regular basis. Now, I do not have to pick up the mouse to go to the Data Tab on the Ribbon and select Filter.

If you are using Excel 2007 and higher, note that you can also use this tip (rightmouse click in a cell) to filter by the selected cell’s Icon, Font Color and Cell Background Color. Data Visualization is one of the really great improvements introduced in Excel 2007. Click here to go to my video lesson on Data Visualization.

Tip from Chandoo
Chandoo’s Tip

I recommend that you check out Chandoo’s Excel Blog by clicking on this link. You can click on the RSS Button – or go to http://feeds2.feedburner.com/PointyHairedDilbert directly to start receiving Chandoo’s Excel tips delivered automatically to your e-mail Inbox.

 
And, by the way, you can receive my new posts automatically by going to http://feeds.feedburner.com/DannyRocks which is my RSS feed on Feedburner.
 
I have now added Chandoo’s site to my Blogroll – so just click on the link whenever you visit The Company Rocks website.
 
Let me know if you have a special tip or a favorite website or blog that you want to share with our readers. Add your comment below.
Learn how to “Master Excel in Minutes – Not Months!”
Link to this post!
Filed Under: Data Filters and Queries, Excel Data Tables and Lists, Excel Training Tagged: , , , , , , ,
October 17, 2009 Leave a Comment

Use the =COUNTIF() Function to see how many badges have been claimed

A viewer wants to know how many badges have been claimed and how many have not yet been claimed. In Excel, use the =COUNTIF() Function to get the answer(s) to this question.

If you have ever struggled with using “Nested =IF() functions” you will welcome the =COUNTIF() Function – it is so much easier to use because the arguments for the function follow a logical progression.

My viewer also wanted to see the names of the guests who have claimed or who have not yet claimed their badges. To answer this request, I use a Data Filter – very easy to use and change.

Learn Excel quickly. Over 5 1/2 hours of Excel 2007 training on my DVD. Each lesson averages @ 6 minutes.

Shop for the DVDs in “The 50 Best Tips for…” series at my online bookstore.

Link to this post!
Filed Under: COUNTIF and SUMIF Functions, Excel Functions, Excel Training, MS Excel 2007 DVD, MS Excel Training, Video Response to Viewers Tagged: , , , ,