Excel Basics for Data Analytics

Excel Basics for Data Analytics

Microsoft Excel is a spreadsheet software that is widely used to store, organize, calculate, and analyze data. It is one of the most commonly used tools in data analytics because it provides simple features for working with tables, formulas, charts, and basic data analysis.

Learning Excel basics for data analytics helps beginners understand how to enter data, organize records, perform calculations, and prepare data for further analysis.

In this article, we will learn the basic concepts of Excel, including worksheets, rows, columns, cells, data entry, formatting, formulas, and simple calculations.

What is Excel?

Excel is a spreadsheet application that stores data in a tabular format using rows and columns. Each intersection of a row and column is called a cell.

For example, a small dataset can be created in Excel as follows:

This type of table can be used to perform basic calculations and analyze employee data.

Excel Workbook and Worksheet

An Excel workbook is the complete Excel file used to store and work with data. A workbook can contain one or more worksheets, so we can keep related data in the same file without creating separate Excel files.

For example, an employee management workbook may contain different worksheets such as Employees, Salary, Attendance, and Reports. Each worksheet can contain different types of information related to the same project.

A worksheet is the working area inside an Excel workbook where we enter, organize, and analyze data. It consists of rows and columns, which form individual cells. We can enter text, numbers, dates, formulas, and other data into these cells.

Rows in Excel

Rows are arranged horizontally and are identified using numbers such as:

1, 2, 3, 4, 5, ...

For example, the first record in a dataset may be stored in row 2 if row 1 contains the column headings.

Columns in Excel

Columns are arranged vertically and are identified using letters such as:

A, B, C, D, ...

For example:

A = Name B = Age C = Department D = Salary

What is a cell in Excel?

A cell is the place where a row and column meet. Each cell has a unique address.

For example:

A1 B2 C3 D4

Here, A1 means column A and row 1.

Suppose we enter the following data:

The value Amit is stored in cell A2, while 30000 is stored in cell C2.

Entering Data in Excel

Excel allows us to enter different types of data, such as text, numbers, dates, and formulas.

For example:

Here:

• Amit and Neha are text values.
• 24 and 26 are numeric values.
• 10/01/2026 and 15/02/2026 are date values.

Formatting Data in Excel

Formatting changes the appearance of data and makes a worksheet easier to read.

Common formatting options include

• Bold text
• Font size
• Cell borders
• Number format
• Date format
• Text alignment
• Background formatting

Basic Excel Formulas

Excel formulas are used to perform calculations and work with data in a worksheet. A formula can perform simple mathematical operations or use built-in Excel functions to calculate results automatically.

A formula usually starts with the = sign. After entering a formula, Excel calculates the result based on the values in the referenced cells. If the referenced values change, Excel can automatically update the result.

Common Excel Operators

Some commonly used mathematical operators in Excel are:

SUM Function

The SUM() function is used to add multiple values or a range of cells.

Suppose the salaries are stored in cells D2:D5:

We can calculate the total salary using:

=SUM(D2:D5)

Output:

135000

Explanation

The SUM() function adds all numeric values from D2 to D5.

30000 + 35000 + 32000 + 38000 = 135000

AVERAGE Function

The AVERAGE() function is used to calculate the average of a group of numbers.

For example:

=AVERAGE(D2:D5)

Output

33750

Explanation

Excel adds all the salary values and divides the total by the number of values.

135000 / 4 = 33750

MAX Function

The MAX() function returns the highest value from a range.

=MAX(D2:D5)

Output:

38000

In this example, 38000 is the highest salary in the selected range.

MIN Function

The MIN() function returns the lowest value from a range.

=MIN(D2:D5)

Output:

30000

Here, 30000 is the lowest salary in the selected range.

COUNT Function

The COUNT() function counts the number of cells that contain numeric values.

For example:

=COUNT(D2:D5)

Output:

4

This means that four cells in the selected range contain numbers.

FILTER Function in Excel

The FILTER() function is used to extract only the data that matches a given condition. It is useful when we want to view specific records from a larger dataset without deleting or changing the original data.

Suppose we have the following employee data:

If the data is stored in A2:C5, we can use the following formula to display only employees from the IT department:

=FILTER(A2:C5,B2:B5="IT")

Output:

Explanation

In this formula:

=FILTER(A2:C5,B2:B5="IT")

• A2:C5 is the range from which we want to return data.
• B2:B5="IT" is the condition.
• Excel returns only the rows where the Department is IT.

The original data remains unchanged, while the filtered results are displayed separately.

FILTER Function with a Salary Condition

We can also use FILTER() to find employees whose salary is greater than 32000.

=FILTER(A2:C5,C2:C5>32000)

Output:

Explanation

Here, Excel checks the values in the Salary column and returns only those records where the salary is greater than 32000.

The FILTER() function is particularly useful in data analytics with Excel because it allows us to quickly extract specific records from a dataset based on conditions.

IF Function

The IF() function is used to return different results depending on whether a condition is true or false.

For example, suppose the salary is stored in C2. We can check whether the salary is above 32000:

=IF(C2>32000,"High","Low")

If C2 contains 35000, the output will be:

High

Explanation

The formula checks whether the salary is greater than 32000.

• If the condition is true, Excel returns High.
• If the condition is false, Excel returns Low.

Why Learn Excel for Data Analytics?

Excel is useful for beginners because it provides an easy way to work with datasets and perform basic analysis.

With Excel, we can:

• It is used to organize data into tables.
• It is used to perform calculations using formulas.
• It is used to sort and filter the records.
• It finds totals and averages of records.
• It creates charts and reports.
• It is used to identify patterns in data.
• It prepares data for further analysis.