Exp19_Excel_Ch10_Cap_Auto_Sales| Exp19 Excel Ch10 Cap Auto Sales
#Start Excel. Download and open the file named Exp19_Excel_Ch10_Cap-AutoSales.xlsx. Grader has automatically added your last name to the beginning of the filename.
#Use Get & Transform (Power Query) to import the file e10c1Agents.csv.
Hint: Get Data is located on the Data tab in the Get & Transform Data group.
Rename the worksheet Agents.
Hint: Double click the worksheet tab to edit the sheet name.
#Use Get & Transform (Power Query) to import the Vehicles table from the file e10c1Vehicles.accdb.
Hint: Get Data is located on the Data tab in the Get & Transform Data group.
Rename the worksheet Sales.
Hint: Double click the worksheet tab to edit the sheet name.
#Load the file e10c1Customer_Leads.txt in the Power Query Editor. Use the Power Query Editor to ensure the first row is used as Headers. Close and load the table.
Hint: To open the data in the Power Query Editor click Edit on the Import window.
Rename the worksheet Customer_Leads.
Hint: Double click the worksheet tab to edit the sheet name.
Add the Vehicles, e10c1Agents, and e10c1Customer_Leads tables to the data model.
Hint: Manage Data Model is located on the Data tab in the Data Tools group.
#Define the following relationship:
Table:
e10c1Agents
Field:
AgentID
Table:
Vehicles
Field:
AgentID
Hint: Relationships is located on the Data tab in the Data Tools group.
#Use Power Pivot to create a PivotTable on Sheet1 starting in cell B3. Add the Last field from the e10c1Agents table to the Rows area and Price from the Vehicles table to the Values area.
Hint: Power Pivot is an Excel add-in that must be loaded. Power Pivot can be loaded by accessing COM add-ins in Excel options. Once loaded, the Power Pivot tab is located on the Office ribbon.
Format the range C4:C20 with Accounting Number Format.
Hint: Accounting Number Format is located on the Home tab in the Number group.
Insert a Clustered Column Pivot Chart. Position the chart so the upper left corner is in the center of cell D3.
Hint: #PivotChart is located on the Analyze tab in the Tools group.
Add the Chart Title Sales.
Hint: Chart elements can be added or edited on the Design tab in the Chart Layouts group.
Rename the worksheet SalesAnalysis.
Hint: Double-click the worksheet tab to edit the sheet name.