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
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”
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.
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.
- 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.
- How to calculate Average Excluding Outlayers from the Calculation – VBA Macros
- 10 advanced formatting tricks for Excel users