MICROSOFT EXCEL

Microsoft Excel is a spreadsheet software developed by Microsoft that allows users to organize,
calculate, analyze, and visualize data in a tabular format. It provides users with a range of
powerful tools and functions to manage and manipulate large sets of data, and to perform
complex calculations and analysis quickly and easily.

Here are some of the main uses of Microsoft Excel:

Financial management: Excel is commonly used for financial management tasks such as
budgeting, forecasting, and financial analysis. It can help businesses and individuals to manage
their finances more effectively by creating budgets, tracking expenses, and generating financial
reports.

Data analysis: Excel is a powerful tool for data analysis and visualization. It allows users to
organize and analyze large sets of data quickly and easily, and to generate charts, graphs, and other
visualizations to help identify trends and patterns.

Project management: Excel is often used for project management tasks such as creating project
plans, tracking progress, and managing resources. It can help teams to collaborate more effectively
and to keep track of important project information.

Inventory management: Excel is commonly used for inventory management tasks such as
tracking inventory levels, ordering supplies, and forecasting future inventory needs. It can help
businesses to manage their inventory more efficiently and to reduce waste and costs.

Human resources: Excel can be used for various human resources tasks such as tracking
employee performance, managing employee schedules, and maintaining employee records. It can
help businesses to streamline their HR processes and to ensure compliance with regulations.

Data entry and organization: Excel can be used for simple data entry tasks such as creating lists
and tables, and for organizing and structuring data in a logical and easy-to-understand format.

These are just some of the many uses of Microsoft Excel. It is a versatile and powerful tool that
can be used for a wide range of tasks in various industries and professions.

GETTING STARTED MICROSOFT EXCEL

Microsoft Excel is a popular spreadsheet program used for organizing and analyzing data. The
Excel window consists of several elements, including:

Title Bar: Displays the name of the current workbook and the name of the application.

Ribbon: A set of tabs containing groups of commands and tools for working with your data. The
tabs are Home, Insert, Page Layout, Formulas, Data, Review, and View.

1
Quick Access Toolbar: A customizable toolbar that provides easy access to frequently used
commands.

Workbook Views: Excel provides several views of a workbook, including Normal, Page
Layout, and Page Break Preview.

Formula Bar: Displays the contents of the active cell, including formulas and values.

Name Box: Displays the name of the active cell or range.

Column Headers: Letters that identify the columns in your worksheet.

Row Headers: Numbers that identify the rows in your worksheet.

Cell Grid: The main area of the Excel window where you enter and edit data.

Scroll Bars: Allows you to scroll vertically or horizontally through your worksheet.

Status Bar: Provides information about the current status of your workbook, including the
current cell mode, calculation mode, and more.

Overall, the Excel window is designed to make it easy to work with data and perform complex
calculations and analysis.

The File Tab in Microsoft Excel provides access to various functions related to the current
workbook, including:

Save: Allows you to save the current workbook to a file on your computer or network.

Save As: Allows you to save a copy of the current workbook with a different name, file format,
or location.

Open: Allows you to open an existing workbook from your computer or network.

Close: Allows you to close the current workbook.

Info: Provides information about the current workbook, such as author, title, and last modified
date.

Recent: Displays a list of recently used workbooks for easy access.

New: Allows you to create a new workbook or choose from a variety of templates.

Print: Allows you to print the current workbook or customize the print settings.

2
Share: Provides options for sharing the current workbook with others, such as emailing it or
saving it to a shared location.

Options: Allows you to customize various settings in Excel, such as display options, formulas,
and add-ins.

Account: Provides information about your Microsoft account and options for managing it.
Exit: Closes the Excel application.

The File Tab is a key component of the Excel user interface, providing easy access to essential
functions for managing and working with workbooks.

The Home Tab in Microsoft Excel is the default tab that appears when you open a new or
existing workbook. It contains the most commonly used formatting and editing tools, including:

Clipboard: Allows you to copy, cut, and paste data and formatting.

Font: Allows you to change the font, font size, font color, and font effects of your text.

Alignment: Allows you to adjust the alignment of your text, including horizontal and vertical
alignment, text wrapping, and indentation.

Number: Allows you to format numbers and dates, including adding currency symbols,
decimals, and percentage signs.

Styles: Allows you to apply predefined formatting styles to cells, such as headings and totals.

Cells: Allows you to format cells, including changing the background color, borders, and cell
size.

Editing: Allows you to perform various editing functions, such as inserting and deleting cells,
rows, and columns.

Find & Select: Allows you to search for specific data or select cells based on specific criteria.

Sort & Filter: Allows you to sort and filter your data based on specific criteria.

Conditional Formatting: Allows you to apply formatting to cells based on specific conditions,
such as highlighting cells with values above or below a certain threshold.

Overall, the Home Tab provides a wide range of tools and options for formatting and editing
your data, making it a key component of the Excel user interface.

The Insert Tab in Microsoft Excel provides access to various tools and features for adding
different types of content to your workbook. Some of the key tools and features available on the
Insert Tab include:

3
Tables: Allows you to create and format tables to organize and analyze data.

Charts: Allows you to create a variety of charts to visualize data, such as column charts, line
charts, and pie charts.

Sparklines: Allows you to create small, inline charts that display trends and patterns within
cells.

PivotTables: Allows you to summarize and analyze large amounts of data in a dynamic table.

Recommended Charts: Provides suggestions for the best type of chart to use based on your
data.

Illustrations: Allows you to insert pictures, shapes, and other graphic elements to enhance your
workbook.

Add-ins: Allows you to add additional functionality to Excel, such as data analysis tools or third-
party add-ins.

Apps: Allows you to add apps from the Office Store to extend Excel's capabilities.

Links: Allows you to add hyperlinks to your workbook, such as links to other sheets or external
websites.

Text: Allows you to add text boxes, headers, and footers to your workbook.

Symbols: This allows you to insert special characters, symbols, and other non-alphanumeric
characters into your worksheet.

Overall, the Insert Tab provides a range of tools and features for adding different types of
content to your workbook, making it an important component of the Excel user interface.

The Page Layout Tabin Microsoft Excel provides access to various tools and features for
customizing the layout and appearance of your worksheet when it's printed. Some of the key
tools and features available on the Page Layout Tabinclude:

Themes: Allows you to apply a predefined color scheme and font set to your workbook.

Page Setup: Allows you to customize various settings for printing, such as margins, orientation,
and paper size.

Scale to Fit: Allows you to adjust the size of your worksheet to fit on a printed page.
Sheet Options: Allows you to customize the appearance of your worksheet, such as adding
gridlines, headers, and footers.

4
Arrange: Allows you to adjust the position and layout of objects on your worksheet, such as
charts and images.

Background: Allows you to add a background color or image to your worksheet.

Print Titles: Allows you to specify rows and columns to repeat on each printed page.

Page Borders: Allows you to add a border around the edges of your printed worksheet.

Overall, the Page Layout Tab provides a range of tools and features for customizing the
appearance of your worksheet when it's printed, making it an important component of the Excel
user interface. By using the Page Layout tools, you can make sure that your printed worksheets
look professional and are easy to read.

The Formulas Tab in Microsoft Excel provides access to various tools and features for working
with formulas and functions. Some of the key tools and features available on the Formulas Tab
include:

Function Library: Allows you to browse and select from a wide range of built-in functions,
organized by category.

Insert Function: Allows you to search for and insert a specific function into your worksheet.

Defined Names: Allows you to manage named ranges and create named formulas for use in your
worksheet.

Formula Auditing: Allows you to trace and analyze formula dependencies and errors.

Calculation: Allows you to control the calculation options for your workbook, such as setting
the calculation mode and options for iterative calculations.

Formula Tools: Allows you to modify and manipulate formulas, such as converting formulas to
values or replacing cell references with values.

Data: Allows you to manage external data sources and connections.

Solver: Allows you to perform optimization and what-if analysis on your data.

Overall, the Formulas Tab provides a range of tools and features for working with formulas and
functions, which are essential for performing calculations and analyzing data in Excel. By using
the tools on the Formulas Tab, you can create complex calculations and analyze large amounts
of data with ease.

The Data Tab in Microsoft Excel provides access to various tools and features for managing and
analyzing data. Some of the key tools and features available on the Data Tab include:

5
Get & Transform: Allows you to import and transform data from various sources, such as
databases, web pages, and text files.

Connections: Allows you to manage data connections and refresh data from external sources.

Sort & Filter: Allows you to sort and filter data in your worksheet based on specific criteria.

Data Tools: Allows you to perform various data management tasks, such as removing
duplicates, validating data, and consolidating data from multiple sources.

What-If Analysis: Allows you to perform simulations and scenarios to analyze how changes to
your data will affect your results.

Outline: Allows you to group and summarize data in your worksheet using outlines.

Data Validation: Allows you to set rules and constraints for data entry in your worksheet.

Forecast: Allows you to create forecasts based on historical data.

Overall, the Data Tab provides a range of tools and features for managing and analyzing data,
making it an essential component of the Excel user interface. By using the tools on the Data
Tab, you can efficiently manage and analyze large amounts of data in your worksheets, and gain
insights into your data to make better decisions.

The Review Tab in Microsoft Excel provides access to various tools and features for reviewing
and collaborating on workbooks with others. Some of the key tools and features available on the
Review Tab include:

Proofing: Allows you to check spelling and grammar in your worksheet, as well as access the
thesaurus and translate options.

Comments: Allows you to insert, view, and manage comments in your worksheet.

Changes: Allows you to track and manage changes made to your worksheet, and accept or reject
changes made by others.

Compare: Allows you to compare two versions of a workbook and see the differences between
them.

Protect: Allows you to password protect your workbook, as well as control who can make
changes to specific parts of your worksheet.

Workbook Sharing: Allows you to share your workbook with others, and manage permissions
and access to your workbook.

6
Accessibility: Allows you to check for and fix issues related to accessibility for users with
disabilities.

Overall, the Review Tab provides a range of tools and features for reviewing and collaborating
on workbooks with others, making it an essential component of the Excel user interface. By
using the tools on the Review tab, you can ensure the accuracy and integrity of your worksheet,
collaborate effectively with others, and make your workbook accessible to all users.

The View Tab in Microsoft Excel provides access to various tools and features for controlling
the way your worksheet is displayed on screen. Some of the key tools and features available on
the View Tab include:

Workbook Views: Allows you to switch between different views of your workbook, such as
Normal, Page Layout, and Page Break Preview.

Show: Allows you to show or hide various elements in your worksheet, such as gridlines,
headings, and rulers.

Zoom: Allows you to adjust the zoom level of your worksheet to make it easier to read or fit
more data on screen.

Window: Allows you to manage multiple windows for the same workbook, or to arrange
multiple workbooks side by side.

Macros: Allows you to record, edit, and run macros to automate repetitive tasks in your
worksheet.

Freeze Panes: Allows you to freeze rows or columns in your worksheet to keep them visible as
you scroll.

Arrange All: Allows you to arrange all open workbooks on the screen.

Overall, the View Tab provides a range of tools and features for controlling the way your
worksheet is displayed on screen, making it an important component of the Excel user interface.
By using the tools on the View Tab, you can customize the appearance of your worksheet,
manage multiple windows and workbooks, and automate repetitive tasks with macros.

The Developer Tab in Microsoft Excel is a special tab that is not displayed by default. To use it,
you must first enable it in Excel's options. The Developer Tab provides access to various tools
and features for creating and working with custom macros, add-ins, and other advanced
functionality. Some of the key tools and features available on the Developer Tab include:

Visual Basic: Allows you to open the Visual Basic Editor, where you can create, edit, and
manage custom macros and code.

7
Record Macro: Allows you to record a series of actions in Excel and save them as a macro that
you can run later.

Macros: Allows you to view and manage macros that you have created or recorded.

Add-Ins: Allows you to manage add-ins that provides additional functionality to Excel, such as
custom functions or user interfaces.

ActiveX Controls: Allows you to insert and customize ActiveX controls, such as buttons or
dropdown menus, in your worksheet.

XML: Allows you to import and export data using XML format.

Overall, the Developer Tab provides a range of tools and features for advanced users and
developers who want to create custom macros, add-ins, and other advanced functionality in
Excel. By using the tools on the Developer Tab, you can automate complex tasks, extend
Excel's functionality, and create custom solutions tailored to your specific needs.

CHARTS

Charts in Microsoft Excel are visual representations of data that can be used to help
communicate information and insights in a clear and effective way. Excel provides a range of
chart types, including column charts, line charts, pie charts, bar charts, area charts, scatter charts,
and more. To create a chart in Excel, you first need to select the data you want to include in the
chart.

Once you have selected the data, you can create a chart by following these steps:

• Go to the Insert tab in the Excel ribbon.

• Select the chart type you want to use from the Charts group.
• Excel will automatically create a chart based on the selected data. You can customize the
chart by using the Chart Tools that appear on the ribbon.
• Use the Design, Layout, and Format tabs on the Chart Tools to change the appearance and
layout of the chart.

Some of the key features and options available when working with charts in Excel
include:

Chart Styles: Allows you to quickly apply a pre-defined style to your chart.

Chart Layouts: Allows you to choose a pre-defined layout for your chart, or to customize the
layout manually.

8
Chart Elements: Allows you to add or remove chart elements, such as titles, legends, axes, and
data labels.

Chart Filters: Allows you to filter the data displayed in the chart by selecting specific categories
or values.

Chart Tools: Allows you to customize various aspects of the chart, such as fonts, colors, and
chart types.

Overall, charts are a powerful tool for visualizing and analyzing data in Microsoft Excel. By
using the built-in charting features and options, you can create professional-looking charts
thathelp you communicate your data effectively and make informed decisions based on your
insights.

LINE CHART

A line chart in Microsoft Excel is a type of chart that displays data points as a series of connected
lines. Line charts are commonly used to display trends over time, such as changes in stock
prices, sales figures, or other time-based data.

To create a line chart in Excel, you can follow these steps:

• Select the data you want to include in the chart, including the X-axis (time) and Y-axis
(values).
• Go to the Insert tab in the Excel ribbon.
• Select the Line chart type from the Charts group.
• Excel will automatically create a line chart based on the selected data. You can customize
the chart by using the Chart Tools that appear on the ribbon.
• Use the Design, Layout, and Format tabs on the Chart Tools to change the appearance
and layout of the chart.

Some of the key features and options available when working with line charts in
Excel includes:

Data Labels: Allows you to add labels to the data points on the chart.

Trendline: Allows you to add a trendline to the chart, which can help visualize trends in the data
more clearly.

Axis Titles: Allows you to add titles to the X-axis and Y-axis.

Legend: Allows you to add or remove the legend on the chart.

Chart Title: Allows you to add a title to the chart.

9
Overall, line charts are a powerful tool for visualizing trends and changes over time in Microsoft
Excel. By using the built-in charting features and options, you can create professional-looking
line charts that help you communicate your data effectively and make informed decisions based
on your insights.

Here’s an example of data that can be used to create a line chart in Excel:

Year Sales
2015 20000
2016 25000
2017 30000
2018 35000
2019 40000
2020 45000

This data shows the sales figures for a business over a period of six years. You can use this data
to create a line chart that shows the trend in sales over time. The X-axis (horizontal axis) would
represent the years, and the Y-axis (vertical axis) would represent the sales figures. The line
chart would then display the data points as a series of connected lines, with each line
representing the sales figures for a given year. This chart can help you identify trends and
patterns in sales over time, and make informed decisions about the future of your business.

50000
45000 45000
40000 40000
SALES

35000 35000
30000 30000
Year
25000 25000
Sales
20000 20000
15000
10000
5000
2015 2016 2017 2018 2019 2020
0
YEAR

STACKED LINE CHART

10
 A stacked line chart in Microsoft Excel is a type of chart that displays data in a series of
lines, where each line represents a different category or data series. The lines are stacked on
top of each other to show the total value of all the data series at each point on the chart.

Stacked line charts are commonly used to show how a particular data series contributes to the
overall trend of the data. They are particularly useful when you want to compare multiple
data series that have similar scales or units of measurement.

To create a stacked line chart in Microsoft Excel, you will need to select your data and then
go to the "Insert" tab on the Excel ribbon. From there, you can choose "Line" from the
"Charts" section and then select the "Stacked Line" option. Excel will generate the chart
based on your data, and you can further customize it using the formatting options available.

You can also create a stacked area chart in a similar way by choosing the "Stacked Area"
option instead of the "Stacked Line" option. This will create a chart where the areas beneath
the lines are filled with color to make it easier to see the contribution of each data series to
the total.

Here's an example of data that you could use to create a stacked line chart in Excel;

Year Category 1 Category 2 Category 3

2010 100 150 75
2011 120 130 80
2012 140 170 90
2013 160 190 100
2014 180 210 110
2015 200 230 120

In this example, each row represents a different year, and there are three different categories of
data that are being tracked. You could create a stacked line chart to show how the total value of
each category changes over time. To do this, you would select the data and create a stacked line
chart in Excel, with the years on the X-axis and the total value of the three categories on the Y-
axis. Each line on the chart would represent a different category, and the total value of all three
categories would be stacked on top of each other at each point on the chart.

11
 3000

2500

2000
Category 3
1500 Category 2
Category 1
1000
Year

500

0
1 2 3 4 5 6

COLUMN CHART

A column chart in Microsoft Excel is a type of chart that displays data points as vertical columns.
Column charts are commonly used to compare data points, such as sales figures, market share, or
other values that can be easily compared visually.

To create a column chart in Excel, you can follow these steps:

• Select the data you want to include in the chart, including the X-axis (categories) and Y-
axis (values).
• Go to the Insert tab in the Excel ribbon.
• Select the Column chart type from the Charts group.
• Excel will automatically create a column chart based on the selected data. You can
customize the chart by using the Chart Tools that appear on the ribbon.
• Use the Design, Layout, and Format tabs on the Chart Tools to change the appearance
and layout of the chart.

Some of the key features and options available when working with column charts in
Excel include:

Data Labels: Allows you to add labels to the data points on the chart.

Axis Titles: Allows you to add titles to the X-axis and Y-axis.

Legend: Allows you to add or remove the legend on the chart.

Chart Title: Allows you to add a title to the chart.

12
Overall, column charts are a powerful tool for comparing data points and identifying trends in
Microsoft Excel. By using the built-in charting features and options, you can create professional-
looking column charts that help you communicate your data effectively and make informed
decisions based on your insights.

CLUSTERED COLUMN CHART

A clustered column chart in Excel is a type of chart that displays data in vertical columns, with
each column representing a distinct category or group. The columns are arranged in clusters side-
by-side to make it easy to compare the values of each group.
Clustered column charts are commonly used to show changes in data over time or to compare
data across different categories. They are easy to create in Excel and allow you to customize the
chart's appearance to better suit your needs.

To create a clustered column chart in Excel, you can follow these steps:

• Enter your data into Excel. The data for a clustered column chart should be in two
columns with labels in the first row. The first column will be the x-axis labels and the
second column will be the data for the columns.
• Select the data that you want to include in the chart.
• Click on the "Insert" tab in the ribbon.
• Click on the "Column" button in the "Charts" section.
• Choose the "Clustered Column" chart type.
• Excel will generate a chart with default formatting. You can customize the chart by right-
clicking on various chart elements and selecting "Format [element]". For example, you
can change the colors of the columns, add a chart title, or adjust the axis labels.
• Save your chart by clicking on "File" and selecting "Save As". Choose a file name and
location for the chart.

That's it! You now have a clustered column chart in Excel.

Here is an example of data that you can use to create a clustered column chart in Excel:

Month Sales
Jan 500
Feb 750
Mar 900
Apr 600
May 800
Jun 1000
Jul 1200
Aug 950
Sep 1100
Oct 850

13
 Nov 950
Dec 1300

This data shows the sales for a company over the course of a year, broken down by month. To
create a clustered column chart in Excel using this data, select both columns (Month and Sales)
and follow the steps outlined above.

1400 Sales
1200

1000

800

600

400 Sales

200

0
Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec

STACKED COLUMN CHART

A stacked column chart is a type of chart in Excel that displays multiple sets of data in vertical
columns, with each column representing a category or series. The columns are stacked on top of
one another, with the total height of the column representing the total value of all the data in that
category or series.

To create a stacked column chart in Excel, follow these steps:

• Enter your data in an Excel worksheet. Your data should be arranged in columns or rows,
with each column or row representing a category or series.
• Select the range of cells containing your data, including the column or row headers.
• Go to the "Insert" tab on the Excel ribbon.
• Click on the "Column" chart type.
• Select the "Stacked Column" chart subtype.
• Your stacked column chart should now be displayed in the worksheet.

Here's an example of data that can be used to create a stacked column chart:

14
 Category Series 1 Series 2 Series 3
A 10 20 30
B 15 25 35
C 20 30 40

In this example, there are three series of data (Series 1, Series 2, and Series 3) for each of three
categories (A, B, and C). To create a stacked column chart from this data, select the range of
cells A1:D4 and follow the steps outlined above.

120

100

80
C
60
B

40 A

20

0
Series 1 Series 2 Series 3

PIE CHART

A pie chart in Microsoft Excel is a circular chart that displays data as proportional slices of a pie.
Each slice represents a portion of the whole data set, and the size of each slice is determined by
the percentage or value it represents.

To create a pie chart in Excel, follow these steps:

• Enter your data in an Excel worksheet. The data should be arranged in one column or
row, with each value representing a portion of the whole data set.
• Select the range of cells containing your data.
• Go to the "Insert" tab on the Excel ribbon.
• Click on the "Pie" chart type.
• Select the desired subtype of pie chart (e.g. 2-D Pie, 3-D Pie).
• Your pie chart should now be displayed in the worksheet.

15
Here's an example of data that can be used to create a pie chart:

Category Value
A 30
B 20
C 50

In this example, there are three categories (A, B, and C) with values representing portions of the
whole data set. To create a pie chart from this data, select the range of cells A1:B4 and follow
the steps outlined above. The resulting pie chart will display each category as a slice of the pie,
with the size of each slice determined by its percentage of the whole data set

Value
A

30 B
50
20 C

EXPLOADED PIE CHART

An exploded pie chart in Microsoft Excel is a variation of the standard pie chart where one or
more slices of the pie are separated from the rest of the chart for emphasis. This creates a visual
effect that makes it easier to see the individual components of the data set.
To create an exploded pie chart in Excel, follow these steps:
• Enter your data in an Excel worksheet. The data should be arranged in one column or
row, with each value representing a portion of the whole data set.
• Select the range of cells containing your data.
• Go to the "Insert" tab on the Excel ribbon.
• Click on the "Pie" chart type.
• Select the desired subtype of pie chart with explosion (e.g. 2-D Pie with Explosion, 3-D
Pie with Explosion).
• Your exploded pie chart should now be displayed in the worksheet.
Here's an example of data that can be used to create an exploded pie chart:

16
 Category Value
A 30
B 20
C 50

In this example, there are three categories (A, B, and C) with values representing portions of the
whole data set. To create an exploded pie chart from this data, select the range of cells A1:B4
and follow the steps outlined above. You can choose to explode any or all of the slices to
highlight certain parts of the data set.

Value

30 A

50 B
C
20

PIE OF PIE CHART

A Pie of Pie chart in Microsoft Excel is a variation of the standard pie chart that adds a
secondary pie chart to display smaller values that might be difficult to see in the primary chart.
The secondary pie chart is typically displayed as a smaller pie chart that sits on top of a slice of
the main chart.
To create a Pie of Pie chart in Excel, follow these steps:
• Enter your data in an Excel worksheet. The data should be arranged in one column or
row, with each value representing a portion of the whole data set.
• Select the range of cells containing your data.
• Go to the "Insert" tab on the Excel ribbon.
• Click on the "Pie" chart type.
• Select the "Pie of Pie" chart subtype.
• Your Pie of Pie chart should now be displayed in the worksheet.
Here's an example of data that can be used to create a Pie of Pie chart:

17
 Category Value
A 60
B 20
C 10
D 5
E 5

In this example, there are five categories (A, B, C, D, and E) with values representing portions of
the whole data set. To create a Pie of Pie chart from this data, select the range of cells A1:B6 and
follow the steps outlined above. The resulting Pie of Pie chart will display the larger values in the
main pie chart, and the smaller values in the secondary pie chart.

Value

A
20
10 5 B

10 C
D
60 5
E

BAR CHART
A bar chart in Microsoft Excel is a chart type that displays data as rectangular bars. Each bar
represents a category or data point, and the height or length of the bar represents the value it
represents.
To create a bar chart in Excel, follow these steps:
• Enter your data in an Excel worksheet. The data should be arranged in columns or rows,
with each column or row representing a category or data point and each value
representing a value to be plotted.
• Select the range of cells containing your data.
• Go to the "Insert" tab on the Excel ribbon.
• Click on the "Bar" chart type.
• Select the desired subtype of bar chart (e.g. Clustered Bar, Stacked Bar)

18
 • Your bar chart should now be displayed in the worksheet.
Here's an example of data that can be used to create a bar chart:

Category Value 1 Value 2 Value 3

A 20 30 40
B 10 25 35
C 15 20 30

In this example, there are three categories (A, B, and C) with three values each representing a
value to be plotted. To create a bar chart from this data, select the range of cells A1:D4 and
follow the steps outlined above. The resulting bar chart will display each category as a
rectangular bar, with the height or length of the bar determined by its value.

Clustered Bar Chart

Value 3

C
Value 2
B
A

Value 1

0 10 20 30 40 50

19
 Stacked Bar Chart

Value 3

A
Value 2
B
C

Value 1

0 20 40 60 80 100 120

AREA CHART
An area chart in Microsoft Excel is a chart type that displays data as a series of data points
connected by a line, with the area below the line filled in with color or shading. This chart type is
often used to show changes over time or to compare values across multiple data series.
To create an area chart in Excel, follow these steps:
• Enter your data in an Excel worksheet. The data should be arranged in columns or rows,
with each column or row representing a category or data point and each value
representing a value to be plotted.
• Select the range of cells containing your data.
• Go to the "Insert" tab on the Excel ribbon.
• Click on the "Area" chart type.
• Select the desired subtype of area chart (e.g. Stacked Area, 100% Stacked Area).
• Your area chart should now be displayed in the worksheet.
Here's an example of data that can be used to create an area chart:

Year Sales Expenses

2018 100 80
2019 120 100
2020 140 120

20
 2021 160 130
In this example, there are four years with sales and expenses data for each year. To create an area
chart from this data, select the range of cells A1:C5 and follow the steps outlined above. The
resulting area chart will display the sales and expenses data as connected data points, with the
area below the line filled in with color or shading.
Area Chart

2500

2000

1500
Year
Sales
1000
Expenses

500

0
1 2 3 4

Stacked Area Chart

2350
2300
2250
2200
2150
Expenses
2100
Sales
2050
Year
2000
1950
1900
1850
1 2 3 4

21
 SCATTER CHART
An Excel scatter chart is a type of chart that is used to display the relationship between two sets
of data. It is also called an XY chart. The data is plotted as a set of points, with one variable on
the x-axis and the other on the y-axis.
To create a scatter chart in Excel, follow these steps:
• Enter your data into an Excel worksheet. The data should have two columns: one for the
x-axis values and one for the y-axis values.
• Select the data range that you want to use for the chart.
• Go to the Insert tab and select Scatter from the Charts group.
• Choose the chart type that you want to use. You can choose from several options,
including a simple scatter chart, a scatter chart with smooth lines and markers, or a scatter
chart with straight lines and markers.
• The chart will appear on the worksheet. You can customize the chart by adding a chart
title, axis labels, and other elements.
• To change the appearance of the chart, right-click on the chart and select Format Chart
Area. You can change the color, style, and other aspects of the chart.
• Save the chart by clicking on the Save button in the Excel toolbar.
Scatter charts are useful for displaying the correlation between two sets of data, such as the
relationship between temperature and sales, or between age and income. They are commonly
used in scientific research, financial analysis, and other fields where data analysis is important.
Suppose you have data for temperature and sales of a product for a number of days. The
temperature values are in Celsius and the sales values are in units.

Temperature Sales
(Celsius) (units)

10 25
12 30
15 45
18 55
20 60
22 65
25 75

22
 80
70
60
50
SALES

40
30
20
10
0
0 5 10 15 20 25 30
TEMPARATURE

BUBBLE CHART
A bubble chart is a type of chart in MS Excel that is used to display three sets of data on the
same chart. It is similar to a scatter chart, but the size of the markers (or bubbles) represents the
third variable. The horizontal and vertical axes represent the first two variables, while the size of
the bubbles represents the third variable.
To create a bubble chart in Excel, follow these steps:
• Enter your data into an Excel worksheet. The data should have at least three sets of
values.
• Select the data range that you want to use for the chart.
• Go to the Insert tab and select the "Bubble" chart type.
• The chart will appear on the worksheet. The bubbles will all be the same size by default.
• To change the size of the bubbles based on the third variable, right-click on any bubble
and select "Format Data Series".
• In the "Format Data Series" pane, select "Size" and then select the data range that
contains the third variable.
• You can customize the chart by adding a chart title, axis labels, and other elements.
• To change the appearance of the chart, right-click on the chart and select "Format Chart
Area". You can change the color, style, and other aspects of the chart.
• Save the chart by clicking on the "Save" button in the Excel toolbar.
A bubble chart can be useful for displaying data that has three dimensions. For example, you can
use a bubble chart to display the sales, profit, and market share for different products. The size of
the bubbles can represent the market share, while the horizontal and vertical axes represent the

23
sales and profit, respectively. This allows you to easily compare the different products based on
their sales, profit, and market share.
Here's an example of data that can be used to create a bubble chart in Excel:
Suppose you have the following data for different products, their sales, and their market share:

Product Sales Market Share

A 2000 10%
B 3000 20%
C 4000 30%
D 5000 40%
E 6000 50%

7000

6000

5000

4000

3000

2000

1000

0
0 1 2 3 4 5 6

FUNCTIONS AND FORMULAS

TEXT FUNCTIONS
Excel offers a wide range of text functions that can be used to manipulate text within a cell or
across multiple cells. Here are some of the commonly used text functions in Excel:
1. CONCATENATE: This function is used to combine two or more strings of text.
For example, =CONCATENATE ("Hello", " ", "World") would return "Hello World".

24
 FIRST FULL NAME /
LAST NAME AGE FULL NAME
NAME AGE
Ronnie Ronnie
Ronnie Anderson 40
Anderson Anderson,40
Tom Brown 35 Tom Brown Tom Brown,35
Sally Brook 25 Sally Brook Sally Brook,25
Hill 30 Jeremy Hill Jeremy Hill,30
Robert Robert
Robert Furiyan 29
Furiyan Furiyan,29

2. LEFT/RIGHT/MID: These functions are used to extract a certain number of characters

from the left, right or middle of a text string respectively.
For example, =LEFT ("Hello World", 5) would return "Hello".
LEFT - =left (text, num_of chars)

NAME LEFT
SHARON SHA
DESNY DE
MISRIYA MISR

RIGHT - =right (text, num_of chars)

NAME RIGHT
AKSHAY HAY
AFSAL AL
NIMIYA MIYA

MID - =mid (text, start_ num, num_of chars)

NAME MID
AMRITHA I
SANIGA IG
ANASWARA SWA

25
3. LEN: This function is used to count the number of characters in a text string.
For example, =LEN ("Hello World") would return 11.
LEN - =Len(text)

NAME LEN
RANISHA 7
BASIL 5
ANANDU 6

4. UPPER/LOWER/PROPER: These functions are used to convert text to upper case,

lower case or proper case respectively.
For example, =UPPER ("hello world") would return "HELLO WORLD".
UPPER - =upper (text)

true visory the learning academy

TRUE VISORY THE LEARNING ACADEMY

LOWER - =lower (text)

TRUE VISORY THE LEARNING ACADEMY

true visory the learning academy

PROPER - =proper(text)

true visory the learning academy

True Visory the Learning Academy

5. FIND: This function is used to find the position of a specific text string within another
text string.
For example, =FIND ("World", "Hello World") would return 6.
6. TRIM: This function is used to remove extra spaces from a text string.
For example, =TRIM (" Hello World ") would return "Hello World".

26
 TRIM - =trim (text)

NAME TRIM
AISWARYA LEKSHMI AISWARYA LEKSHMI
MANJU WARRIER MANJU WARRIER
APARNA BALAMURALI APARNA BALAMURALI

7. TEXT: This function is used to format a number or date as text.

For example, =TEXT (TODAY (),"dd-mmm-yyyy") would return today's date in the
format "04-Mar-2023".
These are just a few examples of the text functions available in Excel. There are many
more functions that can be used to manipulate text in various ways, depending on the
specific requirements of your data analysis or reporting tasks.

DATE & TIME FUNCTIONS

Excel has several built-in Date and Time functions that can be used to perform various
operations on dates and times. Here are some of the most commonly used Date and Time
functions in Excel:
1. TODAY (): Returns the current date.
2. NOW (): Returns the current date and time.
3. DATE (year, month, day): Returns the date based on the given year, month, and day.
4. YEAR (date): Returns the year of a given date.
5. MONTH (date): Returns the month of a given date.
6. DAY (date): Returns the day of a given date.
7. HOUR (time): Returns the hour of a given time.
8. MINUTE (time): Returns the minute of a given time.
9. SECOND (time): Returns the second of a given time.
10. TIME (hour, minute, second): Returns the time based on the given hour, minute, and
second.
11. DATEDIF (start_date, end_date, unit): Returns the difference between two dates in the
specified unit (days, months, or years).
12. EDATE (start_date, months): Returns the date that is a specified number of months
before or after the start date.
13. EOMONTH (start_date, months): Returns the last day of the month that is a specified
number of months before or after the start date.
14. NETWORKDAYS (start_date, end_date, holidays): Returns the number of working days
between two dates, excluding weekends and holidays.

27
 15. WEEKDAY (date, type): Returns the day of the week as a number (1-7) based on the
given date, with Sunday as the first day of the week.
These are just some of the many Date and Time functions available in Excel. You can use them
to perform a wide range of calculations and manipulations on dates and times in your
spreadsheets.
LOGICAL FUNCTIONS
Excel has several built-in Logical functions that allow you to test conditions and return true or
false based on the results of the tests. Here are some of the most commonly used Logical
functions in Excel:
1. IF (logical_test, value_if_true, value_if_false): Returns one value if a condition is true
and another value if it's false.
2. AND (logical1, [logical2] ...): Returns true if all of the arguments are true and false
otherwise.
3. OR (logical1, [logical2] ...): Returns true if at least one of the arguments is true and false
otherwise.
4. NOT (logical): Returns the opposite of the logical argument. If the argument is true, NOT
returns false. If the argument is false, NOT returns true.
5. TRUE (): Returns the logical value "true".
6. FALSE (): Returns the logical value "false".
These are just some of the many Logical functions available in Excel. You can use them to test
conditions, perform calculations, and make decisions in your spreadsheets.
MATH & TRIG FUNCTIONS
Excel has several built-in Math & Trig functions that can be used to perform various
mathematical and trigonometric operations. Here are some of the most commonly used Math &
Trig functions in Excel:
1. SUM: Adds a list of numbers and returns the total. The SUM function in Microsoft Excel is
used to add up a range of numbers. It is a very useful and simple function that can save you a
lot of time when working with large sets of data. The syntax for the SUM function is as
follows:
=SUM (number1, [number2], [number3] ...)
Number1 is the first number or range of numbers to be added up. It is a required
argument.
[number2], [number3] ... are additional numbers or ranges of numbers that you want to
added up. These are optional arguments.
You can add up to 255 numbers or ranges of numbers using the SUM function.
Here's an example of how to use the SUM function:
Suppose you have a column of numbers ranging from A1 to A10 and you want to add
them up. To do this, you would enter the following formula in another cell, such as B1:
=SUM (A1:A10)

28
 This formula tells Excel to add up all the numbers in the range A1 to A10 and display the
result in cell B1. Excel will automatically update the result if any of the numbers in the
range change.
You can also use the SUM function to add up individual numbers. For example, if you
want to add up the numbers 5, 10, and 15, you can use the following formula:
=SUM (5, 10, 15)
This formula will return the result of 30.
EXAMPLE:

ITEMS
SUM
X Y Z
10 68 40 118
7 88 64 ?
77 76 38 ?
89 67 56 ?
57 68 1 ?
= SUM (C7:E7)
2. AVERAGE: Returns the average of a list of numbers. The AVERAGE function in Excel
calculates the average (arithmetic mean) of a set of numbers. The syntax for the AVERAGE
function is as follows:
AVERAGE (number1, [number2], ...)
number1: The first number or range of cells to include in the average calculation.
number2: Optional. Additional numbers or ranges of cells to include in the average
calculation.
You can use up to 255 numbers or ranges of cells in the AVERAGE function.
Here's an example of how to use the AVERAGE function:
Assume we have a list of numbers in cells A1 through A10, and we want to calculate the
average of those numbers. To do this, we can enter the following formula in cell B1:
=AVERAGE (A1:A10)
This formula tells Excel to calculate the average of the numbers in cells A1 through A10,
and display the result in cell B1.

EXAMPLE:

Student
Roll
NO

Acco Eco B. S CA Total Average

Name
1 Malini 180 190 185 160 715 178.75
2 Farzana 185 189 180 155 709 ?
3 Priya 133 120 125 100 478 ?

29
 4 Joseph 163 140 130 120 553 ?
5 Amjith 145 130 140 110 525 ?
= AVERAGE (E5:H5)
3. MAX: The MAX function in Excel returns the largest value from a set of values.
The syntax for the MAX function is as follows:
MAX (number1, [number2], ...)
number1: The first number or range of cells to compare.
number2: Optional. Additional numbers or ranges of cells to compare.
You can use up to 255 numbers or ranges of cells in the MAX function.

Here's an example of how to use the MAX function:

Assume we have a list of numbers in cells A1 through A10, and we want to find the
largest number in that list. To do this, we can enter the following formula in cell B1:
= MAX (A1:A10)
This formula tells Excel to find the largest number in cells A1 through A10, and display
the result in cell B1. If the largest number in that range is, for example, 75, then the result
in cell B1 will be 75.
Note that the MAX function can also be used with other data types, such as dates or
times. In this case, Excel will compare the values based on their underlying numeric
values.
EXAMPLE:

85
101
50
250
125
70
400
90
111
= MAX (C58:C66)
4. MIN: The MIN function in Excel returns the smallest value from a set of values.
The syntax for the MIN function is as follows:
MIN (number1, [number2], ...)
number1: The first number or range of cells to compare.
number2: Optional. Additional numbers or ranges of cells to compare.
You can use up to 255 numbers or ranges of cells in the MIN function.

30
 Here's an example of how to use the MIN function:
Assume we have a list of numbers in cells A1 through A10, and we want to find the
smallest number in that list. To do this, we can enter the following formula in cell B1:
= MIN (A1:A10)
This formula tells Excel to find the smallest number in cells A1 through A10, and display
the result in cell B1. If the smallest number in that range is, for example, 5, then the result
in cell B1 will be 5.
Note that the MIN function can also be used with other data types, such as dates or times.
In this case, Excel will compare the values based on their underlying numeric values.

EXAMPLE:

85
101
50
250
125
70
400
90
111
= MIN (F58:F66)
5. COUNT: The COUNT function in Excel counts the number of cells in a range that contain
numbers, dates, times, or text.
The syntax for the COUNT function is as follows:
COUNT (value1, [value2], ...)
value1: The first value or range of cells to count.
value2: Optional. Additional values or ranges of cells to count.
You can use up to 255 values or ranges of cells in the COUNT function.

Here's an example of how to use the COUNT function:

Assume we have a list of numbers and text in cells A1 through A10, and we want to
count the number of cells in that list that contain numbers. To do this, we can enter the
following formula in cell B1:
= COUNT (A1:A10)
This formula tells Excel to count the number of cells in cells A1 through A10 that contain
numbers, and display the result in cell B1. If there are, for example, 7 cells in that range
that contain numbers, then the result in cell B1 will be 7.
Note that the COUNT function does not count blank cells or cells that contain text or
errors. To count cells that contain text or errors, you can use the COUNTA function

31
 instead. To count cells that meet specific criteria, you can use the COUNTIF or
COUNTIFS functions.
EXAMPLE:

Accountancy
180
185
133
163
145

= COUNT (C14:C18)
6. COUNTA: The COUNTA function in Excel counts the number of cells in a range that are
not empty, including cells that contain text, numbers, logical values, and errors.
The syntax for the COUNTA function is as follows:
COUNTA (value1, [value2], ...)
value1: The first value or range of cells to count.
value2: Optional. Additional values or ranges of cells to count.
You can use up to 255 values or ranges of cells in the COUNTA function.
Here's an example of how to use the COUNTA function:
Assume we have a list of data in cells A1 through A10, and we want to count the number of
cells in that list that are not empty. To do this, we can enter the following formula in cell
B1:
= COUNTA (A1:A10)
This formula tells Excel to count the number of cells in cells A1 through A10 that are not
empty, and display the result in cell B1. If there are, for example, 9 cells in that range that
are not empty, then the result in cell B1 will be 9.
Note that the COUNTA function counts all non-blank cells, including cells that contain
text, numbers, logical values, and errors.
EXAMPLE:

Student Name
Malini
Farzana
Priya
Joseph
Amjith
= COUNTA (F13:F18)

32
7. COUNTBLANK: The COUNTBLANK function in Excel is used to count the number of
blank cells within a specified range.
The syntax for the COUNTBLANK function is:
= COUNTBLANK (range)
Where "range" is the range of cells that you want to count the blank cells in.
For example, if you want to count the number of blank cells in cells A1 to A10, you would
use the formula:
= COUNTBLANK (A1:A10)
This would return the number of blank cells in the range A1:A10.
Note that the COUNTBLANK function considers cells that contain formulas that return
blank values as non-blank. This would count cells in the range A1:A10 that contain an empty
string, which includes cells that contain formulas that return blank values.
EXAMPLE:
Accountancy

Application
Economics

Computer
Business

Average
Student
Roll NO

Studies
Name

Total
1 Malini 180 190 185 160 715 178.8

2 Farzana 185 189 180 155 709 177.3

3 Priya 133 120 125 100 478 119.5
4 Joseph 163 140 130 120 553 138.3

5 Amjith 145 130 140 110 525 131.3

6 Malini 180 190 185 160 715 178.8
7 Farzana 185 189 180 155 709 177.3
8 Priya 133 120 125 100 478 119.5

9 Joseph 163 140 130 120 553 138.3

10 Amjith 145 130 140 110 525 131.3
= COUNTBLANK (C24:J37)

33
8. COUNTIF: The COUNTIF function in Excel counts the number of cells in a range that meet
a specified criterion.

The syntax for the COUNTIF function is as follows:

COUNTIF (range, criteria)

range: The range of cells to evaluate.

criteria: The criteria that cells must meet in order to be counted.
Here's an example of how to use the COUNTIF function:
Assume we have a list of grades in cells A1 through A10, and we want to count the number
of grades that are greater than or equal to 70. To do this, we can enter the following formula
in cell B1:

= COUNTIF (A1:A10, ">=70")

This formula tells Excel to count the number of cells in cells A1 through A10 that contain a
value greater than or equal to 70, and display the result in cell B1. If there are, for example,
5 cells in that range that meet the criteria, then the result in cell B1 will be 5.
Note that the criteria argument can be a number, a text string, a cell reference, or a formula.
Also, the COUNTIF function is not case-sensitive, so it will count cells that meet the
criteria regardless of the case of the text. If you want the COUNTIF function to be case-
sensitive, you can use the COUNTIFS function instead.

EXAMPLE:
Use with TEXT; = COUNTIF (C45:C53,"APPLE")
Grape
Apple
Orange
Apple
Grape
Banana
Apple
Grape
Apple

34
Use with NUMBERS; COUNTIF (G45:G53,">100")
85
101
50
250
125
70
400
90
111

9. COUNTIFS: The COUNTIFS function in Excel is used to count the number of cells within a
range that meet multiple criteria. It takes multiple arguments, with each argument
representing a range of cells and its corresponding criteria.
The syntax for the COUNTIFS function is:
= COUNTIFS (range1, criteria1, [range2], [criteria2], ...)
where "range1" is the range of cells that you want to test the first criteria against, "criteria1"
is the first criteria that you want to apply to the first range, and "[range2], [criteria2], ..." are
additional ranges and criteria that you want to apply.
For example, if you have a list of sales data in cells A1:B10, where column A contains the
names of products and column B contains the corresponding sales figures, you could use the
COUNTIFS function to count the number of times a specific product was sold in a particular
month. Suppose you want to count the number of times the product "Widget" was sold in the
month of January. The formula would be:
= COUNTIFS (A1:A10, "Widget", B1:B10, "January")
This formula would count the number of cells in column B where the corresponding product
in column A is "Widget" and the month in the same row is "January".
Note that the COUNTIFS function can handle up to 127 range/criteria pairs. If you need to
count cells that meet more than 127 criteria.
EXAMPLE:

COUNTRY MEDAL
INDIA GOLD
ENGLAND BRONZE
INDIA SILVER
FRANCE GOLD
AUSTRALIA SILVER

35
 INDIA GOLD
ENGLAND BRONZE
INDIA GOLD
ENGLAND SILVER
INDIA SILVER
= COUNTIFS (K44:K53, N43, L44:L53, O43)

INDIA GOLD

3
10. ABS (number): Returns the absolute value of a number.
EXAMPLE:

-20 20
-25 25
50 50
-50 50
60 60
-60 60
= ABS (C36)
11. SQRT (number): Returns the square root of a number.
EXAMPLE:

245 15.65247584
630 25.0998008
750 27.38612788
=SQRT(C74)
12. FACT: The FACT function in Excel is used to calculate the factorial of a given number. The
factorial of a number is the product of all positive integers up to and including that number.
The syntax for the FACT function is:

=FACT (number)

Where "number" is the positive integer for which you want to calculate the factorial.
For example, if you want to calculate the factorial of 5, you would use the formula:
=FACT (5)
This would return the value 120, which is the product of 1 x 2 x 3 x 4 x 5.

36
 Note that the FACT function only works with positive integers. If you enter a negative
number or a non-integer value, Excel will return the #VALUE! Error. If the number is
too large, Excel may return the #NUM! Error indicating that the calculation is too large to
be performed.

EXAMPLE:

2 2
4 ?
6 ?
5 ?
8 ?
10 ?

= FACT (I36)

13. INT: The INT function in Excel is used to return the integer portion of a decimal number.
It rounds down the decimal number to the nearest integer.
The syntax for the INT function is:
=INT (number)

Where "number" is the decimal number for which you want to return the integer portion.
For example, if you want to return the integer portion of the number 3.8, you would use
the formula:
=INT (3.8)
This would return the value 3.
Similarly, if you want to return the integer portion of the number -2.5, you would use the
formula:
=INT (-2.5)
This would return the value -3, as the INT function always rounds down to the nearest
integer.
Note that the INT function does not round the decimal number to the nearest integer, it
always rounds down. If you want to round to the nearest integer, you can use the
ROUND function.

EXAMPLE:

23.25 23
63.67 ?
95.634 ?

37
 254.896 ?
325.124 ?
= INT (C46)

14. EVEN: The EVEN function in Excel is used to round a given number up to the nearest even
integer. If the number is already an even integer, it is returned without change.
The syntax for the EVEN function is:

=EVEN (number)

Where "number" is the number, you want to round up to the nearest even integer.
For example, if you want to round the number 3.2 up to the nearest even integer, you
would use the formula:
=EVEN (3.2)
This would return the value 4, which is the nearest even integer to 3.2.
Similarly, if you want to round the number 7.5 up to the nearest even integer, you would
use the formula:
=EVEN (7.5)
This would return the value 8, as 8 is the nearest even integer to 7.5.
Note that if the number is already an even integer, it is returned without change. For
example, if you use the formula:
=EVEN (4)
It would return the value 4, as 4 is already an even integer.

EXAMPLE:

13 14
25 ?
67 ?
93 ?
23 ?
33 ?
= EVEN (F36)

15. ODD: The ODD function in Excel is used to round a given number up to the nearest odd
integer. If the number is already an odd integer, it is returned without change.
The syntax for the ODD function is:
=ODD (number)

Where "number" is the number, you want to round up to the nearest odd integer.

38
 For example, if you want to round the number 3.2 up to the nearest odd integer, you
would use the formula:
=ODD (3.2)
This would return the value 5, which is the nearest odd integer to 3.2.
Similarly, if you want to round the number 7.5 up to the nearest odd integer, you would
use the formula:
=ODD (7.5)
This would return the value 9, as 9 is the nearest odd integer to 7.5.
Note that if the number is already an odd integer, it is returned without change. For
example, if you use the formula:
=ODD (5)
It would return the value 5, as 5 is already an odd integer.

EXAMPLE:

12 13
26 ?
68 ?
94 ?
24 ?

= ODD (F46:F50)

16. PI: The PI function in Excel is used to return the mathematical constant pi, which is
approximately equal to 3.141592653589793.
The syntax for the PI function is:
=PI ()

This function does not require any arguments. When the formula is entered into a cell, it
returns the value of pi to the cell.
For example, if you want to use the value of pi in a formula, you could enter:
=PI ()*5^2
This formula would return the area of a circle with a radius of 5 units, which is equal to pi
times the square of the radius.
Note that the value of pi returned by the PI function is an approximation and may be
affected by the precision settings in Excel. If you need a higher precision value of pi, you
can use the formula:
=4*ATAN (1)
This formula uses the inverse tangent function to calculate pi to the number of decimal
places determined by the Excel settings.

39
17. MODE: The MODE function in Excel is used to find the most frequently occurring value in a
range of cells. It returns the mode, which is the value that appears most often in the range.
The syntax for the MODE function is:
=MODE (number1,[number2],...)
Where "number1", "number2", and so on, are the cells or ranges of cells from which you
want to find the mode.
For example, if you have a range of cells A1:A10 that contains the numbers 1, 2, 2, 3, 3,
3, 4, 4, 4, and 4, you would use the formula:
=MODE (A1:A10)
This formula would return the value 4, as it is the most frequently occurring value in the
range.
Note that the MODE function only returns a single value, even if there are multiple
modes in the range. If there are two or more values that appear the same number of times
and more frequently than any other value, the MODE function returns the smallest of
those values.
If there are no values that appear more than once, the MODE function returns the #N/A
error.
EXAMPLE:

70 40 70 20 70 50
30 30 10 60 30 80
40 40 80 60 50 80
10 10 10 10 20 50
50 20 90 10 50 70
10 30 90 60 60 30

COLOUMN ROW TABLE

70 10 10

COLOMUN – MODE (C55:H55)

ROW – MODE (C55:C60)
TABLE – MODE (C55:H60)

18. ROMAN: ROMAN function that can be used to convert Arabic numbers to Roman numerals.
The syntax for the ROMAN function is:
=ROMAN (number, [type])
Where "number" is the Arabic number, you want to convert, and "type" is an optional
argument that specifies the style of Roman numerals to be used.
40
 There are four possible values for the "type" argument:
0 or omitted: The default style, which uses subtractive notation for 4 (IV) and 9
(IX), and additive notation for other numbers.
1: Uses additive notation for all numbers.
2: Uses subtractive notation for all numbers.
3: Uses subtractive notation for all numbers, except for those ending in 9 (IX) and
4 (IV), which use additive notation.
Here are some examples of how to use the ROMAN function:
To convert the number 2019 to a Roman numeral using the default style, you would use
the formula:
=ROMAN (2019)
The result would be "MMXIX".
To convert the number 50 to a Roman numeral using additive notation, you would use the
formula:
=ROMAN (50, 1)
The result would be "L".
To convert the number 9 to a Roman numeral using subtractive notation, you would use
the formula:
=ROMAN (9, 2)
The result would be "IX".
EXAMPLE 1:

825 DCCCXXV
648 ?
362 ?
146 ?
= ROMAN (H65, 0)
19. PRODUCT: The PRODUCT function in Excel is used to multiply numbers together. It takes
a range of one or more cells as its argument and returns the product of the values in those
cells. The syntax for the PRODUCT function is:
=PRODUCT (number1, [number2] ...)

41
 Where "number1", "number2", and so on, are the cells or values that you want to
multiply together.
For example, if you want to calculate the product of the numbers in cells A1 through A5,
you would use the formula:
=PRODUCT (A1:A5)
This formula would multiply the values in cells A1, A2, A3, A4, and A5, and return the
result.
Note that the PRODUCT function can handle up to 255 arguments. If you need to
multiply more than 255 numbers together, you can use an array formula.

UNITS PRICE PRODUCT

256 800 204800
336 745 ?
486 600 ?

789 480 ?

PRODUCT (C66, D66)

20. SUMIF: The SUMIF function in Excel is used to sum a range of cells that meet specific
criteria. It takes three arguments: the range of cells to sum, the criteria to apply, and an
optional range of cells to test the criteria against.
The syntax for the SUMIF function is:
=SUMIF (range, criteria, [sum_ range])
Where "range" is the range of cells that you want to test the criteria against, "criteria" is
the criteria that you want to apply to the range, and "sum range" (optional) is the range of
cells that you want to sum if the criteria is met.
For example, if you have a list of sales data in cells A1:B10, where column A contains
the names of products and column B contains the corresponding sales figures, you could
use the SUMIF function to sum the sales of a specific product. Suppose you want to sum
the sales of the product "Widget". The formula would be
=SUMIF (A1:A10, "Widget", B1:B10)
This formula would sum the sales figures in column B where the corresponding product
in column A is "Widget".
Note that the criteria argument can also be a cell reference, a number, or an expression
that evaluates to a number or text string. If you use a cell reference as the criteria

42
 argument, the cell must contain a text string or a number. If you use a number as the
criteria argument, the function will sum the cells that have a numeric value equal to the
criteria. If you use an expression as the criteria argument, the expression must be
enclosed in quotation marks.
EXAMPLE: suppose different countries played in different events and they
scored points. Calculate thier total point from different events.

COUNTRY EVENT POINTS

ENGLAND EVENT 1 3
INDIA EVENT 1 1
AUSTRALIA EVENT 2 3
FRANCE EVENT 1 5
SUMIF
ENGLAND EVENT 3 3
INDIA EVENT 2 1
ENGLAND EVENT 3 1
INDIA EVENT 4 3
FRANCE EVENT 5 3
AUSTRALIA EVENT 6 3
(I6:I15,"australia", K6:K15)

COUNTRY POINTS
INDIA 6

SUMIF (I6:I15, N12, K6:K15)

COUNTRY POINTS
FRANCE 8

21. SUMIFS: The SUMIFS function in Excel allows you to sum values in a range that meet
multiple criteria. It is useful when you want to sum values based on two or more conditions.
The syntax for the SUMIFS function is as follows:
=SUMIFS (sum_ range, criteria_range1, criteria1, [criteria_range2, criteria2] ...)
Sum_range: The range of cells you want to sum.

43
 criteria_range1: The range of cells where the first criteria applies.
criteria1: The criteria to use for the first range.
[criteria_range2, criteria2]: Optional additional range/criteria pairs.
You can add up to 127 range/criteria pairs.
Here's an example of how to use the SUMIFS function:
Suppose you have a table of sales data, and you want to sum the sales for the month of
January in the East region. You would use the following formula:
=SUMIFS (D2:D10, B2:B10, "East", C2:C10, "January")
In this formula, D2:D10 is the range of cells containing the sales data, B2:B10 is the range
of cells containing the region data, and C2:C10 is the range of cells containing the month
data. The criteria "east" and "January" are used to filter the data.
EXAMPLE 1:

SALES
DATE PRODUCT AMOUNT
REP
01-10-19 Peter Mars 500
01-10-19 Smith Galaxy 300
SUMIFS
(F21:F31, 01-10-19 Frank Snickers 400 D21:D31, I29,
E21:E31, J29) 08-10-19 Peter Galaxy 200
08-10-19 Smith Snickers 500
Sales Rep Product Total Amount
08-10-19 Frank Mars 400
frank
15-10-19 Petermars Galaxy 900
700
15-10-19 Smith Snickers 700
These are just some of the
many Math & 15-10-19 Frank Mars 500 Trig functions
available in 22-10-19 Peter Snickers 900 Excel. You can
use them to 22-10-19 Smith Snickers 900 perform a wide
range of calculations
and manipulations in your spreadsheets.
LOOKUP & RFERENCE FUNCTIONS
1. LOOKUP: The LOOKUP function in Excel is used to search for a value in a range of
cells and return a corresponding value from another range of cells. It can be used to
perform approximate or exact matches, depending on the values in the lookup range.

44
 The syntax for the LOOKUP function is:
= LOOKUP (lookup_value, lookup_vector, [result_vector])
where "lookup_value" is the value that you want to search for, "lookup_vector" is the
range of cells that contains the values to search for the lookup value, and
"[result_vector]" (optional) is the range of cells that contains the corresponding values to
return.
If the lookup range is sorted in ascending order, the LOOKUP function will perform an
approximate match, returning the value from the result range that corresponds to the last
value in the lookup range that is less than or equal to the lookup value. If the lookup
range is sorted in descending order, the function will return the first value in the lookup
range that is less than or equal to the lookup value.
If the lookup range is not sorted, the LOOKUP function will perform an exact match,
returning the value from the result range that corresponds to the first cell in the lookup
range that matches the lookup value.
For example, suppose you have a table of employee names and salaries in cells A1:B10,
and you want to find the salary for an employee with the name "John". If the names are
sorted alphabetically, you could use the formula:
= LOOKUP ("John", A1:A10, B1:B10)
This formula would search for the name "John" in the range A1:A10, and return the
corresponding salary from the range B1:B10.
Note that the LOOKUP function is not case-sensitive, and it requires that the lookup
range is sorted in ascending or descending order for approximate matches.
2. VLOOKUP: The VLOOKUP function in Excel is used to search for a value in the
leftmost column of a table or range, and then return a corresponding value from another
column in the same row. It is commonly used to perform lookups on large tables or data
sets.
The syntax for the VLOOKUP function is:
= VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])
where "lookup_value" is the value that you want to search for, "table_array" is the range
of cells that contains the table or range that you want to search in, "col_index_num" is the
number of the column in the table that contains the value you want to return, and
"[range_lookup]" (optional) is a logical value that indicates whether you want to perform
an exact match or an approximate match.
If the range lookup argument is set to TRUE or omitted, the VLOOKUP function will
perform an approximate match, searching for the closest match to the lookup value in the
leftmost column of the table. If the range lookup argument is set to FALSE, the function

45
 will perform an exact match, searching for the exact match to the lookup value in the
leftmost column of the table.
For example, suppose you have a table of employee names, salaries, and departments in
cells A1:C10, and you want to find the department for an employee with a salary of
$50,000. If the table is sorted in ascending order by salary, you could use the formula:
= VLOOKUP (50000, A1:C10, 3, TRUE)
This formula would search for the value 50000 in the leftmost column of the range
A1:C10, and return the corresponding value from the third column of the range (which
contains the department name).
Note that if the lookup value is not found in the leftmost column of the table, the
VLOOKUP function will return an error.
EXAMPLE:
NAME DEPARTMENT POST SALARY
ELDHOSE COMPUTER MANAGER 25000
PRAVEEN COMPUTER SUPERVISOR 15000
JINS COMPUTER TECHNICIAN 12000
RANI HR HEAD 30000
MINI HR MANAGER 22000
SOLY FIANCIAL HEAD 35000
SHIJI FINANCIAL MANAGER 27000
AGNAL FINANCIAL ASSISTANT 22000

NAME SALARY
RANI 30000
VLOOKUP (H6, B4:E12,4, FALSE)
3. HLOOKUP: The HLOOKUP function in Excel is similar to the VLOOKUP function,
but it searches for a value in the top row of a table or range, and then returns a
corresponding value from a row below. It is useful for looking up values in large tables
with multiple rows and columns.
The syntax for the HLOOKUP function is:
= HLOOKUP (lookup_value, table_array, row_index_num, [range_lookup])
where "lookup_value" is the value that you want to search for, "table_array" is the range
of cells that contains the table or range that you want to search in, "row_index_num" is
the number of the row in the table that contains the value you want to return, and

46
 "[range_lookup]" (optional) is a logical value that indicates whether you want to perform
an exact match or an approximate match.
If the range_lookup argument is set to TRUE or omitted, the HLOOKUP function will
perform an approximate match, searching for the closest match to the lookup value in the
top row of the table. If the range_lookup argument is set to FALSE, the function will
perform an exact match, searching for the exact match to the lookup value in the top row
of the table.
For example, suppose you have a table of product names, prices, and discounts in cells
A1:D10, and you want to find the discounted price for a product with the name "Widget".
If the table is sorted in ascending order by product name, you could use the formula:
= HLOOKUP ("Widget", A1:D10, 3, TRUE)
This formula would search for the value "Widget" in the top row of the range A1:D10,
and return the corresponding value from the third row of the range (which contains the
discounted price).
Note that if the lookup value is not found in the top row of the table, the HLOOKUP
function will return an error.
EXAMPLE:
NAME ELDHO PRAVE JINS RAN MINI SOLY SHIJI AGNA
SE EN I L
DEPART COMP COMPU COMPU HR HR FINAN FINAN FINAN
MENT UTER TER TER CIAL CIAL CIAL
POST MANA SUPER TECHNI HEA MANA HEAD MANA ASSIST
GER VISOR CIAN D GER GER ANT
SALARY 25000 15000 12000 3000 22000 35000 27000 22000
0

NAME SALARY
JINS TECHNICIAN
= HLOOKUP (B26, B19:J22,3, FALSE)
AUTO FILTER
The AutoFilter feature in Excel is a tool that allows you to quickly filter data in a range of cells
based on specific criteria. This can be helpful when you are working with large amounts of data
and want to narrow down your results to only show certain values.
To apply an AutoFilter:
Select the range of cells that you want to filter.

47
Go to the "Data" tab in the ribbon and click on "Filter" (alternatively, you can use the
keyboard shortcut "Ctrl+Shift+L").
This will add drop-down arrows to the top of each column in the range. Click on the drop-down
arrow for the column that you want to filter.
In the drop-down menu, you can select specific values to include or exclude from the filter, or
you can use the search box to search for specific values.
You can also select the "Filter by Color" option to filter by cell or font color.
You can add additional filters for other columns in the same range by selecting the drop-down
arrow for each column and applying the desired criteria.
To clear the filter, simply go to the "Data" tab and click on "Clear" (or use the keyboard
shortcut "Ctrl+Shift+L" again).
AutoFilter also allows you to apply complex filters by using the "Custom Filter" option. This
allows you to filter by multiple criteria using operators such as "equals to", "greater than",
"less than", "not equals to", and "contains".
In addition to filtering, AutoFilter also provides several other useful features, such as sorting,
searching, and hiding or showing specific columns. These features can be accessed by clicking
on the drop-down arrow for the column and selecting the desired option.
PIVOT TABLE
A PivotTable is a powerful data analysis tool in Microsoft Excel that allows you to summarize
and analyze large amounts of data quickly and easily. A PivotTable can automatically group data
by categories and create summaries, such as totals, averages, or percentages. It can also display
data in different ways, such as rows and columns, charts, or graphs.
Here are the basic steps to create a PivotTable in Excel:
Select the range of cells that contains your data.
Go to the "Insert" tab in the ribbon and click on "PivotTable".
In the Create PivotTable dialog box, select the range of cells that contains your data and choose
where you want the PivotTable to be located (for example, in a new worksheet or in an existing
worksheet).
Click "OK".
The PivotTable Field List will appear on the right side of the screen. This list contains the fields
from your data that you can add to your PivotTable.
Drag and drop the fields that you want to analyze into the "Rows", "Columns", "Values", and
"Filters" areas of the PivotTable Field List.
Excel will automatically create a PivotTable based on your selections.

48
Once you have created a PivotTable, you can customize it in many ways. For example, you can
change the type of summary calculation, such as sum or average, by clicking on the drop-down
arrow next to the value field in the Values area. You can also sort and filter the data by clicking
on the drop-down arrows next to the row and column fields. Additionally, you can change the
layout of the PivotTable by dragging and dropping fields between the Rows and Columns areas,
or by selecting a different PivotTable layout from the "Design" tab in the ribbon.

EXAMPLE:

Sl. No Sales Rep Region Items Unit Price Amount

1 Saniga West Galaxy 300 125 37500
2 Amritha West Mars 400 130 52000
3 Anaswara North Snickers 500 140 70000
4 Saniga South Bounty 600 155 93000
5 Amritha East Galaxy 700 120 84000
6 Anaswara North Mars 1000 140 140000
7 Saniga West Snickers 900 146 131400
8 Amritha South Mars 800 132 105600
9 Anaswara East Mars 850 136 115600
10 Saniga South Bounty 750 180 135000
11 Amritha North Snickers 650 145 94250
12 Anaswara West Galaxy 450 175 78750
13 Saniga West Galaxy 200 200 40000
14 Amritha East Snickers 250 225 56250
15 Anaswara South Bounty 280 185 51800
16 Saniga North Mars 340 190 64600
17 Amritha North Bounty 560 135 75600
18 Anaswara East Galaxy 345 130 44850
19 Saniga West Snickers 458 145 66410
20 Amritha South Mars 657 165 108405
Sum of
Amount Column Labels

49
 Grand
Row Labels East North South West Total
Amritha 140250 169850 214005 52000 576105
Bounty 75600 75600
Galaxy 84000 84000
Mars 214005 52000 266005
Snickers 56250 94250 150500
Anaswara 160450 210000 51800 78750 501000
Bounty 51800 51800
Galaxy 44850 78750 123600
Mars 115600 140000 255600
Snickers 70000 70000
Saniga 64600 228000 275310 567910
Bounty 228000 228000
Galaxy 77500 77500
Mars 64600 64600
Snickers 197810 197810
Grand Total 300700 444450 493805 406060 1645015

PIVOT CHART
A PivotChart is a graphical representation of data in a PivotTable. It allows you to quickly
analyze and visualize large amounts of data in a clear and concise manner.
To create a PivotChart in Excel:
Create a PivotTable by selecting the range of cells that contains your data and going to the
"Insert" tab in the ribbon and clicking on "PivotTable."
In the Create PivotTable dialog box, choose where you want the PivotTable to be located and
click "OK."
The PivotTable Field List will appear on the right side of the screen. Drag and drop the fields
that you want to analyze into the "Rows", "Columns", "Values", and "Filters" areas of the
PivotTable Field List.
Click anywhere inside the PivotTable.
Go to the "Insert" tab in the ribbon and click on "PivotChart."
In the Insert Chart dialog box, select the type of chart you want to create and click "OK."

50
Excel will automatically create a PivotChart based on your selections.
Once you have created a PivotChart, you can customize it in many ways. For example, you can
change the type of chart by selecting a different chart type from the "Design" tab in the ribbon.
You can also change the layout and style of the chart by selecting a different chart layout or style
from the "Design" tab. Additionally, you can customize the chart elements, such as the axis
labels and legend, by selecting them and using the formatting options in the "Format" tab. You
can also update the data in the chart by changing the data range of the PivotTable.
EXAMPLE: Using the above information

300000
250000
AMOUNT

200000
150000 West
100000 South
50000
0
North
Bounty

Mars

Bounty

Mars

Bounty

Mars
Snickers

Snickers

Snickers
Galaxy

Galaxy

Galaxy

East

Amritha Anaswara Saniga

SALES REP

DATA VALIDATION
Data validation is a feature in Microsoft Excel that allows you to control the type of data that can
be entered into a cell or range of cells. This feature helps to ensure that your data is accurate and
consistent, and can help prevent errors or incorrect data from being entered.
To use data validation in Excel:
Select the cell or range of cells where you want to apply data validation.
Go to the "Data" tab in the ribbon and click on "Data Validation".
In the Data Validation dialog box, choose the type of validation you want to apply. For example,
you can choose to allow only whole numbers, decimal numbers, dates, or text.
Configure the specific validation options based on the type of validation you have chosen. For
example, if you have chosen to allow only whole numbers, you can set a range of acceptable
values, or require the values to be greater than or less than a certain value.

51
Customize the error message that appears when an invalid value is entered, or choose to prevent
the user from entering an invalid value altogether.
Click "OK" to apply the validation.
Once you have applied data validation to a cell or range of cells, Excel will enforce the
validation rules and prevent any invalid data from being entered. If an invalid value is entered, an
error message will appear, and the user will be prompted to enter a valid value. This can help
ensure that your data is accurate and consistent, and can save time and effort by preventing errors
or mistakes.
CONDITIONAL FORMATTING
Conditional formatting is a feature in Microsoft Excel that allows you to apply formatting to
cells based on certain conditions or criteria. This feature can be very useful for highlighting
important data, identifying trends, or identifying errors in your data.
To use conditional formatting in Excel:
Select the cells or range of cells where you want to apply conditional formatting.
Go to the "Home" tab in the ribbon and click on "Conditional Formatting".
Choose the type of formatting you want to apply based on the conditions or criteria you want to
use. For example, you can choose to highlight cells that contain a certain value, cells that are
above or below a certain value, or cells that meet a certain formula or function.
Configure the specific formatting options based on the type of formatting you have chosen. For
example, if you have chosen to highlight cells that are above a certain value, you can choose the
color or style of the formatting that will be applied.
Click "OK" to apply the conditional formatting.
Once you have applied conditional formatting to a cell or range of cells, Excel will automatically
apply the formatting based on the conditions or criteria you have specified. This can help to
make your data more visually appealing and easier to read, and can also help to identify
important trends or anomalies in your data. Additionally, conditional formatting can be a
powerful tool for data analysis, as it can help to identify patterns or trends that might not be
immediately apparent by simply looking at the raw data.

CUSTOM LIST
A custom list in Microsoft Excel is a set of values that you can define and use for sorting and
filling in data. By creating a custom list, you can save time and ensure consistency by specifying
a predefined set of values that Excel will recognize and use in certain situations.
To create a custom list in Excel:

52
Go to the "File" tab in the ribbon and click on "Options".
In the Excel Options dialog box, click on "Advanced".
Scroll down to the "General" section and click on "Edit Custom Lists".
In the Custom Lists dialog box, click on "New List".
In the "List Entries" box, enter the values you want to include in the custom list, one value per
line.
Click "Add" to add the list to the Custom Lists box.
Click "OK" to save the custom list.
Once you have created a custom list in Excel, you can use it for sorting and filling data. For
example, if you have a column of data that includes the names of months, you can use the
custom list to sort the data in chronological order instead of alphabetical order. You can also use
the custom list to quickly fill in data, such as the days of the week or a list of department names.
By creating a custom list, you can ensure consistency and save time when working with your
data in Excel.
GOAL SEEK
The Goal Seek function in Microsoft Excel is a useful tool for finding the input value that is
needed to achieve a desired result in a formula. This function can be particularly useful when
you know the desired outcome of a formula, but you are unsure of the input value that is required
to achieve that outcome.
To use Goal Seek in Excel:
Open the workbook containing the formula you want to work with.
Click on the cell containing the result you want to achieve.
Go to the "Data" tab in the ribbon and click on "What-If Analysis".
Select "Goal Seek" from the drop-down menu.
In the Goal Seek dialog box, specify the following:
Set Cell: The cell containing the result you want to achieve.
To value: The desired value for the set cell.
By changing cell: The cell containing the input value you want to adjust.
Click "OK" to run Goal Seek.
Once you have run Goal Seek, Excel will automatically adjust the input value in the specified
cell to achieve the desired result in the set cell. This can be a very useful tool for working with

53
complex formulas or large datasets, as it allows you to quickly and easily find the input value
that is needed to achieve a desired outcome.
FREEZE PANE
Freeze panes in Microsoft Excel is a useful feature that allows you to freeze certain rows and/or
columns in a worksheet, so that they remain visible as you scroll through the rest of the data.
This can be particularly useful when working with large worksheets that contain a lot of data, as
it allows you to keep key information visible while you navigate through the rest of the
worksheet.
To freeze panes in Excel:
Open the workbook containing the worksheet you want to work with.
Click on the cell that is immediately to the right of the row(s) you want to freeze, or immediately
below the column(s) you want to freeze.
Go to the "View" tab in the ribbon and click on "Freeze Panes".
Select "Freeze Panes" from the drop-down menu.
Excel will automatically freeze the rows and/or columns that are above and/or to the left of the
selected cell. You can then navigate through the worksheet and the frozen rows and/or columns
will remain visible at all times. To unfreeze the panes, simply click on "Unfreeze Panes" from
the "Freeze Panes" drop-down menu.
Alternatively, if you want to freeze only certain rows and/or columns, you can select the row(s)
and/or column(s) that you want to freeze, and then click on "Freeze Panes" from the "Freeze
Panes" drop-down menu. Excel will then freeze only the selected rows and/or columns.

ASSIGNMENT 1

Use of formulas SUM, AVERAGE, IF, COUNT IF, SUMIF, COUNTA

Roll Student
Hindi English Maths Physics Chemistry Total Average Grade
No Name
1 Aleena 75 73 74 76 74 372 74.4 A+
2 Anjaly 70 72 71 70 68 ? ? ?
3 Roshan 45 38 38 42 42 ? ? ?
4 Rijo 35 28 15 30 26 ? ? ?
5 Smith 60 55 48 55 51 ? ? ?
6 Sreelakshmi 48 52 58 56 44 ? ? ?
7 Dilna 78 79 78 79 78 ? ? ?
8 Anjoom 18 15 12 20 18 ? ? ?
9 Christy 22 20 25 22 19 ? ? ?

54
 10 Neshwa 16 22 12 25 23 ? ? ?

1 Find the Total & Average in all subjects in each student

2 Find Grade using IF function -
Greater than 60 = A+
Greater than 50 = A
Greater than 40 = B
Greater than 30 = B+
Greater than 20 = C+
Less than 20 = C
3 How many student get A+ & A Grade
4 The Total and Average of Dilna & Roshan
5 Count how many students
6 How many students gets Maths & Physics Subjects Greater than 70 and less than 30

ASSIGNMENT 2

Use of formulas - PRODUCT, IF, COUNTA, COUNTIF, SUMIF

Sl.
Items Quantity Rate Amount Grade
No
1 AC 25 40,000 1000000 EXPENSIVE
2 FRIDGE 35 35,000 ? ?
3 COOLER 45 15,000 ? ?
4 WASHING MACHINE 30 28,000 ? ?
5 TV 50 32,000 ? ?
6 FAN 40 6,000 ? ?
7 COMPUTER 60 25,000 ? ?
8 KEYBOARD 20 340 ? ?
9 MOUSE 25 250 ? ?
10 PRINTER 70 18,000 ? ?

1. How many items in a list?

2. How many items quantity greater than 40 and less than 30
3. Calculate item washing machine Quantity, Rate, Amount Using SUMIF Function

ASSIGNMENT 3

Use of formulas - SUM, NESTEDIF, COUNTA, COUNTIF, SUMIF, VLOOKUP

55
NAM DEPARTM POST BASIC DA HRA PF NET GRA
E ENT PAY 2.5% 3.5% 1.5% AMOUNT DE
JOSE ADMINIST HEAD 25000 625 875 375 26125 A
PH RATION
AMJI ADMINIST ASSIST 21000 ? ? ? ? ?
TH RATION ANT
SAM ADMINIST ASSIST 21000 ? ? ? ? ?
EER RATION ANT
ASHL FINANCE HEAD 28500 ? ? ? ? ?
IN
SANJ FINANCE ACCOU 22300 ? ? ? ? ?
OE NTANT
AKHI FINANCE CASHIE 12700 ? ? ? ? ?
L R
CHES HUMAN HEAD 26000 ? ? ? ? ?
TIN RESOURCE
MALI HUMAN ASSIST 22600 ? ? ? ? ?
NI RESOURCE ANT
PRIY SALES HEAD 30000 ? ? ? ? ?
A
ALEE SALES MANAG 26000 ? ? ? ? ?
NA ER
AMRI SALES EXECUT 15000 ? ? ? ? ?
THA IVE

1. Calculate DA, HRA, PF & NET AMOUNT?

DA 2.50%
HRA 3.50%
PF 1.50%
NET BASICPAY
AMOUNT + DA +
HRA - PF
2. Find Grade using IF function

>=25000 A
>=20000 B
<20000 C

3. How many employees in Administration, Finance, Human resource, Sales?

4. How many basic salaries in finance department only?
5. Post & Grade of Sameer & Malini?

ASSIGNMENT 4

56
 Use of formulas - SUM, IF, COUNTA, COUNTIF, VLOOKUP, LOOKUP
TOTAL
SALESMAN JAN FEB MAR APR MAY JUN TARGET RESULT
SALES
NOT
ALAN 20000 17000 16000 15750 28500 2000 99250 1,00,000
ACHIEVED
JOHN 25000 16000 38000 18650 27600 25600 ? 2,00,000 ?

NAVEEN 30000 25000 22000 22340 26800 30100 ? 1,50,000 ?

ABLE 32000 22000 45600 36780 24125 33300 ? 1,25,000 ?

FREDDY 28000 23000 35700 12465 36450 29500 ? 2,00,000 ?

JOHNY 22000 28000 61000 54200 22560 22650 ? 2,00,000 ?

ALEKH 21000 36000 18500 32200 38750 23450 ? 2,00,000 ?

SONIYA 19000 28000 32000 13560 16780 26780 ? 1,50,000 ?

ALEENA 18000 22000 31400 22650 12560 18260 ? 1,00,000 ?

DIYA 40000 23000 26900 38950 45870 40740 ? 2,00,000 ?

BEENA 36000 18000 28700 32300 28760 36870 ? 1,50,000 ?

ANJALY 50000 42000 36800 42650 37450 50560 ? 2,00,000 ?

1. Calculate Total Sales?

2. If sales greater than target then result will shows ACHIEVED otherwise NOT
ACHIEVED
3. How many Salesmen?
4. Salesman Naveen's Target and Result using VLOOKUP function?
5. How many salesmen Achieved Target?

ASSIGNMENT 5

57
 Create PIVOT TABLE & PIVOT CHART using data
Sl. No Sales Rep Region Items Unit Price Amount
1 Jins East Galaxy 20000 60 1200000
2 Praveen West Snickers 25000 70 1750000
3 Jose North Bounty 22000 80 1760000
4 Jins South Galaxy 21000 60 1260000
5 Praveen East Snickers 30000 70 2100000
6 Jose West Bounty 38000 80 3040000
7 Jins North Bounty 32000 80 2560000
8 Jins South Snickers 26000 70 1820000
9 Jose East Galaxy 27000 60 1620000
10 Praveen West Snickers 28000 70 1960000
11 Praveen South Galaxy 31400 60 1884000
12 Jins North Bounty 32500 80 2600000
13 Jose West Bounty 36500 80 2920000
14 Praveen North Galaxy 20450 60 1227000
15 Praveen South Snickers 20460 70 1432200
16 Jose East Galaxy 20400 60 1224000
17 Jins East Snickers 30700 70 2149000
18 Jins West Bounty 32500 80 2600000
19 Jins North Bounty 31600 80 2528000
20 Jose South Snickers 37800 70 2646000
21 Jose South Galaxy 36540 60 2192400
22 Praveen North Galaxy 36875 60 2212500
23 Praveen West Snickers 125600 70 8792000
24 Praveen East Bounty 12640 80 1011200
25 Jins East Bounty 40000 80 3200000
ASSIGNMENT 6

USA Annual Purchases Report 2015

Gender

Customer Annual
City Education Annual Salary
ID Purchases
C11 M New York University 6748 7500
C12 M New York High School 5964 5000
C13 F Seattle University 7560 6780
C14 M Chicago University 8015 11000
C15 F New York University 5345 27500
58
 C16 F Chicago High School 4586 15650
C17 F Seattle None 5682 4000
C18 M Seattle University 6675 8560
C19 F Seattle None 7065 12460
C20 M New York University 7402 11806
C21 F Chicago High School 8605 12680

1. Open a new Workbook and create the above worksheet

2. Make sure that your worksheet looks like the picture (Alignment, Shedding, Borders,
Wrap text, Orientation…)
3. Format column E & D to currency with dollar sign and two decimal places
4. Find the total annual purchase for each city
5. Find the average annual purchases for each education

6. Find the total number of customers from each gender

7. Find the total annual salary for each gender in each city

8. Create the charts above the information’s

ASSIGNMENT 7
Use of formulas HLOOKUP & VLOOKUP
VLOOKUP
ID BRAND PRODUCT
A101 DELL COMPUTER
A102 LOGITECH KEYBOARD
A103 ACER MOUSE
A104 HP PRINTER

ID BRAND PRODUCT
A104 HP PRINTER
A103 ? ?
A101 ? ?
A102 ? ?

HLLOKUP
ID A101 A102 A103 A104

59
 BRAND DELL LOGITECH ACER HP
PRODUCT COMPUTER KEYBOARD MOUSE PRINTER

ID BRAND PRODUCT
A102 LOGITECH KEYBOARD
A101 ? ?
A103 ? ?
A104 ? ?

ASSIGNMENT 8

LEFT
Number of
Text Left string
characters required
TRUE VISORY 1 T
TRUE VISORY 2 ?
TRUE VISORY 3 ?
TRUE VISORY 4 ?

LEFT & FIND

Full Name First Name
TRUE VISORY TRUE
LEARNING ACADEMY ?

LEN
Text Length
TRUE VISORY ?
LEARNING ACADEMY ?

LOWER
Text Lower
TRUE VISORY ?
LEARNING ACADEMY ?

UPPER
Text Upper

60
 true visory ?
learning academy ?

PROPER
Text Proper
true visory ?
learning academy ?

TRIM
Text Trim
true visory ?
learning academy ?

Use of CONCATENATE
EMP ID FIRST NAME LAST NAME FULL NAME
D21 Anjaly Saju Anjaly Saju
D22 Alan John ?
D23 Ann Mary ?
D24 Sebin Sebastian ?
D25 Vincent Jose ?
D26 Paul Baby ?

61