Microsoft Excel Note

MICROSOFT EXCEL 

            It is an important tool of M.S – Office package. This tool is designed for executing various Arithmetic operations. This software is also known as spreadsheet operation. This here user has to operate spreadsheets for doing various calculations.
This tool has the advanced features like interlinking of sheets and workbook for executing multiple arithmetic functions at a time. Besides these it also supports following features.
àStudent mark list
àEmployee’s salary list
àGoal seek
àScenarios
àMultiple sheet designing
àConditional formatting
àSales chart
àMacros
àData base management
àSub total
àArithmetic and logical Functions
àCharacter functions
àData filtering
àPivot tables
àAuditing

Spreadsheet: Spreadsheet is an electronic sheet. it able to execute various formulas for operating the numerical data. A spreadsheet is a combination of rows and columns. It consists 256 columns and 65,536 rows. In a spreadsheet each column consists an alphabetic heading.
A, B, C, D etc., and each row consists a numeric heading 1, 2, 3, etc.
Cell:The point where the column and row interacts with each other is called cell. Each cell has its own address. The cell address is the combination of corresponding column heading & row heading.
Workbook:When we start excel on default it will provide a book along with 3 spread sheets this book is called workbook. In a work book we can execute 3 to 29 worksheets at a time. Giving a password also locks workbook.
Fill Handle:A small rectangular box on the right most end of a cell is called fill handle. It is used fill the contents in that cell in required number of cells.

How to start Excel:
àClick on start buttonàall programsàMS Officeà click on MS Excel
àDouble click on Excel icon the desk top
àClick on start buttonàRunàType ‘Excel’ àenter.
Indicators:The spaces between two rows or two columns are known as indicators. These indicators are used to increase or decrease the size of a row or a column.
Title bar:It consists name of the software and the name of the workbook you are operating.
Standard tool bar:It consists various short cut buttons by using which we can do the quick operation.
Menu bar:It consists various menus by using which we can do a lot of operation.
Formatting tool bar:It consists various buttons by using which we can design the worksheet.
Formula bar:This bar is used to operate various formulas in worksheet.
Horizontal and Vertical scroll bar:These bars are used to scroll the worksheet right and lift and up and down.
Status bar:This bar will display the complete status of the worksheet and the operation. This is done on the worksheet.
How to write a formula:
Place on equal to sign in the cell in which you want to set the calculation. Then start bracket and place cell address along with the arithmetic operation and close the bracket. Then press enter to get the result.
 EX: - =sum (A1:A5)
Student Mark List
SL NO
STUDENTS NAME
ENG
MATH
MIL
SCIENCE
SOCIAL
COMP
TOTAL
AVG
DIV
1
SRINU
45
90
75
85
67
65
427
71.17
1st div
2
CHAITU
42
85
50
61
55
48
341
56.83
2nddiv
3
CHIRU
42
85
67
74
84
38
390
65.00
1st div
4
DEEPU
49
87
79
78
65
43
401
66.83
1st div
5
ANIVESH
35
90
37
40
49
43
294
49.00
3rd div
TOTAL: - Add all Subjects i.e. fx: =SUM(num1,num2…)  (Drag All Subjects) à Enter./Autosum
AVG: - fx: =K5(tot)/ 6(no of subjects) à Enter
DIVISION: - fx: =IF(AVG>60,"1ST DIV",IF(AVG>50,"2ND DIV",IF(AVG>35,"3RD DIV",IF(AVG<35,"FAIL")))) à Enter.
EX:-=IF(N11>60,"1st div",IF(N11>50,"2nddiv",IF(N11>35,"3rddiv",IF(N11<35,"fall"))))

EMP CODE
EMPLOYEE NAME
NWD
RPD
BASIC SAL
HRA 12.5%
TA 10%
GROSS SAL
PF 5%
ESI 2.5%
DED
NET SAL
1001
SRINU
27
350
9450.0
1181.3
945.0
11576.3
472.5
236.3
708.8
10867.5
1002
CHAITU
24
400
9600.0
1200.0
960.0
11760.0
480.0
240.0
720.0
11040.0
1003
CHIRU
26
250
6500.0
812.5
650.0
7962.5
325.0
162.5
487.5
7475.0
1004
DEEPU
28
450
12600.0
1575.0
1260.0
15435.0
630.0
315.0
945.0
14490.0
1005
ANIVESH
25
500
12500.0
1562.5
1250.0
15312.5
625.0
312.5
937.5
14375.0
Employees Salary List


Basic Salary:- =No of  Working Days X Rate per Day àEnter. Ex:- =E20*F20
House Rent Allowance:- = Basic salary X HRA % àEnter. Ex:- =G20*12.5/100.
Travellance Allowance:- =Basic salary X TA % àEnter. Ex:- =G20*10/100.
Gross Salary:-  =Sum(Basic salary+HRA+DA) àEnter. Ex:- =Sum(G20+H20+I20)
Proudest Fund:- =Basic salary*PF % àEnter. Ex:- =G20*5/100.
Employee State Insurance:- =Basic salary*ESI % àEnter. Ex:- =G20*2.5/100.
Deduction Amount:- =Sum(PF+ESI) àEnter. Ex:- =Sum(K20+L20).
Net Salary:- =Gross salary-DeductionàEnter or =Gross salary - (PF+ESI)àEnter. Ex:- =J20 – (K20+L20).

Simple Interest
PRINCIPAL
2000
RATE
3
TIME
7
S.I
420






Simple Interest: =Principle*Rate*Time/100
Ex: =G48*G49*G50/100àEnter.

Sales Chart
Items

Amount
Text Books

1000
NoteBooks

800
Pens

1200
Pencils

500
Eraser

300
Menders

400
0








Sales Chart: Go to InsertàChoose which you wantàclick Nextàclick on ColumnsàNextàGive series names i.e Title, X axis & Y axisàNextàFinish.

3. FUNCTIONS
Functions are Pre-Determined formulas. It is also called Calculate Engine. There are 3 functions.
1. Math & Trigonometric Functions.
2. Statistical Functions.
3. Text Functions.
Math & Trigonometric functions
20
30

40

50
2
 1. ABS ( ):- This option is used to return the absolute value of a given no. The absolute value is the value or No without its Sign.i.e (+ or -)
Syntax: - =ABS(No)
Salary
1000
4000
3500
2800
5000
4
 Ex: - =ABS(-123) or (+123) =123
 2. Countblank ( ):- It counts the blank cells within a given cell range.
 Syntax: - =Countblank(Range)
 Ex: - =Countblank(E99:E104) Select All itemsàEnter.
3. Countif ( ):- it contains the cells which meet the given area.
Syntex: - =Countif (Range, “Criteria”)
Ex: - =Countif(Range,">1000")

4. EXP ( ):- It returns to raised the power of number. Natural logarithms are based on the constant e(2.71828182845904).
Syntax:-=Exp (No)-Number is the exponent applied to the base e.
Remarks: To calculate powers of other bases, use the exponentiation operator (^). EXP is the inverse of LN, the natural Logarithm of Number.
Ex:- =exp(1) is equals to 2.718282.
5. INT ( ):- It rounds a number down to the nearest integer.
Syntax:- =Int(No)- It is the real number you want to round down to an integer.
Ex:- =Int(5. 5) is equals to 5.
       =Int(-5..5) is equals to -6.
6. MOD ( ):- It returns the remainder after dividing the number by a divisor. The result has the same sign as divisor.
Syntax:- =Mod(number, divisor)
Number is the number for which you want to find the remember, divisor is the number by which you want to divide number, it divisor is O, mod returns the # div 10 error value.
Ex:-=Mod(5,4) is equals to 1.
      =Mod(5,-4) is equals to -3.
7. SQRT( ):- It returns a positive square root.
Syntax:- =SQRT(number).
Ex:-=SQRT(25) is equals to 5.
Number is the number for which you want the square root. If number is negatives, SQRT returns the #num error value.
8. SUM:- It returns the sum of the number in the list of arguments. You can provide maximum 30 arguments sum functions.
Syntax:-=Sum(number1,number2,…..)
Ex:-=Sum(1,2,3,4,5) is equals to 15.
9. SUMIF( ):- It adds the cells specified by given criteria.
Syntax:- =Sumif(range,criteria,sum_range)
Ex:-=Sumif(A1:A5,">1000",B1:B5). Result is 85.
10. FACT( ):- It returns the factorial of a number, the factorial of a number is equals to [1*2*3*4….*]
Syntax:- =Fact(number). Number is the non-negative number you want the factorial of a number is not an integer. It is truncated.
Ex:-=Fact (5) is equals to 120.
11.POWER( ):- It returns the result of a number raised to a power.
Syntax:- =Power(number, power)
Number is the base number it can be any real number, power is the exponent to which the base number is raised.
Ex:- =Power(5,2) is equals to 25.
12.ROUND( ):-Rounds a number to a specified number of digits. Number is the number which you want to round. Num-digits specified the number of digits to which you want to round number.
Syntax:- =Round(number, num-digits)
Ex:- =Round(2.565,1) is equals to 2.6
            =Round(-2.565,2) is equals to -2.57.
            =Round(21.50,-1) is equals to 20.
13.SIGN( ):- It determines the sign of a number returns 1 if the number is positive, zero(0) if the number is 0, and -1 if the number is negative.
Syntax:-=Sign(number)
Ex:- =Sign(10) is equals to 1.
            =Sign(2-2) is equals to 0.
Statistical Functions
1. Average( ):- It returns the average value of the arguments. You can provide maximum 30 number of arguments.
Syntax:=Average(no1,no2…)
Ex:       =Average(A1:A5)
2. Count( ):- It counts the number of cells that contain numbers within the arguments. Use count to get the numbers of entries in a number field in a range or array of numbers.
Syntax: =Count(val1,val2…)
3. Max( ):- It returns the largest value in a list of arguments.
Syntax: =Max(no1, no2…)
4. Min( ):- It returns the smallest value in the list of arguments.
Syntax: =Min(no1, no2…)
Text Functions
1.Char( ):- It returns the character specified by the code number. Number is between to 255.
Syntax: Char(number)
Ex: =Char(65) is equals to “A”
2.Code( ):- It returns a numeric code for the first character in a text string.
Syntax: Code(text)
Ex: =Code(“A”) is equals to 65.
3. Exact( ):- Compares two text string and returns true if they are exactly same, false otherwise.
Syntax: Exact(text1, text2)
Ex: =Exact(“TECHNOSOFT”, “TECHNOSOFT”) is True.
=Exact(“TECHNOSOFT”, “technosoft”) is False.
4. Left( ):- It returns the first or left most character or characters in a text string.
Syntax: Left(text, num-char)
Ex: =Left(“Technosoft”,4) is equals ‘Tech’.
5. Right( ):- It returns the last or right most character or characters in a text string.
Syntax: Right(text,num-char)
Ex: =Right(“Technosoft”,4) is equals  ‘soft’
6. Mid( ):- It returns a specific number of character from a text string.
Syntax: Mid(text,start-num,num-char)
Ex: =Mid(“Technosoft”,3,4) is equals- ‘ chno’
7. Len( ):- It returns the number of character in a text string.
Syntax: Len(text)
Ex: =Len(“Technosoft”) is equals 10.
8. Proper( ):- Capitalizes the first letter in a text and any other letter in text that follow any character other than a letter converts of other letters to lowercase.
Syntax: Proper(text)
Ex: =Proper(“TechNosofT”) is equals ‘Technosoft’
9. Lower( ):- It converts all uppercase letters in a text string to lower case.
Syntax: Lower(text)
Ex: =Lower(“TECHNOSOFT”) is equals ‘technosoft’
10. Upper( ):- It converts all lowercase letters in a text string to uppercase.
Syntax: Upper(text)
Ex: =Upper(“technosoft”) is equals ‘TECHNOSOFT’
11.Rept( ):- It repeats the text to a given number of times.
Syntax: Rept(text,number-times)
Ex: =Rept(“T”,5) is equals ‘TTTTT’
12. Concatenate( ):- It joint several text strings into one text string.
Syntax: Concatenate (text1,text2…)
Ex: =Concatenate(“Technosoft”, “Solutions”) is equals ‘TechnosoftSolutions’.

Menu Bar
File (Alt+F):
New (Ctrl+N): This option is used to create a new work book. It allows the used to operate new work book.
àClick on fileàclick on newàclick on blank work book.
Open (Ctrl+O): This option is used to open an existing workbook.
Save (Ctrl+S): This option is used to store the workbook in a particular name.
Close (Alt+F4): This option is used to close the active worksheet.
Save As: This option is used to create duplicate of active workbook.
Save as web page: This option is used to save a sheet like a web page in used to save.
Save workspace: This option is used to save any location of the sheet in a particular name. It helps in operating the work sheet in various names.
Web page preview: This feature is used to view the preview of the web page of the active worksheet.
Page setup: This option is used to set the work sheet in different size of papers in different directions of printing purpose. Here used can set paper size.
àPrint volume
àPrint pattern
àGridlines
àHeader and footer
Page: This feature is used for paper setting.
Margin: It is used to set top, bottom, left, and right margin. It is also used to set alignment for printing on the page for alignment (adjusting) text we have to click on horizontal and vertical check button.
Header and Footer: This option is used to set head note and foot note to the various pages of the worksheet.
Sheet: It is used to set print quality line to be repeat. Print area, gridlines, pattern of printing etc.
Print Area: This option is used to set the required portion, which you want to print.
Print preview: This option is used to view the preview of the pages which you want to print the print preview, window contains following buttons.
Print (Ctrl+P): It is used to print various pages of the worksheet here we can set page to be print and the number of copies.
Send To: It is used to maintain complete description or data about the workbook (i.e. author name, purpose, time etc.)
Exit: This option is used to close the active work book.

Edit (Alt+E):
Undo (Ctrl+Z): To recall the previous action.
Redo (Ctrl+Y): To repeat the action called by redo.
Cut (Ctrl+X): To cut the contents in selected cells.
Copy (Ctrl+C): It is used to create the duplicate of selected cells.
Paste (Ctrl+V): It is used to paste the cut or copied paste at required point of the worksheet.
Paste Special: This option is used to some features while pasting the selected calls. It allows the used to use to features like:
àPaste only values
àPaste only format.
àPaste only formula etc.
Fill: This option is used to fill contents in the selected cells in required number of selected cells.
Clear: This option is used to clear.
Delete: To delete the selected contents form the sheet.
Delete sheet: This option is used to delete the active sheets entirely from the workbook.
Move or copy sheet: This option is used to move the work sheet in a work book or to create the copy of the required worksheet.
Find (Ctrl+F): It is used to find the deleted contents from worksheet.
Replace (Ctrl+H): It is used to replace the fine word with another word.
Go to (Ctrl+G): It is used to move the cursor pointer to the required all address it.

View (Alt+V):
Normal: This is the normal presentation of a work sheet by MS Excel. This view is suitable for designing.
Page break preview: This view is suitable for designing a particular page in to required number of pages. It helps to break work sheet in to the required number of pages.
Tool bars: it is used to hide or display the formula bar in the formula bar in the screen.
Formula Bar: It is used to hide or display the formula bar in the screen.
Status bar: It is used to hide or display the status bar in the screen.
Header & Footer: This option is used to provide head notes and footnotes to the various pages of worksheet.
Comments: This option is used to view the comments given through the insert menu.
Full Screen: It is used to display the worksheet entirely on the screen by hiding all it toolbars.
Custom view: This option is used to view a particular portion of the document as per the need hence it helps to save the particular document and particular name.
àTo remove the portion clicks on delete.
Zoom: This option is used to increase or decrease the display made of MS Excel.

Insert (Alt+I):
Cell: It is used to insert a blank cell any point of the worksheet.
Row and column: These options are used to insert a blank row or column at the required point of the worksheet.
Insert work sheet: This option is used to insert a blank worksheet.
Page Break: This option is used to set page break at required point of the worksheet this can be done in page break preview it is also used to remove the existing page breaks on the worksheet.
Comment: This option is used to provide comment on the contents in any cell.
Pictures:  This option is used to insert clipart, picture files from other files on the worksheet.
Chart: This is an important feature contained by MS Excel this feature is used to present the numerical data in diagrammatic from. Hence we can present the data in different type of graphs.
àThis Excel can operate 32 varieties of graphs to present the numeric data in a worksheet from this feature allow single, double or multi data to present in a graphic mode.
àFirst select the data, which one do you want to present in graphs (excluding headings)
àThen Alt+I or insert menu from menu bar and then click on chart.
àNow you will get a chart designing dialog box as shown.
In this box you have to select the required type of chart along with its design.
It displays the following types of charts.
1)      Bar chart
2)      Line chart
3)      Pie chart
4)      Column chart
àAfter selecting the required chart click on next. Now you will get chart on the dialog box here we have to choose series feature to set the required data indicators on the legend.
1)      Title: This feature is used to set chart-heading X-axis, Y-axis.
2)      Gridlines: This feature is used or set gridlines on the background of the chart the gridlines may be major or minor.
3)      Access: This feature is used to set data on the graph for clear presentation.
4)      Legend: This is used to set the position of legend on the chart.
5)      Date Labels: This feature is used to set data on the graph for clear presentation.
6)      Data Tables: This feature is used to attach the data table with the graph.
àAfter making all the above adjustments click on next, now you will get another dialog box, you have to set the shot on which you want in place the graph then click on finish to get the graph.

Format (Alt+O):
Cell Formatting: In MS –Excel you can design the cells as per the requirement. Excel provides a lot of features by using, which we can design, aligns and we can set the format to be operated in a cell. At first select the cells the contents of which you want to design and then follow the steps to design those contents.
Cells: This option consists the various features as follows:
1)      Number: This feature is used to set different format for number written in a cell. Here we can convert dolor in currency value.
2)      Alignment: This feature is used in alignment the contents in selected cell.
3)      Font : This feature is used to design the contain in selected cells
Border: This feature is used to set border lines in the selected cells of the worksheet.
Patterns: This feature is used to set background colors to the selected cells.
Protection: it is used to lock worksheet through protection.
Conditional Formatting: This is another import tool of MS Excel to operate financial data by setting different format to different cell basin on the financial information.
Row: This option is used to set the height of the row. It is used to hide or unhide the selected rows in a worksheet.
Column: This option is also used to adjust the column width and for hide or unhide the selected columns from worksheet.
Sheet: This option is used to change the name of a worksheet and the back ground. It is also used to hide or unhide the selected sheet. It consists thefollowingfeatures.
Rename: It is used to change the name of the worksheet.
Hide: It is used to hide the active worksheet.
Background: It is used to change the background of the worksheet.
Tab Colors: It is used to change the background color of active worksheet name.
Auto Format: This option is used to set a redesign designing to the selected of the worksheet.
Style: This option is used to set the format of each and very cell of the worksheet.
àHere we can set the format style, size, color, alignment and borders settings to the carious cells of a sheet.

Tools (Alt+T):
Spelling: It is used to set the spelling and grammar mistakes in a worksheet.
Research: This option is used to search for the meaning of any word form the available dictionary in the system.
Error check: This option detects the errors in executing a formula in a worksheet.
àWhen we click on this option it will provide two options for checking the error. They are:
1)      Automatic update: Here excel automatically sets the formula to calculate the value.
2)      Edit Formula: Here the user has to change or set the formula to calculate the value.
Speech: This option is used to record the speech of the user in relation to any statement designed in the excel. This speech can be delivered at any time while presenting the statement.
Shared Workbook: This option is used to share the worksheet through internet or network. Once we share any worksheet that can he operated in any location in a particular network.
Shared worksheet: This option is used to open the shared document from any location in a particular network or form the internet.
Track changes: This option is used to set the automatically for operating a particular document or worksheet, which is in sharing. Here we can set name, area to be operated by the user.
Compare and merge worksheets: This feature is used to add or compare the data shared in two worksheets or workbooks. But here the fields in those worksheets must be same, must consists similar type of data.
Protection: This option is used to protect the operation of worksheet by giving a password. This protection can be done indifferent level like.
Online collaboration: This option is used to execute a worksheet through internet,
Formula Auditing: This option is used to audit various formulas used in a worksheet. It will display the path of a formula and an error message where the formula is wrongly executed. It consists some auditing tools by using which user can trace or erase the formula.
Goal Seek: Typically we calculate formulas by supplying input values and then asking excel to calculate the formulas result with a data table. You can work the other way, or the reverse. You can supply a formula. The result you want it to return and then all of the input values, except for the one excel calculates for you. In effect excel works backward from the calculation result to get one of your inputs. Excel does the same thing with the help of goal seek.
Goal seek saves you from performing time- consuming trial and error analysis to seek a specific solution to a formula.
Let us take an example: we want to get 65% marks in examination. Total 6 subjects are there. Five subjects marks are known and the 6th subject mark is not known. Though we know the result of the calculation we can set one input value by Goal Seek.

Goal Seek
SUBJECTS
MARKS
ENG
45
MATH
76
MIL
83
SCIENCE
98
SOCIAL

COMP
65
TOTAL
367
AVG
61.17









Average: Click on AVG i.e. G42à go to toolsàgoal seekàclick on To valueàgive percentage(%)àselect By changing cellàclick on blank cell ex: “Social”àthen OK.
Scenarios: In Microsoft Excel, you can create and save sets of input values that produce different results as Scenarios. A scenario is a group of input values called changing cells saved with a name you provide. Each set of changing cells represents a set of what if assumption that you can apply to a work book model to see the effects on other parts of the model. You can define unto 32 changing cells per Scenario.
Creating a Scenario:
 Simple Interest

PRINCIPAL
2000
RATE
3
TIME
7
S.I
420






Simple Interest: =Principle*Rate*Time/100
Ex: =G48*G49*G50/100àEnter.
Scenarios: Go to ToolsàScenariosàClick on AddàGive scenarios name i.e. Principal, Rate or TimeàClick on changing cellàselect principle, rate or time i.e. G50, G51 or G52àClick OKàGive amountàOKàSelect the amountàClick on Show button.
Macro: This option is used to record some keystrokes in particular name. These keystrokes can be executed on any sheet of that particular workbook.
Add-Ins: This option is used to add some additional features to the worksheet for executing the financial data in more affective manner. Once we activate these features relating to particular software then it can able to execute the data from the software.

Data (Alt+D):
Sort: To arrange the records in either ascending or descending order of a field is called sorting.
àClick on the pointer on the column, on which you want to sort the list.
àClick on the sort button on the standard tool bar, there are two buttons. One is ascending button, which sorts in alphabetical order and next is descending order button, which sorts in reverse alphabetical order. Excel does not include the header row for sorting. If you select a range of cells within the list, Excel with only sort the selected range.
Table:-It is the multiplication of table.
Select the Dataà go to on Dataà click on Tableà click Row input cell ex: $A$1à click on Column input cell ex: $A$2à click OK button.
Filter:-Excel ha another records on criteria that à dataàfilteràClick on Auto filter.


Table
1





1





1
1
2
3
4
5
1
1
2
3
4
5
2
2
4
6
8
10
3
3
6
9
12
15
4
4
8
12
16
20
5
5
10
15
20
25










Comments