Introduction:- MS-Excel is the Spreadsheet Software. It is popular software used
extensively for entering, calculating, manipulating and analysis large volumes
of numerical data.
Microsoft Excel is the most popular software for mathematical calculations. It provides facilities to spread data in tabular form consisting of rows and columns. This way data can be organized easily. Worksheet is also called Spreadsheet or Electronic sheet.
Microsoft Excel is the most popular software for mathematical calculations. It provides facilities to spread data in tabular form consisting of rows and columns. This way data can be organized easily. Worksheet is also called Spreadsheet or Electronic sheet.
Advantage of MS-Excel
Automatic Calculations:- Excel provides many in-built functions for 'quick' and 'accurate'
calculations. Result update automatically whenever data is changed.
Filtration of Required Data:- We can extract required information from a large worksheet by
specifying the specifying the filtration criteria and seeing only the selected data.
Graphical Representation of Data:- Excel helps in representing our data graphically through charts; It
also helps us to 'design' our data to make it look attractive.
Automatic Analysis:- Different option offered by the Excel us to play with numbers and 'analysis' the result by changing them in different ways.
Automatic Analysis:- Different option offered by the Excel us to play with numbers and 'analysis' the result by changing them in different ways.
The different components of MS-Excel one by one
Workbook:- A file in MS-Excel is called Workbook. Each Excel file (Workbook)
has a different but same extension of .xls.
Worksheet:- Each Workbook in
MS-Excel is made of a number of Worksheets. The maximum limit of the worksheet
in a workbook is 255.
Rows:- Rows are horizontal collection of cells that are represented by numbers from 1,2,3.......to 65,536. This means there are 65,536 rows in a Worksheet.
Columns:- Columns are vertical collection of cells that are represented by alphabets like A,B....Z, then AA,AB......AZ.....till 256 columns are 'labelled'. Therefore, there are 256 columns in a Worksheet.
Cells:- Cell is the 'intersection' of a rows and column. Each cell has 'label' (Name) called the cell address by which it is uniquely represented. For Ex- a cell formed at the intersection of column C and row 8 will have 'Cell Address' as C8.
It is used to store data. There are 65,536*256=16777216 cells in a single worksheet.
Active Cell:- Active Cell is the cell with a 'dark border' around it. We work in one 'active cell' at a time.
Cell Range:- Cell Range is a 'continuous' block of cells represented by the address of first and the last cells.
Functions:- Functions are 'pre-predefined' formula. Functions has three parts.1. Equal Sign ( = )
2. Function Name
3. Function Argument or Function Parameter
Ex:- =sum(a1:a5) Here, sum is the function name and (a1:a5) its argument.
Note:- While writing a function or formula in a cell or formula bar always begin with '=' sign.
Operators:- A formula is mathematical expression which contains one or more operators. The type of calculation you want to perform is specified by an operator. Arithmetic operator and Comparison operator are two types of operator.
Arithmetic Operator:-
Arithmetic Operator:-
Note:- The result of the logical test argument is only ever "True" or "False".
Formula That Produce Error Values
Formula Excel displays an error value in a cell when it can not calculate the formula for that cell properly.
How To Start MS-Excel:-
Start-->All Program-->Microsoft Office-->Microsoft Excel 2003 OR
Run-->Excel
Note:- Windows Key + R = To open run dialog box
In Excel:-
Creating Custom Lists:- Custom Lists are 'auto-fill list' that have been created according to our own requirements.
To get options dialog box click on Tools -->Options-->Custom List
Editing Data:- We can edit the data that is 'entered' in the particular cell by using the F2 function key or by 'double clicking' the cell to 'activate' it to make changes to it's contents.
Selecting Data:- A Continuous Block of Cells can be 'selected' using the mouse multiple ranges can be 'selected',using the Ctrl key and the Mouse.
Note:- Press the Escape key typed data will not be not be entered in the cell.
Date & Time Function
1.Today:- Shows the current date.
Syntax:- =Today()<--
2. Now:- Shows the current date and time.
Syntax:- =Now()<--
3. Date:- This function creates a real date by using three normal numbers.
Syntax:- =Date(year,month,day)<--
4. Day:- Returns the day of month, a number from 1 to 31.
Syntax:- =Day(serial_number)<--
5. Hour:- Returns the hour of a time value.
Syntax:- =Hour(serial_number)<--
6. Minute:- Returns the minute of a time value.
Syntax:- =Minute(serial_number)<--
7. Second:- Returns the second of a time value.
Syntax:- =Second(serial_number)<--
Note:- Update the clock press the function key F9.
8. Time:- This function will convert three separate numbers to an actual time.
Syntax:- =Time(Hour,Minute,Second)<--
9. Days360:- Shows the number of days between two dates base on a 360-day year (twelve 30-day months).
Use this function if your accounting system is based on twelve 30-day months.
Syntax:- =Days360(StartDate,EndDate,TRUE or FALSE)<--
TRUE:- Use this for European accounting systems.
FALSE:- Use this for USA accounting systems.
Note:- The calculation does not include the last day. The result of using 1-jan-98 and 5-jan-98 will give a result of 4. To correct this add 1 to the result.
=DAYS360(Start,End,TRUE)+1
10. Weekday:- This function shows the day of the week from a date. The result shown as a number 1 to 7.
Syntax:- =Weekday(serial_number,[return_type])<--
Note:- Return Type 1 represents week of day start from Sunday.
Return Type 2 represents week of day start from Monday.
If no number is specified. Excel will use 1.
11. Year:- This function extracts the year number from a date.
Syntax:- =Year(Date)<--
12. Datedif:- This function calculates the difference between two dates. It can show the result in days,months or years.
Syntax:- =Datedif(FirstDate,SecondDate,"Interval")<--
These are the available intervals.
"Y" The number of complete years.
"M" The number of complete months.
"D" The number of days.
"MD" The difference between the days. (months, and years are ignored).
"YM" The difference between the months. (days and years are ignored).
"YD" The difference between the days. (years and dates are ignored).
Ex:- To calculate the age of a person that birthday is 28-June-1991(B2).
Year Lives =datedif(B2,today(),"y")<--
Month =datedif(B2,today(),"ym")<--
Day =datedif(B2,today(),"md")<--
Syntax:- =Hour(serial_number)<--
6. Minute:- Returns the minute of a time value.
Syntax:- =Minute(serial_number)<--
7. Second:- Returns the second of a time value.
Syntax:- =Second(serial_number)<--
Note:- Update the clock press the function key F9.
8. Time:- This function will convert three separate numbers to an actual time.
Syntax:- =Time(Hour,Minute,Second)<--
9. Days360:- Shows the number of days between two dates base on a 360-day year (twelve 30-day months).
Use this function if your accounting system is based on twelve 30-day months.
Syntax:- =Days360(StartDate,EndDate,TRUE or FALSE)<--
TRUE:- Use this for European accounting systems.
FALSE:- Use this for USA accounting systems.
Note:- The calculation does not include the last day. The result of using 1-jan-98 and 5-jan-98 will give a result of 4. To correct this add 1 to the result.
=DAYS360(Start,End,TRUE)+1
10. Weekday:- This function shows the day of the week from a date. The result shown as a number 1 to 7.
Syntax:- =Weekday(serial_number,[return_type])<--
Note:- Return Type 1 represents week of day start from Sunday.
Return Type 2 represents week of day start from Monday.
If no number is specified. Excel will use 1.
11. Year:- This function extracts the year number from a date.
Syntax:- =Year(Date)<--
12. Datedif:- This function calculates the difference between two dates. It can show the result in days,months or years.
Syntax:- =Datedif(FirstDate,SecondDate,"Interval")<--
These are the available intervals.
"Y" The number of complete years.
"M" The number of complete months.
"D" The number of days.
"MD" The difference between the days. (months, and years are ignored).
"YM" The difference between the months. (days and years are ignored).
"YD" The difference between the days. (years and dates are ignored).
Ex:- To calculate the age of a person that birthday is 28-June-1991(B2).
Year Lives =datedif(B2,today(),"y")<--
Month =datedif(B2,today(),"ym")<--
Day =datedif(B2,today(),"md")<--
Mathematical Function
1. Abs:- Returns the absolute value of a number. The absolute value of a number is the number without its sign.
Syntax:- =Abs(number)<--
2. Int:- This function round a number down to the nearest whole number.
Syntax:- =Int(number)<--
Ex:- =int(28.89)<-- Result is 28 =int(-2.1)<-- Result is -3
3. Even:- This function rounds a number up the nearest even whole number.
Syntax:- =Even(number)<--
Ex:- =Even(28.45)<-- Result is 30 =Even(1)<-- Result is 2 =Even(2)<-- Result is 2
4. Odd:- This function rounds a number up the nearest odd whole number.
Syntax:- =Odd(23.34)<-- Result is 25 =Odd(24)<-- Result is 25
5. Sum:- This function creates a total from a list of numbers. It can be used either horizontal or vertical.
Syntax:= =Sum(Range1,Range2,Range3,........through to Range30)<--
Note:- 1. You can total using the Alt and = keys.
2. You can use the field names into the formula.
Tools-->Options-->Calculation-->Accept labels in formula
6. Sumif:- This function adds all numbers in a range of cells, based on given criteria.
Syntax:- =Sumif(range,criterial,[sum_range])<--
Ex:- =Sumif(A1:A5,">=20")<--
7. Product:- This function multiples a group of numbers together.
Syntax:- =Product(Range1,Range2,Range3,.........through to Ragne30)<--
8. Fact:- This function calculates the factorial of a number.
Syntax:- =Fact(number)<--
9. Power:- This function raises a number to user specified power.
Syntax:- =Power(number,power)<--
Note:- To get same result using the "^" (caret) operator i.e. exponentiation.
10. LCM:- This function calculate the Least Common Multiple, which is the smallest number that can be divided by each of the given numbers.
Syntax:- =LCM(Number1,Number2,Number3,..........through Number29)<--
11. Mod:- This function calculates the remainder after a number has been divided by another number.
Syntax:- =Mod(number,divisor)<--
12. Pi:- This function returns the value of pi.
Syntax:- =Pi()<-- Result is 3.141593
13. Round:- This function rounds a number to a specified amount of decimal places.
If 0 is used the number is rounded to the nearest whole number. If a negative amount of rounding is used the figures to the left of the decimal point are rounded.
Syntax:- =Round(number,num_digits)<--
Ex:- =Round(3.141593,2)<--Result is 3.14 =Round(29.5454,0)<-- Result is 30
=Round(-28.14582,2)<-- Result is -28.15 =Round(21.5,-1)<-- Result is 20
14. Rounddown:- This function rounds a number down to a specified amount of decimal places.
If 0 is used the number is rounded down to the nearest whole number. If a negative amount of rounding is used the figures to the left of the decimal point are rounded.
Syntax:- =Rounddown(number,num_digits)<--
Ex:- =Rounddown(23.4954,2)<-- Result is 23.49 =Rounddown(23.4954,0)<-- Result is 23
15. Roundup:- This function rounds a number up to a specified amount of decimal places.
If 0 is used the number is rounded up to the nearest whole number. If a negative amount of rounding is used the figures to the left of the decimal point are rounded.
Syntax:- =Roundup(number,num_digits)<--
Ex:- =Roundup(28.568,2)<-- Result is 28.57 =Roundup(20.45.0)<-- Result is 21
16. Trunc:- This function removes the decimal part of a number. It does not actually round the number.
Syntax:- =Trunc(number,num_digits)<--
Ex:- =Trunc(45.8765,2)<-- Result is 45.87
17. Roman:- This function produces a number shown as Roman numeral in various formats.
Syntax:- =Roman(number)<--
18. Sumproduct:- This function uses at least two columns of values. The values in the first column are multiplied with the corresponding values in the second second column. The total of all the values is the result of the calculation.
Syntax:- =Sumproduct(Range1,Range2,Range3,....through to Range30)<--
Syntax:- =Int(number)<--
Ex:- =int(28.89)<-- Result is 28 =int(-2.1)<-- Result is -3
3. Even:- This function rounds a number up the nearest even whole number.
Syntax:- =Even(number)<--
Ex:- =Even(28.45)<-- Result is 30 =Even(1)<-- Result is 2 =Even(2)<-- Result is 2
4. Odd:- This function rounds a number up the nearest odd whole number.
Syntax:- =Odd(23.34)<-- Result is 25 =Odd(24)<-- Result is 25
5. Sum:- This function creates a total from a list of numbers. It can be used either horizontal or vertical.
Syntax:= =Sum(Range1,Range2,Range3,........through to Range30)<--
Note:- 1. You can total using the Alt and = keys.
2. You can use the field names into the formula.
Tools-->Options-->Calculation-->Accept labels in formula
6. Sumif:- This function adds all numbers in a range of cells, based on given criteria.
Syntax:- =Sumif(range,criterial,[sum_range])<--
Ex:- =Sumif(A1:A5,">=20")<--
7. Product:- This function multiples a group of numbers together.
Syntax:- =Product(Range1,Range2,Range3,.........through to Ragne30)<--
8. Fact:- This function calculates the factorial of a number.
Syntax:- =Fact(number)<--
9. Power:- This function raises a number to user specified power.
Syntax:- =Power(number,power)<--
Note:- To get same result using the "^" (caret) operator i.e. exponentiation.
10. LCM:- This function calculate the Least Common Multiple, which is the smallest number that can be divided by each of the given numbers.
Syntax:- =LCM(Number1,Number2,Number3,..........through Number29)<--
11. Mod:- This function calculates the remainder after a number has been divided by another number.
Syntax:- =Mod(number,divisor)<--
12. Pi:- This function returns the value of pi.
Syntax:- =Pi()<-- Result is 3.141593
13. Round:- This function rounds a number to a specified amount of decimal places.
If 0 is used the number is rounded to the nearest whole number. If a negative amount of rounding is used the figures to the left of the decimal point are rounded.
Syntax:- =Round(number,num_digits)<--
Ex:- =Round(3.141593,2)<--Result is 3.14 =Round(29.5454,0)<-- Result is 30
=Round(-28.14582,2)<-- Result is -28.15 =Round(21.5,-1)<-- Result is 20
14. Rounddown:- This function rounds a number down to a specified amount of decimal places.
If 0 is used the number is rounded down to the nearest whole number. If a negative amount of rounding is used the figures to the left of the decimal point are rounded.
Syntax:- =Rounddown(number,num_digits)<--
Ex:- =Rounddown(23.4954,2)<-- Result is 23.49 =Rounddown(23.4954,0)<-- Result is 23
15. Roundup:- This function rounds a number up to a specified amount of decimal places.
If 0 is used the number is rounded up to the nearest whole number. If a negative amount of rounding is used the figures to the left of the decimal point are rounded.
Syntax:- =Roundup(number,num_digits)<--
Ex:- =Roundup(28.568,2)<-- Result is 28.57 =Roundup(20.45.0)<-- Result is 21
16. Trunc:- This function removes the decimal part of a number. It does not actually round the number.
Syntax:- =Trunc(number,num_digits)<--
Ex:- =Trunc(45.8765,2)<-- Result is 45.87
17. Roman:- This function produces a number shown as Roman numeral in various formats.
Syntax:- =Roman(number)<--
18. Sumproduct:- This function uses at least two columns of values. The values in the first column are multiplied with the corresponding values in the second second column. The total of all the values is the result of the calculation.
Syntax:- =Sumproduct(Range1,Range2,Range3,....through to Range30)<--
Statistical Function
1. Average:- This function calculates the average from a list of numbers.
Syntax:- =Average(Range1,Rang2,Range3,...........through to Range30)<--
2. Max:- This function picks the highest value from a list of data.
Syntax:- =Max(Range1.Range2,Range3,............through to Range30)<--
3. Min:- This function picks the lowest value from a list of data.
Syntax:- =Min(Range1,Range2,Range3,............through to Range30)<--
4. Large:- This function examines a list of values and picks the value at a user specified position in the list.
Syntax:- Large(List of Numbers To Examine,Position To Pick From)<--
5. Small:- This function examines a list of values and picks the value at a user specified position in the list.
Syntax:- =Small(List of Numbers To Examine, Position To Pick From)<--
