Data

ID Name Customer Product Date

A106 Aradhana Reliant Micro 2 11/25/2014
A118 Bhuvan Everyday Store LED 32 12/5/2015
A111 Kriti Weekend Sale Micro 2 5/20/2014
A119 Mohan Eco Store Micro 2 10/18/2015
A113 Mona Reliant LED 32 1/23/2014
A103 Sakshi Everyday Store Ref 300 10/5/2015
A109 Shruti Weekend Sale Ref 300 4/4/2015
A116 Sree Eco Store Micro 2 5/15/2014
A117 Arnav Reliant LED 32 8/5/2014
A104 Sahil Everyday Store LED 32 1/14/2015
A112 Sameera Weekend Sale LED 32 2/21/2014
A107 Anjali Eco Store LED 40 6/6/2014
A114 Saurabh Reliant LED 40 6/26/2014
A120 Vidhi Everyday Store LED 40 7/12/2014
A101 Raj Weekend Sale Ref 300 2/12/2015
A105 Anita Eco Store Ref 500 3/20/2015
A110 Avinash Reliant Ref 500 4/15/2014
A108 Kavita Everyday Store Ref 500 6/7/2015
A102 Rajesh Weekend Sale Ref 500 2/14/2015
A115 Sankar Eco Store Ref 500 5/25/2015
Total Sale 1260

Area Sale
South 75
South 38
East 76
East 48
East 29
South 80
West 50
North 24
East 55
East 85
North 80
North 61
South 59
South 65
East 80
East 75
South 85
West 45
North 75
East 75
VLookup
ID Name Product Date Area
A105 Anita Ref 500 3/20/2015 East
A107 Anjali LED 40 6/6/2014 North
A106 Aradhana Micro 2 11/25/2014 South
A117 Arnav LED 32 8/5/2014 East
A110 Avinash Ref 500 4/15/2014 South
A118 Bhuvan Micro 2 12/5/2015 South
A108 Kavita Ref 500 6/7/2015 West
A111 Kriti Micro 2 5/20/2014 East
A119 Mohan Ref 300 10/18/2014 East
A113 Mona Micro 2 2/23/2014 West
A101 Raj Ref 300 2/12/2015 East
A102 Raju Ref 500 2/14/2015 North
A103 Rajesh Micro 2 10/5/2015 South
A104 Sahil LED 32 1/14/2015 East
A112 Sameera LED 32 2/21/2014 North
A115 Shikha Ref 500 5/25/2015 East
A114 Saurabh LED 40 6/26/2014 South
A109 Shruti Ref 300 4/4/2015 West
A116 Sree Micro 2 5/15/2014 North
A120 Vidhi LED 40 7/12/2014 North

Sale Max
Sameera Match

Sale
Mona
Search True (1) False (0)
Match Found Displays Val Displays Val
Match Multiple Last Val First Val
Not Found Previous Val #N/A
 Sale
75000
55000
65000
55000
85000
38000
45000
40000
90000
95000
80000
75000
25000
85000
80000
75000
59000
50000
56000
65000

95000 Sale
Vlookup Arguments
1. Lookup Value Search value
2. Table Array Range to search
3. Col Index Num Column that must be returned
4. Range Style Exact, or approximate match (Exact is a must)
act is a must)
POs Pending Shipment
Customer Pending Pending
CST-1001
CST-1002
CST-1003
CST-1004
CST-1005
CST-1006
CST-1007
CST-1008
CST-1009
CST-1010
CST-1011
CST-1012
CST-1013
CST-1014
CST-1015
CST-1016
CST-1017
CST-1018
CST-1019
CST-1020
POs Shipped
Customer Shipped
CST-1001 Partial
CST-1003 Partial
CST-1006 Full
CST-1008 Partial
CST-1012 Full
CST-1004 Partial
Functions
Data Application
sujata sood upper case
naveen verma proper case
MONICA ARORA lower case
ravi kumar combine
ravi kumar combine

123.45 integer only

AB-123456 remove spl char

AB-123456$789 remove spl char

A104Vishal left
A105Sameer right
A106Mona50000 mid

7/1/2014 day
7/1/2014 date
7/1/2014 month
7/1/2014 month
7/1/2014 year
9/10/2014 end of month
9/10/2014 week number

Text to Columns
Delimited

A101234-Raju
A102,Rakesh
A1032-Sangeeta
A10-Sameer Tandon
A1,Akash Deep

Fixed Width

A101Raju
A102Rakesh
A103Sangeeta
A104Sameer Tandon
A105Akash Deep
Usage
SUJATA SOOD
Naveen Verma
monica arora
ravi kumar
ravi kumar

123

AB123456
AB123456789

A104
Sameer
Mona

Tue
01-Jul-2014, Tue
7
Jul
2014
30-Sep-14
37
 Sort, Format, Filter

ID Name Product Date Area Sale

A105 Anita Ref 500 3/20/2015 East 75000
A117 Aradhana Micro 2 11/25/2014 East 65000
A117 Arnav LED 32 8/5/2014 East 55000
A110 Avinash Ref 500 4/15/2014 South 85000
A118 Bhuvan Micro 2 12/5/2015 South 38000
A108 Kavita Ref 500 6/7/2015 West 45000
A111 Kriti Micro 2 5/20/2014 East 76000
A119 Mohan Micro 2 10/18/2014 East 42000
A113 Mona Ref 300 2/23/2014 South 29000
A101 Raj Ref 300 2/12/2015 East 80000
A102 Rajesh Ref 500 2/14/2015 North 75000
A104 Sahil LED 32 1/14/2015 East 85000
A103 Sakshi Ref 300 10/5/2015 South 80000
A112 Sameer LED 32 2/21/2014 North 80000
A107 Sameera LED 40 6/6/2014 South 21000
A115 Sankar Ref 500 5/25/2015 East 75000
A114 Saurabh LED 40 6/26/2014 West 59000
A109 Shruti Ref 300 4/4/2015 West 50000
A116 Sree Micro 2 5/15/2014 North 24000
A120 Vidhi LED 40 7/12/2014 South 35000

Remote Controlled formatting

Name Product Date Area Sale

Anita LED 40 3/20/2015 East 75000
Aradhana Micro 2 11/25/2014 East 65000
Arnav LED 32 8/5/2014 East 55000
Avinash Ref 500 4/15/2014 South 85000
Bhuvan Micro 2 12/5/2015 South 38000
Kavita Ref 500 6/7/2015 West 45000
Kriti Micro 2 5/20/2014 East 76000
Mohan Micro 2 10/18/2014 East 42000
Mona Ref 300 2/23/2014 South 29000
Raj Ref 300 2/12/2015 East 80000
Rajesh Ref 500 2/14/2015 North 75000
Sahil LED 32 1/14/2015 East 85000
Sakshi Ref 300 10/5/2015 South 80000
Sameer LED 32 2/21/2014 North 80000
Sameera LED 40 6/6/2014 South 21000
Value > 75000 Top 3 Data Bars Scale Icons

Sale Sale Sale Sale Sale

75000 75000 75000 75000 75000
65000 62000 65000 65000 65000
55000 55000 55000 55000 55000
85000 45000 85000 85000 85000
38000 38000 38000 38000 38000
45000 45000 45000 45000 45000
76000 40000 40000 40000 40000
42000 42000 42000 42000 42000
29000 29000 29000 29000 29000
80000 80000 80000 80000 80000
75000 45000 75000 75000 75000
85000 85000 85000 85000 85000
80000 25000 25000 25000 25000
80000 55000 80000 80000 80000
21000 55000 55000 86000 86000
75000 75000 75000 75000 75000
59000 59000 59000 59000 59000
50000 50000 50000 50000 50000
24000 56000 56000 56000 56000
35000 25000 65000 65000 65000
Sub Total

Name Product Date Area Sale

Anita Ref 500 3/20/2015 East 75000
Arnav LED 32 8/5/2014 East 55000
Kriti Micro 2 5/20/2014 East 40000
Rajesh Micro 2 10/5/2015 East 25000
Sahil LED 32 1/14/2015 East 85000
Anjali LED 40 6/6/2014 North 55000
Avinash Ref 500 4/15/2014 North 85000
Mohan Ref 300 10/18/2014 North 90000
Raj Ref 300 2/12/2015 North 80000
Raju Ref 500 2/14/2015 North 75000
Anu Micro 2 10/5/2015 South 25000
Aradhana Micro 2 11/25/2014 South 65000
Bhuvan Micro 2 12/5/2015 South 38000
Mona Micro 2 2/23/2014 South 95000
Kavita Ref 500 6/7/2015 West 45000
Minu Ref 500 6/7/2015 West 45000
Rohan Micro 2 2/23/2014 West 95000
Vikas Micro 2 12/5/2015 West 38000
Pivot Source
Record Owner Groups

ID Name Customer Product Date

A101 Raj Weekend Sale Ref 300 2/12/2015
A102 Rajesh Weekend Sale Ref 500 2/14/2015
A103 Sakshi Everyday Store Ref 300 10/5/2015
A104 Sahil Everyday Store LED 40 1/14/2015
A105 Anita Eco Store Ref 500 3/20/2015
A106 Aradhana Reliant Micro 2 11/25/2014
A107 Anjali Eco Store LED 40 6/6/2014
A108 Kavita Everyday Store Ref 500 6/7/2015
A109 Shruti Weekend Sale Ref 300 4/4/2015
A110 Avinash Reliant Ref 500 4/15/2014
A111 Kriti Weekend Sale Micro 2 5/20/2014
A112 Sameera Weekend Sale LED 32 2/21/2014
A113 Mona Reliant LED 32 1/23/2014
A114 Saurabh Reliant LED 40 6/26/2014
A115 Sankar Eco Store Ref 300 5/25/2015
A116 Sree Eco Store Micro 2 5/15/2014
A117 Arnav Reliant LED 32 8/5/2014
A118 Bhuvan Everyday Store LED 32 12/5/2015
A119 Mohan Eco Store Micro 2 10/18/2015
A120 Vidhi Everyday Store LED 40 7/12/2014
A121 Vihan Weekend Sale Ref 300 2/12/2015
A122 Anukriti Weekend Sale Ref 500 2/14/2015
A123 Shalini Everyday Store Ref 300 10/5/2015
A124 Vishakha Everyday Store LED 40 1/14/2015
A125 Alok Eco Store Ref 500 3/20/2015
A126 Anupama Reliant Micro 2 11/25/2014
A127 Lakshita Eco Store LED 40 6/6/2014
A128 Vinod Everyday Store Ref 500 6/7/2015
A129 Bhaskar Weekend Sale Ref 300 4/4/2015
A130 Manu Reliant Ref 500 4/15/2014
A131 Shivani Weekend Sale Micro 2 5/20/2014
A132 Esha Weekend Sale LED 32 2/21/2014
A133 Vihbor Reliant LED 32 1/23/2014
A134 Aniket Reliant LED 40 6/26/2014
A135 Veronica Eco Store Ref 300 5/25/2015
A136 Bhim Eco Store Micro 2 5/15/2014
A137 Nakul Reliant LED 32 8/5/2014
A138 Arnav Everyday Store LED 32 12/5/2015
A139 Arvind Eco Store Micro 2 10/18/2015
A140 Bhanu Everyday Store Ref 300 7/12/2014
A141 Vijay Everyday Store LED 32 12/5/2015
A142 Anand Eco Store Micro 2 10/18/2015
A143 Monica Everyday Store LED 40 7/12/2014
A144 Shivam Weekend Sale LED 40 2/12/2015
A145 Anurag Weekend Sale Ref 500 2/14/2015
A146 Akhilesh Everyday Store Ref 300 10/5/2015
A147 Anantika Everyday Store LED 40 1/14/2015
A148 Akanksha Eco Store Ref 500 3/20/2015
A149 Cathy Reliant Micro 2 11/25/2014
A150 Andy Eco Store LED 40 6/6/2014
A151 James Everyday Store Ref 500 6/7/2015
A152 Steve Weekend Sale Ref 300 4/4/2015
A153 Raju Reliant Ref 500 4/15/2014
A154 Vineeta Weekend Sale Micro 2 5/20/2014
A155 Ashok Weekend Sale LED 32 2/21/2014
A156 Ashutosh Reliant LED 32 1/23/2014
A157 Veenu Reliant LED 40 6/26/2014
A158 Gitika Eco Store Ref 300 5/25/2015
A159 Komal Eco Store Micro 2 5/15/2014
A160 Richa Reliant LED 32 8/5/2014
Total Sale 3850

Value Pivot table does not refresh on data change

Area Sale
East 90
North 85
South 80
East 90
East 90
South 75
North 60
West 45
West 50
South 85
East 76
North 80
North 40
West 59
North 75
North 24
East 60
South 43
West 48
South 70
South 85
North 75
South 80
East 85
East 75
South 75
North 61
West 45
West 50
South 85
East 76
North 80
West 29
West 59
North 75
North 24
East 55
South 38
West 48
East 65
South 38
South 48
East 65
South 80
West 75
South 80
East 85
East 75
South 75
North 61
West 45
West 50
South 85
East 76
North 80
West 29
West 59
North 75
North 24
East 55
Charts
Region Sale-1 Sale-2
North 45 65
South 44 60
East 43 60
West 47 68

Month Sale-1 Sale-2

Jan 45 65
Feb 44 68
Mar 41 69
Apr 40 65
May 48 64
Jun 49 61
Jul 41 62
Aug 46 63
Sep 48 64
Oct 45 67
Nov 42 68
Dec 44 69