Results 1 to 5 of 5
  1. #1
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts

    Modify Pivot Table - Excel 2003

    I have an Excel 2003 worksheet for keeping track of checks received and deposited. There's a maximum of 100 rows of data x 10 columns. I add rows of data every time I receive a check, and I frequently sort the data on one or more of the columns.

    Two of the columns are Amount and Deposit Date. I have a small two-column Pivot Table that keeps track of the amount deposited on each date, regardless of how I sort the table. (The pivot table columns are Sum of Amount and Deposit Date.) When I make one or more deposits on a new date, I refresh the data in the pivot table, which expands vertically to accommodate it. It is very slick, and it does exactly what I want it to do.

    This is the first pivot table I've ever created, and I created it many months ago. I don't remember much about creating it.

    Now I have a problem: I've gotten more than 100 checks, and the pivot table only deals with the first 100.

    How do I expand the number of rows that the pivot table looks at?
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  2. #2
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Hello Lou -
    One feature of Excel that I like to use for data to be worked on (sort,filtered, pivot, etc) is a range named "Database". The name "Database" is unique and activates some nice features within Excel. That name was probably not used here, so lets move on.


    Be sure you have a backup of your workbook.

    Sounds like you need to change the range of the Pivot Table. There are a couple of ways to change the Range of your Pivot Table. Click somewhere within your Pivot Table and then....
    Data>Pivot Table>Back and make sure that the Range includes the rows that you want included in the new PivotTable. then Finish.

    Let us know if that helps.

  3. The Following User Says Thank You to tfspry For This Useful Post:

    Lou Sander (2011-02-21)

  4. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    See Debra's tip here about dynamic named ranges.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. The Following User Says Thank You to rory For This Useful Post:

    Lou Sander (2011-02-22)

  6. #4
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts
    I just used tfspry's hint about changing the range of the pivot table. It worked great! I've also noted the advice about using the named range DATABASE.

    I've got to say that changing the range of a pivot table isn't the most intuitive thing I've ever encountered with Excel :-). Thanks to the Lounge and tfspry for helping me with it.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  7. #5
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Lou - Thanks for posting back. Glad we could help you!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •