PHASE - 1

RESEARCH METHODOLOGY PRACTICAL FILE

Submitted in Partial Fulfillment of the

Requirements of BBA 213

Bachelor of Business Administration (BBA)

Guru Gobind Singh Indraprastha University

Batch (2022-2025)

Dr. Seema Wadhawan Student Name – Devika Gupta

Associate Professor Enrollment no. – 01814201722
 CONTENTS
S No Topic Page No

1  Introduction To MS Excel 3-4

 Features of MS Excel

2 Practical – 1 (Arithmetic Functions) 5-9

 SUM
 SUMIF
 SUMIFS
 COUNT
 COUNTIF
 MIN Function
 MAX Function

3 Practical - 2 (Autofill Function) 10-20

4 Practical – 3 (Excel Charts) 21-28

5 Practical – 4 (Pivot Table) 29-30

6 Practical – 5 (Data Validation) 31-38

 RM LAB PRACTICAL FILE

1. INTRODUCTION TO MS EXCEL

(a) What is MS Excel?

Microsoft Excel is a powerful spreadsheet application developed by Microsoft that

is widely used for various tasks involving data analysis, manipulation, and
visualization. It is a part of the Microsoft Office suite of productivity software and
has been a standard tool for businesses, organizations, and individuals for decades.
Excel provides a user-friendly interface and a range of features that make it a
versatile tool for managing and analyzing data. Let's explore its key components
and functionalities in detail:

Workbook: An Excel file is called a workbook, and it can contain multiple

worksheets (also known as spreadsheets). Each worksheet consists of rows and
columns, forming a grid of cells where you can input and manipulate data.

Cells: Cells are the fundamental units in Excel, and they are identified by their
unique combination of a column letter and a row number (e.g., A1, B2, C3, etc.).
You can enter various types of data into cells, such as numbers, text, dates, and
formulas.

Formulas and Functions: Excel is known for its powerful formula and function
capabilities. Formulas are expressions that perform calculations on data in cells.
For example, you can use formulas to add numbers, calculate averages, or perform
complex mathematical operations. Excel also includes a wide range of built-in
functions (e.g., SUM, AVERAGE, IF, VLOOKUP) that simplify common
calculations.

Charts and Graphs: Excel allows you to create various types of charts and
graphs to visually represent your data. You can choose from bar charts, line
graphs, pie charts, scatter plots, and more. These visuals make it easier to interpret
and present your data effectively.
 Data Analysis Tools: Excel offers several data analysis tools and features, such
as pivot tables, sorting, filtering, and data validation. Pivot tables are particularly
useful for summarizing and analyzing large datasets.

(b) Features of MS Excel

Here are some key features and functions of Microsoft Excel:
 Spreadsheet Interface: Excel provides a grid-like interface where users can
organize data into rows and columns. Each intersection of a row and a column
is called a cell, and you can enter data, text, numbers, and formulas into these
cells.
 Formulas and Functions: Excel offers a wide range of mathematical,
statistical, and logical functions that allow users to perform calculations on
data. Formulas can be created using these functions and can reference other
cells, making it easy to perform complex calculations.
 Data Analysis: Excel provides tools for data analysis, including sorting,
filtering, and pivot tables. These features allow users to manipulate and
summarize data quickly.
 Charts and Graphs: You can create various types of charts and graphs in Excel
to visualize data. This includes bar charts, line graphs, pie charts, and more,
which can make it easier to understand data trends.
 Data Import and Export: Excel can import data from a variety of sources,
including databases, text files, and the internet. It also allows you to export
your spreadsheets in different formats for sharing and collaboration
 Conditional Formatting: You can apply conditional formatting to cells, which
changes the appearance of cells based on specified conditions. This is useful
for highlighting important data or trends.
 Automation and Macros: Excel supports the creation of macros, which are
sequences of actions recorded by the user or written in VBA (Visual Basic for
Applications). Macros can automate repetitive tasks.
 Data Validation: You can set rules for data entry to ensure that only valid data
is entered into cells, helping maintain data accuracy.
 Collaboration: Excel can be used collaboratively through features like sharing
workbooks, tracking changes, and commenting on cells. Multiple users can
work on the same spreadsheet simultaneously, making it suitable for
teamwork.
PRACTICAL I – ARITHMETIC FUNCTIONS

1. SUM
Using the SUM function in Microsoft Excel is quite straightforward. The SUM
function is used to add up a range of numbers in a spreadsheet. Here are the steps to
use the SUM function:
i. This is typically the cell immediately below or to the right of the numbers you
want to add.
ii. In the selected cell, type "=SUM(" (without the quotes). Excel will prompt you
with a tooltip suggesting the SUM function as you start typing.
iii. Inside the parentheses, select the range of cells that contain the numbers you
want to add. You can do this by clicking and dragging your cursor to highlight
the range, or you can manually type the cell references. For example, if you
want to add the numbers in cells A1 to A5, you can type "=SUM(A1:A5)".
iv. After selecting the range, close the function with a closing parenthesis (")").
v. To calculate the sum, press the Enter key on your keyboard. Excel will
instantly display the sum of the numbers in the selected range in the cell where
you entered the formula.
2. SUMIF
The SUMIF function in Microsoft Excel allows you to sum values in a range based on
a specified condition or criteria. Here are the steps to use the SUMIF function:
i. Launch Microsoft Excel and open the spreadsheet where you want to use the
SUMIF function.
ii. Click on the cell where you want the result of the SUMIF function to appear.
This is typically the cell where you want the sum to be displayed.
iii. Click on the Formula Bar at the top of the Excel window. This is where you
will enter the SUMIF function manually.
iv. The basic syntax of the SUMIF function is as follows: “=SUMIF(range,
criteria, [sum_range])”.
v. In the Formula Bar, type the SUMIF function with the appropriate arguments.
vi. After entering the function, press the Enter key. Excel will calculate the result
and display it in the selected cell.
3. SUMIFS

The SUMIFS function in Microsoft Excel allows you to sum values based on multiple
criteria. This function is useful when you have a dataset with multiple conditions that
need to be met for the values to be included in the sum. Here are the steps to use the
SUMIFS function:
i. Launch Microsoft Excel and open the spreadsheet that contains the data you
want to work with.
ii. Before you start using the SUMIFS function, identify the criteria that you
want to apply to your data. You'll need to determine the range of cells that
meet these criteria and the range of cells containing the values you want to
sum.
iii. Click on the cell where you want the sum result to appear. This is where you'll
enter the SUMIFS formula.
iv. Type the following formula into the selected cell: “=SUMIFS(sum_range,
criteria_range1, criteria1, [criteria_range2, criteria2], ...)”.
v. After entering the formula, press the Enter key. Excel will calculate the sum
based on the specified criteria and display the result in the selected cell.

4. COUNT
The COUNT function in Microsoft Excel is used to count the number of cells in a
range that contain numeric values. Here are the steps to use the COUNT function in
Excel:
i. Launch Microsoft Excel and open the worksheet where you want to use the
COUNT function.
ii. Click on the cell where you want the result of the COUNT function to appear.
This is typically the cell where you want to display the count.
iii. To start typing the COUNT function, you can manually type the formula in the
selected cell. The formula syntax is: =COUNT(range)
iv. After specifying the range, simply close the parentheses in the formula.
v. Press the "Enter" key to execute the COUNT function. Excel will calculate the
count of numeric values in the specified range and display the result in the
selected cell.
5. COUNTIF

The COUNTIF function in Microsoft Excel is used to count the number of cells
within a range that meet a specified condition or criteria. Here are the steps to use the
COUNTIF function:
i. Launch Microsoft Excel and open the spreadsheet where you want to use the
COUNTIF function.
ii. Click on the cell where you want the result of the COUNTIF function to
appear. This is where the count of matching cells will be displayed.
iii. Click on the formula bar at the top of the Excel window. This is where you
will enter the COUNTIF formula.
iv. To use COUNTIF, you need to specify two arguments:
Range: This is the range of cells you want to count within. Click and drag to
select the range, or manually enter the cell range address.
Criteria: This is the condition or criteria that you want to apply to the selected
range to determine which cells should be counted.
v. After entering the formula, press the Enter key. Excel will calculate the count
based on the specified criteria and display the result in the selected cell.
vi. The selected cell will now display the count of cells that meet the specified
criteria within the chosen range.
6. MIN Function
The MIN function in Microsoft Excel is used to find the smallest (minimum) value in
a range of numbers. Here are the steps to use the MIN function:
i. Launch Microsoft Excel and open the spreadsheet where you want to use the
MIN function.
ii. Click on the cell where you want the result of the MIN function to appear.
This is typically the cell where you want to display the minimum value.
iii. To use the MIN function, type "=MIN(" (without the quotes) in the selected
cell.
iv. Inside the parentheses, select the range of cells or numbers from which you
want to find the minimum value. You can do this by clicking and dragging
your mouse to highlight the cells or manually typing the cell references. For
 example, if you want to find the minimum value in cells A1 to A10, your
formula would look like this: "=MIN(A1:A10)".
v. After selecting the range, close the parentheses to complete the formula. Your
formula should now look like this: "=MIN(A1:A10)".
vi. Once you've entered the formula correctly, press the Enter key. Excel will
calculate the minimum value from the selected range and display it in the cell
you initially selected.
7. MAX Function
The MAX function in Microsoft Excel allows you to find the maximum value in a
range of cells or a list of numbers. Here are the steps to use the MAX function:
i. Launch Microsoft Excel and open the spreadsheet where you want to use the
MAX function.
ii. Click on the cell where you want the result of the MAX function to appear.
iii. Type "=MAX(" into the selected cell. This is the beginning of the MAX
function syntax.
iv. You can now either manually select the range of cells or type a list of numbers
separated by commas inside the parentheses.
v. After specifying the range of cells or list of numbers, close the parentheses by
typing ")".
vi. Hit the Enter key on your keyboard. Excel will calculate and display the
maximum value in the selected cell.
PRACTICAL II – AUTOFILL FUNCTION
1. SERIES OF NUMBER

Using the Autofill function in Microsoft Excel is a convenient way to quickly fill a
series of numbers, dates, or other data in a column or row. Here are the steps to use
the Autofill function:
i. Launch Microsoft Excel and open the spreadsheet where you want to use the
Autofill function.
ii. Enter the starting number in the cell where you want the series to begin. For
example, if you want to start with the number 1, enter "1" in the cell.
iii. Click on the cell containing the starting value. This is the cell that you want to
use as the basis for the Autofill series.
iv. Move your mouse cursor to the small square or "handle" located in the
bottom-right corner of the selected cell. When you hover over this handle, the
cursor will change to a black crosshair or "Fill Handle."
v. Click and hold the left mouse button on the Fill Handle, and then drag it either
down or across, depending on the direction in which you want to fill the series.
Excel will display a preview of the values as you drag.
vi. If you drag down, Excel will fill the cells in the column below the starting cell
with an incrementing series of numbers. If you drag across, Excel will fill the
cells in the row to the right of the starting cell.
 vii. Once you've reached the desired endpoint for your series, release the left
mouse button.
viii. Excel will automatically fill the series based on the pattern established by the
starting cell. You can now see the sequential numbers in the selected direction.

2. FILLING DAYS

In Microsoft Excel, you can use the Autofill function to quickly fill a series of days or
dates. Here are the steps to fill days using Autofill:
i. Launch Microsoft Excel and open the spreadsheet where you want to fill days.
ii. In the cell where you want the series of days to begin, enter the starting date.
iii. Click on the cell containing the starting date. This cell is now your active cell.
iv. In the lower-right corner of the active cell, you'll see a small square called the
Autofill handle. It's usually a small square at the corner of the selected cell.
v. Click and drag the Autofill handle down or to the right, depending on whether
you want to fill days vertically or horizontally. While dragging, Excel will
display a preview of the dates based on your initial selection.
vi. When you've dragged to the desired endpoint for your date series, release the
mouse button.
vii. Excel will automatically fill in the days or dates in the selected direction,
based on the pattern established by your starting date.
viii. Your series of days or dates is now filled using the Autofill function.
3. FILLING WEEKDAYS

To fill weekdays using the Autofill function in Microsoft Excel, follow these steps:
i. Launch Microsoft Excel and open the spreadsheet where you want to fill
weekdays.
ii. In a cell, enter the starting date for the weekdays.
iii. Click on the cell containing the starting date. This is the cell from which Excel
will begin filling in the weekdays.
iv. Hover your mouse cursor over the small square or "handle" located in the
lower-right corner of the selected cell. When your cursor is in the right
position, it will change to a black crosshair symbol.
v. Click and drag the fill handle down (for more weekdays) or to the right (for
future weekdays) while holding the left mouse button. As you drag, Excel will
fill in the weekdays based on the pattern it detects.
vi. When you've reached the desired number of weekdays or the endpoint you
want, release the left mouse button.
vii. Excel will automatically fill in the weekdays in the selected direction.
viii. Verify that Excel has correctly filled in the weekdays. It should continue the
pattern of weekdays (e.g., Monday, Tuesday, Wednesday) based on the
starting date.
4.FILLING MONTHS

Here are the steps to fill months using Autofill:

i. Launch Microsoft Excel and open the spreadsheet where you want to fill in the
months.
ii. In the cell where you want the series of months to start, type the name of the
first month. For example, you can start with "January" in cell A1.
iii. Click on the cell that contains the first month ("January" in this case). This is
the cell that will act as the starting point for the Autofill series.
iv. Move your cursor to the lower-right corner of the selected cell (cell A1). You
will see a small square called the "Fill Handle."
v. Click and drag the Fill Handle down to fill in the months for as many cells as
you need. If you want to fill in multiple columns, you can drag it horizontally
as well.
vi. When you've dragged the Fill Handle to the desired number of cells or the
endpoint where you want to stop, release the mouse button.
vii. After releasing the mouse button, a small AutoFill Options icon (a square with
a small square in the bottom-right corner) may appear. This icon allows you to
customize how Excel fills the series. You can choose to fill months in a linear
pattern, by rows, or by columns. Click on this icon if you want to explore
these options.
 viii. If you don't see the AutoFill Options icon and are satisfied with the default
options, simply releasing the mouse button will complete the Autofill, and
Excel will fill in the subsequent months in the selected cells.
ix. Check the cells to ensure that Excel has correctly filled in the series of months.
You should see "February," "March," and so on, depending on the number of
cells you filled.

4. FILLING A SERIES WITH FORMATTING

In Microsoft Excel, you can use the Autofill function to fill a series of data with
formatting. This is particularly useful when you want to apply a specific format to a
sequence of numbers, dates, or text. Here are the steps to fill a series with formatting
using Autofill:
i. In a cell, enter the first value or item in your series. This is the value that will
set the starting point for the series.
ii. Apply the desired formatting to the starting value. This can include font styles,
colours, borders, or any other formatting options you want to use.
iii. Click on the cell that contains the formatted starting value. This cell should
now be outlined or selected.
iv. Move your cursor to the bottom-right corner of the selected cell. Your cursor
should change to a small black square, known as the "Autofill handle" or "Fill
handle."
v. Click and hold the left mouse button on the Autofill handle.
 vi. Drag the cursor downwards (for a column) or to the right (for a row) while
holding the mouse button.
vii. When you release the mouse button, Excel will automatically fill the series
based on the formatting of the starting cell.
viii. Check the cells below or to the right of the starting cell to verify that Excel has
filled the series with the desired formatting. The series will continue in the
pattern established by the starting value.
ix. If Excel did not fill the series as expected or if you want to modify the series,
you can adjust the Autofill options. After releasing the mouse button, you may
see a small Auto Fill Options icon (a small square with a downward-pointing
arrow) near the filled cells. Click on this icon to access options like "Copy
Cells," "Fill Series," or "Fill Formatting Only" to fine-tune your results.

5. FILLING A SERIES WITHOUT FORMATTING

Using the AutoFill function in Microsoft Excel, you can quickly fill a series of values
or numbers without formatting. Here are the steps to do this:
i. Launch Microsoft Excel and open the spreadsheet where you want to fill a
series of values.
ii. In a cell, enter the starting value of your series. This can be any number or text
that you want to use as the beginning of your series.
iii. Click on the cell containing the starting value. This cell will be the reference
 point for filling the series.
iv. Move your cursor to the lower-right corner of the selected cell. Your cursor
will change to a small black square, which is called the "Fill Handle."
v. Click and hold the left mouse button while dragging the Fill Handle down or
across the cells where you want to fill the series. As you drag, you'll see a
preview of the series being generated.
vi. Once you've selected the desired number of cells for your series, release the
mouse button. Excel will fill the series based on the pattern it recognizes from
your starting value.
vii. After releasing the mouse button, you might see a small AutoFill Options
button next to the filled cells. Clicking this button allows you to choose
additional options for how the series should be filled, such as Fill Series, Fill
Formatting Only, or Fill Without Formatting.
viii. If you don't see the AutoFill Options button and need to fill the series
differently, you can manually drag the Fill Handle back to the starting point
and then re-drag it in a different direction or adjust the selection as needed.
ix. When you're satisfied with the filled series, release the Fill Handle.
6. FILL DOWN CELL
In Microsoft Excel, the "Fill Down" operation is used to copy the contents of a cell or
a series of cells from the cell above it and fill down into one or more adjacent cells.
Here are the steps to perform the "Fill Down" operation using the autofill function:
i. Enter the data or formula you want to copy into the first cell where you want
to start the "Fill Down" operation.
ii. Click on the first cell containing the data or formula you want to copy. The
selected cell will have a border around it.
iii. The fill handle is a small square located in the bottom-right corner of the
selected cell. When you hover your mouse pointer over this square, it will
change to a black crosshair cursor.
iv. Click and hold the left mouse button while hovering over the fill handle, and
then drag the cursor down (or across) to the cells where you want to copy the
data or formula. As you drag, Excel will create a highlighted "ghost" selection
to preview where the data will be filled.
v. Once you've dragged the cursor to the desired cells, release the left mouse
button. Excel will fill the selected cells with the copied data or formula.
vi. Verify that the data or formula has been successfully filled down into the
target cells. If you encounter any issues or need to make adjustments, you can
repeat the "Fill Down" operation as needed.
7. FILL RIGHT
In Microsoft Excel, you can use the "Fill Right" option within the Autofill function to
quickly copy data from the leftmost cell of a selected range across adjacent cells to
the right. Here are the steps to perform the "Fill Right" operation using Autofill:
i. In the leftmost cell where you want to start the data series or copying, enter the
initial value or data point.
ii. Click on the leftmost cell that contains the data you want to copy.
iii. Place your cursor over the small square in the bottom-right corner of the
selected cell. This small square is known as the "Fill Handle."
iv. Click and drag the Fill Handle to the right. As you drag, Excel will display a
preview of the data being copied to the adjacent cells.
v. When you reach the desired number of cells to copy the data into, release the
mouse button.
vi. Excel will then copy the data from the leftmost cell across the selected range
to the right. You will now have the data series or copied values in the adjacent
cells.

8. FILL DRAG CUSTOM LISTS

In Microsoft Excel, you can create custom lists to use with the AutoFill feature.
Custom lists allow you to quickly fill in a series of values that are not part of the
default AutoFill options, such as days of the week, months, or specific items you
frequently use. Here are the steps to perform fill drag custom lists in the AutoFill
function:
i. Open Excel and click on the "File" tab or Office button, depending on your
Excel version.
ii. Select "Options" or "Excel Options" from the menu. This opens the Excel
Options dialog box.
iii. In the Excel Options dialog box, go to the "Advanced" tab.
iv. Scroll down to the "General" section.
v. Click on the "Edit Custom Lists" button. This opens the Custom Lists dialog
box.
vi. In the Custom Lists dialog box, you'll see a box labeled "List entries."
vii. In this box, type the items for your custom list, each item on a separate line.
viii. After entering your list, click the "Add" button to add it to the custom list.
ix. Click "OK" to close the Custom Lists dialog box.
x. Click "OK" again to close the Excel Options dialog box
xi. Now that you've created your custom list, you can use it with the AutoFill
feature.
xii. In a cell, type the first item from your custom list.
xiii. Click and drag the fill handle (a small square at the bottom-right corner of the
selected cell) down or to the right to fill adjacent cells with the custom list.
Release the mouse button when you've filled the desired number of cells.
xiv. Excel will automatically fill in the cells with the items from your custom list,
in the order you specified. If you dragged the fill handle down, Excel will
continue the list in the same order. If you dragged it to the right, Excel will
cycle through the list and repeat it
PRACTICAL III – EXCEL CHARTS

1. CREATE CHARTS

Creating a chart in Microsoft Excel is a useful way to visually represent your data.
Here are the steps to create a chart in Excel:
i. Ensure that your data is organized in a way that makes sense for the type of
chart you want to create. Your data should have labels or headers for the
categories (X-axis) and the data series (Y-axis).
ii. Click on a cell within your data range.
iii. Drag your mouse to select the data range you want to include in the chart. This
will typically include both the labels and the data values.
iv. With the data selected, go to the "Insert" tab in the Excel ribbon at the top of
the window.
v. In the "Charts" group, you'll see various chart types such as Column, Line,
Pie, Bar, etc. Click on the chart type that best suits your data and visualization
needs. Excel will insert a blank chart into your worksheet.
vi. Excel will create a default chart based on your selected data. You can now
customize the chart to your liking by clicking on elements of the chart, such as
titles, labels, and data series, and modifying their properties. Right-clicking on
different elements also often provides options for customization.
 vii. To further customize the appearance of your chart, use the "Format Chart
Area" or "Format Data Series" options available in the Excel ribbon. This
allows you to change colours, fonts, line styles, and other formatting options.
viii. You can add elements like data labels, titles, legends, and axis labels to your
chart. Click on the chart to select it, and then look for the "+," "Chart
Elements," or "Add Chart Element" button that appears on the chart or in the
ribbon, depending on your Excel version.
ix. You can save the chart as an image or move it to a different location in your
worksheet by clicking and dragging it.
x. If your data changes, Excel will update the chart automatically if it's linked to
the data source. However, you may need to adjust the chart's data range if
you've added or removed data points.
xi. Save Your Excel File.

2. LEGEND POSITION

In Microsoft Excel, you can change the position of the legend in a chart to improve its
readability and appearance. Here are the steps to change the legend position in an
Excel chart:
i. Start by either creating a chart from your data or selecting an existing chart
that you want to modify.
ii. Click anywhere on the chart to select it. When the chart is selected, you should
 see various elements of the chart highlighted, including the legend.
iii. To access the legend formatting options, you can either right-click on the
legend or go to the "Chart Elements" button, typically located near the upper-
right corner of the chart (it looks like a plus sign or gear icon), and then select
"Legend" from the list.
iv. In the formatting options or context menu, you will find various choices for
legend placement. These options typically include: Right, Top, Left, Bottom
and None.
v. Click on the legend position option that you want to apply. The legend will
move to the selected position.
vi. Depending on the chosen position, you may want to resize the legend to better
fit the chart. To do this, click on the legend to select it, and then click and drag
its edges or corners to adjust its size.
vii. Once you have positioned the legend as desired, you can further customize
your chart by formatting other elements, such as axis labels, data labels, or
chart title.
viii. Save your changes, or if you are working with an existing chart, these
adjustments will be automatically applied when you update the chart data or
settings.

3. DATA LABELS
Adding data labels to a chart in Microsoft Excel can help you display the values of
data points directly on the chart, making it easier to interpret. Here are the steps to
insert data labels in a chart:
i. First, you need to have a chart in your Excel spreadsheet. If you don't already
have one, create a chart by selecting the data you want to chart and then go to
the "Insert" tab on the Excel ribbon.
ii. Click on the data series (bars, data points, or slices, depending on your chart
type) to which you want to add data labels. Clicking on one data point in the
series should select the entire series.
iii. After selecting the data series, you will see chart-related tabs appear in the
Excel ribbon. Look for the "Chart Elements" button (it looks like a plus sign)
usually located near the upper-right corner of the chart. Click on it.
iv. In the "Chart Elements" dropdown menu, find and select "Data Labels." This
will display a submenu with options for data labels placement.
v. In the submenu that appears, you'll have options for where you want to place
the data labels. You can choose from options like "Center," "Inside End,"
"Outside End," etc. Click on the position you prefer, and Excel will add data
labels to your chart in the selected location.
vi. After adding data labels, you may want to format them to customize their
appearance further. To do this, right-click on one of the data labels to select
them all, and then right-click again and choose "Format Data Labels." A task
pane will appear on the right side of the Excel window, allowing you to format
the data labels, change their font, add number formatting, or adjust other
properties as needed.
vii. Once you've finished formatting the data labels, you can close the Format Data
Labels pane to complete the process.
4. CHANGE CHART TYPE

To change the chart type in Microsoft Excel, follow these steps:

i. Launch Microsoft Excel and open the workbook containing the chart you want
to modify.
ii. Click on the chart you want to change. This should activate the "Chart Tools"
tab in the Excel ribbon.
iii. If the "Chart Tools" tab is not already visible in the ribbon, click on the chart
to make it appear.
iv. Once the "Chart Tools" tab is displayed, click on it to access the "Design" tab
within it. This tab contains options for modifying the chart.
v. In the "Design" tab, locate the "Change Chart Type" button.
vi. Click on the "Change Chart Type" button.
vii. A dialog box or task pane will open, displaying a variety of chart types.
viii. Choose the new chart type you want by clicking on it. You can preview how
the chart will look in the preview area of the dialog box.
ix. Depending on the new chart type you choose, you may have additional options
to customize the chart's appearance and layout. These options vary based on
the chart type.
x. Adjust any chart-specific settings as needed.
xi. Once you've selected the new chart type and made any necessary
 customizations, click the "OK" or "Finish" button in the dialog box to apply
the changes.
xii. After changing the chart type, review the chart to ensure that all data labels,
titles, and other chart elements are displayed correctly.
xiii. You may need to update or reposition these elements depending on the new
chart type.
xiv. If you're satisfied with the new chart type and its settings, make sure to save
your Excel workbook to preserve the changes.

5. SWITCH ROW/COLUMN

In Microsoft Excel, you can switch rows and columns in a chart by changing the data
source for the chart. Here are the steps to do this:
i. Launch Microsoft Excel and open the workbook that contains the chart you
want to modify.
ii. Click on the chart that you want to switch the rows and columns for. This will
activate the chart and display the "Chart Tools" tab in the Excel ribbon.
iii. Click on the "Chart Tools" tab in the ribbon. You will see a "Design" and a
"Format" tab under this. Click on the "Design" tab.
iv. In the "Data" group, you will find the "Select Data" button. Click on it. This
will open the "Select Data Source" dialog box.
 v. In the "Select Data Source" dialog box, you'll see two lists: "Legend Entries
(Series)" on the left and "Horizontal (Category) Axis Labels" on the right.
vi. To switch rows and columns, you need to swap these two lists. To do this,
click the "Switch Row/Column" button at the bottom left corner of the dialog
box. This will exchange the data for the rows and columns in your chart.
vii. After switching the rows and columns, click the "OK" button in the "Select
Data Source" dialog box to apply the changes.
viii. Your chart will now reflect the new data source with rows and columns
switched.

6. CREATE LINE CHART

Here are the steps to create a basic line chart:
i. Before you create a line chart, you need to have the data you want to visualize.
Make sure your data is organized with a column for the x-axis (categories or
time periods) and one or more columns for the y-axis (values to be plotted).
ii. Highlight the data you want to include in your line chart. This should include
both the x-axis and y-axis data.
iii. With your data selected, go to the "Insert" tab on the Excel ribbon.
iv. In the "Charts" group, click on the "Line" button. You'll see various line chart
options; you can choose the one that best fits your data. Typically, "Line" is
used for basic line charts.
v. A basic line chart will be inserted into your worksheet.
vi. You can customize it by using the "+" button on the chart to add elements like
data labels, titles, axis labels, and more.
vii. Choose from predefined chart styles to change the look of your chart. Click on
the filter icon (a funnel-shaped icon) to filter the data series shown on the
chart.
viii. Click and drag the chart to move it to the desired location on your worksheet.
You can also resize it by clicking and dragging the handles on the corners or
edges of the chart.
ix. Double-click on the chart title, x-axis label, or y-axis label to edit them. You
can also use the "Chart Elements" button mentioned earlier to access these
labels.
x. If you need to update the data in your chart, right-click on the chart and choose
"Select Data." Here, you can edit the data range for your chart.
xi. Save your Excel workbook to ensure your chart is saved along with your data.
xii. You can further format and style your chart by right-clicking on different
elements and accessing formatting options. You can change line colors,
marker styles, and more.
PRACTICAL IV – PIVOT TABLE
1. INSERT PIVOT TABLE
i. Open Excel: Launch Microsoft Excel and open the spreadsheet containing your data.
ii. Organize Data: Ensure your data is well-structured in rows and columns, with each
column having a header. This will make it easier to create a pivot table.
iii. Select Data: Click on any cell within your data range. Excel will automatically detect
the data range, or you can manually select it by dragging your cursor over the data.
iv. Insert Pivot Table: Go to the "Insert" tab in the Excel ribbon.
v. PivotTable: Click on the "PivotTable" button. A dialog box will appear.
vi. Select Range: Confirm that Excel has correctly identified your data range in the
"Table/Range" field. If not, adjust it accordingly.
vii. Choose Location: Choose where you want to place your pivot table. You can either
select an existing worksheet or create a new one.
viii. OK: Click the "OK" button. A blank pivot table will appear in your chosen location.
ix. Field List: On the right-hand side, you'll see the PivotTable Field List. This list
contains all the column headers from your data.
x. Add Fields: Drag and drop the fields you want to analyze into the rows, columns,
values, or filters area of the PivotTable Field List. The rows and columns define the
structure of your pivot table, the values area contains the data you want to analyze,
and the filters area allows you to apply filters to your data.
 xi. Customize Values: If necessary, you can customize the calculation of values. For
example, you can change the calculation from sum to average, count, etc., by clicking
the drop-down arrow next to the value field in the pivot table.
xii. Format: Format your pivot table by applying styles, colors, and fonts to make it
visually appealing and easier to read.
xiii. Refresh Data: If your source data changes, you can refresh the pivot table by right-
clicking on it and selecting "Refresh" to update the analysis.
xiv. Save: Save your Excel file to keep your pivot table and analysis for future reference.
xv. Explore Data: Now, you can interact with your pivot table to analyze and summarize
your data in various ways, such as filtering, sorting, and drilling down into details.
2. DRAG FIELDS
i. Open your Excel workbook that contains the pivot table.
ii. Ensure that you have selected a cell within the pivot table. This will activate
the "PivotTable Field List" or "PivotTable Fields" pane on the right side of the
Excel window.In the "PivotTable Field List" or "PivotTable Fields" pane, you
will see four areas: "Filters," "Columns," "Rows," and "Values."
iii. To add a field to the Columns area, click and drag the desired field from the
"PivotTable Field List" or "PivotTable Fields" pane and drop it into the
"Columns" area.
iv. To add a field to the Rows area, click and drag the desired field from the
"PivotTable Field List" or "PivotTable Fields" pane and drop it into the
"Rows" area.
v. To add a field to the Values area (to perform calculations or display data),
click and drag the desired field from the "PivotTable Field List" or
"PivotTable Fields" pane and drop it into the "Values" area. You can also
right-click on the field and choose the type of calculation you want (e.g., sum,
count, average, etc.).
vi. To add a field to the Filters area, click and drag the desired field from the
"PivotTable Field List" or "PivotTable Fields" pane and drop it into the
"Filters" area. This allows you to filter data based on the selected field.
vii. To rearrange fields within the Columns, Rows, Values, or Filters areas, simply
click and drag a field to a different position within the same area.
viii. To remove a field from the pivot table, click and drag the field out of the
Columns, Rows, Values, or Filters areas and drop it back into the "PivotTable
Field List" or "PivotTable Fields" pane.
 ix. You can also customize field settings by right-clicking on a field in the pivot
table and selecting "Field Settings." This allows you to change the name,
format, and other properties of the field.
x. After making your desired changes to the pivot table layout, Excel will
automatically update the table to reflect your changes.

3. SORT
i. Select the PivotTable: Click anywhere within the PivotTable that you want to sort.
ii. Open the Sort Dialog Box: Depending on your version of Excel, you can open the
Sort dialog box in one of the following ways:
iii. In Excel 2016 and later: Go to the "Data" tab on the Ribbon, then click the "Sort"
button in the "Sort & Filter" group.
iv. In Excel 2013 and earlier: Go to the "Data" tab on the Ribbon, then click the "Sort"
button in the "Sort & Filter" group.
v. Choose the Field to Sort By: In the Sort dialog box, you'll see a list of the fields
(columns) in your PivotTable. Select the field by which you want to sort your data
from the "Sort by" dropdown list.
vi. Select the Sorting Order: Choose either "A to Z" (ascending) or "Z to A" (descending)
in the "Order" dropdown list, depending on how you want to sort your data.
vii. Add More Levels (Optional): If you want to sort by multiple fields, you can add more
levels by clicking the "Add Level" button and selecting additional fields and sort
orders.
viii. Choose Data Options (Optional): You can further refine your sorting by selecting
additional options, such as sorting by values, cell color, font color, or cell icon. These
options are available in the "Sort On" and "Then by" dropdown lists.
ix. Apply the Sort: Once you've configured your sorting preferences, click the "OK"
button in the Sort dialog box.
x. View the Sorted PivotTable: Your PivotTable data will now be sorted according to
your selected criteria.

4. FILTER
i. Open your Excel workbook and ensure that you have a PivotTable set up.
ii. Click anywhere inside the PivotTable to select it. This will activate the "PivotTable
Analyze" or "Options" tab in the Excel ribbon, depending on your Excel version.
iii. In the "PivotTable Analyze" or "Options" tab, locate the "Filter" or "Sort" group.
iv. Click on the "Filter" button, which often looks like a small funnel or filter icon. This
will open the filter options for your PivotTable.
v. A filter drop-down arrow will appear next to each field in your PivotTable. Click the
drop-down arrow for the field you want to filter.
vi. In the drop-down menu, you will see a list of all the unique items or values in that
field. You can either:
vii. Select specific items by checking the checkboxes next to them.
viii. Use the "Select All" checkbox to select all items and then uncheck the ones you want
to exclude.
ix. Use the search box to filter items by typing keywords.
 x. After selecting your filter criteria, click the "OK" or "Apply" button in the filter menu
to apply the filter to your PivotTable.
xi. Your PivotTable will now display only the data that matches your selected filter
criteria.To remove the filter and show all the data again, click the same filter drop-
down arrow and choose "Clear Filter" or "Select All."
xii. You can also apply filters to multiple fields in your PivotTable to refine your data
further. Just repeat the above steps for each field you want to filter.

5. CHANGE SUMMARY CALCULATION

i. Open Excel: Launch Microsoft Excel and open the workbook containing the pivot
table you want to modify.
ii. Select the Pivot Table: Click anywhere inside the pivot table to select it. This action
will display the "PivotTable Field List" on the right or "PivotTable Analyze" tab in
the Excel ribbon, depending on your Excel version.
iii. Access the Field List or Analyze Tab: If you see the "PivotTable Field List" on the
right, skip to step 4. If not, go to the "PivotTable Analyze" tab in the Excel ribbon.
iv. Choose the Data Field: In the "PivotTable Field List" or "Values" area of the
"PivotTable Analyze" tab, you'll see a list of data fields used in your pivot table.
These are typically found in the "Values" area of the Field List.
v. Change the Summary Calculation:
vi. If you're using the Field List:
vii. Click the dropdown arrow next to the data field you want to change the calculation
for.
viii. Choose "Value Field Settings."
ix. In the "Value Field Settings" dialog box, you can change the calculation method by
selecting a different option from the "Summarize value field by" dropdown menu.
Options may include Sum, Count, Average, Max, Min, etc.
x. Click "OK" to apply the changes.
xi. If you're using the PivotTable Analyze tab:
xii. Click the dropdown arrow on the data field you want to modify under the "Values"
section.
xiii. Select "Value Field Settings."
xiv. In the "Value Field Settings" dialog box, change the summary calculation method by
choosing a
xv. Save Your Workbook: Don't forget to save your Excel workbook to retain the changes
you've made to the pivot table.
xvi. different option from the "Summarize value field by" dropdown menu.
xvii. Click "OK" to confirm your changes.
xviii. Refresh the Pivot Table (if necessary): If the data source has been updated or you've
made changes that affect the pivot table's data, right-click on the pivot table and select
"Refresh" to ensure the calculations are up to date.
xix. Review the Pivot Table: Examine the pivot table to ensure that the summary
calculation method has been updated according to your preferences.

PRACTICAL V – DATA VALIDATION

 1. Create Data Validation rule
i. Select the cell(s) you want to create a rule for.
ii. Select Data >Data Validation.
iii. On the Settings tab, under Allow, select an option:
a. Whole Number - to restrict the cell to accept only whole numbers.
b. Decimal - to restrict the cell to accept only decimal numbers.
c. List - to pick data from the drop-down list.
d. Date - to restrict the cell to accept only date.
e. Time - to restrict the cell to accept only time.
f. Text Length - to restrict the length of the text.
g. Custom – for custom formula.
iv. Under Data, select a condition.
v. Set the other required values based on what you chose for Allow and Data.
vi. Select the Input Message tab and customize a message users will see when entering
data.
vii. Select the Show input message when cell is selected checkbox to display the message
when the user selects or hovers over the selected cell(s).
viii. Select the Error Alert tab to customize the error message and to choose a Style.
ix. Select OK.

2. WHOLE NUMBERS
i. Select the cell(s) you want to create a rule for.
ii. Select Data >Data Validation.
iii. On the Settings tab, under Allow, select an option:
a. Whole Number - to restrict the cell to accept only whole numbers.
b. Decimal - to restrict the cell to accept only decimal numbers.
c. List - to pick data from the drop-down list.
d. Date - to restrict the cell to accept only date.
e. Time - to restrict the cell to accept only time.
f. Text Length - to restrict the length of the text.
g. Custom – for custom formula.
iv. Under Data, select a condition.
v. Set the other required values based on what you chose for Allow and Data.
vi. Select the Input Message tab and customize a message users will see when entering
data.
vii. Select the Show input message when cell is selected checkbox to display the message
when the user selects or hovers over the selected cell(s).
viii. Select the Error Alert tab to customize the error message and to choose a Style.
x. Select OK.

3. TEXT
i. Select the cell(s) you want to create a rule for.
ii. Select Data >Data Validation.
iii. On the Settings tab, under Allow, select an option:
a. Whole Number - to restrict the cell to accept only whole numbers.
b. Decimal - to restrict the cell to accept only decimal numbers.
c. List - to pick data from the drop-down list.
d. Date - to restrict the cell to accept only date.
e. Time - to restrict the cell to accept only time.
f. Text Length - to restrict the length of the text.
g. Custom – for custom formula.
 iv. Under Data, select a condition.
v. Set the other required values based on what you chose for Allow and Data.
vi. Select the Input Message tab and customize a message users will see when entering
data.
vii. Select the Show input message when cell is selected checkbox to display the message
when the user selects or hovers over the selected cell(s).
viii. Select the Error Alert tab to customize the error message and to choose a Style.
ix. Select OK.

4. DATE
i. Open Excel: Launch Microsoft Excel and open the workbook where you want to
create the data validation rule.
ii. Select a Cell: Click on the cell where you want to apply the data validation rule. This
is the cell where users will input dates.
iii. Access Data Validation: Navigate to the "Data" tab in the Excel ribbon.
iv. Choose Data Validation: Click on "Data Validation" in the "Data Tools" group. This
will open the Data Validation dialog box.
v. Select Validation Criteria: In the Data Validation dialog box:
a. In the "Allow" dropdown menu, choose "Date."
vi. Set Date Criteria: Based on your specific requirements, select the criteria for your date
validation rule. For example, you can:
 a. In the "Data" dropdown menu, choose "between" if you want to set a date
range.
b. In the "Start date" and "End date" fields, enter your desired date range using
Roman numerals.
vii. Input Message (Optional): If you want to provide an input message for users when
they select the cell, you can go to the "Input Message" tab and enter your message
without bold text using Roman numerals.
viii. Error Alert (Optional): If you want to set an error message for invalid date entries, go
to the "Error Alert" tab and enter your error message without bold text using Roman
numerals.
ix. Save and Apply: Click the "OK" button to save your data validation rule and apply it
to the selected cell.

5. ERROR ALERT
i. Open Excel and go to the worksheet where you want to apply the data validation rule.
ii. Select the cell or cells where you want to apply the data validation rule. To select
multiple cells, hold down the Ctrl key while clicking on each cell.
iii. Go to the "Data" tab in the Excel ribbon.
iv. In the "Data Tools" group, click on "Data Validation."
v. In the "Data Validation" dialog box that appears, go to the "Settings" tab.
 vi. In the "Allow" drop-down list, choose the type of data validation you want to apply.
For example, you can select "Whole number" to allow only whole numbers or "List"
to create a drop-down list of choices.
vii. Configure the validation criteria based on your selection. For example, if you selected
"Whole number," you can set the minimum and maximum values allowed.
viii. Go to the "Error Alert" tab in the same dialog box.
ix. Check the box that says "Show error alert after invalid data is entered."
x. In the "Style" drop-down list, choose the type of error alert you want to display. You
can choose from "Stop," "Warning," or "Information."
xi. In the "Title" field, enter the title for your error alert. This is the text that will appear
in the title bar of the alert box.
xii. In the "Error message" field, enter the error message you want to display when invalid
data is entered. This is the main content of the alert box.
xiii. (Optional) You can specify the type of icon to display in the error alert by selecting an
option from the "Error Alert" section.
xiv. Click the "OK" button to apply the data validation rule with the error alert.
xv. Test your data validation rule by entering data that does not meet the criteria. The
error alert will appear if the entered data is invalid.
xvi. You can further customize the error alert appearance by changing fonts, colors, and
other formatting options through the "Font" and "Border" buttons in the "Error Alert"
tab.
 6. DROP DOWN LIST
i. Open Excel and navigate to the worksheet where you want to create the drop-down
list.
ii. Click on the cell where you want the drop-down list to appear. This is the cell where
users will select options from the list.
iii. Go to the "Data" tab in the Excel ribbon.
iv. In the "Data Tools" group, click on "Data Validation."
v. In the "Data Validation" dialog box, go to the "Settings" tab.
vi. In the "Allow" dropdown menu, select "List."
vii. In the "Source" field, enter the list of items you want in the drop-down list, separated
by commas. For example, if you want the drop-down list to contain options like
"Option 1," "Option 2," and "Option 3," you would enter: Option 1, Option 2, Option
3
viii. Ensure that the "In-cell dropdown" option is checked. This will allow users to see and
select options from the drop-down list.
ix. Click the "OK" button to apply the data validation rule.
x. The selected cell will now have a small arrow next to it, indicating that it has a drop-
down list. Users can click on the cell to see and select options from the list.