Saturday, September 18, 2021

Microsoft Excel 2010 Basic Study Notes

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

11.                   How to calculate Grade?

=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?

  1. Select all marks at a time>

  2. Home > conditional formatting >

  3. Manage rules > new rules > Format only cell that contain

  4. Choose Greater Than  > Type any value

  5. Format > Choose any color

  6. 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.)
  1. Select the column of name and net holding ctrl key.

  2. Insert > Chart > column > Select any Chart

  3. Design tab > Move Chart > New Sheet > Ok

  4. Layout Tab > Data Labels > outside end

  5. Layout Tab > Axis Title > Primary Horizontal Axis Title > Title Bellow Axis

  6. Layout Tab > Axis Title > Primary Vertical Axis Title > Vertical Title

  7. Go to Format Tab, you can change the chart shape color.

  8. 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.
  1. At first we have to sure that we a worksheet table of minimum 2 or 3 pages.

  2. Click on print title option > Sheet > Row To Repeat at top > Select a row or field name of heading > ok

  3. 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.

  1. We have a Mark sheet.

  2. We want to change any percentage by changing any subject’s mark.

  3. 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.
  1. Select any entire table like Mark sheet, Salary Sheet>
  2. Data > Sort > Sort by > Name>
  3. Order > Select any criteria.


34.                   Filter :

  1. Select any entire table>

  2. Data > filter

  3. An arrow sign will be appear on each fields name

  4. Click on arrow and choose criteria


35.                   Advanced Filter:

  • We need a mark sheet or salary sheet or any other.
  1. Create a Criteria (Ex. Percentage > 75) in two cells

  2. Click on a blank cell

  3. Data > Advanced

  4. Copy to another location

  5. List Range : Select the entire table

  6. Criteria Range : Select the criteria Cell

  7. Copy To : Click on a blank cell


36.                   Data Validation(List):

  • To create a drop down list of some product name
  1. Select one or more cell of a column

  2. Data > Data Validation > Allow: List

  3. Type some product name in Source using Comma

  4. Ok

37.                   Data Validation(Decimal):

Select one or more cell of a column

  1. Data > Data Validation > Allow: Decimal

  2. Data : Between

  3. Type minimum and maximum value

  4. Type an Input Message

  5. 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.
  1. After creating a sales voucher > click on any amount > click on Trace Precedents from formulas tab.
  2. 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.
  1. Type two different values in two different sheets

  2. Click on 3rd sheet and click on a blank cell

  3. Data > consolidate > reference > select the 1st value from first sheet > add

  4. Select the 2nd value from 2nd sheet > add > ok.


43.                   Comment

  1. Click on a cell
  2. 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

Microsoft Access 2010 Notes

  1.        What is Ms Access? ·          Microsoft Access is a  database management system  (DBMS) from  Microsoft  that combines the...