மிக முக்கியமான எக்செல் ஷார்ட் கட்ஸ்

Super easy trick. Only 5% of Excel users know about it! 

CTRL+; - Enters the current date in the right format

How to use it? 
1. Click in any cell 
2. Press Ctrl +; 
Simple? smile emoticon

I wonder who knows the difference between Ctrl +; and =(Today) formula?

Please, share the knowledge!

ROMAN formula

This simple to use, math based Excel formula will convert a value, written in Arabic format (standard numeral system) and converts it into the Roman Numeral system as text. The syntax is as follows:
=ROMAN(number, [form])

The ROMAN formula syntax has the following arguments:
The number argument. This can be a set value, or a cell address, of the Arabic numeral that you wisg to convert.
The optional argument, form. This is a number specifying the type of roman numeral you want. The roman numeral style ranges from Classic to Simplified, becoming more concise as the value of form increases. Refer to the following list.
Form Type
0 or omitted Classic.
1,2,3 More concise
4 Simplified.
TRUE Classic.
FALSE Simplified.

REPT function

Let’s assume you needed to replicate a certain symbol over a 1000 times in a single cell. Now, you can Repeats text a given number of times. Use REPT to fill a cell with a number of instances of a text string.
Syntax
=REPT(text, number_times) where, the ‘text’ is the text that you intend to repeat and the ‘number_of-times’ is a positive number specifying the number of times the text should be repeated.

Countdown to a Specific Date in Excel

In A1 enter =TODAY(). In B1 enter the date you want to count the years, months, and days until. 

In column C3, D3, and E3 enter =B1-A1. Right click on C3 and select custom, enter Y in the text box. Repeat for D3, but enter M instead of Y. Repeat again for E3 and enter in the custom format text box.

Freeze Panes to See Headings When Working with Multiple Columns and Rows

When working with multiple rows and columns, you may want to see your column and row headings. To freeze a single row or column, go to the View tab, select Freeze Panes. To free only the top row, select Top Row or to freeze only the first column, select First Column. In this case, I wanted to see both by first row and column, so I selected the entire first row. Holding control, select the entire first column, then go to Freeze Panes, and select Freeze Panes. Go to back to the View tab, select Freeze Panes and then Unfreeze Panes to unfreeze.

sing the IF formula

The IF formula can be used to determine whether a condition specified by you turns out to be true of false. This can be useful if you need to evaluate a decision based on supplied data.
The syntax for the IF formula is as follows: IF(logical_test, [value_if_true], [value_if_false])

- The logical_test is the condition that you would be checking against. In this case, we are checking to see if the product does not have a tax attached to it.

- The value_if_true is the value displayed if the test turns out to be true. In our case, we calculate the final sales of the item. (Multiply the unit price by the amount of units sold.)

- The value_if_false is the value displayed if the test turns out to be false. In our case, we calculate the total sales, then add the tax to determine the final sales.

Learn to use appropriate "Cell Reference" in Excel Formulas

Add a Check Box in Excel (for example if you want to include a form into you sheet)

To add a check box in Excel, go to the Developer tab, select Insert, Form Control, Checkbox. Drag the shape to add your text box. To edit the location, text, appearance, and options - right click on the checkbox.

Data Group : For a Clean & Neat Data Look

Use Groups to Tie a Range of Cells Together so they can be Expanded/Collapsed

When working with large sets of data, improve your workflow by grouping cells. 
>> Select the rows or columns you wish to group
>> Select the Data tab 
>> Select Group 
>> Then select either Rows or Columns. 

Use the + or – symbol on the left/top to expand and collapse groups. 

You can also create subgroups of data as well as determine subtotals.

Learn to find Top & Least Sales Person Name with VLOOKUP function 

You can nest a function as the lookup_value argument with VLOOKUP, to return an item relating to the lookup_value function. 

In the pictured example, MIN and MAX are nested to return the name of the salesperson associated with those functions.

The formula in cell F6 is =VLOOKUP(MAX(C8:C19),C8:D19,2,0)
The formula in cell G6 is =VLOOKUP(MIN(C8:C19),C8:D19,2,0)

The VLOOKUP function’s fourth argument is range_lookup. For the MIN and MAX functions, the range_lookup is 0, which could have also been written as FALSE or simply omitted altogether. This deals with your desire and expectation that an exact lookup value will be found. In this example, you know that a specific minimum and maximum number will be found in the list in column A.

How to detach 1 text string in two different columns?
shared by paul Prakash 

Go to Data tab, select Text to Columns and follow below steps...

You can easily add a background image to your worksheet. To do this, click on the 'Page Layout' tab, and click on the 'Background' option in the 'Page Setup' group. You can select the picture and it appears in the background of the sheet.

You can use your organizations logo as a background too!

Finding Duplicate values using Conditional formatting

select the cells and apply Conditional formatting and select Duplicate values

Customizing the Ribbon in Excel 

You may want to add additional tabs or shortcuts to speed up your workflow in Excel. Select File, select Options, and select Customize Ribbon. From there, you can add and customize tabs, create your own, and add shortcuts for commands.

How to enter Current Dates and Times in Excel?

To enter the current date and time in Excel, enter =NOW(), 
To enter the current Date in excel, enter =TODAY(). 
The dates and/or times will be automatically updated when the sheet is updated or reopened. 

To insert a fixed date, select a cell, and Press [Cntrl] + [;]
To add a fixed time, Press [Cntrl] + [Shift] +[;]

Qsn: How to calculate the Age between two Dates / Times and show it in a meaningful way?

Sol: Use the simple formula "A-B" (NewDate - OldDate) just same as we subtract two numerical values, but added with a simple trick... Know how...

I have 
Old Date "May 10, 1985" in cell "B2"
New Date "Jan 01, 2014" in cell "C2"

Now the action starts...
In cell "D2" enter the formula =C2-B2
This will return a numerical value "10463" which is infact number of days between two dates, but how to make them more meaningful

>> Select cell "D2" 
>> Right Click 
>> Select "Format Cells"
>> Select "Custom" from Category

==>> For Age difference
>> Enter (yy "Years", mm "Months", dd "Days") in "Type" space
It will convert "10463" to "28 Years, 08 Months, 23 Days" 

==>> For Time difference (in cell D7)
>> Enter (hh "Hours", mm "Minutes", ss "Seconds") in "Type" space

Ta.. Da.... Magic....!!

One more way to get this done is using TEXT() formula, but it is not as flexible as Format Cells option

Combine Text in Cells 

Here’s another little-known feature available in Excel that I bet you’ll find various uses for. It is the ability to combine text from multiple cells into one cell.

I use it frequently for joining text when I receive a file that has first and last names in separate columns. This feature allows me to easily combine the last name from one column with the first name of the other column and show the result as a full name in another column.

We have first name in column B and last name in column C
In cell D1 enter "Full Name" as your column heading.
In cell D2 enter either of the following formulas:

=B2 & " " & C2 (will give you "FirstName LastName")
=C2 & ", " & B2 (will result in "LastName, FirstName")

Count only Positive values in a given range 
=countif(Data-Range,”>0″)

This trick is useful when you need to calculate average of a bunch of numbers but need to exclude zeros and negative values:
=sum(Data-Range)/countif(Data-Range,">0")

When you need to calculate average of numbers but need to exclude zeros only
=sum(Data-Range)/countif(Data-Range,"<>0")

Excel Interface

EXCEL : Calculate the time difference between two times

=TEXT(C4-B4,"H") Hours between two times.

=TEXT(C5-B5,"HH:MM") Hours and minutes between two times.

=TEXT(C6-B6,"HH:MM:SS") Hours, minutes, and seconds between two times

TOP 16 VBA Shortcuts

Who use VBA?