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

The default shape of the cell comments in Excel is rectangular and which is not attractive. Sometimes you may require different shapes as per your need in different situation.
Comments with different shapes
To do this just have a look at next section. I have explained it clearly.
3. Format painter 
Sometimes we do some formatting to some cells and want to have the same formatting at some other cells. What to do in such situations ?
Apply same formatting
Format painter

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.

Automatic fill series

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.
Go to special
Attendance sheet
  • After you do the above step, all the non-blank cells will be selected. You can now change their background color. Write something and press Ctrl+Enter. It will be written to all the non-blank cells simultaneously.

Also read


Courtesy: funbutlearn.com

Show Comments

One Response

  1. manish yadav

Leave a Reply