Need Excel pivot table independent columns and no sub levels

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 :)

prakash

Posted 2016-07-30T12:35:54.187

Reputation: 13

Please post also some raw data. And explain what's the difference between the two tables, it's not obvious. – Máté Juhász – 2016-07-30T12:46:30.640

Shared the raw data, any views ? – prakash – 2016-07-30T13:14:19.753

Answers

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)

Ron Rosenfeld

Posted 2016-07-30T12:35:54.187

Reputation: 3 333

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 ? – prakash – 2016-08-02T13:12:27.867

@Prakash You would have to modify the Connection script to do that. – Ron Rosenfeld – 2016-08-02T13:59:08.463

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

JaredT

Posted 2016-07-30T12:35:54.187

Reputation: 1 012

I have already tabular form select. i want it to be like this https://docs.google.com/uc?authuser=0&id=0B-t0S1XlXOxpbFJCX3YxYTQtams&export=download Thank you for the reply

– prakash – 2016-07-30T13:06:28.440

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 – 2016-07-30T13:16:15.393

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 – prakash – 2016-07-30T13:30:35.407

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. – JaredT – 2016-07-30T14:00:39.450

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? – prakash – 2016-07-30T14:17:40.757

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 – JaredT – 2016-07-30T14:21:40.903