Visualizing and Exploring Data

BCA Eight Semester, BCA487CO

Data Visualization
● the process of displaying data (often in large quantities) in a meaningful
fashion to provide insights that will support better decisions.
● Data visualization improves:
○ Decision-making
○ Provides managers with better analysis capabilities that reduce reliance on IT professionals.
○ Improves collaboration and information sharing
● what is portrayed in visualizations is the information and not the data.
● Visualizing data provides a way of communicating data at all levels of a
business and can reveal surprising patterns and relationships.
● It is easier for human brain to process visual information if presented
effectively.
● Data visualization is important both for building decision models and for
interpreting their results.
Key aspects of data visualization
● Graphical representation: Data is shown using visual elements like points, lines,
bars, and colors instead of just numbers.
● Pattern and trend identification: By seeing data visually, it becomes easier and
faster to spot trends, patterns, and outliers that might be hidden in a spreadsheet.
● Communication: It is a powerful communication tool, allowing people to tell a story
with data and share insights with a wider audience without confusion.
● Informed decision-making: By making data more accessible, it helps stakeholders
quickly understand the key takeaways and make better, data-driven decisions.
● Exploratory vs. Explanatory: Visualization can be used to explore data to find
patterns (exploratory) or to communicate known patterns to an audience
(explanatory).
Dashboards

● A dashboard is a visual representation of a set of key business measures.

● Dashboards provide important summaries of key business information to
help manage a business process or function.
● include tabular as well as visual data to allow managers to quickly locate key
data.
● Data dashboards are a summary of different, but related data sets.
● compile information from various sources into one screen, using
visualizations like charts, graphs, and tables to present data in a clear,
easy-to-digest format.
Tools and Software for Data Visualization
● Business intelligence platforms
○ Tableau
■ popular tool for creating a wide variety of charts and interactive dashboards, including
maps.
■ The public version of Tableau is free for people who want to make visualizations for
learning or sharing online.
○ Microsoft Power BI:
■ A tool that integrates well with Excel and allows users to create custom dashboards and
reports for web and mobile use.
■ Businesses can access interactive data visualizations in real-time.
■ It provides completely customizable dashboards.
○ Microsoft Excel
■ may seem basic but it’s actually very helpful for creating data visualizations that support
decision-making.
■ allow users to make charts, tables and more than 20 types of visuals.
■ Provides inbuilt formulas.
■ Offers automated program to filter or organize data.
● Specialized and open-source tools
○ [Link]:
■ A powerful JavaScript library for creating custom, interactive data visualizations for the
web using HTML, SVG, and CSS.
○ [Link]
■ helps designers and developers create charts using JavaScript
■ easy to use and great for building simple but flexible data visualizations.
○ Google Charts:
■ A free tool from Google that provides a gallery of ready-to-embed, interactive JavaScript
charts.
○ Infogram:
■ A web-based tool that allows users to create infographics, reports, maps, and
dashboards without coding skills.
○ Grafana:
■ An open-source platform for creating dynamic dashboards and reports from multiple
data sources.
○ Jupyter:
■ An open-source software that allows for the creation and sharing of documents with live
code, equations, visualizations, and text.
● Programming-based tools
○ Python libraries:
■ Python offers numerous libraries for data visualization, including Seaborn, Plotly, Bokeh,
and Altair.
○ R:
■ A statistical programming language with packages for creating visualizations, often
used in conjunction with tools like Stata.
● AI-powered tools
○ Zoho Analytics:
■ An AI-powered business intelligence and data analytics platform that excels in data
visualization.
○ Domo:
■ An AI-powered data visualization tool that helps transform data into meaningful insights.
○ ThoughtSpot
■ delivers an AI-powered, conversational experience
■ search-based interface and built-in AI agents, AI-augmented dashboards, natural
language queries, and automated insights
Creating Charts in Microsoft Excel

● Excel provides a wide range of professional chart types:

vertical/horizontal bar charts, line charts, pie charts, area charts, scatter plots,
etc.
● Offers extensive customization options for clear business analyses and
presentations.
● Different charts suit different data types.
● Using the wrong chart may confuse the audience or misrepresent insights.
● Focus on clarity and accurate data storytelling.
● Keep designs simple;
● avoid unnecessary 3-D effects, shadows, cones, or cylinders.
Steps to Create Charts in Excel

● Highlight the data range before inserting a chart.

● Go to Insert → Charts group → Select chart type and subtype.
● Customize using:
○ Design tab: change chart type, data selection, layout, and style.
○ Format tab: adjust formatting and appearance.
● Right-click chart elements or use Quick Layouts for fast adjustments.
● Color Design Considerations
○ Around 10% of men experience color blindness—affecting color distinction.
○ Red-green color combinations can be problematic.
○ Use blue–orange palettes for professional, accessible visuals.
Column and Bar Charts

● Vertical —> Column Charts

● Horizontal —> Bar Charts
● Clustered column chart: compares values across categories using vertical
rectangles
● Stacked column chart: displays the contribution of each value to the total by
stacking the rectangles.
● 100% stacked column chart: compares the percentage that each value
contributes to a total.
● Column and bar charts are useful for comparing categorical or ordinal data,
● Helps to illustrate differences between sets of values,and to show proportions
or percentages of a whole.
Line Charts
● Line charts provide a useful means for
displaying data over time.
● show the relationship between two
variables; they are most often used to
track changes or trends over time (having
one of the variables set to time on the
x-axis).
● Line charts are often used to show
time-dependent changes in the values of
some measure.
● Can plot multiple data series but can be
complex if the magnitude of data values
differs greatly.
Pie Chart
● Circular chart divided into slices, where each
slice represents a proportion of the whole.
● Should only be used to illustrate relative
proportions of a specific measure
● pie charts are best presented in a
two-dimensional format.
● Avoid (3-D) pie charts—especially those that
are rotated—and keep them simple.
● effective when displaying a small to medium
number of categories. Too many slices can
make the chart difficult to interpret.
Area Charts
● combines the features of a pie chart
with those of line charts.
● effective for visualizing trends and
the volume of data, highlighting both
the relationship of data to zero and
the cumulative total.
● present more information than pie or
line charts alone.
Scatter Chart
● is a graph that shows the relationship
between two sets of data.
● uses dots to represent individual data
points, with the position of each dot
determined by its values on the horizontal
(x-axis) and vertical (y-axis) dimensions.
● are useful for visualizing correlations and
patterns between variables.
● effective way to explore the existence of
trends, concentrations, and outliers.
Bubble Chart
● Enhanced versions of scatter plots
● effectively represent three different sets of data
using position and size.
● The horizontal and vertical axes represent two
variables, while the size of the bubbles represents
the magnitude or significance of the third variable.
● Can compare data points based on their position
on the chart and their corresponding bubble size.
● In this example, we can see which countries have
the highest income and life expectancy, and also
understand which countries have the largest
populations within that context. For example, a
large bubble at the top right of the chart would
indicate a country with high income and life
expectancy, and also a large population.
●
Miscellaneous Excel Charts
● Excel provides several additional charts for special applications.
● A stock chart allows to plot stock prices, such as the daily high, low, and

close. It may also be used for scientific data such as temperature changes.

● A surface chart shows 3-D data.

● A doughnut chart is similar to a pie chart but can contain more than one data
series.
● A radar chart allows you to plot multiple dimensions of several data series.
Geographic Data

● Geographic data refers to information that is associated with specific

geographic locations on the Earth's surface.
● Data that includes location information linked to real-world positions.
● Describes where things are, in addition to what they are.
● Often represented using coordinates (latitude/longitude), addresses, or map
layers.
● Types of Geographic Data:
○ Vector Data:
○ Raster data
● Vector data
○ represents geographic features as points, lines, and polygons.
○ It uses coordinates (X, Y or latitude, longitude) to define the spatial location of the
features.
○ This type of data is commonly used to represent cities, roads, and land parcels.
● Raster Data
○ Raster data is represented as a grid of cells or pixels.
○ It is ideal for representing continuous data or phenomena that can be measured at any
point on the Earth's surface.
○ Examples include scanned maps, aerial photographs, and satellite imagery.
● Uses of Geographic Data
○ Urban planning and land management
○ Transportation and traffic analysis
○ Disaster prediction and emergency response
○ Agriculture and environmental monitoring
○ Business: market segmentation, site selection, delivery route optimization
○ Geospatial analytics and GIS applications
Other Excel Data Visualization Tools
Data Bars, Color Scales, and Icon Sets
● part of Excel’s Conditional Formatting rules
● Allows us to visualize different numerical values through
the use of colors and symbols.
● adds colored bars to cells to visually represent the values
inside them.
● helps to quickly compare numbers at a glance.
● What data bars do
○ Visualize data:
■ They provide a quick visual comparison of values
within a range, with longer bars representing higher
values and shorter bars representing lower ones.
○ Highlight trends:
■ They can help you quickly spot high and low numbers
in a dataset without reading every number.
○ Act as in-cell charts:
■ Data bars are a type of formatting that stays within
the cells, unlike a separate chart that is placed
elsewhere.
○ Are customizable:
■ You can edit the rules, change colors, and set
conditions for how the bars are displayed, such as
based on a percentage or a specific number range.
 Color Scales
● color scales apply a gradient of two or
three colors to a range of cells, with colors
corresponding to the minimum, midpoint,
and maximum values in the data set
● This provides a quick way of identifying
the largest and smallest product-month
sales values.
● To apply a color scale, select the cells, go
to the Home tab, click Conditional
Formatting, point to Color Scales, and
choose a format from the displayed
options.
Icon Sets
● Icon sets in Excel are a conditional
formatting feature that adds icons to cells
to visually represent data trends and
values at a glance.
● useful for quickly comparing data,
showing conditions (like performance
ratings)
● makes complex spreadsheets easier to
understand by using icons such as arrows,
flags, or check marks.
Sparklines
● Sparklines are graphics that summarize a
row or column of data in a single cell.
● helps to spot patterns in a compact way.
● Excel has three types of sparklines: line,
column, and win/loss.
● Line sparklines are useful for time-series
data and displays trends using a line
graph.
● column sparklines uses vertical bars to
represent values and are more appropriate
for categorical data.
● Win-loss sparklines are useful for data
that move up or down over time. Shows
positive and negative data using markers.
Data Queries: Tables, Sorting, and
Filtering
Table
● To make managing and analyzing a group
of related data easier, we can turn a range
of cells into an Excel table (previously
known as an Excel list).
● Tables connect cells in a range and put it
into a fixed structure.
● Tables make it easier to structure and
organize data.
● To create a table:
○ Select the cell or the range in the data.
○ Select Home > Format as Table.
○ Pick a table style.
○ In the Format as Table dialog box, select
the checkbox next to My table as headers
if you want the first row of the range to be
the header row, and then click OK.
Sorting

● Sorting refers to arranging your data in a particular order, either ascending or

descending, based on one or more columns.
● helps highlight patterns and relationships within your dataset.
● To sort, click the "Sort & Filter" button on the Home tab and choose an order.
● Sorting in Excel:
○ Simple sort: Click a cell in the column you want to sort, go to the Home tab and select "Sort &
Filter," then choose "Sort A to Z" or "Sort Z to A".
○ Custom sort: Select "Custom Sort" from the "Sort & Filter" menu to sort by multiple columns,
define a specific order, or set criteria for numbers, text, or dates.
○ Sorting as an Excel Table: If your data is formatted as a table, the filter buttons in the header
row are the easiest way to sort. Click the arrow in the column you want to sort and pick your
order.
Filtering

● Filtering allows you to display only the rows that meet specific criteria.
● Makes it easier to focus on relevant data without altering the original dataset.
● How to Apply Basic Filters:
○ Enabling Filters:
■ Select the range of your data (including headers), go to the Data tab, and click Filter. This
adds drop-down arrows to each header.
○ Using the Filter Menu:
■ Click the drop-down arrow for the column you want to filter. You can choose to filter by
specific values, or use text, number, or date filters for more nuanced criteria, such as
“Greater than” or “Contains.”
● Clear Filters:
○ To remove a filter, click the drop-down arrow of the filtered column and select Clear Filter
From [Column Name]. OR
○ remove all filters by clicking the Filter button in the Data tab again.
Advanced Filtering Techniques
● Using Text Filters
○ Excel allows to filter based on text criteria, such as specific phrases or partial matches.
Choose Text Filters from the drop-down menu to filter data containing certain words,
starting with specific letters, or using wildcards (e.g., * for multiple characters).
● Number and Date Filters
○ Filter numeric data by selecting options like Top 10, Greater Than, or Between.
○ For dates, choose options such as This Week, Next Month, or Custom Filter to define
specific date ranges.
● Creating Custom Views
○ For frequent filtering scenarios, consider saving custom views.
○ Set desired filters, go to the View tab, click on Custom Views, and save the view with a
name.
○ This allows to quickly switch between different data presentations without reapplying
filters.
Tips for sorting and filtering

● Format as a table:
○ If your data is not already in a table, format it as one.
○ This automatically adds filter buttons and keeps rows intact when you
sort, preventing data from becoming unsynchronized.
● Be careful with single column sorting:
○ Do not try to sort a single column if it is part of a larger table with other
data, as this can cause rows to become out of sync.
● Use the shortcut:
○ Use the shortcut Ctrl+Shift+L to quickly toggle filters on and off.
Statistical Methods for Summarizing
Data
Statistics

● Statistics, as defined by David Hand, past president of the Royal Statistical

Society in the UK, is both the science of uncertainty and the technology of
extracting information from data.
● It involves collecting, organizing, analyzing, interpreting, and presenting data.
● A statistic is a summary measure of data.
● Statistical methods are essential for business analytics to make informed,
data-driven decisions by uncovering patterns in data, forecasting future
trends, and reducing uncertainty.
● Statistics allow businesses to move beyond intuition.
Statistical Analysis in Excel
● Descriptive statistics refers to methods of describing and summarizing data using tabular,
visual, and quantitative techniques.
● Descriptive statistics
○ Measures of Central Tendency:
■ single values that describe the "center" or typical value of a dataset.
■ Mean: The average of all values.
■ Median: The middle value when the data is ordered, useful for datasets with
outliers.
■ Mode: The value that appears most frequently.
○ Measures of Variation:
■ These describe the spread or dispersion of the data.
■ Range: The difference between the highest and lowest values.
■ Standard Deviation: Measures the amount of variation or dispersion around the
mean.
■ Variance: The average of the squared differences from the mean.
● Microsoft Excel supports statistical analysis in two ways:
○ With statistical functions that are entered in worksheet cells directly or embedded in
formulas
○ With the Excel Analysis Toolpak add-in to perform more complex statistical
Frequency Distributions
● A frequency distribution is a table that shows the number of observations in
each of several non overlapping groups
● Frequency Distributions for Categorical Data
○ Categorical variables naturally define the groups in a frequency distribution.
○ To construct a frequency distribution, we need to count the number of observations that
appear in each category.
○ This can be done using the Excel COUNTIF function.
● Relative Frequency Distributions:
○ Expressing as a fraction or proportion of the total is called relative frequency.
■ Relative frequency of i = (frequency of category i)/n
○ To express in percentage multiply relative frequency by 100
○ A relative frequency distribution is a tabular summary of the relative frequencies of all
categories.
Frequency Distributions for Numerical Data

● For numerical data that consist of a small number of discrete values, we can
construct a frequency distribution similar to the categorical data.
● For numerical data that have many different discrete values with little
repetition or are continuous, a frequency distribution requires that we define
by specifying
○ the number of groups,
○ the width of each group, and
○ the upper and lower limits of each group.
● It is important to remember that the groups may not overlap, so that each
value is counted in exactly one group.
● define the groups after examining the range of the data
Excel Histogram Tool

● Histogram = A graphical depiction of a frequency distribution for

numerical data in the form of a column chart.
● Frequency distributions and histograms can be created using the
Analysis Toolpak in Excel.
● Limitation:
○ the frequency distribution and histogram are not linked to the data; thus, if
any of the data is changed the entire procedure must be reconstructed.
Cumulative Relative Frequency Distributions
● The cumulative relative frequency
represents the proportion of the total
number of observations that fall at or
below the upper limit of each group.
● A tabular summary of cumulative relative
frequencies is called a cumulative relative
frequency distribution.
● It is calculated by summing all the relative
frequencies at or below each upper limit.
Percentiles
● The kth percentile is a value at or below which at least k percent of the observations lie.
● The way by which percentiles are calculated is not standardized.
● most common way to compute the kth percentile is to order the data values from smallest
to largest and calculate the rank of the kth percentile using the formula
nk/100 + 0.5
Where n = number of observations
Round this to the nearest integer, and take the value corresponding to this rank as the kth
percentile.
● The Excel function [Link](array, k) computes the kth percentile of data in the
range specified in the array field, where k is in the range 0 to 1, inclusive.
● Excel also has a tool for sorting data from high to low and computing percentiles
associated with each value.
Example

Suppose we want to compute the 25th percentile (k = 25) of a dataset with n = 10

values.

P 25 = (10*25)/100 + 0.5 = 3

Percentile Position = 3.0

That means the 25th percentile is located at the 3rd value in the sorted data.
Quartiles
● Quartiles break the data into four parts.
● The 25th percentile is called the first quartile,Q1;
● The 50th percentile is called the second quartile, Q2;
● The 75th percentile is called the third quartile, Q3;
● The 100th percentile is the fourth quartile, Q4.
● One-fourth of the data fall below the first quartile, one-half are below the second
quartile, and three fourths are below the third quartile.
● We may compute quartiles using the Excel function [Link](array, quart),
where array specifies the range of the data and quart is a whole number between 1
and 4, designating the desired quartile
● We can extend these ideas to other divisions of the data.
● For example, deciles divide the data into 10 sets: the 10th percentile, 20th percentile,
and so on.
● All these types of measures are called data profiles, or fractiles.
Example

Let's consider the dataset: [4,7,9,12,15,18,21,25,30,40]

Quartiles divide data into four equal parts:
Q1 = 25th percentile
Q2 = 50th percentile (Median)
Q3 = 75th percentile
Using Formula: Q1 = 3.0 = 3rd value hence Q1 = 9
Q2 = 5.5, Take the average of the 5th and 6th values: Q2 = (15+18)/2 = 16.5
Q3 = 8, hence Q3 = 25
Cross Tabulations

● A cross-tabulation is a tabular method that displays the number of

observations in a data set for different subcategories of two categorical
variables.
● A cross-tabulation table is often called a contingency table.
● The subcategories of the variables must be mutually exclusive and
exhaustive, meaning that each observation can be classified into only one
subcategory, and, taken together over all subcategories, they must constitute
the complete data set.
● Cross-tabulations are commonly used in marketing research to provide
insight into characteristics of different market segments using categorical
variables such as gender, educational level, marital status, and so on.
Exploring Data Using PivotTables

● PivotTables : Excel provides a powerful tool for distilling a complex data set
into meaningful information
● PivotTables allows you to create custom summaries and charts of key
information in the data.
● PivotTables can be used to quickly create cross-tabulations and to drill down
into a large set of data in numerous ways.
● Can quickly pivot—or reorganize— data, allowing to examine worksheet in
several ways.
● If you change any of the data in your source worksheet, the PivotTable will not
update automatically.
● To manually update it, select the PivotTable and then go to Analyze and
Refresh.
Creating a PivotTable

1. Select the table or cells (including column headers) you want to include in
your PivotTable.
2. From the Insert tab, click the PivotTable command.
3. The Create PivotTable dialog box will appear. Choose your settings, then click
OK
4. A blank PivotTable and Field List will appear on a new worksheet.
5. Once you create a PivotTable, you'll need to decide which fields to add. Each
field is simply a column header from the source data.
a. In the PivotTable Field List, check the box for each field you want to add.
6. The PivotTable will calculate and summarize the selected fields.
7. can sort the data in a PivotTable using the Sort & Filter command on the
Home tab as well as apply any type of number formatting.
PivotCharts

● Microsoft Excel provides a simple one-click way of creating PivotCharts to

visualize data in PivotTables.
● To display a PivotChart for a PivotTable, first select the PivotTable.
● From the Analyze tab, click on PivotChart.
● Excel will display an Insert Chart dialog that allows you to choose the type of
chart you wish to display.
Slicers and PivotTable Dashboards

● Excel 2010 introduced slicers—a tool for drilling down to “slice” a PivotTable
and display a subset of data.
● To create a slicer for any of the columns in the database, click on the
PivotTable and choose Insert Slicer from the Analyze tab in the PivotTable
Tools ribbon.
● Excel camera tool is a useful tool for creating PivotTable-based dashboards.
● If you create several different PivotTables and charts, you can easily use the
camera tool to take pictures of them and consolidate them onto one
worksheet.
● In this fashion, you can still make changes to the PivotTables and they will
automatically be reflected in the camera shots.