Filter by the Selected Cell

Excel 2007 added the feature to filter table fields by the selected cell.

What does this mean?

Focus on analyzing data instead of defining filter criteria each time.

This quick filter method is ideal for selecting categories, for example: cities, products, subjects, suppliers, etc.

You can filter by:

• Cell's value
• Cell's color
• Cell’s font color
• Cell's icon

How to…

1. Select the cell on which you want to apply the filter or right click directly on it.

2. Go to: Filter>Filter by Selected Cell’s value

3. Click

Sum/Count/Average only Visible Rows with SUBTOTAL Function

Hidden rows are not excluded in SUM/COUNT/AVERAGE/MAX/MIN functions; what does this mean? Your results may appear greater, distorted, etc.

How to avoid this situation? Use SUBTOTAL function…

Follow the below steps :

1. Write SUBTOTAL function

2. Specify “function_num” as 109. 101 to 111 options ignore values of rows hidden by the Hide Rows Command.

3. Complete the Formula: =SUBTOTAL(109,B2:B7)

4. Enter

If B3 & B4 are hidden rows, Then they would not be included in calculation.


.

Check VLOOKUP Formula for #NA Error without Slowing Down Computations (Excel 2007)

You can see across forums that the way to trap the #N/A error in VLOOKUP Formulas is this way:
=IF(ISERROR(VLOOKUP(A2,$H$18:$I$21,2,FALSE)), "Not found",VLOOKUP(A2,$H$18:$I$21,2,FALSE))


This Formula calls VLOOKUP twice, this means double processing.

Avoid losing time when you work with intensive computing spreadsheets. Use IFERROR instead…

Embed the VLOOKUP Formula into an IFERROR function. The syntax is IFERROR(value,value_if_error). The Formula is done:

=IFERROR(VLOOKUP(A2,$H$18:$I$21,2,FALSE),"Not Found")

.

Excel Famous Ranges & Cells

Good One :)

1. IM21 The legal drinking age cell
2. K9 The dog cell
3. AK47 The assault weapon cell
4. HI5 The alternate handshake cell
5. AH:HA The discovery range
6. F16 The fighter jet cell
7. AM:FM The radio range
8. ET2 The Brute' cell
9. BY:BY The farewell range
10. IC2 The double-vision cell
11. IQ100 The average intelligence cell
12. HO:H...O The Santa Claus range
13. GO2 The destination cell
14. FU2 The same to you cell
15. EX2 The second former spouse cell
16. CU8 The oil-rich country cell
17. AG1:GB1 The '00 disputed Florida vote range
18. T42 The old soft-shoe cell
19. U2 The Irish rock group cell
20. C4 The explosive cell
21. R2:D2 The android range
22. I1:U1 The tied game cell
23. H8:U2 The ex-wife range
24. IN2:CA9 The dog-lover range
25. D84:U2 The double date range
26. I812 The Monica Lewinsky meets Linda Lovelace cell
27. I12:CU2 The "when can we meet" cell

How to Get a Stock Quote ... COOL FEATURE

1.Before you proceed, make sure that Smart Tags are turned on @ http://support.microsoft.com/kb/289148

2.Enter a recognized U.S. Financial Symbol, for example MSFT,CSC in a cell. Make sure you enter the symbol in upper-case letters.

3.Click outside the cell.

4.Move your mouse pointer over the purple triangle that appears in the lower right corner of the cell and then click the arrow besides the ‘Smart Tags Actions‘. A list of options will appear.

5.Click ‘Insert refreshable stock price‘

6.In the ‘Insert Stock Price‘ dialogue box, choose from the following two options.

* Insert the stock price on a new worksheet.
* Insert the stock price on a new location on a worksheet you are currently working on.

NOTE: The data returned might fill up a considerable portion of your worksheet.