Samantha Vee, the owner of a local video game store, is interested in how video games sales vary by platform and region. She has acquired a sample data set and needs your help to explore the data.
Grader – Instructions Excel 2022 Project
YO22_Excel_Ch06_Assessment_Video_Game_Sales
Project Description:
Samantha Vee, the owner of a local video game store, is interested in how video games sales vary by platform and region. She has acquired a sample data set and needs your help to explore the data.
Steps to Perform:
Step |
Instructions |
Points Possible |
1 |
Start Excel. Download and open the file named Excel_Ch06_Assessment_VideoGameSales.xlsx. Grader has automatically added your last name to the beginning of the filename. |
0 |
2 |
The data on the VideoGameSales worksheet includes sales (in millions) for several video games on various platforms and from various genres. On the VideoGameSales worksheet, insert a table with headers using the data. |
0.8 |
3 |
Apply the Light Blue, Table Style Medium 27 to the table and with the table selected, including the headers, create a SalesDatabase named range to be used in database functions later. |
1 |
4 |
Add a Total Row to the table to have the sales values in the NorthAmerica, Europe, Japan, Other, and GlobalSales columns summed. |
1 |
5 |
Slicers are an easy way to explore data in a table. Insert slicers for Platform, Year, and Genre. |
1 |
6 |
Move the Platform slicer so that its top-left corner is just inside the border of cell K1. Modify its Layout property so that it has 3 columns and resize the slicer so that all options are visible and extra white space is minimal. Apply the White, Slicer Style Other 2 style to the slicer. |
0.4 |
7 |
Move the Genre slicer so that its top-left corner is just inside cell N1. Modify its Layout property so that it has 2 columns and resize the slicer so that all options are visible and extra white space is minimal. Apply the White, Slicer Style Other 2 style to the slicer. |
0.4 |
8 |
Move the Year slicer so that its top-left corner is inside cell K13. Modify its Layout property so that it has 3 columns and resize the slicer so that all options are visible and extra white space is minimal. Modify the setting so that the caption reads Release Year. Apply the White, Slicer Style Other 2 style to the slicer. |
0.4 |
9 |
Using the slicers, filter the Table to only show records where the Platform is PS3 and DS and the Genre is either Adventure or Action. Select the filtered data, including column headings and the Total row, and paste the data on the SelectData worksheet, starting in cell A1. Adjust the column widths as necessary so that all data are visible. |
1.2 |
10 |
Clear the filters applied to the VideoGameSales data. Copy the headings and paste them onto the DatabaseTotals worksheet, starting in cell A1 to begin to setup and create database functions to further analyze the data. If necessary, adjust the column widths so that all data are visible. |
1 |
11 |
Complete the DatabaseTotals worksheet using the appropriate database functions using the SalesDatabase named range, use GlobalSales as the field in your calculations, and only calculate games that meet the following criteria: In row 2, specify the criteria for games on the Wii platform with a release year of newer than 2006 In row 3, specify the criteria for games on the DS platform in the Adventure genre. |
4 |
12 |
Using the data on the VideoGameSales worksheet, create a PivotTable on a new worksheet and rename the worksheet, PivotAnalysis. Do not add the data to the Data Model. Be sure the Report Layout is in Compact Form. |
1 |
13 |
Structure the PivotTable on the PivotAnalysis worksheet according to the following: Filters: Publisher Rows: Platform and Year Columns: Genre Values: Sum of GlobalSales |
1.2 |
14 |
Configure the PivotTable on the PivotAnalysis worksheet according to the following: Create a custom name for Sum of GlobalSales to be Total GlobalSales (in millions) Format Total GlobalSales as Accounting with 2 decimal places Change Row Labels in cell A4 to Platform and Year Change Column Labels in cell B3 to Genre Apply Light Blue, Pivot Style Light 9 Adjust the column widths, if necessary. |
1.2 |
15 |
Using the data on the VideoGameSales worksheet, insert a PivotChart on a new worksheet. Rename the worksheet with the PivotChart and PivotTable, NorthAmericanSalesByPlatform. |
1 |
16 |
Structure the PivotChart on the NorthAmericanSalesByPlatform worksheet according to the following: Filters: Genre Axis (Categories): Platform Values: Sum of NorthAmerica |
1.2 |
17 |
Configure the PivotChart on the NorthAmericanSalesByPlatform worksheet according to the following: Change the Chart Type to Clustered Bar. Apply a Style 4 Chart Style Apply a Layout 1 Quick Layout Edit the chart title to North American Sales by Platform Delete the Legend Move the PivotChart to a new worksheet with the name SalesByPlatformPivotChart |
1.2 |
18 |
Using the PivotTable on the NorthAmericanSalesByPlatform worksheet, create a drill-down of the DS sales and rename the worksheet created NorthAmericanSalesDS. |
1 |
19 |
Using the PivotChart on the SalesByPlatformPivotChart worksheet, modify the Genre filter to only show data for Action, Adventure, and Fighting genres. If using a Mac, use the PivotTable filter on the NorthAmericanSalesByPlatform worksheet (the source of the PivotChart) to apply the filters. |
1 |
20 |
Save and close Excel_Ch06_Assessment_VideoGameSales.xlsx. Exit Excel. Submit the file as directed. |
0 |
Total Points |
20 |
Created On: 07/24/2023 1 YO22_Excel_Ch06_PS2 – Video Game Sales 1.0
,
VideoGameSales
Name | Platform | Year | Genre | Publisher | NorthAmerica | Europe | Japan | Other | GlobalSales |
Jeopardy! 2nd Edition | PS | 2000 | Misc | Hasbro Interactive | $ 0.16 | $ 0.11 | $ – 0 | $ 0.02 | $ 0.29 |
TrackMania Turbo | PS4 | 2016 | Action | Ubisoft | $ 0.03 | $ 0.24 | $ – 0 | $ 0.05 | $ 0.32 |
Touch Detective: Nameko Shigeru | 3DS | 2013 | Adventure | Success | $ – 0 | $ – 0 | $ 0.07 | $ – 0 | $ 0.07 |
NHL 16 | PS3 | 2015 | Sports | Electronic Arts | $ – 0 | $ 0.03 | $ – 0 | $ 0.01 | $ 0.04 |
Ride | PS4 | 2015 | Racing | Milestone S.r.l. | $ 0.03 | $ 0.13 | $ 0.03 | $ 0.03 | $ 0.22 |
Dragon Zakura DS | DS | 2007 | Misc | Electronic Arts | $ – 0 | $ – 0 | $ 0.01 | $ – 0 | $ 0.01 |
Sherlock Holmes: Crimes & Punishments | PS3 | 2014 | Adventure | Focus Home Interactive | $ 0.03 | $ 0.04 | $ – 0 | $ 0.01 | $ 0.08 |
Ace Attorney Investigations 2 | DS | 2011 | Adventure | Capcom | $ – 0 | $ – 0 | $ 0.26 | $ – 0 | $ 0.26 |
Metal Fight Beyblade | DS | 2009 | Action | Hudson Soft | $ – 0 | $ – 0 | $ 0.09 | $ – 0 | $ 0.09 |
Farmtopia | DS | 2010 | Simulation | 505 Games | $ 0.18 | $ – 0 | $ – 0 | $ 0.01 | $ 0.19 |
Pokemon Ranger | DS | 2006 | Role-Playing | Nintendo | $ 1.29 | $ 0.04 | $ 0.73 | $ 0.12 | $ 2.18 |
BeatMania IIDX 16: Empress + Premium Best | PS2 | 2009 | Simulation | Konami Digital Entertainment | $ – 0 | $ – 0 | $ 0.04 | $ – 0 | $ 0.04 |
Mobile Suit Gundam Seed: Rengou vs. Z.A.F.T. Portable | PSP | 2007 | Shooter | Namco Bandai Games | $ – 0 | $ – 0 | $ 0.12 | $ – 0 | $ 0.12 |
MiniCopter: Adventure Flight | Wii | 2007 | Simulation | 505 Games | $ 0.04 | $ – 0 | $ – 0 | $ – 0 | $ 0.05 |
Super Speed Machines | DS | 2009 | Racing | Midas Interactive Entertainment | $ 0.02 | $ – 0 | $ – 0 | $ – 0 | $ 0.02 |
Knockout Kings 2001 | PS | 2000 | Fighting | Electronic Arts | $ 0.23 | $ 0.15 | $ – 0 | $ 0.03 | $ 0.41 |
Clock Zero: Shuuen no Ichibyou – ExTime | PSV | 2015 | Action | Idea Factory | $ – 0 | $ – 0 | $ 0.02 | $ – 0 | $ 0.02 |
Walt Disney Pictures Presents: The Wild | GBA | 2006 | Platform | Disney Interactive Studios | $ 0.09 | $ 0.03 | $ – 0 | $ – 0 | $ 0.13 |
Marker Man Adventures | DS | 2009 | Platform | Majesco Entertainment | $ 0.02 | $ – 0 | $ – 0 | $ – 0 | $ 0.03 |
Tactics Layer: Ritina Guard Senki | DS | 2009 | Strategy | D3Publisher | $ – 0 | $ – 0 | $ 0.01 | $ – 0 | $ 0.01 |
From TV Animation One Piece: Yume no Lufy Kaizokudan Tanjou! | GB | 2001 | Role-Playing | Banpresto | $ – 0 | $ – 0 | $ 0.39 | $ – 0 | $ 0.39 |
Pac-Man Player's Choice Bundle | GC | 2003 | Misc | Namco Bandai Games | $ 0.48 | $ 0.12 | $ – 0 | $ 0.02 | $ 0.62 |
Burnout Paradise | X360 | 2008 | Racing | Electronic Arts | $ 0.63 | $ 0.58 | $ 0.01 | $ 0.15 | $ 1.36 |
Prison Break: The Conspiracy | PS3 | 2010 | Action | Deep Silver | $ 0.05 | $ 0.16 | $ – 0 | $ 0.05 | $ 0.26 |
Mario Golf: Toadstool Tour | GC | 2003 | Sports | Nintendo | $ 1.09 | $ 0.15 | $ 0.25 | $ 0.04 | $ 1.53 |
Poker for Dummies | PC | 2008 | Misc | Electronic Arts | $ – 0 | $ 0.01 | $ – 0 | $ – 0 | $ 0.01 |
Resident Evil 5 | PC | 2009 | Action | Capcom | $ 0.01 | $ 0.04 | $ – 0 | $ 0.01 | $ 0.07 |
Far Cry Instincts | XB | 2005 | Shooter | Ubisoft | $ 0.32 | $ 0.24 | $ – 0 | $ 0.02 | $ 0.57 |
Grand Theft Auto: Mission Pack #1, London 1969 | PS | 1998 | Adventure | Take-Two Interactive | $ 0.06 | $ 0.04 | $ – 0 | $ 0.01 | $ 0.11 |
SimCity | SNES | 1991 | Simulation | Nintendo | $ 0.93 | $ 0.27 | $ 0.75 | $ 0.04 | $ 1.98 |
Data from “Video Game Sales” compiled by GregorySmith, https://www.kaggle.com/gregorut/videogamesales |
SelectData
DatabaseTotals
Field |
Sum |
Average |
Count |
Max |
Min |
Documentation
Video Game Sales | |||
Create Date | By Whom | Description | Workbook Name |
5/12/22 | Samantha Vee | alzoabi_Excel_Ch06_Assessment_VideoGameSales.xlsx | |
Mod. Date | By Whom | Mod. Description | Last Version Backup Name |
Create Date | Sheet Name | Creator | Purpose |
5/12/22 | VideoGameSales | Samantha Vee | Sample data of global video game sales |
5/12/22 | SelectData | Samantha Vee | Filtered data for focused analysis |
5/12/22 | DatabaseTotals | Samantha Vee | Exploring data with database functions |
5/12/22 | PivotAnalysis | Samantha Vee | PivotTable analysis to explore Global Sales |
5/12/22 | NorthAmericanSalesByPlatform | Samantha Vee | PivotT |