Excel Project: Sales Dashboard (Employee Tracker)
Summary: We are going to be creating a “Dashboard” in Excel, which is a visual representation of data that can be manipulated to help manager’s track employee attainment to sales goals. This Dashboard will access an already existing data table and will utilize a PivotTable and Line Chart to summarize and filer the data.
Download: Excel #3 Start Sheet
Step 1: Start by downloading the file “Excel Assignment #3 – Data” this should feature a single tab worksheet with 8 columns and 3457 rows (THAT’S 27,656 CELLS!!!)
Note: It’s important when working with such a large table to learn a couple of keyboard shortcuts. All shortcuts starting with “Ctrl” can be translated to “Command” for Mac users)
Step 2: Click anywhere in a table and use the shortcut Ctrl+A to select every cell in the range. Now format the range as a table (use either a “Light” or “Medium” default Table Style). The Default Name for the Table is “Table1” which is displayed in the Name Box next to the Formula Bar.
Hint: If it is not “Table1” make note of the Table Name and interchange that value where necessary. Also, make sure the data for “Period” is formatted in the Date format “Mar-12”. The data entries should appear as “Jan-08, Sep-09, etc)
Shortcut Tips: The shortcut Ctrl+Shft+Arrow Key will select everything in that direction to the end of the table
Another example, select the same cell (A2) and use the command Ctrl+Shft+Right; you will now have selected cells A2:H2
Finally you can combine these shortcuts to make a large selection. Again select Cell A2. Use the shortcut Ctrl+Shft+Right to select A2:H2. Now use the shortcut Ctrl+Shft+Down to select cells A2 through H3457. You have just selected all the data excluding the header row (Compare that to dragging the mouse to make the same selection)
Step 3: Using the Ctrl+A shortcut (or by typing “Table1” in the name box) select all the data in the table. Insert a PivotTable into Cell J1 (In the Create PivotTable Window, in the first selection mark “Select a Table or Range” and enter “Table1” for Table/Range. In the second selection mark “Existing Worksheet” and select Cell J1 for location [Option: you may also type “Data!$J$1”]
A PivotTable Fields pane should open (if not you can open it by Clicking on the pivot table, going to the PivotTable tab – sub tab Analyze and clicking the “Field List” button on the far Right of the Ribbon).
Organize your PivotTable Fields as following (Be sure to remove any extra fields that are not listed below):
Filters: Region then Customer Type
Columns: Sales Person – Name
Rows: Year then Period
Values: Sum of Sales Amount
(Each Item can be dragged and dropped but make sure to have the first category listed on top)
Warning: It may try to put all options clicked into ‘Values’; only Sum of Sales Amount should be in the 'Values'
On the Analyze sub-tab (Options for Mac users) on the left side of the Ribbon, change the PivotTable Name to “PivotTable1”
Make sure the Period data is in the format “Mar-12” under the Category “Date”
Step 4: Insert a new sheet and rename it “Dashboard”. We will now be creating another PivotTable, this one to represent our Sales Data. Our new table will be able to be filtered by Customer Type and by Region. This will create a table with Product and Salesperson Rows and Quarter and Yearly Data in the Columns. We are interested in Yearly Subtotals and Individual Product Subtotals, but because this is to represent individual sales goals attainment, we do not need to provide sales grand totals.
In Cell A1 insert a PivotTable using “Table1” for the data. (Mac users: Return to Sheet1, select anywhere on data on the left, Command+A, then select 'Summarize with PivotTable', leave the top alone with Table1, on the bottom change from 'New Worksheet' into 'Existing Worksheet' then click in the 'Location:' then click 'Dashboard' tab and 'A1' within that worksheet. The final should look like: Dashboard!$A$1
Organize your PivotTable Fields as following:
Filters: Customer Type and Region
Columns: Year and Qtr
Rows: Product and Sales Person – Name
Values: Sum of Sales Amount
(In the top right corner unselect the “+/- Buttons” (Mac users: it is 'Triangles') and “Field Headers” (Mac Users: Headers) to remove the collapse and drop down icons next to the labels)
Change the PivotTable Name to “PivotTable2”
Make sure the Product Category is in a different column than the Sales Person by right clicking a cell with product listed (such as A7 “Glue Guns”)
Right-click A7 and select “Field Settings”
On “Layout & Print” tab unselect “Display labels from the next field….”
(All boxes should be unselected on this tab)
(Mac Users: this will happen automatically, if you wish to tinker with the layout options, it is under the ribbon with Layout)
Remove the Grand Totals (Mac Users: 'Totals' on ribbon) from the PivotTable by going to the Design sub-tab. On the left side of the Ribbon, use the Grand Totals drop-down menu and select “Off for Rows and Columns” (Mac Users: unselect the check marks)
In the Sub Totals drop-down menu select “Show all Subtotals at Top of Group” (Mac Users: this happens automatically)
In the Design sub-tab, choose a ”Dark” PivotTable Style
Format the values using an Accounting Number Format with no “$” symbol, be sure to select all data in table using the shortcut previously mentioned
Hint: You can click the comma in the Number Format quick options or under more options select accounting and none in the symbol drop-down menu)
Step 5: We now have two PivotTables, one is located next to Table1 on the “Data” sheet the other at the top of the “Dashboard” sheet. We are going to create special chart called a PivotChart, which is a Chart that references a PivotTable, we will use a Line Chart that can display all 4 sales people simultaneously and be filtered by Region and Customer Type as well as have the ability to choose multiple selections of each category. This line graph will reference the PivotTable located on the “Data” sheet (PivotTable1).
Reference the data in PivotTable1 by entering the name into the Name Box, or select a cell in the PivotTable itself.
Insert a 2-D Line Chart (the very first selection available). Then Move the Chart to the Dashboard by either Right Clicking or Using the Analyze or Design sub-tabs and clicking “Move Chart”. Use the drop-down menu for “Object In” and select “Dashboard” to move it to the other sheet. Now on the Dashboard Sheet locate the Chart (you may have to zoom out to the right) drag and place it below PivotTable2. Stretch the Chart horizontally to match the width of the PivotTable.
In the Analyze sub-tab of the PivotChart Tools tab, open the Field List pane on the right side of the Ribbon.
Under the Axis (Categories), click the drop down arrow on “Period” and select “Field Settings”. On the Bottom left of the Field Settings Window select number format and choose “Mar-12” under Date.
Remove unnecessary filters by selecting the “Field Buttons” drop-down menu on the left of the Ribbon. Uncheck “Show Axis Field Buttons” and “Show Value Field Buttons”
Finally, in the Design sub-tab choose a color scheme in the “change colors” drop-down menu. Use a “colorful” scheme so the lines can be easily distinguished. (Also can change themes and colors in the Page Layout tab)