Need to create PivotTable by year

New Contributor

I have a huge worksheet with 972K of rows. I need to create:

 

  1. Pivottable with year(s) as columns (from 2008 to 2017). I have attached a screen shot of current setting
  2. In addition to # 1, I need the create the same report with filter (selection) of a 100+ names out of 1,000s.

I have Excel for Windows as well as Mac. Also, I had converted "date" fields using Data>Text to Column.

 

I will appreciate the help

4 Replies

@Rhyme1996 Your picture isn't very helpful, as it doesn't show the date field names. But, assuming that one of them is called "Date" and has real dates in it, drag it to the Column box and group by year (if not already done automatically). 

 

But since you mention that you are dealing with near to one million rows and have Excel for Windows at your disposal, consider connecting to the data via Power Query. Transform, clean and filter the data there and load it into the Data Model. Then, create your tables with Power Pivot.

 

 

I downloaded Kutools for Excel and it helped 13 workbooks into a master worksheet (total of 972 rows) in 1 minutes. It also has a feature for PivotTable Special Time Grouping (Year, Month, Day, time etc.) that I used to set up PivotTable by Year.

The only thing I need help is in "filtering" select names out of 2K names. I know how to use it in lists but still trying figure out how to do it in PivotTable

@Rhyme1996 Not sure. You say you have a pivot table containing thousands of names and you only want to filter out a few thousands. I assume you don't want to click on every name in the Pivot Table filter box. Not even sure how many names you can have there.

 

Would you have a list of the names to filter out? Then you could connect to the data with Power Query (a free tool in Excel for Windows) and create a sub-set on which you can then create a pivot table. But then again, I'm not sure what you are after.

Thanks. I am going to try Power Query. I have over 2K names and I have created one set of PivotTables with ALL names. I have to create another set of the same PivotTables, albeit with about 120 names out of 2K names.