EXCEL : Convert number to currency format
14: EXCEL : Convert number to currency format

Ctrl + Shift + $

Try it out.

Excel: COUNT Function

13: Excel COUNT Function

Purpose of COUNT function is to count number data and ignore text data. To count, choose a cell and enter the COUNT syntax: = COUNT (Range).The range is the cells of whom the counting needs to be done, for example cell A1:C6. If you enter any number data later in these cells, the counting cell will automatically update. So remember to keep the counting cell in a corner of the worksheet or away from the path of data rows and columns.

In the below screenshot, It’s only Counting Number of cell contain only Number, between B2 to B10.

WORD: EXCEL: Automatically replace text in the document

12: Automatically replace text in the document — for example, you can replace "Tom" with "Tommy"

Ctrl + H opens the Find and Replace dialog box. This can be used to FIND specified characters in the document and REPLACE it with what we want. 

Try it out.

Note: Ctrl + F is used just to Find text in the document, Ctrl + H to Find and Replace.

WORD : Keyboard shortcut to Increase or Decrease the font size of selected text

11: WORD : Keyboard shortcut to Increase or Decrease the font size of selected text

CTRL + ] If you want to INCREASE the font size of a text selection 

CTRL + [ If you want to DECREASE the font size of a text selection

EXCEL : Remove unwanted text Data in Excel with the LEFT Function

10: EXCEL : Remove unwanted text Data in Excel with the LEFT Function

At times, spreadsheet data carries unwanted characters or words included with the correct data.

Based on where such unwanted characters are located in the cell, Excel has functions which can be used to get rid of them.

• LEFT function - If you have unwanted characters on the left side of your good data
• RIGHT function - If you have unwanted characters on the left side of your good data
• MID function - If you have unwanted characters on both sides of your good data

Syntax for the LEFT Function
= LEFT ( text , num_chars )

text - the data you want to change which can be a cell reference pointing to where the data is stored.

num_chars - Number of characters to be retained from the text / cell specified above.

Example : Using LEFT Function to remove unwanted characters
See the image as well.

1. Column B contains data with unwanted characters on the right
2. In cell C3, enter the LEFT Function = LEFT (
3. In “text” , link the cell that carries the bad data, i.e Cell B3
4. On “num_chars”, give the no. of good characters on the left that need to be separated from bad. Enter the number 3 since we only want to keep the three leftmost characters of data.
5. Close ) and hit Enter.
6. The unwanted characters (^&%$) will be removed leaving just the number 100 in cell C3.
7. Drag the Cell C3 to to remaining cells (till C7)

EXCEL: Assigning a RANK to numbers in a list

9: EXCEL: Assigning a RANK to numbers in a list

RANK function, assigns a rank based on the size of a number compared to other numbers in a list.

The syntax for RANK function is:

= RANK ( number, ref, [order] )

Number - Cell reference of the number to be ranked.
Ref - Range of cells to use in ranking the number.
Order - Whether the number is ranked in ASCENDING or DESCENDING order.

Type "0" (zero) to rank in descending order (largest to smallest).
Type "1" (one) to rank in ascending order (smallest to largest).

Example: Using RANK Function

1. Data table contains names (B4:B13) and scores (C4:C13) of 10 students
2. Enter the rank function in cell D4
3. Drag select cells C4 to C13 in the spreadsheet to enter the range into the dialog box.
4. In [order] in the dialog box, type 0 (zero) to rank the number in descending order.

Note: Lock the function while dragging it through Column D so that the ref range does not get dragged along. Use $ for the same. Example : =RANK(C5,$C$4:$C$13,0).
One can either type $ each time before C, 4, C & 5 or use F4 key before C4 and C13 to insert $


Try it out.

WORD : Keyboard shortcuts to change alignment of selected text

8: WORD : Keyboard shortcuts to change alignment of selected text

Ctrl + L Left align the text
Ctrl + R Right align the text 
Ctrl + E Center the text
Ctrl + J Justify the text 

Try it out.

Note: Ctrl + A can be used to select the entire text after which the above shortcuts can be applied

EXCEL : Check if values in adjacent cells/columns are equal, greater or smaller

7: EXCEL : Check if values in adjacent cells/columns are equal, greater or smaller.

Need to compare values in adjacent columns? Easy!
Formulae:
For checking if values are equal: Write =(A:A=B:B) in Cell C2 and press Enter .
For checking if value in cell of Column A is greater than the adjacent value of Column B: Write =(A:A>B:B) in Cell D2 and press Enter .
For checking if value in cell of Column A is smaller than the adjacent value of Column B: Write =(A:A<B:B) in Cell E2 and press Enter .
Drag cells (C2, D2, E2) till the row you want results to be displayed.
Try it out.

EXCEL : Calculate the number of days, months, or years between two dates

6:EXCEL : Calculate the number of days, months, or years between two dates

The DATEDIF function can be used for this.

The syntax for the DATEDIF function is:

= DATEDIF ( start_date , end_date , unit )

• start_date - First or starting date (eg. DATE OF BIRTH)
• end_date - Second or last date (eg. TODAYS DATE)
• unit - Function to find the number of days ("D"), complete months ("M"), or complete years ("Y") between the two dates 

EXCEL : Calculate the time difference in one time unit

5: EXCEL : Calculate the time difference in one time unit

=INT((C4-B4)*24) Hours between two times.

=INT((C5-B5)*1440) Minutes between two times. 

=INT((C6-B6)*86400) Seconds between two times.