Excel Excercises
Order History Order History Data - Jan 1 - May 30, 2020Data removed as invalid (if possible, fix data and replace) Step 1: Cleanse data for .. (save this data on the right for later inspection and/or correciton)(insert data here with errors highlighted) a. Invalid Invoice # (must start with A followed by 7 digits) b. Invalid Part # (only the 4 listed in Reference Table are valid) c. Other invalid data (e.g. text in numerical fields, dates out of range) c. Zeros -- replace with blanks Step 2: Compute the Days Late (positive values) or Days early (negative values) Step 3: Remove outliers (save data for individual analysis of cause) Step 4: Insert the Item name of each order Step 5: Filter data by Part # showing only data for "12-23451"Outliers (for later analysis of cause) Step 6: Sort displayed data by "Days Late" (highest to lowest)(insert data here with errors highlighted) Order dateInvoice #Part #QtyPromised delivery dateActual Delivery DateDays Late (early)Item Name 01/02/20A245438512-23451801/28/201/28/2020 01/02/20A2454386102348301/07/201/7/2020 01/02/20A2454387A34321501/12/201/11/2020 01/02/20A245438810-432201/08/201/8/2020 01/02/20A2454389A34321801/12/201/11/2020 01/04/20A245439013-1241201/30/202/6/2020 01/07/20A245439112-23451602/02/202/9/2020 01/08/20A245439210-432401/14/201/14/2020 01/08/20A2454393A34321201/18/201/18/2020 01/10/20A245439412-23451302/05/202/14/2020 01/12/20B245439102348101/17/201/17/2020 01/13/20A245439610-432T01/19/201/19/2020 01/14/20A245439712-23451902/09/202/16/2020 01/17/20A2454398A34321201/27/151/26/2015 01/17/20A245439912-23451102/12/202/6/2020 01/17/20A245440010-432001/23/201/23/2020 01/18/20A2454401102341301/23/201/23/2020 01/20/20A245440212-23451402/15/202/5/2020 01/22/20A245440310-432501/28/201/28/2020 01/23/20A245440412-23451202/18/202/9/2020 01/24/20A2454405A34321202/03/202/3/2020 01/25/205440610-432101/31/201/31/2020 01/28/20A2454407A34321902/07/202/7/2020 01/31/20A245440812-23451102/26/208/19/2020 01/31/20A245440910-432402/06/202/6/2020 02/01/20A2454410102344802/06/202/6/2020 02/02/20A2454411A34321502/12/202/11/2020 02/03/20A245441210-432002/09/202/9/2020 02/04/20A245441312-23451103/01/203/10/2020 02/05/20A2454414A34321502/15/202/15/2020 02/05/20A245441512-23451403/02/202/21/2020 02/08/20A245441610234102/13/202/13/2020 02/09/20A245441710-432202/15/205/15/2020 02/10/20A2454418A34321702/20/20Not received 02/10/20A245441912-23451103/07/203/11/2020 02/12/20A245442010-432402/18/202/18/2020 02/15/20A2454421102345702/20/202/20/2020 02/15/20A2454422A34321102/25/202/24/2020 02/15/20A245442310-432502/21/202/21/2020 02/17/20A245442412-23451303/14/203/8/2020 02/18/20A2454425102346702/23/202/23/2020 02/20/20A245442610-432502/26/202/26/2020 02/21/20A245442712-23451303/18/203/10/2020 02/23/20A2454428A343211003/04/203/3/2020 02/23/20A245442910-432403/04/203/3/2020 02/28/20A2454430A34321403/09/203/9/2020 02/28/20A245443112-234511003/25/204/1/2020 03/01/20A245443210-432103/06/203/6/2020 03/01/20A2454433102343103/06/203/6/2020 03/06/20A2454434A34321403/16/203/16/2020 03/06/20A245443510-432103/12/203/12/2020 03/08/20A245443612-23451904/03/204/9/2020 03/09/20A2454437A34321203/19/203/18/2020 03/09/20A245443810-432303/15/203/15/2020 03/12/20A2454439102346903/17/203/17/2020 03/13/20A245444010-432403/19/203/19/2020 03/14/20A245444112-23451604/09/204/4/2020 03/15/20A2454442A34321403/25/203/25/2020 03/18/20A245444312-23451404/13/204/9/2020 03/19/20A245444410-432303/25/203/25/2020 03/20/20A2454445A343211003/30/203/29/2020 03/21/20A245444612-23451704/16/204/7/2020 03/21/20A245444710-432503/27/203/27/2020 03/23/20A2454448A34321904/02/204/2/2020 03/23/20A245444910-432503/29/203/30/2020 03/24/20A2454450102346703/29/203/29/2020 03/25/20A245445110-432503/31/204/1/2020 03/27/20A245445210-432504/02/204/2/2020 03/28/20A245445312-23451104/23/204/20/2020 03/28/20A245445410-432204/03/204/4/2020 03/29/20A2454455A34321704/08/204/8/2020 03/31/20A2454456102349604/05/204/5/2020 03/31/20A2454457A34321304/10/204/9/2020 04/02/20A245445810-432504/08/204/8/2020 04/04/20A2454459A34321204/14/204/14/2020 04/04/20A245446010-432404/10/204/10/2020 04/05/20A2454461A34321404/15/204/15/2020 04/05/20A245446210-432304/11/204/11/2020 04/06/20A245446312-23451105/02/205/5/2020 04/07/20A245446410-432404/13/204/13/2020 04/11/20A2454465102344104/16/204/16/2020 04/11/20A2454466A34321304/21/204/21/2020 04/11/20A245446712-23451105/07/205/8/2020 04/13/20A2454468A34321804/23/204/23/2020 04/13/20A245446910-432304/19/204/19/2020 04/17/20A2454470102346104/22/204/22/2020 04/19/20A245447112-23451705/15/205/15/2020 04/22/20A2454472A34321805/02/205/1/2020 04/23/20A2454473102346404/28/204/28/2020 04/24/20A2454474A343211005/04/205/3/2020 04/27/20A245447512-234511005/23/205/13/2020 04/28/20A2454476102345105/03/205/4/2020 04/29/20A245447712-23451405/25/206/2/2020 05/01/20A2454478A34321305/11/205/11/2020 05/02/20A245447912-23451405/28/206/4/2020 05/04/20A2454480A34321705/14/205/13/2020 05/10/20A245448112-23451706/05/206/7/2020 05/12/20A2454482102344505/17/205/17/2020 05/15/20A2454483102347805/20/205/21/2020 05/18/20A245448412-23451206/13/206/21/2020 05/24/20A2454485102346805/29/205/29/2020 05/24/20A245448612-23451306/19/206/24/2020 05/31/20A245448712-234511006/26/206/17/2020 Reference Table Part NumbersItem NameSupplierUnit Price 10234Screw, #8x12, T34 StainlessAcme fittings$ 0.02 10-432Display, Premium, 8" touchSamsung$ 283.00 12-23451Seat, driver, power, leather, blackNorthwest$ 194.00 A34321Bolt, 1/4", T34 StainlessAcme fittings$ 0.03 Raw Data Student NameGraduation YearGPAMajorRe-coded GPA 2019259BehSciLOInstructions: 2019340GeoSci1) Re-code the GPA column in to the appropriate data format (e.g. on a 4.00 scale; ex: 3.52). 2019213MSS2) Clean any bad data. 2019317Management3) Create a Pivot Table using the entire data table into a new worksheet. 2019323SysEngr4) In that Pivot Table, find the average GPA for each Major (e.g. Major in ROWS). 2019283Management5) Add a comparison between the Class of 2019 and the Class of 2020 (e.g. add Graduation Year in COLUMNS). 2019355AeroEngr 2019356Economics 2019314AeroEngr 2019264Management 2019No Major 2019243Management 2019334BehSci 2019385Physics 2019250PolSci 2019223BehSci 2019345CivEngr 2019320SysEngr 2019248BehSci 2019262SysEngr 2019268Meteor 2019251CompSciCW 2019285AeroEngr 2019351AeroEngr 2019323BehSci 2019331BehSci 2019348Biology 2019260CivEngr 2019334CompSciCW 2019267PolSci 2019No Major 2019264Management 2019347AstroEngr 2019284ElEngr 2019224Management 2019320MatChem 2019337HistoryMil 2019372Biology 2019252Biology 2019374CivEngr 2019258Economics 2019297CompEngr 2019238Economics 2019326MechEngr 2019258CivEngr 2019390BioChem 2019No Major 2019331CivEngr 2019293SysEngr 2019No Major 2019332BioChem 2019328CompEngr 2019298SysEngr 2019375BehSci 2019233Management 2019333CompEngr 2019297Chemistry 2019324BehSci 2019325SysEngr 2019302Management 2019341CompSci 2019304Economics 2019282SysEngr 2019240Management 2019313ElEngr 2019270English 2019293CivEngr 2019259Biology 2019278Economics 2019327MSS 2019No Major 2019304Management 2019369AeroEngr 2019351CivEngr 2019260BehSciLO 2019332BehSci 2019370ElEngr 2019321SysEngr 2019256Management 2019381PolSci 2019283HistoryMil 2019226CivEngr 2019303Biology 2019305OpsRsch 2019317Chemistry 2019312Management 2019297Management 2019333Economics 2019339Biology 2019259Biology 2019249SysEngr 2019281Economics 2019265SysEngr 2019385Management 2019264OpsRsch 2019287GeoSci 2019368MSS 2019237Management 2019313PolSci 2019262Management 2019304Management 2019299OpsRsch 2019295Economics 2019334CompEngr 2019280BehSci 2019309SysEngr 2019237Management 2019332LegalStu 2019350Economics 2019286Management 2019302AeroEngr 2019220GeoSci 2019316BehSci 2019269Management 2019227Management 2019292Management 2019221Management 2019308Management 2019256CompSciCW 2019280AeroEngr 2019297LegalStu 2019315LegalStu 2019341Economics 2019268Biology 2019247Management 2019373AeroEngr 2019297Economics 2019304Chemistry 2019333PolSci 2019304ElEngr 2019243SpaceOps 2019219Management 2019364SysEngr 2019312BioChem 2019223Management 2019321AeroEngr 2019259Management 2019260Economics 2019242Management 2019312PolSci 2019273Economics 2019282Management 2019284Biology 2019273English 2019363BehSci 2019232PolSci 2019290Biology 2019339Biology 2019333SysEngr 2019338AeroEngr 2019252Management 2019274Biology 2019252CivEngr 2019375GeoSci 2019318CompEngr 2019330Physics 2019335AeroEngr 2019214CompSci 2019No Major 2019293HistoryMil 2019273PolSci 2019338LegalStu 2019245Management 2019245Management 2019365MechEngr 2019227Management 2019264Management 2019332CivEngr 2019296Management 2019222SocSci 2019352MechEngr 2019209SocSci 2019341Biology 2019343PolSci 2019377MechEngr 2019288Management 2019300CompSci 2019278Management 2019268CivEngr 2019288Management 2019314Management 2019299CompSci 2019337Biology 2019268SysEngr 2019323PolSci 2019No Major 2019298BioChem 2019234Management 2019340AeroEngr 2019283Management 2019245Economics 2019322CivEngr 2019388GeoSci 2019249SocSci 2019306BehSci 2019326Management 2019304SysEngr 2019313GeoSci 2019310SysEngr 2019279CivEngr 2019284Management 2019292AeroEngr 2019297Meteor 2019211SocSci 2019349AeroEngr 2019378HistoryMil 2019293Management 2019327PolSci 2019266SpaceOps 2019277HistoryInt 2019246SysEngr 2019297Management 2019332FAS-PolSci 2019249BehSci 2019328CivEngr 2019249CivEngr 2019337MathApp 2019262BehSci 2019295SysEngr 2019274Biology 2019328Biology 2019240Management 2019300English 2019229Management 2019353AstroEngr 2019363Biology 2019296BehSciLO 2019232MSS 2019266Management 2019241AeroEngr 2019239Management 2019246Biology 2019314AstroEngr 2019238Management 2019217SysEngr 2019249SysEngr 2019285CivEngr 2019271CivEngr 2019305English 2019320PolSci 2019292Management 2019232Management 2019323Management 2019311Management 2019396AstroEngr 2019231SysEngr 2019264PolSci 2019365Math 2019268SysEngr 2019318OpsRsch 2019331Biology 2019293PolSci 2019268Management 2019368BehSci 2019323SysEngr 2019394Economics 2019330HistoryMil 2019285MechEngr 2019340Biology 2019238Management 2019213Management 2019283BehSci 2019380AeroEngr 2019255Management 2019367AeroEngr 2019307Economics 2019230Management 2019304BehSci 2019265BasSci 2019229Management 2019252HistoryMil 2019380AstroEngr 2019272Management 2019276Biology 2019No Major 2019241Management 2019338AeroEngr 2019238Management 2019330SysEngr 2019291HistoryInt 2019289Economics 2019363AeroEngr 2019362AeroEngr 2019283Biology 2019317SysEngr 2019384PolSci 2019393SysEngr 2019300GeoSci 2019273English 2019No Major 2019281Management 2019395Biology 2019329PolSci 2019256BehSci 2019297EnvEngr 2019247LegalStu 2019371LegalStu 2019278BasSci 2019351FAS-Geo 2019256SysEngr 2019No Major 2019No Major 2019289BehSci 2019245Management 2019324CompEngr 2019291AeroEngr 2019335Management 2019233SysEngr 2019320BehSci 2019212BachSci 2019343FAS-MSS 2019387AstroEngr 2019251GeoSci 2019253GeoSci 2019No Major 2019226CivEngr 2019322SysEngr 2019272Management 2019240Management 2019241Management 2019337OpsRsch 2019331AeroEngr 2019209SocSci 2019345Biology 2019299English 2019286Economics 2019264LegalStu 2019260Economics 2019246Management 2019365OpsRsch 2019225SocSci 2019308Management 2019278FAS-PolSci 2019330BehSciLO 2019230SysEngr 2019290Management 2019389MechEngr 2019305CivEngr 2019275Management 2019333FAS-PolSci 2019352OpsRsch 2019341Biology 2019309SysEngr 2019311Biology 2019397AstroEngr 2019289Physics 2019320AeroEngr 2019363SysEngr 2019258Biology 2019266CivEngr 2019279Meteor 2019261FAS-PolSci 2019378Meteor 2019315Biology 2019379AstroEngr 2019324SysEngr 2019293SysEngr 2019386BehSci 2019380SysEngr 2019264CivEngr 2019289HistoryMil 2019252CompSciCW 2019337AeroEngr 2019257AeroEngr 2019278Economics 2019363Economics 2019296SysEngr 2019300AeroEngr 2019378AstroEngr 2019257CompSci 2019329MechEngr 2019277Management 2019380AeroEngr 2019317MechEngr 2019257MSS 2019307Management 2019254Management 2019310PolSci 2019285Management 2019371AstroEngr 2019223SocSci 2019265PolSci 2019241Biology 2019285LegalStu 2019326AeroEngr 2019271OpsRsch 2019261PolSci 2019273MechEngr 2019273Management 2019364Biology 2019216SysEngr 2019344OpsRsch 2019263BehSciHF 2019209MSS 2019308MSS 2019247BehSciHF 2019283BehSci 2019246SysEngr 2019268CompSci 2019306BehSciHF 2019283SysEngr 2019222Management 2019388Management 2020240Management 2020237Management 2020316AstroEngr 2020268BehSci 2020310CivEngr 2020287Biology 2020244Management 2020253PolSci 2020331Economics 2020319AeroEngr 2020227FAS-Geo 2020No Major 2020245Management 2020322MathApp 2020281Management 2020218BachSci 2020358MathApp 2020362Economics 2020294Management 2020223Management 2020377PolSci 2020381FAS-Hist 2020304BasSci 2020310Management 2020376MechEngr 2020268FAS-PolSci 2020267BasSci 2020273Management 2020365CompSci 2020380Management 2020370MatChem 2020256BehSci 2020234Management 2020246Economics 2020367AeroEngr 2020296Physics 2020319Economics 2020311OpsRsch 2020341AeroEngr 2020261Management 2020273CivEngr 2020No Major 2020322MechEngr 2020306SysEngr 2020266SysEngr 2020362AeroEngr 2020304Chemistry 2020No Major 2020338BasSci 2020275BehSci 2020317BehSci 2020344ElEngr 2020325ElEngr 2020396AeroEngr 2020351Management 2020257FAS-PolSci 2020312SysEngr 2020389OpsRsch 2020321GeoSci 2020210Management 2020295CivEngr 2020236Management 2020309Management 2020342MechEngr 2020336Management 2020253Management 2020307BehSci 2020259Management 2020364CompSciCW 2020301SysEngr 2020363MechEngr 2020243English 2020245LegalStu 2020286HistoryMil 2020315GeoSci 2020300History 2020310Economics 2020351MSS 2020311MechEngr 2020390Management 2020288AeroEngr 2020275Biology 2020354LegalStu 2020351AeroEngr 2020273CompSciCW 2020293CompSciCW 2020241Management 2020303Biology 2020269Biology 2020336Economics 2020282Management 2020316English 2020307LegalStu 2020290SysEngr 2020282SysEngr 2020240Management 2020363SysEngr 2020247Economics 2020333SysEngr 2020303English 2020294Management 2020365CompSciCW 2020264FAS-Geo 2020270GeoSci 2020228BehSci 2020313AeroEngr 2020246SysEngr 2020223Management 2020225FAS-Hist 2020296MechEngr 2020383ElEngr 2020315CompSciCW 2020309SysEngr 2020321Biology 2020249Management 2020325Economics 2020336Biology 2020342FAS-Hist 2020303Management 2020274AeroEngr 2020295Management 2020335AeroEngr 2020284CivEngr 2020251Management 2020309BehSci 2020233BehSciHF 2020366GeoSci 2020278SysEngr 2020289GeoSci 2020306Biology 2020308Economics 2020244SysEngr 2020249PolSci 2020255Management 2020226Management 2020297Management 2020305Management 2020368Economics 2020279MatChem 2020373AeroEngr 2020244SysEngr 2020299BehSci 2020321ElEngr 2020344AeroEngr 2020315EnvEngr 2020287Management 2020285AeroEngr 2020285Economics 2020338HistoryInt 2020271Management 2020237Management 2020308Physics 2020239SysEngr 2020217Management 2020300ElEngr 2020256CompEngr 2020337Management 2020223SysEngr 2020223Management 2020315CompEngr 2020300HistoryInt 2020309Management 2020289MechEngr 2020309Biology 2020324Physics 2020324Economics 2020305BehSciHF 2020251Economics 2020272MechEngr 2020288AeroEngr 2020279AstroEngr 2020267Management 2020231Management 2020310AeroEngr 2020317BehSci 2020309GenEngr 2020271BehSci 2020301AeroEngr 2020254CompEngr 2020311HistoryMil 2020246SocSci 2020318Management 2020266BehSci 2020341Biology 2020276BehSci 2020324Management 2020269Economics 2020243Management 2020357BehSci 2020375Economics 2020224EnvEngr 2020304Biology 2020258Management 2020297CivEngr 2020304Management 2020302Management 2020223BehSciHF 2020300Management 2020382CompEngr 2020320CompSciCW 2020245OpsRsch 2020242Management 2020286SysEngr 2020356SysEngr 2020334Biology 2020269LegalStu 2020249BehSci 2020241Economics 2020No Major 2020363BehSciHF 2020232Management 2020293Biology 2020221MSS 2020355Economics 2020255Humanities 2020334SysEngr 2020349Biology 2020259BehSci 2020239AstroEngr 2020224Management 2020301Economics 2020275PolSci 2020335Management 2020243PolSci 2020365CivEngr 2020282Economics 2020227Management 2020326LegalStu 2020260GeoSci 2020396Biology 2020249LegalStu 2020333GeoSci 2020298OpsRsch 2020338SysEngr 2020341Economics 2020254GeoSci 2020No Major 2020301Management 2020205SocSci 2020313MechEngr 2020368AeroEngr 2020322LegalStu 2020240GeoSci 2020385AstroEngr 2020268HistoryInt 2020281PolSci 2020213SocSci 2020308Biology 2020331CivEngr 2020249CompSci 2020244Management 2020356BioChem 2020366OpsRsch 2020285CompSci 2020374FAS-Geo 2020373Management 2020351SysEngr 2020280MechEngr 2020327AeroEngr 2020212SysEngr 2020337SysEngr 2020281Management 2020350MechEngr 2020278AstroEngr 2020287Management 2020342Biology 2020307MechEngr 2020267Biology 2020244FAS-PolSci 2020283LegalStu 2020262LegalStu 2020293Management 2020346Management 2020360Biology 2020321BehSci 2020230Management 2020326Management 2020340Humanities 2020299PolSci 2020375CivEngr 2020334OpsRsch 2020306MechEngr 2020272Management 2020314Physics 2020298AstroEngr 2020306Biology 2020256Management 2020288CivEngr 2020331PolSci 2020299Management 2020340OpsRsch 2020279PolSci 2020348Biology 2020343BehSci 2020327AstroEngr 2020289LegalStu 2020317Management 2020314Management 2020299CompEngr 2020277CivEngr 2020288Economics 2020275English 2020241BasSci 2020333Economics 2020377AstroEngr 2020306SysEngr 2020371GeoSci 2020258SysEngr 2020247SysEngr 2020355SysEngr 2020228Economics 2020359LegalStu 2020250SocSci 2020267BasSci 2020306AeroEngr 2020262HistoryInt 2020284BehSci 2020241BioChem 2020253BasSci 2020No Major 2020256SysEngr 2020350CivEngr 2020219Management 2020270SysEngr 2020258Management 2020326Biology 2020313CivEngr 2020300English 2020353Management 2020370CivEngr 2020281CompSciCW 2020272Biology 2020269FAS-PolSci 2020327CivEngr 2020281English 2020359CivEngr 2020273Physics 2020277SysEngr 2020339MechEngr 2020356Economics 2020333AstroEngr 2020263BehSciLO 2020239Management 2020263Management 2020303LegalStu 2020339BehSci 2020315CompSciCW 2020254Management 2020351CivEngr 2020292Management 2020255SysEngr 2020No Major 2020314SysEngr 2020302SysEngr 2020238Management 2020324PolSci 2020281Physics 2020336MechEngr 2020287Management 2020331GeoSci 2020312ElEngr 2020278PolSci 2020345GeoSci 2020302Biology 2020270Management 2020282Economics 2020238Management 2020339English 2020311Biology 2020354BehSci 2020252CivEngr 2020355PolSci 2020266CompEngr 2020295PolSci 2020261Physics 2020261Management 2020377Management 2020370MSS 2020238HistoryInt 2020282Economics 2020221SysEngr 2020302Management 2020385English 2020374Chemistry 2020357GeoSci 2020340Biology 2020259ElEngr 2020339Management 2020307Management 2020234SysEngr 2020312Management 2020263AeroEngr 2020273GeoSci 2020293Biology 2020293Management 2020No Major 2020275BehSci 2020230BehSci 2020299Management 2020302MechEngr 2020309Physics 2020217SysEngr 2020245SysEngr 2020275ElEngr 2020258BehSci 2020258Management 2020365AstroEngr 2020326OpsRsch 2020274AeroEngr 2020315BehSci 2020359MatChem 2020395Biology 2020364OpsRsch 2020321Biology 2020301Management 2020241PolSci 2020268BehSciHF 2020293Economics 2020319MechEngr 2020272BehSci 2020305CivEngr 2020354OpsRsch 2020382Meteor 2020231Management 2020249Management 2020323AeroEngr 2020226Management 2020323AeroEngr 2020367ElEngr 2020239Management 2020302EnvEngr 2020270FAS-PolSci 2020303SysEngr 2020258CompSciCW 2020248Economics 2020287PolSci 2020299GeoSci 2020379PolSci 2020286MechEngr 2020301Management 2020356MechEngr 2020377Physics 2020301LegalStu 2020270CompSciCW 2020300GeoSci 2020293Management 2020271AeroEngr 2020260CivEngr 2020307Economics 2020393AeroEngr 2020259CompSciCW 2020375Economics 2020237Biology 2020269BehSci 2020257Management 2020257CivEngr 2020288FAS-Geo 2020364MatChem 2020304AstroEngr 2020337GeoSci 2020285MathApp 2020298Chemistry 2020335SysEngr 2020374SysEngr 2020305Economics 2020265SocSci 2020332MechEngr 2020255Management 2020267LegalStu 2020No Major 2020290Economics 2020333Biology 2020269Management 2020292Management 2020307Economics 2020252PolSci 2020319Economics 2020289Economics 2020234Management 2020294Management 2020267Management 2020347MechEngr 2020393ElEngr Pivot Table Instructions Once you have created your cleaned “Re-coded GPAs”, to get started with the new Pivot Table, highlight the columns of data that you need. Your data must have Headers in the first row and all columns must be side-by-side. Then, under the Insert Tab, click on “Pivot Table”. Use the default options to create a new Pivot Table in a new worksheet. Your new blank Pivot Table will appear on the left, with the options on the right. To show the table, first select the field that you are interested in – for this activity we are interested in the various Majors at the college. These majors now appears in rows – sorted alphabetically with no duplicates. Now, select what data that you want. In this case, you want to know the average GPA of all graduates within every major, so drag the “Re-Coded GPA” field down to the lower-right under “Sum of Values”. (Don’t use the original GPA field as that is a text field and not in the normal 4 point oh scale.) You’ll see that the Excel default for values is “Count”. This is useful to know how many of each category that you have in the data. You can now change the “Count” to “Average” for the GPAs by clicking on the “Count GPA” then selecting “Value Filed Settings”. Use the drop-down to change “Count” to “Average”. Notice the other options there too. Consider reformatting your new column of Average GPAs to the normal settings of only 2-digits. Finally, drag the GRADUATION YEAR into the COLUMN block and you'll have that data separated plus the original Grand Total of all Years. That’s it! Once you have a Pivot Table, you will need to REFRESH the table if your data ever changes – it does NOT automatically refresh like formulas do in Excel. You can always update your table, add new columns or row, or even automatically filter fields to quickly see the new results. If you want static results, say for a report, consider copy-and-pasting your Pivot Table as Text-Only into a new location. When you're ready to compare GPAs from one year to another, here's how to do it! On your Pivot Table that you just created, click anywhere on the table to bring up the right-side menu (if it's not already open). To show comparisons from one Class (e.g. year) to another, just drag the field "Graduation Year" down into the "Columns" block. This will very quickly provide you with a new column for each YEAR in your data, plus the same TOTAL column from before. Now, you can see the changes! Based on your data and desired look, play around with which set-up looks better -- switching columns and rows, maybe. You can also add multiple items in Columns or Rows, but that can get very messy. Check out filtering and sorting too, within the Pivot Table's dropdown menus by your row title and your column title. Sheet1 Here's some example problems to get you comfortable with Basic and Intermediate skills. In the YELLOW highlighted cells, type in your answer! Exercise #DataHINTSANSWERS Basics Formulas:1Add two numbers, 3+2=?55Type =3+2 and then the enter-key5 2Subtract two numbers, 5-2=?33Type =5-2 and then the enter-key3 3Multiply two numbers, 6x3=?1818Type =6*3 and then the enter-key18 4Divide two numbers, 8 divided by