5 Interesting and Funny Excel Tricks You Must Know

I always try to do something out of nothing. Today I was just playing with Excel and suddenly one idea came to my mind. I thought, if I am learning a lot of new thing from Excel then why not I share them to others. Really MS Excel is a sea of knowledge. Whatever proficiency you may have in Excel but still I would say there would be a lot of thing yet to be known. I just love MS Excel. Lets learn these funny and interesting Excel tricks.

1. Calculate your age with Excel

A very unfamiliar, undocumented but very useful Excel function is DATEDIF. This will calculate the difference between 2 dates. This will help you to calculate your age. The syntax for DATEDIF is
=DATEDIF(StartDate, EndDate, Interval)
The interval can be Y, M, D, YM, MD and YD.
Example: Just copy and paste the following in a cell in Excel and press enter

=DATEDIF(“03-Dec-1988″,”01-Oct-2012″,”Y”)

If you want your exact age in year-months and days then copy and paste the following

=DATEDIF(“03-Dec-1988″,”01-Oct-2012″,”Y”) & ” Years ” & DATEDIF(“03-Dec-1988″,”01-Oct-2012″,”YM”) & ” Months ” & DATEDIF(“03-Dec-1988″,”01-Oct-2012″,”MD”) & ” Days”

Also read Excel Formulas: 10 Formulas That Helped Me Keep My Job

2. Change the shape of the cell comments

Instead of doing the same formatting again and again, you can just select the cell(s) where you have already done the formatting and then click the button Format Painter present in Home tab. Then select the cell(s) where you want to apply the formatting. The formatting will be automatically applied.

Also read

4. Automatic fill series

Sometimes we need to fill some series in Excel. Lets say some products name starts with DK3001 and continues as DK3002, DK3003, DK3004, DK3005, ………….., DK3999 and we need to fill the entire series in a column. How to do this ? Will you go by writing one by one ? No worries. I have a solution. Just write DK3001 and keep your mouse at the bottom right black dot of the cell.

Just a single click at the black dot and drag it down and leave at row 10. Automatically the entire series will be generated. Is not it amazing ?

Also read How to Use Flash Fill in Excel 2013

5. Format only Non-blank cells in Excel

Sometimes we need to write something in all the non-blank cells in a particular range at a time or we may also need to format all the non-blank cells present in a particular range. Lets consider a school’s attendance sheet. In the sheet if any cell is blank means the student is absent for the day. The cells which are blank, I want make them red and want to write “ABSENT” in it. How to do this ? I have the solution…

• Just select the cells(blank & non-blank) in Excel and press f5.
• A small window will appear, press the button Special.
• Another window will appear, select the radio button Blanks and click OK.