INTRODUCTION
MS-Excel is made of grid of rows and columns, which know as spreadsheet or electrician sheet or worksheet. In a Worksheet the intersection of row and column is called cell. In it columns are alphabetically labeled as A,B,C……..Z and then AA,AB,…..AZ and then BA,BB,….BZ and so on to IA,IB….IV, it is last column(IV) in excel sheet so it consists of Total 256 columns in it. And rows are numerically numbered as 1,2,3,4,5………..65,536 rows. So one worksheet consists 256 columns and 65,536 rows and there are 1,67,77,216 cells in it. Like this there are 255 worksheet in one workbook. And MS-Excel can N number of workbook.
WORKBOOK(N)
WORKSHEET(1)
ROWS(65,536) AND COLUMNS(256)
CELLS(1,67,77,216)
WORKBOOK(1)
WORKSHEETS(255)
Fig : Representation of Workbooks and Worksheets in excel
To work with MS-EXCEL from Windows environment.
START ƒ PROGRAMS ƒ MS-EXCEL
The environment of the ms-excel is as
Title Bar Menu Bar Standard Toolbar Columns
Sheet name cell(intersection of row and column)
Rows
This are important parts of ms-excel 1) Title Bar 2) Menu Bar 3) Standard Toolbar(optional) 4)Worksheet Name 5)Rows 6) Columns 7) Scroll Bars(Horizontal and Vertical) 8) Name Box 9) Formula Bar
1) Title Bar
2) Menu Bar
Some of the menus of Excel are as follows :
1) File Menu 2) Edit Menu 3) View Menu 4) Insert Menu 5) Format
Menu 6) Tools Menu 7) Data Menu 8) Window Menu 9) Help
Menu.
3) Standard Toolbar which optional. It consists all shortcut icons to work
with files and cells.
4) Name Box : It display the reference of cursor pointer in the ms-excel
sheet(spreadsheet), it displays the Address of the cell where the cursor
pointer is moving that means cell address(intersection of row and column).
Example like A1. Here Column Alphabet is followed by Row number. A is
the Column Alphabet and 1 is Row Number.
5) Sheet Name : This are worksheet names that there in one workbook. The
default number of sheets in the excel can be controlled by user. The
minimum is 1 sheet and maximum is 255 sheets display in it. To adjust the
number of sheets go to tools menu in that select options (option) in it, a
window will be displayed in that select General option it there will be
option called sheets in new workbook with textbox with increment and
decrement button to it. By default there given name as Sheet1 and Sheet2 so
on up to Sheet255. Later sheet can be renamed also.
Sheet name list
6) Formula Bar : The Data (number or Characters or Strings or Used for
calculations also). First move your cursor pointer to the place you want to
enter the data in it and select Formula bar and enter the data that will
display in the cell where your cursor pointer there. Or if enter the data in the
cell directly also the data is visual in Formula Bar.
7) Scroll Bar : The Scroll Bar is used to move the sheet vertical and
horizontally.
In MS-Excel we have 9 menus in it, they are File, Edit, View, Insert, Format, Tools, Data, Window and Help.
This menu are helpful in doing the common work within the worksheet the option of the different type of menu are as shown below.
File Menu Options Edit Menu Options View Menu Opt
Insert Menu Option Format Menu Option Tools Menu Option
Data Menu Option
Fig : The Workspace Area(Worksheet)
The MS-Excel is made of Grid Lines(Horizontal and Vertical), they are know as Rows and Columns, they are used to enter the data in the cell. The data are aligned properly in the cell.
Note : This gridlines are not visible while we print the worksheet or see a print preview at that time this gridlines will not visible. They are only for enter data in the proper way in the cell so they are aligned properly.
To move around the spreadsheet use keyboard arrow keys like ƒ¨ to move Right side of sheet, ƒ to move left side of sheet to move down side of the sheet and to move up side of sheet. To move one screen of sheet up or down use scroll bars. To move the desired cell type the cell address in the Name Box it take your cursor pointer to desired cell.
In excel the number are Right justified or Aligned ( i.e. means that numbers are displayed to Right corner of the cell) and Character are left justified or Aligned ( i.e. means that numbers are displayed to right corner of the cell).
Number in the cell Character in the cell
To enter the formula in excel we have special symbol it is
= (Equal to symbol), it used for all types of arithmetic and logical operations on the values and with cell address also. Suppose we want to add to values of cell A1 and B1 and result must be displayed in the C1 cell then first enter 2 values in the Cell A1 and B1 after that move your cursor to cell C1 and there type =Cell A1 value + cell B1 value. For example A1 cell has value 10 and B1 cell has value 20 then C1 cell will have result value as 30(10+20=30). We can also add the values using the cell address name also. Like this A1+B1. The advantage of using cell address is that we can copy the same formula to the other cell also. We can enter date and time into the cell in ms-excel.
To insert a new worksheet in the workbook the following step is involved.
INSERT ƒ WORKSHEET
A new worksheet will in introduced in the workbook. The sheet name will given in the increase order of the sheet. For example there are sheet1, sheet 2, sheet3 if i want a new sheet then the sheet name will be sheet4.
FORMATTING
In excel we can increase and decrease the size of the column and row. The increasing and decreasing of row and column can done with help of mouse and keyboard also. To work with the mouse move the mouse to required column or row last to increase or decrease the size by dragging the mouse left and right for column and up and down for row. The default the width of the column size is 8.43 (or 64 pixels) and row height is 12.75. The size of column width and row height can be changed form the keyboard also the steps are as follows :
To increase the width of the column is like this
FORMAT ƒ COLUMN ƒ WIDTH
A small window will be displayed like this in which we can enter the size and change it.
To increase the height of the row is like this
FORMAT ƒ ROW ƒ HEIGHT
A small window will be displayed like this in which we can enter the size and change it.
Note : Without distribution width of cell we can enter 8 numbers and 7 characters in it(Font Size is 12). It differs from Font Type ,Style and Size also.
To format the data (number or character in the cell) the following steps are required to work with
Step 1: enter the data in the cell.
Step 2: FORMAT ƒ CELL
Step 3: A small window will be displayed it look like this
To format the number select number option in it. It has internal options like general , number, currency, accounting, date, time, percentage, fraction, scientific, text, special and custom.
To align the data in the text we will use Alignment option in it. To change the font type, style , size, color, effects and underline we will use Font option in it. To draw the border to the cell as table form we will select border option in it. To fill the color to cell background we will select the pattern option in it. To lock the formula from or being copied to other cell we will select the protection option in it.
As per the requirement of formatting we will use the option in it.
To format the numbers in the excel that means increasing the decimal point number to the number or using (,) comma after the hundred’s place or type of values to be used like no negative values. Steps are as followed to format the number:
Step 1: Enter the number(s) in the cell.
Number(s) in the cell before
Step 2: High light the cell which you want to format using shift button +
arrow keys(up, down, left, right) or with mouse button dragging.
High lighted number in the cell to be formatted
Step 3: FORMAT ƒ CELL
A small format cells window will be displayed in that select number option menu and in that again number option and then format the number.
In this i am
formatting the number’s
decimal value to 2
places and allowing
only positive values,
at last press ok
button on it.
The result will look like this after formatting the number(s) in the cell
Number(s) after formatted is as in the cell
To format the number to currency format the following steps are required :
Step 1: Enter the number(s) in the cell as required.
Step 2: High light the number(s) in cell to be formatted.
Step 3: FORMAT ƒ CELL
Step 4: In the format cells select currency option from the number menu then select the number of decimal places required after the number and symbol required before the number as prefix to it( like dollar symbol $, pound symbol £ and etc,,. And the type of value negative or positive values only.
Numbers before formatting Number after formatting
-123
125
-45
100
$123.46
$125.00
$45.00
$100.00
Number formatted with 2 decimal values and $ symbol and positive values only.
To format the number to percentage format the following steps are required :
Step 1: Enter the number(s) in the cell as required.
Step 2: High light the number(s) in cell to be formatted.
Step 3: FORMAT ƒ CELL
Step 4: In Format Cells select Number option. In it select percentage option then select the number of decimal places option to introduce number of decimal places after the normal numbers.
The result will be multiply of 100 of cell value, with number of decimal values as select and at last of result we will have percentage symbol(%).
Numbers before formatting Number after formatting
100
200
300
400
10000.00%
20000.00%
30000.00%
40000.00%
To format the Date the following steps are required :
Step 1: Enter the Date(s) in the cell as required.
Step 2: Highlight the Date(s) in cell to be formatted.
Step 3: FORMAT ƒ CELL
Step 4: In the Format Cells select Date option from number menu. In the Date option we have 2 sub option in it 1) Type : it specifies which date format style we want to format it. 2) Location to change according the place like u.s.a., u.k., France and etc,,
Date(s) before formatting Date(s) after formatting
5/11/2005
5/12/2005
5/13/2005
5/14/2005
5/15/2005
11-May-05
12-May-05
13-May-05
14-May-05
15-May-05
Few of the options of the date
type sub options are like this.
To Format the Text in the cell(s) the following steps are involved:
Step 1: Enter the Data(s) in the cell as required.
Step 2: High light the Data(s) in cell to be formatted.
Step 3: FORMAT ƒ CELL
Step 4: In the format cells window select Font menu option. In it sub option are will be like this.
1) Font Type 2) Font Style 3) Font Size 4) Color 5) Underline Style 6) Effects to text 7) Preview.
Get Help With Your Essay
If you need assistance with writing your essay, our professional essay writing service is here to help!
The Font menu has option called Font which name of the Font type like Arial, courier new, time new Roman, Arial Narrow and etc, and 4 Font Style like Regular, Bold, Italic, Bold Italic, and then Font Size from 1 to 100 and etc,,, and then Underline style , Color of the Font, and then Special effects like Strikethrough, Superscript and Subscript. And one place to see effects all that is Preview place. As per you requirement select above option in it.
Text before formatting Text after formatting
RAMA
RAVI
RAMA
RAVI
It font type is Arial, Now font type is Courier new size is 10
Font size is 10 and according to type and style bold and
Normal style. effects is strikethrough.
Note : The Font size and style will change according to the corresponding selection of the Font Type.
To format data in cell with proper Alignment involves following steps:
Step 1: Enter the Data in the cell as required.
Step 2: High light the Data in cell to be formatted.
Step 3: FORMAT ƒ CELL
Step 4: In format cells window select Alignment Menu option. The window look like this
In this we can align the horizontal and vertical text can be controlled and orientation and Text direction are the sub option in it.
Text Alignment Horizontal has option in it like general, left(indent), center, Right(indent), fill, justify, center Across selection, distributed(indent) and Vertical has option in it like Top, bottom, center, justify, distributed. As per the selection the text will be aligned in the cell.
The text control is used to control the text in the cell, wrap text wrappers the text not allowing it move out the cell last border and forcing it to start in the next line in the same cell, shrink to fit if the entered text size more than the cell size than it will shrink (reduce the size of text and fit it) in the cell, merge cells is used to merge combine the group of cells into one total cell.
To change the direction of text in the cell we use the orientation option in the excel according to the degrees given to the text in the cell it will change it directions.
Note : The formatting of the Font can also done by the standard tool bar icons also but is shortcut way.
To format data in cell with proper Border involves following steps:
Step 1: Enter the Data in the cell as required.
Step 2: High light the Data in cell to be formatted.
Step 3: FORMAT ƒ CELL
Step 4: In the format cells we have border menu to draw border to the cell as per the you selection of type in it. The window look like this:
It has sub option like line ƒ style and color presetƒ none or outline or inside
Border
Data before border to cell Data after border to cell
slno
name
age
1
rama
22
2
ravi
23
3
ramesh
25Slno name age
1 rama 22
2 ravi 23
3 rana 25
As per the style and color of line and presets and border selected the border will be drawn to the corresponding cell which are highlighted only.
ENTER NUMBER IN THE MS-EXCEL
In ms-excel we can fill the series of number in the rows or columns. The number can also be filled automatically in the rows or columns. To enter the number automatically without the help of menu bar option. To do this first we must enter 2 number into consecutive rows or columns like this as shown the below figure. Then highlight that two consecutive rows or columns, then make your
Mouse Pointer as fill handler something like this and then drag the mouse pointer to the sequences of number to be generated or up to the required row or column limit. The number generated is by the difference of the two number in the above fig difference is 1 then generated series is like this
Like this we can generate the series of number in the ms-excel, the generated number is the difference of the second number minus the first number 10 20 then difference is 10 number sequence will be 10 20 30 40 50 and etc,,, .
If we want to generate through the menu options then we require to following the way. First step enter the number in cell that is the starting number in it to generate the series of number. Second step go to Edit menu in that select Fill option and in it select sub option Series a window will be displayed like this
In the window the type Linear means the number will be generated in sum(addition) of the previous no in the cell plus the step value up to the stop value or up to the selected or highlighted range in the worksheet, Growth is the multiplication of the cell number into the step up to the stop value or up to the selected or highlighted range in the worksheet, date to generate date on the data unit depend, AutoFill will fill the number automatically if but in this option no step value or stop value is required it will fill the number automatically up to the highlighted cell in the worksheet. To generate the number with have second number in the cell then use the trend option the above window
The option to series option
EDITING
The editing in excel and be done by press function F2 or click on the cell the mouse pointer will be displayed on the formula bar or select the cell which we want to editing work in it and then go to formula bar and press it the formula bar will be highlighted then we can do the modification to it.
CALCULATIONS
In excel calculation can be done in 2 ways 1)one is called formula method this can be done by using the expression of the values directly or by using the cell address reference. For doing this the symbol required is = which the prefix symbol used for any type of formula calculations
For example : =10+20*30
=A1+B1*C1
In the above excel sheet we have written the formula with the help of cell address and the result is shown the cell D1
Using calculation we have 2 type of cell reference, they are Absolute cell reference and other is Relative cell reference. The first in it used is directly by specifying the cell address directly like A1, B2 with symbol like =A1+B1*C2. The result of this cell can be copied to other cell where every this formula is dragged with the mouse. The second in it used is indirectly by specifying the cell address the each cell address has $(dollar) symbol in front of column alphabet and row number for example (A1) is represented in it like $A$1. If it used for the calculation the result of can be found in respective cell address but it cannot be copied to other cell because the result of first cell value will be copied to other cell as it is.
FUNCTIONS
The second method is Function method. In excel we have few in built functions which are also used for calculating the values in the cell but they are restricted to one type of calculations only like we have sum functions to find the sum of values in the given cell address or direct values given within the () of the sum functions.
For example =sum(a1+b1+c1) or =sum(a1:e1) or =sum(10+20+30)
To have functions in excel we have follow this step.
Insert ƒ Function (with the symbol to it as fx )
When we select this option a window will be displayed it looks like this:
This window(Paste Function) has 2 parts on is the Function Category part which as name of all main category of functions like Financial, Math & Trig, Data & Time, Statistical and etc. It has second window also in it we can see the sub function name of the select main function category like if we selected Math & Trig the sub function will displayed like sum, sin, tan, cos, abs and etc,,.
1)If consider the category as Math & Trig its few sub functions are follows:
slno
Function Name
Syntax
Purpose
1
Abs
Abs(number)
Returns the absolute value of a number, a number without its sign
2
Cos
Cos(number)
Returns the cosine of an angle
3
Exp
Exp(number)
Returns e raised to the power of a given number
4
Fact
Fact(number)
Returns the factorial of a number, equal to 1*2*3*….*number
5
Power
Power(number,power)
Returns the result of a number raised to a power
6
Sin
Sin(number)
Returns the sine of an angle
7
SQRT
SQRT(number)
Returns square root of a number
8
Sum
Sum(number1,number2,
……. NumberN)
Add all the number in a range of cells
9
Tan
Tan(number)
Returns the tangent of an angle
Example
Result
Abs(-23.456)
23.456
Cos(90)
-0.44807
Exp(1)
2.718282
Fact(5)
120
Power(10,2)
100
Sin(0)
0
SQRT(4)
2
Sum(10,20,30)
60
Tan(45)
1.6197752
2)If consider the category Statistical as its few sub functions are follows:
slno
Function Name
Syntax
Purpose
1
Average
Average(number1,number2,
…..)
Returns the average(arithmetic mean) of its arguments, which can be numbers or names or arrays or reference that contains number
2
Count
Count(Value1,Value2,….)
Counts the number of cell that contain numbers and numbers within the list of arguments.
3
Large
Large(array,k)
Returns the k-th largest value in a data set
4
Max
Max(number1,number2,…)
Returns the largest value in a set of values. Ignores logical values and text
5
Min
Min(number1,number2,….)
Returns the smallest value in a set of values. Ignores logical values and text
6
Small
Small(array,k)
Returns the k-th smallest value in a data set
7
Mode
Mode(number1,number2,…)
Returns the most frequently occurring or repetitive value in an array or range of data
8
Median
Median(number1,number2,..)
Returns the median or the number in the middle of the set of given numbers.
Example
Result
Average(10,20,30)
20
Count(10,20,30)
3
Large({10,20,30},2)
20
Max(10,20,30)
30
Min(10,20,30)
10
Small({10,20,30},1)
10
3) If consider the category Date and Time as its few sub functions are follows:
slno
Function Name
Syntax
Purpose
1
Date
DATE(year,month,day)
Returns the serial number that represents a particular date.
2
Today
TODAY( )
Returns the serial number of the current date. The serial number is the date-time code used by Microsoft Excel for date and time calculations.
3
Now
NOW( )
Returns the serial number of the current date and time.
4
Year
YEAR(Date)
Returns the year corresponding to a date. The year is returned as an integer in the range 1900-9999.
5
Month
MONTH(Date)
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).
6
Day
DAY(Date)
Returns the day of a date, represented by a serial number. The day is given as an integer ranging from 1 to 31.
7
Hour
HOUR(Time)
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.).
8
Second
SECOND(Time)
Returns the seconds of a time value. The second is given as an integer in the range 0 (zero) to 59.
9
Minute
MINUTE(Time)
Returns the minutes of a time value. The minute is given as an integer, ranging from 0 to 59.
Example
Result
Today()
2/20/05
Now()
2/20/05
Hour(11:45:45)
11
Minute(11:45:45)
45
Day(2/20/05)
20
Month(2/20/05)
2
Year(2/20/05)
2005
Second(11:45:58)
58
4) If consider the category Logical as its few sub functions are follows:
slno
Function Name
Syntax
Purpose
1
IF
IF(logical_test,value_if_true,value_if_false)
Returns one value if a condition you specify evaluates to TRUE and another value if it evaluates to FALSE.
Logical_test: is any value or expression that can be evaluated to TRUE or FALSE.
Value_if_true: is the value that is returned if logical_test is TRUE.
Value_if_false: is the value that is returned if logical_test is FALSE
2
False
FALSE( )
Returns the logical value FALSE.
3
True
TRUE( )
Returns the logical value TRUE.
4
AND
AND(logical1,logical2, …)
Returns TRUE if all its arguments are TRUE; returns FALSE if one or more arguments is FALSE.
5
OR
OR(logical1,logical2,…)
Returns TRUE if any argument is TRUE; returns FALSE if all arguments are FALSE.
6
NOT
NOT(logical)
Reverses the value of its argument. Use NOT when you want to make sure a value is not equal to one particular value.
The IF functions can be used with the help of the Comparison operators, they are as follows :
You can compare two values with the following operators. When two values are compared by using these operators, the result is a logical value, either TRUE or FALSE.
Comparison
operator
Meaning
Example
= (equal sign)
Equal to
A1=B1
> (greater than sign)
Greater than
A1>B1
< (less than sign)
Less than
A1 Greater than or equal to
A1>=B1
<= (less than or equal to sign)
Less than or equal to
A1<=B1
<> (not equal to sign)
Not equal to
A1<>B1
The IF function can use the AND , OR and NOT logical function it .
The details of AND Function is as follows: AND(logical1,logical2, …)
Logical1, logical2, … are 1 to 30 conditions you want to test that can be either TRUE or FALSE.
The arguments must evaluate to logical values such as TRUE or FALSE, or the arguments must be arrays or references that contain logical values.
If an array or reference argument contains text or empty cells, those values are ignored.
If the specified range contains no logical values, AND returns the #VALUE! error value.
The details of OR Function is as follows: OR(logical1,logical2,…)
Logical1,logical2,… are 1 to 30 conditions you want to test that can be either TRUE or FALSE.
The arguments must evaluate to logical values such as TRUE or FALSE, or in arrays or references that contain logical values.
If an array or reference argument contains text or empty cells, those values are ignored.
If the specified range contains no logical values, OR returns the #VALUE! error value.
You can use an OR array formula to see if a value occurs in an array. To enter an array formula, press CTRL+SHIFT+ENTER in Microsoft Excel for Windows or +ENTER in Microsoft Excel for the Macintosh.
Example
Result
IF(10>20,10,20)
20
IF(10<20,10,20)
10
IF(AND(10>20,10<30),10,20)
20
IF(OR(10>20,10<30),10,20)
10
IF(NOT(10>20),10,20)
10
IF(NOT(10<20),10,20)
20
IF(10>20,”true”,”false”)
false
IF(100<200,"rama","krishna")
rama
5) If consider the category Text as its few sub functions are follows:
slno
Function Name
Syntax
Purpose
1
Concat
enate
Joins several text strings into one text string.
2
Text
Converts a value to text in a specific number format.
3
Trim
Removes all spaces from text except for single spaces between words. Use TRIM on text that you have received from another application that may have irregular spacing.
4
Lower
Converts all uppercase letters in a text string to lowercase.
5
Upper
Converts text to uppercase.
6
Len
LEN returns the number of characters in a text string.
7
Proper
Capitalizes the first letter in a text string and any other letters in text that follow any character other than a letter. Converts all other letters to lowercase letters.
8
Mid
MID returns a specific number of characters from a text string, starting at the position you specify, based on the number of characters you specify.
9
Code
Returns a numeric code for the first character in a text string. The returned code corresponds to the character set used by your computer.
Example
Result
CONCATENATE (“rama”,”krishna”)
ramakrishna
Upper(“ramakrishna”)
RAMAKRISHNA
Lower(“RAMAKRISHNA”)
ramakrishna
Len(“Ramakrishna”)
11
Trim(” Rama “)
Rama
Text(2.715, “$0.00”)
$2.72
MID(“I am a good boy”,8,15)
good boy
Code(“A”)
65
You can create either an embedded chart or a chart sheet.
Select the cells that contain the data that you want to appear in the chart.
If you want the column and row labels to appear in the chart, include the cells that
contain them in the selection.
Click Chart Wizard.
Follow the instructions in the Chart Wizard.
Alt + Insert + Chart
This symbol will be displayed in the Standard Toolbar of Excel.
The total drawing of the chart requires 4 steps After the selection of the Chart option in the Insert Menu.
The Step 1 in it is selection of the C
CONCATENATE (text1,text2,…)
TEXT(value,format_text)
TRIM(text)
LOWER(text)
UPPER(text)
LEN(text)
PROPER(text)
MID(text,start_num,num_chars)
CODE(text)
CHARTS
Create a chart
Cite This Work
To export a reference to this article please select a referencing style below:
