{"text_format":"1","workbook":"xlsx","n_sheets":9,"sheets":["Start here","How to create a Pivot Table","How to use a Pivot Table","Sorting and filtering","Formatting your Pivot Table","Pivot Charts","Thank you","AnswerOptions","Source data"],"not_rendered":{"charts":0,"images":4,"macros":1,"external_links":0,"defined_names":0,"comments":0,"pivot_tables":4},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Start here","index":0,"n_rows":25,"n_cols":9,"rows_kept":25,"cut":false,"hidden":false,"merged":[[17,7,17,11],[3,8,3,10],[4,1,5,4],[16,7,16,11],[4,7,4,11],[5,7,5,11]]}
[]
[]
[]
[null,null,null,null,null,null,null,null,"Important!"]
[null,"Welcome!",null,null,null,null,null,"When you open this workbook for the first time"]
[null,null,null,null,null,null,null,"you'll see one (or both) of the following warnings:"]
[null,"I'm so glad you downloaded this workbook. "]
[]
[null,"When you practice what you've learned in this guide"]
[null,"you'll reap the benefits of two very important factors:"]
[]
[null,"1) You'll remember everything much better."]
[]
[null,"2) You will be able to apply what you've learned to a "]
[null,"broader range of real-world scenarios."]
[]
[null,"Click the next sheet to get started. If you get stuck, click",null,null,null,null,null,"Click 'Enable Editing' and/or 'Enable Content'"]
[null,"the hint buttons to get help.",null,null,null,null,null,"for this Assignment to work properly"]
[]
[null,"In order for this workbook to work properly, "]
[null,"follow the quick guide to the right."]
[]
[null,"Best regards"]
[null,"Kasper Langmann"]
[null,"Co-founder of ","Spreadsheeto"]

{"sheet":"How to create a Pivot Table","index":1,"n_rows":36,"n_cols":7,"rows_kept":36,"cut":false,"hidden":false,"merged":[[0,0,1,8],[2,6,4,6]]}
["How to create a Pivot Table"]
[]
[null,null,null,null,null,null,"Answer in the green cells"]
[]
[]
["1)","If you have raw data, how powerful will your",null,null,null,null,"Click the drop-down -->"]
[null,"Pivot Table be?"]
[]
["2)","If you have very processed data, how powerful",null,null,null,null,"Click the drop-down -->"]
[null,"will your Pivot Table be?"]
[]
[]
["3)","Create a Pivot Table from Dataset1 below that shows the"]
[null,"sales of each department."]
[]
["4)","Add the additional data from Dataset2 to Dataset1 and"]
[null,"make the Pivot Table include the new data."]
[]
[null,null,"Dataset1"]
[null,null,"Full name","Department","Salary"]
[null,null,"Abigail Aalderink","Sales",45000]
[null,null,"Sanford Bartolo","Sales",59000]
[null,null,"Samuel Bartnick","Sales",54000]
[null,null,"John Dumas","IT",44000]
[null,null,"Kristi Hines","Production",45000]
[null,null,"Apple Lyn","IT",47000]
[null,null,"Lee Nazal","HR",40000]
[null,null,"Lindsy Kline","Marketing",59000]
[null,null,"Vicky James","HR",57000]
[null,null,"Bradley Sack","IT",42000]
[null,null,"Steven Lamar","Sales",62000]
[]
[null,null,"Dateset2"]
[null,null,"Full name","Department","Salary"]
[null,null,"Sally Johnson","Sales",42000]
[null,null,"Christy Brown","Sales",61000]

{"sheet":"How to use a Pivot Table","index":2,"n_rows":21,"n_cols":9,"rows_kept":21,"cut":false,"hidden":false,"merged":[[0,0,1,8],[2,6,4,6]]}
["How to use a Pivot Table"]
[]
[]
[]
[]
["1)","Remove the subtotals from the Pivot Table "]
[null,"to the right.",null,null,null,null,null,"Row Labels","Sum of Salary"]
[null,null,null,null,null,null,null,"Elly Kam",1484000]
[null,null,null,null,null,null,null,"Marketing",254000]
["2)","Change the 'Value Field Settings' so you can see",null,null,null,null,null,"Production",459000]
[null,"the average of the salary for each department.",null,null,null,null,null,"Sales",771000]
[null,null,null,null,null,null,null,"Scott Joyce",1234000]
[null,null,null,null,null,null,null,"Accounting",211000]
["3)","Insert a 'Calculated Field' that shows the monthly",null,null,null,null,null,"Adm",362000]
[null,"salary of each employee.",null,null,null,null,null,"HR",250000]
[null,null,null,null,null,null,null,"IT",411000]
["4)","In the green cell below, divide the sum of the salaries",null,null,null,null,null,"Grand Total",2718000]
[null,"in the production department with 12. Use a "]
[null,"reference to the number in the Pivot Table."]
[null,"Afterwards, move the 'Nearest Manager' field to the"]
[null,"Columns' area of the Pivot Table."]

{"sheet":"Sorting and filtering","index":3,"n_rows":68,"n_cols":9,"rows_kept":68,"cut":false,"hidden":false,"merged":[[0,0,1,8],[2,6,4,6]]}
["Sorting and filtering"]
[]
[]
[]
[]
["1)","Filter out the 'Accounting' and 'Adm' departments"]
[null,"from the Pivot Table.",null,null,null,null,null,"Row Labels","Sum of Salary"]
[null,null,null,null,null,null,null,"Elly Kam",1484000]
[null,null,null,null,null,null,null,"Marketing",254000]
["2)","Use a filter rule that filters out all employees with",null,null,null,null,null,"Annie Hunter",55000]
[null,"a salary below 50,000.",null,null,null,null,null,"Bobbi Simon",42000]
[null,null,null,null,null,null,null,"Chatsworth Miller-Stevenson",47000]
[null,null,null,null,null,null,null,"Jake Prock",51000]
["3)","Insert a Slicer that filters on the two managers.",null,null,null,null,null,"Lindsy Kline",59000]
[null,null,null,null,null,null,null,"Production",459000]
[null,null,null,null,null,null,null,"David Jones",84000]
["4)","Create a Report Filter with the Department fields",null,null,null,null,null,"Emily Bodenham",53000]
[null,"and display only the 'Marketing', 'Sales', and the ",null,null,null,null,null,"Kristi Hines",45000]
[null,"HR' department.",null,null,null,null,null,"Mitch Gramlow",56000]
[null,null,null,null,null,null,null,"Peter Bowland",63000]
[null,null,null,null,null,null,null,"Robert Nole",56000]
[null,null,null,null,null,null,null,"Sarah Smith",47000]
[null,null,null,null,null,null,null,"Steven Perry",55000]
[null,null,null,null,null,null,null,"Sales",771000]
[null,null,null,null,null,null,null,"Abigail Aalderink",45000]
[null,null,null,null,null,null,null,"Chris Borgogni",56000]
[null,null,null,null,null,null,null,"Daniel Turek",40000]
[null,null,null,null,null,null,null,"Darrel Hobbs",58000]
[null,null,null,null,null,null,null,"David Goods",53000]
[null,null,null,null,null,null,null,"Irene Pawell",61000]
[null,null,null,null,null,null,null,"James Sperman",65000]
[null,null,null,null,null,null,null,"Lorna Walker",56000]
[null,null,null,null,null,null,null,"Mary Oms",42000]
[null,null,null,null,null,null,null,"Matt Baker",63000]
[null,null,null,null,null,null,null,"Samuel Bartnick",54000]
[null,null,null,null,null,null,null,"Sanford Bartolo",59000]
[null,null,null,null,null,null,null,"Steven Lamar",62000]
[null,null,null,null,null,null,null,"Valerie Stevens",57000]
[null,null,null,null,null,null,null,"Scott Joyce",1234000]
[null,null,null,null,null,null,null,"Accounting",211000]
[null,null,null,null,null,null,null,"Jake Fann",63000]
[null,null,null,null,null,null,null,"Joe Keenan",48000]
[null,null,null,null,null,null,null,"Sam Zaborszky",60000]
[null,null,null,null,null,null,null,"Scott Jastrow",40000]
[null,null,null,null,null,null,null,"Adm",362000]
[null,null,null,null,null,null,null,"Andrew Gupta",63000]
[null,null,null,null,null,null,null,"Caylie Price",57000]
[null,null,null,null,null,null,null,"Daniella Ruder",50000]
[null,null,null,null,null,null,null,"Jaremy Davis",52000]
[null,null,null,null,null,null,null,"Stephanie Miller-Stevenson",57000]
[null,null,null,null,null,null,null,"Steven Davidson",41000]
[null,null,null,null,null,null,null,"Susan Ricas",42000]
[null,null,null,null,null,null,null,"HR",250000]
[null,null,null,null,null,null,null,"Lee Nazal",40000]
[null,null,null,null,null,null,null,"Mike O'Neil",61000]
[null,null,null,null,null,null,null,"Nate Harris",45000]
[null,null,null,null,null,null,null,"Richard David",47000]
[null,null,null,null,null,null,null,"Vicky James Lilian Samson",57000]
[null,null,null,null,null,null,null,"IT",411000]
[null,null,null,null,null,null,null,"Apple Lyn",47000]
[null,null,null,null,null,null,null,"Bradley Sack",42000]
[null,null,null,null,null,null,null,"Bryan Sloane",59000]
[null,null,null,null,null,null,null,"John Dumas",44000]
[null,null,null,null,null,null,null,"Jonny Chan",50000]
[null,null,null,null,null,null,null,"Mai Taylor",45000]
[null,null,null,null,null,null,null,"Mark Cox",61000]
[null,null,null,null,null,null,null,"Tom Briones",63000]
[null,null,null,null,null,null,null,"Grand Total",2718000]

{"sheet":"Formatting your Pivot Table","index":4,"n_rows":55,"n_cols":9,"rows_kept":55,"cut":false,"hidden":false,"merged":[[0,0,1,8],[2,6,4,6]]}
["Formatting your Pivot Table"]
[]
[]
[]
[]
["1)","Apply the 'Medium 8' Style to the Pivot Table."]
[null,null,null,null,null,null,null,"Row Labels","Sum of Salary"]
[null,null,null,null,null,null,null,"Elly Kam",1484000]
[null,null,null,null,null,null,null,"Marketing",254000]
["2)","Include a 1000 Separator in all values in the ",null,null,null,null,null,"Annie Hunter",55000]
[null,"Pivot Table.",null,null,null,null,null,"Bobbi Simon",42000]
[null,null,null,null,null,null,null,"Chatsworth Miller-Stevenson",47000]
[null,null,null,null,null,null,null,"Jake Prock",51000]
["3)","Highlight all values (that are not subtotals or",null,null,null,null,null,"Lindsy Kline",59000]
[null,"grand totals) using Conditional Formatting.",null,null,null,null,null,"Production",459000]
[null,null,null,null,null,null,null,"David Jones",84000]
["4)","Rename the 'HR' department to 'Human Resources'",null,null,null,null,null,"Emily Bodenham",53000]
[null,"in the Pivot Table to the right.",null,null,null,null,null,"Kristi Hines",45000]
[null,null,null,null,null,null,null,"Mitch Gramlow",56000]
["5)","Change the layout of the Pivot Table to 'Tabular form'",null,null,null,null,null,"Peter Bowland",63000]
[null,"and repeat all item labels.",null,null,null,null,null,"Robert Nole",56000]
[null,null,null,null,null,null,null,"Sarah Smith",47000]
[null,null,null,null,null,null,null,"Steven Perry",55000]
[null,null,null,null,null,null,null,"Sales",771000]
[null,null,null,null,null,null,null,"Abigail Aalderink",45000]
[null,null,null,null,null,null,null,"Chris Borgogni",56000]
[null,null,null,null,null,null,null,"Daniel Turek",40000]
[null,null,null,null,null,null,null,"Darrel Hobbs",58000]
[null,null,null,null,null,null,null,"David Goods",53000]
[null,null,null,null,null,null,null,"Irene Pawell",61000]
[null,null,null,null,null,null,null,"James Sperman",65000]
[null,null,null,null,null,null,null,"Lorna Walker",56000]
[null,null,null,null,null,null,null,"Mary Oms",42000]
[null,null,null,null,null,null,null,"Matt Baker",63000]
[null,null,null,null,null,null,null,"Samuel Bartnick",54000]
[null,null,null,null,null,null,null,"Sanford Bartolo",59000]
[null,null,null,null,null,null,null,"Steven Lamar",62000]
[null,null,null,null,null,null,null,"Valerie Stevens",57000]
[null,null,null,null,null,null,null,"Scott Joyce",661000]
[null,null,null,null,null,null,null,"HR",250000]
[null,null,null,null,null,null,null,"Lee Nazal",40000]
[null,null,null,null,null,null,null,"Mike O'Neil",61000]
[null,null,null,null,null,null,null,"Nate Harris",45000]
[null,null,null,null,null,null,null,"Richard David",47000]
[null,null,null,null,null,null,null,"Vicky James Lilian Samson",57000]
[null,null,null,null,null,null,null,"IT",411000]
[null,null,null,null,null,null,null,"Apple Lyn",47000]
[null,null,null,null,null,null,null,"Bradley Sack",42000]
[null,null,null,null,null,null,null,"Bryan Sloane",59000]
[null,null,null,null,null,null,null,"John Dumas",44000]
[null,null,null,null,null,null,null,"Jonny Chan",50000]
[null,null,null,null,null,null,null,"Mai Taylor",45000]
[null,null,null,null,null,null,null,"Mark Cox",61000]
[null,null,null,null,null,null,null,"Tom Briones",63000]
[null,null,null,null,null,null,null,"Grand Total",2145000]

{"sheet":"Pivot Charts","index":5,"n_rows":68,"n_cols":10,"rows_kept":68,"cut":false,"hidden":false,"merged":[[0,0,1,8],[2,6,4,6]]}
["Pivot Charts"]
[]
[]
[]
[]
["1)","Insert a Pivot Chart (column chart) from the Pivot Table"]
[null,"to the right.",null,null,null,null,null,null,"Row Labels","Sum of Salary"]
[null,null,null,null,null,null,null,null,"Elly Kam",1484000]
[null,null,null,null,null,null,null,null,"Sales",771000]
["2)","Arrange it so it shows the total salery for each department",null,null,null,null,null,null,"Abigail Aalderink",45000]
[null,"only - and not for each employee.",null,null,null,null,null,null,"Chris Borgogni",56000]
[null,null,null,null,null,null,null,null,"Daniel Turek",40000]
[null,null,null,null,null,null,null,null,"Darrel Hobbs",58000]
["3)","Sort it descending from left to right (based on total salary).",null,null,null,null,null,null,"David Goods",53000]
[null,null,null,null,null,null,null,null,"Irene Pawell",61000]
[null,null,null,null,null,null,null,null,"James Sperman",65000]
["4)","Filter out the 'Production', 'IT', and 'HR' department.",null,null,null,null,null,null,"Lorna Walker",56000]
[null,null,null,null,null,null,null,null,"Mary Oms",42000]
[null,null,null,null,null,null,null,null,"Matt Baker",63000]
[null,null,null,null,null,null,null,null,"Samuel Bartnick",54000]
[null,null,null,null,null,null,null,null,"Sanford Bartolo",59000]
[null,null,null,null,null,null,null,null,"Steven Lamar",62000]
[null,null,null,null,null,null,null,null,"Valerie Stevens",57000]
[null,null,null,null,null,null,null,null,"Production",459000]
[null,null,null,null,null,null,null,null,"David Jones",84000]
[null,null,null,null,null,null,null,null,"Emily Bodenham",53000]
[null,null,null,null,null,null,null,null,"Kristi Hines",45000]
[null,null,null,null,null,null,null,null,"Mitch Gramlow",56000]
[null,null,null,null,null,null,null,null,"Peter Bowland",63000]
[null,null,null,null,null,null,null,null,"Robert Nole",56000]
[null,null,null,null,null,null,null,null,"Sarah Smith",47000]
[null,null,null,null,null,null,null,null,"Steven Perry",55000]
[null,null,null,null,null,null,null,null,"Marketing",254000]
[null,null,null,null,null,null,null,null,"Annie Hunter",55000]
[null,null,null,null,null,null,null,null,"Bobbi Simon",42000]
[null,null,null,null,null,null,null,null,"Chatsworth Miller-Stevenson",47000]
[null,null,null,null,null,null,null,null,"Jake Prock",51000]
[null,null,null,null,null,null,null,null,"Lindsy Kline",59000]
[null,null,null,null,null,null,null,null,"Scott Joyce",1234000]
[null,null,null,null,null,null,null,null,"IT",411000]
[null,null,null,null,null,null,null,null,"Apple Lyn",47000]
[null,null,null,null,null,null,null,null,"Bradley Sack",42000]
[null,null,null,null,null,null,null,null,"Bryan Sloane",59000]
[null,null,null,null,null,null,null,null,"John Dumas",44000]
[null,null,null,null,null,null,null,null,"Jonny Chan",50000]
[null,null,null,null,null,null,null,null,"Mai Taylor",45000]
[null,null,null,null,null,null,null,null,"Mark Cox",61000]
[null,null,null,null,null,null,null,null,"Tom Briones",63000]
[null,null,null,null,null,null,null,null,"Adm",362000]
[null,null,null,null,null,null,null,null,"Andrew Gupta",63000]
[null,null,null,null,null,null,null,null,"Caylie Price",57000]
[null,null,null,null,null,null,null,null,"Daniella Ruder",50000]
[null,null,null,null,null,null,null,null,"Jaremy Davis",52000]
[null,null,null,null,null,null,null,null,"Stephanie Miller-Stevenson",57000]
[null,null,null,null,null,null,null,null,"Steven Davidson",41000]
[null,null,null,null,null,null,null,null,"Susan Ricas",42000]
[null,null,null,null,null,null,null,null,"HR",250000]
[null,null,null,null,null,null,null,null,"Lee Nazal",40000]
[null,null,null,null,null,null,null,null,"Mike O'Neil",61000]
[null,null,null,null,null,null,null,null,"Nate Harris",45000]
[null,null,null,null,null,null,null,null,"Richard David",47000]
[null,null,null,null,null,null,null,null,"Vicky James Lilian Samson",57000]
[null,null,null,null,null,null,null,null,"Accounting",211000]
[null,null,null,null,null,null,null,null,"Jake Fann",63000]
[null,null,null,null,null,null,null,null,"Joe Keenan",48000]
[null,null,null,null,null,null,null,null,"Sam Zaborszky",60000]
[null,null,null,null,null,null,null,null,"Scott Jastrow",40000]
[null,null,null,null,null,null,null,null,"Grand Total",2718000]

{"sheet":"Thank you","index":6,"n_rows":24,"n_cols":3,"rows_kept":24,"cut":false,"hidden":false,"merged":[]}
[]
[]
[]
[]
[]
[null,"Thank you for being awesome and doing these assignments."]
[]
[null,"One thing is to read how to do it"]
[null," - another thing is to try it out for yourself."]
[]
[null,"By practicing, you'll learn Excel so much faster."]
[null,"So keep up the good work and effort!"]
[]
[null,"I've created a course about Pivot Tables that will "]
[null,"make you master this awesome tool in less than 1 hour."]
[]
[null,"Check it out at:"]
[null,"http://spreadsheeto.com/pivot-tables-course/"]
[]
[null,"See you around!"]
[]
[null,"Best regards"]
[null,"Kasper Langmann"]
[null,"Co-founder of ","Spreadsheeto"]

{"sheet":"AnswerOptions","index":7,"n_rows":3,"n_cols":2,"rows_kept":3,"cut":false,"hidden":true,"merged":[]}
[1,2]
["Very powerful","Very powerful"]
["Not so much","Not so much"]

{"sheet":"Source data","index":8,"n_rows":53,"n_cols":4,"rows_kept":53,"cut":false,"hidden":true,"merged":[]}
["Full name","Department","Nearest Manager","Salary"]
["Abigail Aalderink","Sales","Elly Kam",45000]
["Sanford Bartolo","Sales","Elly Kam",59000]
["Samuel Bartnick","Sales","Elly Kam",54000]
["John Dumas","IT","Scott Joyce",44000]
["Kristi Hines","Production","Elly Kam",45000]
["Apple Lyn","IT","Scott Joyce",47000]
["Lee Nazal","HR","Scott Joyce",40000]
["Lindsy Kline","Marketing","Elly Kam",59000]
["Vicky James Lilian Samson","HR","Scott Joyce",57000]
["Bradley Sack","IT","Scott Joyce",42000]
["Steven Lamar","Sales","Elly Kam",62000]
["Tom Briones","IT","Scott Joyce",63000]
["Mike O'Neil","HR","Scott Joyce",61000]
["Steven Davidson","Adm","Scott Joyce",41000]
["Nate Harris","HR","Scott Joyce",45000]
["Bryan Sloane","IT","Scott Joyce",59000]
["Chris Borgogni","Sales","Elly Kam",56000]
["David Goods","Sales","Elly Kam",53000]
["Chatsworth Miller-Stevenson","Marketing","Elly Kam",47000]
["Stephanie Miller-Stevenson","Adm","Scott Joyce",57000]
["Mary Oms","Sales","Elly Kam",42000]
["Jaremy Davis","Adm","Scott Joyce",52000]
["Darrel Hobbs","Sales","Elly Kam",58000]
["David Jones","Production","Elly Kam",41000]
["Mitch Gramlow","Production","Elly Kam",56000]
["Valerie Stevens","Sales","Elly Kam",57000]
["Andrew Gupta","Adm","Scott Joyce",63000]
["Sam Zaborszky","Accounting","Scott Joyce",60000]
["Joe Keenan","Accounting","Scott Joyce",48000]
["Susan Ricas","Adm","Scott Joyce",42000]
["Peter Bowland","Production","Elly Kam",63000]
["Jonny Chan","IT","Scott Joyce",50000]
["Richard David","HR","Scott Joyce",47000]
["Matt Baker","Sales","Elly Kam",63000]
["Annie Hunter","Marketing","Elly Kam",55000]
["Jake Prock","Marketing","Elly Kam",51000]
["Daniel Turek","Sales","Elly Kam",40000]
["James Sperman","Sales","Elly Kam",65000]
["Mark Cox","IT","Scott Joyce",61000]
["David Jones","Production","Elly Kam",43000]
["Steven Perry","Production","Elly Kam",55000]
["Scott Jastrow","Accounting","Scott Joyce",40000]
["Lorna Walker","Sales","Elly Kam",56000]
["Mai Taylor","IT","Scott Joyce",45000]
["Bobbi Simon","Marketing","Elly Kam",42000]
["Robert Nole","Production","Elly Kam",56000]
["Irene Pawell","Sales","Elly Kam",61000]
["Sarah Smith","Production","Elly Kam",47000]
["Jake Fann","Accounting","Scott Joyce",63000]
["Daniella Ruder","Adm","Scott Joyce",50000]
["Caylie Price","Adm","Scott Joyce",57000]
["Emily Bodenham","Production","Elly Kam",53000]