05-24-2020 04:52 PM
05-24-2020 04:52 PM
Suppose I have a table:
Different departments vs equipment:
Columns: Sales, Marketing, Front-end, Back-end, Database
Rows: Desktop, Laptop, Tablet
Now I want to group a few of the columns such as front-end, back-end into Development.
I then want to use all this information in a pivot table. (see attached sample)
How do I model the property of some columns belonging to another group?
05-24-2020 05:52 PM
In order to show 3 departments under Development one as you wish, you need to list them as division under that department in your raw data table. Your Yes/No need to be in the format of number (1 = Yes, 0 = No). Then you pivot your raw data table so that Department is on top of Division under Columns. You have to set Value fields to "Min" so that if any department has any Yes then the output will show 1 (rather than sum or count which result in incorrect output). To clearly show 3 departments are under Development, it is suggested you choose different color for their headings and also set Development as Center Across Selection (Format Cells/Alignment/Horizontal). Finally, choose the whole area containing all numbers of the pivot table then Format Cells/Custom/"Yes";;"No".
Pls see the attached workbook as a sample,
05-24-2020 06:40 PM
@hynguyen Thanks for your response.
The one problem is that suppose I have many rows for equipment, let's say 100. It will become very difficult to maintain.
I was thinking I can maintain a separate table for Division and department. However, I am not sure how to use that in a Pivot (if I have two tables).
05-24-2020 06:58 PM
@nilanjenator In case you have much longer list of Equipment, I think you should consider using Data Model feature instead. It is like a small version of Microsoft Access (database) within Excel. Please check about it here: https://support.office.com/en-us/article/create-a-data-model-in-excel-87e7a54c-87dc-488e-9410-5c75db...