About Us
IPO Result
Guess & Win Offers
Nepali Rashifal
Nepali Date Converter
Preeti to Unicode Converter
Unicode to Preeti Converter
Ticker
6/recent/ticker-posts
Header Ads Widget
Contact Us For Advertisement: 9858091920
Home-icon
News
Share Market
_Share News
_Guess & Win Offers
_Upcoming IPO
_IPO Result
Questions
Vacancy
Career
__⬤ Vacancy
__⬤ Exam Schedule
__⬤ Exam Result
__⬤ Reference Notes
Tools
_⬤ BS to AD Converter
_⬤ AD to BS Converter
_⬤ Unicode to Preeti
_⬤ Preeti To Unicode
_⬤ Compress JPEG
_⬤ PDF Converter
__✦ Compress PDF
__✦ Merge PDF
__✦ Split PDF
__✦ Add Watermark PDF
Computer Course
_⬤ Microsoft Office
__✦ MS Word
__✦ MS Excel
__✦ MS PowerPoint
__✦ MS Access
_⬤ Accounting Package
__✦ Tally
_⬤ Graphic Designing
__✦ Adobe PageMaker
__✦ Adobe Photoshop
__✦ Filmora
__✦ Other
_⬤ Programming
__✦ HTMML/CSS
__✦ C Programming
_⬤ Tips and Tricks
Syllabus
_⬤ Security Forces
__✦ Nepal Army
__✦ Nepal Police
__✦ APF Nepal
__✦ NID Nepal
_⬤ Banking Sector
__✦ Nepal Rastra Bank
__✦ Rastriya Banijya Bank
__✦ Agricultural Dev. Bank
__✦ Nepal Bank Limited
_⬤ Other Sector
__✦ Nepal Oil Corporation
__✦ Nepal Telecom
__✦ Nepal Elect. Authority
__✦ Social Security Fund
__✦ Others
Home
Notes
Microsoft Excel (MS Excel) Notes
Microsoft Excel (MS Excel) Notes
Tutor Chetan
September 19, 2020
Microsoft Excel
Introduction:
Application fine name: excel.exe
Extension name: .XLS
Microsoft Excel is a spreadsheet software program produced by Microsoft Corporation, USA. It allows user to organize, format and calculate data with formulas using a spreadsheet system. This software as a part of the Microsoft office suite (group) and is compatible with other applications in the office suite.
MS-Excel is also known as commercial spreadsheet application produced and distributed by Microsoft Corporation for Microsoft Windows and Mac OS X operating system. It features the ability to perform basic calculations, use graphing tools, and create pivot tables and chart.
Excel has the same basic features as every spreadsheet, which use a collection of cells arranged into rows and columns to organize data manipulation. There are 256 columns and 65536 rows in a single worksheet (for office 2003) or 16384 columns and 1048576 rows in a single worksheet (for office 2007). We can move the cell pointer around the worksheet by pressing arrow key or by clicking left button of mouse into the particular cell.
#. How to start MS-Excel program?
Steps:
1.
Click on Start button
2.
Click on Programs or All programs
3.
Click on Microsoft Office group
4.
Click on Microsoft Excel
OR
i)
Click on Start button
ii)
Click on Run or press winlogo+R. It displays run dialog box.
iii)
Into the open box, type the application file name of MS-Excel program (excel.exe)
iv)
Click on ok. Then we will get following Microsoft Excel program screen.
1.
Title bar
2.
Menu bar
3.
Options bar
4.
Formula bar
5.
A B C columns name (column headings)
6.
1 2 3 row number (row headings)
7.
Cell pointer
8.
Left arrow
9.
Right arrow
10.
Down arrow
11.
Up arrow
12.
Close
13.
Restore/Maximize
14.
Minimize
#. How to Save the worksheet data? (Ctrl+S/F12)
Steps:
1.
Click on
Office button
at the top- left side of the workbook screen.
2.
Click on
Save
option. It displays Save As dialog box.
3.
Choose the required drive and directory name.
4.
Type the file name into file name box.
5.
Click on
Save
Button
#. How to Close the worksheet data? (Ctrl+W)
Steps:
1.
Click on
Office button
2.
Click on
Close
option.
#. How to open the new blank workbook? (Ctrl+N)
Steps:
1.
Click on
Office button
2.
Click on
New
option
3.
Click on
Blank Workbook
4.
Click on Create button
#. To open worksheet data: (Ctrl+O/Crtl+F12)
Open option i s used to open the workbook data, which is already saved for editing or printing or reading purpose.
Steps:
1.
Click on
Office button
2.
Click on
Open
option. It displays open dialog box.
3.
Choose the required drive and directory name, where the files are kept.
4.
Choose the file name
5.
Click on
Open
button
#. How to exit from MS-Excel Program (Alt+F4)
Steps:
1.
Click on
Office button
2.
Click on Exit Excel button
Basic Terms of MS-Excel Program:
#. What is workbook?
Microsoft Excel is a workbook. It is the collection of worksheet. Default; there are 3 worksheets in a workbook. They are Sheet1, Sheet2, Sheet3.
#. What is worksheet?
Worksheet is the group of cells (row/column). There are 16384 columns and 1048576 rows in a single worksheet.
#. What is row?
Horizontal line of the cells on the worksheet is called row.
#. What is column?
Vertical line of the cells on the worksheet is called column.
#. What is cell?
Small rectangular block on the worksheet is called cell. It is also known as meet point of rows and columns.
#. What is cell address?
Each cell has its own address, which is denoted by column name and row number.
Example:
A
1
, B
10
, D
20
, etc.
#. What is cell range?
Cell range is the grouped or highlighted rectangular block on the worksheet, which is separated by double dot or colon.
Example:
A
1
. . D
5
B
2
.. B
8
A5 ..G5
A
1
: D
5
B
2
: B
8
A5:G5
#. Operators:
Operators are the single or group of arithmetical or logical indicators (sign). These are the elements of the formula, which are used to solve the numerical problem. There are two types of operators, they are;
A.
Arithmetical (Numerical) operators
B.
Logical operators
#. Numerical operators:
Numerical operators are those operators, which perform some arithmetical operation like: addition, subtraction, multiplication, division, percentage and exponents
For examples:
i) + (plus) = for addition
ii) – (minus) = for subtraction
iii) * (Asterisk) = for multiplication
iv) / (Slash) = for division
v) % (percentage) = to calculate percentage
vi) ^ Caret (Exponent) = to calculate square, cube, 4
th
, 5
th
, .. nth times
#. Logical operators:
These operators are for decision-making. Various logics are used to solve the problems. These operators compare more than two conditions and give the valuable result "true" or "false".
Example:
i.
= (equal to)
ii.
> (greater than)
iii.
>= (greater than and equal to )
iv.
< (less than)
v.
<= (less than and equal to )
vi.
< > (not equal to)
#. Logical Functions:
IF, AND, OR are known as logical functions in MS-Excel program.
#. IF Function:
This function is used, if only one condition is given. It may be either true or false.
#. AND function:
This function is used, if the all data range (field) names are given as a condition.
S.N.
Name
Subject
Total
Result
Eng
Nep
Sci
OM
CM
1
Messi
45
55
40
60
50
250
Pass
2
Ronaldo
85
75
90
85
95
430
Pass
3
John Cena
35
44
36
38
42
195
Pass
4
Salman Khan
65
75
70
65
60
335
Pass
5
Trump
30
45
40
50
55
220
Fail
6
Chetan
45
55
70
85
45
300
Pass
Example:
Result: =if Eng>=35 and Nep>=35 and Sci>=35 and OM>=35 and CM>=35, "Pass", otherwise "Fail"
Solution: =IF(AND(C4>=35,D4>=35,E4>=35,F4>=35,G4>=35),"Pass","Fail")
#. OR Function:
This function is used, if more than one comparative condition is given as a condition. It checks the first condition, if the first condition is wrong it will check the second condition and gives the valuable result "True" or "False".
Sno
Name
Address
Salary
HRA
Total
1
Messi
Dang
12500
0
2
Ronaldo
Pokhara
3500
350
3
John Cena
Surkhet
7500
750
4
Salman Khan
Nepalgunj
5000
500
5
Trump
Butwal
14200
0
6
Chetan
Surkhet
16500
1650
Example:
HRA will give 10% of salary if his/her salary is less than 6000 or address is surkhet
Solution: =IF(OR(D4<6000,C4="Surkhet"),10%*D4,0)
#. Functions(Alt+=):
Functions are used to solve the numerical problems into the resulting cell.
=Sum(Range of cells)=>Returns the sum of selected cells.
=Max(Range of cells) =>Returns the largest value into the selected cells
=Min(Range of cells) => Returns the smallest value for selected cells
=Average(Range of cells) => Returns the average value of the selected cells
=SQRT(Number) >=Returns the square root of a number
=Power(Number,power) => Returns the power of a number.
=Int =>Returns the whole number without decimal place
=Today( ) => Returns the current system date only.
=Now( ) => Returns the current system date and time.
ABC Cold Store
Kathmandu, Nepal
S.No.
Description
Qty
Rate
Amount
1
Coca-Cola
35
10
2
Fanta
40
15
3
Mirinda
75
12
4
Pepsi
55
14
5
Thums up
150
16
6
Sprit
100
18
7
Ice cream
150
20
8
Mineral water
125
15
9
Reo
180
10
Criteria:
Amount : =
Qty x Rate Enter
Total amount: =
Sum(Range of cells) Enter
Maximum amount: =
Max(Range of cells) Enter
Minimum amount: =
Min(Range of cells) Enter
Average amount:
=Average(Range of cells) Enter
Discount amount
: =3% of total amount Enter
Vatable amount: =
Total amount – discount Enter
13% vat:
= 13% of Vatable amount Enter
Grand Total: =
Vatable amount+13% Vat Enter
#. Adding/Removing border line:
Steps:
1.
Select (highlight) the required cells (rows and columns)
2.
Click on
Home
menu.
3.
Click on
Font drop-down
arrow button. It displays a dialog box.
4.
Click on
Boarder
tab.
5.
Choose the required line style and line color.
6.
Click on
outline
and
Inside
button
7.
Click on ok.
#. Font:
Font option is used to change the font name, font style, font size, font color, underline style, superscript and subscript etc. of the selected text.
Steps:
1.
Select (highlight) the required text or records
2.
Click on
Home
menu.
3.
Click on
Font drop-down
arrow button. It displays a dialog box.
4.
Click on
Font
tab.
5.
Choose the required option.
6.
Click on ok.
#. Patterns/Fill:
Pattern or Fill is used to change the background color or patterns style of the selected cells.
Steps:
1.
Select (highlight) the required cells.
2.
Click on
Home
menu.
3.
Click on
Font drop-down
arrow button. It displays a dialog box.
4.
Click on
Pattern or Fill
tab.
5.
Choose the required background color or pattern style.
6.
Click on ok.
#. Alignment:
It is used to change the horizontal/vertical alignment of the record into the cells.
Steps:
1.
Select (highlight) the required cells.
2.
Click on
Home
menu.
3.
Click on
Font drop-down
arrow button. It displays a dialog box.
4.
Click on
Alignment
tab. It displays;
#. Horizontal alignment:
It is used to change the horizontal alignment of the selected t ext into the cells. (left or center or right)
#. Vertical alignment:
It is used to change the vertical alignment of the selected text into the cells. (top or center or bottom)
#. Wrap text:
It is used to type the multiple lines into a single cell.
#. Shrink to fit:
It is used to managing the record into the particular cell without changing column width.
#. Merge Cells:
It is used to join or combine the multiple cells into a single cell.
#. Orientation:
It is used to change the direction of the record into the cell.
1.
Choose the required options.
2.
Click on ok.
#. Row Height:
Row Height option is used to change (enlarge or reduce) the height of the selected rows.
#.Autofit Row Height:
Autofit Row Height option is used to bring back to the original row height.
#. Column Width:
Column Width option is used to change (enlarge or reduce) the width of the selected columns.
#. Autofit Column Width:
Autofit column width option is used to fix the column width as per text width.
#. Default Width:
Default Width option is used to display the original column width of the selected column.
#. Hide & Unhide:
Hide & Unhide option is used to hide row, column, sheet and unhide the row, column and sheet on the worksheet.
#. Rename Sheet:
Rename Sheet option is used to change the name of selected sheet.
#. Move or Copy Sheet:
Move or copy sheet option is used to move or copy the sheet from one workbook to another workbook.
#. Tab Color:
Tab Color option is used to change the sheet tab color of the selected sheet.
#.Protect Sheet:
Protect Sheet option is used to protect the worksheet. When we have to protect the sheet with protection password, nobody can modify the text on the cell. All Cells become locked.
#. Format Cells:
Format Cells option is used to change the number formatting style, alignment, font, boarder line, pattern style etc. of the selected cells.
Steps:
1.
Highlight the required records
2.
Click on
Format drop-down arrow
button.
3.
Click on
Format Cells
option. It displays format cells dialog box.
4.
Choose the required options.
5.
Click on
ok
.
#. Insert:
Insert option is used to insert row, column, worksheet on the active worksheet window screen.
#. How to insert row?
1.
Keep the cell pointer into the required cell.
2.
Click on
Home
menu.
3.
Click on
Insert drop-down
button.
4.
Click on
Insert Cells
and click on
Entire Row
or Click on
Insert Sheet Rows
option
#. How to insert Column?
1.
Keep the cell pointer into the required cell.
2.
Click on
Home
menu.
3.
Click on
Insert drop-down
button.
4.
Click on
Insert Cells
and click on
Entire Column
or Click on
Insert Sheet Columns
option.
#. How to insert worksheet?
1.
Click on
Home
menu.
2.
Click on
Insert drop-down
button.
3.
Click on
Insert Sheet
option.
#. Delete:
Delete option is used to delete unnecessary row or column or worksheet on the workbook window screen.
#. How to delete row?
Steps:
1.
Select the row, which we want to delete.
2.
Click on
Delete drop-down
button
3.
Click on
Delete Cells
option and click on
Entire Row
option or Click on
Delete Sheet Rows
option.
#. How to delete Column?
Steps:
1.
Select the column, which we want to delete.
2.
Click on
Delete drop-down
button
3.
Click on
Delete Cells
option and click on
Entire Column
option or Click on
Delete Sheet Columns
option.
#. How to delete Worksheet?
Steps:
1.
Select the worksheet, which we want to delete.
2.
Click on
Delete drop-down
button
3.
Click on
Delete Sheet
option or press right button of mouse on the sheet and click on
Delete
option.
#. Format as table:
Format as table option is used to format the selected rows and columns as per need.
Steps:
1.
Select the rows and columns.
2.
Click on
Home
menu.
3.
Click on
Format As table drop-down
button
4.
Click on required formatting style.
#. Cell Style:
Cell Style option is used to change the style of the selected cells on the worksheet screen.
Steps:
1.
Select the required cell
2.
Click on
Home
menu.
3.
Click on
Cell Style drop-down
button.
4.
Click on required cell style.
#. Conditional Formatting:
Conditional Formatting option is used to format the selected worksheet data for given conditions
Steps:
1.
Highlight the worksheet data.
2.
Click on
Home
menu.
3.
Click on
Conditional Formatting drop-down arrow
button
4.
Click on
New Rule
.
5.
Select the Rule type
6.
Select the required logical operator and type the value and click on
Format
button.
7.
Choose the required formatting style.
8.
Click on
ok
.
#. Clear:
Clear option is used to delete the all worksheet data or formatted style or comments or text on the worksheet.
Steps:
1.
Select the worksheet data.
2.
Click on Home menu.
3.
Click on Clear drop-down arrow button. It displays;
Clear All . (It deletes all worksheet data).
Clear Formats. (It deletes all formatted style).
Clear Contents. ( It deletes all typed records).
Clear Comments. (It deletes all comments).
4.
Click on required option as per need.
#. Fill:
Fill option is used to fill the series data on the worksheet and also justify the record into the cell.
#. How to fill the series data?
Steps:
1.
Keep the cell pointer into the proper cell.
2.
Type the starting value and press enter then select the starting value
3.
Click on
Home
menu.
4.
Click on
Fill drop-down arrow
button.
5.
Click on
Series
option.
6.
Select
Row
or
Column
7.
Type the step value and stop value.
8.
Click on
ok
.
#. How to justify the record?
Steps:
1.
Select the range of cell.
2.
Click on
Home
menu
3.
Click on
Fill drop-down arrow
button
4.
Click on
Justify
option.
5.
Click on
ok
.
#. AutoSum:
Autosum option allows to display the sum of the range of cells.
Steps:
1.
Keep the cell pointer into the resulting cell.
2.
Click on
Home
menu
3.
Click on
Autosum drop-down arrow
button
4.
Click on
Sum
.
#. How to sort the records?
Sort option is used to arrange the field records in ascending (A-Z,0-9) and descending (Z-A, 9-0) order into the cells.
Steps:
1.
Select the worksheet records.
2.
Click on
Home
menu.
3.
Click on
Sort & Filter drop-down
button
4.
Click on
Custom Sort
option. It displays a dialog box.
5.
Choose the required column name.
6.
Click on
Ascending
or
Descending
order
7.
Click on
ok
.
#. Find & Select:
Find & select option is used to find or replace the text or number on the worksheet cell.
#. How to find the record?
Steps:
1.
Keep the cell pointer at top of cells.
2.
Click on
Home
menu.
3.
Click on
Find & Select drop-down arrow
button.
4.
Click on
Find
option.
5.
Type the searching records
6.
Click on
Find Next
button. Then the cell pointer is appeared at that cell where the record is located.
#. How to replace the record?
Replace option is used to find the record into the cell and replace with new record.
Steps:
1.
Keep the cell pointer at top of cells.
2.
Click on
Home
menu.
3.
Click on
Find & Select drop-down arrow
button.
4.
Click on
Replace
option.
5.
Type the searching record and new record (replacement)
6.
Click on
Replace or Replace
button.
#. Goto(Ctrl +G)/F5:
Goto option is used to move the cell pointer into the particular cell address.
Steps:
1.
Click on
Home
menu
2.
Click on
Find & Select
drop-down arrow button
3.
Click on
Goto
option.
4.
Type the cell address
5.
Click on
ok
.
#. How to change the margin?
Steps:
1.
Click on
Page Layout
menu.
2.
Click on
Margins
drop-down arrow button
3.
Click on
Custom Margin
option. It displays page set dialog box.
4.
Choose the required margin, paper size and print orientation.
Example
Margin:
Print Orientation
Paper size
To margin Portrait or Landscape A4
Bottom margin
Left margin
Right margin
5.
Click on
ok
.
#. How to select the print Area?
Set Print Area option is used to select the worksheet data for printing mode.
Steps:
1.
Click on
Page Layout
menu.
2.
Click on
Print Area
drop-down button.
3.
Click on
Set Print Area
option.
# To remove the print area:
Steps:
1.
Click on
Page Layout
menu.
2.
Click on
Print Area
drop-down button.
3.
Click on
Clear Print Area
option.
#. How to change the background of worksheet?
Steps:
1.
Click on
Page Layout
menu.
2.
Click on
Background
option.
3.
Choose the required picture.
4.
Click on
Insert
button.
#. How to delete the background of worksheet?
Steps:
1.
Click on
Page Layout
menu.
2.
Click on
Delete Background
option.
#. How to break the page on the worksheet?
Steps:
1.
Keep the cell pointer into the particular cell.
2.
Click on
Breaks
drop-down arrow button.
3.
Click on
Insert Page Break
option.
#. To remove the page break code line?
Steps:
1.
Keep the cell pointer into the particular cell.
2.
Click on
Page Layout
menu.
3.
Click on
Breaks
drop-down arrow button.
4.
Click on
Remove Page
Break
option.
#. How to the worksheet data?
Steps:
1.
Click on
Office
button.
2.
Click on
Print
option. Or Press Ctrl+P. It displays print dialog box.
3.
Choose the printer name, page range
4.
Click on
ok
.
Post a comment
0 Comments
Search Here
Follow by Email
Get all latest content delivered straight to your inbox.
Contact For Advertise: 9858091920
Social Plugin
Official Facebook Page
Subscribe Us
Popular Posts
Check IPO Result - Mahila Laghubitta Bittiya Sansthan
February 25, 2021
Jyoti Life Insurance - UpcomingIPO
February 25, 2021
Nepal Krishi Sahakari Kendriya - Vacancy for various posts
February 25, 2021
Support Us Through eSewa
Support Us Through Khalti
Support Us Through IME Pay
0 Comments