Answered step by step
Verified Expert Solution
Question
1 Approved Answer
Help me know the step by steps more clearly and how to apply them 1 Start Excel. Download and open the file named Excel _
Help me know the step by steps more clearly and how to apply them Start Excel. Download and open the file named ExcelChAssessmentVideoGameSales.xlsx Grader has automatically added your last name to the beginning of the filename.
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.
Apply the Light Blue, Table Style Medium to the table and with the table selected, including the headers, create a SalesDatabase named range to be used in database functions later.
Add a Total Row to the table to have the sales values in the NorthAmerica, Europe, Japan, Other, and GlobalSales columns summed.
Slicers are an easy way to explore data in a table.
Insert slicers for Platform, Year, and Genre.
Move the Platform slicer so that its topleft corner is just inside the border of cell K Modify its Layout property so that it has columns and resize the slicer so that all options are visible and extra white space is minimal. Apply the White, Slicer Style Other style to the slicer.
Move the Genre slicer so that its topleft corner is just inside cell N Modify its Layout property so that it has columns and resize the slicer so that all options are visible and extra white space is minimal. Apply the White, Slicer Style Other style to the slicer.
Move the Year slicer so that its topleft corner is inside cell K Modify its Layout property so that it has 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 style to the slicer.
Using the slicers, filter the Table to only show records where the Platform is PS 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 A Adjust the column widths as necessary so that all data are visible.
Clear the filters applied to the VideoGameSales data. Copy the headings and paste them onto the DatabaseTotals worksheet, starting in cell A 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.
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 specify the criteria for games on the Wii platform with a release year of newer than
In row specify the criteria for games on the DS platform in the Adventure genre.
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.
Structure the PivotTable on the PivotAnalysis worksheet according to the following:
Filters: Publisher
Rows: Platform and Year
Columns: Genre
Values: Sum of GlobalSales
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 decimal places
Change Row Labels in cell A to Platform and Year
Change Column Labels in cell B to Genre
Apply Light Blue, Pivot Style Light
Adjust the column widths, if necessary.
Using the data on the VideoGameSales worksheet, insert a PivotChart on a new worksheet. Rename the worksheet with the PivotChart and PivotTable, NorthAmericanSalesByPlatform.
Structure the PivotChart on the NorthAmericanSalesByPlatform worksheet according to the following:
Filters: Genre
Axis Categories: Platform
Values: Sum of NorthAmerica
Configure the PivotChart on the NorthAmericanSalesByPlatform worksheet according to the following:
Change the Chart Type to Clustered Bar.
Apply a Style Chart Style
Apply a Layout 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
Using the PivotTable on the NorthAmericanSalesByPlatform worksheet, create a drilldown of the DS sales and rename the worksheet created NorthAmericanSalesDS.
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.
Save and close ExcelChAssessmentVideoGameSales.xlsx Exit Excel. Submit the file as directed.
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started