+ Reply to Thread
Results 1 to 2 of 2

A tough nut to crack: Excel pivot table layout

  1. #1
    Registered User
    Join Date
    11-01-2021
    Location
    Copenhagen
    MS-Off Ver
    MS 365
    Posts
    1

    A tough nut to crack: Excel pivot table layout

    So here's a tricky one. (At least for me)



    I'm trying to set up a worksheet in order to organize and visualize data.

    The best way to do that, was instinctively a pivot table.

    Untitled.png

    The first problem that I stumbled upon is that when creating a pivot table, you have to unmerge cells. I then unmerged all cells and autofilled the blanks with a 0. As this negatively impacts the layout, is there a way to "merge" the cells, without really merging them? Maybe something like an overlap feature? thumbnail image 1 of blog post titled

    The next problem is that due to the unmerging of the cells, the resulting pivot table is much more cumbersome to work on, than first intended.

    Untitled2.png

    The unmerging of the cells means that the category "Classification summary" is no longer applied to the previous range, and is now limited to the cell below. That means that I now have to manually add every single cell that was previously within that range.
    What I was hoping for, if you look at the first picture, is that when I insert classification summary into the legend, that all cells will then be shown.
    Meaning that a chart of "Classification summary" would automatically show its respective bars and their individual values for "OK, FE, NV, RI, DL, TO, TU".


    A solution or a possible workaround would be deeply appreciated.

    Thanks in advance

    JD

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: A tough nut to crack: Excel pivot table layout

    Hi and welcome to the forum

    Pictures are of little value. Honestly, no one wants to re-type your data to try and solve your issue. Additionally, we would only be guessing at how your data was structured, ie. formulas, formatting, etc. Additionally, due to how some browsers behave, many of our members cannot see uploaded pictures/images. Please do not take this route.

    Please attach a sample file that represents what you have. The structure of your attachment should be the same structure as your actual data. Any proprietary information should be changed.

    Include in the attachment any code you're currently using (whether it works or not) and an "After" of what you wish the output to be.

    To attach a file to your post,
    click advanced (next to quick post),
    scroll down until you see "manage file",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

+ 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. Replies: 5
    Last Post: 03-08-2018, 08:14 AM
  2. [SOLVED] Pivot table-remove expand and close from pivot layout
    By excelmr in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-01-2015, 07:00 PM
  3. [SOLVED] Change and autorefresh pivot table as per the selection (Very tough one )
    By amitav in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-18-2012, 10:51 AM
  4. Pivot Table layout
    By leo73pk in forum Excel General
    Replies: 2
    Last Post: 12-21-2010, 12:29 AM
  5. Pivot Table Layout changes
    By tek9step in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-24-2010, 12:11 PM
  6. pivot table layout
    By indieman in forum Excel General
    Replies: 4
    Last Post: 03-11-2010, 04:57 AM
  7. Pivot Table Layout
    By smlaff01 in forum Excel General
    Replies: 4
    Last Post: 11-01-2007, 09:06 AM

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