UNIT: 2 – DATA VISUALIZATION AND DESCRIPTIVE STATISTICS USING EXCEL

[A] PLOTTING THE GRAPSH IN EXCEL:

Introduction:
Visualizing data is a key part of effective analysis, and Excel offers a wide range of tools to help you to present
information clearly. Excel charts transform raw data into clear, visual representations, enabling us to analyse
trends, compare datasets and communicate insights effectively. Charts visually represent data, making
complex information easier to interpret. Understanding the different types of charts in Excel can help you
choose the best way to display your data based on your goals and audience.

Chart types and when to use them:

Chart Type When to Use

Compare values across a few categories, shown
Bar Charts
horizontally
Useful for showing proportions within a whole.
Pie Charts Quantify them and display them as percentage.

Ideal for displaying relationships between variables.

Scatter Charts
Show correlations between two sets of values.
If we want to create small charts that are easy to
embed into cells and help visualize data trends, Line
and Column Sparklines in Microsoft Excel are a great
Sparkline Charts
option. They are a simple and effective way to
display data without taking up too much space on
your spreadsheet.

How to Create a chart:

1. Select data for the chart.
Press CTLR + SHIFT + Down Arrow to select
all cells to the last contiguous data.
2. Select Insert > Recommended Charts.
3. Select a chart on the Recommended
Charts tab, to preview the chart.
You can also use Recommended Charts tab to see Excel’s suggestion for selection of chart.
4. Select a chart.
5. Select OK.

Change Chart type:

You can easily change to a different type of chart
at any time.
1. Select the chart.

T.Y. BBA SEM: 6

 2. On the Chart Design tab, in the Type group, click Change Chart Type.
3. On the left side, select appropriate chart.
4. Click OK.

Legend Position:
To move the legend to the right side of the chart, execute the
following steps.

1. Select the chart.

2. Click the + button on the right side of the chart, click the
arrow next to Legend and click Right.

Data labels:
You can use data labels to focus your readers' attention on a single
data series or data point.

1. Select the chart.

2. Click the + button on the right side of the chart and click the
check box next to Data Label.

T.Y. BBA SEM: 6

What is Bar Chart?
A bar chart is a graphical representation used to display and compare discrete categories of data through
rectangular bars, where the length or height of each bar is proportional to the frequency or value of the
corresponding category.

Here is a detailed look at the integral parts of a bar chart:

Title:
The title is the explanatory heading of the bar chart. It describes what the chart represents, aiding readers in
understanding the purpose of the chart instantly. It usually sits at the top, is easily viewable, and should be
clear, concise, and descriptive.

Bars:
The bars are the main components of the chart. They represent categories or groups of data. Each bar's
height (or length, in the case of a horizontal bar chart) represents the frequency or the quantity of the
category it represents. The space between the bars is usually equal to demonstrate accurate comparisons.

X & Y Axes:
The bar chart is divided into two axes – X, the horizontal axis, and Y, the vertical one.
 The X-Axis (Horizontal Axis): This is the numerical scale that represents the quantities or frequencies
associated with each category. The length (or height) of the corresponding bar indicates this value.

 The Y-Axis (Vertical Axis): It's often where the different categories are represented. In the case of a
time-series bar chart, this axis represents the timeline.

Legend:
In some bar charts, especially where multiple datasets are being compared, a legend communicates what
each bar represents. It uses colours, patterns, or textures and correlating labels to differentiate between data
sets.

T.Y. BBA SEM: 6

Labels:
Labels detail what each axis and bar represent. They show the values of individual bars. The Y-axis is generally
labelled with the categories or groups while the X-axis is labelled with numerical values, in question.

What is Pie Chart?

A pie chart is a type of graph representing data in a circular form, with each slice of the circle representing a
fraction or proportionate part of the whole. All slices of the pie add up to make the whole equalling 100
percent and 360 degrees.

A pie chart is used to:

 Show parts-to-whole relationship

 Demonstrate each element’s contribution to the whole

 Compare the contribution of different categories to the whole

Title:
The title is the explanatory heading of the pie chart. It describes what the chart represents and helps readers
quickly understand the subject of the data. It is usually placed at the top and should be clear, concise, and
descriptive.

Slices:
The slices are the main components of a pie chart. Each slice represents a category or part of the whole. The
size (angle or area) of each slice is proportional to the value or percentage it represents. All slices together
make up 100% of the data.

T.Y. BBA SEM: 6

Pie:
The entire circle of the pie chart represents the whole dataset or total value (100%). Individual slices show
how each category contributes to this total.

Legend:
A legend is used to explain what each slice represents, especially when different colours or patterns are
used. It helps the viewer easily match slices with their corresponding categories.

Labels:
Labels provide information about each slice, such as the category name and its value or percentage. These
labels may appear inside the slices or outside the pie with connecting lines for clarity.

What is Scatter Chart?

A scatter chart, commonly referred to as a scatter plot, is a graphical representation used to explain the
relationship between two continuous variables within a dataset. This visual tool employs a Cartesian
coordinate system, where each data point is symbolized by a marker on a two-dimensional plane.

Title:
The title explains what the scatter plot represents. It gives a quick idea about the relationship being studied
between two variables.

Data Points:
A scatter chart comes to life with the plotting of data points. Each data point represents a unique pair of
values – one from the independent variable and the other from the dependent variable. These data points
are visualized on the chart as markers and occupy a specific position determined by their corresponding
values along the X and Y axes.

T.Y. BBA SEM: 6

X & Y Axes:
The horizontal axis (X-axis) signifies the values of one variable, often referred to as the independent variable.
The vertical axis (Y-axis) represents the values of the other variable, known as the dependent variable.

Scale:
The scale on both axes should be uniform. It helps in accurate plotting and easy interpretation of the
relationship.

Pattern:
The fundamental purpose of a scatter chart is to capture the relationships between the two variables. Within
the patterns or correlations found on a scatter plot, several distinct features emerge, each providing insight
into the nature of the relationship. The overall pattern of points shows the type of relationship between
variables—positive, negative, or no correlation.

Types of Correlation:
I. Perfect positive correlation
II. Perfect negative correlation
III. Partial positive correlation
IV. Partial negative correlation
V. NO correlation

What sparklines are?

A sparkline is a tiny chart in a worksheet cell that provides a visual representation of data. Use sparklines to
show trends in a series of values, such as seasonal increases or decreases, economic cycles, or to highlight
maximum and minimum values. For best impact, position a sparkline near its data.

T.Y. BBA SEM: 6

How to insert sparklines in Excel:
To create a sparkline in Excel, perform these steps:
1. Select a blank cell where you want to add a sparkline, typically at the end of a row of data.
2. On the Insert tab, in the Sparklines group, choose the desired type: Line, Column or Win/Loss.
3. In the Create Sparklines dialog window, put the cursor in the Data Range box and select the range
of cells to be included in a sparkline chart.
4. Click OK.

More rows of data? Drag handle to add a Sparkline for each row.

T.Y. BBA SEM: 6

Format a Sparkline chart:
1. Select the Sparkline chart.

2. Select Sparkline and then select an option.

 Select Line, Column, or Win/Loss to change the chart type.

 Show markers and highlight specific data points.

1. High Point – highlights the maximum value in a sparkline.

2. Low Point – highlights the minimum value in a sparkline.
3. Negative Points - highlights all negative data points.
4. First Point – shades the first data point in a different color.
5. Last Point – changes the color of the last data point.
6. Markers – adds markers at each data point. This option is only available for line
sparklines.
 Select a Style for the Sparkline.

 Select Sparkline Color and the color.

 Select Sparkline Color > Weight to select the width of the Sparkline.

 Select Marker Color to change the color of the markers.

 If the data has positive and negative values, select Axis to show the axis.

 To group sparklines, Select two or more mini charts.

 On the Sparkline tab, click the Group button.

 To ungroup sparklines, select them and click the Ungroup button.

T.Y. BBA SEM: 6

Pivot Tables in Excel:
A PivotTable is an extremely powerful tool that you can use to slice and dice data. You can track and analyse
hundreds of thousands of data points with a compact table that can be changed dynamically to enable you
to find the different perspectives of the data. It is a simple tool to use, yet powerful.

The major features of a PivotTable are as follows −

 Creating a PivotTable is extremely simple and fast

 Enabling churning of data instantly by simple dragging of fields, sorting and filtering and different
calculations on the data.

 Arriving at the suitable representation for your data as you gain insights into it.

 Ability to create reports on the fly.

 Producing multiple reports from the same PivotTable in a matter of seconds.

 Providing interactive reports to synchronize with the audience.

Creating a PivotTable from a Data Range:

To create a PivotTable from this data range, do the following −

1. Ensure that the first row has headers. You need headers
because they will be the field names in your PivotTable.

2. Select the data range.

3. Click the INSERT tab on the Ribbon.

4. On the Insert tab, in the Tables group, click PivotTable.

5. The following dialog box appears. Excel automatically selects the data for you. The default location
for a new pivot table is New Worksheet.

The PivotTable Fields list appears on the right side of the worksheet, containing the header names of the
columns in the data range. Further, on the Ribbon, PivotTable Tools ANALYZE and DESIGN appear.

.
T.Y. BBA SEM: 6
Fields represent the columns in your data range or Excel table, and will have check boxes. The selected fields
are displayed in the report. Areas represent the layout of the report and the calculations included in the
report. By default, this is not selected and whatever changes you make in the selection of fields or in the
layout options are reflected in the PivotTable instantly.

In case the PivotTable Fields Task Pane is not displayed, check the Ribbon for the following –

 Click the ANALYZE tab under PIVOTTABLE TOOLS on the Ribbon.

 Check if Fields List is selected (i.e. highlighted) in the Show group.
 If Fields List is not selected, then click it.

PivotTable areas are a part of PivotTable Fields Task Pane. By arranging the selected fields in the areas, you
can arrive at different PivotTable layouts. As you can simply drag the fields across areas, you can quickly
switch across the different layouts, summarizing the data, in a way you want.
There are four PivotTable areas available –

 ROWS:
If you select the fields in the PivotTable Fields lists by just
checking the boxes, all the nonnumeric fields will
automatically be added to the ROWS area, in the order you
select. You can optionally, drag a field to the ROWS area. The
fields that are put in ROWS area appear as rows in the
PivotTable, with the Row Labels being the values of the
selected fields.

 COLUMNS:
The fields that are put in COLUMNS area appear as columns
in the PivotTable, with the Column Labels being the values
of the selected fields.

 FILTERS:
The Filters area is to place filters in PivotTable.

 ∑ VALUES (Read as Summarizing Values):

The primary use of a PivotTable is to summarize values.
Hence, by placing the fields by which you want to summarize
the data in ∑ VALUES area, you arrive at the summary table.

Sorting on Fields:
You can sort the data in a PivotTable so that it will be easy for you to find the items you want to analyse. You
can sort the data from lowest to highest values or highest to lowest values or in any other custom order that
you choose.
 Click the arrow in the Row Labels.
The following sorting options are displayed −
 Sort A to Z.
 Sort Z to A.
 More Sort Options.
T.Y. BBA SEM: 6
Value Field Settings:
By default, Excel takes the summarization as sum of the
values of the field in ∑ VALUES area. However, you have
other calculation types, such as, Count, Average, Max, Min,
etc.

With Values Field Settings, you can set the calculation type
in your PivotTable. You can also decide on how you want to
display your values.

 Click on Field in ∑ VALUES area.

 Select Value Field Settings from the dropdown list.

The Value Field Settings dialog box appears.

[B] Descriptive Statistics:

Microsoft Excel is a powerful tool used for simple and advanced data analysis. It includes formulas and
the Data Analysis ToolPak for statistical computations.

Steps to Enable Data Analysis ToolPak

1. Go to File → Options → Add-ins.

2. Select Analysis ToolPak → click Go next to “Manage Excel Add-ins.”
3. Check Analysis ToolPak → click OK.
Now you’ll find Data Analysis under the Data tab

Measures and Function in Excel:

Measure Excel Function Description

Mean =AVERAGE(range) Calculates average of data values

Median =MEDIAN(range) Finds middle value when data is ordered

Mode =[Link](range) Finds most frequent value

Range =MAX(range)-MIN(range) Spread between highest and lowest values

Variance =VAR.S(range) Measures sample variability

Standard =STDEV.S(range) Measures dispersion around mean

Deviation

T.Y. BBA SEM: 6

Interpretation of Results:
 The mean shows central tendency or average.

 The median represents the center of the data unaffected by extreme values.

 The mode identifies the most frequent element.

 Variance and standard deviation describe data spread — a smaller value indicates more consistency.

 A larger range or variance indicates higher fluctuations in data.

Descriptive Statistics Using Data Analysis ToolPak:

The Data Analysis ToolPak provides a built-in feature to automatically compute multiple descriptive measures
at once — including mean, median, mode, standard deviation, count, range, minimum, maximum, and more.
It saves time and ensures accuracy.

Steps to Perform Descriptive Statistics via ToolPak:

1. Click Data → Data Analysis → Descriptive Statistics.

2. In the dialog box:

 Select the Input Range (your data area).

 Tick “Labels in first row” if column names

are included.

 Choose Output Range (where results will

appear).

 Check Summary Statistics.

3. Click OK — Excel will generate a table showing

mean, standard error, median, mode, variance,
kurtosis, skewness, range, minimum, maximum,
sum, and count.

t – Test in Excel:
A t-test is a statistical tool to check if the difference between the means of two groups is significant or just
due to chance, helping decide if they're truly different or similar, commonly used in hypothesis testing
with Student's t-distribution for small samples when population standard deviation is unknown. It calculates
a p-value (likelihood of the difference being random) to support or reject the null hypothesis (no difference).

T.Y. BBA SEM: 6

Types of t-Tests:
 One-Sample t-Test: Compares one sample mean to a known population mean (e.g., comparing a
class's average score to the national average).

 Independent Samples t-Test (Two-Sample): Compares means of two separate, unrelated groups
(e.g., test scores for Group A vs. Group B).

 Paired Samples t-Test (Dependent): Compares means from the same group at different times or
conditions (e.g., blood pressure before and after a treatment)

Procedure:
1. Go to Data → Data Analysis → t-Test (choose appropriate type).

2. Select:

 t-Test: Two-Sample Assuming Equal Variances (homogeneous samples).

 t-Test: Two-Sample Assuming Unequal Variances (heterogeneous samples).

3. Input both data ranges.

4. Set significance level (e.g., 0.05).

5. Click OK to view results.

Interpretation:

 Excel outputs the t-statistic and p-value.

 If p-value < 0.05, reject 𝐻0 (significant difference).

 If p-value ≥ 0.05, accept 𝐻0 (no significant difference).

T.Y. BBA SEM: 6

F – Test in Excel:
An F-test is a statistical hypothesis test that compares the variances of two or more samples or populations,
determining if they are significantly different, often using the ratio of sample variances (F-statistic = Variance
1 / Variance 2) to see if they come from the same population.

Procedure:
1. Go to Data → Data Analysis → F-Test Two-Sample for Variances.

2. Input both data ranges.

3. Set significance level (e.g., 0.05).

4. Click OK to view results.

Interpretation:

 Excel outputs the F-statistic and p-value.

 If calculated F > critical F → Reject 𝐻0 : variances are

unequal.
 If calculated F ≤ critical F → Accept 𝐻0 : variances
are not significantly different.

"I'm not gonna run away, I never go back on my word! That's my nindo: my ninja way!"
- NARUTO UZUMAKI

T.Y. BBA SEM: 6