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.
- 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
- How to calculate Average Excluding Outlayers from the Calculation – VBA Macros
- 10 advanced formatting tricks for Excel users
Courtesy: funbutlearn.com
Change the Shape/Color of Cell Comments in Excel
- Right click on Quick Access Toolbar–>Click on Customize Quick Access Toolbar. It will open another window.
- You can see there a dropdown called Choose command from. Select All command from the list and search for Change shape in the list. Click on Add and click OK.
- Now a Change shape button will appear in your Quick Access Toolbar.
2. Changing the shape of the cell comments
- Now Right click on the cell where you have inserted the comment–>Click on Edit Comment
- A rectangular box will appear with the comment, click on the border of the box.
- Now go to the Change shape button which you have just added to the Quick Access Toolbar and choose a shape of your choice.
3. Changing the background colors and border
- To change the background color, right click on the cell where you have inserted the comment–>Click on Edit Comment
- A rectangular box will appear with the comment, right click on the border of the box–>Click on Format comment…
- Now you can change the color, margin, border etc.