Excel Tips | Adding Macros to your toolbar

So lets see some tips about excel .we know what is macros and its importance so how can
we add macros on our toolbar here is the tip

a.Choose Customize from the Tools menu. Excel displays the Customize dialog box.
Make sure the Toolbars tab is selected.

b.In the list of toolbars, make sure there is a check mark beside the toolbar to which you want your macro added. The check mark ensures that the toolbar is displayed on the screen.

c.Click on the Commands tab.

d.In the list of Categories, choose the Macros entry. Your macros should then appear in the Commands list.

e.In the Commands list, select the macro you want assigned to a toolbar.

f.Using the mouse, drag the macro from the Commands list to the location on the toolbar where you want it to appear.

g.When you drop the macro, it appears on the toolbar.

h.To add more macros, repeat steps 6 through 8.

i.Click on Close and then you can see your result at you toolbar


Hope you liked it..

Some of Advanced Formulas & Tips

Excel is the very useful and important resource so i thought let I may share with you some for the useful advance excel tips and formulas with u


• Multi-Conditional Count/Sum:
(Count)
Synt. =SUMPRODUCT(--(Rng_1=Val1), --(Rng_2=Val2))

ex. =SUMPRODUCT(--(A2:A100 = “abcd”), --(B2:B100 = 1234))

(Sum)
Synt. =SUMPRODUCT(--(Rng_1=Val1), --(Rng_2=Val2), Rng_to_Sum)

ex. =SUMPRODUCT(--(A2:A100 = “abcd”), --(B2:B100 = 1234), H2:H100)

Important: All ranges must be of equal size. Also, cannot use whole-column ranges such as A:A.

Note: Multi-Conditional Count and Sum can be accomplished with a Pivot Table but for dynamic spreadsheet design these formulas are excellent (but memory hogs.) Also, up to 30 conditions when counting, 29 conditions when summing (plus one range to sum.)

• INDEX/MATCH Combination (HLOOKUP/VLOOKUP alternative):
Synt. =INDEX(Rng, MATCH(Val_to_Look_up, Lookup_Rng, [0 or 1]), [Col_Number])

ex. =INDEX(A:C,MATCH(“abcd”,A:A, 0), MATCH(123,1:1, 0))

Note: The “[ ]” in Excel means that this argument is optional.

Explantion: This is more versatile than HLOOKUP/VLOOKUP. With the INDEX/MATCH combination you can use it as a substitute for HLOOKUP or VLOOKUP, -or-, it can be used to look across columns and down rows for a value, as the above example illustrates. So, the above formula will look down column A until it finds “abcd” and then it will look across columns in row 1 until it finds the value 123. It will then return the value at the “abcd”/123 intersection.

• Determine Last Used Row:
(Numeric)
ex. =INDEX(A:A, MATCH(9.99999999999999E+307,B:B))

(Alpha)
ex. =INDEX(A:A,MATCH(REPT(“z”,255),B:B))

• Using Last Used Row in a Formula:
ex. =SUM(A2:INDEX(A:A,MATCH(9.99999999999999E+307,B:B)))
or
ex. =SUM(A2: INDEX(A:A,MATCH(REPT(“z”,255),B:B)))

Note: This technique is important for faster formula calculation, as in when performing multi-conditional Counts and Sums because you want to use the smallest range possible. Also, aids in dynamic spreadsheet design because the ranges don’t need to be manually altered.

• Unique Value Counting
Synt. =SUMPRODUCT((Rng_1<>"")/COUNTIF(Rng_1, Rng_1&""))

ex. =SUMPRODUCT((A2:A100<>"")/COUNTIF(A2:A100,A2:A100&""))

Note: Use the smallest range possible due to slow calculation times. See Using Last Used Row in a Formula above.

• Conditional Count/Sum Across Multiple Sheets
1. On the Worksheet Menu Bar go to Insert/ Name/ Define
2. In the “Names in workbook:” inputbox, type: Shts.
3. In the “Refers to:” inputbox, type: ={“Sheet1”, “Sheet2”, “Sheet3”} (use the actual names of the worksheets.)
4. Now you can use the following formulas to conditionally count or sum across worksheets:
ex. =SUMPRODUCT(COUNTIF(INDIRECT(Shts&"!A:A"),">0"))
or
ex. =SUMPRODUCT(SUMIF(INDIRECT(Shts&"!A:A"),">100",INDIRECT(Shts&"!A:A")))