Excel Dashboard for Business

Analytics

Proprietary content. ©Great Learning. All Rights Reserved. Unauthorized use or distribution prohibited
Agenda
In this session, we will discuss:
● Introduction to Excel Dashboards
● Importance of Data Cleaning
● Types of Functions
● Pivot Tables
● Getting started with Organizing Data
● Building Excel Dashboards

Proprietary content. ©Great Learning. All Rights Reserved. Unauthorized use or distribution prohibited
Introduction to Excel Dashboards

Proprietary content. ©Great Learning. All Rights Reserved. Unauthorized use or distribution prohibited
Introduction to Excel Dashboards
What is an Excel Dashboard?

● An Excel Dashboard is a visual representation of data that provides a

quick, at-a-glance overview of key information, trends, and insights.
● Key Features:
○ Data Visualization: Graphs, charts, and tables
○ Interactivity: Filters, slicers, and user-friendly controls
○ Real-time Data: Automatic updates and data connections
○ Customization: Tailored to specific needs and preferences

Proprietary content. ©Great Learning. All Rights Reserved. Unauthorized use or distribution prohibited
Introduction to Excel Dashboards
Why Use Excel Dashboards?

● Benefits:
○ Data Clarity: Simplifies complex data into digestible visuals
○ Decision Support: Helps in making informed choices
○ Efficiency: Saves time through automation and real-time updates
○ Professional Reporting: Creates polished, professional-looking reports
● Use Cases:
○ Sales and Marketing: Tracking sales performance, marketing campaigns
○ Financial Analysis: Budgeting, expense tracking, financial forecasting
○ Project Management: Progress monitoring, resource allocation

Proprietary content. ©Great Learning. All Rights Reserved. Unauthorized use or distribution prohibited
Importance of Data Cleaning

Proprietary content. ©Great Learning. All Rights Reserved. Unauthorized use or distribution prohibited
Importance of Data Cleaning
What is Data Cleaning?

● Data cleaning, also known as data cleansing, is the process of identifying and correcting errors,
inconsistencies, and inaccuracies in datasets.
● Missing values, duplicate entries, formatting errors, outliers, and inconsistencies in data.
● Poor data quality can lead to incorrect analysis, faulty insights, and unreliable decision-making.

Proprietary content. ©Great Learning. All Rights Reserved. Unauthorized use or distribution prohibited
Importance of Data Cleaning
Why Data Cleaning Matters

● Improved Accuracy: Clean data ensures more accurate analysis and decision-making.
● Cost Savings: Prevents costly errors and mistakes based on inaccurate data.
● Enhanced Efficiency: Saves time in data analysis and report generation.
● Data Integrity: Ensures trust in the data and the insights derived from it.

Proprietary content. ©Great Learning. All Rights Reserved. Unauthorized use or distribution prohibited
Importance of Data Cleaning
Data Cleaning Process

● Data Assessment: Understanding the data and its quality.

● Data Validation: Identifying errors, inconsistencies, and missing values.
● Data Transformation: Standardizing and formatting data.
● Data Enrichment: Adding missing information or context.
● Data Documentation: Maintaining a record of changes and cleaning processes.

Proprietary content. ©Great Learning. All Rights Reserved. Unauthorized use or distribution prohibited
Types of Functions

Proprietary content. ©Great Learning. All Rights Reserved. Unauthorized use or distribution prohibited
Types of Functions
What are Functions?

● They are pre defined instructions that perform specific calculations in a particular order in a
spreadsheet.
● Basic syntax of function:-
= Formula(Range of cells)

=AVERAGE(No.1, No.2)

Start of a
function
Range of cells
Formula name,
performs a specific
calculation
Proprietary content. ©Great Learning. All Rights Reserved. Unauthorized use or distribution prohibited
Types of Functions
COUNTIF

● A function that counts the number of cells that match a specific condition.
= COUNTIF(Range, “Condition”)

Proprietary content. ©Great Learning. All Rights Reserved. Unauthorized use or distribution prohibited
Types of Functions
LENGTH

● A function that returns the length of a text string.

= LEN(Cell)

Proprietary content. ©Great Learning. All Rights Reserved. Unauthorized use or distribution prohibited
Types of Functions
LEFT/RIGHT

● A function that returns specific number of characters from left/right side in a cell.
= LEFT(Cell, No. of characters)
= RIGHT(Cell, No. of characters)

Proprietary content. ©Great Learning. All Rights Reserved. Unauthorized use or distribution prohibited
Types of Functions
CONCATENATE

● A function that combines multiple text cells into a single cell.

= CONCATENATE(CELL-1, CELL-2)

Proprietary content. ©Great Learning. All Rights Reserved. Unauthorized use or distribution prohibited
Types of Functions
TRIM

● A function that removes extra unwanted spaces from the cell.

= TRIM(CELL-1)

Proprietary content. ©Great Learning. All Rights Reserved. Unauthorized use or distribution prohibited
Pivot Tables

Proprietary content. ©Great Learning. All Rights Reserved. Unauthorized use or distribution prohibited
Pivot Tables
What is a Pivot Table?

● A Pivot Table is a data processing tool in spreadsheet software (e.g., Excel) used for summarizing,
analyzing, and presenting complex data.
● Key Features:
○ Drag-and-drop interface for organizing data
○ Aggregates data for insights
○ Dynamic and customizable
○ User-friendly for non-technical users

Proprietary content. ©Great Learning. All Rights Reserved. Unauthorized use or distribution prohibited
Pivot Tables
Why use Pivot Tables?

● Benefits:
○ Data Summarization: Easily summarize and reorganize data.
○ Quick Insights: Rapidly discover patterns and trends.
○ Flexibility: Adjust and experiment with data views.
○ Error Reduction: Minimize manual data manipulation errors.

Proprietary content. ©Great Learning. All Rights Reserved. Unauthorized use or distribution prohibited
Organizing Data in Excel

Proprietary content. ©Great Learning. All Rights Reserved. Unauthorized use or distribution prohibited
Building Excel Dashboards

Proprietary content. ©Great Learning. All Rights Reserved. Unauthorized use or distribution prohibited
Summary
Here is a brief recap:
● Data cleaning, also known as data cleansing, is the process of identifying and correcting errors,
inconsistencies, and inaccuracies in datasets.
● They are pre-defined instructions that perform specific calculations in a particular order in a
spreadsheet.
● A Pivot Table is a data processing tool in spreadsheet software (e.g., Excel) used for summarizing,
analyzing, and presenting complex data.

Proprietary content. ©Great Learning. All Rights Reserved. Unauthorized use or distribution prohibited