A tough nut to crack: Excel pivot table layout

%3CLINGO-SUB%20id%3D%22lingo-sub-2911381%22%20slang%3D%22en-US%22%3EA%20tough%20nut%20to%20crack%3A%20Excel%20pivot%20table%20layout%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2911381%22%20slang%3D%22en-US%22%3E%3CP%3ESo%20here's%20a%20tricky%20one.%20(At%20least%20for%20me)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20trying%20to%20set%20up%20a%20worksheet%20in%20order%20to%20organize%20and%20visualize%20data.%3C%2FP%3E%3CP%3EThe%20best%20way%20to%20do%20that%2C%20was%20instinctively%20a%20pivot%20table.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20first%20problem%20that%20I%20stumbled%20upon%20is%20that%20when%20creating%20a%20pivot%20table%2C%20you%20have%20to%20unmerge%20cells.%20I%20then%20unmerged%20all%20cells%20and%20autofilled%20the%20blanks%20with%20a%200.%20As%20this%20negatively%20impacts%20the%20layout%2C%20is%20there%20a%20way%20to%20%22merge%22%20the%20cells%2C%20without%20really%20merging%20them%3F%20Maybe%20something%20like%20an%20overlap%20feature%3F%26nbsp%3B%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Untitled.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F322659iF369F773F615B3B4%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Untitled.png%22%20alt%3D%22Untitled.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EThe%20next%20problem%20is%20that%20due%20to%20the%20unmerging%20of%20the%20cells%2C%20the%20resulting%20pivot%20table%20is%20much%20more%20cumbersome%20to%20work%20on%2C%20than%20first%20intended.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Untitled2.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F322670iAFBD8F0FE694FE79%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Untitled2.png%22%20alt%3D%22Untitled2.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EThe%20unmerging%20of%20the%20cells%20means%20that%20the%20category%20%22Classification%20summary%22%20is%20no%20longer%20applied%20to%20the%20previous%20range%2C%20and%20is%20now%20limited%20to%20the%20cell%20below.%20That%20means%20that%20I%20now%20have%20to%20manually%20add%20every%20single%20cell%20that%20was%20previously%20within%20that%20range.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I%20was%20hoping%20for%2C%20if%20you%20look%20at%20the%20first%20picture%2C%20is%20that%20when%20I%20insert%20classification%20summary%20into%20the%20legend%2C%20that%20all%20cells%20will%20then%20be%20shown.%3C%2FP%3E%3CP%3EMeaning%20that%20a%20chart%20of%20%22Classification%20summary%22%20would%20automatically%20show%20its%20respective%20bars%20and%20their%20individual%20values%26nbsp%3B%20for%20%22OK%2C%20FE%2C%20NV%2C%20RI%2C%20DL%2C%20TO%2C%20TU%22.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EA%20solution%20or%20a%20possible%20workaround%20would%20be%20deeply%20appreciated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJD%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2911381%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ECharting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2911728%22%20slang%3D%22en-US%22%3ERe%3A%20A%20tough%20nut%20to%20crack%3A%20Excel%20pivot%20table%20layout%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2911728%22%20slang%3D%22en-US%22%3EYour%20raw%20data%20is%20in%20a%20crosstabular%20layout.%20You%20have%20to%20unpivot%20it%20first.%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

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. 

 

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? Untitled.png

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.

 

JD

1 Reply
Your raw data is in a crosstabular layout. You have to unpivot it first.