Home Archives for FIlter a List in Excel
April 9, 2012 Leave a Comment

How to Roll Up a Summary by Month to Filter an Excel Pivot Table

Filter Using a Roll Up by Month Summary

Filter with a Roll Up by Month Summary

In this Excel tutorial, I respond to a viewer request. He likes the new “Roll Up Summary by Month” feature for filtering a field in an Excel 2007 – 2010 Field. What he finds frustrating – there seems to be no natural way to accomplish this with an Excel Pivot Table.

Natural Language Date Filters in Excel

Before I solve my readers dilemma, I demonstrate how to take advantage of the new “Natural Language” Date Filters that were introduced in Excel 2007. Date Filters allow you to filter records from “Today,” “Last Week,” “Next Month,” etc. They are available for Excel Tables and Excel Pivot Tables. These “Natural Language” Date Filters are a major improvement in Excel!

Group a Field for Pivot Tables

To solve my viewers question, I “Grouped” the original Date Field in his Pivot Table to produce “virtual” fields for “Month,” and “Year.” Now, it is a simple step to filter the “virtual” Month Field to obtain a “roll up” filter for individual months in the Pivot Table. Just select a single cell in the Pivot Table Date Field and choose Group Field. Make your choices in the Grouping Dialog Box and you are “good to go!”

I also show you how to take advantage of the Expand and Collapse Field Commands in a Pivot Table.

In-Depth Video Tutorial for Excel Pivot Tables

At my secure, online shopping website, you can purchase my 90-minute Video tutorial for Excel Pivot Tables. Available for immediate downloading or on a DVD-ROM. Version specific editions for Excel 2003, 2007, and 2010.

Watch Video Tutorial in High Definition

Follow this link to watch this tutorial in High Definition Mode on my YouTube Channel – DannyRocksExcels

 

 

 

How to Create a Roll Up by Month Filter for an Excel Pivot Table [ 5:42 ] Play Now | Play in Popup | Download (1696)
Link to this post!
Filed Under: Excel Data Tables and Lists, Excel Training, Pivot Tables, Video Response to Viewers Tagged: , , , , , , , ,
June 2, 2011 1 Comment

Don’t Subtotal Excel Data, Use Subtotal Function Instead

Subtotal Function

Subtotal Function Numbers

I used to love creating Subtotaled Reports. They are useful. They are easy to create. But they are also “clunky.” In my opinion, there are too many steps to take when you wish to see a Subtotal for a different field or to use a different function in your Subtotals.

Let me introduce you to the Subtotal Function in Excel. Here are several ways to take advantage of this function:

  • You can place the Subtotal Function in any cell on your worksheet – it does not have to reside directly below your data field.
  • You can use the Subtotal Function in connection with Data Filters – to get the subtotal for the visible cells in a filter.
  • You can use any of the 11 functions available to the Subtotal Function (Sum, Average, Count, etc.)

Watch This Video in High Definition on YouTube

This file size for this video is a little bigger than usual. So, to watch it, click on this link to view it in High Definition Mode on YouTube.

Subtotal Function Part Two

I have decided to film a second video lesson on the topic of the Subtotal Function – Using Subtotal Function in Excel Tables and Lists. Click on this link to watch my second video on this topic.

Watch or Download My 24 minute Introduction to Pivot Tables Video Recording

I have started to posted a series of “extended length” video tutorials online at: http://thecompanyrocks.webex.com – Follow this link to get more information about viewing or downloading my “free” Introduction to Pivot Tables.”

Get my best-selling DVD-ROM, “The 50 Best Tips for Excel 2007” for only $29.97!

Link to this post!
Filed Under: Excel Data Tables and Lists, Excel Functions, Excel Training, Subtotals and Outlines in Excel, 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: , , , , , , ,
May 29, 2010 2 Comments

Five Benefits from Using Tables in Excel 2007

I really enjoy the major changes that Excel 2007 has incorporated into Tables. In this video I demonstrate Five Benefits when you convert your Excel data lists into Tables.

(Note: This is a re-post. I now make many of my videos available as Podcasts and I need to send these videos to the iTunes store.)

Click here if you would like to subscribe to my Podcast at the iTunes store.

Learn how to “Master Excel in Minutes – Not Months!”

Five Benefits from Using Tables in Excel 2007 Play Now | Play in Popup | Download (1181)
Link to this post!
Filed Under: Excel Data Tables and Lists, Excel Training, New in Excel 2007, Podcast Tips & Timesavers, Tips and Time Savers Tagged: , , , , ,