Forum Discussion
How do you add a third dimension to a table?
Suppose I have a table:
Different departments vs equipment:
Columns: Sales, Marketing, Front-end, Back-end, Database
Rows: Desktop, Laptop, Tablet
Data: Yes/No
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?
4 Replies
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,
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).
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-5c75dbcb0f7b
