A Major project report on

Data Analysis with Spreadsheets (Lab) Practical File

Submitted in partial fulfillment of the requirements for BCOM (Hons.)

programme
Guru Gobind Singh Indraprastha University, Delhi

Submitted to Submitted by
Dr. Shikha Gupta GOURAV BEHURA
(Associate Professor) Enrolment no. 01219288822

BATCH 2022-2025

Lingaya’s Lalita Devi Institute of Management & Sciences

(NAAC Accredited “A” Grade Institute & Approved u/s 2(f) of UGC
Act 1956)
Mandi Road, Mandi, New Delhi-110047

1
 CERTIFICATE

To Whom It May Concern

I ANMOL KUMAR SINGH, Enrolment No. 01219288822 from B.COM(H)-IV Sem

of the Lingayas Lalita Devi Institute of Management & Sciences, Delhi hereby declare
that the Project entitled Financial modelling using spreadsheets, is an original work and
the same has not been submitted to any other institute for the award of any other degree.

Date:

Signature of the Student

2
 ACKNOWLEDGEMENT

It is my pleasure to be indebted to various people, who directly or indirectly contributed

in the development of this work and who influenced my thinking, behavior and acts
during the course of study.

I express my sincere gratitude to the worthy Director of Lingaya”s Lalita Devi Institute
of Management & Sciences, for providing me an opportunity of doing this project under
his leadership.

I also extend my sincere indebtedness to Dr. Shikha Gupta who provided her valuable
suggestion and precious time in accomplishing my project.

I also take the opportunity to express my sincere gratitude to each and every person ,
who directly or indirectly helped me throughout the project and without anyone of them
this project would not have been possible. The immense learning from this project
would be indelible forever.

GOURAV BEHURA
01219288822

3
 Table of content

Topic Page no

CERTIFICATE 2
ACKNOWLEDGEMENT 3
TABLE OF CONTENTS 4-6

UNIT 1: - 7 – 11

INTRODUCTION TO MS EXCEL

a. What is Excel
b. Features of MS Excel
c. How to open MS Excel
d. Spreadsheet Details
e. How to open a new spreadsheet or workbook

UNIT 2: - 12 – 19

BASIC FUNCTIONS AND FORMULAES

a. LOGICAL FUNCTIONS
b. STATISTICAL FUNCTION
c. SUM FUNCTION
d. SUBTOTAL FUNCTION
e. PMT FUNCTION
f. IRR FUNCTION
g. PASTE SPECIAL

UNIT 3 :- 20 – 23

4
 BASIC COMMANDS

a. VLOOKUP FUNCTION
b. HLOOKUP FUNCTION
c. DATE AND TIME FUNCTION

UNIT 4 :- 24 – 60

ADVANCED FUNCTIONS

a. SHOPPING CART
b. DROP DOWN MENU
c. DATA VALIDATION
d. CONDITIONAL FORMATTING
e. SCENARIO MANAGER
f. GOAL SEEK
g. DATA FILTERING
h. DATA ANALYSIS

UNIT 5 :- 61 – 66

DATA TABLE

a. PIVOT TABLE
b. PIVO CHART

UNIT 6 :- 67 – 74

BARS AND CHARTS

UNIT 7 :- 75 – 78

SHORTCUT KEYS

UNIT 8 :- 79 – 83

5
 TYPES OF ERRORS

a. #DIV/0 ERROR
b. #N/A ERROR
c. #NAME? ERROR
d. #NULL! ERROR
e. #NUM! ERROR
f. #REF! ERROR
g. #VALUE! ERROR
h. ###### ERROR
i. CIRCULAR REFERENCE ERROR

6
 INTRODUCTION TO MS EXCEL
1.1 What is Excel ?

It is a spreadsheet program developed by Microsoft. Excel organizes data

in columns and rows and allows you to do mathematical functions. It runs
on Windows, macOS, Android and iOS.

The first version was released in 1985 and has gone through several
changes over the years. However, the main functionality mostly remains
the same.

Excel is typically used for:

 Analysis
 Data entry
 Data management
 Accounting
 Budgeting
 Data analysis
 Visuals and graphs
 Programming
 Financial modeling

a. Features of MS Excel
 It is the most popular spreadsheet program in the world
 It is easy to learn and to get started.
 The skill ceiling is high, which means that you can do more advanced
things as you become better
 It can be used with both work and in everyday life, such as to create a
family budget

7
 It has a huge community support
 It is continuously supported by Microsoft
 Templates and frameworks can be reused by yourself and others,
lowering creation costs.
 There are 10,48,576 no. of rows ( horizontal lines) and 16,384 no. of
columns ( vertical lines ) present in excel.

b. How to open MS Excel ?

To open MS Excel from the Start menu, select All Programs » Microsoft
Office » Microsoft Office Excel

c. Spreadsheet Details

8
 1. TitleBar: The title bar displays the name of the spreadsheet and
application.

2. Toolbar: It displays all the options or commands available in Excel for

use.

3. NameBox: It displays the address of the current or active cell.

4. Formula Bar: It is used to display the data entered by us in the active

cell. Also, this bar is used to apply formulas to the data of the spreadsheet.

5. Column Headings: Every excel spreadsheet contains 256 columns and

each column present in the spreadsheet is named by letters or a
combination of letters.

6. Row Headings: Every excel spreadsheet contains 65,536 rows and each
row present in the spreadsheet is named by a number.

7. Cell: In a spreadsheet, everything like a numeric value, functions,

expressions, etc., is recorded in the cell. Or we can say that an intersection
of rows and columns is known as a cell. Every cell has its own name or
address according to its column and rows and when the cursor is present
on the first cell then that cell is known as an active cell.

8. Cell referring: A cell reference, also known as a cell address, is a way

for describing a cell on a worksheet that combines a column letter and a
row number. We can refer to any cell on the worksheet using cell
references (in excel formulae). As shown in the above image the cell in
column A and row 1 is referred to as A1. Such notations can be used in

9
 any formula or to duplicate the value of one cell to another (by using =
A1).

9. Navigation buttons: A spreadsheet contains first, previous, next, and last

navigation buttons. These buttons are used to move from one worksheet
to another workbook.

10. Sheet tabs: As we know that a workbook is a collection of worksheets.

So this tab contains all the worksheets present in the workbook, by default
it contains three worksheets but you can add more according to your
requirement.

1.5 How to open a new spreadsheet or workbook ?

To create a new spreadsheet follow the following steps:

Step 1: Click on the top-left, Microsoft office button and a drop-down

menu appear.
Step 2: Now select New from the menu.

10
Step 3: After selecting the New option a New Workbook dialogue box
will appear and then in Create tab, click on the blank Document.

A new blank worksheet is created and is shown on your screen.

2. BASIC FUNCTIONS AND FORMULAES

2.1 LOGICAL FUNCTIONS

Logical functions are used to compare more than one condition or multiple
conditions. It returns the result as TRUE or FALSE by evaluating the
arguments.These functions are used for calculating the result and help to
elect any one of the given data. Based on the requirement, the contents in
the cell are evaluated using the respective logical condition. Here in this
tutorial, the types of Logical Functions used are:

2.1.1 AND FUNCTION

11
 The AND function tests single or multiple conditions. It returns the value
true if all the values evaluate to true and return false if any one of the value
evaluates to false.

Syntax

=AND (logical 1, [logical 2]...)

2.1.2 OR FUNCTION

The OR function returns the result as True if any arguments evaluate to

true and return False if all the arguments evaluate to False. It acts on
multiple testing conditions. It is combined with AND function and IF
condition based on the requirement.

Syntax

1. =OR (logical 1, [logical 2],...)

12
 2.1.3 NOT FUNCTION

NOT is one of the logical functions which return the reversed logical value.
An inbuilt function in Excel is used along with the formula based on the
requirement.

Syntax

1. =NOT (logical value)

2.1.4 IF FUNCTION

The IF function checks whether a condition is met, and returns one value
if true and another value if false.

13
2.2 STATISTICAL FUNCTION

2.2.1 AVERAGE FUNCTION

The AVERAGE() function focuses on calculating the average of the

selected range of cell values.

2.2.2 COUNT FUNCTION

The function COUNT() counts the total number of cells in a range that
contains a number. It does not include the cell, which is blank, and the
ones that hold data in any other format apart from numeric.

14
2.2.3 MIN FUNCTION

The MIN function will return the smallest numeric value within a given
set of data or an array.

2.2.4 LARGE FUNCTION

The LARGE function is similar to the MAX function but the only
difference is it returns the nth largest value within a given set of data or
an array.

15
2.2.5 SMALL FUNCTION

The SMALL function is similar to the MIN function, but the only
difference is it return nth smallest value within a given set of data or an
array.

2.2.6 MAX FUNCTION

The MAX function will return the largest numeric value within a given
set of data or an array.

16
2.3 SUM FUNCTION

The SUM() function, as the name suggests, gives the total of the selected
range of cell values. It performs the mathematical operation which is
addition.

2.4 SUBTOTAL FUNCTION

The SUBTOTAL() function returns the subtotal in a database. Depending

on what you want, you can select either average, count, sum, min, max,
min, and others.

17
2.5 PMT FUNCTIONS

The Excel PMT function is a financial function that calculates the

payment for a loan based on a constant interest rate, the number of
periods and the loan amount.

2.6. IRR FUNCTIONS

What is the IRR formula in Excel?

The Excel IRR function returns the internal rate of return for a series of
periodic cash flows represented by positive and negative numbers. In all
calculations, it's implicitly assumed that: There are equal time intervals
between all cash flows. All cash flows occur at the end of a period

18
2.7 PASTE SPECIAL

What is the use of Paste Special?

When you copy text that has different formatting into an Office program,
the program, such as PowerPoint or Word, automatically reformats that
text to match the text of the destination. However, you can use Paste
Special to maintain the original formatting or paste it as a link or a
picture.

Click the first cell in the area where you want to paste what you
copied. On the Home tab, under Edit, click Paste, and then click Paste
Special. Paste all cell contents and formatting, including linked data.
Paste only the formulas as entered in the formula bar.

3. BASIC COMMANDS

3.1.1 VLOOKUP FUNCTION

The VLOOKUP function is used when we have a set of vertical data. It

allows us to search a range of data references with columns, and retrieves
the right information we are looking for.

19
 Formula :-

=VLOOKUP(lookup value, range containing the lookup value, the

column number in the range containing the return value, Approximate
match (TRUE) or Exact match (FALSE))

The arguments of the VLOOKUP formula are,

 lookup_value: The value for which we are trying to retrieve the result
from the table_array (2nd argument). It is a mandatory argument.
 table_array: It will be either range or table_array where we search for
the lookup_value. It is a mandatory argument.
 col_index_num: In a given table_array, it is the column we are looking
for the result. It is a mandatory argument.
 [range_lookup]: In this optional argument, we need to specify the kind
of match we need:
o 0 or FALSE – It will search for the exact match of the lookup_value in
the table_array. If nothing is specified, 1 or TRUE will be the default
mode.
o 1 or TRUE – It will search for the approximate match of
the lookup_value in the table_array

20
 3.1.2 HLOOKUP

The HLOOKUP function is used when we have a set of horizontal data. It

allows us to search a range of data references with rows, and retrieves the
right information we are looking for.

Formula:-

=HLOOKUP(lookup_value, table_array, row_index_num,

[range_lookup])

The arguments of the HLOOKUP formula are,

 lookup_value: The value for which we are trying to retrieve the result
from the table_array (2nd argument). It is a mandatory argument.
 table_array: It will be either range or table_array where we search for
the lookup_value. It is a mandatory argument.

21
 row_index_num: In a given table_array, it isthe row we are looking for
the result. It is a mandatory argument.
 [range_lookup]: In this optional argument, we need to specify the kind
of match we need:
o 0 or FALSE – It will search for the exact match of the lookup_value in
the table_array. If nothing is specified, 1 or TRUE will be the default
mode.
o 1 or TRUE – It will search for the approximate match of
the lookup_value in the table_array.

3.2 DATE AND TIME FUNCTION

3.2.1 DATE

DATE(year, month, day) returns a serial number of a date based on the

year, month and day values that you specify.

22
3.2.2 TODAY

The TODAY() function returns today's date, exactly as its name suggests.

3.2.3 NOW

NOW() function returns the current date and time. As well as TODAY, it
does not have any arguments. If you wish to display today's date and
current time in your worksheet.

23
 4. ADVANCED FUNCTIONS

4.1 SHOPPING CART

Shopping Cart – Also sometimes called a basket, it's the listing of products
your customers wants to buy at checkout. As we have data on the field
“Item” name, customers can now pick an item from the drop down. Soon
as they select an item, using Vlookup, the workbook identifies the
corresponding Item description and per item price from the “inventory”.
This information is displayed on the “Shopping Cart”. When the field
“Quantity” is updated, the total price gets populated with correct values.

1. Enter the data.

2. Multiply the two cells under QUANTITY and PRICE i.e. G3*H3 in
the cell I3.

24
3. Now write the formula of Vlookup in the cell H4 under the Price.

25
 As now the Shopping Cart is ready to use .

4.2 DROP DOWN MENU

Drop-down list work more efficiently in worksheets by using drop-down
lists in cells. Drop-downs allow people to pick an item from a list that
you create.

1. Select the cell in the worksheet where you want the drop-down list.
2. Go to the Data tab on the Ribbon, then Data Validation .

26
3. On the Settings tab, in the Allow box, click List .
4. Click in the Source box, then select your list range. We put ours on a
sheet, in range C2:C5

Now the drop down menu has added.

Here is another example of creating drop down menu in which we are

selecting the all the items listed in the sheet.

1. Go to the Data> Data Validation

2. Select Data Validation then Data Validation Dialog box will appear.
27
3. Now go to the settings in the appeared dialog box and select OPTION-
“LIST” in Allow.

4. Then select the cells (A2:A23) in the SOURCE option

5. Then Click OK.

6. Now the dialog box appeared as shown in this figure.

28
 4.3 DATA VALIDATION

Data validation in Excel is a technique that restricts user input in

a worksheet. It is often used to limit user entry.
Settings Tab
The settings tab is where you enter the validation criteria. There are eight
options available to validate for user input:

 Any Value - It removes any existing data validation.

 Whole Number - It allows only whole numbers. For example, you can
specify that the user must enter the number between 0 to 30.

 Decimal - The user must enter a number with decimal values.

 List - The user will have to create a drop-down list to choose from.

 Date - The user will have to enter the date format.

 Time - The user should enter a time.

 Text Length - It validates input based on the length of the data.

 Custom - It validates the user input using a custom formula.

Input Message TabYou can set the input message to explain what data is
allowed in a given cell. This tab is optional.

29
 Check the 'show input message when the cell is selected'.

 Enter a title.

 Enter an Input message.

Error Alert Tab

You can show the error message if the user tries to enter the invalid data.

On the error message tab:

 Check the ‘Show error alert after invalid data is entered’ box.

 Enter a title.

 Enter an error message.

30
 Now, when you try to enter the value beyond the range, you will get an
error message.

Now that we are familiar with the basic concepts, let’s look at a step-by-
step process to implement data validation in excel.

 How to Validate Data in Excel?

Step 1 - Select The Cell For Validation

31
Select the cell you want to validate. Go to the Data tab > Data tools, and
click on the Data Validation button.

A data validation dialogue box will appear having 3 tabs - Settings, Input
Message, and Error Alerts.

Step 2 - Specify Validation Criteria

On the settings tab, specify your validation criteria.

32
Step 3 - Under Allow, Select The Criteria

Under Allow, select an option from Whole Number, Decimal, List, Date,
Time, Text Length, and Custom.

Step 4 - Select Condition

Under Data, select a condition and set required values based on what you
choose for Allow and Data.

Step 5 - Input Message

You can enter the input message if you want. This step is optional.

Step 6 - Custom Error Message

33
 You can also set your custom error message. This step is optional.

Step 7 - Click Ok

Click OK. Now, if you try entering a value outside the specified range
(10, 50), it will result in an error.

4.4 CONDITIONAL FORMATTING

Apply conditional formatting based on text in a cell :-

1. Select the cells you want to apply conditional formatting to. Click the
first cell in the range, and then drag to the last cell.

2. Click HOME > Conditional Formatting > Highlight Cells Rules > Text
that Contains. In the Text that Contains box, on the left, enter the text you
want highlighted.

34
3. Select the color format for the text, and click OK.

4. Similarly do for greater and lesser values.

35
4.5 SCENARIO MANAGER

A Scenario is a set of values that Excel saves and can substitute

automatically on your worksheet. You can create and save different
groups of values as scenarios and then switch between these scenarios to
view the different results.

If several people have specific information that you want to use in

scenarios, you can collect the information in separate workbooks, and
then merge the scenarios from the different workbooks into one.

After you have all the scenarios you need, you can create a scenario
summary report that incorporates information from all the scenarios.

36
 Scenarios are managed with the Scenario Manager wizard from the What-
If Analysis group on the Data tab.

1. Assume that your worst case budget scenario is Gross Revenue of 50,000
and Costs of Goods Sold of 13,200, leaving $36,800 in Gross Profit. To
define this set of values as a scenario, you first enter the values in a
worksheet, as shown in the following illustration:

2. You then use the Scenario Manager dialog to save these values as a
scenario. Goto the Data tab > What-If Analysis > Scenario Manager >
Add.

3. In the Scenario name dialog, name the scenario Worst , and specify that
cells B2 and B3 are the values that change between scenarios.

37
4. Best Case budget scenario is Gross Revenue of $150,000 and Costs of
Goods Sold of $26,000, leaving $124,000 in Gross Profit. To define this
set of values as a scenario, you create another scenario, name it Best
Case.

38
5. Add the Scenario name dialog, name the scenario Best , and specify that
cells B8 and B9 are the values that change between scenarios.

Scenario summary reports

1. To compare several scenarios, you can create a report that summarizes
them on the same page. The report can list the scenarios side by side or
present them in a PivotTable report.

39
2. A scenario summary report based on the preceding two example
scenarios would look something like the following:

40
 4.6 GOAL SEEK

Goal seeking is the process of finding the correct input value when only
the output is known. The function of goal seeking can be built into
different kinds of computer software programs like Microsoft Excel. Goal
seeking is finding the correct input when only the output is known.

1. Open a new, blank worksheet.

2. First, add some labels in the first column to make it easier to read the
worksheet.
a. In cell A1, type Loan Amount.
b. In cell A2, type Term in Months.
c. In cell A3, type Interest Rate.
d. In cell A4, type Payment.
3. Next, add the values that you know.

41
4. In cell B4, type =PMT(B2/12,B3,B1). This formula calculates the
payment amount.

 Use Goal Seek to determine the interest rate

42
1. On the Data tab, in the Data Tools group, click What-If Analysis, and
then click Goal Seek.

2. In the Set cell box, enter the reference for the cell that contains the
formula that you want to resolve. In the example, this reference is cell B4.

3. In the To value box, type the formula result that you want. In the
example, this is -9000. Note that this number is negative because it
represents a payment.

43
4. In the By changing cell box, enter the reference for the cell that contains
the value that you want to adjust. In the example, this reference is cell B3.

5. Click OK.

44
 4.7 DATA FILTERING

Use filters to temporarily hide some of the data in a table, so you can
focus on the data you want to see.

Filter a range of data

1. Select any cell within the range.

2. Select Data > Filter.

3. Select the column header arrow .

45
4. Select Text Filters or Number Filters, and then select a comparison,
like Between.

5. Enter the filter criteria and select OK.

Filter data in a table

46
 4.8 When you Create and format tables, filter controls are
automatically added to the table headers.

STEPS:-

1. Select the column header arrow for the column you want to filter.
2. Uncheck (Select All) and select the boxes you want to show.

4.Click OK.

47
 How to Insert a Picture Into a Cell Using VBA in Excel
In order to create a macro to insert pictures into Excel cells using VBA,
you actually don't need any advanced Visual Basic knowledge. All you
need to do is enable Developer tools, create the macro and paste the right
code.
Steps to Create a macro in Excel using VBA
1. Enabling Developer Tools
In order to use VBA in Excel, you need to enable Developer tools in
Excel. This will enable the Developer tab in the ribbon, which is disabled
by default.
1. Open Excel.

2. Go to the File menu.

3. Click on Options at the bottom of the screen. This will open the Excel
Options window.

4. In Excel Options, go to the Customize Ribbon tab.

5. Under Main Tabs, check Developer.

48
 Creating the Macro and Inserting the Code

Now it's time to get down to creating the macro. Alternatively, you could
also create a button using VBA for this task, but we're going to stick with
macros.
1. In Excel, go to the Developer tab.

2. In the Code section, select Macros.

3. In the new window, enter a name for your macro under Macro name.
We're going to use insertPhotoMacro.

4. Click Create.

49
Once you click Create, the VBA window will open up and display the
code for your macro. Right now, the code will consist of two lines:
A Sub to initiate the macro, and an End Sub to end it.

50
Let's add some code to this macro. Add the following code between the
two lines:
Dim photoNameAndPath As Variant
Dim photo As Picture

51
photoNameAndPath = Application.GetOpenFilename(Title:="Select
Photo to Insert")
If photoNameAndPath = False Then Exit Sub
Set photo = ActiveSheet.Pictures.Insert(photoNameAndPath)
With photo
.Left = ActiveSheet.Range("A1").Left
.Top = ActiveSheet.Range("A1").Top
.Width = ActiveSheet.Range("A1").Width
.Height = ActiveSheet.Range("A1").Height
.Placement = 1

End With
Your final code should be something like the below:

52
 You don't need to worry about saving your progress. Every change you
make in VBA is instantly saved.
Now it's time to see the code at work.
1. Close the VBA window.

2. Go to the Developer tab in Excel.

3. Select Macros from the Code section.

4. Highlight the macro you just created.

5. Click Run.

53
Now a prompt will pop open asking you to locate the image file you want
to insert. Select your image and then click Open. You should now see
your photo in the A1 cell!

Notice that the inserted picture is shrunk to fit cell A1. You can alter this,
and also change the code to insert the picture in other cells or even a
range of cells. In the next section, we're going to break down the code
and explain the parameters at work.
3. Breaking Down the Code
To make this VBA code work for you the way you want, you need to
understand it. Once you do, you can alter the code to insert photos in any

54
cell in any size. We're going to go through the code bit by bit to make it
easier to understand.
Sub insertPhotoMacro()
Dim photoNameAndPath As Variant
Dim photo As Picture
photoNameAndPath = Application.GetOpenFilename(Title:="Select
Photo to Insert")
If photoNameAndPath = False Then Exit Sub
Set photo = ActiveSheet.Pictures.Insert(photoNameAndPath)
With photo
.Left = ActiveSheet.Range("A1").Left
.Top = ActiveSheet.Range("A1").Top
.Width = ActiveSheet.Range("A1").Width
.Height = ActiveSheet.Range("A1").Height
.Placement = 1

End With
End Sub
Once the code starts, we use the Dim statement to determine the
variable's type. We've got two variables
here: photoNameAndPath and photo itself. We've determined the former
as a Variant and the latter as a Photo.
From there, the photoNameAndPath variable runs and it opens an
application to get the whereabouts of the picture file. This is done
through Application.GetOpenFileName. The Title parameter is optional,
and the content in it is displayed as the window name.
Using If photoNameAndPath = False Then Exit Sub, we specify that if an
invalid or blank address is given, the process should be terminated.
However, if a proper file is fed, then Set photo =

55
ActiveSheet.Pictures.Insert(photoNameAndPath) indicates that the
picture should be set as the photo variable we defined earlier, and it
should be inserted into the active spreadsheet.
Finally using With photo and the five lines following it, we elaborate on
the image's positioning. .Left and .Top indicate the start locations,
while .Width and .Height indicate the end locations. If you're looking to
insert the image in other cells, or in a range, then these are the lines you
should alter.

.Placement indicates whether the picture should be sized with the cells or
inserted in free form. Setting it to 1 will size it with the cells.
Ultimately, we use End With and then End Sub to close the macro. Note
that you can change the photoNameAndPath and photo variables to
whatever other name you like. Just remember to keep the names
consistent throughout the code.

4.9 DATA ANALYSIS

How to activate data analytics tool pack ?

Step 1.Click the File tab, click Options, and then click the Add-Ins category.

56
Step 2.In the Manage box, select Excel Add-ins and then click Go. ...

Step 3.In the Add-Ins box, check the Analysis ToolPack check box, and then click
OK.

57
5.DATA TABLE

5.1 PIVOT TABLE

Pivot tables are among the most useful and powerful features in Excel.
We use them in summarizing the data stored in a table. They organize and
rearrange statistics (or "pivot") to

draw attention to the valuable facts. You can take an extremely large data
set and see the relevant information you need in a clean, concise,
manageable way.

Create Pivot Table

For creating pivot table , you need to follow the following steps:

58
Step 1. Put your data in unique columns. This is the most important steps
as if your data is not in correct manner you will nit get the desired results
.

Step 2. Insert a pivot table: Go to the "Insert" tab in the Excel ribbon and
click on the "PivotTable" button. This will open the "Create PivotTable"
dialog box.

59
Srep 3. Choose your data source: In the dialog box, Excel will
automatically detect the range of your selected data. Ensure that the
correct range is displayed under "Select a table or range."
Click ok. Then it will create a pivot table worksheet.

Here are some questions related to the data given above–

60
Question 1.- How many items were sold by each company in a year

Question 2- show the average discount percentage per sales person.apply

the external filter that will show only Sales made in Columbia . sort the
result in ascending order . who is the best sales agent?

61
Question 3- create a report detailing the $ sales by region , country &
store, in the following format:

Region country store sale.

5.2 PIVOT CHART

Pivot chart in excel is an in-built program tool that helps you summarize
selected rows and columns of data in a spreadsheet. The visual
representation of a PivotTable or any tabular data helps summarize and
analyze the datasets, patterns, and trends.

62
 n the above image , you can see the pivot chart which is made from the
data in the pivot table

Here , we have only selected country and item column and only 6
countries are choosen to be shown in the graph.

6. BARS AND CHARTS

In Microsoft Excel, charts are used to make a graphical representation of

any set of data. A chart is a visual representation of data, in which the data
is represented by symbols such as bars in a bar chart or lines in a line chart.
A vertical bar graph in Excel is a separate chart type, known as a column
ar chart. Along with pie charts, bar graphs are one of the most commonly
used chart types. They are simple to make and easy to understand.

Excel provides you different types of charts that suit your purpose. Based
on the type of data, you can create a chart. You can also change the chart
type later.

Excel offers the following major chart types −

 Column Chart

 Line Chart

 Pie Chart

 Doughnut Chart

 Bar Chart

 Area Chart

 XY (Scatter) Chart

63
 Bubble Chart

 Stock Chart

 Surface Chart

 Radar Chart

 Combo Chart

We can find the Charts group under the Insert tab on the Ribbon.

6.1 STEPS TO MAKE PIE CHART IN MS EXCEL

Step 1: Select the required data in which you have to insert pie chart.

Step 2: Go to the insert tab on the ribbon.

Step 3: Go to the charts options in the ribbon.

Step 4: Click the arrow to see the different types of pie chart available in
your document.

Step 5: Select the desired pie chart.

64
6.2 STEPS TO MAKE CLUSTERED BAR CHART

Step 1: Select the required data in which we have to insert the bar chart.

Step 2: On the insert on the ribbon display options icon on the top right
corner of your document.

Step 3: Select 2D bar chart.

Step 4: Click on the clustered bar.

65
6.3 STEPS TO MAKE CLUSTERED COLUMN

Step 1: Select the required data in which we have to insert the bar chart.

Step 2: On the insert on the ribbon display options icon on the top right
corner of your document.

Step 3: Select 2D column.

Step 4: Click on the clustered Column.

66
6.4 STEPS TO MAKE STACKED BAR CHART

Step 1: Select the required data in which we have to insert the bar chart.

Step 2: On the insert on the ribbon display options icon on the top right
corner of your document.

Step 3: Select 2D bar chart.

Step 4: Click on the stacked bar.

67
6.5 STEPS TO MAKE 100% STACKED BAR CHART

Step 1: Select the required data in which we have to insert the bar chart.

Step 2: On the insert on the ribbon display options icon on the top right
corner of your document.

Step 3: Select 2D bar chart.

Step 4: Click on the 100% stacked bar.

68
6.6 STEPS TO MAKE STACKED COLUMN

Step 1: Select the required data in which we have to insert the bar chart.

Step 2: On the insert on the ribbon display options icon on the top right
corner of your document.

Step 3: Select 2D column.

Step 4: Click on the stacked column.

69
 6.7 STEPS TO MAKE 100% STACKED COLUMN

Step 1: Select the required data in which we have to insert the bar chart.

Step 2: On the insert on the ribbon display options icon on the top right
corner of your document.

Step 3: Select 2D column.

Step 4: Click on the 100% stacked column.

7. SHORTCUT KEYS

Close a workbook. Ctrl+W

Open a workbook. Ctrl+O

Go to the Home tab. Alt+H

Save a workbook. Ctrl+S

70
Copy selection. Ctrl+C

Paste selection. Ctrl+V

Undo recent action. Ctrl+Z

Remove cell contents. Delete

Choose a fill color. Alt+H, H

Cut selection. Ctrl+X

Go to the Insert tab. Alt+N

Apply bold formatting. Ctrl+B

Center align cell contents. Alt+H, A, C

Go to the Page Layout tab. Alt+P

Go to the Data tab. Alt+A

Go to the View tab. Alt+W

Open the context menu. Shift+F10 or

Windows Menu key

Add borders. Alt+H, B

Delete column. Alt+H, D, C

Go to the Formula tab. Alt+M

Hide the selected rows. Ctrl+9

Hide the selected columns. Ctrl+0

71
7.1. ADVANCE SHORTCUT
KEYS FOR EXCEL

ALT+' This shortcut will open

(apostrophe) the formatting Style
dialog window.

2 CTRL+1 This shortcut will open the

Format Cells dialog window.

3 CTRL+SHFT+~ This shortcut will apply the

General number format to the
specified cell(s).

4 CTRL+SHFT+$ This shortcut will apply the

Currency format to your
specified cell(s) with two
decimal places (negative value
are put in parentheses).

5 CTRL+SHFT+% This shortcut will apply the

Percentage format to the
specified cell(s) with no decimal
places.

6 CTRL+SHFT+^ This shortcut will apply the

Exponential number format to
the selected cell(s) with two
decimal places.

72
7 CTRL+SHFT+# This shortcut will apply the Date
format with the dd/mm/yyyy
(day, month, and year).

8 CTRL+SHFT+@ This shortcut will apply the

Time format to the selected
cell(s) with the hour and minute,
and AM or PM.

9 CTRL+SHFT+! This shortcut will implement the

Number format to the selected
cell(s) with two decimal places,
thousands separator, and minus
sign (-) for negative values.

10 CTRL+B This shortcut will apply or

remove the bold formatting from
the selected cell(s).

11 CTRL+I This shortcut will apply or

remove italic formatting from
the selected cell(s).

12 CTRL+U This shortcut will apply or

remove underlining from the
selected cell(s).

13 CTRL+5 This shortcut will apply or

remove strikethrough from the
selected cell(s).

73
14 CTRL+9 This shortcut will hide the
selected rows from your Excel
worksheet.

15 CTRL+SHFT+( This shortcut will unhide any

(opening parenthesis) hidden rows within the selected
cell(s).

16 CTRL+0 (zero) This shortcut will hide the

selected columns from your
Excel worksheet.

17 CTRL+SHFT+) (closing This shortcut will unhide any

parenthesis) hidden columns within the
selection.

18 CTRL+SHFT+& This shortcut will implement the

outline border to the selected
cells.

19 CTRL+SHFT+_ This shortcut will exclude the

outline border from the selected
cells.

74
8. TYPES OF ERRORS

MS Excel is popular for only its most useful automatic calculation

feature, which we achieve by applying various functions and formulas.
But while using formulas in Excel cell, we get multiple types of errors.

8.1 – #DIV/0 Error

#DIV/0! Error is received when we work with a spreadsheet formula,

which divides two values in a formula and the divisor (the number being
divided by) is zero. It stands for divide by zero error.

8.2 – #N/A Error

This error means “no value available” or “not available.” It indicates that
the formula cannot find the value that we suppose it may return.

For example, using Excel’s VLOOKUP, HLOOKUP, MATCH,

and LOOKUP functions, we may get this error if we do not find
referenced value in the source data as an argument.

 When the source data and the lookup value are not of the same data type:

75
8.3 – #NAME? Error

This error is displayed when we usually misspell the function name.

8.4 – #NULL! Error

This error is usually displayed when cell references are not specified
correctly.
We get this error when we do not use the space character appropriately.
The space character is called the “intersect operator,” which specifies the
range that intersects each other at any cell.

76
In the below image, we have used the space character, but the ranges
A2:A12 and B2:B12 are not intersecting; that is why this error is
displayed.

8.5 – #NUM! Error

This error is usually displayed when a number for any function argument
is found invalid.

8.6 – #REF! Error

This error stands for reference error. This error usually comes when

77
1. We accidentally deleted the cell which we referenced in the formula.
2. We cut and paste the referenced cell in different locations.

As we deleted cell B7, then cell C7 shifted left to take the place of B7,
and we got a reference error in the formula as we deleted one of the
referenced cells of the formula.

8.7 – #VALUE! Error

This error comes when we use the wrong data type for a function or
formula. For example, we can add only numbers. But if we use any other
data type like text, this error will be displayed.

78
8.8 – ###### Error

This error is displayed when the column width in excel is not enough to
show the stored value in the cell.
In the below image, dates and times are written in the cells. But, as
column width is not enough, ##### is being displayed.

79
8.9 – Circular Reference Error

This type of error comes when we reference the same cell in which we
are writing the function or formula.

The above image shows that we have a sum of 0 as we have referenced

B4 in the B4 cell itself for calculation.

Whenever we create this type of circular reference in excel, Excel alerts

us about the same too.

80