YO19_Excel_Ch06_PS1_Automobiles

1

Start Excel. Download and open the file named Excel_Ch06_PS1_Automobiles.xlsx. Grader has automatically added your last name to the beginning of the filename. Save the file to the location where you are storing your files.

0

2

Converting plain data sets to Excel Tables can provide easy ways to explore the data.

The data on the AutoSample worksheet includes details about vehicles that the company may be interested in buying for resale. On the AutoSample worksheet, insert a table with headers using the data.

8

3

With the table selected, including the headers, create a CarDatabase named range to be used in database functions later.

2

4

Insert six rows above the table to make room to create an advanced filter.

5

5

In order to create an Advanced Filter you must include the column headings to which you will be specifying criteria.
Copy the headers in A7:J7 and paste the headings starting in cell A1.

2

6

Using the Advanced Filter, display only the records with an Automatic transmission type, that are a Midsize vehicle size, with an MSRP of <30000.

12

7

Copy the filtered data, including the headings, and paste a copy on the SelectedVehicles worksheet, starting in cell A1. If necessary, adjust the column widths so that all data are visible.

10

8

Clear the filters applied to the AutoSample 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.

6

9

Complete the DatabaseTotals worksheet using the appropriate database functions using the CarDatabase named range, use the MSRP as the field in your calculations, and only calculate vehicles that meet all the following criteria:
• Vehicle year is newer than 2015
• Transmission type is Automatic
• Highway MPG is greater than 25

20

10

Using the data on the AutoSample worksheet, create a PivotTable on a new worksheet and rename the worksheet, PivotAnalysis.
• The PivotTable should use the Make field as the Rows and MSRP as the Values.
• Change the MSRP calculation so that it calculates the Average of MSRP and format the field as Accounting with 2 decimal places.
• Change Row Labels in cell A3 to Vehicle Make and adjust the column width as necessary so that all data are visible.

17

11

Further modify then PivotTable on the PivotAnalysis worksheet.
• Create a drill-down of the Volkswagen onto a new worksheet and rename the worksheet Volkswagens.
• Insert a Year slicer and position it so that its top-left corner in the cell D2.
• Insert a Vehicle Style slicer and position it to that its top-left corner in cell G2.
• Use the Year slicer to only show vehicles that are 2010 and newer.

10

12

Create a Clustered Bar PivotChart from the PivotTable.
• Apply the Style 10 chart style.
• Apply the Monochromatic, Palette 5 colors to the chart.
• Delete the Total legend from the right-side of the chart.
• Edit the chart title to read Average MSRP.
• Move the chart to its own worksheet with the name AverageMSRPPivotChart.

8

13

Save and close Excel_Ch06_PS1_Automobiles.xlsx. Exit Excel. Submit the file as directed.

0