UNIT – 3

MS EXCEL

Microsoft Excel is a powerful spreadsheet application developed by Microsoft. It is a part of the

Microsoft Office suite and is widely used for various purposes, including data analysis,
calculation, visualization, and organization. Here's an introduction to Microsoft Excel:

Features and Capabilities:

1. Grid Interface: Excel's main interface consists of cells organized in rows and columns forming
a grid, where users input data, numbers, text, or formulas.

2. Formulas and Functions: Excel offers a wide range of built-in functions and formulas for
mathematical, statistical, financial, and logical calculations. Functions like SUM, AVERAGE, IF,
VLOOKUP, and many others assist in data manipulation and analysis.

3. Data Analysis Tools: It provides tools for sorting, filtering, and analyzing data sets, including
pivot tables, data validation, conditional formatting, and what-if analysis.

4. Charts and Graphs: Users can create various types of charts and graphs to visually represent
data, including bar charts, pie charts, line graphs, histograms, and more.

5. Data Import and Export: Excel supports importing data from external sources such as
databases, text files, and web data. It also allows exporting data in different formats for use in
other applications.

6. Collaboration and Sharing: Excel enables collaboration among users by allowing multiple
people to work on a spreadsheet simultaneously. It also integrates with Microsoft's cloud
service, OneDrive, facilitating easy sharing and real-time collaboration.

7. Macros and Automation: Advanced users can create macros (automated scripts) using VBA
(Visual Basic for Applications) to automate repetitive tasks or perform complex operations.

Common Use Cases:

1. Financial Analysis: Excel is extensively used in finance for budgeting, forecasting, financial
modeling, and managing financial data.

2. Business Analytics: It's employed for data analysis, reporting, and decision-making in
businesses for tracking sales, inventory, expenses, and more.
3. Educational and Academic Purposes: Excel is widely used in educational settings for teaching
and learning purposes, especially in mathematics, statistics, and data analysis courses.

4. Project Management: It's used for creating Gantt charts, project schedules, tracking project
expenses, and managing tasks in project management.

Versions and Platforms:

➢ Excel is available on various platforms including Windows, macOS, iOS, and Android.
➢ Different versions of Excel are released periodically, each introducing new features and
enhancements.

Excel's versatility and range of functionalities make it a popular tool for individuals, businesses,
educators, and professionals across diverse industries for data management, analysis, and
visualization.

INSERTING ROWS AND COLUMNS:

In Microsoft Excel, inserting rows and columns is a common task that allows users to add space
for additional data or modify the structure of the worksheet. Here's how you can insert rows
and columns in Excel:

Inserting Rows:

1. Inserting a Single Row:

➢ Right-click on the row number below where you want to insert the new row.
➢ Select "Insert" from the context menu. This action inserts a new row above the selected
row.

2. Inserting Multiple Rows:

➢ Select the number of rows that you want to insert. To do this, click and drag to highlight
the same number of rows as you want to insert.
➢ Right-click on the selected rows.
➢ Choose "Insert" from the context menu. This action inserts the same number of rows
above the selected rows.

3. Using the Ribbon:

➢ Select the row below where you want to insert the new row.
➢ Go to the Home tab in the Ribbon.
 ➢ Click on the "Insert" dropdown in the Cells group and choose "Insert Sheet Rows."

Inserting Columns:

1. Inserting a Single Column:

➢ Right-click on the column letter to the right of where you want to insert the new column.
➢ Select "Insert" from the context menu. This action inserts a new column to the left of
the selected column.

2. Inserting Multiple Columns:

➢ Select the number of columns that you want to insert. To do this, click and drag to
highlight the same number of columns as you want to insert.
➢ Right-click on the selected columns.
➢ Choose "Insert" from the context menu. This action inserts the same number of columns
to the left of the selected columns.

3. Using the Ribbon:

➢ Select the column to the right of where you want to insert the new column.
➢ Go to the Home tab in the Ribbon.
➢ Click on the "Insert" dropdown in the Cells group and choose "Insert Sheet Columns."

Important Notes:

➢ Inserting rows or columns shifts existing data down or to the right, respectively, to
accommodate the new rows or columns.
➢ You can also use keyboard shortcuts: Ctrl + Shift + "+" (Plus Sign) to insert rows or
columns.
➢ Excel allows inserting rows or columns in multiple selected areas simultaneously.

SIZING ROWS AND COLUMNS:

In Microsoft Excel, adjusting the size of rows and columns allows users to optimize the layout
and presentation of data within a worksheet. Here's how you can size rows and columns in
Excel:

Sizing Rows:

1. AutoFit Row Height:

 ➢ Double-click the boundary between two row headers (on the left side of the worksheet).
➢ This action automatically adjusts the row height to fit the content within the row.

2. Manually Adjusting Row Height:

➢ Place the cursor on the boundary between two row headers (the line separating two
rows) until it changes to a double-headed arrow.
➢ Click and drag the boundary up or down to adjust the height of the row manually.

3. Setting a Specific Row Height:

➢ Select the row or rows you want to resize by clicking and dragging over the row numbers
on the left side of the worksheet.
➢ Right-click on the selected rows.
➢ Choose "Row Height" from the context menu and enter the desired row height value.

Sizing Columns:

1. AutoFit Column Width:

➢ Double-click the boundary between two column headers (at the top of the worksheet).
➢ This action automatically adjusts the column width to fit the content within the column.

2. Manually Adjusting Column Width:

➢ Place the cursor on the boundary between two column headers until it changes to a
double-headed arrow.
➢ Click and drag the boundary left or right to adjust the width of the column manually.

3. Setting a Specific Column Width:

➢ Select the column or columns you want to resize by clicking and dragging over the
column letters at the top of the worksheet.
➢ Right-click on the selected columns.
➢ Choose "Column Width" from the context menu and enter the desired column width
value.

Tips:

➢ To resize multiple rows or columns simultaneously, select the desired rows or columns
before adjusting their sizes.
➢ You can also adjust row height and column width using the Format options available in
the Home tab under the Cells group or by using keyboard shortcuts.
Sizing rows and columns in Excel allows you to customize the layout of your spreadsheet,
ensuring that data is displayed clearly and efficiently. Adjusting row heights and column widths
helps in improving readability and presenting information in a well-organized manner.

IMPLEMENTING FORMULAS:

Implementing formulas in Excel is fundamental to perform calculations, analyze data, and

automate processes within spreadsheets. Formulas in Excel start with an equal sign (=) followed
by a combination of cell references, values, operators, and functions. Here's an explanation of
how to implement formulas in Excel:

Basics of Formulas:

1. Cell References:

➢ Use cell references (e.g., A1, B2, C3) to refer to cells containing values or data to be
used in calculations. For example, "=A1+B1" adds the values in cells A1 and B1.

2. Operators:

➢ Excel supports arithmetic operators: addition (+), subtraction (-), multiplication (*),
division (/), and exponentiation (^). For example, "=C1*D1" multiplies the values in cells
C1 and D1.

3. Functions:

➢ Excel offers a vast library of functions to perform specific calculations or operations.

Functions are used as =FUNCTION_NAME(arguments). Examples include SUM,
AVERAGE, IF, VLOOKUP, and many others.

How to Enter Formulas:

1. Basic Arithmetic Operations:

➢ Select the cell where you want the result to appear.

➢ Start the formula with an equal sign (=).
➢ Enter the formula using cell references and operators. For example, "=A1+B1" or
"=SUM(A1:B1)".
➢ 2. Using Functions:
➢ Select the cell where you want the result.
➢ Start the formula with an equal sign (=).
 ➢ Type the function name followed by open parenthesis (.
➢ Enter the arguments or cell references within the parentheses. For example,
"=SUM(A1:B5)" adds the values in the range from A1 to B5.

3. Autosum Feature:

➢ Use the Autosum button (Σ) in the Home tab to quickly sum a range of cells. Select the
cell below or beside the data you want to sum, click Autosum, and press Enter.

GENERATING SERIES:

In Microsoft Excel, the "Fill Series" feature allows you to quickly generate a series of data, such
as numbers, dates, months, weekdays, or custom lists, without manually entering each value.
Here's how you can generate series in Excel:

Generating Numeric Series:

1. Using AutoFill for Numeric Series:

➢ Enter the starting values (e.g., 1, 2) in consecutive cells.

➢ Select the cells containing the initial values.
➢ Click and drag the fill handle (a small square at the bottom-right corner of the
selection) down or across to automatically fill the series.

2. Using the Fill Series Dialog:

➢ Enter the starting value in a cell.

➢ Select the cell containing the starting value.
➢ Go to the Home tab.
➢ In the Editing group, click on "Fill" and then select "Series."
➢ Choose the Series Type (Linear, Growth, Date, AutoFill), set the Step Value (if
necessary), and specify the Stop Value (if required).
➢ Click OK to generate the series.

Generating Date Series:

1. AutoFill for Dates:

➢ Enter the starting date in a cell (e.g., January 1, 2023).

➢ Select the cell containing the starting date.
➢ Click and drag the fill handle to generate a series of dates.
2. Using the Fill Series Dialog for Dates:

➢ Enter the starting date in a cell.

➢ Select the cell containing the starting date.
➢ Go to the Home tab.
➢ In the Editing group, click on "Fill" and then select "Series."
➢ Choose "Date" as the Series Type, specify the Step Value (day, month, year), and set the
Stop Value (if needed).
➢ Click OK to create the date series.

Custom Series and List:

1. Using Custom Lists:

➢ Define a custom list (e.g., specific weekdays, months, or any custom sequence).
➢ Go to File > Options > Advanced.
➢ Scroll down to the General section and click "Edit Custom Lists."
➢ Enter the custom list values or import them from a range in your worksheet.
➢ Click Add to create the custom list.

2. Using Custom Fill Series:

➢ Enter a few initial values in a sequence in cells.

➢ Select the cells containing the initial sequence.
➢ Go to the Home tab.
➢ In the Editing group, click on "Fill" and then select "Series."
➢ Choose "AutoFill" or "Linear" as the Series Type and click OK.
Based on specific requirements.

FUNCTIONS:

Functions in Excel are pre-built formulas designed to perform specific calculations or operations
on data within a worksheet. These functions are categorized based on their purposes, such as
mathematical, statistical, financial, logical, text manipulation, date and time, lookup/reference,
and more. Here's an overview of functions in Excel:

Types of Functions:

1. Mathematical Functions:

➢ SUM: Adds a range of numbers.

 ➢ AVERAGE: Calculates the average of a range of numbers.
➢ MAX/MIN: Returns the largest/smallest value in a range.
➢ ROUND: Rounds a number to a specified number of digits.

2. Statistical Functions:

➢ STDEV: Calculates the standard deviation based on a sample.

➢ MEDIAN: Returns the median value in a range of numbers.
➢ COUNT/COUNTA: Counts the number of cells containing numerical/non-numerical
values.

3. Financial Functions:

➢ PV/FV: Calculates the present/future value of an investment.

➢ RATE: Determines the interest rate for a loan or investment.

4. Logical Functions:

➢ IF: Performs a conditional test and returns one value if the condition is true, another if
false.
➢ AND/OR: Checks multiple conditions and returns true/false based on the logic.

5. Text Functions:

➢ LEFT/RIGHT/MID: Extracts characters from a text string.

➢ CONCATENATE: Joins multiple text strings into one.

6. Date and Time Functions:

➢ TODAY/NOW: Returns the current date/time.

➢ DATE/DATEDIF: Constructs or calculates differences between dates.

7. Lookup and Reference Functions:

➢ VLOOKUP/HLOOKUP: Looks up a value in a table and returns a corresponding value.

➢ INDEX/MATCH: Retrieves values at a specified location in a range.

How to Use Functions:

1. Syntax:

➢ Functions follow a specific syntax: =FUNCTION_NAME(argument1, argument2, ...).

➢ Arguments can be cell references, constants, or other functions.

2. Entering Functions:
 ➢ Type "=" in a cell to begin a formula.
➢ Enter the function name and provide the necessary arguments within parentheses.
➢ Press Enter to apply the function and display the result.

3. Function AutoComplete:

➢ Start typing a function name, and Excel suggests available functions with a description.
Use Tab to complete the function name.

4. Function Wizard:

➢ Use the Insert Function button (fx) on the formula bar to launch the Function Wizard,
which guides you through selecting and entering function arguments.

CREATION OF CHARTS:

Creating charts in Excel is an effective way to visualize data, allowing users to interpret trends,
patterns, and comparisons more easily. Here's a step-by-step guide on how to create charts in
Excel:

Steps to Create a Chart:

1. Select Data:

➢ Highlight the data range you want to include in the chart. Include headers if applicable.

2. Insert a Chart:

➢ Go to the Insert tab in the Ribbon.

3. Choose Chart Type:

➢ Click on the desired chart type (e.g., Column, Line, Pie, Bar, etc.) in the Charts group.
Select the dropdown to see all available chart types.

4. Customize Chart Elements:

➢ Once the chart is inserted, Excel generates a default chart based on the selected data.
➢ You can customize various chart elements, such as axes, titles, legends, data labels,
colors, and more. Click on the chart elements to access formatting options.

5. Move or Resize the Chart:

 ➢ Click and drag the chart to move it to a different location within the worksheet.
➢ Use the sizing handles to resize the chart as needed.

Tips for Specific Chart Types:

Column/Bar Charts:

➢ Ideal for comparing values across categories.

➢ Choose between clustered or stacked columns/bars based on your data.

Line Charts:

➢ Effective for showing trends over time.

➢ Suitable for displaying continuous data series.

Pie/Donut Charts:

➢ Use to illustrate proportions or percentages.

➢ Limit the number of categories for better clarity.
➢ Area Charts:
➢ Show trends over time while emphasizing the magnitude of change.
➢ Similar to line charts but the area below the line is filled.

Scatter Plots:

➢ Display relationships between two sets of data.

➢ Useful for identifying correlations or patterns.

Advanced Chart Features:

Combination Charts:

➢ Create charts with multiple data series using different chart types within the same chart.

Secondary Axes:

➢ Use when data series have different scales, allowing better comparison.

Chart Templates:

➢ Save customized charts as templates for reuse in other workbooks.

➢ Interactive Charts:
➢ Utilize slicers, filters, and pivot charts for interactivity and dynamic data visualization.

Chart Design and Styles:

 ➢ Excel provides various chart styles and layouts under the Chart Design tab in the Ribbon,
allowing customization and fine-tuning of the chart's appearance.

Creating charts in Excel is a powerful way to present data visually, aiding in understanding
trends, comparisons, and relationships among different data sets. Excel's flexibility and range of
chart types offer users multiple options for effectively showcasing their data.

INSERTING OBJECTS:

In Microsoft Excel, you can insert various objects to complement your spreadsheet, including
images, shapes, charts, hyperlinks, text boxes, and more. Here's how to insert different objects
in Excel:

Inserting Objects:

1. Inserting Pictures:

➢ Go to the Insert tab in the Ribbon.

➢ Click on "Pictures" to insert an image from your computer.
➢ Browse and select the image file, then click "Insert."

2. Inserting Shapes:

➢ Click on the "Shapes" option in the Illustrations group on the Insert tab.
➢ Select the desired shape from the dropdown list and draw it on the worksheet.

3. Inserting Charts:

➢ Select the data range you want to visualize in a chart.

➢ Go to the Insert tab and choose the desired chart type (e.g., Column, Line, Pie) from the
Charts group.

4. Inserting Hyperlinks:

➢ Highlight the cell or text where you want to insert the hyperlink.
➢ Go to the Insert tab, click on "Hyperlink," and enter the URL or select a location within
the workbook to link to.

5. Inserting Text Boxes:

➢ Click on the "Text Box" option in the Text group on the Insert tab.
➢ Draw a text box and enter text or information.
6. Inserting SmartArt:

➢ Go to the Insert tab and select "SmartArt" to create visual representations like
diagrams, processes, or cycles.

7. Inserting Objects from Other Files:

➢ Go to the Insert tab, click on "Object" in the Text group.

➢ Choose to insert an object from a file (e.g., Word document, PDF, etc.) or create a new
object from available programs.

Embedded vs. Linked Objects:

➢ Embedded Objects: The inserted object becomes part of the Excel file. Changes made to
the original file won't affect the embedded object in Excel.
➢ Linked Objects: The inserted object is linked to an external file. Changes made to the
original file reflect in the linked object within Excel.

Object Formatting and Editing:

➢ Once inserted, most objects can be formatted, resized, and customized using the Format
tab in the Ribbon or by right-clicking and selecting formatting options.

Object Placement and Alignment:

➢ Click and drag objects to reposition them within the worksheet.

➢ Use alignment options available in the Format tab to align objects precisely.

Object Interaction and Protection:

➢ Some objects, like charts and shapes, can be interactive. For example, you can add
hyperlinks to shapes or data points in a chart.
➢ Protecting a worksheet can prevent accidental deletion or modification of inserted
objects.

Inserting various objects in Excel helps in enhancing the visual appeal of your spreadsheets and
conveying information effectively. Excel's versatility allows users to incorporate different
elements to complement and illustrate data, making it more understandable and engaging.
FILTERING, SORTING, AND INSERTING WORKSHEET:

Certainly! Here's an overview of filtering, sorting, and inserting worksheets in Microsoft Excel:

Filtering Data:

Filtering allows users to display specific data subsets based on defined criteria, hiding rows that
do not meet the filter conditions.

1. Applying Filters:

➢ Select the data range.

➢ Go to the Data tab and click on the "Filter" button. Alternatively, use the shortcut Ctrl +
Shift + L.
➢ Dropdown arrows appear in the header row. Click on these arrows to apply filters and
select specific criteria to display only the relevant data.

2. Filtering Options:

➢ Use filter options like text filters, number filters, date filters, and more to refine data
based on specific conditions.

3. Clearing Filters:

➢ To remove filters, go back to the Data tab and click on the "Filter" button again. This
action clears all filters applied to the data.

Sorting Data:

➢ Sorting rearranges data in ascending or descending order based on specific columns or

criteria.

1. Simple Sorting:

➢ Select the data range to be sorted.

➢ Go to the Data tab and click on "Sort A to Z" (ascending) or "Sort Z to A" (descending)
to sort based on the selected column.

2. Custom Sorting:

➢ Go to the Data tab and select "Sort."

➢ Choose "Custom Sort" to specify sorting criteria for multiple columns or to sort by
specific conditions.

Inserting Worksheets:
Adding or inserting new worksheets within an Excel workbook is straightforward.

1. Inserting a New Worksheet:

➢ Right-click on an existing worksheet tab.

➢ Select "Insert" to add a new worksheet before or after the current sheet.

2. Renaming Worksheets:

➢ Double-click on the worksheet tab name to edit and rename it to a more descriptive
title.

3. Navigating Between Worksheets:

➢ Click on the worksheet tabs at the bottom of the Excel window to navigate between
different sheets.

4. Deleting Worksheets:

➢ Right-click on the worksheet tab and choose "Delete" to remove a worksheet. Be

cautious as this action is irreversible and deletes all data in the sheet.

Worksheet Management:

➢ Excel allows you to manage multiple worksheets within a single workbook, making it
easy to organize and separate different sets of data.
➢ Worksheets can be copied, moved, or grouped to streamline data management.