Playing with Colors

http://www.cpearson.com/excel/colors.aspx

Using SumProduct

Sumproduct is by far one of the most powerful and versatile functions provided by Excel. In its most basic form, SUMPRODUCT multiplies corresponding members in given arrays, and returns the sum of those products. This page discusses the classic use of SUMPRODUCT, how creativity and inbuilt flexibility has enabled it to evolve into a far more useful function, and explains some of the techniques being deployed. Finally, some examples of SUMPRODUCT show its versatility.

The link below shows the use of Sumproduct.


http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Excel macro help from MSDN

http://social.msdn.microsoft.com/Forums/en-US/isvvba/threads

Shortcuts using Windows logo key

Press                                   To

Windows logo key                  Display or hide the Start menu

Windows logo key+L              Lock the computer

Windows logo key+BREAK -Display the System Properties dialog box

Windows logo key+D              Show the desktop

Windows logo key+M       Minimize all windows

Windows logo key+SHIFT+M   Restore minimized windows

Windows logo key+E         Open My Computer

Windows logo key+F             Search for a file or folder

CTRL+Windows logo key+F     Search for computers

Windows logo key+F1           Display Windows Help

Windows logo key+R             Open the Run dialog box

Windows logo key+U             Open Utility Manager

Application key                      Display the Shortcut menu for the selected item

Top 10 Navigation Tips for Microsoft Excel

These are my top 10 Navigation Tips … I do them without thinking, but have recently had to show a few younger folks that using your mouse isn’t the most efficient way to navigate Excel.  For anyone else who needs a navigation refresher, these will make working in your spreadsheets much faster:

1. [Ctrl]+[Home] takes you to cell A1
2. [Ctrl]+[End] takes you to the last data cell in your worksheet
3. [Ctrl]+[PgDn] takes you to the next worksheet
4. [Ctrl]+[PgUp] takes you to the previous worksheet
5. [End]+[↑] takes you to the top of the row
6. [End]+[↓] takes you to the bottom of the row
7. [End]+[→] takes you to the furthest right entry in the row
8. [End]+[←] takes you to the furthest left entry  in the row
9. [Ctrl]+[A] selects/highlights all cells that you are using in a worksheet
10. [Shift]+[Spacebar] selects/highlights your row

http://businesstoolsblog.com/

Excel Dashboard

Excel dashboards are excellent way of presenting data... 

Here are few dashboards we can use.

6 part tutorial for Excel Dashboards

http://chandoo.org/wp/2008/08/20/create-kpi-dashboards-excel-1/

Match two excel files

The best tool that will help you to compare,
update and synchronize your Excel spreadsheets.

Synkronizer is an easy-to-use, yet extremely powerful, Excel tool for comparing and synchronizing Excel spreadsheets and Excel databases.

Synkronizer makes your life easier by saving your time and speeding up your data processing. It's a must-have Excel add-in for everyone from power users to beginner users.

http://www.synkronizer.com/

Extracting Text from Names using Find

Here Are A Few Examples Of How You Can Use Excels Text Functions To Extract Parts Of Peoples Names From A List. The Same Formulas Could Also Be Used On Any Text, They Don't Have To Be Names

Full Name	Formula Used	Result
A2=David Hawley	=LEFT(A2,FIND(" ",A2))	David
A3=Ray Goodwin	=MID(A3,FIND(" ",A3,1)+1,LEN(A3))	Goodwin
A4=Graeme Dee	=LEFT(A4)&MID(A4,FIND(" ",A4)+1,1)	GD
A5=Suzanne Greenhouse	=LEFT(A5)&MID(A5,FIND(" ",A5),LEN(A5))	S Greenhouse
A6=Fred Baker	=LEFT(A6,FIND(" ",A6))&MID(A6,FIND(" ",A6)+1,1)	FredB
A7=Mary Hardwick	=MID(A7,FIND(" ",A7,1)+1,LEN(A7)) & " " & LEFT(A7,FIND(" ",A7))	Hardwick Mary
A8=Banana	=LEN(A8)-LEN(SUBSTITUTE(A8,"a",""))	3 i.e occurence of "a"

http://www.ozgrid.com/Excel/TextFormulas.htm

Vlookup and Match

How do you use Vlookup and Match functions to get great results.

This solution can be used when you need to get the output for a criteria that should adhere to two criterias

In the example below we see how we get the price for 1kg of a, 3kg of a, 1kg of c and 2kg of b.

e.g.

	1	2	3
a	10	20	30
b	40	50	60
c	70	80	90

FORMULA: =VLOOKUP(B12,B5:E8,MATCH(C12,B5:E5,0))

Explanation to match

Match will give you the column value to lookup. 

Syntax – MATCH(lookup_value,lookup_array,match_type)Match_type is the number -1, 0, or 1. Match_type specifies how Microsoft Excel matches lookup_value with values in lookup_array.

Output

a	1	10
a	3	30
c	1	70
b	2	50

Export Exchange Global Address List to Access

Outlook doesn't give you an option to export the Exchange Global Address list to any format. You don't even have the option to sort while searching, which drives me crazy.. If I want to see who works in the Houston office, I don't want to have to go through the entire list.

Sure, you can get your exchange administrator to export a list for you, but thankfully there's an easy way using Microsoft Access.

Prerequisite: You will need to make sure you do this on a computer that has Outlook installed and configured.

Start by opening Access, and creating a blank database. Now select File \ Get External Data \ Import

You will see a standard file open dialog. In here, you will open the drop-down menu for Files of type, and you should see Exchange() in the list:

As soon as you select that, it will automatically open the Import Exchange/Outlook Wizard. Select the Global Address List in the tree, and then hit Next. Note that you could export other lists using this same method if you wanted to.

At this point, you will be asked where you want to store the data. If this is the first time, you will just select new table. You could alternatively push the data into any table you want, but we're just going to import the whole thing.

Now you should see the new table in your database, and it will contain the entire contents of the global address list.

http://www.howtogeek.com/howto/database/access/export-exchange-global-address-list-to-access/