Need help with sorting/grouping data either groups and/or pivot table

Best is a term that is subject to much interpretation. The attached of your file shows a way to display and count the personnel in each Regional Office using a Pivot Table:
Copy of Demo template.xls
Let me know if you have any questions.

Originally Posted by exced

how did you add the Values under the Column Tab ?

I am a little uncertain about what you mean by columns tab, however if you are referring to the grand totals a pivot table puts those in by default (change using design > layout > grand totals if desired).

Originally Posted by exced

Also under the Values field, I see you added all the # for each title that is reflected in the data portion of the table.

Did you have to do it manually or is there a way to simply add information in the Values section and automatically it will generate the sum/count and present it in the table?

I was trying to shorten the width of the columns so I manually put a # symbol in place of the "Count of" that the pivot table automatically shows when you drag one of the fields into the values box. Here is the way the table would look otherwise:
Copy of Demo template.xls
Enable Editing to see the layout of the field list.
Let me know if you have any questions.

The "Column Labels" window will fill automatically when you pull fields from the "Choose fields to add to report:" window to the "Values" window.

On second thought let me go ahead and attempt to explain the rest of the process.
-Next un-select #DD through #Adviser.
-Pull the fields District Director through Adviser into the Values window just as you did into the Row Labels.
-If you want to change the column heading of Count of Division Director to #DD select the column heading (E4) and type in #DD.
Notice that in the copy of your file I put in post #2 I changed the names slightly so that the pivot table would give a count of different individuals holding the positions. That may or may not be the situation, I was just making a guess.

OK, lets start with the fields that have been pulled into the Row Labels. After pulling them down there, individually select each of the fields (in the Row Labels window), you will then see a pop up from which you need to select the has the last choice of "Field Values". That will cause another window to pop up with a Subtotals & Filters tab, from that tab select None and click OK. Let me know when you finish that step and we can proceed.

Notice in the attached file that I removed the Column headings that started with # from the data sheet and un-selected them in the pivot table Field List. I pulled the four fields named Division Director, Consultant, Associate and Advisor from the Choose fields to add to report window into the Values window, just as I did earlier with those same fields pulled into the Row Labels window. When I pull each of those four fields down there the pivot table automatically changes the name to Count of Division Director ... I hope that this is making some sense or that you'll see what I am talking about by looking at the Field List.

Copy of Copy of Demo template-1.xls
Let me know if you have any questions.

You are welcome and thank you for the feedback. If you have not already please mark the thread as 'Solved' using the thread tools link above your first post. I hope that you have a good day.