+ Reply to Thread
Results 1 to 6 of 6

Repeating fields in pivot table source

  1. #1
    Registered User
    Join Date
    04-21-2021
    Location
    NRW
    MS-Off Ver
    Microsoft Excel 365 MSO (16.01.13901.20366) 64-Bit
    Posts
    4

    Repeating fields in pivot table source

    Hi there!

    I have a problem with my pivot table:

    Lets assume I have the following source for my pivot table:

    Month Type Value Description Category 1 Category 2
    January Variable expenses 1,00 Test description Some category So
    2,00 Another test description Some category Some other category
    3,00 Test description Some category Some other category
    Revenue 1000,00 Some revenue Rvenue cat Other cat
    500,00 Some other revenue Rvenue cat Other cat
    February Variable expenses 4,00 Test description Some category So
    5,00 Another test description Some category Some other category
    6,00 Test description Some category Some other category
    [...]
    and so on. If I now try to make a pivot table out of this, excel just evaluate the first row for January, February and so on but does not automatically recognize, that all the other rows (up to February) are also part of January. Its just shown as "empty". The same thing for my "types". Yes - I could now just copy the value of January down till it reaches February and the same for types too but that would make the source extremely confusing and bad formatted. Would look like:

    Month Type Value Description Category 1 Category 2
    January Variable expenses 1,00 Test description Some category So
    January Variable expenses 2,00 Another test description Some category Some other category
    January Variable expenses 3,00 Test description Some category Some other category
    January Revenue 1000,00 Some revenue Rvenue cat Other cat
    January Revenue 500,00 Some other revenue Rvenue cat Other cat
    February Variable expenses 4,00 Test description Some category So
    February Variable expenses 5,00 Another test description Some category Some other category
    February Variable expenses 6,00 Test description Some category Some other category

    That would work but (as you can see) is not really a solution if you want to keep the data clear.

    Is there an option in pivot tables to say excel: please reuse the value you found if there is an empty cell or somethin like this?

    Thanks for the answers!
    Last edited by mrCy; 04-21-2021 at 08:01 AM.

  2. #2
    Registered User
    Join Date
    04-21-2021
    Location
    NRW
    MS-Off Ver
    Microsoft Excel 365 MSO (16.01.13901.20366) 64-Bit
    Posts
    4

    Re: Repeating fields in pivot table source

    Okay, I just found here in the forum a link (excelforum.com/excel-general/573106-repeating-names-in-row-fields-of-a-pivot-table.html)
    in a solved thread to:

    contextures.com/xldataentry02.html

    Seriously?! Is excel that "dumb" that it cant manage a table with those empty cells?
    I have parallel libre office installed because I want to check the pivot tables there and it worked out of the box with the table I posted above
    (with the empty cells) I really just thought excel does it another way and you have to click somewhere through the options to enable this
    feature but the link says, you HAVE TO fill out the empty cells? Thats like a joke...I really want someone to prove me
    (and the poster in the solved thread) wrong


    (Sry that I could not post the links as clickable links. I'm not allowed to at the moment)

  3. #3
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,886

    Re: Repeating fields in pivot table source

    Short answer: no. Your data source is not well set up for pivot tables - you need the labels repeated for each row. If you had included your Office version in your profile, we might be able to suggest options relevant to what you have.
    Rory

  4. #4
    Registered User
    Join Date
    04-21-2021
    Location
    NRW
    MS-Off Ver
    Microsoft Excel 365 MSO (16.01.13901.20366) 64-Bit
    Posts
    4

    Re: Repeating fields in pivot table source

    Quote Originally Posted by rorya View Post
    Short answer: no. Your data source is not well set up for pivot tables - you need the labels repeated for each row. If you had included your Office version in your profile, we might be able to suggest options relevant to what you have.
    Thats a point. Sry! It is Microsoft Excel 365 MSO (16.01.13901.20366) 64.Bit

  5. #5
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,886

    Re: Repeating fields in pivot table source

    In that case, you could leave the source data as it is and use Get and Transform (formerly Power Query) to load the data - either into the data model, or just as a connection - and use its FillDown options to fill in the blanks in your columns before connecting to a pivot table.

  6. #6
    Registered User
    Join Date
    04-21-2021
    Location
    NRW
    MS-Off Ver
    Microsoft Excel 365 MSO (16.01.13901.20366) 64-Bit
    Posts
    4

    Re: Repeating fields in pivot table source

    I just have a look at this get and transform thing and it worked as expected but it robs my automation in the whole document if I understand it correctly. At the end it is a finance planner and I just want to duplicate tables and change the appropriate year and then the rest should be calculated automatically.

    Those get and transform "querys" are somethin which I than must create everytime the year and therefore the tables/sources changed.

    But yeah it is kind of a solution, thank you!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Source columns not showing in Pivot fields
    By lilybickel in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 05-17-2022, 09:40 AM
  2. Hide fields(table columns) from Pivot table Fields
    By alexholly in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 04-28-2017, 08:24 PM
  3. Replies: 0
    Last Post: 01-06-2016, 07:00 AM
  4. Replies: 3
    Last Post: 03-28-2014, 01:36 PM
  5. How to Disable automatic fields in pivot table like total and count on fields
    By anushka in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 09-24-2009, 07:53 AM
  6. repeating names in row fields of a pivot table
    By Jack N in forum Excel General
    Replies: 1
    Last Post: 08-18-2006, 11:40 AM
  7. [SOLVED] How do pivot table source fields automatically create tabs
    By Datamonkey in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 04-25-2006, 01:55 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1