Need Excel pivot table independent columns and no sub levels

Question

Experts: Need an help - i want the excel pivot table to show each logically independent columns next to each other instead of sub level/nested.

I can surely create multiple pivot table but would be ending up having 10+. So is it possible and if yes how? I tried Google and looking through videos but they were of no help, so I'm finally writing a post.

This is how I want it to look:

Added a sample sheet and the pivot table that i get and how i want it to be :)

Solution 1

Perhaps if you added columns to your original table, and then Pivot those columns, you could get something like what you want:

Formulas:

E2: =N(Sheet1!$D2="Disabled")
F2: =N(Sheet1!$D2="Enabled")
G2: =N(Sheet1!$C2="Disabled")
H2: =N(Sheet1!$C2="Enabled")

Fill down.

Then create your pivot table: (Note that you will want to use the SUM and not the COUNT function)

Solution 2

I believe you are looking for the report layout settings.Choose something other than "Compact" and you should meet your goal. "No sub-levels" can be obtained by clicking Subtotals and Grand Totals to the left of Report Layout

Comments

[Máté Juhász]

Please post also some raw data. And explain what's the difference between the two tables, it's not obvious.

[prakash]

Shared the raw data, any views ?

[prakash]

I have already tabular form select. i want it to be like this docs.google.com/… Thank you for the reply

[prakash]

Is it possible ? any thoughts or view. This would really help me to create dashboards for the Leadership. a real need ( the google drive doesn't show the pivot tables online properly, dont know how to add file here)

[prakash]

please let me know if you are trying as i dont see anyone else trying to help me. If no answer, its better to delete an unsolved post

[JaredT]

I've poked around for a bit and couldn't find anything that might entirely collapse the column labels. You may want to consider restructuring the layout of your data. Even if an answer does not successfully answer the question there is value in leaving it so others understand what is not the answer.

[prakash]

okay, thank you :) So you have tried columns(SCOM & SCCM service) to be not sub levels but appear next to each other instead of sub-group. Any option of Fields Sets options will help here?

[JaredT]

For them to appear on the same level in the pivot table they'd need to be different values in the same column of the source data

[prakash]

thank you, one more thing - making it to match my scenario. I have a data connection to csv file present on a global share, now im creating a pivot table via the connection. Any suggestions? one option i to modify the source script to create these (SCCM service disabled) but if i want to do during the import, any solutions ?

[Ron Rosenfeld]

@Prakash You would have to modify the Connection script to do that.