Introduction to Microsoft Excel 2016

Microsoft Excel is a versatile spreadsheet software developed by Microsoft, used globally

for organizing data and performing financial analysis. Its key features include data entry,
management, financial modeling, and charting. Excel is extensively used in finance and
accounting for budgeting, forecasting, and analysis due to its robustness and flexibility. It
supports various functions, formulas, and shortcuts to enhance productivity and efficiency.
Excel is a vital tool for professionals in finance, accounting, and other fields requiring data
organization and analysis.

Excel is a useful tool for business, scientific and statistical analysis. It can be
used to:
 Manage and organize data.
 Analyze data.
 Sort Data.
 Apply Filters to data.
 Create visual representation of data using Charts.
 The Excel 2016 File Extension Name is .XLSX.
To Open Excel 2016 , Follow some Steps :
1. Goto Window Search Box.
2. Then Write Excel 2016.
3. Click on Excel 2016 Application.
4. Click on Blank Workbook.
 Screen Elements

The Ribbon
The Ribbon is designed to help you quickly find the commands that you need to complete a
task. Ribbons are divided into logical groups called "Tabs." Each tab has its own set of
unique functions to perform. For example, there are various tabs – "Home," "Insert," "Page
Layout," "Formulas," "Date," "Review," and "View."
To reduce clutter, some Tabs are shown only when needed. For example, the Picture Tools
tab is shown only when a picture is selected.

 Home Tab :-The Home is the most used tab; it incorporates all text and cell formatting
features such as font and paragraph changes. The Home Tab also includes basic
spreadsheet formatting elements such as text wrap, merging cells and cell style.
 Insert Tab :-The Insert tab allows you to insert a variety of items into a workbook from
pictures, clip art, and headers and footers.

 Page Layout :-Tab The Page Layout tab has commands to adjust page such as margins,
orientation and themes.

 Formulas Tab :-The Formulas tab has commands to use when creating Formulas. This
tab holds an immense function library which can assist when creating any formula or
function in your spreadsheet.

 Data Tab :-The Data tab allows you to modifying worksheets with large amounts of data
by sorting and filtering as well as analyzing and grouping data.

 Review Tab:- The Review tab allows you to correct spelling and grammar issues as well
as set up security protections. It also provides the track changes and notes feature
providing the ability to make notes and changes to someone’s workbook.

 View Tab :-The View tab allows you to change the view of your workbook including
freezing or splitting panes, viewing gridlines and hide cells.
 File Menu
Here you will find the basic commands such as open, save, print, etc.

Quick Access Toolbar

The place to keep the items that you not only need to access quickly, but want to be
immediately available regardless of which of the Ribbon's tabs you're working on. If you put
so many items on the Quick Access Toolbar that it becomes too big to fit on the title bar,
you can move it onto its own line.

Tell Me
This is a text field where you can enter words and phrases about what you want to do next
and quickly get to features you want to use or actions you want to perform. You can also
use Tell Me to find help about what you're looking for, or to use Smart Lookup to research
or define the term you entered.

Formula Bar
A place where you can enter or view formulas or text.

Expand Formula Bar Button

This button allows you to expand the formula bar. This is helpful when you have either a
long formula or large piece of text in a cell.

Worksheet Navigation
Tabs By default, every workbook starts with 1 sheet.

Insert Worksheet Button

Click the Insert New Worksheet button to insert a new worksheet in your workbook.

Vertical Scroll Bar

This bar is used to move Up & Down in a Worksheet.

Horizontal Scroll Bar

This bar is used to move Left & Right in a Worksheet.

Normal View
This is the “normal view” for working on a spreadsheet in Excel.

Page Layout View

View the document as it will appear on the printed page.

Page Break Preview

View a preview of where pages will break when the document is printed.

Zoom Level
Allows you to quickly zoom in or zoom out of the worksheet.
 Navigating in the Excel Environment
Below is a table that will assist you with navigating/moving around in the Excel
environment.
Key Description
ARROW Move one cell up, down, left, or right in a worksheet. SHIFT+ARROW KEY
KEYS extends the selection of cells by one cell.
BACKSPAC Deletes one character to the left in the Formula Bar. Also clears the
E content of the active cell. In cell editing mode, it deletes the character to
the left of the insertion point.
DELETE Removes the cell contents (data and formulas) from selected cells without
affecting cell formats or comments. In cell editing mode, it deletes the
character to the right of the insertion point.
END Moves to the cell in the lower-right corner of the window when SCROLL
LOCK is turned on. Also selects the last command on the menu when a
menu or submenu is visible. CTRL+END moves to the last cell on a
worksheet, in the lowest used row of the rightmost used column. If the
cursor is in the formula bar, CTRL+END moves the cursor to the end of the
text. CTRL+SHIFT+END extends the selection of cells to the last used cell on
the worksheet (lower-right corner). If the cursor is in the formula bar,
CTRL+SHIFT+END selects all text in the formula bar from the cursor
position to the end—this does not affect the height of the formula bar.
ENTER Completes a cell entry from the cell or the Formula Bar, and selects the cell
below (by default).
ESC Cancels an entry in the cell or Formula Bar. Closes an open menu or
submenu, dialog box, or message window.
HOME Moves to the beginning of a row in a worksheet. CTRL+HOME moves to the
beginning of a worksheet.
PAGE Moves one screen down in a worksheet.
DOWN
PAGE UP Moves one screen up in a worksheet.
SPACEBAR In a dialog box, performs the action for the selected button, or selects or
clears a check box. CTRL+SPACEBAR selects an entire column in a
worksheet. SHIFT+SPACEBAR selects an entire row in a worksheet.
CTRL+SHIFT+SPACEBAR selects the entire worksheet.
TAB Moves one cell to the right in a worksheet.
Alt + Enter Insert a New Line within Cell
F2 Enable Editing within a Cell
Shift + Tab Move One Cell to the Left

Worksheet and workbook specifications and limits

Feature Maximum limit
Sheets in a workbook Limited by available memory (default is 1 sheet)
Total number of rows and columns on 1,048,576 rows by 16,384 columns
a worksheet
Column width 255 characters
Row height 409 points
Page breaks 1,026 horizontal and vertical
Total number of characters that a cell 32,767 characters
can contain
Hyperlinks in a worksheet 65,530
Zoom range 10 percent to 400 percent
Undo levels 100
Workbook :-A file in a spreadsheet program.
Worksheet :-A smaller section of a workbook file designated by sheet tabs. A workbook can
have multiple worksheets.
Cell :-Every worksheet is made up of thousands of rectangles, which are called cells. A cell is
the intersection of a row and a column. Just click on it to select a cell.
Active Cell :-The active cell is the cell currently selected with the outline surrounding the
cell and in the lower right corner, there will be a fill handle. In this example, the active cell is
Cell C4. The active cell can be a blank cell where data will be inputted when a user is typing.
Columns:-Columns are identified by letters (A, B, C). There are 16,384 columns in Excel
2016.Every column has its own alphabet for identity, from A to XFD.
Rows:-The name assigned to each horizontal line in a spreadsheet. Rows are identified by
numbers (1, 2, 3). There are 1,048,576 rows in Excel 2016.

Highlighting/Selecting Areas Using the Mouse

Select cells:-The selected cell has a dark border around it.

Moves a cell’s contents:

Activate the Autofill feature:

To Select a Column:Click on the column letter.

To Select a Row:Click on the row number.
To Select the Entire Worksheet:Click above row 1 and to the left of column A or hit CTRL+ A
on the keyboard.
 Autofill
Frequently, it is necessary to enter lists of information. For example, column headings are
often the months of the year or the days of the week. To simplify entering repetitive or
sequential lists of information, Excel has a tool called Autofill. This tool allows
preprogrammed lists, as well as custom lists, to be easily added to a spreadsheet.

Practical
 Week Days Name to Display in Series.(Ex- Sun, Mon)
 Month’s Name to Display in Series.(Ex- Jan, Feb)
 Display 1oth Natural Numbers In Series(Ex- 1, 2)
To Insert Sheet Rows And Column, follow some Steps :
1. First select one row and one column.
2. Then go to Home Tab.
3. Click on Insert Option under Cells Gallery.
4. Then Click on Insert Sheet Row and Column.

To Insert Cells, follow some Steps :

1. First select one cell or cells.
2. Then go to Home Tab.
3. Click on Insert Option under Cells Gallery.
4. Then Click on Insert Cells.

To Insert Sheet, follow some Steps :

1. Then go to Home Tab.
2. Click on Insert Option under Cells Gallery.
3. Then Click on Insert Sheet.
Or
4. Click on New Sheet under Insert Worksheet Button.

To Delete Sheet Rows And Column,follow someSteps :

1. Select any desire cell
2. Then go to Home Tab
3. Click on Delete Option Under Cells Gallery
4. Click on Delete Sheet Row And Column

To Delete Cell,follow some Steps :

1. Select any desire cell or cells.
2. Then go to Home Tab.
3. Click on Delete Option Under Cells Gallery.
4. Click on Delete Cell.
To Delete Sheet,follow some Steps :
1. Select any desire sheet.
2. Then go to Home Tab.
3. Click on Delete Option Under Cells Gallery.
4. Click on Delete Sheet.

To Merge & Centre on your desire cells, follow some Steps :

1. Select any desire cells.
2. Then go to Home Tab.
3. Click on Merge & Centre Option under Alignment Gallery.

4. Click on Merge & Centre.

Editing Cells
Excel provides a major enhancement over earlier spreadsheet products in its ability to edit
cells easily. There are various methods for cell editing, including double-clicking in the cell,
using the F2 key, and typing in the formula bar.

To Edit a Cell in the Worksheet:

1) Position yourself in the cell you would like to edit
2) Press the F2 key on the keyboard or double-click in the cell
3) Use the backspace or delete keys to edit the cell
4) Press Enter when you have finished editing the cell
~OR~
5) Click in the cell you would like to edit
6) Click in the formula bar and make any necessary changes
7) Press Enter when you have finished editing the cell

Saving a Worksheet
When working in Excel it is necessary to save your files. It is also very important that while
working, your file is saved frequently. When naming a file, you are restricted to 255
characters. Avoid most punctuation; spaces are acceptable.

To Save the File:

1) Click on the File tab
2) Click Save
3) Choose the destination
4) Type a file name
5) Click Save
Clearing Cells
As we begin to look at formatting, it is important to understand what makes up the
contents of a cell. There are three distinct items that can be in a cell:
Contents
Formats
Comments
These allow items to be formatted properly, even if the values change. However, when
trying to delete or clear a cell, it can be a bit tricky. Excel stores formats and contents
separately, simply deleting the contents does not delete the format.

To Clear a Cell Format:

1)Click in the cell that contains formatting.

2)Click the drop-down arrow next to the Clear button on the Home tab in the Editing group.
3) Click Clear Formats.

Display Corner Numbers

Display Corner Number
35 40
32
23 42
Total

Total Formula
=A2+C2+B3+A4+C4
Display Distance and Time Formula
 Time (hrs) Rate (mph) Distance
1.2 4.5
1.3 4.4
1.28 4.6
4.5 6.1
4.4 7
4.6 5.3

Apply :- =A2*B2
Time Formula Syntax :- =Distance/Rate
Apply :- =C5/B5

Formatting Values
Applying formats to any cell(s) can be done either using the Font, Alignment and Number
groups or using the dialog box which will include all the formatting options.

To Apply the Currency Format:

1) Highlight the cell(s).
2) Click on the Currency Style button on the Home tab in the Number group
3) If necessary, click on the Increase or Decrease Decimal button on the Number group.
To Apply the Comma Format:
1) Highlight cells.
2) Click on the Comma Style button on the Number group.
3) If necessary, click on the Increase or Decrease Decimal button on the Number group .
Formatting Labels
A Label, or text formatting is applied virtually the same way it is done in word processing
programs.
To Format the Title Labels:
1) Highlight the cell(s)
2) Select a font from the Font group
3) Select a point size from the Font group

Using the Dialog Box:

1)Highlight the cells.
2)Click on the arrow in the corner of one of the formatting groups (Font, Alignment,
Number) to open the Format Cells dialog box and click on one of the tabs.

Cell Reference
An Excel cell reference, also known as a cell address, is a mechanism that defines a cell on
a worksheet by combining a column letter and a row number. We can refer to any cell (in
Excel formulas) in the worksheet by using the cell references.
For example:

Here we refer to the cell in column A & row 2 by A2 & cell in column A & row 5 by A5. You
can make use of such notations in any of the formulas or copy the value of one cell to
another cell (by using = A2 or = A5).
 Types of Cell Reference in Excel
Understanding various cell references primarily makes it easier for us to use Excel
formulas and avoid unexpected formula errors. When copying and pasting Excel formulas,
this is quite useful. Based on various use situations, Excel offers three main types of cell
references, including:
1. Relative Cell Reference
2. Absolute Cell Reference
3. Mixed Cell Reference
1. Relative Cell References
In Excel, a relative reference is a form of a cell reference. By default, all cell references are
relative references. Relative references are changed when copied across different cells
based on the relative positions of rows and columns. For example, suppose we copy the
formula =B1*C1 from row 1 to row 2, the formula will become=B2*C2. When we have to
repeat a calculation across numerous rows or columns, relative references are extremely
useful.
2. Absolute Cell References
In Excel, an absolute cell reference is one of the cell reference types in which the cells being
referred to do not alter like they did in a relative reference. We utilize the $ sign by
pressing f4 to create a formula for absolute referencing. The $ sign means lock, and it locks
the cell reference for all of the formulas, ensuring that the same cell is referred to all of
them.

3. Mixed Cell Reference in Excel

A Mixed cell reference is a mixture of both relative and absolute cell reference. In mixed
cell reference, dollar signs are attached to either the letter or the number. For
example, $B2 or B$4. It's a mix of relative as well as absolute reference.

Calculate Interest Rate(Mixed Fraction)

Interest Rate
Investments 5.0% 6.0% 7.0%
100
150
200
250
300

Note :- Decrease 1 Decimal on the Interest Rate Percentage.

Interest Rate Formula :- =$A3*B$2

Calculate Interest Rate

Interest Rate
 Investments 12% 15% 20%
10000
14000
20000
25000
50000

Interest Rate Formula :- =$A3*B$2

Create Multiplication Table
Multiplication Table
Number's 1 2 3 4 5 6 7 8 9 10
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 Multiplication Table Formula :- =$A3*B$2

Calculate Student Details

Student's Details
Name Age Height (In) Weight
Asit 13 56 65
Badal 11 51 45
Sajal 15 48 64
Partha 10 46 35
Jatin 12 53 41
Tarun 13 57 62
Parag 14 55 34
Sum
Average

Sum :-Display the sum of the selected cells directly after the selected cells.
Sum Formula Syntax :- =Sum(First Value Address : Last Value Address)
Sum Formula Apply :- =Sum(B3:B9)
Average :-The Average Function calculates the average of numbers provided as
arguments. To calculate the average, Excel sums all numeric values and divides
by the count of numeric values.
Average Formula Syntax :- =Average (First Value Address : Last Value Address)
Average Formula Apply :- =Average (B3:B9)
Cell Styles
Cell style is a pre-defined set of formats, such as fonts, font sizes, number formats, borders,
and shading.
To Apply Cell Style , Follow some Steps :
1. Select the cell, then from Home tab, Styles group, and click on Cell Styles.
2. Choose the suitable style.

Border tab
Excel offers different types of borders that you can add to individual cells or ranges of cells
in your spreadsheet.
To add borders to selected cell(s):
1. Select the cell or range of cells that
you want bordered.
2. Select a line type from the Style area.
3. Select a line color from the Color area.
4. In the Border section of the format
cells dialog box, select where you want
the border applied.
None:to remove borderlines.
Outline:borderlines will surround the entire cell or cell range.
Inside:borderlines will appear around all cell edges for each selected cell.
Border icon on Font group on Home Tab

Sorting Data
Sorting is a common task that allows you to change or customize the order of your
spreadsheet data. For example, you could organize an office birthday list by employee,
birthdate, or department, making it easier to find what you're looking for. Custom sorting
takes it a step further, giving you the ability to sort multiple levels (such as department first,
then birthdate, to group birthdates by department), and more.

To Sort in Alphabetical Order :

• Step 1: Select a cell in the column you want to sort by.
• Step 2: Select the Data tab, and locate the Sort and Filter group.
• Step 3: Click the ascending command sort ascending to Sort A to Z, or the descending
command sort ascending to Sort Z to A.

• Step 4: The data in the spreadsheet will be organized alphabetically.

Benapur Higher Secondary School

Marksheet
Roll No. SNam Ben Eng Mat Lsc Psc Hist Geo Total % Grade
e h

Rules :-
1. Entry some records.
2. Calculate Total and Percentage.
3. Calculate Grade with condition(>=80,”A”, >=60,”B”, >=40,”C”)
4. Create Merit List (Sort) and also create Column Chart.
5. Less than 40 of any subject identify Red color (Use Conditional Formatting).

Total Formula :- =Sum(C2:I2)

% Formula Syntax :- =Total/No. of Subject
% Formula Apply :- =J2/7
Grade Formula :- =If(K2>=80,”A”,If(K2>=60,”B”,If(K2>=40,”C”,”Fail”)))

To Arrange Records(Merit List) ,Follow some Steps:

1)Select Field against record cells then goto Home Tab Or Data Tab
2)Click on Sort & Filter Option
3)Click on Custom Sort
4)Click on Sort By option box then Click your desire Fields (Total)
5)Click on Order Option Box then Click on (A to Z/Z to A using Text Record to
Sort ) or (Largest To Smallest/Smallest to Largest using Number Record to Sort)
6)Finally Click on Ok

Charts
A chart in Excel is a visual representation of data or values that allows us to make an
interpretation or comparison visually. Charts in Excel help summarize or report large
amounts of data, making it easier to interpret.
You can quickly display the "Insert Chart" dialog box by selecting your data and clicking on
the dialog box launcher in the bottom right corner of the group. If a chart is active the
"Change Chart Type" dialog is displayed.

Recommended Charts -Displays the "Insert Chart" dialog box (Recommended Charts tab).

Column or Bar -Drop-Down. The drop-down contains the commands: 2-D Column, 3-D
Column, 2-D Bar, 3-D Bar and More Column Charts.

Line or Area - Drop-Down. The drop-down contains the commands: 2-D Line, 3-D Line, 2-D
Area, 3-D Area and More Line Charts.
Pie or Doughnut - Drop-Down. The drop-down contains the commands: 2-D Pie, 3-D Pie,
Doughnut and More Pie Charts.
Hierarchy - Drop-Down. The drop-down contains the commands: Treemap, Sunburst and
More Hierarchy Charts.
Statistic - Drop-Down. The drop-down contains the commands: Histogram, Box and
Whisker and More Statistical Charts.
Scatter XY or Bubble - Drop-Down. The drop-down contains the commands: Scatter, Bubble
and More Scatter Charts.
Waterfall, Funnel, Stock, Surface or Radar - This drop-down contains the commands:
Waterfall, Funnel, Stock, Surface, Radar and More Stock Charts.
Combo - Drop-Down. Use a combo chart when the range of values in the chart varies
widely or you have mixed types of data. The drop-down contains the commands: Clustered
Column-Line, Clustered Column-Line (secondary axis), Stacked Area Clustered Column or
Create Custom Combo Chart.
Maps - Drop-Down. The drop-down contains the commands: Filled Map and More Map
Options.
PivotChart - Button with Drop-Down. The button displays the "Create PivotChart" dialog
box. The drop-down contains the commands: PivotChart and PivotChart and PivotTable.

ToCreate Different Types of Chart,Follow some Steps :

1)Select Two Or More Than Two Fields against Record Cells Or Select Gapping Fields against
Record Cells so by pressing Ctrl Key then Select it.
2)Goto Insert Tab.
3)Click on Different Types of Chart Option under Charts Gallery.
4)Then Choose Different Types of Chart Styles.

Conditional formatting
Conditional formatting makes it easy to highlight certain values or make particular cells easy
to identify. This changes the appearance of a cell range based on a condition (or criteria).
You can use conditional formatting to highlight cells that contain values which meet a
certain condition. Or you can format a whole cell range and vary the exact format as the
value of each cell varies.

To Create a Conditional Formatting Rule

• Step 1: Select the cells that you want to add the formatting to.
• Step 2: In the Home tab, click the Conditional Formatting command.
A drop-down menu will appear.
• Step 3: Select Highlight Cells Rules or Top/Bottom Rules.
We will choose Highlight Cells Rules for this example.
A menu will appear with several rules.
• Step 4: Select the desired rule (Greater Than, for example).
• Step 5: From the dialog box, enter a value in the space
provided, if applicable.If you want, you can enter a cell
reference instead of a number.
• Step 6:Select a formatting style from the drop-down menu.
• Step 7:The formatting will be applied to the selected cells.
 To Use Preset Conditional Formatting
Excel has several presets that you can use to quickly apply conditional formatting to your
cells. They are grouped into three categories:
Data Barsare horizontal bars added to each cell, much like a bar graph.
Color Scaleschange the color of each cell based on its value. Each color scale uses a two or
three color gradient. For example, in the Green - Yellow - Red color scale, the highest values
are green, average values are yellow, and the lowest values are red.
Icon Setsadd a specific icon to each cell based on its value.
• Step 1: Select the cells you want to add the formatting to.
• Step 2: In the Home tab, click the Conditional Formatting command. A drop-down menu
will appear.
• Step 3:Select Data Bars, Color Scales or Icon Sets (Data Bars, for example). Then, select
the desired preset.
• Step 4:The conditional formatting will be applied to the selected cells.

Remove Conditional Formatting Rules

• Step 1: Select the cells that have conditional formatting.
• Step 2: In the Home tab, click the Conditional
Formatting command. A drop-down menu will appear.
• Step 3: Select Clear Rules.
• Step 4: A menu will appear. You can choose to clear
rules from the Selected Cells, Entire Sheet, This Table,
or This PivotTable.

Paschim Medinipur Youth Computer Training Centre

Transcript
Reg No. SName CName MT1[10] Practical[20] MT2/Theory[70] Total % Grade

Rules :-
1. Entry some records.
2. Must be enter CName (CDTA,CFAS,CDTP)
3. Calculate Total and Percentage.
4. Calculate Grade with condition
>=90  A+ >=50 C+
>=80 A >=40 C
 >=70 B+ <=40 FAIL
>=60 B
5. Create Format as Table and also Create Bar Chart.
6. Identify Fail Candidate by using Conditional Formatting.
Total Formula :- =Sum(D2:F2)
% Formula :- =G2/1
Grade Formula :-
=If(H2>=90,”A+”,if(H2>=80,”A”,if(H2>=70,”B+”,if(H2>=60,”B”,if(H2>=50,”C+”,if(H2>=40,”c”,
”Fail”))))))

To Create Conditional Formatting(Character Format), follow some Steps:

1)Select Grade Record Cells
2)Then Go to Home Tab
3)Click on Conditional Formatting Option
4)Highlight Rules Option then click on Equal To
5)Write Fail
6)Then Choose Color
7)Click on Ok

Formatting Tables
Just like regular formatting, tables can help to organize your content and make it easier for
you locate the information you need. To use tables effectively, you'll need to know how to
format information as a table, modify tables, and apply table styles.

To Format Information as a Table

• Step 1: Select the cells you want to format as a table.
• Step 2: Click the Format as Table command in the Styles group on the Home tab.

• Step 3:A list of predefined table styles will appear. Click a table style to select it.
• Step 4:A dialog box will appear, confirming the range of cells you have selected for your
table. The cells will appear selected in the spreadsheet, and the range will appear in the
dialog box.
• Step 5:If necessary, change the range by selecting a new range of cells directly on your
spreadsheet.
• Step 6:If your table has headers, check the box next to My table has headers.
• Step 7:Click OK. The data will be formatted as a table in the style that you chose.

Printing a Worksheet
To Print, Preview and Modify Page Setup
1) Click on the File tab
2) Click on Print
The spreadsheet shows as it will be printed. You can proceed to print the document from
here, or you can change things to make the printed output look different.

Page Setup
You can change options under Settings or you can click on Page Setup.

Clicking on Page Setup will open a dialog box with four tabs:
Page
Margins
Header/Footer
Sheet

Page:
1) Change the Orientation.
2) Adjust the Scaling.
3) Change the Paper Size.
Margins:
1) Change the margins.
2) Center on the page either horizontally, vertically or select both.

Header/Footer:
1)To select from one of the already created headers/footers, click on the drop-down arrow
for Header and also for Footer and choose from the list.
2)To create a custom header and/or footer, click on Custom Header and Custom Footer.

This area is made of three sections – left, center and right. Any information added in these
sections will appear in that area (left, center or right) in the header or footer. You will also
see a row of buttons in this dialog box. Following are their functions:
3) Click in a section to position your cursor.
4) Enter text/fields.
5) Click OK when finished.

Sheet Tab:
1) Repeat Rows and Columns under Print Titles
2) Check off what to print under Print
3) Change the Page Order

GST (Goods and Services Tax)

GST, or Goods and Services Tax, is an indirect tax imposed on the supply of goods and
services. It is a multi-stage, destination-oriented tax imposed on every value addition,
replacing multiple indirect taxes, including VAT, excise duty, service taxes etc. Goods and
Services are included under a single domestic indirect taxation law for the whole of india. In
this regime, tax is charged at each point of sale.
The Goods and Services Tax Act was passed by the Parliament on March 29, 2017, and it
went into effect on July 1, 2017, making it the most recent piece of legislation to be passed
in the country.
How does Goods and Services Tax (GST) work?
Given below is how GST works –
Manufacturer
The manufacturer will be responsible for paying GST on the raw materials that are
purchased as well as the value that has been added to the product during the
manufacturing process.
Service Provider
In this case, the service provider will be responsible for paying GST on both the amount
paid for the product and the amount of value that has been added to the product.
However, the amount of tax that the manufacturer has paid can be deducted from the total
amount of GST that is required to be collected.
Retailer
The retailer is responsible for paying GST on both the product purchased from the
distributor and the margin added to the price of the product. However, the amount of tax
that has been paid by the retailer can be deducted from the total amount of GST that is
required to be collected.
Consumer
As a consumer, you are responsible for paying GST on the product you have purchased.

Types of GST
 Central Goods and Services Tax (CGST): The CGST is levied on products and services that
are supplied within a state
 State Goods and Services Tax (SGST): Like the Central Goods and Services Tax (CGST), the
State Goods and Services Tax (SGST) is levied on the sale of goods and services within a
state
 Integrated Goods and Services Tax (IGST): The IGST is levied on interstate transactions
involving the sale of goods and services
 Union Territory Goods and Services Tax: Tax on the supply of goods and services in the
Union Territories of the country, which include the Andaman and Nicobar Islands, Daman
and Diu, Dadra and Nagar Haveli, Lakshadweep, and Chandigarh, is levied under the Union
Territory Goods and Services Tax (UTGST). The UTGST is levied in addition to the CGST

Objectives of GST
 In order to realise the ideology of “One Nation, One Tax”.
 In order to consolidate a large proportion of India’s indirect taxes.
 In order to prevent the cascading effect of taxes.
 To put a stop to tax evasion(Tax evasion is the crime of not paying the full amount of tax
that you should pay).
 In order to broaden the base of taxpayers.
 Procedures for conducting business online for the convenience of the customer.
 Improved distribution and logistical infrastructures.
 The goal is to promote competitive pricing while also increasing consumption.

Key Features of GST

Here are the key features of Goods and Services Tax –
Single Indirect Tax
GST is single, unified tax reform. It consolidated numerous existing indirect central and
state taxes such as the Central Value Added Tax, Special Additional Duty of Customs,
Service Tax, and VAT. The abolition of these indirect taxes has made many goods and
services more inexpensive compared to consumers as well as made tax compliance easier
for businesses.
Input Tax Credit System
The input tax credit is a popular GST feature in India. A manufacturer or service provider
can deduct input tax paid on purchases from their total output tax liability. To claim the tax
credit, the input and output invoices must match. This removes the traditional ‘tax-on-tax’
effect. It also reduces tax evasion.
GST Composition Scheme
A voluntary composition scheme is available to SMEs in certain states with a turnover of up
to Rs. 1.5 crore and in the case of North-Eastern states and Himachal Pradesh, the limit is
now Rs 75 lakh. Businesses can pay a fixed GST rate of 1%,5%, 6% based on their business
and their turnover under this scheme. However, they cannot claim the input tax credit. A
company must choose between the composition scheme and the input tax credit feature.
Four-tier Tax Structure
GST has four tiers of taxation: 5%, 12%, 18%, and 28%. This tax structure applies to all
goods and services. Many essential commodities, such as food, are exempt(free) from GST.
This 4-tier structure offers improved transparency and lower costs for goods and services.

Benefits of GST In India

 Easy to Use Online Procedure.
 Higher Threshold for Registration.
 Easy Transportation of Goods for E- commerce Companies.
 More Systematized & Regulated Process.
 More Efficient Logistics.
 No More “Tax on Tax”
 Composition Scheme Under GST to the Rescue of Small Companies.

Indirect tax structure under GST

Indirect Tax Structure under GST

Indirect taxes subsumed under GST Indirect taxes not

Federal level State level subsumed under GST
 Central excise duty  State value added  Property Tax & Stamp
 Additional excise duties tax/sales tax Duty
(goods of special  Entertainment tax (other  Electricity Duty
importance) than levied by the local  Excise Duty on Alcohol
 Service tax bodies)  Basic Custom Duty
 Additional customs  Central sales tax (levied  Petroleum crude,
duty / countervailing by the Centre and Diesel, Petrol, ATF &
duty collected by the States) Natural Gas.
 Special additional duty of  Octroi and entry tax
customs  Purchase tax
 Excise duty levied under  Luxury tax
Medicinal and Toiletries  Taxes on lottery, betting,
Preparation Act and gambling
 Additional excise duties  State surcharges and
levied under textiles and cesses
textile products  Taxes on advertisements
 Central surcharges and
cesses
 Components of GST
There are three components to GST:
 CGST: Central goods and services tax, 50% GST levied on an intra-state sale and collected
by the central government;
 SGST/UGST: State/union territory goods and services tax, 50% GST levied on an intra-
state sale and collected by the state or union territory; and
 IGST: Integrated goods and services tax, 100% GST levied on interstate sales and
collected by the central government. The IGST is the aggregate of the CGST and SGST; the
SGST is appropriated from the state where the supplies are consumed. The Central
Government is bound to share a further 42% from its share of GST with the states.
Dual tax
The GST is a dual levy, which means that both the federal and State government levy tax on
supply of goods and services based on the nature of transaction (Inter-State or Intra-State).
Accordingly, GST has two concurrent components:
 State/Union Territory GST (SGST/UTGST): It is levied and collected by the state or union
territory (UT).
 Central GST (CGST): It is levied and collected by the federal government.

GST Regime – Some salient features

Some of the salient features of the GST regime are:
GST Rates
The GST rates in India have been determined as follows:
 Exempt – 0%
 Precious Stones – 0.25%
 Gold – 3%
 Merit Rates – 5%
 Standard Rates – 12% & 18%
 Demerit Rates – 28%
 Compensation Cess – levied over and above the demerit rates

GST Registration
Under the GST regime, the registration threshold is INR 10 Lakh for special category states
(Arunachal Pradesh, Assam, Jammu and Kashmir, Manipur, Meghalaya, Mizoram, Nagaland,
Sikkim, Tripura, Himachal Pradesh and Uttarakhand) and INR 20 Lakh for Rest of India. Small
dealers with turnover below INR.
With the recent changes, the threshold for registration is increased to 20 lakhs for special
category states and 40 lakhs for rest of India. However, states are free to opt for a new
threshold or retain the old threshold.
Existing dealers would be auto-migrated into the GST regime and given a 15-digit PAN-
based GSTIN – 2 digits to represent state code, 10 digits of PAN, 1 entity code digit
applicable for taxpayers having multiple business verticals within the state, 1 blank digit and
1 checksum digit. All taxable persons will need to process the GST registration online on the
portal.
 GST Returns
The GST regime requires all businesses to mandatorily file GST returns online every month/
quarter along with the requisite annual returns.
 Regular Dealer
o Quarterly Returns: Turnover up to 1.5 crores
o Monthly Returns: Turnover more than 1.5 crores

 Composition Dealer
o Quarterly self-assessed payment and annual returns.

Return Forms
 Regular Dealer
o Form GSTR-1: Monthly or Quarterly basis the turnover threshold
o Form GSTR-3B: Self-assessed monthly returns

 Composition Dealer
o Form GST CMP-08: Quarterly self-assessed return statement-cum-challan
o Form GSTR-4:Annual return

GST Payments
 Mandatory e-payment for amount > INR 10,000
 Pay GST Online via NEFT/RTGS/IMPS
 Pay GST Offline via Cash/Cheque/DD/NEFT/RTGS etc.
 Challan is auto-populated and can be downloaded
 Automated refunds process

HSN Code
HSN code stands for “Harmonized System of Nomenclature,” a eight-digit code used to
systematically name and classify goods for taxation and trade. It was developed by the
World Customs Organization (WCO) and serves as the global standard for categorizing over
5,000 products, playing a vital role in international trade and tax regulation.

E-Way Bill
Electronic-Way Bill is a document introduced under the GST regime that needs to be
generated before transporting or shipping goods worth more than INR 50,000 within State
or Inter-State . The physical copy of E-Way Bill must be present with the transporter or the
person in charge of the conveyance and should include information such as goods,
recipient, consignor and transporter.The E-Way Bill was rolled out nationwide on 1 st April
2018.

Inox Electronic Shop

Gole Bazar :: Kharagpur :: Paschim Medinipur
Item Item Company Total
Rate Qty Total CGST SGST
Code Name Name Amount
Rules :-
1. Entry some record.
2. Must be enter Company Name(SONY, HP,DELL,LG and SAMSUNG etc.)
3. Calculate Total.
4. Calculate CGST(1.2%) and SGST(1.5%) .
5. Finally Calculate Total Amount.
6. Must be arranging records.

Total Formula Syntax :- =Rate * Qty

Apply :- =D2*E2
CGST Formula Syntax :- =Total *CGST
Apply :- =F2*1.2%
SGST Formula Syntax :- =Total * SGST
Apply :- =F2*1.5%
Total Amount Formula Syntax :- =Total+CGST+SGST
Apply :- =F2+G2+H2

Sujata Medical Store

Puratan Bazar :: Kharagpur :: Paschim Medinipur
Manufacturer Expiry Tota Total
Mcode Mname Mtype Rate Qty CGST SGST
Date Date l Amount

Rules :-
1. Entry some record.
2. Must be enter Medicine Type (Tablet, Syrup, Capsule, Tonnic).
3. Calculate total.
4. Calculate CGST(1.5%) and SGST(1.9%) .
5. Finally Calculate Total Amount.
6. Create Format as Table.
Total Formula Syntax :- =Rate * Qty
Apply :- =F2*G2
CGST Formula Syntax :- =Total *CGST
Apply :- =H2*1.5%
SGST Formula Syntax :- =Total * SGST
Apply :- =H2*1.9%
Total Amount Formula Syntax :- =Total+CGST+SGST
Apply :- =H2+I2+J2
 Anita Cosmatic Shop
KharidaBazar :: Kharagpur :: Paschim Medinipur

Item Code Item Name Rate Qty Total

Discount[10%]
CGST[2%]
SGST[2.5%]
Total Amount
Rules :-
1. Entry some records.
2. Calculate Total.
3. Calculate Discount
4. Calculate CGST & SGST
5. Finally Calculate Total Amount.
6. Create cell Style.
Total Formula Syntax :- =Rate * Qty
Apply :- =C2*D2
Discount Formula Syntax :- =Sum(First Value Cell Address : Last Value Cell
Address)*Discount
Apply :- =Sum(E2:E6)*10%
CGST Formula Syntax :- =Sum(First Value Cell Address : Last Value Cell Address)*CGST
Apply :- =Sum(E2:E6)*2%
SGST Formula Syntax :- =Sum(First Value Cell Address : Last Value Cell Address)*SGST
Apply :- =Sum(E2:E6)*2.5%
Total Amount Formula Syntax :- =Sum(First Value Cell Address : Last Value Cell Address)-
Discount+CGST+SGST
Apply :- =Sum(E2:E6)-E7+E8+E9

Character Function
Title First Name Last Name Full Name Upper Lower Proper
Mr. Ajit Das
Miss Neetu Bera
Mr. Goutam De
Miss Sujata Gupta
Mr. Tarun Mitra

Concatenate Function :-Joins several text strings into one text string.
Syntax :- (Text1,Text2,….)
Full Name Formula :- =Concatenate(A2,” “,B2,” “,C2)
Upper Function :-Converts a Text string to all Uppercase letters.
Syntax :- =Upper(Text)
Upper Formula :- =Upper(D2)
Lower Function :-Converts all letters in a text string to Lowercase.
Syntax :- =Lower(Text)
Lower Formula :- =Lower(D2)
Proper Function :-Converts a text string to proper case ; the first letter in each word in
Uppercase, and all other letters Lowercase.
Syntax :- =proper(Text)
Proper Formula :- =Proper(D2)

Country Name Left Middle Right Length

Left Function :-Returns the specified number of characters from the start of a text string.
Syntax :- Left(text,number_characters)
Apply :- =Left(A2,5)
Middle Function :-Returns the characters from the middle of a text string, given a starting
position and length.
Syntax :-Mid(text,start_number,number_characters)
Apply :- =Mid(A2,2,7)
Right Function :-Returns the specified number of characters from the end of a text string.
Syntax :-Right(text,number_characters)
Apply :- =Right(A2,8)
Length Function :-Returns the number of characters in a text string.
Syntax :- Len(text)
Apply :- =Len(A2)

Number Function
Num Coun
Num1 Num2 Sum Avg Max Min Num1 Abs
3 t
45 76 32 -56

Roun Num Num Num

Num1 Int Mod Sqrt
d 1 2 1
234.56
234 22 121
7
Sum Formula :-=Sum(A2:C2)
Average Formula :-=Average(A2:C2)
Max Function :-Evaluates a set of expressions and returns the expression with the
maximum value.
Syntax :- =Max(num1,num2,…)
Apply :- =Max(A2,B2,C2)
Min Function :-Evaluates a set of expressions and returns the expression with the minimum
value
Syntax :- =Min(num1,num2,…)
Apply :- =Min(A2,B2,C2)
Count Function :-The Microsoft Excel COUNT function counts the number of cells that
contain numbers as well as the number of arguments that contain numbers.
Syntax :- =Count(Num1,Num2,Num3)
Apply :- =Count(A2,B2,C2)

Abs Function :-Returns the absolute value of a number. The absolute value of a number is
the number without its sign.
Syntax :- =Abs(Num)
Apply :- =Abs(i2)
Integer Function :-The Microsoft Excel INT Function is a function that is responsible for
returning the integer portion of a number. It works by the process of rounding down a
decimal number to the integer.
Syntax :- =Int(Num)
Apply :- =Int(A9)
Round Function :-The ROUND function rounds a number to a specified number of digits.
Syntax :- =Round(Num,num_digits)
Apply :- =Round(A9,2)
Apply :- =Round(A9,1)
Apply :- =Round(A9,0)
Apply :- =Round(A9,-1)
Mod Function :-Returns the remainder after number is divided by divisor. The result has
the same sign as divisor.
Syntax :- Mod(Number,Divisor)
Apply :-= Mod(D9,E9)
Square Root Function :-The square root of a number is a value that, when multiplied by
itself, gives the number. The SQRT function in Excel returns the square root of a number.
Syntax :-Sqrt(Number)
Apply :- =Sqrt(G2)
 Power Function :-Returns the result of a number raised to a power.
Syntax :- =Power(number, power)
Apply :- =Power(5,2)

Date & Time Function

WeekDa Weeknu
Date Day Month Year
y m
8/22/202
1
9/6/2021

Day Function :-Returns the day of a date, represented by a serial number. The day is given
as an integer ranging from 1 to 31.
Syntax :- =Day(Serial_Number)
Apply :- =Day(A2)
Month Function :-Returns the month of a date represented by a serial number. The month
is given as an integer, ranging from 1 (January) to 12 (December).
Syntax :- =Month(Serial_Number)
Apply :- =Month(A2)
Year Function :-Returns the year corresponding to a date. The year is returned as an integer
in the range 1900-9999.
Syntax :- =Year(Serial_Number)
Apply :- =Year(A2)
Weekday Function :-Returns the day of the week corresponding to a date. The day is given
as an integer, ranging from 1 (Sunday) to 7 (Saturday), by default.
Syntax :- = Weekday(serial_number,[return_type])
Apply :- =Weekday(A2)
WeeknumFunction :-Returns the week number of a specific date.
Syntax :- =Weeknum(serial_number,[return_type])
Apply :- =Weeknum(A2)

Time Hours Minutes Second

10:30:00

Hours Function :-Returns the hour of a time value. The hour is given as an integer, ranging
from 0 (12:00 A.M.) to 23 (11:00 P.M.).
Syntax :- =Hour(Serial_Number)
Apply :- =Hour(A8)
Minutes Function :-Returns the minutes of a time value. The minute is given as an integer,
ranging from 0 to 59.
Syntax :- =Minute(Serial_Number)
Apply :- =Minute(A8)
Second Function :-Returns the seconds of a time value. The second is given as an integer in
the range 0 (zero) to 59.
Syntax :- =Second(Serial_Number)
Apply :- =Second(A8)
 Today Function :-Returns the serial number of the current date.
Apply :- =Today()
 Now Function :-Returns the serial number of the current date and time.
Apply :- =Now()

Stock Management Sheet

Purchases Sales Closing Stock Profit

Item
Slno. &
Name Quantity Rate Amount Quantity Rate Amount Quantity Amount
Loss

Rules :-
1. Entry some record.
2. Entry Purchases and Sales Quantity &Rate , also calculate Amount.
3. Calculate Closing Stock Quantity & Amount.
4. Calculate Profit & Loss.
5. Create Cell Style.
Purchases Amount Formula :- =C3*D3
Sales Amount Formula :- =F3*G3
Closing Stock Quntity Formula Syntax :- = Purchases Quntity-Sales Quantity
Apply :- =C3-F3
Closing Stock Amount Formula Syntax:-=Closing Stock Quantity* Purchases Rate
Apply:-=i3*D3
Profit & Loss Formula Syntax :- =Closing Stock Amount+Sales Amount-Purchases Amount
Apply :- =J3+H3-E3

Bina Pani Bastrallya

GoleBazar :: Kharagpur :: Paschim Medinipur
Bill Sheet
Invoice Product Total
Rate Quantity Amount GST 12%
No. Description Amount
 Total Sales

Rules :-
1. Entry some record.
2. Entry Product Description (Shirt, Jeans, Frock, Suit Salwar, and Trouser etc.).
3. Calculate Amount.
4. Calculate Gst & Total Amount.
5. Calculate Total Sales.
6. Arrange Records Against Product Description.
Amount Formula :- =C2*D2
GST Formula :- =E2*12%
Total Amount Formula :- =E2+F2
Total Sales Formula :- =Sum(G2:G6)

West Bengal Electricity Board

Electricity Bill
Meter Consumer Consumer Meter Pervious Current Total Unit Total Monthly
Address
No. Name Type Rent Reading Reading Unit Price Bill Pay

Rules :-
1. Entry some records.
2. Must be enter Consumer Type (Domestic , Commercial)
3. Current reading greater than previous reading.
4. Must be enter Meter Rent Rs.50/-
5. Calculate Total Unit.
6. Calculate Unit Price Based on Consumer Type [Domestic @ Rs.8/- per units &
Commercial @Rs.10/- per units.
7. Calculate Total Bill.
8. Calculate Monthly Pay
Total Unit Formula Syntax :- =Abs(Previous Reading – Current Reading)
Apply :- =Abs(F2-G2)
Unit Price Formula :- =if(left(D2,1)=”D”,H2*4,H2*6)
Total Bill Formula Syntax :- =Unit Price + Meter Rent
Apply :- =i2+e2
Monthly Pay Formula Syntax :- =Total Bill/No. of Month
Apply :- =J2/3

Kharagpur Medical Hospital

Patient Patient Dept Ward Admit Discharged No.of Bed Dr. Dr.Visit Total
 Id Name Date Date days Charge Visit Charge Bill

Rules :-
1. Entry some records
2. Must be enter Dept (Eye, Nose, Ear, Skin)
3. Must be enter Ward( General, Special)
4. Must be enter Dr. Visit & Dr. Visit Charge
5. Calculate No. of Days.
6. Calculate Total Bill
No. Of Days Formula Syntax :- =Abs(Admit – Discharge)
Apply :- =Abs(E2-F2)
Total Bill Formula Syntax :- =(No. Of Days * Bed Charge)+(No. Of Days * Dr. Visit * Dr. Visit
Charge)
Apply :- =(G2*H2)+(G2*I2*J2)

Medinipur Scan Centre

Patient Doctor Tests Test Advance Due
Address Age Rate
name Name Name Type Pay Amount

Rules :-
1. Entry some records.
2. Must be enter Doctor Name .
3. Must be enter Tests Name (BloodTest, M.R.I, C.T scan, Urine Test & Chest Xray).
4. Must be Tests Type (Sugar Fasting,Leg,Brain,Regular,Culture and Plain).
5. Calculate Due Amount.
6. Create Column Chart.
Due Amount Formula Syntax :- =Rate – Advance Pay
Apply :- =F2-G2

Kharagpur Book Library

Book Book Author Book Date Of Return Over
Fine
Code Name Name Type Issue Date Days
Rules :-
1. Entry some record.
2. Must be enter Book Type (Novel, Story, Poetry, Prose).
3. Calculate Over Days.
4. Calculate Fine with over days (Greater than seven days)
5. Arrange records against Book Type.
6. Create Column Chart.

Over Days Formula Syntax :- =Date Of Issue – Return Date

Apply :- =Abs(E2-F2)
Fine Formula Condition :-
>7,10/-
<7,0/-
Apply :- =if(G2>7,10,0)
South Eastern Railway
Payslip
Ecode Ename Dept Basic DA[60%] HRA[20%] MA[12%] TA[11%] Gross PF[15%] Ptax Net

Rules :-
1. Entry some records.
2. Must be enter Department (ACC,MKT,SAL)
3. Calculate DA, HRA, MA TA & PF with Basic Amount.
4. Calculate Gross.
5. Calculate Ptax with Condition against Basic Salary
>=15000,10%
>=10000,7%
<=10000,5%
6. Finally Calculate Net Amount.
7. Create Subtotal.

DA Formula Syntax :- =Basic*Da

Apply :- =D2*60%
HRA Formula Syntax :- =Basic*Hra
Apply :- =D2*20%
MA Formula Syntax :- =Basic*Ma
Apply :- =D2*12%
TA Formula Syntax :- =Basic*Ta
Apply :- =D2*11%
Gross Formula Syntax :- =Basic+Da+Hra+Ma+Ta
Apply :- =D2+E2+F2+G2+H2
PF Formula Syntax :- =Basic*Pf
Apply :- =D2*15%
PtaxFormula Apply :- =if(D2>=15000,D2*10%,if(D2>=10000,D2*7%,D2*5%))
Net Formula Syntax :- =Gross – Pf - Ptax
Apply :- =I2-J2-K2

To create Subtotal in this Table,follow some Steps :

 First arrange your Data Records(So, go to Data Tab =>Click on Sort=>Click on Sort By
option box Then Choose Dept Field =>Then after click on Order Option Box=>Click on A
to Z=>Click on Ok
 Then After Create Subtotal(So, Click on Field against record cells then go to Data
Tab=>click on Subtotal under Outline Gallery=>Click on at each change in option box
then click on your desire field or Dept=>Finally Click on Ok)

Commission List
Serial No. Agent Name Region Collection Commission

Rules :-
1. Entry some records.
2. Must be enter Region (East, West, North, South)
3. Calculate Commission with Condition apply on Collection
>=100000, 10%+5000,
>=50000, 7%+3000,
>=25000, 5%+1500,
<=25000, 0%+500)
4. Create cell Style & Advance Filter

CommissionFormula :-=If(D2>=100000,D2*10%+5000,If(D2>=50000,D2*7%
+3000,If(D2>=25000,D2*5%+1500,D2*0%+500)))

To apply Advance Filter in this Table,follow some Steps :

 First Write criteria.
 Then select Field against record cells.
 Goto Data Tab.
 Click on Advance under Sort & Filter Gallery.

Agent Name Region Region

Sujoy Das East East
South

 Click on Criteria Range blank box then select criteria range in your Worksheet.
 Click on Copy to another location box then click on Copy to blank box.
 Select any one cell in the Worksheet.

 Finally click on ok.

Age Calculation
Name Date Of Birth Year Month Day
S.Das 2/21/1990

Year Formula:- =Datedif(B2,Today(),”Y”)

Month Formula :- =Datedif(B2,Today(),”YM”)
Day Formula :- =Datedif(B2,Today(),”MD”)

To Protect Your Sheet, Follow some Steps :

 First open your desire sheet.
 Goto Review Tab.
 Click on Protect Sheet under Changes Gallery.

 Then write Password.

 Again write Confirm Password.
 Click on Ok.

To Unprotect Your Sheet ,Follow some Steps :

 Goto Review Tab.

 Click on UnProtect Sheet under Changes Gallery.

 Then write Correct Password.

 Click on ok.
 Loan Interest Installment
Loan
Custome Time Of Interes Installmen
Slno Amoun Rate Total
r Name Month t t
t

Rules :-
 Entry some record
 Enter Customer Name
 Enter Loan Amount and also enter Rate with Percentage (ex.- 2%, 5% etc.)
 Calculate Interest (Loan Amount*Rate*Time Of Month)
 Calculate Total(Loan Amount + Interest)
 Calculate Installment (Total/Time Of Month)
 Create Cell Styles
 Create Different Type of Paste in this Table

Interest Formula :- =C2*D2*E2

Total Formula :- =C2+F2
Installment Formula :- =G2/E2

To copy & Paste in this Table, Follow some Steps :

 First select in this Table(Data’s).
 Goto Home Tab.
 Click on Copy under Clipboard Gallery.
 Thenafter Paste Copy Data’s so, click on Paste option
Present Under Clipboard Gallery (Such as Formula,
Paste_Values, No Border, Transpose).

Loan Interest
Customer Name S.Das
1800
Amount 0
Rate Of Interest 12%
Number Of Periods 10
EMI
 Total Amount Repaid
PMT Function :-Calculate the payment for a loan based on constant payments and a
constant interest rate.
Syntax :-=PMT(rate,nper,pv,[fv],[type])
Nper Full Form :- Number Of Periods
Pv Full Form :- Present Value
Fv Full Form :- Future Value
EMI Formula :- =PMT(B3/12,B4,-B2,0,0)
Total Amount Repaid Syntax :- EMI*No. Of Periods
Total Amount Repaid :- =B5*B4
Goal Seek
A Goal Seek is a tool that is used to find an unknown value from a set of known values. It
comes under the What-If Analysis feature of Microsoft Excel, which is useful to find out the
value that will give the desired result as a requirement. This function instantly calculates
the output when the value is changed in the cell. You have to mention the result you want
the formula to generate and then determine the set of input values that will generate the
result.

To apply Goal Seek in this Table, Follow some Steps :

 First select the Total Amount Repaid Record cells.
 Then goto Data Tab.

 Click on What-if-analysis under Data Tools Gallery.

 Click on Goal Seek.
 Click on Ok

Paschim Medinipur Youth Computer Training Centre

Student Admission
Form No. SName FName Address Gender Caste CName CFees AdmFees DueFees

Rules :-
1. Entry some records.
2. Must be Enter Gender (Male, Female) & Caste (GENERAL, OBC, ST & SC).
3. Must be Enter CName (CDTA, DDTA, DITA, CFAS, DFAS, CDTP, DDTP). By using Data
Validation.
4. Calculate Due Fees.
5. Arrange records against Cname.
6. Create Bar Chart.
7. Create Vlookup Function (Sheet Handling)

To Create Validation List (Combo List) in this Table, Follow some Steps :
 First select Cname field against records.
 Go to Data Tab.
 Click on Data Validation Option under Data Tools Gallery.
 Click on Data Validation .
 Click on Allow Option Box then click on List.

 Click on Source Blank Box then write (Cdta,Ddta,Dita,Cfas,Dfas,Cdtp,Ddtp).

 Click on Ok.

Cfees Formula Syntax:- =If(Left(text,[number_characters])

Apply :- =If(left(G2,1)=”C”,2000,3000)
To Create Data validation whole number in this Table, Follow some Steps :
 First select Admfees field against record cells.
 Goto Data Tab.

 Click on Data Validation Option under Data Tools Gallery.

 Click on Data Validation.
 Click on Allow Option Box then click on Whole Number.
 Then write Maximum and Minimum Numbers.
 Click on Input Message.
 Then Write Input Message.

 Click On Error Alert.

 Click on Ok.

Due Fees Formula Syntax :- =Cfees – Admfees

Apply :- =H2-I2

Vlookup(Vertical Lookup)
VLOOKUP stands for ‘Vertical Lookup’. It is a function that makes Excel search for a certain
value in a column (the so called ‘table array’), in order to return a value from a different
column in the same row.
Syntax :-=Vlookup(lookup_value,table_array,col_index_num,[range_lookup])

Sheet Handling (Sheet2)

Form No. SName FName Address Gender Caste CName CFees

 Sname Formula :- =Vlookup(A2,Sheet1!A2:J6,2,True)

 Fname Formula :- =Vlookup(A2,Sheet1!A2:J6,3,True)
 Address Formula :- =Vlookup(A2,Sheet1!A2:J6,4,True)
 Gender Formula :- =Vlookup(A2,Sheet1!A2:J6,5,True)
 Caste Formula :- =Vlookup(A2,Sheet1!A2:J6,6,True)
 Cname Formula :- =Vlookup(A2,Sheet1!A2:J6,7,True)
 Cfees Formula :- =Vlookup(A2,Sheet1!A2:J6,8,True)

Macros
A macro is an action or a set of actions that you can run as many times as you want. When
you create a macro, you are recording your mouse clicks and keystrokes. After you create a
macro, you can edit it to make minor changes to the way it works.

To record Macro, Follow some Steps :

 Goto View Tab.
 Click on Macros Option under Macros Gallery.

 Click on Record Macros.

 Then write Macros Name.
 Write any Short Cut [Ctrl +r].

 Then click on ok.

 Create Loan Interest Table.

State Bank Of India

 Loan Interest
Principl Compound
Slno Name Time Rate Amount
e Interest

Rules :-
 Entry some records
 Enter Principle And Time
 Enter Rate with Percentage (ex. – 2%, 5%, 7% etc.)
 First Calculate Amount [Principle*(1+Rate)^Time)
 Calculate Compound Interest (Amount-Principle)
Amount Formula :- =C2*(1+E2)^D2
Compound Interest :- =G2-C2

To Stop Recording, Follow some Steps :

 Goto View Tab.
 Click on Macros Option under Macros Gallery.

 Click on Stop Recording.

To View Macros, Follow some Steps :

 Goto View Tab.
 Click on Macros Option under Macros Gallery.
 Click on View Macros.
 Then Choose your Macro File Name.
 Click on Run.