data analysis and visualization reporting in excel · data analysis and visualization reporting in...
TRANSCRIPT
Data Analysis and Visualization Reporting in Excel
Richard B. Ali, CPA, CMA
Senior Accountant – Sidra Medicine
Microsoft Professional in Data Analysis
AGENDA
1 BASIC DATA ANALYSIS IN EXCEL
• Creating Charts
• Excel Tables
• Pivot Tables and Pivot Charts
• Slicers
• Common Data Analysis Functions In Excel
2 DATA VISUALISATION IN EXCEL
• Dashboard
• Case Studies
• Data Visualisation Concepts
• Tools
3 WHERE DO YOU GO FROM HERE?• CMA• Data Analytics, Data Scientist
4 Q & A
Basic Data Analysis in Excel
AGENDA
1 BASIC DATA ANALYSIS IN EXCEL
• Creating Charts
• Excel Tables
• Pivot Tables and Pivot Charts
• Slicers
• Common Data Analysis Functions In Excel
2 DATA VISUALISATION IN EXCEL
• Dashboard
• Case Studies
• Data Visualisation Concepts
• Tools
3 WHERE DO YOU GO FROM HERE?• CMA• Data Analytics, Data Scientist
4 Q & A
Lab 1: Creating Charts
You are the finance business partner 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.
Now, it's your job to present this data in a
compelling manner to Lucy.
www.edx.org
Lab 1: Creating Charts
1. Create a Line Chart for Yearly
Category Revenue
a) Add a total Revenue on each
year column
2. Create a Pie Chart for Revenue
by Category
a) Change to %
3. Create a Clustered Bar Chart for
Revenue by Country
a) Sort Highest/Lowest
www.edx.org
Lab 1: Creating Charts
Demo
https://support.office.com/en-us/article/create-a-chart-from-start-to-finish-0baf399e-dd61-4e18-8a73-b3fd5d5680c2?ui=en-US&rs=en-
US&ad=USwww.edx.org
Lab 2: Excel Tables
Continuing on the story…
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.
Your IT Analyst provided you with a different data source.
This time the data has more than one hundred thousand
rows.
Before you can create additional reports to Lucy, first you
need to prepare the data.www.edx.org
Lab 2: Excel Tables
1. Convert Data Into an Excel Table
• Features of a Table
2. Add total row, filter the data (e.g. USA).
3. Add Columns
• Year (YEAR Function)
• Month (TEXT Function)
• Age Group (IF Functions)
Youth (<25)
Young Adults (25-34)
Adults (35-64)
Seniors (>64)
• Frame Size (IF and RIGHT functions)
• Use a combination of the IF() and RIGHT()
functions to derive the frame size of a
bicycle from the last two characters of
the Product column, when the Product
Category is Bikes. Otherwise, leave it
blank.
• Profit (Revenue - Cost) www.edx.org
Lab 2: Excel Tables
REFERENCES:
For more information regarding Excel tables, please review the following resources:
Overview of Excel tables: https://support.office.com/en-us/article/Overview-of-Excel-tables-7ab0bb7d-3a9e-4b56-a3c9-
6c94334e492c
Using structured references with Excel tables: https://support.office.com/en-us/article/Using-structured-references-with-
Excel-tables-f5ed2452-2337-4f71-bed3-c8ae6d2b276e
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: https://support.office.com/en-us/article/SUBTOTAL-function-7b027003-f060-
4ade-9040-e478765b9939
For more information about the IFS() function, please review the following resource:
https://support.office.com/en-us/article/ifs-function-36329a26-37b2-467c-972b-4a39bd951d45
For more information about the IF() function, please review the following resource:
The IF function: https://support.office.com/en-US/article/IF-function-69AED7C9-4E8A-4755-A9BC-AA8BBFF73BE2
Advanced IF functions: https://support.office.com/en-us/article/Advanced-IF-functions-17341cef-510d-4d60-a657-
913f878fdacf
Lab 3: Pivot Table and Pivot Charts
Continuing the story…
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.
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.
www.edx.org
Lab 3: Pivot Table and Pivot Charts
Pivot Tables
1. Name the Excel Table (salestable)
2. Create Pivot table on Revenue by Product Category, Subcategory BY Year
• 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.
3. Create Pivot table on Revenue by Country and States
• Arrange the layout so that the pivot table displays the Country and State in
the Rows, Year in the Columns, and Revenue (Sum of) as the Values. Sort the
pivot table by Sum of Revenue so that the Country and State with the
highest revenue is displayed first.
4. Create Pivot table on Revenue by Frame Size
• Display the Frame Size in the Rows and Revenue (Sum of) as the Values.
Hide the rows that do not have a Frame size (blank Frame size), then sort the
pivot table by Sum of Revenue so that the Frame size with the highest
revenue is displayed first.
5. Create Pivot table on Revenue by Age Group
• Add Age Group as the Rows and Revenue (Sum of) as the Values. Sort the
pivot table by Sum of Revenue so that the Age Group with the highest
revenue is displayed first.
www.edx.org
Lab 3: Pivot Table and Pivot Charts
Pivot Charts
1. Pivot table on Revenue by Product Category, Subcategory BY Year
• Select a Column chart to display the yearly sales by category so that the
years are together.
2. Pivot table on Revenue by Country and States
• 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.
• Switch Row/Column in Select Data Source
• Collapsing the Country fields in order to hide the States fields.
3. Pivot table on Revenue by Frame Size
• Select a Bar chart to display the order of revenue by Frame size. Sort the
Y axis to show the Frame size that has the highest revenue on the top.
Hint: Select the Categories in reverse order option in the Axis Options.
4. Pivot table on Revenue by Age Group
• Select a Pie chart to display the proportion of each Age
Group (remember the chart styles) with data labels, formatted to two
decimal points.www.edx.org
Lab 3: Pivot Table and Pivot Charts
REFERENCES:
For more information about PivotTable and PivotChart, please review the following resources:
Overview of PivotTable and PivotChart reports: https://support.office.com/en-us/article/Overview-of-
PivotTable-and-PivotChart-reports-527c8fa3-02c0-445a-a2db-7794676bce96
Create a Pivot Chart (separate Pivot Charts were first available in Excel 2013): https://support.office.com/en-
us/article/Create-a-PivotChart-c1b1e057-6990-4c38-b52b-8255538e7b1c
Sort data in a PivotTable: https://support.office.com/en-US/article/Sort-data-in-a-PivotTable-E41F7107-
B92D-44EF-861F-24430830450A
www.edx.org
Lab 4: Slicers
SLICER
1. Add Country SLICER
2. Add Year SLICER
3. Add Product Category SLICER
4. Report Connections
5. Custom Sort
• Go to DATA SORT CUSTOM SORT
6. Create New Pivot Table – Year/Month Revenue
7. Slicer, Filter, Sort, Select
For more information regarding Slicers on Pivot Tables, please review the following resources:
Use slicers to filter PivotTable data: https://support.office.com/en-us/article/Use-slicers-to-filter-PivotTable-
data-249F966B-A9D5-4B0F-B31A-12651785D29D www.edx.org
Common Data Analysis Functions In Excel
1. CONCATENATE
2. LEN
3. COUNTA
4. DAYS/NETWORKDAYS
5. SUMIFS
6. AVERAGEIFS
7. VLOOKUP
8. FIND/SEARCH
9. IFERROR
10. COUNTIFS
11. LEFT/RIGHT
12. RANK
13. MINIFS
14. MAXIFS
15. SUMPRODUCT
https://excelwithbusiness.com/blog/15-excel-data-analysis-functions-you-need-to-know/
Common Data Analysis Functions In Excel
CONCATENATE
https://excelwithbusiness.com/blog/15-excel-data-analysis-functions-you-need-to-know/
=CONCATENATE is one of the easiest to learn
but most powerful formulas when conducting
data analysis. Combine text, numbers, dates
and more from multiple cells into one
Common Data Analysis Functions In Excel
LEN
https://excelwithbusiness.com/blog/15-excel-data-analysis-functions-you-need-to-know/
=LEN quickly provides the number of
characters in a given cell. As in the example
above, you can identify two different kinds of
product Stock Keeping Units (SKUs) using the
=LEN formula to see how many characters the
cell contains.
Common Data Analysis Functions In Excel
COUNTA
https://excelwithbusiness.com/blog/15-excel-data-analysis-functions-you-need-to-know/
=COUNTA identifies whether a cell is empty or
not. In the life of a data analyst, you’re going
to run into incomplete data sets daily.
COUNTA will allow you to evaluate any gaps
the dataset might have without having to
reorganize the data.
Common Data Analysis Functions In Excel
DAYS/NETWORKDAYS
https://excelwithbusiness.com/blog/15-excel-data-analysis-functions-you-need-to-know/
=DAYS is exactly what it implies. This function
determines the number of calendar days
between two dates.
=NETWORKDAYS is slightly more robust and
useful. This formula determines the number of
“workdays” between two dates as well as an
option to account for holidays.
Common Data Analysis Functions In Excel
SUMIFS
https://excelwithbusiness.com/blog/15-excel-data-analysis-functions-you-need-to-know/
=SUMIFS is one of the “must know” formulas
for a data analyst. The common formula used
is =SUM, but what if you need to sum values
based on multiple criteria? SUMIFS is it. In the
example below, SUMIFS is used to determine
how much each product is contributing to
top-line revenue.
Common Data Analysis Functions In Excel
AVERAGEIFS
https://excelwithbusiness.com/blog/15-excel-data-analysis-functions-you-need-to-know/
Much like SUMIFS, AVERAGEIFS allows you
to take an average based on one or more
criteria.
Common Data Analysis Functions In Excel
VLOOKUP
https://excelwithbusiness.com/blog/15-excel-data-analysis-functions-you-need-to-know/
VLOOKUP is one of most useful and
recognizable data analysis functions. As an
excel user, you’ll probably need to “marry”
data together at some point. For example,
accounts receivable might know how much
each product costs, but the shipping
department can only provide units shipped.
This is the perfect use case for VLOOKUP.
Common Data Analysis Functions In Excel
FIND/SEARCH
https://excelwithbusiness.com/blog/15-excel-data-analysis-functions-you-need-to-know/
=FIND/=SEARCH are powerful functions for
isolating certain text within a data set. Both
are listed here because =FIND will return a
case-sensitive match, i.e. if you use FIND to
query for “Big” you will only return Big=true
results. But a =SEARCH for “Big” will match
with Big or big, making the query a bit
broader. This is particularly useful for looking
for anomalies or unique identifiers
Common Data Analysis Functions In Excel
IFERROR
https://excelwithbusiness.com/blog/15-excel-data-analysis-functions-you-need-to-know/
=IFERROR is something that any analyst who
actively presents data should take advantage
of. Using the previous example, looking for
specific text/values in a dataset won’t return a
match. This causes a #VALUE error, and while
harmless, it is distracting and an eyesore.
Use =IFERROR to replace the #VALUE errors
with any text/value.
Common Data Analysis Functions In Excel
COUNTIFS
https://excelwithbusiness.com/blog/15-excel-data-analysis-functions-you-need-to-know/
=COUNTIFS is the easiest way to count the
number of instances a dataset meets a set of
criteria. In the example above the product
name is used to determine which product was
the best seller. COUNTIFS is powerful because
of the limitless criteria you can input.
Common Data Analysis Functions In Excel
LEFT/RIGHT
https://excelwithbusiness.com/blog/15-excel-data-analysis-functions-you-need-to-know/
=LEFT, =RIGHT are simple and efficient
methods for extracting static data out of cells.
=LEFT will return the “x” number of characters
from the beginning of the cell, while =right
will return the “x” number of characters from
the end of the cell. In the example below,
=LEFT is used to extract the consumers area
code from their phone number, while =RIGHT
is used to extract the last 4 digits.
Common Data Analysis Functions In Excel
RANK
https://excelwithbusiness.com/blog/15-excel-data-analysis-functions-you-need-to-know/
=RANK is an ancient excel function but that
doesn’t downplay its effectiveness for data
analysis. =RANK allows you to quickly denote
how values rank in a dataset in ascending or
descending order. In the example, RANK is
being used to determine which clients
order the most product.
Common Data Analysis Functions In Excel
MINIFS
https://excelwithbusiness.com/blog/15-excel-data-analysis-functions-you-need-to-know/
=MINIFS is very similar to the min function
except it allows you to take the minimum of a
set of values, and match on criteria as well. In
the example, =MINIFS is used to find the
lowest price each product sold for.
Common Data Analysis Functions In Excel
MAXIFS
https://excelwithbusiness.com/blog/15-excel-data-analysis-functions-you-need-to-know/
=MAXIFS like its counterpart minifs, allows
you to match on criteria, but this time it looks
for the maximum number.
Common Data Analysis Functions In Excel
SUMPRODUCT
https://excelwithbusiness.com/blog/15-excel-data-analysis-functions-you-need-to-know/
=SUMPRODUCT is a great function to
calculate average returns, price points, and
margins. SUMPRODUCT multiples one range
of values by its corresponding row
counterparts. It’s data analysis gold. In the
example below, we calculate the average
selling price of all our products by using
sumproduct to times Price by Quantity and
then divide by the total volume sold.
Data Visualisation in Excel
AGENDA
1 BASIC DATA ANALYSIS IN EXCEL
• Creating Charts
• Excel Tables
• Pivot Tables and Pivot Charts
• Slicers
• Common Data Analysis Functions In Excel
2 DATA VISUALISATION IN EXCEL
• Dashboard
• Case Studies
• Data Visualisation Concepts
• Tools
3 WHERE DO YOU GO FROM HERE?• CMA• Data Analytics, Data Scientist
4 Q & A
Lab 4: Dashboard
Continuing the story….
You have created several pivot tables and pivot charts for Lucy.
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:
• Year
• Country
• Customer Gender
• Age Group
• Product Category
• Sub Category
• Frame sizewww.edx.org
Lab 5: Dashboard
1. Start by adding a new sheet named Dashboard.
2. Move the Charts to the New Sheet
• Align The Snap to Grid option located at the Align Object on the Page Layout tab.
• Add Chart Titles (Design Add Chart Element)
Yearly Sales by Country
Yearly Sales by Category
Sales by Frame Size
Sales by Age Group
3. Add slicers to the Dashboard and arrange appropriately. 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.
4. Connect the slicers to the charts. PivotChart Analyze tab Filter Connections
• Connect the Yearly Sales by Country chart to all slicers, except the Year slicer.
• Connect the Yearly Sales by Category chart to the Age Group, Country, Customer Gender,
and Frame Size slicers.
• Connect the Sales by Frame Size chart to all slicers, except the Frame Size slicer.
• Connect the Sales by Age Group chart to all slicers, except the Age Group.
5. Clear the Gridlines on the Dashboard. View tab Untick Gridlineswww.edx.org
Lab 5: Dashboard
6. A quick glance on the Yearly Sales by Country shows that Australia has an unusual trend compared to
the other countries. Which year does Australia have the least sales (revenue)??
7. Create an additional pivot chart to show Sales by Country using Pie chart. Show percentages for each
slice of the pie and connect the chart to all the slicers, except the Country slicer. Overall, Australia
commands 25% of the company's total sales. But in some of the years, this proportion changes.
What is the percentage of Australia sales (of total sales) in the year that it has the least sales (previous
question)?
8. Let's filter the charts by Australia using the Country slicer. What might be the cause of this trend?
9. Create an additional pivot chart to show Sales by Category using a Pie chart. Show percentages for
each slice of the pie and connect the chart to all the slicers, except the Category slicer.
Filter the charts by Australia using the country slicer and play around with the Year filter. Notice for
different years, the changes in composition of Australia's sales by Category.
Based only on years where all three categories have sales (there are three slices in the pie) in Australia,
which year does the Bikes category have the lowest proportion of sales?
What is the percentage of Bikes sales (of total sales) in that year? www.edx.org
Lab 5: Dashboard
10. 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.
How would you describe the proportion of sales by customer gender?
Which Bike's frame size is more popular for each Gender?
11. What about Customer Gender vs age group? Right now the Sales by Age Group chart does not
differentiate by Gender. Modify this chart to be a Column chart. Show the Customer Gender side-by-
side for each age group. Ensure that the chart is connected to all slicers. Last but not least, sort the Age
Group appropriately.
Hint: Clear any existing filter, place Customer Gender as Legend and create Custom List to sort the Age
Group.
In Australia, which Age Group has more sales revenue to females than to males?
www.edx.org
Case Studies
AP Unprocessable Report
Case Studies
Nepal Earthquake Damage Assessment Report
Data Visualisation Concepts
Data Visualisation Concepts
Data Visualisation Concepts
Data Visualisation Concepts
Data Visualisation Concepts
Purpose of Visualization
Our role is to make it delicious for users.
Both a science and an art.
www.edx.org
Data Visualisation Concepts
REFERENCES:
For further reading about luminaries in Visualization, explore the following:
Edward Tufte - https://www.edwardtufte.com/tufte/
Hans Rosling - https://en.wikipedia.org/wiki/Hans_Rosling
Amanda Cox - https://www.nytimes.com/by/amanda-cox
Stephen Few - http://perceptualedge.com/about.php
Colin Ware - http://ccom.unh.edu/vislab/colin_ware.html
There are also a variety of visualizations on interesting topics here:
https://toplink.weforum.org/knowledge/explore
To get inspired about what's possible with Data Visualization, it's great to see examples - Power BI's Data
Stories Gallery is an excellent place to start: https://community.powerbi.com/t5/Data-Stories-Gallery/bd-
p/DataStoriesGallery
Tools:
https://towardsdatascience.com/top-5-bi-tools-that-you-must-use-for-data-visualization-7ccc2a852bd3
https://financesonline.com/data-visualization/
Data Visualisation Concepts
Data Visualisation Concepts
Data Visualisation Concepts
Tools
Data Visualisation has transcended into Business Intelligence…
WHERE DO YOU GO FROM HERE?
AGENDA
1 BASIC DATA ANALYSIS IN EXCEL
• Creating Charts
• Excel Tables
• Pivot Tables and Pivot Charts
• Slicers
• Common Data Analysis Functions In Excel
2 DATA VISUALISATION IN EXCEL
• Dashboard
• Case Studies
• Data Visualisation Concepts
• Tools
3 WHERE DO YOU GO FROM HERE?• CMA• Data Analytics, Data Scientist
4 Q & A
Where Do You Go From Here
www.imanet.org
Where Do You Go From Here
https://www.datapine.com/blog/data-science-vs-data-analytics/
Data Science versus Data Analytics
Data science is an umbrella term for a more
comprehensive set of fields that are focused on
mining big data sets and discovering innovative
new insights, trends, methods, and processes.
Data analytics is a discipline based on gaining
actionable insights to assist in a business’s
professional growth in an immediate sense. It is
part of a wider mission and could be
considered a branch of data science.
“Information is the oil of the 21st century, and
analytics is the combustion engine.” – Peter
Sondergaard, former Senior VP of Gartner
Where Do You Go From Here
https://digitaldefynd.com/best-business-analytics-certification-course-training/