Microsoft Office Excel 2007
Application Name:-Excel.exe
Extension Name:-xlxs
M.S Excel is the most powerful spreadsheet Package developed by Microsoft Corporation U.S.A. Spreadsheet is a columnar pad used for calculation. It is designed for official purposes. It helps us to create a data file because It has a very powerful tool which makes us faster more than normal activities. It helps us to create tables also with powerful style. Especially M.S Excel is design for create a data file.
How to start M.S Excel ?
Click on start Menu
Select Programme
Choose Microsoft Office
Click on M.S Excel 2007
Or,
Press Home key + R
Run box will appear then
Type “excel”
Click on Ok and Press Enter
Workbook:- A Microsoft Office Excel workbook is a file that contain one or more worksheets that you can use to organize various kind of related information.
Worksheet:- An Excel worksheet is a single spreadsheet that contains cells organized by rows and columns. A worksheet begins row number one and column A. Each cell can contain a number, text or formula.
Worksheet is the collection of rows and columns. There are 256 Columns and 65536 rows in Excel 2003.
1048576 rows and 16384 columns in 20007.
Cell:= A cell is the intersection between a row and a column on a spreadsheet that starts with cell A1.
Cell Range:- It is the group of cells. Cell range is Denoted by double dots (..) or column signs (:).
Functions:- Function are the predefined formula that perform calculation in M.S Excel allow solving problems quickly which work in the worksheet. The Following function are used in MS Excel as below:
= Today () :- It is used to display date according to the system date.
= Now () :- It is used to display today date and
Time according to the system date.
= Sum () :- It is use to calculate the sum values of number.
= Average () :- It is use to calculate the Average (percentage).
= Count () :- It is use to count the items numbers Which is selected.
= Min () :- It is use to display the minimum Values of selected items(numbers).
= Max () :- It is use to display the maximum Values of selected items(numbers).
= If () :- The most command and logical function is the IF Function, which allow you to developed several kinds of tests based on Operators used in the statement. It is used to make the condition.
🎉 Syntax :-
If (condition, value if true, value if false)
> is greater than
< is less than
= is equal to
>= is greater than or equal to
<= is less than or equal to
<> is not equal to
And () :- The AND logical function returns the value TRUE if all of its arguments are true;
Otherwise it returns the value FALSE. It is used to make the given condition true.
🎉 Syntax :-
=or (condition 1, condition 2 ….)
=if(or(B2>=50,C2>=55),”P”,”F”)
= Or () :- The Or Logical function returns TRUE if one or more of its argument is true ;
Otherwise It returns FALSE. It helps us to make any one true from the given condition .
🎉 Syntax :-
=or (condition 1, condition 2 ….)
=if(or(B2>=50,C2>=55),”P”,”F”)
= Not () :- It helps us to make false to the given conditions.
= Sum () :- The sum if function adds the cells specified by the given criteria.
🎉 Syntax :- = Sumif (range, criteria, sum range)
= Countif () :- It counts the number of cells with a range that meet the given criteria.
🎉 Syntax :- = count (range, criteria )
= Sqrt () = This function gives us to square root of given number.
🎉 Syntax :- = Sqrt (64)
= Product () :- This function is used to find product of given numbers.
= Rank () :- This function is used to give the position of the selected numbers.
= Round () :- This function is used to remove the decimal number of the Selected item.
Exercise 1 🟰
Condition:- Discount is 5%, Vat is 13%
Total = Qty * Rate or = C2*D2, or =product(C2:D2) Press Enter key.
Discount:- =Total*5%, = E2*5% Press Enter key.
Net Amount = Total - Dis
Vat:- =Net Amount*13%, =G2*13%
Press Enter key.
GT :- Net Amount + Vat
Exercise 2 🟰
Age Differences
Conditions:-
If age is 0-12 = Child Age
If age is 13-19 = Teen Age
If age is 20-35 = Young Age
If age is 36- 55 = Adult Age
If age is 56- above = Old Age
=If(D2<=12,”Child”,If(D2<=19,”Teen”,If(D2<=35,”Young”,If(D2<=55,”Adult”,”Old”))))
Exercise 3 🟰
Telephone Bill
Conditions:-
Minimum Call :- 175
Minimum Charge :- 200
Excess Per Call :- Rs 1.35
Vat :- 13%
TAx :- 15%
Formula:-
Excess call =If(E2<175,E2-175,0)
Total R.s :- 200+F2*1.35
VAt :- G2*13%
Tax :- G2*15%
GT :- G2+H2+I2
Exercise 4 🟰
Electric Bill
Conditions:-
Minimum Units :- 120
Minimum Charge :- Rs.80
Excess Per Unit :- Rs 7.35
Formula:-
Excess Unit =If(E2>20,E2-20,0)
Total R.s :- (80+F2*7.35)
Exercise 5 🟰
Salary Sheet
TADA :- Travelling and Daily Allowance
HRA :- House Rent Allowance
Conditions:-
Bonus :- If salary is less than 10000, bonus is 10% and GReater than 10000, Bonus is 7%
TADA :- If Adress is out of Biratnagar, TADA is 5% if Adress is in Biratnagar TADA is 0%
HRA :- If Adress is out of Biratnagar and salary is less than 15000 then HRA is 7% and If not HRA is 0%
Vat :- 13%
Formula:-
Bonus =If(E2<10000,E2*10%,E2*7%)
TADA =If(C2<>”Biratnagar”,E2*5%,0)
HRA =If(and(C2<>”Biratnagar”,E2<15000),E2*7%,0)
VAt :- E2*13%
Net Salary :- =E2+F2+G2+H2-I2
Home Tab
Fill :- It copy the contents and formats of selected range into cell down, up, left and right side.
Series: It is used to fill number in columns and rows as a sequential value.
Type a no.1 in blank cell and press Enter
Re-select no.1 of cell
Click on Home Tab (Under Editing Group)
Click on Fill Downward arrow (⇓)
Click on series
Choose the series in row or column
Click on stop box and type your stop value
Click on Ok
Transpose:-
= This option is used to change the row value into column and column values into row.
Select the data cell range
Copy the selected cell
Palace the cursor in any cell
Right click on cell
Click of Paste special
Tick of Transpose Option
Click on Ok
How to Delete Sheet ?
= It removes the selected Sheet from current workbook.
Select Sheet which you want to Delete
Go to Home TAb (Under Cell Group)
Click on Delete
Click on Delete Sheets.
How to Insert rows, columns and Sheet ?
Place the cursor where you want to insert rows and column
Go to Home TAb (Under Cell Group)
Click on Insert
Click on Insert rows, columns and sheets.
How to break Page?
Go to view Tab
Click on Page break (Under workbook views)
Click on Page break Preview
How to show Document in normal view?
Go to View TAb
Click on normal
Header and Footer
= This command is used to insert the page number on the page.
Go to Insert TAb
Click on Header and Footer (Under Text Group)
Choose the number
Click on Ok
Note: To see the Page number only in print Preview.
Row:- = This command is used to insert the row on the top of the active cell.
Click on Home Tab
Click on rows
Column = This command is used to insert the column in the left side of the active cell.
Worksheet:- = This command is used to insert the Blank worksheet.
Go to Home Tab
Under Cell Group, Click on Insert
Click on worksheet
10.Chart:- = This command is used to insert the Chart of selected DAta.
At first make a TAble
Select data
Click on insert tab
Click on chart
Select chart type and style
Like Pie chart, Column chart, Bar chart etc
Click on next button
Click on next
Type chart title and -x axis value and -y axis values.
Click on next
Click on Finish
Tips: If you want to add Table chart, Legend, data labels, data table select chart and go to layout TAb.
Number:- = This command is used to give a currency symbol for selected DAta.
Click on Home Tab
Click on Number Group
Click on Currency
To change currency symbol
Click on Start button
Click on Control Pannel
Open Regional and Language Option
Click on Customize/ Additional setting
Click on currency
Type your Required currency symbol like as Rs.
Click on apply
Click on Ok
Click on Ok
Alignment:- = This command is used to give the alignment for the text or style.
Select Data
Click on Home Tab
Click on Alignment
Change the degree of alignment
Click on Ok
Font:- = This command is used to change a font style and colour etc.
Select the Data
Click on Home TAb
Click on Font Group
Click on Font Select the required Option
Merge Cell :- = Join the selected cells into one larger cell centre the contents in new cell.
Select cell
Go to Home Tab
Under Alignment Group, Click on Merge and Center
Wrap Text:- = Make All contents visible within a cell by displaying it on multiple lines
Border:- = This command is used to set the Border for the Data.
Select the Data
Click on Home TAb
Click on Font Group
Click on Border
Select all Border Option
Conditional Formatting:- = This command is used to apply the colour for the data with given required conditions.
Steps:
Select the cell or sheet
Click on Home Tab
Click on Conditional Formatting
Click on new rule
Click on Format cell use
Choose any required Condition (Less than, grater than, between , Non Between)
Type number in the given box
Click on Format Button
Choose the font color
Click on Ok, If you want to add more then, repeat the same process Like above.
How to Delete Conditional formatting ?
Select the cell or sheet
Go to Home Tab
Click on Conditional Formatting
Click on clear rule
DATA TAB
Goal Seek :- = Goal seek is method to find specific value for a cell by adjusting the value of one other cell.
Click on value of total amount
Go to data tools, what if analysis
Click on Goal seek
Type new target value in to value box
Click on by changing cell box then select the value of rate
Click on Ok
Click on Ok
Sort :- = This command is used to arrange the given data into required order form. (Ascending Order and Descending Order)
Select the given data
Go to Home TAb (Under sort and filter group)
Click on Sort A to Z and Z to A
c. Auto Filter:- = Filter Selected Information automatically using list boxes. This give you the ability to display or Print only that information that matches the value or range of values.
Place the cursor TAble heading
Go to Data TAb
Click on filter
View Tab
Freeze Panes:-
= Freezing panes allows you to select data the remains visible when scrolling in a sheet. For Example, keeping rows and columns labels visible as you scroll.
good
ReplyDelete