tips and tricks

Adaptive Processes Consulting Pvt. Ltd. An ISO 9001:2000 Certified Company

www.AdaptiveProcesses.com

This document is the property of and proprietary to Adaptive Processes Consulting Pvt. Ltd. Contents of this document should not be disclosed to any

unauthorized person. This document may not, in whole or in part, be reduced, reproduced, stored in a retrieval system, translated, or transmitted in

any form or by any means, electronic or mechanical.

Essential Excel Tips and Tricks

http://www.adaptiveprocesses.com/

Adaptive Processes Simpler, Faster, Better

Excel Tips and Tricks

Retrieving values from tables with a common column

vLookup

Suppose you have two excel sheets with some common

column and some different columns. You can get the value

for different column using vLookup.



For example:

a. In the Test.Xls, in

WS1 we have Project

Number and Ratings

for different

Questions. In WS2,

we have Project

Number and Name.

So in order to get

Project Name in WS1,

you follow the

following steps



b. In WS1, Insert

Columns in the

beginning and copy

and paste the common

column (preferably left

most column) and

different column from

WS2 and sort in

ascending order for

common column



c. In WS1, Use the Formula

V Lookup in Cell O2 Use

as follows

Note: Lookup Value is the common cell reference in

WS1 (value to be searched) ,

Table Array is the Values

you copied from WS2 and

Index Column Number is

the number of the column

from which Value needs to

be returned and always put

Range Lookup as False if

you want an exact match.



d. You will see the

value returned

now and drag it

down for other

cell values.



2. Making formula cell

values permanent

e. Now in WS1, if you

remove Columns

A&B (as they are

needed any more),

you will see that

Values in Name

Column has become

#REF as the formula

is invalid.



f. To avoid this

problem, you can

make the values

permanent basically

remove the formula

but retain the value by

doing Paste Special

and Select Only

Values after copying

the column.



3. Quick Reporting Using Pivot Table

a. Now suppose you want to find out average project rating for Projects, you can do the

same by Pivot Table

Select Data Go to Data Pivot Chart Report Select Next Select Next Choose Layout



b. Now Pull Name to Row

and Q10, Q11, Q12 to

Data Area



c. Now the Data area says

Sum where as you need

average, so you Click on

Sum of Q10 and

Change it to Average



4. Converting Row Data

in a Pivot Table to

Column Data

a. Now if you see the

Pivot Table Report, you

will see that Data is

coming in Rows where

as you need the data in

columns.



b. Select Data and Go to

Format Report and

Select a Report that

looks like Matrix (Table

1)



c. Now the report will

look like



5. Finding Unique Values in a set of values Advanced Filter

a. Now in WS1, you have some project names but some of them repeat. You are interested to know how many unique names / values are there. This you can do by using Advanced Filter.

Select the Data Column Go to Data Filter Advanced Filter



b. Choose Copy to Other

Location, Copy to a new

place (choose a blank

column), and Check

Unique Records Only



c. Now the results will

have unique records in

Column N



6. Converting Data in

Rows to Data in

Columns Transpose

a. Now suppose you want

to put values in Column

N into other columns

rather than Rows, do

the following

Copy Data Go to a

blank cell Go to Edit

Paste Special

Check Transpose, you

will see row values in

columns



7. Keeping certain header row constant when you have more than one page data - Freeze Pane

a. Now you cant see Project 7 and Project 8 column names clearly, and you would like to see the left most column also, then you can do Freeze Pane.



Select cell B2, Go to Window Freeze Pane. Now you can scroll the Scroll Bar and see columns which were not visible earlier and still see the left most column.



8. Row / Column groupings

a. Now there are many Rows for Project 6 and you would like to Group them so that they look like one Record but without hiding or deleting the rows. Select the Rows except the first row in the Project 6 rows

Go to Data Group and Outline Group.



b. Now you can click the minus sign on Left Most side of the Worksheet, and the result will be .

c. Similarly you can group Columns also.



9. Writing multiple row data in a single cell

To write multiple rows data in a single cell, Press Alt-Enter after finishing first row data in the cell

10. Text Wrapping in a cell

You may have text which does not fit into the width provided in the Column, so select Cells, Go to Format Cells Alignment Wrap Text



11. Restricting cell values - Data Validation List of Values

Now you would like to put your Project Managers to put locations for Projects in WS2 but would like to restrict the values to Locations like Delhi, Bangalore and Pune. This is called Data Validation and you can do that by following steps:



a. Choose a cell location till which people are unlikely fill data, for the example purpose, I am doing this in Cell C15, Put the Values to be Chosen in Rows.

Go to Cell C2, Go to Data Validation Select Allow Value to be List In Source Choose List of Values



b. Now you see the Drop-down List in Cell C2



12. Modifying header and footer of multiple sheets at same time

a. Select all the Sheets where you would like to have the same header and footer, Go to View Header and Footer Pick Custom Header / Footer You can pick date, time, File Name, Tab Name from the icons and do necessary formatting.



13. Converting Text in a cell to Columns

a. Sometimes when you extract data from a tool, it may provide you data that will a Comma Separated Value (.csv) file or you copy a report output on Excel, you may have to put data into appropriate columns by converting Text to Columns like Cell A2 in WS4



b. Go to Data Text to Columns Choose Delimited (if Separated by any special character or Fixed Width as appropriate) Select Delimiting Character (here it is Comma) and see the result



14. Changing one of the histogram bars to line type

a. You have drawn a bar graph based on data in WS3, it looks like below

52

79

88

28

62

71

54

7881

0

10

20

30

40

50

60

70

80

90

100

Total Employees Participated in Survey % Covered

Total Employees 52 79 88

Participated in Survey 28 62 71

% Covered 54 78 81

Year 2002 Year 2003 Year 2004



b. But you would like to put % Covered as a Line graph rather than bar graph, Right Click on the Data Series (Yellow bar), Choose Chart Type as Line Type and then see how the graph looks now



52

79

88

28

62

71

54

7881

0

10

20

30

40

50

60

70

80

90

100

Total Employees Participated in Survey % Covered

Total Employees 52 79 88

Participated in Survey 28 62 71

% Covered 54 78 81

Year 2002 Year 2003 Year 2004



15. Graph on double axis

a. Now suppose you would like to draw a graph with data in WS5, the graph will look like this

0

50

100

150

200

250

300

350

400

Year 2000 Year 2001 Year 2002 Year 2003

India's GDP

Growth Rate



b. Since growth rate is very small value compared to GDP, it will not be visible. To avoid this problem, you could draw the graph on Double axis so that Range on both sides will be different

Select Data Go to Charting Icon Select Custom Type Choose Line Column on 2 Axis and this is how the graph will look like

270

280

290

300

310

320

330

340

350

360

Year 2000 Year 2001 Year 2002 Year 2003

0

1

2

3

4

5

6

7

India's GDP

Growth Rate



16. Changing color of a cell given a condition - Conditional formatting

Now in WS5 sheet, you would like to color cells with Growth Rate greater than 5 as Green and those below 5 as Red color, you can do the same by doing Conditional Formatting.

tips and tricks

Documents

better excel tips

column data

adaptive processes consulting

essential excel tips

tricks http

common column vlookup

ws1 value

index column number