Recently, a client shared data with me that is clearly a pivot table and wanted me to make another pivot from it using Excel. This is a common and annoying problem we all face when working with Excel. Today, let me share my approaches for creating a pivot from another pivot report using Excel.
Option 1: If you have access to “original” data
Ha, I know, but we can dream eh? So, if you do have access to the original data from which the pivot is generated, just use that data and make the new pivot as you want.
If you need help creating a pivot report in the first place, learn the process here.
Option 2: Making a Pivot from Another Pivot when you don’t have access to original data
Tbh, this is the real scenario for most of us. We have a pivot and don’t have access to the data that was used to make it. Now we need to make another pivot. In this case, follow the below steps.
Pivot from Another Pivot – FREE Excel Template
I created a free Excel template to guide you thru the process with sample data. Download it here and use the sample data to understand the process better.
Step 1: Select and name your pivot range
- Select the entire pivot table (including any headers) in Excel.
- Go to the name box (next to formula bar on the left)
- Type the name “pivot_range”
- Pro tip: If you have multiple pivots, you can use names like “pivot_range1”, “pivot_range2”
See this illustration for the step.
Step 2: Go to Data Ribbon and load up the “pivot” to Power Query
- Keep the pivot table selected
- Go to Data Ribbon
- Click on “From Table/Range” option in the Get & Transform Data area
This will load the Power Query Editor with your Pivot Table Data.
Step 3: Let’s “unpivot” the Pivot Table with Power Query
Now that our “pivot table” is in Power Query, we can “unpivot” it and create a regular table. This can be used to make our new pivot table.
Here is a snapshot of how the Power Query editor looks with the pivot_range data.
[optional step] Promote headers if needed
Depending on how your source Pivot is setup, you may need to adjust the column headings in Power Query. For example, in my case, I need to promote the headers. To do this, click on “Use First Row as Headers” button in the Home ribbon of Power Query editor.
See below illustration.
Step 4: Replace “null” with value from above
In my sample pivot, you can see that Rep name is not printed in all rows, just the first row. This shows up as null in the Power Query editor for rest of the rows. We just need to fill these down based on the top value.
- Select the column(s) with this problem
- Go to “Transform” ribbon in Power Query Editor
- Click on “Fill” and select Down to fill down all the nulls with the value from above
Step 5: Remove rows with “totals” & “sub-totals”
We don’t need totals or sub-totals any more. We will calculate them in the new pivot as needed. For now, let’s remove all the rows and columns that have totals.
- Select the first column that has “total” labels
- Click on “filter” button
- Uncheck any total labels.
- Repeat the steps for any other rows that need this clean-up step.
- Pro tip: Use Text Filters > Does not contain to filter out all rows with “total” word in them.
Step 6: Remove Grand total / Sub-total columns (if any)
Let’s also remove any “grand total” columns and “sub-total” columns from our pivot report. Right click on the column with totals and select “remove” to take this column out.
Step 7: Unpivot the data
Finally, our pivot report is ready to be unpivoted.
- Select the column(s) with row labels. In the above example, I selected “representative” and “day of week” columns
- Pro Tip: Hold SHIFT or CTRL to select multiple columns in one go.
- Right click on the column headings of either column.
- Select “unpivot other columns”
- This should reshape the pivoted data to unpivoted format.
- See this quick demo (GIF):
Step 8: Rename the new “attribute” & “value” columns
Double click on the newly added “attribute” and “value column headers to rename them to appropriate labels. In my case, I named them – Gender & Calls.
Step 9: Load the data back to Excel so we can make the pivot
Ok. We are done. Just load the data back to Excel. To do this, go to “Home” ribbon and click on “Close & Load” button.
Step 10: Create the Pivot from the loaded data
Once the data is in Excel, just select any cell in the data, go to Insert > Pivot Table (shortcut: ALT N V T) and set up the pivot as per your needs. In my case, I needed the pivot report with number of calls by Day of Week & Representative. So here is how I made it (see the quick video demo).
Things to keep in mind:
The Power Query based approach to create pivot from another pivot is great, but you need to keep a few things in mind.
- Doesn’t work for averages: If your original pivot table has “averages” instead of “sums”, the new pivot will not be correct. This is because you will make the mistake of “averaging averages”. This technique works great for sums & counts only. Any other measures like average / median / min /max, you need “actual” data to make the new pivot.
- Power Query steps can get complicated: If your original pivot has a very complex, nested layout, then the PQ steps needed to “transform” data can be complex (but not impossible). I suggest learning how to use Power Query to solve such issues. Refer to this article or video to start your PQ journey.
- Needs refresh for data changes: If your original Pivot table changes (new values or new rows / columns), you need to update the “pivot_range” named range and refresh the power query data.
- To update the named range: Go to Formula ribbon in Excel and click on “Name Manager”. Select the name “pivot_range” and edit it. Adjust the cell references as per your newly updated pivot.
- To Refresh Power Query: Right click on the Power Query data you have loaded in Step 9. Select “Refresh’ to update the loaded data with new changes. Now go to the pivot you made (in step 10) and refresh that too (you guessed it right! Right click and Refresh).
What to do if you get an error (in Power Query):
Errors can happen either during the initial process (steps 3 to 9) or when you refresh the power query connection. Solving the error depends on your exact pivot table layout and what changes were made. But here are the most likely reasons for the error.
- Column names have changed: You will get error if your columns (in the original pivot) were changed between updates. Adjust the names in the original pivot or go to Power Query editor, locate the step where the error is happening and adjust the names there.
- Data type issues: If for some reason, your original pivot’s values are read by Power Query as “text”, it can create issues. Right click on the columns with numbers and explicitly convert them to numbers in PQ.
- Layout changes: If your pivot layout changes (say, instead of 2 columns, it now has 3 columns of row labels), then your refresh will fail. You need to select one more column before unpivoting (step 7).
- Other issues: Leave a comment with the issue / error you are facing so I can help.
Pivot from Another Pivot – FREE Excel Template
I created a free Excel template to guide you thru the process with sample data. Download it here and understand the process better.
In conclusion:
Power Query in Excel offers an elegant, simple and easy way to deal with the annoying issue of using “pivot tables” as data source. I had plenty of success with this method and I hope will too. If you do have any questions or face issues during the process, leave a comment.
25 Responses to “Shift Calendar Template – FREE Download”
Hi Chandoo,
your recent postings include only Excel 2007 templates. Unfortunately the company I work at still runs Excel 2003. Is it possible to get your awesome files in other excel version as well?
Thanks so much for your great excel stuff!
Is it possible to do this for shifts with hours instead of days? To organise a three shift day?
Thanks in advance,
Stelios
In my organization there are 45 employees i need split then into three shifts ex:A shift:14,B shift:14,C shift:14 and week off:3 kindly help me on this.
@Masthan
You need to understand what rules your company has for the various shifts / roster combinations
Chandoo, I once did a shift control spreadsheet for my team. I put one person in each line, the columns were the days. I put a shift code in each cell indicating in which shift that person should work, or if the person were out that day. I have two codes for being out. One is for vacations and one is to compensate days worked in weekends. This way I was able to count how many persons I have in each shift, how many were on vacations and how many were out compensating (that's the term we use here) weekend worked hours.
Later I included the possibility of a person be in two lines one for normal hours other for overtime. This is mainly used for planning purposes. If you would like I can send you an example. The only problem of this spreadsheet is that we don't have a person view, only this consolidated view.
Hi George, I would like to have a copy of your spreadsheet if you can share it.
Thanks in advance, Chuck
Hi Chandoo,
Where is the code located ? is it VBA ? If so , how do you hide it ? Or it is .NET ?
Thx
@Idan
.
No VBA or code, it is all done with Mirrors.
Only Joking,
.
But there is no VBA or code,
It is all done with Named Formulas and Lookups.
Have alook at the cells in the calander area and Named Formulas in the Formulas, Name Manager Tab.
How can i calculate between two or more different workbooks? Please, reply me as early as possible.
@Anand
Open the workbooks you want to link to
Start a formula = and click and change between workbooks as required.
You can use the View, Switch window menu to change workbooks mid formula
The format for using workbooks is
=[Workbook.xlsm]Sheet1!$A$1
or
=SUM('[Book2.xls]Sheet1'!$A$1:$D$10)
etc
Hi Chandoo,
I am working with a call centre wherein i ned to update at the month end 20 to 30 employees login hours which are defict to track it at the month end is very difficult is there any template which can be made to track that why on a particular day a guy who needs to be on calls was why not on calls.
Thank you so much Chandoo. This is really helping me. As usual, you rock.
What's FortyTwoDays and Calendar in Name manager?
Both are unused and FortyTwoDays doesn't make any sense.
I have a SQL db that contains records of events scheduled/completed on a particular date. Can this method ous building a calendar be used to display those events on the respective day?
Positively awesome!
I'm attempting to help a friend create a schedule for adult classes - and of course its not"paid help". Here is the scenario:
20 classes, instructor, room#, student class size, start date, number of class days (need to subtract weekends)
class
instructor
room
students
start
#days
PATH
karen
201
21
01/01/13
11
BILLING
jane
401
15
01/12/13
13
MEDISOFT
mike
301
11
01/25/13
9
he'd like to see these classes show up in different colors within the same month's calendar chart. He can draw it, but I'd like to see it done automatically through data, and I just can't visualize it, but I KNOW this will work - can you help?
Jan 🙂
Dear chandoo,
Try many way to download still can't access. Any way we want to try out 3 shifts with 3 guys in a group .eg Group A Morn, Group B Night and Group C Rest. And every each group must work on sunday to take turns. In fact we are security teams so that's why sunday is required to work. Pls guide and show how to put in the working calendar. Thank you in advance.
I've been trying to copy and/or recreate this to use in a workbook I'm doing for the transportation department I'm working for. I need to have the calendar on the first sheet in my document (it has graph's from data on another sheet). I'm trying to use it to track (with the conditional formatting) accidents and injuries. I've redone the conditional formatting to do 4 different accident types (no injury, near miss, OSHA recordable injury and work loss injury), but when I enter the formula's you have in the calendar portion where it says "DateOfFirst-FirstWeekDay" I can't figure out how you did that. Are you able to help?
I would like to use Excel to solve the following problem for a community work. I want to create a Driver schedule for a given month from a pool of volunteers for a community service. Each of these volunteers can drive only on specific days in a week. I would like to populate the driving schedule for each weekday with primary, secondary and tertiary drivers in a random fashion so that I do not overburden one person. I would greatly any help you can provide.
Hi chandoo,
Thanks for your valuable effort for create this template and let me know how to add multiple employees in the the Roaster.
Hi Chandoo,
This article on shift roaster is very helpful. Could you please let me know how i can use the same for n number of resources who work 24/7, considering their leaves and holidays?
Thanks,
Savitha
Hi Chandoo,
This article on shift roaster is very helpful to all. Could you please let me know how i can use the same if I want to add for some more shifts, since the color is not getting change if I add more shifts like 4,5 etc.,
Thanks,
Murali
nice post
How can I change the date to 2017 under Shift Data worksheet.
solution 1:
mydata=B2:C16
stoplist=E2:E8
=LET(RNG,A2:A16,SMR,C2:C16, F,(RNG=E2)+(RNG=E3)+(RNG=E4)+(RNG=E5)+(RNG=E6)+(RNG=E7)+(RNG=E8),SUM(SMR)-SUM(SMR*F))
=LET(RNG,A2:A16,SMR,C2:C16,RH,N(B2:B16=B2), F,(RNG=E2)+(RNG=E3)+(RNG=E4)+(RNG=E5)+(RNG=E6)+(RNG=E7)+(RNG=E8),TOT,SUM(SMR)-SUM(SMR*RH*F),SUM(SMR*RH)-SUM(SMR* RH*F))
ALTERNATE SOLUTION
=SUM(C2:C16)-SUM(FILTER(C2:C16,ISNUMBER(BYROW(A2:A16,LAMBDA(a,TOROW(SEARCH(a,E2:E8),2))))))
=SUM((B2:B16=B2)*(C2:C16))-SUM((ISNUMBER(BYROW(A2:A16,LAMBDA(a,TOROW(SEARCH(a,E2:E8),2))))*(B2:B16=B2)*(C2:C16)))
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Replaced Value" = Table.ReplaceValue(Source,null,";",Replacer.ReplaceValue,{"Column1"}),
#"Transposed Table" = Table.Transpose(#"Replaced Value"),
#"Removed Other Columns" = Table.SelectColumns(#"Transposed Table",{"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27", "Column28", "Column29", "Column30", "Column31", "Column32", "Column33", "Column34", "Column35", "Column36", "Column37", "Column38", "Column39", "Column40", "Column41", "Column42", "Column43", "Column44", "Column45", "Column46", "Column47", "Column48", "Column49", "Column50", "Column51", "Column52", "Column53", "Column54", "Column55", "Column56", "Column57", "Column58", "Column59", "Column60", "Column61", "Column62", "Column63", "Column64", "Column65", "Column66", "Column67", "Column68", "Column69", "Column70", "Column71", "Column72", "Column73", "Column74", "Column75", "Column76", "Column77", "Column78", "Column79", "Column80", "Column81", "Column82", "Column83", "Column84", "Column85", "Column86", "Column87"}),
#"Merged Columns" = Table.CombineColumns(#"Removed Other Columns",{"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27", "Column28", "Column29", "Column30", "Column31", "Column32", "Column33", "Column34", "Column35", "Column36", "Column37", "Column38", "Column39", "Column40", "Column41", "Column42", "Column43", "Column44", "Column45", "Column46", "Column47", "Column48", "Column49", "Column50", "Column51", "Column52", "Column53", "Column54", "Column55", "Column56", "Column57", "Column58", "Column59", "Column60", "Column61", "Column62", "Column63", "Column64", "Column65", "Column66", "Column67", "Column68", "Column69", "Column70", "Column71", "Column72", "Column73", "Column74", "Column75", "Column76", "Column77", "Column78", "Column79", "Column80", "Column81", "Column82", "Column83", "Column84", "Column85", "Column86", "Column87"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"Merged"),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Merged Columns", {{"Merged", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Merged"),
#"Added Prefix" = Table.TransformColumns(#"Split Column by Delimiter", {{"Merged", each "|" & _, type text}}),
#"Replaced Value1" = Table.ReplaceValue(#"Added Prefix","||","|",Replacer.ReplaceText,{"Merged"}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Replaced Value1", "Merged", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Merged.1", "Merged.2", "Merged.3", "Merged.4", "Merged.5", "Merged.6", "Merged.7", "Merged.8"}),
#"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter1",{"Merged.1"}),
#"Removed Duplicates" = Table.Distinct(#"Removed Columns")
in
#"Removed Duplicates"