Excel Function : Ignore Positive/Negative Number with ABS()

Number Absolute Value
80 80 =ABS(C4)
-10.25 10.25 =ABS(C5)
36.25 36.25 =ABS(C6)
23.64 23.64 =ABS(C7)
What Does it Do ?
This function calculates the value of a number, irrespective of whether it is positive or negative.
Syntax
 =ABS(CellAddress or Number)
Formatting
The result will be shown as a number, no special formatting is needed.
Example
The following table was used by a company testing a machine which cuts timber.
The machine needs to cut timber to an exact length.
Three pieces of timber were cut and then measured.
In calculating the difference between the Required Length and the Actual Length it does
not matter if the wood was cut too long or short, the measurement needs to be expressed as
an absolute value.
Table 1 shows the original calculations.
The Difference for Test 3 is shown as negative, which has a knock on effect
when the Error Percentage is calculated.
Whether the wood was too long or short, the percentage should still be expressed
as an absolute value.
Table 1
Test
Cut
Required
Length
Actual
Length
Difference Error
Percentage
Test 1 120 120 0 0%
Test 2 120 90 30 25%
Test 3 120 150 -30 -25%
=D36-E36
Table 2 shows the same data but using the =ABS() function to correct the calculations.
Table 2
Test
Cut
Required
Length
Actual
Length
Difference Error
Percentage
Test 1 120 120 0 0%
Test 2 120 90 30 25%
Test 3 120 150 30 25%
=ABS(D45-E45)

Excel Function : Age Calculation by DATEDIF

You can calculate a persons age based on their birthday and todays date.
The calculation uses the DATEDIF() function.
The DATEDIF() is not documented in Excel 5, 7 or 97, but it is in 2000.
(Makes you wonder what else Microsoft forgot to tell us!)
Birth date : 17-Sep-81
Years lived : 36  =DATEDIF(C8,TODAY(),”y”)
and the months : 0  =DATEDIF(C8,TODAY(),”ym”)
and the days : 28  =DATEDIF(C8,TODAY(),”md”)
You can put this all together in one calculation, which creates a text version.
Age is 36 Years, 0 Months and 28 Days
 =”Age is “&DATEDIF(C8,TODAY(),”y”)&” Years, “&DATEDIF(C8,TODAY(),”ym”)&” Months and “&DATEDIF(C8,TODAY(),”md”)&” Days”
Another way to calculate age
This method gives you an age which may potentially have decimal places representing the months.
If the age is 20.5, the .5 represents 6 months.
Birth date : 17-Sep-81
Age is : 36.08  =(TODAY()-C23)/365.25

Smart Excel : Speedup the Excel with Shorts

Shortcuts help you navigate and working fastly in excel :

 

Excel Shortcuts

 

1. To format any selected object, press ctrl+1
2. To insert current date, press ctrl+;
3. To insert current time, press ctrl+shift+;
4. To repeat last action, press F4
5. To edit a cell comment, press shift + F2
6. To autosum selected cells, press alt + =
7. To see the suggest drop-down in a cell, press alt + down arrow
8. To enter multiple lines in a cell, press alt+enter
9. To insert a new sheet, press shift + F11
10. To edit active cell, press F2 (places cursor in the end)
11. To hide current row, press ctrl+9
12. To hide current column, press ctrl+0
13. To unhide rows in selected range, press ctrl+shift+9
14. To unhide columns in selected range, press ctrl+shift+0
15. To recalculate formulas, press F9
16. To select data in current region, press ctrl+shift+8
17. To see formulas in the worksheet, press ctrl+shift+` (ctrl+~)
18. While editing formulas to change the reference type from absolute to relative vice versa, press F4
19. To format a number as currency, press ctrl+shift+4 (ctrl+$)
20. To apply outline border around selected cells, press ctrl+shift+7
21. To open the macros dialog box, press alt+F8
22. To copy value from above cell, press ctrl+’
23. To format current cell with comma formats, press ctrl+shift+1
24. To go to the next worksheet, press ctrl+shift+pg down
25. To go to the previous worksheet, press ctrl+shift+pg up

Smart Excel : Speed the Excel Work

1. Use keyboard shortcuts

Following tricks help you to work smooth and fasting., shortcuts are the easiest way to speed up excel work.:

Shortcut Feature
CTRL + Arrow Keys Quickly move around within your data. For example, CTRL + Down Arrow selects the last cell below the current cell that has data in it before it hits a blank cell.
F2 Begin editing the selected cell (just like double clicking the cell)
F4 Repeat the last action you took (e.g., applying formatting or deleting a row)
F12 The same as selecting Save As… from the File tab, but much quicker.
When Editing Formulas
F4 F4 will toggle the leading $ which makes the column and row parts of your references static (A1, $A$1, $A1, A$1)
F9 Also while editing formulas, pressing F9 will evaluate the selected part of your formula. This can be useful when error-checking your formulas.
TAB Autocomplete the name of the function that matches what you’ve typed. For example, if you type “=CON” then press TAB, the formula will autocomplete to “=CONCATENATE(“.
Alt + Enter Insert a line break into a cell without leaving formula editing mode.

2. Conditional Formatting to view duplicate cells

excel3

Smart Tip: You can filter table columns by cell color. These two features together allow you to filter a table to show only duplicates.

 

3. Define Named Ranges

Named Ranges make it easier to refer to single cells or a range of cells in a formula by giving them a name. Simply select a cell or range of cells, click the “Name” box in the Formula tab and enter a name. Now, when you need to refer to the Named Range in a formula, just start typing its name and Excel will know which cell(s) you are referring to.

excel4

4. “New Window” on the “View” tab to look different sheets from the same file in separate windows


excel1

5. Create drop-down lists with Data Validation

It allow you to ensure that correct data is being entered into a spreadsheet, and can also alert you to incorrect data.

 

excel5

 

 

6.  Quick Access Toolbar (QAT)

To add a feature to the QAT, right-click the item in the ribbon and click “Add to Quick Access Toolbar.”


excel2

Smart Tip: Add the “Paste Special” function to the QAT and you’ll be able to paste values or formats with two quick keystrokes (ALT + 1, then V).

 

7. Use INDEXMATCH formulas

Smart Tip : Instead of  VLOOKUP formulas with INDEX-MATCH

 

8. Employ Tables and Structured References

Tables and Structured References simplify formula writing. Similar to Named Ranges, columns of data will be named automatically by virtue of inclusion in a table. Not only does this keep your data clean and easy to understand, it’s a cinch to refer to data on other sheets, as well.

 

excel6

 

 

9. Use the Group feature to quickly show and hide columns or rows

.

excel7

 

 

 

Smart Excel ==> Delete Blank Rows in Excel

Below example make you learn how to delete blank rows or rows that contain blank cells.

 

1. On the Home tab, in the Editing group, click Find & Select.

 

Click Find & Select

2. Click Go To Special.

Click Go To Special

3. Select Blanks and click OK.

Select Blanks

Excel selects the blank cells.

Delete Blank Rows in Excel

4. On the Home tab, in the Cells group, click Delete.

Click Delete

5. Click Delete Sheet Rows.

Delete Sheet Rows

Result:

Delete Blank Rows Result