Guided Exercises

Open the Chapter13_Charts file.

The FurnitureAnalysis worksheet shows what products each vendor of a furniture store sells during 2024. You would like to create a visual to show how many transactions each vendor had as well as how much revenue each vendor brought into the store. Before you can do that, the data need to be summarized.

1. In cell H4, use a function to return the number of transactions the customer in cell G4 had during 2024. Fill the function down through cell H8 to return the number of transactions for each vendor.
2. In cell I4, use a function to return the total revenue the vendor in cell G4 brought in during 2024. Fill the function down through cell I8 to return the total revenue for each vendor. Format as Accounting.
3. Now that the data is summarized, a chart can be created. Select the data in cells G3:I8 so that vendor names and column headings are selected as well as the corresponding values.
4. Insert a Clustered Column – Line on Secondary Axis chart.
   • Position it over the shaded area in cells G12:M27.
   • Change the Chart Style to Style 2.
   • Change the Chart Title to 2024 Vendor Transactions. Make the font size 20pt and change the font color to Blue, Accent 1, Darker 25%.
   • Add Axis Titles to the Primary Vertical and Secondary Vertical axes, but not the Horizontal.
     • Change the axis title on the left to read # of Transactions. Change the font to 12pt, Century Gothic.
     • Change the axis title on the right to read Revenue. Change the font to 12pt, Century Gothic.

The completed Clustered Column – Line on Secondary Axis chart should look like the following image:

Another good chart for this data is a pie chart which can show how each category vendor to the whole set of data, especially if you’d like to display percentages.

• • Select cells G3:I8 again and insert a 3-D Pie Chart. Place it over the shaded area below the chart above, in cells G27:M39.
  • Change the Chart Title to Vendor Comparison.
  • Change the Chart Style to Style 8.
  • Click the Select Data button in the ribbon, and notice you can turn off # of Transactions and just have Total Revenue.
  • Find More Options for the Data Labels, clicking the three bars called “Label Options”. Under Label Options, check to add Percentage
  • If you wish, you can click and drag one of the pie wedges (e.g.; B&B Spaces is the largest) out to emphasize it.
  • Your finished chart should look something like this:

Website Traffic Analysis

In the WebsiteTraffic worksheet, create a column chart that shows the monthly website traffic for the Haptown main website.

1. Select cells A3:B368 and insert a Clustered Column bar chart.
   • Change the title to Pageviews Per Month
   • Change the style to Style 9
   • Double-click the dates on the horizontal axis and change the Base option to Months.
   • Add Axis Titles and use Month for the horizontal and Pageviews for the vertical.
   • Your finished column chart should look something like this:

Sales PivotTable

The SalesData worksheet contains data from the furniture store’s 2024 transactions. The data on this worksheet is nicely organized, but there is a lot of it! We can use a PivotTable to further summarize the data so that it can be explored interactively.

1. Select the SalesPivotTable worksheet. Notice that cell A3 has a fill color. This is the cell that we are going to choose as the location for the PivotTable.
2. Go back to the SalesData worksheet and select one cell that has data in it.
3. Click on the Insert tab and then click on PivotTable on the far-left side of the ribbon.
4. In the Create PivotTable window that opens, confirm that the range of data is in the box at the top.
5. Click on the button near the bottom, next to Existing Worksheet.
6. Click in the box next to Location.
7. Click the SalesPivotTable worksheet.
8. Click in cell A3. Click OK.
9. Notice the PivotTable Fields list on the right side. This is where we will set up which fields are the columns of the PivotTable and which fields are the rows of the PivotTable as well as which values are summarized and in which way they are summarized.
   • Look at the image below of what your finished PivotTable will look like. Above the PivotTable, in cells A3:B3 is a filter so that the data can be filtered down to different departments. In the Fields List, drag the Department field to the Filters area of the design.
   • The categories are each in their own column. Therefore, drag the Category field to the Columns quadrant.
   • The rows are related to when the transactions took place. Therefore, drag the Date field to the Rows quadrant.
     • Notice that Months will be added as well so that Excel will group the date by month. We will also group by Quarter later.
   • The actual data inside the PivotTable are totals of the volume and revenue amounts. Therefore, bring the Volume field into the Values quadrant.
     • Notice that the name of the field changes to Sum of Volume.
   • Then, bring the Revenue field into the Values quadrant, making sure it is placed under the Volume.
     • Notice that the Revenue field becomes Sum of Revenue.
   • Click on the Sum of Revenue field and select Value Field Settings. Click on the Number Format button. Select Currency as the format of this field. Click OK and OK again.
   • Close the PivotTable Fields list.
10. Change cell B4 to Category by typing over it. It’s ok, it will still remain a drop-down.
11. Change cell A6 to Date by typing over it.
12. Right-click on any one of the months and choose Group. Deselect Days and Select Quarters, then click OK.
13. Change the PivotTable style to Light Blue, Pivot Style Medium 6.
14. Use the PivotTable to answer the questions in column M. After you are finished answering the questions, make sure any filters you used are cleared so that your PivotTable looks like the image below.

The finished PivotTable should resemble this image:

More Practice