'Merging Two Columns in Pivot Table
I am trying to reformat my pivot table. Currently, there are two columns called closed and completed, which essentially represent the same thing. I would like to merge these two columns together. How can I do this?
Solution 1:[1]
If you're working with an external datasource (SQL or Access data), this page walks you through how to do it. http://answers.microsoft.com/en-us/office/forum/office_2010-excel/merge-two-field-sources-in-one-pivot-table-column/934518fe-5e91-47db-95d2-c5d1b4e31082
If you're using internal excel data ranges, you'll need to concatenate the columns first, then bring the concatenated column into your Pivot table.
ie. dataset is A1:G50, columns to combine are F & G. In cell H1, type =f1&g1 hit enter, and use the fill anchor (bottom right of cell) to fill down. Now create your Pivot Table with A1:H50, and just ignore columns F & G.
Solution 2:[2]
If the two fields columns represent the same thing, consider whether you can use just one of the fields in your report.
Let's say for your completed tasks field Closed says "Yes" and field Completed says either "Done" or "True".
If you concatenate the two fields' values you'll have PivotItems: "Yes-Done" and "Yes-True" etc..." If it's necessary to report all those combinations separately, then my suggestion to use one field isn't a good fit.
If those combinations all have the same meaning, then you could keep it simple and just use one field.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | adsweeny |
| Solution 2 | Jerry Sullivan |