1. How to open excel?
- Start > All Programs > MS Office > MS Excel 2010.
2. What is Worksheet and Workbook?
- An Excel worksheet is a single spreadsheet that contains cells organized by rows and columns.
- A workbook is an Excel file that contains one or more worksheets.
3. What is cell?
- In excel worksheet all rows and columns joins into a cell, it is like a rectangular box, where we put values and enter formula.
4. What is the last column heading?
- XFD
5. What is the last row number?
- 1048576
6. What is the first and last cell address?
- A1 and XFD1048576
7. What is the extension of Excel?
- .xlsx
8. How to calculate total?
=SUM(A1:E1)
=SUM(select the range of the number)
=SUM(1st cell address : last cell address)
9. What is autosum?
- Using autosum you can calculate total or percentage automatically selecting the range of the numbers.
10. How to calculate Average?
= AVERAGE (A1:E1)
= AVERAGE (select the range of the number)
=AVERAGE(1st cell address : last cell address)
=total number*100/full marks
=IF(K2>80,"A",IF(K2>60,"B",IF(K2>40,"C","Fail")))
****K2= THE PERCENTAGE CELL
12. Increase and decrease decimal number:
To adjust decimal number, you can use it from home tab in Number group.
13. How to apply Conditional formatting on mark sheet?
- Select all marks at a time>
-
- Home > conditional formatting >
-
- Manage rules > new rules > Format only cell that contain
-
- Choose Greater Than > Type any value
-
- Format > Choose any color
-
- Ok > ok >apply > ok
14. How to insert row and column?
- Select a particular cell > Home > Insert > Insert sheet row or Insert Sheet Column.
15. How to delete row and column?
- Select a particular cell >Home > Delete > Delete sheet row or Delete Sheet Column.
16. Merge & Center:
- When you are trying to make a single large cell using multiple cells, then select some cells and click on Merge & Center Option.
17. Border and fill color:
- You can change the border style, color, and background color of selected cells.
18. Wrap Text:
- When you are trying to adjust a long text into a small cell, the click on Wrap Text option selecting the cell, the text will be adjusted with multiple lines.
19. Shrink To Fit:
- It is used to adjust a long text into a small cell with a single line. If you increase the cell size text size will be increased and if you decreased the cell size the text size will be decreased.
- Select the particular cell > open format cell dialog box > alignment > Shrink to fit.
20. Text Orientation:
- This option is used to rotate or diagonal or vertical text in a cell.
21. Salary Sheet
- Emp_id : (Example: SBI-501)
- Emp_name (Ex. Suresh Sharma)
- Designation (Ex. Manager)
- Basic (Ex. 12500)
- Ta : [Traveling allowance] (Basic*7%)
- Da : [Dearness allowance](Basic*65%)
- Hra: [House Rent Allowance] (Basic*20%)
- Ma : [Medical allowance](Basic*5%)
- Gross : =sum(Basic:ma)
- Pf (gross*8.5%)
- Esi (Gross*4%)
- Net : =gross-(pf+esi)
|
salary sheet sep, 2016 |
|||||||||||
|
emp_id |
emp_name |
designation |
basic |
ta |
da |
hra |
ma |
gross |
pf |
esi |
net |
|
sbi-101 |
sujay das |
manager |
12500 |
875 |
8125 |
2500 |
625 |
24625 |
2093 |
985 |
21547 |
|
sbi-152 |
Tanmoychatterjee |
clerk |
11200 |
784 |
7280 |
2240 |
560 |
22064 |
1875 |
883 |
19306 |
|
sbi-155 |
Bikashhalder |
clerk |
11000 |
770 |
7150 |
2200 |
550 |
21670 |
1842 |
867 |
18961 |
|
sbi-185 |
Subratapaul |
peon |
8900 |
623 |
5785 |
1780 |
445 |
17533 |
1490 |
701 |
15341 |
22. How to create chart (column) on salary sheet for name and net?
- We find here different type of chart. (Column, pie, line, bar, area, bubble, stock etc.)
- Select the column of name and net holding ctrl key.
-
- Insert > Chart > column > Select any Chart
-
- Design tab > Move Chart > New Sheet > Ok
-
- Layout Tab > Data Labels > outside end
-
- Layout Tab > Axis Title > Primary Horizontal Axis Title > Title Bellow Axis
-
- Layout Tab > Axis Title > Primary Vertical Axis Title > Vertical Title
-
- Go to Format Tab, you can change the chart shape color.
-
- Go to Design Tab, you can change chart type.
23. Margin:
- Page layout > Margin or Custom margin
24. Header & Footer
- Page layout > Margin > Custom margin > Header & Footer > Custom Header or Custom Footer
- There are three different sections; you can put here date, time, any heading, page number etc.
25. Orientation
- Page layout > Orientation > Landscape or Portrait
26. Page Size:
- Page layout > Size > A4
27. Print area:
- Print area is used to mark a specific area of the sheet for printing.
- Select a range for printing > Page layout > Print Area.
28. Breaks:
- Breaks are used to specify where a new page will begin in the printer copy.
- Click on a particular cell > Page Layout > Breaks.
29. Background:
- Click on background option > Choose any image. You can apply image in background of the sheet.
30. Print Title:
- To apply a common heading (row) or column, we can use Print Title option.
- At first we have to sure that we a worksheet table of minimum 2 or 3 pages.
-
- Click on print title option > Sheet > Row To Repeat at top > Select a row or field name of heading > ok
-
- Go to office button > Print Preview.
31. PMT (Per Month Transaction )
- Product Name (Ex. SONY LCD TV)
-
- Price (Ex. 56000)
-
- Down Payment (=Price *45%)
-
- Credit Amount (=Price-Down Payment)
-
- Installment (Ex. 4)
-
- Interest (Ex. 7%)
-
- Payable Amount
i. Formulas > Financial > PMT
ii. Rate : Interest Cell /12
iii. NPER : Installment Cell
iv. PV : Credit amount Cell
v. Type ABS before PMT in Formula Bar with ()
vi. Change Data Type into Number or General.
32. Goal Seek
You can change any value by other value, but both of the values should be in a formula.
- We have a Mark sheet.
-
- We want to change any percentage by changing any subject’s mark.
-
- Click on that particular Percentage > Data > What if analysis > Goal Seek
i. Set Cell :
ii. To Value : Type any percentage what you want to apply
iii. By Changing Cell: click on any subject’s mark.
6. Ok.
33. Sort:
- To display all records with criteria like A to Z, Maximum to Minimum or Minimum to Maximum, we can use sort.
- Select any entire table like Mark sheet, Salary Sheet>
- Data > Sort > Sort by > Name>
- Order > Select any criteria.
34. Filter :
- Select any entire table>
-
- Data > filter
-
- An arrow sign will be appear on each fields name
-
- Click on arrow and choose criteria
35. Advanced Filter:
- We need a mark sheet or salary sheet or any other.
- Create a Criteria (Ex. Percentage > 75) in two cells
-
- Click on a blank cell
-
- Data > Advanced
-
- Copy to another location
-
- List Range : Select the entire table
-
- Criteria Range : Select the criteria Cell
-
- Copy To : Click on a blank cell
36. Data Validation(List):
- To create a drop down list of some product name
- Select one or more cell of a column
-
- Data > Data Validation > Allow: List
-
- Type some product name in Source using Comma
-
- Ok
37. Data Validation(Decimal):
Select one or more cell of a column
- Data > Data Validation > Allow: Decimal
-
- Data : Between
-
- Type minimum and maximum value
-
- Type an Input Message
-
- Type an Error Alert
38. Sub Total:
- Item (make a dropdown list using data validation)
- Price
-
- Qty (Use decimal validation here [3 to 6])
-
- Amount (Price * Qty)
i. Add minimum 8 to 10 records repeating same items
ii. Select the entire table
iii. Data > Sort > Sort by item
iv. Data > Sub Total >
v. Check qty and amount
vi. Ok
39. Billing System / Sales Voucher
- Product Id (Ex. Big-101)
-
- Product Name (Ex. Dove)
-
- Price (Ex. 45)
-
- Qty (Ex. 3)
-
- Amount (Price * Qty)
-
- Discount Rate (Ex. 4%)
-
- Discount Amount (Amount * Discount Rate)
-
- Gross amount (Amount – Discount Amount)
-
- GST rate (Ex. 5%)
-
- GST Amount (Gross amount * GST Rate)
-
- Net Amount (Gross Amount + GST Amount)
40. Trace Precedents and Trace Dependents
- It is used to show arrow on cells affected with the other values using formula.
- After creating a sales voucher > click on any amount > click on Trace Precedents from formulas tab.
- Click on any price > click on Trace Dependents.
41. Text to columns
- Type all fields in particular cell using comma > click on other and cell > click on that particular cell where you typed all fields. >Data > Text to column > delimited > next > choose comma> next > finish.
- It is used to separate the content of one cell into separate cell.
42. Consolidate
- It is used to calculate total of numbers of different sheets.
- Type two different values in two different sheets
-
- Click on 3rd sheet and click on a blank cell
-
- Data > consolidate > reference > select the 1st value from first sheet > add
-
- Select the 2nd value from 2nd sheet > add > ok.
43. Comment
- Click on a cell
- Review > New comment > Type any comment in comment box.
44. Count
- To find out total number of records
|
sl no |
item |
|
501 |
ponds |
|
502 |
dove |
|
503 |
lux |
|
504 |
sunsilk |
|
505 |
oil |
|
506 |
axe |
|
507 |
cinthol |
=COUNT(A1:A8)
45. MAX and MIN
To find out maximum number and minimum number
=MAX(Select the range of number)
=MIN(Select the range of number)
46. How to create auto fill list?
File > Option > Advanced > Edit Custom List >Type some name one by one in List Entries box> Add >ok.
47. Hide / Unhide Row & Columns
To hide any row or column – Right click on row number or column heading > hide.
To unhide row and column – select two row numbers or columns headings > unhide.
48. How to calculate working time?
- Field names are In Time | Out Time | Total Working Time
-
- Select the cell where you will enter value
-
- Home > Format > Format Cell
-
- Number > Category: Time > Type: 1:30PM
-
- Select Working Time cell
-
- Number > Category: Custom > Type: h:mm
-
- Formula : = out time – in time
-
- In time: 10:00 (10:00 AM)
-
- out time: 19:00 (7:00 PM)
-
- Total working Time: 9
49. Protect Sheet
Home > Format > Protect Sheet > Type any password > Reenter password> ok.
50. Excel to word mail merge.
No comments:
Post a Comment