MS Excel Question Answer – Computer Basics, Computer Fundamentals

 

EXCEL

 

Ms excel:-

          Excel is an electronic spreadsheet program which is used to input, calculate and manipulate data , stored in form rows and columns ,generation of charts, perform mathematical ,logical, financial and text functions etc.

 

What is extension name of excel?

 Dot XLS

 

How to open ms excel?

Go to start menu → all program → ms office → ms excel.

press window + r → type me excel→ ok

 

What is worksheet?

Worksheet is a collection of gridlines called horizontal rows and vertical columns .

 

What is workbook ?

A collection of one or more worksheets is called workbook.

 

How many columns or rows in one worksheet in MS Excel 2003?

Rows = 65536, Columns =256

 

How to go to last column & last row?

Last column: – ctrl + right arrow

Last row: – ctrl + down arrow

 

How can editing in a cell or see formula ?

Press F2

 

How can wrap text in a cell?

Alt + Enter

 

What is name box ?

It displays the address of cell at the top of sheet in left side.

 

What is cell?

Cell is intersection of row and column.

 

How to go to option use?

Ctrl + G, F5

 

How to go to on next sheet?

Ctrl + page down

 

How to go to on previous sheet?

Ctrl + page up

 

How to go next sheet on a fixed address?

Press f5 → type sheet + no sheet+ 1 key with shift & give address column → row → ok.

 

How to write a text in all cells?

Select all sheet → give text name → ctrl + enter.

 

How to change direction of enter key?

Go to tools menu →option → edit → select direction from move selection after enters → ok.

 

How to use custom list?

Select the list → go to tools menu→ option → custom list → import → add → ok→ write any name & draw it.

 

How to give color for gridlines?

Go to tools menu → option → view → click on gridlines color → choose color → ok.

 

How to hide gridlines?

Go to tools option → view → click on gridlines → ok

 

How to insert a new column?

Right click on mouse → insert → click on entire row & entire column → ok

 

How to insert a new worksheet?

Right click on sheet → insert → ok OR Shift + alt +f1   

How to delete a sheet?

Right click on sheet → delete

 

How to rename a sheet?

Right click on sheet → rename → give any name → ok

 

How to copy a sheet?

Right click on sheet → move or copy → click on a create a copy → ok

 

How to select a single row & column?

Row: – shift + space

Column: – ctrl + space

 

How to prepare table for number?

Create a series row & column → keep the curser near no of 2 = select no2 from row → press → f4 for three times → again select no of 2 from column → press f4 for two times → enter → draw the number of row & column

 

How can transpose the table .

Select the table → copy → click another location → paste special → transpose → ok

 

How to create a series row & column?

Go to edit menu → fill → series → select row and column → give step value → give stop value → ok

 

How to insert date for row and column?

Give date → go to edit menu  →fill → series → select row and column → click on date → select date unit → give step value → stop value → ok

 

How to insert header & footer in excel sheet?

Insert the text in excel sheet → go to view menu → header and footer → click on custom header → ok → click on custom footer → ok → click on print preview → ok

 

How to use comments?

Go to insert menu → give comment→ go to view menu → comment

 

What is custom view option?

Custom view option is by which we can give any name a text and see the text its name.

 

How to use custom view?

Insert the text → go to view menu → custom view → give name → ok

 

How to see custom view text?

Go to view menu → custom view → select view name → show

 

How to insert a new worksheet?

Right click on sheet → insert → ok  OR  shift + alt + f1, f4

 

How to prepare chart?

Select the table → go to insert menu → chart → select chart type → next → next → finish.

 

How to give color in chart?

Right click on chart → format → chart area → chose → ok

 

How to symbol?

Go to insert menu → symbol → select font style → select subject → chose symbol → ok

 

How to total by keyboard?

Alt + (+) key →enter →shift +down arrow → ctrl +d

 

To Make Relative reference formula: See the example which is given below.

Example:

Product

Quantity

Rate

Total

Close-Up

10

25

250

Colegate

15

30

450

In Relative reference we use the formula Cell* Cell

(QUANTITY* RATE).

To Make Absolute reference formula:

Example:                      

Product

Quantity

Rate

Total

Close-Up

10

 

25

250

Colegate

15

375

 

In Absolute reference we fixed the cell with Dollar Sign ($) for result (F4)

QUANTITY CELLS* RATE CELL ($Column $Row) F4.

 

To Make Mixed references formula:

Example:

 

1

2

3

4

5

6

7

8

9

10

1

 

 

 

 

 

 

 

 

 

 

2

 

 

 

 

 

 

 

 

 

 

3

 

 

 

 

 

 

 

 

 

 

4

 

 

 

 

 

 

 

 

 

 

5

 

 

 

 

 

 

 

 

 

 

 

Apply formula on blank cell: = above one* beside one.

Select the Table> Data (Menu Bar)> Table> Row input cell (Above one cell)> Column Input cell (Beside one cell)> Ok.

 

Financial Function 

Loan Project 

 

Principal value            = 10000

Rate                             = 4%

Year                            = 4

 

How to calculate pmt?

= pmt → ctrl + a →

Select rate → press f4→ /12

N per (in select year) → press f4 → *12

 PV in (press (–) key select principal value) → f4 →ok.

 

How to calculate PPMT?

 

= PPMT → ctrl + a

Rate:  select rate → press f4 / 12

Per:   select S.No 1st

N per: – select year → press f4 → type * 12

PV: – press (-) key →select principal value

  

How to calculate ipmt?

 = ipmt → ctrl + a

Rate: – select rate → press f4 → / 12

Per: – select s.no 1st          

N per: – select year → press f4 → type * 12

PV: – press (-) key →select principal value

 

Full form in financial function:-

 

PMT: – Per Month Total

PPMT: – Principal Per Month Total

IPMT: – Interest Per Month Total

PV: – Present Value

NPER: – Number of Period

FV: – Future Value

Logical Function

 

s.no

Name

Dept

Grade

House

Basic

Da

Ta

Hra

Total

1

A

Sale

 

 

 

 

 

 

 

2

B

Acct

 

 

 

 

 

 

 

3

C

Pur

 

 

 

 

 

 

 

4

D

Sale

 

 

 

 

 

 

 

5

E

Acct

 

 

 

 

 

 

 

6

f

market

 

 

 

 

 

 

 

 

How to calculate grade, house, basic, da, ta, hra, & total?

Grade: – type = if (select + dept in sale = “sale”,”senior”,”junior”) →enter

House: – type =if (and (select dept in sale =”sale”, again select grade =”senior”),”yes”,”no”) →enter

Basic: – type = if (and (select dept in sale =”sale”, again select grade =”senior”), 15000, 12000) →enter

Da: – type = if (and (select dept in sale =”sale”, again select grade =”senior”), select basic *10%, again select basic *6%) → enter

Ta: – type =if (and (select dept in sale =”sale”, again select grade =”senior”), select basic *9%, again select basic *7%) → enter

Hra: – type =if (and (select dept in sale =”sale”, again select =”senior”), select basic *9%, again select basic *7%) → enter

 

Full form in logical functions:-  

 

S.No                            1, 2,3,4,5, etc

Name                           A, B, C, D, E, etc

Dept                            Sale, Pur, Acct Etc 

Grade                          Senior, Junior

House                          Yes, No

Basic                           Basic Salary Give

Da                               Dearness Allowances

Ta                                Traveling Allowances

Hra                              House Rent Allowances

Total                            Do the All Total

 

How to use number?

Select the column → go to format menu → cell → number → select category→ ok OR Ctrl +I

             

How to use text alignment?

Go to format menu → cell→ alignment → select the position → ok           

 

What is wrap text?

Wrap text is command by which use can give text in a particular cell.

 

How to use wrap text?

Go to format menu → cell → alignment → click on wrap text → ok.

Enter text then Alt+Enter

 

How to use shrink to fit?

Press ctrl + 1→ alignment → shrink to fit → ok.

 

How to use merge cell?

Select the cell → go to format menu → cell → alignment → click on merge cell → ok.

 

How to use orientation?

Press ctrl + 1 → alignment → give text direction → ok.

 

How to use font?

Go to format menu → cell → font → give font style → give size → give color → ok.

 

How to use border / apply the border?

Press ctrl + 1 → border → choose patterns → chose alignment style → choose color → ok.

 

How to give password whole cell?

Go to tools menu → protection → protect sheet → give password two times → ok.

 

How to give password to a limit range?

Select the all sheet → format menu → cell → protection → lock → ok → again select the limit range → again go to format menu → cell protection → locked → ok → go to tools menu protection → protect sheet → give password two times → ok.

 

How to use unprotect sheet or password remove in sheet?

Go to tools menu → protection → unprotect sheet → give password → ok.

 

How to use auto format?

Select the row / column → go to format menu → auto format.

 

How to use conditional formatting?

Select any column → go to format menu → conditional formatting → give condition → font → give color → add→ give condition → again number → give color → ok.

 

How to create a new style?

Go to format menu →  style name → modify → alignment → give text alignment → orientation degrees → go to font → give font style → size → color → ok → ok.

 

What is normal value of row / column?

Row (width) 12.75

Column (width) 8.43

 

What is goal seek?

Goal seek is an option by which we can change value of one cell.

 

How to use goal seek?

Type number→ total all →give cell value→ select a change cell → ok

 

How to use install solver?

Go to tools menu → add –in → solver add – in → ok → yes → again go tools menu → select target cell→ guess → add→ cell reference → constraint → ok → solver

 

How to use scenarios?

Select a single row → go to tools menu → scenarios →add →  give scenarios name →ok→ add other for change the values  → repeat the process for scenarios →ok→ click on scenarios show

 

There are two types of lookup function:-

Vlookup

Hlookup

 

How to use v lookup function?

 

Draw a table → copy the heading → paste it another location → go to data menu → validation → setting → list → source → =  name column list → ok→ put in type =vlookup →ctrl +a

 

                        Lookup value: –   select list cell

                        Table area: –        all table select

                        Column & row: – 2/3

                        Range  lookup: – false → ok

 

How to use hlookup?

 

Draw a table → select all table → copy of all table →click another location →  paste special →transpose →  again copy heading paste it other location → go to data menu → validation → setting → source= → ok → type =h lookup → ctrl +a

 

                        Lookup value: –   select list cell

                        Table area: –        select the all table (horizontal)

                        Column &row             2/3

                        Range lookup: –          false     → ok

 

How to set the direction of right to left of sheet?

Go to tools menu → option → international → click on default direction to left → click on view current sheet right to left → ok.

 

How to use sort option?

Go to data menu → sort → change position → ok

 

How to use auto filter?

Make a table → select it → go to data menu → filter → auto filter → see the report on your according

 

How to use advance filter?

Make a table → copy the heading → paste it → give the condition → select all previous table → go to data menu → filter → advance filter → select criteria  range → click on copy to select any cell → ok

 

How to use form?

Create a table → select it → go to data menu → form → fill the name →criteria →  new press your need → close

 

How to use subtotals?

Click in table → do the dept column ascending or descending order  → go to data menu → subtotals → add each change in → dept → subtotal to → you want to total of those  → choose formula → ok

 

How to use validation?

Select any column → go to data menu →validation → give condition with number →ok

 

How to use custom in validation?

How can we can put validation that column not accept a duplicate value ?

Select entire column(such as P) then we can put condition → validation → =COUNTIF(P:P,P24)=1

 

How to use consolidate?

Prepare the table for two month jan/feb → give the heading for month at march → select the blank cell → go to data menu  → consolidate →select the value of Jan table in a reference → add → again → select the value of Feb. in reference → add → ok

 

How to create group &outline?

Select the range → go to data menu → group &outline →group →select row /column →ok

 

How to create pivot table /pivot chart report?

Select the table →go to data menu →next →finish →add to range the table.

Related posts