MS OFFICE/TIPS

1        How to select only UN read mails in outlook

Solution : Type read:false in the search box of outlook or on Home tab click Filter E-mail button and then Unread option(Shortcut Key ALT + H + L + U).

2      How to use HLOOKUP Function in excel.

Solution : =HLOOKUP(“_Column”,”_Range,”Parameter”)

1Shortcut key  to insert current time in MS EXCEL ?

 CTRL + SHIFT + :

2 What is Recent tab in file menu?

Solution : Recent tab also known as Excel History Pane saves our recent activities or modifications made in excel sheet/workbook, which is located in the file menu and is divided into two sections Recent Workbooks on left side and Recent Places on right side.

Shortcut key to open Recent tab  ALT + F + R.

-          Recent workbooks section displays the list of recently opened or modified excel files

-          Recent places section displays the list of folders that has been recently visited.

1     Can you name any search engine other than Google or Yahoo ?

Solution: Yes, there is Bing created by Microsoft 2009 formely known as Live/Windows search. Infact now Bing is supporting Yahoo and Google for speedy results. 

2  What is Format Painter?

Solution: Format Painter is the part of standard tool given in MS Office, you can use this to quickly copy formating of text,alignment, shapes, graphpics, picture borders, column, row etc.

Shortcut key : Right window + S + R or you can use ALT + H + F + P.

3 Can we use CTRL + D with filtered data?

Solution: Never use CTRL + D when your data is filtered because this will copy above text down where you are using it.

4  State one word to define MS excel ?.

Solution: Tabular.

  Can we navigate between Excel sheets without mouse ?

Solution: Yes by using CTRL + Page Down/Page Up.

   What is MSTSC?

Solution: MSTSC stands for Microsoft Terminal Services Client,Which is used for remote login at clinet and sever end using computer name or IP address.

7  What is Font face in MS Excel?

Solution: Font face is the text appreance that is displayed and printed.

            Example – Arial, Algerian, Handwriting.

8  What is concatenate in MS Excel.

Solution: Concatenate is a predefined function to join two or more strings(Text) in one column.

Example - 

Hello (Cell A1)

NIIT Cell A2)

=concatenate(A1,A2)

Hello NIIT (Cell A3)

9)    Shortcut key to warp text in excel?

Solution: ALT + Enter Key.

10)  Multiplication operator in excel?

Solution: (*) Asterisk.

   What Bcc stands for in outlook, can we check who is marked in the Bcc.?

Solution: Bcc stands for blind carbon copy, Bcc by default is the hidden property of mail. Yes we can view Bcc  if we check sent items or mail header by viewing it’s properties.

Shortcut key to add or remove Bcc : ALT + P + B

    Full form of GOOGLE ?

Solution: Global Organization Of Oriented Group Language Of Earth.

    How to highlight duplicate rows with formula?

Solution: Well there are 6 formulas to fetch out the duplicates from the data and we may also use conditional formatting for this.

                Lets try to figure it out with Countif 

= countif(A:A,A:A)>1 

    How many sheets are there in workbook by default? Can we change it ?

Solution : By default workbook starts out with three sheets. Yes we can change this value as per the requirement.

Step 1 – Click File menu.

Step 2 – Click Options.

Step 3 – In  General tab change the numeric value in “Include this many sheets”.

2  Formula to count number of characters in the cell?

Solution: = LEN(Cell_reference)

Text	Formula	Output
NIIT (A1)	=LEN(A1)	4

3  Explain the path to open MS Excel?

Solution: Start – All Programs – Microsoft Office – Microsoft Excel.

Or Start – Run – Type excel – Enter.

   Can we create keyboard shortcut to open excel?

Solution: Yes we can create keyboard shortcut to open excel.

Start – All Programs – Microsoft Office – Right click Microsoft Excel – Properties

-      In the shortcut key tab type the key that you want to assign example (CTRL + ALT + Z).

Now you can open excel by pressing (CTRL + ALT + Z) from your keyboard.

1   Formula to select the maximum value in the range?

Solution: = max(range_).

    Formula to select the minimum value in the range?.

Solution: =min(range_).

     Difference between save and save as?

Solution: Save as is used when the file is saved for the first name or if we want to rename the file.(F12) or (ALT + F + A).

Save – This is a regular saving of the document whenever the file is modified (CTRL + S) or (ALT + F + S)

1  Shortcut key to insert time and date separately?

Solution: Time -: CTRL +  SHIFT + : (CONTROL + SHIFT + COLON )

                    Date -: CTRL + ; (CTRL + SEMI COLON)

2 Operator and formula  to join text of different cells in on cell?

Solution: & is the operator to join text and formula is  =H12&I12

   How to insert space between the above (=H12&I12) formula ?

Solution: =H12&" " &I12

Attached is the example for Query 2 and 3.

  What is action script ?

Solution: Action script is a programming languge that creates the 3D objects and creates the motion path for them.

     Full form of Excel VBA?

Solution: Visual basic for application, this is based on dot net technology and macros are created with VBA.

I VBA is applicable to only EXCEL?

Solution: No, VBA is applicable to all MS OFFICE application(Example Excel, Powerpoint, Word, Acess, One Note Project Planning Etc)

1    Shortcut Key to Open Format Cells ?

Solution: CTRL + 1

      Shortcut to wrap text?

Solution: ALT + Enter.

1   How to insert zero (0) at the beging of the cell.

Solution: You can insert single quote (‘) berfore zero or you can change the format of the column to text.

  

Shortcut key to make the cell text.

Solution: ALT + H + N + TE + Enter.

How to rename the excel work sheet?

      Solution: Right click on the sheet tab or press F2 and change the default name.

How to insert new blank sheet with shortcut key?

      Solution: SHIFT + F11

1Can we add password to excel files, if yes how?

Solution: Yes we can add password to excel files, below are the steps:

-       Click on File menu.

-       Click Save as button.

-       In the save as dialog box click tools button at the bottom and select general option.

-       This will allow you to insert password to your file.

2Can we update excel automatically ?

Solution: Yes we can by creating a connection and setting the refresh time of the connection created. 

1Can we hide the gridlines in the Excel Sheet?

Solution: Yes we can hide the grid lines in the Excel sheet, following are the steps

-          Click on File menu.

-          Click  Options Button.

-          In Advanced tab un check the show gridlines option.

-          Click Ok.

2Formula to count only numbers(Numeric Values) in a range?

Solution: = COUNT(range_)

Note: Count function will only count numeric values in a range and will skip all the blanks and text value.

   What is office clipboard?

 Solution: Office Clipboard is a short term memory generated by the office application when we cut/copy/paste/undo/redo some data.

2)    What is the difference between undo and redo?

                         Solution: Undo (CTRL + Z) cancel the last task performed by maximum 12 times.

                         Redo(CTRL + Y) can cancel or repertory the last 99 actions.

  If excel sheet is not working and the data is also not saved what to do?

Solution: Without closing the excel sheet reboot the system ans close your excel sheet with CTRL + Pause break.

2  Macros are giving errors and not working properly?

Solution: Shut down the system for 10 minutes and restart it, this will make your system cpu free to execute macros.

3 What is Title bar in excel sheet?

Solution : Title bar display’s the name of the current opened sheet/document.