CloudSwyft Global Systems, Ltd.

The ability to analyze data is a powerful skill that helps you make better decisions. Microsoft Excel is one of the top tools for data analysis and the built-in pivot tables are arguably the most popular analytic tool.In this course, you will see the power of Excel pivots in action and their ability to summarize data in flexible ways, enabling quick exploration of data and producing valuable insights from the accumulated data.Pivots are used in many different industries by millions of users who share the goal of reporting the performance of companies and organizations. In addition, Excel formulas can be used to aggregate data to create meaningful reports. To complement, pivot charts and slicers can be used together to visualize data and create easy to use dashboards.You should have a basic understanding of creating formulas and how cells are referenced by rows and columns within Excel to take this course. If required, you can can find many help topics on Excel at the Microsoft Office Support Site. You are welcome to use any supported version of Excel you have installed in your computer, however, the instructions are based on Excel 2016. You may not be able to complete all exercises as demonstrated in the lectures but workarounds are provided in the lab instructions or Discussion forum. Please note that Excel for Mac does not support many of the features demonstrated in this course.After taking this course you’ll be ready to continue to our more advanced Excel course, Analyzing and Visualizing Data with Excel.

This activity introduces you to the course outline and learning outcomes, as well as CloudSwyft and its partnership on this course.

We're glad to welcome you to DAT205x: Introduction to Data Analysis using Excel!

Everyday, millions of people use Excel for their daily tasks. Whether is for number crunching, reporting, calculations, you name it. In this course, you will learn how to perform data analysis using Excel’s most popular features. You will learn how to create pivot tables from a range with rows and columns in Excel. You will see the power of Excel pivots in action and their ability to summarize data in flexible ways, enabling quick exploration of data and producing valuable insights from the accumulated data.

We welcome all learners! The only thing you need is Microsoft Excel installed in your computer and the yearn to learn! If you're a novice user, or just stuck with something, check out the discussion forum. If you're a more advanced learner, or already a seasoned Excel user, stick around the discussion group and perhaps help your fellow learners. 

We hope you'll have an enriching and engaging learning experience!

For more information , you can see: 
Microsoft Excel: <a href="https://aka.ms/edx-dat205x-xls" target="_blank" rel="noreferrer noopener">https://aka.ms/edx-dat205x-xls</a>

Welcome!

If you're new to edX here is some information to get you started. Even if you've used edX for other courses, the information here will help orient you to how this course is laid out.

<h2>LAYOUT OF THE COURSE</h2>

This course is laid out in modules and units. Each module contains 1 or more units that cover the material for that module. The module is the topic area and the units have all the learning material you'll need to learn that topic. To navigate through the module's units, use the navigation bar at the top of the module, or you can use the left and right arrows to move through each unit.

<img alt="edX navigation bar" src="https://apollostudio.cloudswyft.com/assets/courseware/v1/34a23620b5fc6ba90d8b07fbc557dfb6/asset-v1:UNITAR+DAT205x+2018_T2+type@asset+block/edX_nav_bar_sm.png" style="height:191px;width:864px;" />

Each unit will generally have some video content, reading material and problem sets, and a set of assessment questions to test your knowledge. Some units also have a discussion board where you can post questions and other material so fellow students can provide feedback. The units are meant to be taken in order but you can skip around if you already have mastered the material in a particular unit. In order to do well on the assessments, you should learn the material in all the units. Hover over the unit with your mouse to see the unit title.

<h2>INSTRUCTOR FEEDBACK</h2>

Given the nature of this MOOC-style course and the large number of students enrolled, it's not possible for instructors to provide feedback to students. However, we encourage you to seek help and feedback from your fellow students using the discussion boards. This is a great way to help each other out and enrich your learning experience.

<h2>OTHER RESOURCES</h2>

edX Student FAQ: This FAQ answers a lot of questions about the edX student experience. You should at least read over the questions so if you encounter any issues during the course, you can refer back to this document for help.

DemoX: A fun and interactive course designed to help you explore the edX learning experience. Perfect to take before you start your course.

Course Basics

<h2>Who is CloudSwyft?</h2>

CloudSwyft is a leading Learning-as-a-Service platform provider empowering students and industry professionals through modernized digital learning and skills development. Through its technology learning labs platform, learners like yourself can develop future-ready skills from data science, AI, and machine learning to specific basic and advanced skills on Azure Cloud.

<h2>What are Hands-On Labs?</h2>

Technology skills learning and development can’t be achieved by simply viewing Massive Open Online Courses (MOOC) videos, lectures, presentation slides, webinars, or classroom-based sessions. Everyone should learn technology skills by doing! That’s why through Hands-On Labs, CloudSwyft is making the tech upskilling journey better for students and industry professionals.

CloudSwyft-Powered Hands-On Labs are virtual lab environments (whether computer lab environments or Azure Cloud Sandbox Portal environments). They allow students or learners to access live, fully functional practice environments through a browser attached and aligned with each course activity.

If you encounter lab problems or questions on the course content, please reach out to us at <a href="mailto:support@cloudswyft.com">support@cloudswyft.com</a>

CloudSwyft: What are Hands-On Labs?

<h1>Test Launch Your CloudSwyft Labs</h1>

It is time to test launch your CloudSwyft Hands-On Lab session. You will need to start off by requesting your lab account.

<blockquote>
Note: CloudSwyft’s Hands-On Lab environments are best launched and accessed on the latest Google Chrome browser with a stable internet connection.
</blockquote>

 

When you click the Start button, CloudSwyft Lab opens in a new window. Click on Request Lab Provision.

<img alt="" src="https://raw.githubusercontent.com/BerksVC/CSRepo/main/CLMP%20First%20Launch.png" />

Select How to Use Microsoft Excel for Data Analysis, then click Send Request. Provisioning will take between 12-24 hours.

Once your account has been registered and your lab provisioned, you can log in to see and launch your lab sessions.

 

If your lab has been provisioned successfully, you may click on the Start icon to access your lab.

<img alt="1666077032-image.png" src="https://s3.ap-southeast-2.amazonaws.com/s3.go1.service/cloud-swyft.mygo1.com/apiom/5492232/1666076972/1666077032-image.png" />

Once you see the Launch icon, click on it too.

<img alt="1666077006-image.png" src="https://s3.ap-southeast-2.amazonaws.com/s3.go1.service/cloud-swyft.mygo1.com/apiom/5492232/1666076948/1666077006-image.png" />

The first time you launch your lab session, a unique workplace will be created for your labs. All the information, software, and data you require will be pre-installed with shortcuts on your desktop.

<blockquote>
You may also be asked to join Microsoft Teams. You do NOT need to do this! You can click X on the top right to close out of both of these options.
</blockquote>

<a href="https://ibb.co/7thD3vZ"><img alt="2" src="https://i.ibb.co/Zz9rQdF/2.png" /></a>

<blockquote>
Click Yes to close the network connection window.
</blockquote>

<a href="https://ibb.co/q71Ky8g"><img alt="3" src="https://i.ibb.co/ckT0g98/3.png" /></a>

<a href="https://ibb.co/xzjXfMt"><img alt="Screenshot-2021-09-14-190606" src="https://i.ibb.co/wpWBwKF/Screenshot-2021-09-14-190606.png" /></a>

Click on the Lab Activity icon in order to view and follow the lab activity instructions and exercises you need to perform for your course or lab class.

Click on Next on your Lab Activities so you can read further lab instructions.

<a href="https://imgbb.com/"><img alt="Screenshot-2021-10-13-144055" src="https://i.ibb.co/S6KzM51/Screenshot-2021-10-13-144055.png" /></a>

<blockquote>
A reminder to log out of your lab session whenever you are done with your lab by clicking on the Shutdown icon at the upper right-hand corner of the Lab Session Window screen before returning to your course. Click Yes to confirm and return to your course. You need to ensure that you log out of your lab session in order to stop the access clock.
</blockquote>

<blockquote>
Do not shut down the machine from within the virtual machine.
</blockquote>

The file below provides step-by-step instructions on how to sign-up to, access the labs, and how to use Remote Desktop Connection. Click the <a href="https://cloudswyft.sharepoint.com/:b:/s/FRStargzfiles/EYKniqstc4RNlvmfdozgTfoBlTwCEoyVMNqcR-3t64SlMg?e=WsVyHb">Student Guide</a> to download these instructions.

If you encounter any Hands-On Lab problems, please reach out to <a href="mailto:support@cloudswyft.com">CloudSwyft Support</a>.

Test Launch Your CloudSwyft Labs

Introduction

In this activity, you'll be introduced to the basics of reporting data in Excel and using Excel charts.

Lecture

For more information about available chart types in Excel, please review these resources:

<ul>
	<li>
	<a href="https://support.office.com/en-us/article/Available-chart-types-in-Office-a6187218-807e-4103-9e0a-27cdb19afb90?ui=en-US&amp;rs=en-US&amp;ad=US" target="_blank" rel="noreferrer noopener">https://support.office.com/en-us/article/Available-chart-types-in-Office-a6187218-807e-4103-9e0a-27cdb19afb90?ui=en-US&amp;rs=en-US&amp;ad=US</a>
	</li>
</ul>

We recommend that you explore the Available Charts Types in Office (not all charts types are available in each version of Excel). For the quiz, you will need to review the section titled “Select a chart type to learn more about it”. From the dropdown box “What chart type are you interested in?” select the chart type you are interested in to learn more about that chart.

<img alt="" src="https://apollostudio.cloudswyft.com/assets/courseware/v1/a3cec16262fc902fa1df5be47504ca45/asset-v1:UNITAR+DAT205x+2018_T2+type@asset+block/pic1.png" style="border:0px;font-family:inherit;font-size:inherit;font-style:inherit;font-variant:inherit;font-weight:inherit;height:549px;line-height:1.4em;padding:0px;vertical-align:baseline;width:753px;" />

Quiz

This Future-Ready Technology Course has integrated Virtual Lab Environments (Compute Labs or Azure Sandbox Labs) powered by the <a href="https://cloudswyft.com/" target="_blank" rel="noreferrer noopener">CloudSwyft </a>proprietary Hands-On Learning Labs Platform Technology aligned for each Module section to provide the best digital learning experience for students and enable the strong importance of practice learning for tech upskilling. 

 

You may find it on each and every Course Module or Lesson that has a Lab Exercise section. 

 

Enjoy your Hands-On Lab Learning Session! 

 

If there are any issues please contact CloudSwyft Support Team (support@cloudswyft.com).

CloudSwyft Hands-On Lab 1

You are the new marketing manager of an established Bicycle company. The company sells bicycles and accessories, such as clothing and other accessories to bikers in six countries.

The company has just hired Lucy as its new Sales manager. You are tasked to introduce Lucy to the company, its product portfolio and its sales performance since 2011. To do this, you have asked Jack, the IT manager to prepare some data for you.

Now, it's your job to present this data in a compelling manner.

Lab Check: Scenario

The first thing you'd like to do is to present the data graphically. You do this by creating charts for the three groups of data you received from Jack, your IT manager. Give each chart a descriptive title as shown in the three charts below.

Your first solution should look like this (numbers may not be the same).

<img src="https://studio-branchacademy.cloudswyft.com/assets/courseware/v1/6d5311d2f9935b5128c6880f13e980d2/asset-v1:FutureLab+DAT205x+2022_T1+type@asset+block/lab1x.PNG" alt="lab1x.PNG" /><img src="https://s3.ap-southeast-2.amazonaws.com/s3.go1.service/cloud-swyft.mygo1.com/apiom/4178717/1663742728/1663742788-image.png" alt="1663742788-image.png" />

Once you have created the three charts above, let's customize them a little bit more.

For the Yearly Category Revenue chart, display the total revenue for each year as a separate line.

<ol>
	<li>Create a Total Revenue line in the range B7 to H7 by summing the annual revenue in rows 3 through 6.</li>
	<li>Select the Yearly Category Revenue chart.</li>
	<li>The data source for the chart is highlighted.</li>
	<li>Drag the highlighted selection to include the total row (B7 to H7)</li>
</ol>

Lab 1

Introduction to Reporting in Excel

In this activity, we'll learn about Excel tables and their features.

For more information regarding Excel tables, please review the following resources:

<ul>
	<li>Overview of Excel tables: <a href="https://support.office.com/en-us/article/Overview-of-Excel-tables-7ab0bb7d-3a9e-4b56-a3c9-6c94334e492c">https://support.office.com/en-us/article/Overview-of-Excel-tables-7ab0bb7d-3a9e-4b56-a3c9-6c94334e492c</a></li>
	<li>Using structured references with Excel tables: <a href="https://support.office.com/en-us/article/Using-structured-references-with-Excel-tables-f5ed2452-2337-4f71-bed3-c8ae6d2b276e">https://support.office.com/en-us/article/Using-structured-references-with-Excel-tables-f5ed2452-2337-4f71-bed3-c8ae6d2b276e</a></li>
</ul>

For those of you who are interested, the Excel tables' Totals row uses the SUBTOTAL() function. For more detailed information on this function review this link: <a href="https://support.office.com/en-us/article/SUBTOTAL-function-7b027003-f060-4ade-9040-e478765b9939">https://support.office.com/en-us/article/SUBTOTAL-function-7b027003-f060-4ade-9040-e478765b9939</a>

For more information about the IF() function, please review the following resource:

<ul>
	<li>The IF function: <a href="https://support.office.com/en-US/article/IF-function-69AED7C9-4E8A-4755-A9BC-AA8BBFF73BE2">https://support.office.com/en-US/article/IF-function-69AED7C9-4E8A-4755-A9BC-AA8BBFF73BE2</a> </li>
	<li>Advanced IF functions: <a href="https://support.office.com/en-us/article/Advanced-IF-functions-17341cef-510d-4d60-a657-913f878fdacf">https://support.office.com/en-us/article/Advanced-IF-functions-17341cef-510d-4d60-a657-913f878fdacf</a></li>
</ul>

<h3>Quiz File</h3>

Download the quiz file <a href="https://github.com/MicrosoftLearning/Introduction-to-Data-Analysis-using-Excel/raw/master/Quiz/SalesForCourse_quizz_table.xlsx">SalesForCourse_quizz_table.xlsx</a> to answer the questions below.

CloudSwyft Hands-On Lab 2

While your first attempt to show the company's performance to Lucy was not bad, clearly she has a lot more requirements than what you provided. She wants to know more about the year over year sales, sliced into different categories, sub-categories, and countries. She also wants to see additional information such as customer demographics.

Jack has provided you with a different data source. This time the data has more than one hundred thousand rows.

You can download the data <a href="https://github.com/MicrosoftLearning/Introduction-to-Data-Analysis-using-Excel/raw/master/Module2/Lab2Start%20v5.xlsx" target="_blank" rel="noreferrer noopener">here.</a>

Before you can create additional reports to Lucy, first you need to prepare the data.

Scenario

The first thing you need to do is to convert the data into an Excel table.

Once you do that, you can add total row, filter the data, and the total will reflect the total only for the filtered data. Let's try this. Add a total row for the table, and use the Sum aggregation to show the total of the Revenue column and then filter the data only for United States.

Lab 2

Excel Tables

In this activity, we'll introduce you to the basic features of the pivot table. We'll also look at how you can use pivot tables and charts to build a basic dashboard.

Lecture 1

Lecture 2

For more information about PivotTable and PivotChart, please review the following resources:

<ul>
	<li>Overview of PivotTable and PivotChart reports: <a href="https://support.office.com/en-us/article/Overview-of-PivotTable-and-PivotChart-reports-527c8fa3-02c0-445a-a2db-7794676bce96">https://support.office.com/en-us/article/Overview-of-PivotTable-and-PivotChart-reports-527c8fa3-02c0-445a-a2db-7794676bce96 </a></li>
	<li>Create a Pivot Chart (separate Pivot Charts were first available in Excel 2013): <a href="https://support.office.com/en-us/article/Create-a-PivotChart-c1b1e057-6990-4c38-b52b-8255538e7b1c">https://support.office.com/en-us/article/Create-a-PivotChart-c1b1e057-6990-4c38-b52b-8255538e7b1c </a></li>
	<li>Sort data in a PivotTable: <a href="https://support.office.com/en-US/article/Sort-data-in-a-PivotTable-E41F7107-B92D-44EF-861F-24430830450A">https://support.office.com/en-US/article/Sort-data-in-a-PivotTable-E41F7107-B92D-44EF-861F-24430830450A</a></li>
</ul>

And in case you missed it, here's an Overview of Excel tables: <a href="https://support.office.com/en-us/article/Overview-of-Excel-tables-7ab0bb7d-3a9e-4b56-a3c9-6c94334e492c">https://support.office.com/en-us/article/Overview-of-Excel-tables-7ab0bb7d-3a9e-4b56-a3c9-6c94334e492c</a>

CloudSwyft Hands-On Lab 3

Now that you have prepared the data in an Excel table, you can start to create pivot tables to aggregate the data and create some reports.

You can download the prepared <a href="https://github.com/MicrosoftLearning/Introduction-to-Data-Analysis-using-Excel/raw/master/Module3/Lab3AStart%20v5.xlsx">data</a> here.

From your conversation with Lucy, you know that she is interested in looking into the yearly sales data broken down by countries, product categories, and age groups.

First, let's start by naming the Excel table. Name the Excel table prepared in the previous lab to SalesTable. From now on, every time you add a pivot table, it should be based on this SalesTable, unless mentioned otherwise.

Now, proceed to add your first pivot table. Insert a new pivot table based on the SalesTable to a new sheet. Arrange the layout so that the pivot table displays the Product Category and Sub Category in the Rows, Year in the Columns, and Revenue (Sum of) as the Values.

Lab 3A

Now you can start adding some charts to the sheet.

First, add a pivot chart for the pivot table that shows yearly sales (revenue) by Country. Select a Line chart to display the yearly trend. Make sure that the Years are located in the X axis, the Revenue in the Y axis, and the Countries as categories. Hint: You might find the Switch Row/Column in Select Data Source window useful. In addition, try Collapsing the Country fields in order to hide the States fields.

Lab 3B

Basic Pivot Tables

In this step, we'll jump right into our first topic, Dashboards. You'll learn how slicers, filters and pivots can be used to build sophisticated dashboards.

For more information regarding Slicers on Pivot Tables, please review the following resources:

<ul>
	<li>Use slicers to filter PivotTable data: <a href="https://support.office.com/en-us/article/Use-slicers-to-filter-PivotTable-data-249F966B-A9D5-4B0F-B31A-12651785D29D">https://support.office.com/en-us/article/Use-slicers-to-filter-PivotTable-data-249F966B-A9D5-4B0F-B31A-12651785D29D</a></li>
</ul>

<h3>Quiz File</h3>

Download the quiz file <a href="https://github.com/MicrosoftLearning/Introduction-to-Data-Analysis-using-Excel/raw/master/Quiz/SalesForCourse_quizz_table.xlsx">SalesForCourse_quizz_table.xlsx</a> to answer Question 1 below.

CloudSwyft Hands-On Lab 4

You have created several pivot tables and pivot charts for Lucy.

You can download the latest report <a href="https://github.com/MicrosoftLearning/Introduction-to-Data-Analysis-using-Excel/raw/master/Module4/Lab4AStart%20v5.xlsx">here</a>.

So far everything has been well received by her. However, she would like to have easier ways to slice and dice the reports and charts herself.

You sat down with Lucy, and come up with several different ways that Lucy could slice the data

<ul>
	<li>Year</li>
	<li>Country</li>
	<li>Customer Gender</li>
	<li>Age Group</li>
	<li>Product Category</li>
	<li>Sub Category</li>
	<li>Frame size</li>
</ul>

<h4> </h4>

Start by adding a new sheet named Dashboard. Then move (Cut and Paste) the four charts that you have to that sheet. Arrange the charts as appropriate. 
Hint: The Snap to Grid option located at the Align Object on the Page Layout tab might be useful.

For easy reference, let's give the charts titles if they don't have any, or rename them as appropriate. Name the four charts as follows:

<ul>
	<li>Yearly Sales by Country</li>
	<li>Yearly Sales by Category</li>
	<li>Sales by Frame Size</li>
	<li>Sales by Age Group</li>
</ul>

Hint: To add a chart title, select the chart, then on the Design tab, select Add Chart Element.

You can now add slicers to the sheet. Select the Yearly Sales by Country chart, and add seven slicers corresponding to Year, Country, Customer Gender, Age Group, Product Category, Sub Category and Frame Size. Arrange the slicers as appropriate.

The next thing you need to do is to connect these slicers to the charts. Let's do this chart by chart. 
Hint: The Filter Connections option is available on the chart Analyze tab.

<ol>
	<li>Connect the Yearly Sales by Country chart to all slicers, except the Year slicer. In another words, disconnect the Year slicer from the Yearly Sales by Country</li>
	<li>Connect the Yearly Sales by Category chart to the Age Group, Country, Customer Gender, and Frame Size slicers.</li>
	<li>Connect the Sales by Frame Size chart to all slicers, except the Frame Size slicer.</li>
	<li>Connect the Sales by Age Group chart to all slicers, except the Age Group.</li>
</ol>

For niceties, you can clear the Gridlines on the Dashboard, 
Hint: The Gridlines option is available on the View tab.

Once you've done the above, you're ready to present the dashboard to Lucy.

Lab 4A

Create an additional pivot chart to show Sales by Customer Gender using Pie chart. Show percentages for each slice of the pie and connect the chart to all the slicers, except the Customer Gender slicer.

Lab 4B

Dashboards

In this activity, you'll explore how to use calculated fields and conditional formatting in pivot tables to analyse datasets and detect anomalies in data.

For more information on calculating values in a PivotTable, check out the following resource:

<ul>
	<li><a href="https://support.office.com/en-us/article/Calculate-values-in-a-PivotTable-report-11f41417-da80-435c-a5c6-b0185e59da77">https://support.office.com/en-us/article/Calculate-values-in-a-PivotTable-report-11f41417-da80-435c-a5c6-b0185e59da77</a></li>
</ul>

And to learn more about conditional formatting, check out the following resources:

<ul>
	<li>Quick start: Apply conditional formatting: <a href="https://support.office.com/en-us/article/Quick-start-Apply-conditional-formatting-6b6f7c2a-5d62-45a1-8f67-584a76776d67">https://support.office.com/en-us/article/Quick-start-Apply-conditional-formatting-6b6f7c2a-5d62-45a1-8f67-584a76776d67</a></li>
	<li>Change, find, or clear conditional formats dialog box options: <a href="https://support.office.com/en-us/article/Change-find-or-clear-conditional-formats-dialog-box-options-8a1cc355-b113-41b7-a483-58460332a1af?ui=en-US&amp;rs=en-US&amp;ad=US">https://support.office.com/en-us/article/Change-find-or-clear-conditional-formats-dialog-box-options-8a1cc355-b113-41b7-a483-58460332a1af?ui=en-US&amp;rs=en-US&amp;ad=US</a></li>
	<li>Use a formula to apply conditional formatting: <a href="https://support.office.com/en-us/article/Use-a-formula-to-apply-conditional-formatting-fed60dfa-1d3f-4e13-9ecb-f1951ff89d7f">https://support.office.com/en-us/article/Use-a-formula-to-apply-conditional-formatting-fed60dfa-1d3f-4e13-9ecb-f1951ff89d7f </a></li>
</ul>

CloudSwyft Hands-On Lab 5

Lucy was impressed with the dashboard you created.

You can download the <a href="https://github.com/MicrosoftLearning/Introduction-to-Data-Analysis-using-Excel/raw/master/Module5/Lab5Start%20v5.xlsx">file</a> here.

With the dashboard, she is able to narrow down her interest. Specifically, she is interested with the sales in Australia. She would like to perform simple profitability analysis on the product category and sub-category, for specific year. Furthermore, she wants to have the option of going deeper to product level.

Start by adding a new sheet named Details. Add a pivot table that shows Order Quantity, Revenue, and Profit by Product Category. Add the Year and Country field to the filter list.

Add the Sub Category field to the rows of the pivot table. Make sure the rows are fully expanded. Now you have quite a few numerical data shown in the pivot table, which might not be as easy to analyze as before. Apply Data bars conditional formatting to the Order Quantity, Revenue and Profit column on Sub Category level. Use blue, green, and yellow respectively.

Filter the pivot table for Australia and 2016, and answer the following questions.

Lab 5

Profitability Analysis and Finding Anomalies

In this activity, we'll look at comparing data in pivot tables. We'll focus on year-on-year comparison values using the pivot table as well as the SUMIFS function.

To learn more about SUMIFS() function and relative/absolute references, check out the following resources:

<ul>
	<li>SUMIFS function:  <a href="https://support.office.com/en-us/article/SUMIFS-function-c9e748f5-7ea7-455d-9406-611cebce642b">https://support.office.com/en-us/article/SUMIFS-function-c9e748f5-7ea7-455d-9406-611cebce642b </a></li>
	<li>Switching between relative, absolute, and mixed references: <a href="https://support.office.com/en-us/article/Switch-between-relative-absolute-and-mixed-references-dfec08cd-ae65-4f56-839e-5f0d8d0baca9">https://support.office.com/en-us/article/Switch-between-relative-absolute-and-mixed-references-dfec08cd-ae65-4f56-839e-5f0d8d0baca9</a> </li>
</ul>

CloudSwyft Hands-On Lab 6

Lucy seems intrigued your presentation on Australia's sales.

You can download the <a href="https://github.com/MicrosoftLearning/Introduction-to-Data-Analysis-using-Excel/raw/master/Module6/Lab6Start%20v5.xlsx">file</a> here.

Now, she wants to review the sales difference (growth) for Product Category and Sub Category between year 2015 and 2016. She wants to be able to filter the report by all the options available, including Customer Gender and Age Group.

Lab 6

Comparing Year over Year in Pivot Tables

In this activity, you'll go through different ways to visualise and chart data in Excel. In particular, you'll explore how to visually represent hierarchical data making use of Treemaps and Sunburst charts.

For more information regarding treemap and sunburst charts, please review the following resources:

<ul>
	<li>Create a treemap chart in Office 2016: <a href="https://support.office.com/en-us/article/Create-a-treemap-chart-in-Office-2016-dfe86d28-a610-4ef5-9b30-362d5c624b68">https://support.office.com/en-us/article/Create-a-treemap-chart-in-Office-2016-dfe86d28-a610-4ef5-9b30-362d5c624b68</a></li>
	<li>Create a sunburst chart in Office 2016: <a href="https://support.office.com/en-us/article/Create-a-sunburst-chart-in-Office-2016-4a127977-62cd-4c11-b8c7-65b84a358e0c?ui=en-US&amp;rs=en-US&amp;ad=US">https://support.office.com/en-us/article/Create-a-sunburst-chart-in-Office-2016-4a127977-62cd-4c11-b8c7-65b84a358e0c?ui=en-US&amp;rs=en-US&amp;ad=US</a></li>
	<li>Breaking down hierarchical data with Treemap and Sunburst charts: <a href="https://blogs.office.com/2015/08/11/breaking-down-hierarchical-data-with-treemap-and-sunburst-charts/">https://blogs.office.com/2015/08/11/breaking-down-hierarchical-data-with-treemap-and-sunburst-charts/</a></li>
</ul>

CloudSwyft Hands-On Lab 7

Upon reviewing the growth report you created, (download the <a href="https://github.com/MicrosoftLearning/Introduction-to-Data-Analysis-using-Excel/raw/master/Module7/Lab7Start%20v5.xlsx">file</a> here), Lucy asked for a report that shows composition of Product Categories and Sub Categories based on certain filters, including Year, Country, Customer Gender, and Age Group.

Specifically, Lucy wants to see the report visualized using a hierarchical chart.

Let's add another sheet to the workbook and name it Composition.

Start by adding a new pivot table with Year, Country, Customer Gender, and Age Group as Filters, Product Category and Sub Category as Rows, and Sum of Revenue as Values.

Format the pivot table to show in Tabular form, do not show subtotals and grand totals, and repeat all item labels.

---EXCEL 2016 Users---

Important Note: Treemap and Sunburst charts are only available in Excel 2016. If you are not on Excel 2016, skip the following instructions and jump to the next "Important Note" section.

Now, copy the structure of the rows and columns to the cells next to the pivot table, that is the Product categories and Sub categories as Rows. Delete the data area for now.

Populate the data area of the newly created cross tabular structure using SUMIFS() function. 
Assuming that you have:

<ul>
	<li>
	<ul>
		<li>F7: Product Category eg. "Accessories"</li>
		<li>G7: Sub Category eg. "Bike Racks"</li>
		<li>C1: Year filter</li>
		<li>C2: Country Filter</li>
		<li>C3: Customer Gender Filter</li>
		<li>C4: Age Group Filter</li>
	</ul>
	</li>
</ul>

Then, the cell that corresponds the Product Category Accessories and Sub Category Bike Racks would have the following formula:

<pre>
<code>=SUMIFS(SalesTable[Revenue], 
SalesTable[Product Category],$F7, 
SalesTable[Sub Category],$G7, 
SalesTable[Year],IF($C$1="(All)","&gt;0",$C$1), 
SalesTable[Country],IF($C$2="(All)","*",$C$2), 
SalesTable[Customer Gender],IF($C$3="(All)","*",$C$3), 
SalesTable[Age Group],IF($C$4="(All)","*",$C$4)) 
</code></pre>

Once your table is populated, remove the fields from the Rows, Columns, and Values of the Pivot table, so that only the filters remains. Align the filters with your cross tabular report. Create a treemap or sunburst chart based on the data in the cross tabular report. Name the chart "Sales Composition".

---NON EXCEL 2016 Users---

Important Note: Treemap and Sunburst charts are only available in Excel 2016. If you are not on Excel 2016, the instructions below will help you to analyze hierarchical data using pivot tables instead. If you are on Excel 2016, go ahead and solve the Lab questions.

With your pivot table, sort the Sub Category column, by Descending order of Sum of Revenue. This way, whenever you change the filter and the sub category composition changes, they will still be presented in descending order within the product category.

Now, let's show percentages of a sub category within the product category.

Add an extra column next to the Sum of Revenue column, by adding another Sum of Revenue as Values. Go to Value Field Settings and give the column title "% of Product Category". Go to Show Value As tab and select % of Parent Row Total.

Lab 7

Reporting Hierarchical Data

In this step, you'll be introduced to multi-table pivot tables. You'll learn the process of using the 'data model' to import data into Excel from different data sources.

For more information regarding importing data, please review the following resources:

<ul>
	<li>Connect an Access database to your workbook: <a href="https://support.office.com/en-us/article/Connect-an-Access-database-to-your-workbook-a3d6500c-4bec-40ce-8cdf-fb4edb723525">https://support.office.com/en-us/article/Connect-an-Access-database-to-your-workbook-a3d6500c-4bec-40ce-8cdf-fb4edb723525 </a></li>
	<li>Tutorial: Import Data into Excel, and Create a Data Model: <a href="https://support.office.com/en-us/article/Tutorial-Import-Data-into-Excel-and-Create-a-Data-Model-4b4e5ab4-60ee-465e-8195-09ebba060bf0">https://support.office.com/en-us/article/Tutorial-Import-Data-into-Excel-and-Create-a-Data-Model-4b4e5ab4-60ee-465e-8195-09ebba060bf0 </a></li>
</ul>

And to learn more about VLOOKUP(), check out this resource:

<ul>
	<li>VLOOKUP function: <a href="https://support.office.com/en-us/article/VLOOKUP-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1">https://support.office.com/en-us/article/VLOOKUP-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1 </a></li>
</ul>

CloudSwyft Hands-On Lab 8

Lastly, Lucy wants to have more information about the customer demographics in addition to the already available age and gender. Since your data (download <a href="https://github.com/MicrosoftLearning/Introduction-to-Data-Analysis-using-Excel/raw/master/Module8/Lab8Start%20v5.xlsx" target="_blank" rel="noreferrer noopener">file here</a>) has Customer ID for each row, you can "connect" these rows with your customer demographics database. Your customer demographics is stored in an Access database, which you can download <a href="https://github.com/MicrosoftLearning/Introduction-to-Data-Analysis-using-Excel/raw/master/Module8/Demographics.mdb" target="_blank" rel="noreferrer noopener">here</a>.

Workaround: Alternatively, the customer demographics is also provided as a txt file <a href="https://github.com/MicrosoftLearning/Introduction-to-Data-Analysis-using-Excel/raw/master/Module8/Customer_demographics.txt" target="_blank" rel="noreferrer noopener">here</a>, in case you could not work with Access database.

Important: If you are using Excel 2010, please download and install the Power Pivot from <a href="https://aka.ms/edx-dat205x-sspp" target="_blank" rel="noreferrer noopener">https://aka.ms/edx-dat205x-sspp</a>. You need Power Pivot in order to perform this lab. Remember to download the version according to your Excel (32 or 64 bit).

Lab 8

Introduction to multi-table pivot tables and the data model

Congratulations! You've completed the Introduction to Data Analysis using Excel course! 

We hope you've enjoyed the learning journey as much as we loved creating and delivering this course. We'd love to hear from you! Please take some time to leave a review for this course. We hope to see you again in future courses from Microsoft and Go1!

How to Use Microsoft Excel for Data Analysis

What you'll learn

Explore more technology skills