What is Data Analytics?

Introduction and Definition-

Some important definitions are as follows-
 Data Analytics is the process of collecting, organizing and studying data to find
useful information understand what’s happening and make better decisions.
 Data Analytics is the process of collecting, organizing, cleaning, analyzing, and
interpreting data to uncover useful insights, patterns, and trends that support decision-
making and problem-solving.
 Data analytics is the process of analyzing raw data in order to draw out meaningful,
actionable insights, which are then used to inform and drive smart business
decisions.
 Data analytics refers to the process of collecting, organizing, transforming, and
analyzing data to extract meaningful insights, make predictions, and support informed
decision-making. It is a multidisciplinary field that combines techniques from
mathematics, statistics, and computer science to uncover patterns and trends in data.
 Data analytics is a multidisciplinary field that employs a wide range of analysis
techniques, including math, statistics, and computer science, to draw insights from data
sets. Data analytics is a broad term that includes everything from simply analyzing data
to theorizing ways of collecting data and creating the frameworks needed to store it.

Data is everywhere, and people use data every day, whether they realize it or not. Data is also
crucial in a professional sense. Organizations that use data to drive business strategies often
find that they are more confident, proactive, and financially savvy. As a result, data analytics is
important across many industries. A sneaker manufacturer might look at sales data to determine
which designs to continue and which to retire, or a health care administrator may look at
inventory data to determine the medical supplies they should order.

In simple words it helps people and businesses learn from data like what worked in the past,
what is happening now and what might happen in the future.

Key Components of Data Analytics-

Here’s a breakdown of the major steps involved in the data analytics process:

Step Description
 1. Data Collection Gathering raw data from sources like websites, sensors, surveys, or
databases
2. Data Cleansing Removing errors, duplicates, and inconsistencies to ensure data
quality
3. Data Analysis Applying statistical and computational methods to find patterns
and insights
4. Data Understanding what the results mean in context and drawing
Interpretation conclusions
5. Data Presenting findings using charts, graphs, and dashboards for clarity
Visualization

Importance and Usage of Data Analytics-

Data analytics is used in many fields like banking, farming, shopping, government and
more. It helps in many ways:

Data Analytics Importance-

 Helps in Decision Making: It gives clear facts and patterns from data which help
people make smarter choices.
 Helps in Problem Solving: It points out what's going wrong and why making it easier
to fix problems.
 Helps Identify Opportunities: It shows trends and new chances for growth that might
not be obvious.
 Improved Efficiency: It helps reduce waste, saves time and makes work smoother by
finding better ways to do things.

Data Analytics Importance in more detail-

✅ 1. Helps in Better Decision-Making

 Data analytics gives us facts and patterns from data.

 This helps people and businesses make smarter choices instead of guessing.
 Example: A company can see which products sell best and focus on those.

✅ 2. Solves Problems Quickly

 It shows what’s going wrong and why.

 This makes it easier to fix issues and improve performance.
 Example: If sales drop, analytics can find out if it’s due to pricing, location, or
customer interest.

✅ 3. Finds New Opportunities

 Analytics reveals trends and chances for growth that may not be obvious.
 Example: A farmer might discover which crops grow best in certain seasons using
data

Improves Customer Experience

 By understanding customer behavior, companies can offer better services.

 Example: Online stores suggest products based on what you’ve viewed or bought
before.

✅ 6. Supports Innovation

 Data shows gaps in the market or areas to improve.

  This helps in creating new products or services.
 Example: A tech company might use data to design a new app feature.

✅ 7. Reduces Risks

 Predictive analytics can warn about future problems.

 Example: Banks use data to detect fraud or check if someone is likely to repay a
loan

✅ 8. Gives Competitive Advantage

 Companies using analytics can act faster and smarter than competitors.
 They can adapt to changes and stay ahead in the market.
 Example: A business that tracks customer trends can launch new products before
others.

Life Cycle of Data Analytics-

The Data analytics lifecycle was designed to address Big Data problems and data science
projects. The process is repeated to show the real projects. To address the specific demands
for conducting analysis on Big Data, the step-by-step methodology is required to plan the
various tasks associated with the acquisition, processing, analysis, and recycling of data.

Phase 1: Discovery -

o The data science team is trained and researches the issue.

o Create context and gain understanding.
o Learn about the data sources that are needed and accessible to the project.
o The team comes up with an initial hypothesis, which can be later confirmed with
evidence.

Phase 2: Data Preparation -

o Methods to investigate the possibilities of pre-processing, analysing, and preparing

data before analysis and modelling.
 o It is required to have an analytic sandbox. The team performs, loads, and transforms
to bring information to the data sandbox.
o Data preparation tasks can be repeated and not in a predetermined sequence.
o Some of the tools used commonly for this process include - Hadoop, Alpine Miner,
Open Refine, etc.

Phase 3: Model Planning -

o The team studies data to discover the connections between variables. Later, it selects
the most significant variables as well as the most effective models.
o In this phase, the data science teams create data sets that can be used for training for
testing, production, and training goals.
o The team builds and implements models based on the work completed in the
modelling planning phase.
o Some of the tools used commonly for this stage are MATLAB and STASTICA.

Phase 4: Model Building -

o The team creates datasets for training, testing as well as production use.
o The team is also evaluating whether its current tools are sufficient to run the models
or if they require an even more robust environment to run models.
o Tools that are free or open-source or free tools Rand PL/R, Octave, WEKA.
o Commercial tools - MATLAB, STASTICA.

Phase 5: Communication Results -

o Following the execution of the model, team members will need to evaluate the
outcomes of the model to establish criteria for the success or failure of the model.
o The team is considering how best to present findings and outcomes to the various
members of the team and other stakeholders while taking into consideration
cautionary tales and assumptions.
o The team should determine the most important findings, quantify their value to the
business and create a narrative to present findings and summarize them to all
stakeholders.
Phase 6: Operationalize -

o The team distributes the benefits of the project to a wider audience. It sets up a pilot
project that will deploy the work in a controlled manner prior to expanding the project
to the entire enterprise of users.
o This technique allows the team to gain insight into the performance and constraints
related to the model within a production setting at a small scale and then make
necessary adjustments before full deployment.
o The team produces the last reports, presentations, and codes.
o Open source or free tools such as WEKA, SQL, MADlib, and Octave.

Process of Data Analytics-

1. Data Collection : Data collection is the first step where raw information is gathered
from different places like websites, apps, surveys or machines. Sometimes data comes
from many sources and needs to be joined together. Other times only a small useful part
of the data is selected.
2. Data Cleansing : Once the data is collected it usually contains mistakes like wrong
entries, missing values or repeated rows. In this step the data is cleaned to fix those
problems and remove anything that isn’t needed. Clean data makes the results more
accurate and trustworthy.
3. Data Analysis and Data Interpretation: After cleaning the data is studied using tools
like Excel, Python, R or SQL. Analysts look for patterns, trends or useful information
that can help solve problems or answer questions. The goal here is to understand what
the data is telling us.
4. Data Visualization: Data visualization is the process of creating visual representation
of data using the plots, charts and graphs which helps to analyze the patterns, trends and
get the valuable insights of the data. By comparing the datasets and analyzing it data
analysts find the useful data from the raw data.

Types of Data Analytics-

There are different types of data analysis in which raw data is converted into valuable
insights. Some of the types of data analysis are mentioned below:
Types of Data Analytics-

1. Descriptive Data Analytics : Descriptive data analytics helps to summarize and

understand past data. It shows what has happened by using tables, charts and averages.
Companies use it to compare results, find strengths and weaknesses and spot any
unusual patterns.
Descriptive analytics explains what has happened in the past. It focuses on analyzing
historical data to identify trends, patterns and summaries. Businesses commonly use it for
reporting and tracking performance, as it gives a overview of results over time.
Applications:
 Data Queries: Retrieving specific values or attributes from datasets.
 Reports: Generating summaries such as sales reports, expense sheets or customer
activity.
 Descriptive Statistics: Using mean, median, mode and variance to interpret data.
 Data Dashboards: Interactive visuals to track KPIs and trends.
 Customer Segmentation: Grouping customers by demographics or purchasing habits.
Example: A retail company uses descriptive analytics to create a monthly sales report that
shows which products sold the most, helping managers track overall performance.
Pros:
 Easy to implement and interpret.
 Provides a clear historical view for performance tracking.
Cons:
 Limited to past events without explaining causes.
 Cannot predict or recommend future actions.

2. Diagnostic Data Analytics: Diagnostic data analytics looks at why something

happened in the past. It uses tools like correlation, regression or comparison to find the
cause of a problem. This helps companies understand the reason behind a drop in sales
or a sudden change in performance.
Diagnostic analytics answers the question “Why did it happen?”. It goes deeper than
descriptive analytics by examining data to find patterns, dependencies and causes. This type
of analysis helps organizations uncover the reasons behind success or failure.
Applications:
 Data Discovery: Exploring datasets to detect anomalies or unusual behavior.
 Data Mining: Identifying hidden patterns, clusters or associations in past data.
 Correlation Analysis: Studying relationships between variables to identify causes.
 Problem-Solving: Helping businesses understand reasons for poor performance.
 Operational Insights: Uncovering bottlenecks or inefficiencies in workflows.
Example: An airline uses diagnostic analytics to analyze why flight delays increased and
discovers that weather disruptions and staff shortages were the main causes.
Pros:
 Helps identify root causes of problems.
 Improves decision-making by revealing dependencies.
Cons:
 Requires detailed, high-quality historical data.
 Can be time-consuming and complex to perform.

3. Predictive Data Analytics: Predictive data analytics is used to guess what might
happen in the future. It looks at current and past data to find patterns and make
forecasts. Businesses use it to predict things like customer behavior, future sales or
possible risks.
Predictive analytics focuses on what is likely to happen in the future. It uses past and
current data, along with statistical models and machine learning, to forecast outcomes such
as customer behavior, market demand or risks.
Applications:
 Linear Regression: Predicting numerical outcomes like sales or revenue growth.
 Time Series Forecasting: Estimating future trends such as demand or stock prices.
 Data Mining: Uncovering patterns that indicate future behavior.
 Predictive Modeling: Creating models to predict customer churn, fraud or credit risk.
 Decision Analysis & Optimization: Evaluating scenarios to determine the best
strategy.
 Transaction Profiling: Detecting suspicious or unusual financial transactions.
Example: A bank applies predictive analytics to estimate the likelihood of customers
defaulting on loans, helping it decide whether to approve or reject applications.
Pros:
 Anticipates future risks and opportunities.
 Improves planning and resource allocation.
Cons:
 Accuracy depends heavily on data quality.
 Models may be complex and require advanced expertise.

4. Prescriptive Data Analytics: Prescriptive data analytics helps to choose the best action
or solution. It looks at different options and suggests what should be done next.
Companies use it for things like loan approval, pricing decisions and managing
machines or schedules.
Prescriptive analytics focuses on what action should be taken. It doesn’t just predict
outcomes but also recommends the best steps to achieve goals or reduce risks. By
combining big data, business rules, optimization and AI, it suggests the most effective
decisions.
Applications:
 Decision Support: Helping leaders choose the most effective action.
 Healthcare Strategic Planning: Optimizing resources using operational, demographic
and economic data.
 Risk Mitigation: Suggesting strategies to minimize exposure to risks.
 Opportunity Optimization: Identifying actions to maximize benefits from upcoming
market trends.
 What-if Analysis: Simulating different decision outcomes and their consequences.
Example: A logistics company uses prescriptive analytics to recommend the most efficient
delivery routes, reducing fuel costs and improving on-time delivery.
Pros:
 Provides actionable recommendations along with predictions.
 Helps optimize decisions for maximum benefits.
Cons:
 Requires advanced technology and expertise.
 Implementation can be costly and resource-intensive.

Real-world applications of Data Analytics-

Data Analytics finds applications across various industries and domains. Here are some
examples-

 Business and marketing industry- In the business and marketing industry, Data
Analytics is used for customer segmentation and targeting, market trend analysis,
pricing optimisation, sales forecasting and social media analytics.

 Healthcare industry- Data Analytics in Healthcare industry is used for patient risk
assessment, disease outbreak prediction, clinical decision support systems, drug
effectiveness analysis and healthcare resource allocation.

 Finance industry- In the finance industry, Data Analytics is used for fraud detection
and prevention, risk modelling and management, investment portfolio optimisation,
credit scoring and financial market analysis.

Why Excel Is Important in Data Analytics? / Role of Excel in Data Analytics:

Excel plays a crucial role in data analytics by enabling users to organize, manipulate, and
analyze large datasets efficiently. It offers powerful features such as pivot tables, conditional
formatting, and advanced functions (like XLOOKUP, IFERROR, MATCH) that help identify
trends, clean data, and perform complex calculations. Excel also supports data visualization
through charts and graphs, making it easier to communicate insights. Additionally, tools like
Power Query and Data Analysis Toolpak enhance its capability to handle data transformation
and statistical analysis. Its accessibility and user-friendly interface make it ideal for both
beginners and experienced analysts to extract actionable insights and support decision-
making.

Key Roles of Excel in Data Analytics-

1. Data Organization and Management- Excel allows analysts to efficiently

organize large datasets using rows, columns, and tables. Features like sorting,
filtering, and data validation help maintain clean and structured data.
2. Data Cleaning and Preparation
Tools such as "Remove Duplicates," conditional formatting, and functions like
IFERROR and COUNTBLANK assist in identifying and correcting errors,
handling missing values, and ensuring data quality before analysis.
3. Data Analysis and Calculation
Excel supports a wide range of functions (e.g., XLOOKUP, MATCH, RANK,
SUMPRODUCT) that enable complex calculations, data retrieval, ranking,
and aggregation, which are essential for extracting meaningful insights.
4. Advanced Analytical Tools
The Analysis ToolPak add-in provides capabilities for regression analysis and
other statistical methods, allowing users to explore relationships between
variables and make predictions.
5. Data Visualization
Pivot tables and pivot charts transform raw data into summarized, interactive
reports and visualizations such as bar charts, line graphs, and dashboards,
facilitating easier interpretation of trends and patterns.
6. Date and Time Calculations
Functions like DAYS and NETWORKDAYS help calculate durations and
working days, useful for project management, HR analytics, and financial
forecasting.
7. Error Handling- Functions like IFERROR help manage and replace errors in
formulas, ensuring that analyses are robust and outputs are meaningful even
when data issues arise.
8. Flexibility and Accessibility- Excel’s user-friendly interface and widespread
availability make it accessible to both beginners and professionals, supporting
a broad range of industries including finance, healthcare, retail, and more.
 9. Integration and Extensibility-Excel can be integrated with other tools and
supports add-ins, enhancing its capabilities for data transformation,
automation, and advanced analytics.
Summary
Excel is a versatile and powerful tool in data analytics, enabling users to clean,
analyze, visualize, and interpret data effectively. Its comprehensive features
support decision-making processes across various business functions and
industries.

OR

Key Roles of Excel in Data Analytics-

1. Accessibility and Ease of Use-

 Excel is widely available and easy to learn.

 No coding required—users can manipulate data using formulas and built-in tools.
 Ideal for quick analysis without needing advanced software.

✅ 2. Data Cleaning and Preparation

 Excel helps clean messy data before analysis.

 Key features:
 Remove Duplicates: Eliminate repeated entries.
 TRIM & CLEAN: Remove extra spaces and non-printable characters.
 Text Functions: Use LEFT, RIGHT, MID, CONCAT to format strings.
 You can convert raw data into structured tables for easier handling

✅ 3. Data Organization and Management

 Excel tables allow sorting, filtering, and categorizing data.

 You can use named ranges, data validation, and conditional formatting to manage
data efficiently.
 Supports importing data from external sources like CSV, SQL, or web queries.

✅ 4. Exploratory Data Analysis (EDA)

 Excel supports basic statistical analysis:

  Mean, median, mode
 Standard deviation, variance
 Frequency distributions
 You can use formulas like AVERAGE(), COUNTIF(), STDEV(), etc., to explore data
patterns.

✅ 5. Data Visualization

 Excel offers a wide range of charts:

 Bar, line, pie, scatter, histogram
 Dynamic visuals with slicers and timelines
 Pivot Charts and Recommended Charts help visualize trends and comparisons
easily.

✅ 6. Advanced Analytics with PivotTables

 PivotTables are Excel’s most powerful feature for summarizing large datasets.
 You can:
 Group data by categories
 Calculate totals, averages, percentages
 Drill down into details interactively
 Ideal for dashboards and reporting.

✅ 7. Scenario Analysis and Forecasting

 Excel supports:
 What-If Analysis: Test different scenarios using Goal Seek or Data Tables.
 Forecast Sheets: Predict future trends using historical data.
 Solver Add-in: Optimize decisions based on constraints.

✅ 8. Integration with Other Tools

 Excel can connect with:

 Power BI for advanced visualization
 SQL databases for live data queries
 Python/R for scripting and automation
 It acts as a bridge between basic and advanced analytics platforms.

🧠Summary Table: Excel’s Role in Data Analytics-

 Function Excel Feature Used Benefit
Data Cleaning Remove Duplicates, TRIM, CLEAN Improves data quality
Data Tables, Filters, Named Ranges Easier data management
Organization
Basic Analysis AVERAGE, COUNTIF, STDEV Quick insights
Visualization Charts, Conditional Formatting Clear data presentation
Summarization PivotTables, Grouping Efficient data summarization
Forecasting Forecast Sheet, Goal Seek Predict future outcomes
Integration Power BI, SQL, Python Extends Excel’s capabilities

Data Types in Excel-

Excel supports several data types that determine how data is stored, displayed, and processed:

1. Text (String)
 Represents any combination of letters, numbers, and symbols treated as text.
 Examples: Names, addresses, product codes.
 Even if numbers are entered as text (e.g., phone numbers), Excel treats them as
strings.
2. Number-
 Numeric values used for calculations.
 Includes integers, decimals, percentages, and scientific notation.

Examples: 100, 3.14, 25%, 1.2E3.

3. Date and Time

 Excel stores dates and times as serial numbers internally.
 Dates are integers representing days since January 1, 1900 (default).
 Times are fractional parts of a day.
 Examples: 12/31/2023, 14:30 (2:30 PM).
 4. Boolean (Logical)
 Represents TRUE or FALSE values.
 Often used in logical tests and conditional formulas.

5. Error Values
 Indicate problems in formulas or data.
 Examples: #DIV/0! (division by zero), #N/A (value not available), #REF! (invalid
cell reference).

6. Currency and Accounting

 Specialized number formats for financial data, displaying currency symbols and fixed
decimal places.

Data Sources in Excel-

Excel can import and connect to data from various sources, enabling dynamic and
comprehensive data analysis:

1. Manual Data Entry

Directly typing data into cells.

Suitable for small datasets or quick data capture.

2. Excel Worksheets and Workbooks

 Data can be linked or copied between sheets or different Excel files.

3. Text Files (CSV, TXT)

 Comma-separated values (CSV) or tab-delimited text files are common for data
exchange.
 Excel’s import wizard helps parse and format these files.

4. Databases-
 Excel can connect to databases like SQL Server, Access, Oracle, and MySQL using
ODBC or other connectors.
 Enables importing large datasets and refreshing data dynamically.
 5. Web Data-
 Data can be imported from web pages or APIs using Power Query or built-in web
query tools.

Other Office Applications

Data can be imported from Word, Outlook, or PowerPoint files.

Power Query / Get & Transform

A powerful tool within Excel to connect, combine, and transform data from multiple sources
including files, databases, online services, and more.

External Data Connections

Excel supports connections to online services like SharePoint, Azure, and cloud storage
platforms.

Summary Table-

Data Type Description Example

Text (String) Letters, numbers treated as text "John Doe", "A123"
Number Numeric values for calculations 100, 3.14, 25%
Date and Time Dates and times stored as serial numbers 01/01/2024, 14:30
Boolean (Logical) TRUE or FALSE values TRUE, FALSE
Error Values Indicate formula or data errors #DIV/0!, #N/A
Currency/Accounting Financial numbers with currency format $1,000.00
Data Source Description
Manual Entry Typing data directly into cells
Excel Worksheets/Workbooks Data within or across Excel files
Text Files (CSV, TXT) Importing structured text data
Databases Connecting to SQL, Access, Oracle, etc.
Data Type Description Example
Web Data Importing from websites or APIs
Other Office Apps Importing from Word, Outlook, etc.
Power Query / Get & Transform Advanced data import and transformation tool
External Data Connections Cloud services, SharePoint, Azure, etc.
Examples of Data Types in Excel

Here are common data types you’ll encounter when working in Excel:

Example Value Description

Data Type

Number 250, 3.14, -45 Used for calculations like totals or averages
Text (String) "Apple", "HR Labels, names, categories
Dept"
Date & Time 19-Sep-2025, 18:04 For tracking events, deadlines, timestamps
Boolean (Logical) TRUE, FALSE Used in formulas and conditions
Error #DIV/0!, #VALUE! Appears when a formula fails or is invalid
Currency ₹1,500.00, $99.99 Number formatted with currency symbol
Percentage 75%, 0.25 Useful for ratios, growth rates

Examples of Data Sources in Excel-

Excel can pull data from many external sources. Here are some examples:

Source Type Example Source How It’s Used in Excel

Excel File sales_data.xlsx Open or link to another workbook

CSV File [Link] Import structured data from plain text

[Link] Use Power Query to extract tables from a

Web Page
table site

Connect to enterprise databases for live

SQL Database SQL Server, MySQL, Oracle
data
Source Type Example Source How It’s Used in Excel

SharePoint List Company Tasks List Import task or project data from SharePoint

Azure Cloud Azure Blob Storage Access cloud-stored big data

Power BI
Sales Dashboard Dataset Use Power BI dataflows directly in Excel
Dataset

Import semi-structured data using Power

JSON File [Link]
Query

Excel Interface-

An Excel spreadsheet, called a workbook, contains one or more worksheets, each a grid
of 1,048,576 rows and 16,384 columns for data management. Workbooks organize related
data across multiple worksheets in a single file.
1. Understanding Excel Workbooks and Worksheets-
 Workbook: A single Excel file containing one or more worksheets.
 Worksheet: A grid with over 17 billion cells (1,048,576 rows × 16,384 columns) for
entering and analyzing data.
 Starting Point: Open a blank workbook or select a template via File > New.
2. Key Features of Excel Spreadsheets
2.1. Rows and columns
 Rows (horizontal, numbered) and columns (vertical, lettered) intersect to form cells.
 Example: Row 3 and Column B create cell B3.
 Capacity: 1,048,576 rows and 16,384 columns per worksheet.

2.2 Cell Formatting

 Adjusts cell appearance (e.g., font, color, borders, alignment, number formats).
 Steps:
1. Select a cell or range.
2. Go to Home > Font, Alignment, or Number to customize.
2.3. Formulas
Automate calculations using predefined functions.
 Syntax: =FormulaName(Arguments) (e.g., =SUM(A1:A10)).
Some of the commonly used formulas are:

Formula Purpose Example Description

Finds the sum of Adds all numbers in the range

SUM =SUM(A1:A10)
numeric data A1 to A10

Counts the number of cells

Counts cells with
COUNT =COUNT(B1:B10) containing numbers in B1 to
numeric data
B10

Finds the Returns the largest number in

MAX =MAX(C1:C10)
maximum number the range C1 to C10

Finds the Returns the smallest number in

MIN =MIN(D1:D10)
minimum number the range D1 to D10

Returns the Displays today's date, updates

TODAY =TODAY()
current date automatically

Finds the square Calculates the square root of the

SQRT =SQRT(E1)
root value in cell E1

For example, we can use the formula to find the average of the integers in column C from
row 2 to row 7:
= AVERAGE(D2:D7)
The range of values on which we want to average is defined by D2:D6. The formula is
located near the name field on the formula tab.

2.4. Pivot Tables

 Summarize and analyze large datasets dynamically.
 Steps:
1. Select data.
2. Go to Insert > PivotTable.
3. Drag fields to Rows, Columns, Values, or Filters to organize data.
2.5. Text Manipulation
 Commands to modify text (e.g., CONCATENATE, LEFT, RIGHT).
 Example: =CONCATENATE(A1, " ", B1) combines text from A1 and B1.
3. Uses of Spreadsheets-
The use of Spreadsheets is endless. It is generally used with anything that contains
numbers. Some of the common use of spreadsheets are:
 Finance: Manage budgets, taxes, transactions, and billing.
 Forms: Create templates for timesheets, surveys, or performance reviews.
 Education: Track student attendance, grades, or schedules.
 Lists: Organize to-do lists, grocery lists, or contact details.
 Hospitality: Record customer data (e.g., room numbers, check-in/out dates).
Components of Excel Spreadsheets-

The basic components of spreadsheets are:

 Title Bar: Displays the workbook name and application.
 Toolbar: Provides access to Excel commands (e.g., Home, Insert).
 Name Box: Shows the active cell’s address (e.g., B3).
 Formula Bar: Displays or edits data/formulas in the active cell.
 Column Headings: Lettered (A–XFD) to identify columns.
 Row Headings: Numbered (1–1,048,576) to identify rows.
 Cell: Intersection of a row and column (e.g., A1).
 Cell Reference: Identifies a cell (e.g., A1) for use in formulas.
 Navigation Buttons: Move between worksheets in a workbook.
 Sheet Tabs: List worksheets; add or rename via right-click.
5. Editing or Deleting Cell Contents
To delete cell content follow the following steps:
Step 1: To alter or delete the text in a cell, first select it.
Step 2: Press the Backspace key on our keyboard to delete and correct text. Alternatively,
hit the Delete key to delete the whole contents of a cell. we can also edit and delete text
using the formula bar. Simply select the cell and move the pointer to the formula bar.
6. Commonly Spreadsheet Applications

Detailed Explanation-

Worksheet: After opening an Excel workbook, we get a window of Excel to perform any
required operation that is the worksheet.
Cell: The cell is the shortest part of Excel. Usually, a cell is denoted by the combination of
row and column headings. Cell A1 means that the cell is located in the first column and first
row. Cell numbers are unique.
Active Cell: When we click on any cell, it becomes the active cell. The address of the active
cell is shown in the Name Box at the upper left corner of the sheet.
Row: Row is the horizontal collection of cells and is denoted by a number. On the left side of
the sheet, you can see the row bar that indicates all rows. Excel has 1,048,576 rows in total.
Column: The column is the vertical collection of cells and is denoted by alphabetic
characters. You will have a bar on the upper side of the worksheet consisting of alphabetic
characters starting from A, that is the column bar. Each character of this bar indicates
individual columns. Excel has 16,384 columns in total.
Title Bar: The Title bar is the horizontal bar that contains the name of the Excel file and is
located at the top of the workbook.
Quick Access Toolbar: The Quick Access Toolbar or QAT is a customized toolbar, located
at the left-upper side of the workbook. We gather all the frequently used commands here so
that there is no need to search for them.
Control Buttons: Control buttons are located at the upper-right side of the workbook and are
used for control purposes like minimizing, maximizing, and closing.
Ribbon: The Ribbon is the key interface in Excel that organizes and contains various
commands. It is divided into tabs, each housing groups of related commands. It was first
introduced in Excel 2007 and is available in all the latest versions including Excel 365.
Formula Bar: Formula bar is located below the ribbon. We can insert, modify, and delete
any value or formula in Excel from this bar. We can also see the formula of any cell in this
bar.
Name Box: The Name Box is on the left side of the Formula Bar. We can see the address cell
or name of a range from this box. We can also go to the desired cell or select the range by
inserting the cell reference or name in this box.
Scroll Bar: The scroll bar is used to navigate the Excel worksheet in 4 directions. There are
two scroll bars: the horizontal scroll bar for left and right, and the vertical scroll bar for up
and down directions.
Sheet Tab: The sheet tab contains the names of all available sheets on the workbook. We can
also create new sheets from there. It is also called the leaf bar. It is located at the bottom left
corner of a workbook above the Status Bar.
Status Bar: The status bar is a horizontal bar located at the bottom of the workbook. It
indicates the current status of the selected cell and other mathematical calculations like sum,
average, count, etc.
Zoom Slider: It refers to the zoom adjustment of Excel workbooks that ranges from 10% to
400%. It is located at the bottom-right corner of the Excel workbook.
View Buttons: This button refers to different ways to present the workbook in Excel. There
are three modes: Normal, Page Layout, and Page Break Preview.

Excel's structure is made of two pieces, the Ribbon and the Sheet.

Have a look at the picture below. The Ribbon is marked with a red rectangle and the Sheet is
marked with a yellow rectangle:
First, let's start with explaining the Ribbon.

The Ribbon explained

The Ribbon provides shortcuts to Excel commands. A command is an action that allows you
to make something happen. This can for example be to: insert a table, change the font size, or
to change the color of a cell.

The Ribbon may look crowded and hard to understand at first. Don't be scared, It will
become easier to navigate and use as you learn more. Most of the time we tend to use the
same functionalities over again.

The Ribbon is made up by the App launcher, Tabs, Groups and Commands. In this
section we will explain the different parts of the Ribbon.
App launcher

The App launcher icon has nine dots and is called the Office 365 navigation bar. It allows
you to access the different parts of the Office 365 suite, such as Word, PowerPoint and
Outlook. App launcher can be used to switch seamlessly between the Office 365 applications.

Tabs

The tab is a menu with sub divisions sorted into groups. The tabs allow users to quickly
navigate between options of menus which display different groups of functionality.

Groups

The groups are sets of related commands. The groups are separated by the thin vertical line
break.

Commands

The commands are the buttons that you use to do actions.

Now, let's have a look at the Sheet. Soon you will be able to understand the relationship
between the Ribbon and the Sheet, and you can make things happen.

The Sheet explained

The Sheet is a set of rows and columns. It forms the same pattern as we have in math
exercise books, the rectangle boxes formed by the pattern are called cells.

Values can be typed to cells.

Values can be both numbers and letters:

Multiple Sheets

You start with one Sheet by default when you create a new workbook. You can have many
sheets in a workbook. New sheets can be added and removed. Sheets can be named to making
it easier to work with data sets.

First, click the plus icon, shown in the picture below, create two new sheets:

Tip: You can use the hotkey Shift + F11 to create new sheets. Try it!

Second, right click with your mouse on the relevant sheet and click rename:
Second, right click with your mouse on the relevant sheet and click rename:
Third, enter useful names for the three sheets:

In this example we used the names Data Visualization, Data Structure and Raw Data. This
is a typical structure when you are working with data.

Below are some common spreadsheet application in excel-

 Microsoft Excel: Part of Microsoft Office/365, available on Windows, macOS,
Android, iOS.
 Google Sheets: Free, cloud-based, supports desktop and mobile platforms.
 Apache OpenOffice Calc: Free, open-source, available on Windows, macOS, Linux.
 LibreOffice Calc: Free, open-source, similar to OpenOffice, for multiple platforms.

Formulas and Functions in MS-Excel-

Two significant terms, Formula and Function, are unique ingredients that play with the
dataset and give you an accurate result in less time.

Excel formulas are mathematical expressions to compute the integer values usually defined in
the worksheet.
Functions are inbuilt formulas in Microsoft Excel that evaluate complex mathematical and
statistical problems.

Basic Functions in Excel-

We have a dataset of vegetable costs from a week in a household. Using this, we explore
essential Excel functions to analyze the data effectively.
1. Sum Function
This function adds all values within a selected range, helping us calculate the total cost of
vegetables.
Syntax:
=SUM(number1, [number2], ...)
Where,
 number1, [number2]: are the numbers, cell references (e.g., C3:C8), or ranges to add
together. Use this to calculate the total of values, like summing vegetable costs in
C3:C8
2. Max Function
We use this to identify the highest value in a range, such as finding the most expensive
vegetable.
Syntax:
=MAX(number1, [number2], ...)
Where,
 number1, [number2]: are the numbers or range (e.g., C5:C12) to evaluate. Apply this to
find the highest value, such as the most expensive item in a list.

3.

3. Min Function
This helps us find the lowest value in a range, useful for spotting the cheapest item.
Syntax:
=MIN(number1, [number2], ...)
Where,
 number1, [number2]: are the numbers or range (e.g., C5:C10) to evaluate. Use this to
determine the lowest value, like the cheapest vegetable.
4. Average Function

We calculate the average value in a range to understand typical costs.

Syntax:
=AVERAGE(number1, [number2], ...)
Where,
 number1, [number2]: are the numbers or range (e.g., C5:C10) to average. Employ this
to find the mean value, such as the average cost of items.
5. Count Function

This counts the number of cells with values in a range, aiding in data tracking.
Syntax:
=COUNT(number1, [number2], ...)
Where,
 number1, [number2]: are the numbers or range (e.g., C5:C10) to count.
6. Len Function

We use this to determine the number of characters in a text string, useful for data
validation.
Syntax:
=LEN(text)
Where,
 text is a cell reference (e.g., A5) containing the text string to measure. Apply this to
count characters, like the length of "brinjal".
7. Sumif Function

This adds values in a range that meet a specific condition, enhancing selective calculations.
Syntax:
=SUMIF(range, criteria, [sum_range])
Where,
 range is the cells to check (e.g., C5:C10), criteria is the condition (e.g., ">20"), and
[sum_range] (optional) is the cells to sum if different from range. Use this to add values
meeting a condition, like costs over 20.
8. AverageIf Function

We calculate the average of values meeting a condition with this function.

Syntax:
=AVERAGEIF(range, criteria, [average_range])
Where,
 range is the cells to check (e.g., C5:C10), criteria is the condition (e.g., ">20"), and
[average_range] (optional) is the cells to average if different from range. Apply this to
average values meeting a condition, like costs above 20.
Sorting and Filtering Data in Excel-

Excel is a powerful tool that allows users to manage and manipulate large amounts of data
quickly and efficiently. One of the key features of Excel is the ability to sort and filter data,
which can be especially useful for working with datasets that contain hundreds or even
thousands of rows of information. In this article, we'll explore how to use Excel's sorting and
filtering capabilities to organize and analyze data.
Sorting can be done in ascending or descending order based on a single column or multiple
columns. Filtering allows users to view specific data based on criteria, such as dates, text, or
numerical values. Excel also provides advanced filtering options, such as filtering by color or
by using complex criteria.
Sorting Data
Sorting Data by One Column
Sorting data by one column is a straightforward process that allows users to rearrange data
based on the values in a specific column. To sort data by one column, follow these steps:
  Select the range of cells that you want to sort.
 Click the "Data" tab on the Excel ribbon.
 Click the "Sort A-Z" button to sort data in ascending order or the "Sort Z-A" button to
sort data in descending order. Excel will automatically sort the data based on the
values in the selected column, either in ascending or descending order.

Sorting Data by Multiple Columns

Sorting data by multiple columns is a more advanced technique that allows users to sort data
based on two or more criteria. This is especially useful when sorting data that has multiple
columns that contain related information. To sort data by multiple columns, follow these
steps:
 Select the range of cells that you want to sort.
 Click the "Home" tab on the Excel ribbon.
 Click the "Sort & Filter" button to display the "Sort & Filter" menu.
 Click the "Custom Sort" option to open the "Sort" dialog box.
 In the "Sort" dialog box, select the first column that you want to sort by and choose
the sort order (ascending or descending).
 Click the "Add Level" button to add another column to sort by.
 Repeat steps 5 and 6 until you have added all of the columns that you want to sort by.
 Click the "OK" button to apply the sorting criteria.

Excel will now sort the data based on the values in the selected columns, in the order that
they were added. For example, if you sorted by column A first and then by column B, Excel
would sort the data based on the values in column A first, and then sort the data within each
group of values in column A based on the values in column B.