MS - Excel

About Excel
Data entry and editing features in Excel, copy, paste, paste special options, sort and filter
options, auto sum, steps of finding average and standard deviation of data using statistical
functions. Matrix operations like transpose, multiply and inverse using Excel functions. Simple
graphs like bar chart, line chart and pie chart in Excel.

Introduction to Spreadsheets

Spreadsheets involves the collection, organization, analysis, and manipulation of data in a

structured format using a spreadsheet application like Microsoft Excel, Google Sheets, or
LibreOffice Calc. Spreadsheets are powerful tools for handling numerical, textual, and
graphical data in tabular form, making them ideal for a wide range of data processing tasks.
Key Features of Spreadsheets:
1. Grid Structure: Spreadsheets consist of a grid of cells organized into rows and
columns. Each cell can hold data, such as numbers, text, or formulas.
2. Formulas and Functions: Spreadsheets allow the use of built-in functions and custom
formulas to perform calculations, analyze data, and automate tasks.
3. Data Manipulation: Data can be sorted, filtered, and transformed easily using built-in
tools. Users can create charts, graphs, pivot tables, and other visual representations of
data.
4. Automation: Macros and scripting can automate repetitive tasks and data processing
workflows.
Types of Using Spreadsheets:
1. Data Entry and Storage: Spreadsheets allow users to input and store large amounts of
data in an organized way. Each cell can represent a different data point, making it easy
to track and maintain records.
2. Data Analysis: By using formulas and functions, users can perform complex
calculations, statistical analysis, and forecasting. For instance, functions like SUM,
AVERAGE, and IF are commonly used for quick calculations.
3. Data Visualization: Spreadsheets provide tools for creating visual aids such as charts,
graphs, and pivot tables. This helps users interpret data patterns, trends, and
relationships effectively.
4. Data Sorting and Filtering: Spreadsheets allow users to organize data based on
various criteria, making it easy to sort, filter, and group data for better analysis and
reporting.
5. Reporting and Presentation: Once processed, spreadsheet data can be summarized in
reports, graphs, or tables for presentation purposes, aiding decision-making.

Sri GCSR College Department of Computer Science 1

 Applications of Spreadsheets:
• Financial Analysis: Spreadsheets are widely used for budgeting, financial forecasting,
and performance analysis.
• Data Management: Companies use spreadsheets to manage customer information,
inventory, sales data, and other operational data.
• Academic and Research Use: Spreadsheets are used for statistical analysis,
experiment tracking, and result reporting.
• Project Management: Spreadsheets can track project timelines, resource allocation,
and cost management.
Advantages of Spreadsheets in Data Processing:
1. User-Friendly Interface: Spreadsheets are accessible to both novice and experienced
users due to their simple and intuitive interface.
2. Versatility: They support a wide variety of data types and formats, making them
applicable to different fields.
3. Cost-Effective: Spreadsheet software is often included in office suites, making it an
affordable solution for data processing.
4. Flexibility: Users can tailor spreadsheets to meet specific data processing needs, from
simple record keeping to advanced data analysis.
Introduction to Microsoft Excel

Microsoft Excel is one of the most widely used spreadsheet applications in the world. It is part
of the Microsoft Office suite and provides powerful tools for data organization, analysis,
calculation, and visualization. Excel’s versatility and robust features make it essential in
various fields, including business, finance, engineering, education, and research.
Key Features of Excel:
1. Grid Layout: Excel is built on a grid of rows and columns. Each cell in the grid can
contain data, such as numbers, text, or formulas. Columns are labeled with letters (A,
B, C…), while rows are labeled with numbers (1, 2, 3…).
2. Formulas and Functions: Excel allows users to perform mathematical, logical, and
statistical operations using formulas and pre-built functions. These include: o Basic
Functions: SUM, AVERAGE, MIN, MAX, COUNT o Logical Functions: IF, AND,
OR, NOT

o Text Functions: CONCATENATE, LEFT, RIGHT, TRIM o Date & Time

Functions: TODAY, NOW, DATEDIF o Lookup Functions: VLOOKUP,
HLOOKUP, INDEX, MATCH

Sri GCSR College Department of Computer Science 2

 3. Data Visualization: Excel offers a variety of charts and graphs, such as pie charts, bar
graphs, and line charts, to help users visualize data trends and patterns. The PivotTable
and PivotChart features provide advanced tools for data summarization and reporting.
4. Data Sorting and Filtering: Excel enables users to sort data based on various criteria
(e.g., alphabetical order, ascending/descending values) and filter datasets to display
only specific records.
5. Data Analysis Tools:
o PivotTables: PivotTables are used for data summarization, enabling users to
quickly analyze large data sets by breaking them down into more manageable
summaries.
o What-If Analysis: Excel’s "What-If" analysis tools, including Goal Seek and
Scenario Manager, allow users to forecast and model different scenarios based
on variable inputs..
6. Conditional Formatting: Users can apply formatting rules to cells based on the values
they contain. For example, cells with values greater than a certain number can be
highlighted in green, or text can be automatically colored red if it matches a specific
condition.
7. Macros and Automation: Excel allows users to automate repetitive tasks using
macros, which are sequences of commands recorded and played back. These macros
are typically written in VBA (Visual Basic for Applications), providing advanced users
with the ability to create custom tools and automate complex workflows.
8. Collaboration Features: Excel supports real-time collaboration, allowing multiple
users to edit a document simultaneously, especially when stored in the cloud through
services like OneDrive or SharePoint.
Applications of Excel:
1. Business and Finance: Excel is extensively used for financial analysis, budgeting, and
forecasting. It allows businesses to manage income statements, balance sheets, cash
flows, and perform investment analyses.
2. Data Management: Excel serves as a tool for organizing, storing, and analyzing large
datasets. It is commonly used for customer databases, inventory tracking, and project
management.
3. Statistical and Scientific Analysis: Researchers and scientists use Excel for statistical
analysis, trend forecasting, and complex data modeling. Excel’s charting capabilities
help visualize experimental data and trends.
4. Education: Educators use Excel for managing grades, creating timetables, and
preparing course schedules. Students often use Excel for assignments, data analysis,
and learning data handling skills.
5. Project Management: Excel is often used to create Gantt charts, manage project
timelines, allocate resources, and track project progress.

Sri GCSR College Department of Computer Science 3

 Advantages of Excel:
• Ease of Use: Excel has an intuitive interface, which makes it accessible to users with
varying levels of expertise.
• Versatility: Excel supports various tasks, from simple data entry to complex financial
modeling.
• Data Capacity: Excel can handle a significant amount of data, with over 1 million rows
and 16,000 columns available in each worksheet.
• Integration: Excel integrates easily with other Microsoft Office applications (Word,
PowerPoint, Access) and can import/export data in a wide range of formats (e.g., CSV,
XML, TXT).
• Customization: Advanced users can create custom functions, automate workflows
using VBA, and design specialized dashboards for reporting and analysis.
Components of Excel Spreadsheet

An Excel spreadsheet consists of several key parts that allow users to organize, manage, and
analyze data. Here are the main components of an Excel spreadsheet:
1. Workbook
• A workbook is the entire Excel file that contains one or more worksheets. It is the file
saved with a .xlsx or .xls extension.
2. Worksheet (Sheet)
• A worksheet is a single page within a workbook where data is entered and organized.
Each workbook can contain multiple worksheets, usually labeled as Sheet1, Sheet2, etc.
3. Rows and Columns
• Rows: Horizontal lines of data, numbered along the left side (1, 2, 3, etc.). Each row
extends across the spreadsheet.
• Columns: Vertical lines of data, labeled by letters at the top (A, B, C, etc.). Columns
extend down the spreadsheet.
4. Cells
• A cell is the individual box where a row and column intersect. Each cell can contain
text, numbers, formulas, or other data.
• Cell Reference: Each cell has a unique reference (or address), which is a combination
of its column letter and row number (e.g., A1, C5).
5. Cell Range
• A range is a selection of two or more cells. It is specified by the top-left and bottom-
right corners of the range.

Sri GCSR College Department of Computer Science 4

 Ribbon

Name box
Formula Bar

Active cell
Column

Scroll Bar

row
Tab
Status Bar

6. Formula Bar
• The formula bar is located above the worksheet and displays the content of the selected
cell. It is also where users enter or edit data, formulas, and functions for the active cell.
7. Ribbon
• The ribbon is a toolbar at the top of the Excel window, containing various tabs such as
Home, Insert, Formulas, Data, Review, and View. Each tab provides different tools
for performing specific tasks.
8. Name Box
• The name box is located next to the formula bar. It displays the reference of the
currently selected cell or range. You can also use the name box to navigate to a specific
cell by typing in its address.
9. Tabs/Worksheets Navigation
• Tabs at the bottom of the spreadsheet allow you to navigate between different
worksheets within a workbook. You can also add, rename, delete, or move worksheets.

10. Status Bar

• The status bar is located at the bottom of the Excel window and displays information
about the current mode, the status of data, and certain operations like the sum, average,
or count of selected cells.
11. Scroll Bars
• Excel has vertical and horizontal scroll bars that allow you to navigate through a
worksheet that is larger than the screen display.
12. Gridlines

Sri GCSR College Department of Computer Science 5

 • Gridlines are the faint horizontal and vertical lines that separate cells. These help define
the structure of the spreadsheet but do not appear in printed copies unless specified.
13. Headers and Footers
• Headers and footers are areas at the top and bottom of each printed page where you can
insert information like page numbers, dates, or document titles.
14. Active Cell
• The active cell is the currently selected cell where data entry or editing is performed. It
is highlighted with a thicker border.
15. Cell Styles and Formatting
• Excel provides various options to format the appearance of cells, such as changing the
font, cell color, borders, and number formats (e.g., currency, percentages).

Steps to enter data in excel sheet

1. Open Excel
Launch Microsoft Excel by clicking on the Excel icon or opening it from your start menu
or desktop.
2. Create a New Workbook or Open an
Existing One
• New Workbook: Click on File > New
> Blank Workbook.
• Existing Workbook: Click on File >
Open to open an existing workbook.
3. Select a Cell
Click on a cell where you want to enter
data. The active cell will be highlighted
with a border, and its address will be
displayed in the "Name Box" (e.g., A1 for
the first column and row).
4. Enter Data
• Start typing your data (e.g., numbers, text, dates) into the selected cell. Excel will
automatically detect the type of data you're entering.
• Press Enter to confirm the entry and move to the cell below.
• To enter data into multiple cells in a row, press Tab to move to the right after each entry

Sri GCSR College Department of Computer Science 6

 Steps for editing features in Excel
In-Cell Editing
• Double-Click to Edit:
1. Open your Excel workbook.
2. Navigate to the cell you want to edit.
3. Double-click the cell. The cursor will appear, allowing you to edit the content
directly.
4. After editing, press Enter to save changes, or Esc to cancel.
• F2 Key:
1. Select the cell you want to edit.
2. Press F2 on your keyboard. This will put the cell into edit mode.
3. Make your changes.
4. Press Enter to save the changes or Esc to cancel.
• Clear/Delete Content:
1. Select the cell or range of cells you want to clear.
2. Press the Delete key to remove the content while retaining formatting.
3. To remove formatting as well, go to Home > Editing > Clear > Clear All.
Copy, Cut, and Paste
• Copy (Ctrl + C):
1. Select the cell or range of cells you want to copy.
2. Press Ctrl + C, or right-click and select Copy.
3. Navigate to the destination cell.
4. Press Ctrl + V, or right-click and select Paste.

• Cut (Ctrl + X):

1. Select the cell or range you want to cut.
2. Press Ctrl + X, or right-click and choose Cut.
3. Move to the destination cell.
4. Press Ctrl + V, or right-click and choose Paste.
• Paste Special:
1. Copy or cut your data.
2. Right-click on the destination cell.
3. Choose Paste Special from the context menu.

Sri GCSR College Department of Computer Science 7

 4. In the dialog box, select the desired paste option (e.g., Values, Formats) and
click OK.
Undo and Redo
• Undo (Ctrl + Z):
1. Perform an action you want to undo.
2. Press Ctrl + Z, or click the Undo button (curved arrow) in the Quick Access
Toolbar.
• Redo (Ctrl + Y):
1. If you’ve undone an action and want to redo it, press Ctrl + Y, or click the
Redo button (curved arrow pointing right) in the Quick Access Toolbar.

Find and Replace

• Find (Ctrl + F):
1. Press Ctrl + F to open the Find dialog box.
2. Type the text or number you want to find.
3. Click Find Next to search through the data.

• Replace (Ctrl + H):

1. Press Ctrl + H to open the Replace dialog box.
2. Enter the text or number you want to find in the Find what box.
3. Enter the replacement text or number in the Replace with box.

Sri GCSR College Department of Computer Science 8

 4. Click Replace to replace the current instance or Replace All to replace all
instances.

Fill Handle for Copying and AutoFill

• Using Fill Handle:
1. Enter data in a cell (e.g., "1").
2. Hover over the bottom-right corner of the cell until you
see a small square (the fill handle).
3. Drag the fill handle down or across to copy the data or
continue a series (e.g., numbers, dates).
• AutoFill Options:
1. After using the fill handle, click the AutoFill Options icon that appears.
2. Choose how you want to fill the cells (e.g., Fill Series, Fill Formatting Only).
Insert or Delete Rows and Columns
• Insert Rows/Columns:
1. Right-click the row number or column
letter where you want to insert a new row or
column.

2. Select Insert from the context menu. A new

row or column will be added.

• Delete Rows/Columns:
1. Right-click the row number or column
letter you want to delete.
2. Select Delete from the context menu. The
row or column will be removed, and data will shift accordingly.

Sri GCSR College Department of Computer Science 9

 Merge and Unmerge Cells
• Merge Cells:
1. Select multiple cells you want to merge.
2. Go to the Home tab, and in the Alignment group, click Merge & Center. You
can also choose Merge Across or Merge Cells from the dropdown menu.

• Unmerge Cells:
1. Select the merged cell.
2. Go to the Home tab, and in the Alignment group, click Merge & Center again,
or choose Unmerge Cells from the dropdown menu.
Text Alignment and Formatting
• Change Alignment:
1. Select the cells you want to align.
2. Go to the Home tab, and in the Alignment group, choose the desired alignment
options (e.g., Left, Center, Right, Top, Middle, Bottom).
• Bold, Italic, Underline:
1. Select the cells with text you want to format.
2. Use the Bold (Ctrl + B), Italic (Ctrl + I), or Underline (Ctrl + U) buttons in the
Home tab.
Font and Size:

Sri GCSR College Department of Computer Science 10

 3. Select the cells you want to format.
4. Go to the Home tab, and in the Font group, choose the desired font type and
size.
• Number Formatting:
1. Select the cells with numbers.
2. Go to the Home tab, and in the Number group, choose the desired format (e.g.,
Currency, Percentage, Date).
Wrap Text and Shrink to Fit
• Wrap Text:
1. Select the cells where you want to wrap text.
2. Go to the Home tab, and in the Alignment group, click Wrap Text.
• Shrink to Fit:
1. Select the cells you want to adjust.
2. Go to the Home tab, and in the Alignment group, click the small arrow in the
bottom-right corner to open the Format Cells dialog.
3. Go to the Alignment tab, and check the Shrink to Fit box. 12. Adjust Cell Size
• Resize Rows/Columns:
1. Drag the boundary between row numbers or column letters to resize.
2. For precise resizing, right-click the row number or column letter, choose Row
Height or Column Width, and enter the desired size.
• AutoFit:
1. Double-click the boundary between row numbers or column letters to
automatically adjust the size based on the content.

AutoSum in Excel
The AutoSum feature in Microsoft Excel is a quick and efficient way to sum up a range of numbers
in your spreadsheet. It eliminates the need to manually write formulas and is widely used for
adding values in rows or columns.
Steps to Use AutoSum in Excel
1. Locate the AutoSum Button:
o It is found in the Home tab in the Editing group.

Sri GCSR College Department of Computer Science 11

 o Alternatively, it’s in the Formulas tab under the Function Library group.

2. Select the Cell:

o Click on the cell where you want the sum to appear.
3. Click the AutoSum Button: o Press the Σ (AutoSum) button, or use the
shortcut Alt + = on your keyboard.
4. Auto-Select the Range:
o Excel automatically highlights a range of adjacent numbers to sum up.
o If the selected range is not correct, click and drag to select the desired range
manually.

5. Press Enter:
o Once the correct range is highlighted, press Enter to complete the operation.
o The sum of the selected numbers will appear in the chosen cell.

Sri GCSR College Department of Computer Science 12

Formatting Cell
MS Excel Cell can hold different types of data like Numbers, Currency, Dates, etc. You can
set the cell type in various ways as shown below:
Right Click on the cell » Format cells » Number.
• Click on the Ribbon from the ribbon.
• Below are the various cell formats.

• General: This is the default cell format of Cell.

• Number: This displays cell as number with separator.
• Currency: This displays cell as currency i.e. with currency sign.
• Accounting: Similar to Currency, used for accounting purpose.
• Date: Various date formats are available under this, like 17-09-2013, 17thSep-2013,
etc.
• Time: Various Time formats are available under this like 1.30PM, 13.30, etc.
• Percentage: This displays cell as percentage with decimal places like 50.00%.
• Fraction: This displays cell as fraction like 1/4, 1/2 etc.

• Scientific: This displays cell as exponential like 5.6E+01.

• Text: This displays cell as normal text. Special: Special formats of cell like Zip code,
Phone Number.
• Custom: You can use custom format by using this.

Excel Conditional Formatting

MS Excel Conditional Formatting feature enables you to format a range of values so that the
values outside certain limits, are automatically formatted.
Choose Home Tab » Style group » Conditional Formatting dropdown.

Sri GCSR College Department of Computer Science 13

 Various Conditional Formatting Options
• Highlight Cells Rules: It opens a continuation menu with various options for defining the
formatting rules that highlight the cells in the cell selection
CONDITIONAL FORMATING
that contain certain values, text, or dates, or that have values greater or less than a particular
value, or that fall within a certain ranges of values.
Suppose you want to find cell with Amount 0 and Mark them as red. Choose Range of cell »
Home Tab » Conditional Formatting DropDown » Highlight Cell Rules » Equal To.
After Clicking ok, the cells with value zero are marked as red.

Sri GCSR College Department of Computer Science 14

 Top/Bottom Rules: It opens a continuation menu with various options for defining the
formatting rules that highlight the top and bottom values, percentages, and above and below
average values in the cell selection.
Suppose you want to highlight the top 10% rows, you can do this with these Top/Bottom
rules.

Data Bars: It opens a palette with different color data bars that you can apply to the cell
selection to indicate their values relative to each other by clicking the data bar thumbnail.
With this conditional Formatting, data Bars will appear in each cell.

Sri GCSR College Department of Computer Science 15

 Color Scales: It opens a palette with different three- and two-colored scales that you can apply
to the cell selection to indicate their values relative to each other by clicking the color scale
thumbnail.
See the below screenshot with Color Scales, conditional formatting applied.

Icon Sets: It opens a palette with different sets of icons that you can apply to the cell selection
to indicate their values relative to each other by clicking the icon set.
See the below screenshot with Icon Sets, conditional formatting applied.

New Rule: It opens the New Formatting Rule dialog box, where you define a custom
conditional formatting rule to apply to the cell selection.

Sri GCSR College Department of Computer Science 16

 Clear Rules: It opens a continuation menu, where you can remove the conditional formatting
rules for the cell selection by clicking the Selected Cells option, for the entire worksheet by
clicking the Entire Sheet option, or for just the current data table by clicking the This Table
option.
Manage Rules: It opens the Conditional Formatting Rules Manager dialog box, where you
edit and delete particular rules as well as adjust their rule precedence by moving them up or
down in the Rules list box.

Methods to Sort data in Excel

Sorting allows you to rearrange data in a specific order, either ascending or descending, based on
one or more columns.
How to Sort Data
1. Select the Data:
o Highlight the range of cells or the table you want to sort. Include headers for better
results.
2. Access the Sort Option:
o Go to the Data tab in the ribbon and locate the Sort & Filter group. o Click
Sort for more advanced sorting options.

3. Sort by a Column:
o In the Sort dialog box, choose the column you want to sort by.
o Select the sort order:
▪ A to Z (Ascending) or Z to A (Descending) for text.
▪ Smallest to Largest or Largest to Smallest for numbers.
▪ Oldest to Newest or Newest to Oldest for dates.
4. Add Levels (Optional):

Sri GCSR College Department of Computer Science 17

 o To sort by multiple columns, click Add Level and specify additional columns and
sort orders.

5. Click OK:
o Your data will now be sorted based on the chosen criteria.

How to Filter data in Excel

Filtering allows you to display only the data that meets specific criteria, hiding the rest temporarily.
How to Apply Filters
1. Select the Data: o Highlight the table or range of cells you want to filter.
2. Enable Filters:
o Go to the Data tab and click the Filter button (a funnel icon).
o Small dropdown arrows will appear in the header of each column.
3. Filter by Criteria:
o Click the dropdown arrow on the column you want to filter.
o Choose one or more filter options:
▪Text
Filters
(e.g.,

"Contains," "Starts with," "Equals").

▪ Number Filters (e.g., "Greater than," "Less than," "Between").
▪ Date Filters (e.g., "This Week," "Next Month," "Last Year").
o Check/uncheck specific items to display.

Sri GCSR College Department of Computer Science 18

 4. Clear Filters:
o To remove a filter,
click the dropdown
arrow and choose
Clear Filter from
[Column Name]. o
Alternatively, click
the Clear button in
the Data tab.
What are the Functions in Excel?
Functions in Excel are predefined formulas that help perform calculations, manipulate data,
and automate tasks. They are categorized into different types:
1. Mathematical & Trigonometric Functions
• SUM(range): Adds all the numbers in a range.
• AVERAGE(range): Returns the average (arithmetic mean).
• ROUND(number, digits): Rounds a number to a specified number of digits.
• ABS(number): Returns the absolute value.
• MOD(number, divisor): Returns the remainder after division.
2. Logical Functions
• IF(condition, value_if_true, value_if_false): Returns one value if a condition is true and
another if false.
• AND(condition1, condition2, ...): Returns TRUE if all conditions are true.
• OR(condition1, condition2, ...): Returns TRUE if at least one condition is true.
• NOT(condition): Reverses the logical value.
3. Text Functions
• LEFT(text, num_chars): Extracts a specific number of characters from the start.
• RIGHT(text, num_chars): Extracts a specific number of characters from the end.
• MID(text, start_num, num_chars): Extracts characters from the middle of a string.
• LEN(text): Returns the length of a text string.
• CONCATENATE(text1, text2, ...) or TEXTJOIN(delimiter, ignore_empty, text1, text2,
...): Joins multiple text values.
4. Date & Time Functions
• TODAY(): Returns the current date.

Sri GCSR College Department of Computer Science 19

 • NOW(): Returns the current date and time.
• YEAR(date), MONTH(date), DAY(date): Extracts the year, month, or day from a date.
• DATEDIF(start_date, end_date, unit): Calculates the difference between two dates.
• EOMONTH(start_date, months): Returns the last day of a month.

5. Lookup & Reference Functions

• VLOOKUP(value, table, column_index, [range_lookup]): Searches for a value in a table.
• HLOOKUP(value, table, row_index, [range_lookup]): Searches for a value in a
horizontal table.
6. Statistical Functions
• COUNT(range): Counts the number of numeric values.
• COUNTA(range): Counts the number of non-empty cells.
• COUNTIF(range, criteria): Counts cells that meet a condition.
• LARGE(array, k), SMALL(array, k): Returns the k-th largest or smallest value.
7. Financial Functions
• PMT(rate, nper, pv): Calculates loan payments.
• FV(rate, nper, pmt): Returns the future value of an investment.
• NPV(rate, value1, value2, ...): Calculates the net present value.

Here are detailed explanations and examples for each Mathematical & Trigonometric
Function in Excel:
1. SUM(range) - Adds all the numbers in a range
Formula:
=SUM(A1:A5)
Example:
If cells A1 to A5 contain 2, 4, 6, 8, 10, the result will be:
=SUM(A1:A5) → 2 + 4 + 6 + 8 + 10 = 30
2. AVERAGE(range) - Returns the arithmetic mean
Formula:
=AVERAGE(A1:A5)
Example:
If A1 to A5 contain 10, 20, 30, 40, 50, the result will be:
=AVERAGE(A1:A5) → (10+20+30+40+50)/5 = 30
3. ROUND(number, digits) - Rounds a number to a specified number of digits
Formula:
=ROUND(A1, 2)

Sri GCSR College Department of Computer Science 20

Example:
If A1 = 12.5678,
=ROUND(A1, 2) → 12.57 (Rounded to 2 decimal places)
=ROUND(A1, 1) → 12.6 (Rounded to 1 decimal place)
=ROUND(A1, 0) → 13 (Rounded to the nearest integer)
4. ABS(number) - Returns the absolute value (removes the negative sign)
Formula:
=ABS(A1)
Example:
If A1 contains -25, then:
=ABS(A1) → 25
If A1 contains 30, then:
=ABS(A1) → 30
5. MOD(number, divisor) - Returns the remainder after division
Formula:
=MOD(A1, B1)
Example:
If A1 = 10 and B1 = 3, then:
=MOD(A1, B1) → 10 ÷ 3 = 3 remainder **1** → Result = **1**
If A1 = 25 and B1 = 4, then:
=MOD(A1, B1) → 25 ÷ 4 = 6 remainder **1** → Result = **1**
These functions are commonly used for mathematical calculations in Excel.
Do you need more examples or explanations on other functions? 😊
Logical Functions in Excel with Examples
Logical functions help perform decision-making operations by evaluating conditions and
returning TRUE or FALSE. Here are the most commonly used logical functions:
1. IF Function
Checks a condition and returns one value if true and another if false.
Formula:
=IF(condition, value_if_true, value_if_false)
Example:
If we want to check if a student has passed or failed based on marks in A1, where 50 is the
passing mark:
=IF(A1>=50, "Pass", "Fail")
A1 (Marks) Output
70 Pass
45 Fail

Sri GCSR College Department of Computer Science 21

 A1 (Marks) Output
50 Pass
2. AND Function
Returns TRUE if all conditions are true; otherwise, it returns FALSE.
Formula:
=AND(condition1, condition2, ...)
Example:
If a student needs at least 50 marks in both Math (A1) and Science (B1) to pass, we can use:
=AND(A1>=50, B1>=50)
A1 (Math) B1 (Science) Output
60 55 TRUE
45 55 FALSE
50 50 TRUE
3. OR Function
Returns TRUE if at least one condition is true; otherwise, it returns FALSE.
Formula:
=OR(condition1, condition2, ...)
Example:
If a student passes if they get at least 50 marks in either Math (A1) or Science (B1):
=OR(A1>=50, B1>=50)
A1 (Math) B1 (Science) Output
60 45 TRUE
45 45 FALSE
50 40 TRUE
4. NOT Function
Reverses the logical value: TRUE becomes FALSE and vice versa.
Formula:
=NOT(condition)
Example:
If we want to check if a number is NOT greater than 50:
=NOT(A1>50)
A1 A1>50 Output (NOT A1>50)
60 TRUE FALSE
40 FALSE TRUE

Sri GCSR College Department of Computer Science 22

 A1 A1>50 Output (NOT A1>50)
50 FALSE TRUE
Combining Logical Functions
You can combine these functions for more complex logic.
Example: IF + AND
If a student needs at least 50 marks in both Math (A1) and Science (B1) to pass, we can use:
=IF(AND(A1>=50, B1>=50), "Pass", "Fail")

A1 (Math) B1 (Science) Output

60 55 Pass
45 55 Fail
50 50 Pass
Example: IF + OR
If a student passes if they get at least 50 marks in either Math (A1) or Science (B1):
=IF(OR(A1>=50, B1>=50), "Pass", "Fail")

A1 (Math) B1 (Science) Output

60 45 Pass
45 45 Fail
50 40 Pass
These logical functions are very useful for decision-making in Excel.
Would you like more examples or help with a specific scenario? 😊
Text Functions in Excel with Examples
Text functions help manipulate and extract parts of text data in Excel. Here’s how they work
with examples:
1. LEFT Function
Extracts a specific number of characters from the start of a text string.
Formula:
=LEFT(A1, num_chars)
Example:
If A1 = "ExcelFunctions"
Formula Output
=LEFT(A1, 5) Excel
=LEFT(A1, 3) Exc

Sri GCSR College Department of Computer Science 23

2. RIGHT Function
Extracts a specific number of characters from the end of a text string.
Formula:
=RIGHT(A1, num_chars)
Example:
If A1 = "ExcelFunctions"
Formula Output
=RIGHT(A1, 5) tions
=RIGHT(A1, 3) ons
3. MID Function
Extracts a substring from the middle of a text string based on a starting position and
length.
Formula:
=MID(A1, start_num, num_chars)
Example:
If A1 = "ExcelFunctions"
Formula Output
=MID(A1, 6, 4) Func
=MID(A1, 2, 3) xce

4. LEN Function
Returns the total number of characters in a text string (including spaces).
Formula:
=LEN(A1)
Example:
If A1 = "Excel Functions"
Formula Output
=LEN(A1) 15
(Includes the space between "Excel" and "Functions")
5. CONCATENATE (or TEXTJOIN) Function
Joins multiple text values into a single string.
Using CONCATENATE (Old Method)
Formula:
=CONCATENATE(A1, " ", B1)
Example:
If A1 = "Hello", and B1 = "World"

Sri GCSR College Department of Computer Science 24

 Formula Output
=CONCATENATE(A1, " ", B1) Hello World
Using TEXTJOIN (New & More Powerful)
Formula:
=TEXTJOIN(" ", TRUE, A1, B1, C1)
Example:
If A1 = "Excel", B1 = "is", C1 = "Powerful"
Formula Output
=TEXTJOIN(" ", TRUE, A1, B1, C1) Excel is Powerful
Combining Text Functions
Example: Extracting First Name from Full Name
If A1 = "John Doe" and you want only "John":
=LEFT(A1, FIND(" ", A1) - 1)
Example: Extracting Last Name
=RIGHT(A1, LEN(A1) - FIND(" ", A1))
For A1 = "John Doe", this returns "Doe".

Date & Time Functions in Excel with Examples

Date & Time functions help in working with dates, calculating differences, and extracting
date parts in Excel.
1. TODAY() - Returns the current date
This function returns only the current date (without the time).
Formula:
=TODAY()
Example Output (if today is March 5, 2025):
=TODAY() → 05-03-2025
(The format depends on your system settings.)
2. NOW() - Returns the current date and time
This function returns both date and time.
Formula:
=NOW()
Example Output (if today is March 5, 2025, and the time is 2:45 PM):
=NOW() → 05-03-2025 14:45
3. YEAR(), MONTH(), DAY() - Extracts parts of a date
These functions extract specific parts of a date.
Formulas:

Sri GCSR College Department of Computer Science 25

=YEAR(A1) 'Extracts the year
=MONTH(A1) 'Extracts the month
=DAY(A1) 'Extracts the day
Example:
If A1 = "15-08-2020"
Formula Output
=YEAR(A1) 2020
=MONTH(A1) 8
=DAY(A1) 15
4. DATEDIF() - Calculates the difference between two dates
This function calculates the difference in years, months, or days between two dates.
Formula:
=DATEDIF(start_date, end_date, unit)
Units:
• "Y" → Difference in years
• "M" → Difference in months
• "D" → Difference in days
Example:
If A1 = "01-01-2000" and B1 = "05-03-2025"
Formula Output
=DATEDIF(A1, B1, "Y") 25
=DATEDIF(A1, B1, "M") 302
=DATEDIF(A1, B1, "D") 9201
5. EOMONTH() - Returns the last day of a month
This function gives the last date of a month based on a given start date.
Formula:
=EOMONTH(start_date, months)
• months = 0 → Returns the last day of the current month
• months = 1 → Returns the last day of the next month
• months = -1 → Returns the last day of the previous month
Example:
If A1 = "15-03-2025"
Formula Output Explanation
=EOMONTH(A1, 0) 31-03-2025 Last day of March 2025
=EOMONTH(A1, 1) 30-04-2025 Last day of April 2025

Sri GCSR College Department of Computer Science 26

 Formula Output Explanation
=EOMONTH(A1, -1) 29-02-2025 Last day of February 2025 (leap year)
Combining Date Functions
1. Find Age from Birthdate
If A1 contains Date of Birth, find age in years:
=DATEDIF(A1, TODAY(), "Y")
If A1 = "15-08-1995", the result will be 29 (in 2025).
2. Get First Day of the Month
=DATE(YEAR(A1), MONTH(A1), 1)
If A1 = "15-03-2025", the output will be "01-03-2025".
3. Get Last Day of the Current Year
=DATE(YEAR(A1), 12, 31)
If A1 = "15-03-2025", the output will be "31-12-2025".
Statistical & Financial Functions in Excel with Examples
Excel provides statistical functions to analyze data and financial functions to perform
calculations related to investments and loans. Below are the most commonly used functions with
examples.
6. Statistical Functions
1. COUNT(range) - Counts the number of numeric values
This function counts only numbers in a given range.
Formula:
=COUNT(A1:A10)
Example:
A
10
20
30
ABC
40
=COUNT(A1:A6) → Output: 3 (Only numeric values are counted)
2. COUNTA(range) - Counts the number of non-empty cells
This function counts both numbers and text but ignores blank cells.
Formula:
=COUNTA(A1:A10)
Example:

Sri GCSR College Department of Computer Science 27

 A
10
20
30
ABC
40
=COUNTA(A1:A6) → Output: 4 (Counts all non-empty values)
3. COUNTIF(range, criteria) - Counts cells that meet a condition
This function counts values based on a specific condition.
Formula:
=COUNTIF(A1:A10, ">50")
Example:
A
60
45
75
30
90
=COUNTIF(A1:A5, ">50") → Output: 3 (Counts values greater than 50)
4. LARGE(array, k) - Returns the k-th largest value
This function finds the k-th largest value in a dataset.
Formula:
=LARGE(A1:A10, 2)
Example:
A
10
40
60
20
80
=LARGE(A1:A5, 2) → Output: 60 (Second-largest number)
5. SMALL(array, k) - Returns the k-th smallest value
This function finds the k-th smallest value in a dataset.

Sri GCSR College Department of Computer Science 28

Formula:
=SMALL(A1:A10, 3)
Example:
A
10
40
60
20
80
=SMALL(A1:A5, 3) → Output: 40 (Third-smallest number)
7. Financial Functions
1. PMT(rate, nper, pv) - Calculates loan payments
This function calculates the monthly installment (EMI) for a loan.
Formula:
=PMT(rate, nper, pv)
Where:
• rate = Interest rate per period
• nper = Number of periods (months)
• pv = Loan amount (present value)
Example:
A loan of ₹10,000 at an annual interest rate of 12%, to be paid in 24 months.
=PMT(12%/12, 24, -10000)
Output: ₹-471.78 (Negative sign means outgoing payment)

Steps of finding average of data using statistical functions.

Excel provides built-in statistical functions to calculate the average (mean) and standard
deviation of a dataset. Here's how to compute these:
Finding Average
Step 1: Enter Your Data
1. Open an Excel spreadsheet.
2. Input your dataset into a column or row (e.g., A1:A5).
Step 2: Calculate the Average
1. Select a Cell for the Result: o Click on a blank cell where you want the
average to appear.
2. Use the AVERAGE Function:

Sri GCSR College Department of Computer Science 29

 o Type the formula:
=AVERAGE (A1:A5)
Replace A1:A5 with the range of your dataset.
3. Press Enter:
o Excel will display the average of the data.

Cell Referencing In Excel Introduction

Cell referencing in Excel is a fundamental concept used in formulas and functions. It determines
how a formula behaves when copied or moved to different cells. There are three types of cell
references in Excel:

1. Relative Reference
2. Absolute Reference
3. Mixed Reference

1. Relative Cell Reference

Definition

A relative reference is the default type of cell reference in Excel. It adjusts automatically when
the formula is copied to another cell.

Example

Let's say you have the following values:

A B C

1 5 10

2 15 20

If you enter the formula =B1+C1 in A1, Excel adds the values in B1 and C1. Now, if you copy
this formula to A2, the formula will automatically change to =B2+C2.

Use Case

• Best for applying formulas to multiple rows or columns.

• Commonly used in tables, data series, and when applying formulas using the Fill Handle.

Example with Autofill

1. Enter =B1+C1 in A1.

2. Drag the Fill Handle (bottom-right corner of the cell) down to A2.
3. Excel automatically changes the formula in A2 to =B2+C2.

Sri GCSR College Department of Computer Science 30

 2. Absolute Cell Reference
Definition

An absolute reference does not change when copied or moved. It remains fixed, making it
useful when referring to constant values. Absolute references are indicated using a $ sign before
the column and row ($A$1).

Example

Consider a scenario where you have a constant tax rate in cell B1 (e.g., 10%). You want to
calculate the tax for multiple products:

A (Product) B (Price) C (Tax Rate) D (Tax Amount)

Apple 100 10% (B1) =B2*$B$1

Banana 200 =B3*$B$1

If you copy the formula =B2*$B$1 from D2 to D3, the reference to B2 will change to B3, but
$B$1 remains fixed.

Use Case

• Used for fixed values like tax rates, exchange rates, and fixed discounts.
• Essential for financial models and calculations where some values must remain
constant.

Shortcut to Convert a Reference to Absolute

1. Select the cell reference in the formula bar.

2. Press F4 to toggle between relative, absolute, and mixed references.

3. Mixed Cell Reference

Definition

A mixed reference locks either the row or the column but not both. It is useful in scenarios
where either row or column values remain fixed while the other varies.

Types of Mixed References

1. Column Fixed, Row Relative ($A1)

o The column (A) stays fixed, but the row changes when copied.
2. Row Fixed, Column Relative (A$1)
o The row (1) stays fixed, but the column changes when copied.

Sri GCSR College Department of Computer Science 31

Example (Column Fixed: $A1)

Imagine you are calculating commissions based on different rates stored in Column A, but sales
data varies across Row 1:

A (Commission %) B (Sales Rep 1) C (Sales Rep 2)

5% ($A1) 100 200

10% ($A2) 150 250

Formula in B2: =B2*$A1

• If copied to C2, it changes to =C2*$A1 (Column A remains fixed).

Example (Row Fixed: A$1)

Imagine a scenario where you apply a fixed tax rate from Row 1 across different products:

A (Product) B (Price) C (Tax Rate in Row 1) D (Tax Amount)

Apple 100 10% (C1) =B2*C$1

Banana 200 =B3*C$1

• If copied down, Row 1 stays fixed, but the column changes accordingly.

Use Case

• Used in tables and financial modeling where either row or column needs to remain
constant.
• Common in multiplication tables and dynamic calculations.

Comparison Table
Reference Type Example Behavior When Copied

Relative A1 Adjusts based on the new location

Absolute $A$1 Remains constant

Mixed (Column Fixed) $A1 Column remains fixed, row changes

Mixed (Row Fixed) A$1 Row remains fixed, column changes

Practical Applications of Cell Referencing

1. Using Relative References in a Sales Table

If you have a list of sales and want to apply a 10% commission, you can use a relative reference:

Sri GCSR College Department of Computer Science 32

 A (Product) B (Sales) C (Commission Formula) D (Commission Amount)

Apple 100 =B2*10% 10

Banana 200 =B3*10% 20

2. Using Absolute References for a Fixed Tax Rate

If the tax rate is stored in B1 and needs to be applied to all products:

A (Product) B (Price) C (Tax Rate - $B$1) D (Tax Amount)

Apple 100 10% (B1) =B2*$B$1

Banana 200 =B3*$B$1

Here, $B$1 remains fixed even when copied.

3. Using Mixed References in a Multiplication Table

To create a multiplication table:

A B C D

1 1 2 3

2 1 =$A2*B$1 =$A2*C$1 =$A2*D$1

3 2 =$A3*B$1 =$A3*C$1 =$A3*D$1

• $A2 keeps column A fixed while the row changes.

• B$1 keeps row 1 fixed while the column changes.

Creating Simple Graphs in Excel: Bar Chart, Line Chart, and Pie Chart
Graphs and charts are essential for visually representing data in Excel. They help to interpret and
analyze data more easily. Below are the steps for creating simple graphs like bar charts, line
charts, and pie charts in Excel.
Bar Chart in Excel
A bar chart is used to represent data with rectangular bars, where the length of the bar is
proportional to the value.
Steps to Create a Bar Chart:

Sri GCSR College Department of Computer Science 33

1. Enter Data: Product Sales
• Prepare the data in a tabular format. Example: A 50
2. Select the Data: B 70
• Highlight the range of data you want to include in the chart (including C 40
headers). D 90
Insert the Bar Chart:
• Go to the Insert tab in the ribbon.
• In the Charts group, click on the Bar Chart icon.

• Choose the type of bar chart you prefer (e.g., Clustered Bar, Stacked Bar, etc.).
3. Customize the Chart (Optional):
• After the chart is inserted, you can customize it by adding chart elements (like titles,
labels), changing colors, or modifying the axis.

4. Result: The bar chart will display the sales of each product as bars.
Line Chart in Excel

Sri GCSR College Department of Computer Science 34

A line chart is used to show trends over time or categories. It connects data points with a line,
helping to visualize changes.
Steps to Create a Line Chart:
1. Enter Data:
• Example data for a line chart: Month Sales
2. Select the Data: Jan 200
• Highlight the data range you want to chart (including headers). Feb 300
3. Insert the Line Chart:
Mar 250
• Go to the Insert tab in the ribbon.
Apr 400
• In the Charts group, click on the Line Chart icon.
• Choose the type of line chart you prefer (e.g., Line, Stacked Line, etc.).
4. Customize the Chart (Optional):
• You can add titles, gridlines, change the line color, and adjust axes for better
visualization.
5. Result:

• The line chart will show the trend of sales over the months.
Pie Chart in Excel

A pie chart is used to show proportions of a whole, where each slice represents a category’s
contribution to the total.
Steps to Create a Pie Chart:

Sri GCSR College Department of Computer Science 35

 Category Value

1. Enter Data: A 50

• Example data for a pie chart: B 25

2. Select the Data: C 15

• Highlight the data range you want to chart (excluding total or any D 10
irrelevant data).
3. Insert the Pie Chart:
• Go to the Insert tab in the ribbon.
• In the Charts group, click on the Pie Chart icon.
• Choose the type of pie chart you prefer (e.g., 2-D Pie, 3-D Pie).
4. Customize the Chart (Optional):
• Add a chart title, labels, or a legend for clarity.
• Right-click on the chart to format individual slices (e.g., change color or add data labels).
5. Result:
• The pie chart will display the percentage share of each category in the total.

Additional Customizations (for all charts):

1. Add Chart Elements:
o After creating the chart, use the Chart Elements button (plus sign) next to the
chart to add or remove elements like titles, axis labels, legends, and data labels.
2. Change Chart Style and Layout:
o Use the Chart Styles and Chart Layouts options under the Design tab to change
the look and feel of your chart.

3. Modify Axis and Labels:

o Right-click on the axes or labels to change the scale, format, or appearance.

Sri GCSR College Department of Computer Science 36

VLOOKUP and HLOOKUP in Excel
VLOOKUP (Vertical Lookup) and HLOOKUP (Horizontal Lookup) are two powerful functions
in Excel used to search for data in tables. They help retrieve values based on a key from structured
datasets.

VLOOKUP (Vertical Lookup)

Definition: VLOOKUP is used to search for a value in the first column of a table and return a
corresponding value from another column.

VlookUp searches for a value vertically down for the lookup table.
=VLOOKUP (lookup_value,table_array,col_index_num,range_lookup)
has 4 parameters as below.
lookup_value: It is the user input. This is the value that the function uses to search on.
The table_array: It is the area of cells in which the table is located. This includes not only the
column being searched on, but the data columns for which you are going to get the values that
you need.
Col_index_num: It is the column of data that contains the answer that you want.
Range_lookup: It is a TRUE or FALSE value. When set to TRUE, the lookup function gives
the closest match to the lookup_value without going over the lookup_value. When set to FALSE,
an exact match must be found to the lookup_value or the function will return #N/A. Note, this
requires that the column containing the lookup_value be formatted in ascending order.
You have a table of employee details:
Employee ID Name Department Salary (₹)
101 Rahul IT ₹50,000
102 Priya HR ₹45,000
103 Arjun Sales ₹55,000
If you want to find Priya's Salary using her Employee ID (102):
=VLOOKUP(102, A2:D4, 4, FALSE)

VLOOKUP Example
Let's look at a very simple example of cross-referencing two spreadsheets. Each spreadsheet
contains information about the same group of people. The first spreadsheet has their dates of
birth, and the second shows their favorite color. How do we build a list showing the person's
name, their date of birth and their favorite color? VLOOOKUP will help in this case. First of all,
let us see data in both the sheets.
.

Sri GCSR College Department of Computer Science 37

This is data in the first sheet

This is data in the second sheet.

Sri GCSR College Department of Computer Science 38

Now for finding the respective favorite color for that person from another sheet we need to
vlookup the data. First argument to the VLOOKUP is lookup value (In this case it is person
name). Second argument is the table array, which is table Excel in the second sheet from B2 to
C11. Third argument to VLOOKUP is Column index num, which is the answer we are looking
for. In this case, it is 2 the color column number is 2. The fourth argument is True returning partial
match or false returning exact match. After applying VLOOKUP formula it will calculate the
color and the results are displayed as below.

As you can see in the above screen-shot that results of VLOOKUP has searched for color in the
second sheet table. It has returned #N/A in case where match is not found. In this case, Andy's
data is not present in the second sheet so it returned #N/A.

Sri GCSR College Department of Computer Science 39