Redwood Associates Proprietary

How pivots make data meaningful

Module 1: Report generation the easier way

Redwood Associates Proprietary

Module overviewIn this module, we will cover:

What is a pivot table

What are the features of a pivot table

How to use a pivot table

Additional material:

1. Video tutorial Introduction to pivots

2. Video tutorial Let pivots do the work

3. Video tutorial Creating reports without getting intotechnicalities

4. Video tutorial Slicing reports with pivots

5. Video tutorial Working with dates in pivots

Redwood Associates Proprietary

What is a Pivot TableTo understand what is a Pivot Table, let us first look at the

meaning of a pivot.

A pivot is a central point, pin or shaft on which a mechanism

turns or oscillates.

So when looked at together with the meaning of pivot, we can

deduce that a Pivot Table is a basic outline used to handle huge

data, with tools to then manipulate or view data differently.

Redwood Associates Proprietary

What is a Pivot TableA Pivot Table can be used to convert an idea of how ones data

should be presented to reality.

We will now look at the different options under Pivot Table.

Redwood Associates Proprietary

What are the options under Pivot TableCreate PivotTable

Choose the data: Specify whether the data is within Excel or in an external data source

Choose the location of the PivotTable: Specify whether the PivotTable should be placed in a new sheet or choose a specific location where it needs to be placed

Redwood Associates Proprietary

What are the options under PivotTablePivotTable Field List

Field List: Lists column headings[variable names] of the data. These can be clicked or dragged to the required area/s

Column Labels:The field which we drag and drop here will be displayed in the output table as column headings.Note: Only unique values will be displayed here.

Row Labels:The field which we drag and drop here will be displayed in the output table as row headings.Note: Only unique values will be displayed here.

Values:Any type of field/s which require a calculation can be dropped here.

Redwood Associates Proprietary

How to prepare dataHere are a few pointers to make sure that data is made ready so

that a Pivot Table can be prepared out of it.

1. Make sure that there are no cells hidden in the data.

2. Ensure that there are no rows or columns that are blank.

(This will be useful when auto selecting data)

To remove any blank values or cells/rows/columns use the Go to

Special option on the Home tab.

3. Use Remove duplicates option in the Data tab to remove any

duplicate records in the data.

Redwood Associates Proprietary

The Next TopicMost of your learning on how to use Pivot Tables are in the video

tutorials which are listed here. Make sure that you go through

these videos and also have the sample data set ready to solve any

examples.

So, heres what you will be looking at next:

1. Introduction to pivots - Video tutorial

2. Let pivots do the work Video tutorial

3. Creating reports without getting into technicalities Videotutorial

4. Slicing reports with pivots Video tutorial

5. Working with dates in pivots - Video tutorial

Redwood Associates Proprietary

About your Excel trainerKamal Vaswani

Kamal has clocked around 3500 hours in training, predominantly

in Excel. He has trained approximately 1500 students at the

training centre of ATI (Analytics Training Institute) Bangalore. In

addition he has been the face of Excel training in around 30

corporate organizations across India.

Kamal is featured in all video tutorials of this course. These video

tutorials are live recordings of a classroom session conducted at

ATI.

Kamal is a graduate from Delhi University. To find out more

about him go to

http://in.linkedin.com/pub/kamal-vaswani/37/b20/838

http://in.linkedin.com/pub/kamal-vaswani/37/b20/838http://in.linkedin.com/pub/kamal-vaswani/37/b20/838http://in.linkedin.com/pub/kamal-vaswani/37/b20/838http://in.linkedin.com/pub/kamal-vaswani/37/b20/838http://in.linkedin.com/pub/kamal-vaswani/37/b20/838http://in.linkedin.com/pub/kamal-vaswani/37/b20/838http://in.linkedin.com/pub/kamal-vaswani/37/b20/838http://in.linkedin.com/pub/kamal-vaswani/37/b20/838http://in.linkedin.com/pub/kamal-vaswani/37/b20/838http://in.linkedin.com/pub/kamal-vaswani/37/b20/838http://in.linkedin.com/pub/kamal-vaswani/37/b20/838http://in.linkedin.com/pub/kamal-vaswani/37/b20/838http://in.linkedin.com/pub/kamal-vaswani/37/b20/838http://in.linkedin.com/pub/kamal-vaswani/37/b20/838

Redwood Associates Proprietary

End of Module 1