excel examples

======================================================================

Microsoft Sites:

Excel, Word, Powerpoint course Index:

http://office.microsoft.com/en-in/training/results.aspx?qu=excel%20training&ex=4&page=2&queryid=3a38f69d-9b0e-421c-a387-0b22d549c6c1

Excel shortcuts:(This is very informative)

http://office.microsoft.com/en-in/excel-help/introduction-0-52-RZ102673162.aspx?CTT=1&section=2

http://office.microsoft.com/en-in/excel-help/excel-2010-keyboard-shortcuts-i-ctrl-key-shortcuts-RZ102673162.aspx?section=5

Know Excel 2007

http://office.microsoft.com/en-in/training/get-to-know-excel-2007-create-your-first-workbook-RZ010076674.aspx?CTT=1

Excel link for learning basic formulas with examples like vlookup etc.,: 

http://office.microsoft.com/en-in/excel-help/examples-of-commonly-used-formulas-HP005200127.aspx

http://www.baycongroup.com/el0.htm

Some Basic Macro examples like macro for deleting duplicate rows-- writing macros for uppercase, lowercase text etc.,

http://support.microsoft.com/search/default.aspx?query=macros+in+excel+examples&catalog=LCID%3D1033&mode=r


Normal Excel blogs for excel tips:

http://spreadsheetpage.com/index.php/site/tips

http://spreadsheets.about.com/od/excelformulas/u/advanced_topics_user_path.htm

Simple Macros

http://msdn.microsoft.com/en-us/library/office/ee264224(v=office.12).aspx - - Good one

http://www.excelvbamacros.com/

VBA Examples:

http://msdn.microsoft.com/en-us/office/hh360994.aspx

http://msdn.microsoft.com/en-us/library/ee814737.aspx -- Very good

http://msdn.microsoft.com/en-us/library/dd721892.aspx

VBA Basics - http://www.datapigtechnologies.com/ExcelMain.htm

Excellent! But few examples

http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=315

http://pubs.logicalexpressions.com/pub0009/LPMArticle.asp?ID=302

http://www.your-save-time-and-improve-quality-technologies-online-resource.com/free-vba-tutorials.html

VBA Tutorial: http://www.excelfunctions.net/Excel-VBA-Tutorial.html

http://quickvba.blogspot.in/

http://www.excelvbamacros.com/p/contact-us.html

http://excelvbatutor.com/vba_chp7.htm

Other sites:

http://www.internet4classrooms.com/technology_tutorials/advanced_excel_topics_charts_technology_tutorials.htm

http://www.excelforum.com/excel-general/

http://spreadsheetpage.com/index.php/tips

http://excelpoweruser.wordpress.com/2012/11/06/comment-formatting/

Practicing with data bases / Examples can download data

http://office.microsoft.com/en-in/templates/desktop-student-database-TC001225355.aspx

http://office.microsoft.com/en-in/templates/?CTT=97

Yahoo answers links for macros and vba basic examples

http://answers.yahoo.com/question/index?qid=20080314122402AA5Xkbg


VBA Access

http://www.functionx.com/vbaccess/index.htm

http://www.functionx.com/access/applications/ceilinn.htm


==============================================

Other Examples:

1. Index, Match
2.Max, Offset

Data Validation use list column for country, region, city each depending on other like country india, if you select india then in the next column it should display states, you select one state say andhra pradesh, then in the third column you should be able to select district all these column lists are interdependent on each other.

http://www.contextures.com/xldataval02.html

	A	B	C	D	E	F	G	H
1	color\model	Model1	Model2	Model3	Model4		Arguments
2	Brown	5	4	3	2		model	color
3	Red	0	2	8	9		Model2	blue
4	Green	10	15	17	20
5	Blue	8	9	10	11
6	Rainbow	1	8	40	80
7
8	4	=MATCH(H3,A2:A6,0)
9	2	=MATCH(G3,B1:E1,0)
10	9	=INDEX(A1:E6,(MATCH(H3,A2:A6,0)+1),(MATCH(G3,B1:E1,0)+1))
11	9	=INDEX(A1:E6,5,3)    Row=5, col=3 equivalent to above
12	9	=INDEX(A1:E6,MATCH("Blue",A1:A6,0),MATCH("Model2",A1:E1,0))     equivalent to above

Source: http://dmcritchie.mvps.org/excel/vlookup.htm

A	B	C	D	E
1	Color Name	Tint Formula	Cost
2	8422 Hampestead	B1 C4 F1	$12.25
3	8427 Wishing Star	M2 T8	$ 6.35
4	8433 Ashford	C7 F12 S8	$ 8.75
5	8449 Modernist	I6 S5 T4	$ 9.25
6	8460 Bridal Wreath	AXN1 D12 E6	$ 9.45
7		Most Expensive:	$12.25	8422 Hampestead
8
9	CELL	Formula
10	C7	=MAX(C2:C6)
11	D7	=OFFSET(A1,MATCH(C7,C2:C6,0),0)
12
13	you can rewrite formulas as
14	C7	=MAX(C$2:OFFSET(C7,-1,0))
15	D7	=OFFSET($A$1,MATCH(C7,C$2:OFFSET(C7,-1,0),0),0)

Source: http://dmcritchie.mvps.org/excel/vlookup.htm

	A	B	C	D	E	F	G	H
1	color\model	Model1	Model2	Model3	Model4		Arguments
2	Brown	5	4	3	2		model	color
3	Red	0	2	8	9		Model2	blue
4	Green	10	15	17	20
5	Blue	8	9	10	11
6	Rainbow	1	8	40	80
7
8	4	=MATCH(H3,A2:A6,0)
9	2	=MATCH(G3,B1:E1,0)
10	9	=INDEX(A1:E6,(MATCH(H3,A2:A6,0)+1),(MATCH(G3,B1:E1,0)+1))
11	9	=INDEX(A1:E6,5,3)    Row=5, col=3 equivalent to above
12	9	=INDEX(A1:E6,MATCH("Blue",A1:A6,0),MATCH("Model2",A1:E1,0))     equivalent to above

	A	B	C	D	E
1	Cat1	SubCat1	Data1	Data2	Data3
2	1	15	1.2	4.5	6
3	1	18	2.2	6	8.2
4	1	22	3.1	7.1	9
5	2	15	3	5	6
6	2	18	3.1	5.1	6.1
7	2	22	3.2	5.4	6.2
8	3	15	4	4	4
9	3	18	5	5	5
10	3	22	6	6	6

=INDEX(C2:C10,MATCH(1,(A2:A10=G1)*(B2:B10=H1),0))

array entered with ctrl + shift & enter, assuming we are retrieving from column C, where G1 holds one lookup and H1 another.. Or hardcoded assume we want 3 from A and 15 from B

=INDEX(C2:C10,MATCH(1,(A2:A10=3)*(B2:B10=15),0))

would return 4

To find two different values:

	A	B	C	D	E
1	Book Id	Location	Date In	Date Out
2	23	1	02/12/2001	04/22/2001
3	45	5	04/17/2001	04/19/2001	Location Mismatch
4	57	6	03/18/2000	03/19/2000
5	59	7	05/12/2001

	A	B	C	D
1	Book Id	Location	Date Purchased
2	23	1	02/12/2000
3	45	2	04/17/2001	Location Mismatch
4	56	5	04/20/2000	Missing ID 56
5	57	6	03/18/1999
6	59	7	05/12/2000

Formula in D2 of Sheet M.Set1:  (use fill-handle to fill down)
=IF(ISERROR(VLOOKUP(A2,M.Set2!$A$2:$C$200,2,FALSE)),"Missing ID " & A2,IF(VLOOKUP(A2,M.Set2!$A$2:$C$200,2,FALSE)<> B2,"Location Mismatch",""))

Formula in E2 of Sheet M.Set2:  (same as previous formula just for a different sheet)
=IF(ISERROR(VLOOKUP(A2,M.Set1!$A$2:$C$200,2,FALSE)),"Missing ID " &A2,IF(VLOOKUP(A2,M.Set1!$A$2:$C$200,2,FALSE)<>B2,"Location Mismatch",""))

Unless you modify your data structure, VLOOKUP will not work as No1 and 
Patrick have indicated. 

To maintain the current structure, use INDEX(MATCH()). 

For example, to use name in SheetA!B1 as the look_up value for the
inspection of data in range SheetB!B1:B10 and return the associated value
from SheetB!A1:A10, use in SheetA the formula
=INDEX(SheetB!A1:A10,MATCH(SheetA!B1,SheetB!B1:B10,0)) 

	A	B	C	D	E	F	G	H	I
1	Plant-Name	Prod. Name	Jan	Feb	March	April
2	Plant-A	Prod A	5	12	8	22		Given:
3	Plant-A	Prod B	6	15	15	18		Plant	Plant-A
4	Plant-A	Prod C	8	66	42	45		Prod.	Prod B
5	Plant-B	Prod A	2	45	12	16		Month	March
6	Plant-B	Prod B	4	22	6	45
7	Plant-B	Prod C	6	15	2	52		Find value	(see formula)

Bob Ulmas, solved this problem 2001-05-30 in worksheet.functions as follows:If “Plant Name” is in cell A2, then this formula will work
Array-enter:
    =INDEX(data,MATCH(A10&A11,A3:A8&B3:B8,0),MATCH(A12,2:2,0))
Where A10 contains variable plant (Plant A) and A11 contains variable
product (Prod B) and A12 contains the variable month (March)
array-enter is Ctrl/shift/enter

A B C D E 3 Hidden 4 INVOICE NO INVOICE NO MANIFEST NO SKU Sum of INVOICED QTY 5 5014 5014 10033 25180030100 35 6 5014 10034 25180030090 49 7 5014 10035 25100417355 50 8 5014 10036 25180011345 51 9 5014 10037 25100417341 40 10 5014 10038 25100417341 4 11 5014 25100417355 1 12 5014 25180011345 1 13 5015 5015 10038 25180011345 40 14 5015 10039 25180011346 41 15 5015 10040 25180011347 42 16 5015 10041 25180011348 43 17 5015 10042 25180011349 44 18 5015 10043 25180011350 45 19 5015 10044 25180011351 46 20 5015 10045 25180011352 47 21 5015 10046 25180011353 22 23 24

Spreadsheet Formulas Cell Formula A5 =IF(B5="",A4,B5) A6 =IF(D6="","",IF(B6="",A5,B6)) A23 =IF(D23="","",IF(B23="",A22,B23))

=SUM(SUM(OFFSET(A1,SMALL(IF(ISNUMBER(A2:A100),ROW(A2:A100)),{1,2,3})-1,)))



Recall, array formulas are entered by simultaneously pressing the Ctrl+Shift+Enter keys, not just Enter. The curly braces are not entered by you; Excel places them automatically when the array formula is properly applied.

Source: http://www.atlaspm.com/toms-tutorials-for-excel/toms-tutorials-for-excel-summing-the-first-nth-numbers-in-a-mixed-list/

Pivot table - Good example

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




Removing Duplicate Rows in one column using Macros: 

Sub DelDups_OneList()
Dim iListCount As Integer
Dim iCtr As Integer

' Turn off screen updating to speed up macro.
Application.ScreenUpdating = False

' Get count of records to search through.
iListCount = Sheets("Sheet1").Range("A1:A100").Rows.Count
Sheets("Sheet1").Range("A1").Select
' Loop until end of records.
Do Until ActiveCell = ""
   ' Loop through records.
   For iCtr = 1 To iListCount
      ' Don't compare against yourself.
      ' To specify a different column, change 1 to the column number.
      If ActiveCell.Row <> Sheets("Sheet1").Cells(iCtr, 1).Row Then
         ' Do comparison of next record.
         If ActiveCell.Value = Sheets("Sheet1").Cells(iCtr, 1).Value Then
            ' If match is true then delete row.
            Sheets("Sheet1").Cells(iCtr, 1).Delete xlShiftUp
               ' Increment counter to account for deleted row.
               iCtr = iCtr + 1
         End If
      End If
   Next iCtr
   ' Go to next record.
   ActiveCell.Offset(1, 0).Select
Loop
Application.ScreenUpdating = True
MsgBox "Done!"
End Sub
    

Removing Duplicate Rows in two columns using macros

Sample 2: Compare two lists and delete duplicate items

The following sample macro compares one (master) list against another list, and deletes duplicate items in the second list that are also in the master list. The first list is on Sheet1 in the range A1:A10. The second list is on Sheet2 in the range A1:A100. To use the macro, select either sheet, and then run the macro. 

Sub DelDups_TwoLists()

Dim iListCount As Integer

Dim iCtr As Integer

' Turn off screen updating to speed up macro.

Application.ScreenUpdating = False

' Get count of records to search through (list that will be deleted).

iListCount = Sheets("sheet2").Range("A1:A100").Rows.Count

' Loop through the "master" list.

For Each x In Sheets("Sheet1").Range("A1:A10")

   ' Loop through all records in the second list.

   For iCtr = 1 To iListCount

      ' Do comparison of next record.

      ' To specify a different column, change 1 to the column number.

      If x.Value = Sheets("Sheet2").Cells(iCtr, 1).Value Then

         ' If match is true then delete row.

         Sheets("Sheet2").Cells(iCtr, 1).Delete xlShiftUp

         ' Increment counter to account for deleted row.

         iCtr = iCtr + 1

      End If

   Next iCtr

Next

Application.ScreenUpdating = True

MsgBox "Done!"

End

good site:

Tom ultris site:

http://www.atlaspm.com/?s=row&x=0&y=0

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