excel links for reference

                                                              MS - Excel :



Official Excel Microsoft Blog:

http://blogs.office.com/b/microsoft-excel/


Basic Excel training in Microsoft office blog:

http://blogs.office.com/b/microsoft-excel/archive/2011/06/28/take-the-next-steps-in-growing-your-excel-skills.aspx


Excel Archive Forums

http://www.mrexcel.com/archive/index.html 

Writing Macros:

http://office.microsoft.com/en-us/excel-help/CH010369017.aspx?9656

Vlookup information:

http://vlookupweek.wordpress.com/ 





Vlookup Example:

http://colinlegg.wordpress.com/2012/03/25/binary-searches-with-vlookup/

updating prices in master list using vlookup:

http://blogs.office.com/b/microsoft-excel/archive/2012/03/28/-vlookup-tutorial-from-bill-jelen.aspx




Counting Unique values / Text with out duplicating text items:

http://office.microsoft.com/en-us/excel-help/count-unique-values-among-duplicates-HP010070481.aspx?CTT=3

About Frequency:

http://office.microsoft.com/en-us/excel-help/frequency-function-HP010062503.aspx?CTT=5&origin=HP010070481



Match:

http://office.microsoft.com/en-us/excel-help/match-function-HP010062414.aspx?CTT=5&origin=HP010070481



Holding control to enter data automatically in other sheets:

http://office.microsoft.com/en-us/excel-help/enter-data-manually-in-worksheet-cells-HP001216364.aspx


to count the duplicates  =countif($A:$A,A1)

http://answers.yahoo.com/question/index?qid=20081114050312AAYxL2y

To add name kumar in the end use concatenate:

CONCATENATE(E13,"kumar")

http://stackoverflow.com/questions/2703431/add-common-prefix-to-all-cells-in-excel


IF examples:

http://office.microsoft.com/en-us/excel-help/if-function-HP010342586.aspx?097



Forumula's in Excel:

http://office.microsoft.com/en-us/excel-help/excel-functions-by-category-HP010342656.aspx#BMcompatibility_functions


Avoiding Duplicates: Error message, before saving the workbook:

Source:
http://excelzoom.com/2009/05/data-validation-to-avoid-duplicate-values/

Click Data | Validation.

On the Data Validation window’s Settings tab, select Allow: Custom.

In the Formula box, type the following formula: =MATCH(A1,$A:$A,0)=ROW(A1) (if you’re using this in a different column than column A, change the “A1″ and “$A”‘s in the formula to whichever column you’re using)

Click OK.

Adding List drop down using data validation:

Source: 

http://excelzoom.com/2009/03/drop-down-lists-using-data-validation/


To do this, follow the steps below.

• Select the cell where you want your custom list to appear (if you want the same list to appear in multiple cells, you can copy the list later).
• Click Data | Validation.
• On the Data Validation window’s Settings tab, select Allow: List, and make sure the In-cell dropdown box is checked.
• In the Source box, type the items you want in the drop down list separated by a comma. Alternatively, you can refer to a range of cells in the same worksheet that contains your list, or refer to a named range elsewhere in your workbook.
• Click OK.

Pivot tables examples:

http://excelzoom.com/2009/03/using-pivottables/


http://www.pivot-table.com/

www.free-training-tutorial.com

www.homeandlearn.co.uk

Consolidate example: 

http://blogs.office.com/b/microsoft-excel/archive/2012/07/05/merge-worksheets-in-excel.aspx


Recording Macro:

http://excelzoom.com/2009/02/install-and-use-macros/

Automatically creating Index for files:

http://excelzoom.com/2009/03/automatically-create-an-index-for-your-excel-file/



Common Formula's in Excel:

http://office.microsoft.com/en-us/excel-help/CH001000491.aspx


Hiding formulas or locking cells:

http://office.microsoft.com/en-us/excel-help/display-or-hide-formulas-HA102430137.aspx

Excel shortcuts

Ctrl+A Select All

Ctrl+B Bold

Ctrl+C Copy

Ctrl+D Fill Down

Ctrl+F Find

Ctrl+G Goto

Ctrl+H Replace

Ctrl+I Italic

Ctrl+K Insert Hyperlink

Ctrl+N New Workbook

Ctrl+O Open

Ctrl+P Print

Ctrl+R Fill Right

Ctrl+S Save

Ctrl+U Underline

Ctrl+V Paste

Ctrl W Close

Ctrl+X Cut

Ctrl+Y Repeat

Ctrl+Z Undo

F1 Help

F2 Edit

F3 Paste Name

F4 Repeat last action

F4 While typing a formula, switch between absolute/relative refs 

F5 Goto

F6 Next Pane

F7 Spell check

F8 Extend mode

F9 Recalculate all workbooks

F10 Activate Menubar

F11 New Chart

F12 Save As

Ctrl+: Insert Current Time

Ctrl+; Insert Current Date

Ctrl+" Copy Value from Cell Above

Ctrl+' Copy Formula from Cell Above

Shift Hold down shift for additional functions in Excel's menu 

Shift+F1 What's This?

Shift+F2 Edit cell comment

Shift+F3 Paste function into formula

Shift+F4 Find Next

Shift+F5 Find

Shift+F6 Previous Pane

Shift+F8 Add to selection

Shift+F9 Calculate active worksheet

Shift+F10 Display shortcut menu

Shift+F11 New worksheet

Shift+F12 Save

Ctrl+F3 Define name

Ctrl+F4 Close

Ctrl+F5 XL, Restore window size

Ctrl+F6 Next workbook window

Shift+Ctrl+F6 Previous workbook window

Ctrl+F7 Move window

Ctrl+F8 Resize window

Ctrl+F9 Minimize workbook

Ctrl+F10 Maximize or restore window

Ctrl+F11 Inset 4.0 Macro sheet

Ctrl+F12 File Open

Alt+F1 Insert Chart

Alt+F2 Save As

Alt+F4 Exit

Alt+F8 Macro dialog box

Alt+F11 Visual Basic Editor

Ctrl+Shift+F3 Create name by using names of row and column labels 

Ctrl+Shift+F6 Previous Window

Ctrl+Shift+F12 Print

Alt+Shift+F1 New worksheet

Alt+Shift+F2 Save

Alt+= AutoSum

Ctrl+` Toggle Value/Formula display

Ctrl+Shift+A Insert argument names into formula

Alt+Down arrow Display AutoComplete list

Alt+' Format Style dialog box

Ctrl+Shift+~ General format

Ctrl+Shift+! Comma format

Ctrl+Shift+@ Time format

Ctrl+Shift+# Date format

Ctrl+Shift+$ Currency format

Ctrl+Shift+% Percent format

Ctrl+Shift+^ Exponential format

Ctrl+Shift+& Place outline border around selected cells

Ctrl+Shift+_ Remove outline border

Ctrl+Shift+* Select current region

Ctrl++ Insert

Ctrl+- Delete

Ctrl+1 Format cells dialog box

Ctrl+2 Bold

Ctrl+3 Italic

Ctrl+4 Underline

Ctrl+5 Strikethrough

Ctrl+6 Show/Hide objects

Ctrl+7 Show/Hide Standard toolbar

Ctrl+8 Toggle Outline symbols

Ctrl+9 Hide rows

Ctrl+0 Hide columns

Ctrl+Shift+( Unhide rows

Ctrl+Shift+) Unhide columns

Alt or F10 Activate the menu

Ctrl+Tab In toolbar: next toolbar

Shift+Ctrl+Tab In toolbar: previous toolbar

Ctrl+Tab In a workbook: activate next workbook