hello everyone welcome back to my
channel. I'm VIJAY THAPA and in this channel
I usually share programming tutorials and tech tips.
And in this episode i will show you
How you can do data visualization using Microsoft Excel. So without further ado
Let's move to our tutorial.
So, what and why data visualization?
Simply, Data visualization is representation of data in graphical form.
Over the period of time you might have
collected bunch of data in
your Microsoft Excel or in your register
in rows columns numerical format (or other format) and
from this data it is really difficult to
find patterns trends and visualize them
and represent them in front of other people.
But this data can be visualized
into a graphical form which is more
easier to understand the data. And
convert them into information which will
help in better decision making and also
find the trends and pattern. So let's do
some data visualization in Maxo pixels
so first of all we'll need to have our
data. If you want to continue this
tutorial but do not have the data then
you can download the data from the link
below and we'll continue.
Let's open our
data in Microsoft Excel for this I am
using Microsoft Excel 2016, but you can do
this data visualization with earlier version of Microsoft Excel as well. So I'll
just go to my excel sheet which is clean-data.xlsx and open in Microsoft Excel.
You can either double-click on it or
right-click and open with Microsoft Excel
So, Here is 
our data, Basically this is a data of an
e-commerce system. First column is of
order ID, it is a unique ID generated by
the system. Second one is time and
date of product ordered and the third
column is of the customer city fourth
one is customer state, review score,
payment type, payment value, order
item quantity, price, freight value and
product category name and using this
data we will do some data visualization
and represent this data into different
pictorial or graphical format like bar chart, pie
chart and line chart. And we'll also try to
find the trend from those charts so the
first step is to convert these data into
a pivot table. Pivot Table is a feature
provided by Microsoft Excel to do data
visualization or some data analysis part.
To convert this data into pivot table,
Go to INSERT and click on Pivot table.
And here we will select new worksheet, so we
do not want to mess up with original
dataset. But we will generate the
pivot table in new worksheet. So select
the new worksheet and click on ok
So
this is our new pivot table.
Here we
can do data analysis using the pivot
table. Here we have filters columns
rows and values. So first I want to
perform data analysis of Freight Value by
Customer state. So I want to drag the
Customer State in our row and our Value
will be Freight values. So I'll just scroll and
drag-and-drop Freight value into values and
it has automatically identified as a SUM
calculation. But if you click on this 'i'
icon you can see different type of
calculations you can perform like sum,
count, average, max, min, etc.
For now I'm using SUM because I want to
calculate different values in different
customer states so this is custom state
just I'll click on it and change it to
customer state and this is SUM of our Freight Value.
So this data basically
represents the Total Freight Value for
different state so this is a numerical
data visualization but now we can
generate bar chart out of it. So I'll
just select the table and I'll go to
insert and here we can see our Pivot chart
so I'll just click on it and
It automatically generates a bar chart for
us, based on our data. so I'll just
maximize it, by clicking on corner and
dragging it. So here we have different
customer state and this is the total freight
value. so I'll just change its name from
'Total' to 'Total value by customer state'
so this state which is SP has the
highest right value and I think AC or
AP and RR has the lowest Freight value.
so like this we can find the trends and
patterns out of our data. So I'll just
change the name of it as well so it is
'Freight value by customer state' so this
is one of our data visualization.
Now let's do another data visualization. 
I'll go to our original data source
which is orders merged and now I will
try to find any other trends or patterns
based on
total orders by product review so I just
want to find if there's any relationship
between review score of the product and
product ordered by the customer. so let's
go to insert and create another Pivot
tables. I'll click on Pivot table and as
before select the new worksheet and click
on OK
now we need to go to Pivot table
fields and so we are trying to find the
relationship between Review score and
product ordered quantity. so I'll just
drag review score in our rows and In
our values drag order item quantity
and yes I need a sum of Order item
quantity so this is our data so it has
automatically calculated the sum of
total product order to based on the
product Review score
I will just change its header to product
review Score that's it. Now for this
data I want to generate a pie chart
because there are only 5 rows so it will
better to visualize it into a pie chart
so I'll just click on this Pivot table
go to insert and go to Pivot chart
and the default chart in Pivot table
is Bar charts but we can change into
different other Charts. For that, just
right click on the chart and you can
select on change chart type and I'll
select pie chart and I'll go with 2d pie
chart the first one so this is the pie
chart based on the product Review score and
some of the order item quantity.
Here I will just maximize the
chart and here we can see the maximum
number of orders are
from the product whose Review score is five (5)
and the second one yellow shows 19% of
product order are from the product whose
Review score is four (4) and the least is the
product whose Review score is 2. So, I'll change
the heading. so this is another data
analysis or data visualization using
Microsoft Excel for data visualization
and by the table let's let's check out
another chart where we will visualize data of
total sales made over the period of time.
Before that I'll just change the heading
of this sheet into 'product ordered by
review' and then I'll go to the original
data set and I'll just create it into a
Pivot table, new worksheet. OK. Now
let's find total sales over the period
of time. So for this I will drag order
purchase time stamp in two rows and then
it automatically categorizes the time
stamp into year and quarter. And for
values we will use payment value and we do
not need to count instead of count we
need to do SUM, so I'll just click on
this icon and select sum and click OK
so from this table we can see that total
payment or total sales made during
the different quarter of different years
like in 2016 fourth quarter October the
total sales done (made) was offered 4603 6.41
in December it was 19.62
Similarly in 2017 first quarter
second quarter third quarter fourth
quarter and so on. Now let's convert this
data into a chart. For this I'll select
table and I will simply click on Pivot
chart and there is a shortcut but it
might not be available in
earlier version of Microsoft Excel.
So, you can go to insert and select Pivot
chart this is the Bar chart
so let's maximize it but for now I want
to use line chart instead of Bar Chart.
So, I'll Just right click on it and change
chart type and select line and I will
select one of these so here we can see
the total sales made over the period of
time in different quarter in different
months we can also minimize it and only
see the quarters or year in 2016 in third
quarter fourth quarter and if want to
see more detail and then we can click on
plus icon and it will show more
detailed chart for us. I will change its
heading it will be total sales by time
(year/quarter). Here also I'll change the
heading of the sheet as well 'sales by Time'
And similarly, I would like to show
more advanced chart you can generate or
more advanced visualization you can do
using Pivot table in Microsoft Excel. so I'll
go to orders merged and again create a
Pivot table from this data so go to
insert and click on Pivot table and as
before select new worksheet and click on OK
and in this advanced chart or advanced
data visualization I's like to find the
trend about 'payment  type used for making
payments in our e-commerce system over
the period of time'. So for that I will
drag first thing I need to drag in our
rows section is time stamp and then
payment type and then payment value will
be in our Values Section. I think I will
drag this payment type into our column
so it will be on top and we can see in
our different payment type if we drag
this here we cannot see in our column
section it will be inside the row
section inside quarters we have a
different payment method but this is a
little bit difficult to understand and
visualize as well so I'll make it as
colum so that we can see in our column
sections so there are different payment
type like Boleto, credit card, debit card
unknown and Voucher.
And different payment
type used in different year and
different quarter's can be seen in this
data but now I want to generate a chart
bar chart out of this data so I'll just
select the data and go to insert and
click on Pivot chart and I'd like to
use bar chart for this one so I'll just
maximize it like this
I don't need another one so I'll
close it. So this is the chart for the
relationship between different payment
method use for making payments in
different years and quarters, here we can
see the mostly used payment method
or payment type is credit card and the
least is I think Debit card. Voucher
is used quite often, Boleto is also used
quite of Often, so like this we can
identify the patterns and trends from
the data you have quality over the
period of time by using a simple tool as
Microsoft Excel so this is it guys if
you like this video then don't forget to
LIKE, SHARE and SUBSCRIBE, and if you have
any confusion related to this video
Then, don't forget to comment, I'll reply
as soon as possible so once again thank you
so much for watching
