Analyze and visualize data with Microsoft Excel 2016
Objective
Upon completing this lab, you will have hands-on experience with the following functions and concepts related to Excel PivotCharts and Slicers:
- Understand the Excel PivotChart and Slicers
- How to create stand-alone PivotCharts
- How to connect multiple PivotCharts to one or more slicers
- How to Drilldown and across in the PivotChart to get more detail from the data.
- The Lab information below is done using Excel 2016 in Office Pro Plus.
- How to create stand-alone PivotCharts
- How to connect multiple PivotCharts to one or more slicers
- How to Drilldown and across in the PivotChart to get more detail from the data.
Scenario
VanArsdel is a company that manufactures and sells sporting goods. The company has offices in the United States (US) and several other countries. Its sales comprise of US sales and International sales. VanArsdel’s sales come from its owned manufactured products, as well as other manufacturers’ products.
An analyst has enriched the data model you created in previous labs, you can use this new model, to see an increase in sales over time, and compare our sales vs competitor sales. Specifically, you would like to know:
- For 2014, how did we sell relative to last year for each month of the year.
- Are we seeing the same trends for our competitor’s sales.
- Which Product Category had the highest sale in June for each state.
Virtual Machines
- CPHOL303
Exercise 1 : Introduction to Analyzing Data in Excel
-
Getting Started
To get started, open the Excel Workbook Lab Intro to Analyzing data in Excel located on the Desktop.
This workbook already has a set of reports, and a more sophisticated data model. Let’s first look at the Field List to see what is in the model
-
Market Share tab
The Market Share tab show our company sales within each segment. Click on the PivotChart “Change in market Share over the years”, which shows we slipped in Market Share in 2014
-
If the Field List does not automatically appear
If the Field List does not automatically appear, go to the Analyze Tab and Click on Field List to enable it.
-
In the Field List
In the Field List, we can see that a set measures (also called explicit measures) have been added to the data model. These are predefined calculations that are defined in the Data model, which we can use in our analysis.
-
Year over Year Sales Comparison
Interesting for us is the measure around Growth in Revenue from Last Year. Let’s use this measure to create our Year over Year Sales comparison. Select the YoY worksheet.
This is a completed version of the succeeding steps. To compare your work to the solution rather than do the lab inside an existing solution, create a new tab and complete the rest of the steps.
-
Hide the grid
The first thing we should do for the dashboard is to hide the grid. This makes for a cleaner, less distracted view. To do this go to the View Tab, and uncheck Gridlines
Next, we want to create a chart that shows us Sales Per Year. In 2013, we introduced the ability to create standalone PivotChart, in prior versions you always needed to create a PivotTable first and then create a PivotChart.
-
To create the PivotChart
To create the PivotChart, go to the Insert tab, and click on the PivotChart button in the Charts Group. Choosing the first option, which allows us to create a standalone PivotChart.
-
Create Pivot Chart
In the Data Connection Window select Use this workbook’s data model and click Ok
-
An empty PivotChart will be created
An empty PivotChart will be created for you. We want to show the sales figures across years. Under Analyze select Field List. In this model, there is already created a measure for aggregating revenue in the Sales Table called TotRevenue, let’s add this to the PivotChart by checking the fx TotRevenue checkbox.
-
Add Year field from the Calendar table
We also want to see Revenue per year, so let’s add Year field from the Calendar table to the Axis by checking the DateHierarchy checkbox and selecting Year . At the bottom of the PivotChart Fields under Legend (Series), click on down arrow next to DateHierarchy and select Move to Axis Fields (Categories). This should create the following PivotChart.
-
Change Chart Title
Let’s change the title of the chart to Historic Sales Figures, by double-clicking on the title.
-
PivotChart Interactive
This PivotChart shows revenue per year. The PivotChart is also interactive. If we want to see how the revenue was per month in 2014, we can double-click on 2014, to see the month breakdown.
-
Click CTRL+Z
If you wish to see the data for years again, you can click CTRL+Z (to undo), or select a single month. If you hover over the data point, right click and select Quick Explore. The Quick Explore Icon is shown.
-
To drill up, click on the Drill Up>>Year
To drill up, hover over the year data point. Click on the Year data point and then click the Quick Explore icon. Highlight the Calendar and click on the Drill Down Month box to the right of Calendar. Hover and click on the Month Data point, select the Quick Explore icon once again. On the right hand side of Calendar there should be two boxes: Drill Down DateColumn and Drill Up Year. Click on the Drill Up Year box.
-
Select Locations and Geo
Select 2014, and in the Quick Explore button, select Locations and Geo. You are now looking at revenue for 2014, broken down by Geography. The Quick Explore function allows you to drill quickly down and drill across looking at different data in your model.
The Quick Explore also allows you drill across, which means if you want to see the value in the PivotChart broken down by another dimension. Let’s say we want to see how Revenue in 2014 is broken down by Geography
-
Click CTRL+Z (Undo) to get back to the year view
Click CTRL+Z (Undo) to get back to the year view again.
-
In the Analyze Tab
In the Analyze Tab, change the name of the Chart from Chart 7 to HistoricSalesFigures
We want to add additional slicers to this dashboard. So we can easily let end users interact with the data.
Before we create the slicers, we should rename the PivotChart itself. Since we are connecting multiple slicers and charts together, naming the PivotChart makes it much easier for you to know you are connecting up the right chart.
-
In the Field List
In the Field List, select Country field, to view this expand the Locations Table and the More Field. You can now see Revenue per Country.
-
Manufacturer Table
Since the Manufacturer Table is not in use yet for this PivotChart, it is not directly available in the field list. Click All to see all the tables in the model
Next we also want to see our Sales vs Competitors Sales. In this model, the analyst has created a helper dimension in the Manufacturer Table called IsVanarsdel
-
Add IsVanarsdel as a slicer
Add IsVanarsdel as a slicer by right clicking on IsVanarsdel underneath the Manufacturer Field Llist and selecting Add as Slicer.
-
Change the name of the slicer
The default name of IsVanarsdel, is not very user friendly, so we want to change the name of the slicer. To do this, right-click on the slicer and select Slicer Settings.
-
We want to change this name to Compete
We want to change this name to Compete, so change the name and caption for the slicer and click Ok. Note: You may need to put a number after Compete in the Name field, since another slicer has the same name.
-
To create a new PivotChart
To create a new PivotChart, cick the YoY worksheet then go to Insert>>PivotChart. Select Create PivotChart and click Ok. For this chart we want to use a new measure, called GrowthFromLastYear. Which shows the difference between the selected period and last year’s period. This is located underneath Sales in the Fields List.
OK, we have a PivotChart showing history sales, but we wanted to see data Year over Year.
Let’s create a new PivotChart that shows this information.
-
Add Date Hierarchy to PivotChart
We also wish to see this over time so let’s add the Date Hierarchy, to the PivotChart. This located underneath the Calendar in the PivotChart Field List .
-
Create a slicer for Year
We also want to allow the user to select which year to view, so we want to create a slicer for Year. Go to PivotChart Field List>Calendar Table>DateHierarchy>Right click on Year and select Add as Slicer.
-
Connect the other slicers
The last thing we want to do is to connect the other slicers that we created to this Pivot Chart. To do this select the PivotChart, and in the Analyze tab select Filter Connections. In the Filter Connection Dialog select Compete and Country.
-
Click Style Icon
All of your PivotCharts should now be connected together. You can change the look and feel of the PivotCharts, by changing the style. To do this click on the style icon and select your preferred style.
-
Your final report can then look like this
Your final report can then look like this:
Please click on the Knowledge icon to view the questions related to the PivotCharts from this Lab.
Questions:
For 2014, how did we sell relative to last year for each month of the year?
Are we seeing the same trends for our competitors sales?
Which Product Category had the highest sale in June for each state?
Congratulations!
You have successfully completed this Lab!
Click Continue to close and finalize this lab.
Getting Started
To get started, open the Excel Workbook Lab Intro to Analyzing data in Excel located on the Desktop.
This workbook already has a set of reports, and a more sophisticated data model. Let’s first look at the Field List to see what is in the model
Market Share tab
The Market Share tab show our company sales within each segment. Click on the PivotChart “Change in market Share over the years”, which shows we slipped in Market Share in 2014
If the Field List does not automatically appear
If the Field List does not automatically appear, go to the Analyze Tab and Click on Field List to enable it.
In the Field List
In the Field List, we can see that a set measures (also called explicit measures) have been added to the data model. These are predefined calculations that are defined in the Data model, which we can use in our analysis.
Year over Year Sales Comparison
Interesting for us is the measure around Growth in Revenue from Last Year. Let’s use this measure to create our Year over Year Sales comparison. Select the YoY worksheet.
This is a completed version of the succeeding steps. To compare your work to the solution rather than do the lab inside an existing solution, create a new tab and complete the rest of the steps.
Hide the grid
The first thing we should do for the dashboard is to hide the grid. This makes for a cleaner, less distracted view. To do this go to the View Tab, and uncheck Gridlines
Next, we want to create a chart that shows us Sales Per Year. In 2013, we introduced the ability to create standalone PivotChart, in prior versions you always needed to create a PivotTable first and then create a PivotChart.
To create the PivotChart
To create the PivotChart, go to the Insert tab, and click on the PivotChart button in the Charts Group. Choosing the first option, which allows us to create a standalone PivotChart.
Create Pivot Chart
In the Data Connection Window select Use this workbook’s data model and click Ok
An empty PivotChart will be created
An empty PivotChart will be created for you. We want to show the sales figures across years. Under Analyze select Field List. In this model, there is already created a measure for aggregating revenue in the Sales Table called TotRevenue, let’s add this to the PivotChart by checking the fx TotRevenue checkbox.
Add Year field from the Calendar table
We also want to see Revenue per year, so let’s add Year field from the Calendar table to the Axis by checking the DateHierarchy checkbox and selecting Year . At the bottom of the PivotChart Fields under Legend (Series), click on down arrow next to DateHierarchy and select Move to Axis Fields (Categories). This should create the following PivotChart.
Change Chart Title
Let’s change the title of the chart to Historic Sales Figures, by double-clicking on the title.
PivotChart Interactive
This PivotChart shows revenue per year. The PivotChart is also interactive. If we want to see how the revenue was per month in 2014, we can double-click on 2014, to see the month breakdown.
Click CTRL+Z
If you wish to see the data for years again, you can click CTRL+Z (to undo), or select a single month. If you hover over the data point, right click and select Quick Explore. The Quick Explore Icon is shown.
To drill up, click on the Drill Up>>Year
To drill up, hover over the year data point. Click on the Year data point and then click the Quick Explore icon. Highlight the Calendar and click on the Drill Down Month box to the right of Calendar. Hover and click on the Month Data point, select the Quick Explore icon once again. On the right hand side of Calendar there should be two boxes: Drill Down DateColumn and Drill Up Year. Click on the Drill Up Year box.
Select Locations and Geo
Select 2014, and in the Quick Explore button, select Locations and Geo. You are now looking at revenue for 2014, broken down by Geography. The Quick Explore function allows you to drill quickly down and drill across looking at different data in your model.
The Quick Explore also allows you drill across, which means if you want to see the value in the PivotChart broken down by another dimension. Let’s say we want to see how Revenue in 2014 is broken down by Geography
Click CTRL+Z (Undo) to get back to the year view
Click CTRL+Z (Undo) to get back to the year view again.
In the Analyze Tab
In the Analyze Tab, change the name of the Chart from Chart 7 to HistoricSalesFigures
We want to add additional slicers to this dashboard. So we can easily let end users interact with the data.
Before we create the slicers, we should rename the PivotChart itself. Since we are connecting multiple slicers and charts together, naming the PivotChart makes it much easier for you to know you are connecting up the right chart.
Before we create the slicers, we should rename the PivotChart itself. Since we are connecting multiple slicers and charts together, naming the PivotChart makes it much easier for you to know you are connecting up the right chart.
In the Field List
In the Field List, select Country field, to view this expand the Locations Table and the More Field. You can now see Revenue per Country.
Manufacturer Table
Since the Manufacturer Table is not in use yet for this PivotChart, it is not directly available in the field list. Click All to see all the tables in the model
Next we also want to see our Sales vs Competitors Sales. In this model, the analyst has created a helper dimension in the Manufacturer Table called IsVanarsdel
Add IsVanarsdel as a slicer
Add IsVanarsdel as a slicer by right clicking on IsVanarsdel underneath the Manufacturer Field Llist and selecting Add as Slicer.
Change the name of the slicer
The default name of IsVanarsdel, is not very user friendly, so we want to change the name of the slicer. To do this, right-click on the slicer and select Slicer Settings.
We want to change this name to Compete
We want to change this name to Compete, so change the name and caption for the slicer and click Ok. Note: You may need to put a number after Compete in the Name field, since another slicer has the same name.
To create a new PivotChart
To create a new PivotChart, cick the YoY worksheet then go to Insert>>PivotChart. Select Create PivotChart and click Ok. For this chart we want to use a new measure, called GrowthFromLastYear. Which shows the difference between the selected period and last year’s period. This is located underneath Sales in the Fields List.
OK, we have a PivotChart showing history sales, but we wanted to see data Year over Year.
Let’s create a new PivotChart that shows this information.
Let’s create a new PivotChart that shows this information.
Add Date Hierarchy to PivotChart
We also wish to see this over time so let’s add the Date Hierarchy, to the PivotChart. This located underneath the Calendar in the PivotChart Field List .
Create a slicer for Year
We also want to allow the user to select which year to view, so we want to create a slicer for Year. Go to PivotChart Field List>Calendar Table>DateHierarchy>Right click on Year and select Add as Slicer.
Connect the other slicers
The last thing we want to do is to connect the other slicers that we created to this Pivot Chart. To do this select the PivotChart, and in the Analyze tab select Filter Connections. In the Filter Connection Dialog select Compete and Country.
Click Style Icon
All of your PivotCharts should now be connected together. You can change the look and feel of the PivotCharts, by changing the style. To do this click on the style icon and select your preferred style.
Your final report can then look like this
Your final report can then look like this:
Please click on the Knowledge icon to view the questions related to the PivotCharts from this Lab.
Questions:
For 2014, how did we sell relative to last year for each month of the year?
Are we seeing the same trends for our competitors sales?
Which Product Category had the highest sale in June for each state?
For 2014, how did we sell relative to last year for each month of the year?
Are we seeing the same trends for our competitors sales?
Which Product Category had the highest sale in June for each state?
You have successfully completed this Lab!
Click Continue to close and finalize this lab.
No comments:
Post a Comment