Building Pivot Tables… The Basics

Excel function tutorials

Over the past few weeks, I’ve been getting an increased number of questions regarding the use of pivot tables. Believe me, I understand. As much as I know how useful and powerful pivot tables can be, I resisted using them for years. I never felt comfortable using them and always looked for alternative methods. Thankfully, I’m getting much better at it. Today, I wanted to look over a few basics because compared to other types of excel functions, using pivot tables is not as easy to grasp initially. What is a pivot table and when should you use it?

Pivot tables are a way to express lengthy tables of data in a simple way. It helps to draw conclusions about the data, etc. In almost all cases, there are alternative ways but pivot tables are very easy to use once you’re used to it.

What do you need? Simple.

-A table with multiple columns that have headers at the top (it simply will not work without headers).

Ideally, you start by finding the place where you’d like to place the pivot table. It can be on a separate sheet (which works very well actually) or anywhere on an existing one.

Here is an example of a table with different stocks and relevant info about each one:

So then I went to a different sheet and clicked:

Insert/PivotTable

Then, I was asked to select a table:

As you can see, I am now able to see a basic table and must now go to work. Then I would select on the right the columns that I’m looking to add. You would drag the categories to the appropriate location at the bottom. For example, if I’d like to know how many stocks of each category I have, I would do the following:

And have this result:

I could also get the market cap per category by adding it to the values section.

As you can see, the result is very interesting:

I hope this helps a bit. I do encourage you to get a table of data and start playing with it, it’s the best way to get yourself familiar with the power of pivot tables. You can also download my spreadsheet here

***************************************************

Look Good at Work and Become Indispensable Become an Excel Pro and Impress Your Boss


***************************************************

4 Feedbacks on "Building Pivot Tables… The Basics"

Pivot Table Excel 2010

Pivot tables are really powerful tool. I think people should use them more often.



JOSEPH

Hello There,

I would like to have Part 2 and 3 of the eBook using vlookup with Excel 2007. Please advice

Joe



Joseph

Hey I tried simple vlookup Part 1 in excel 2007 and it was pretty good. Let me try your training on complicated vlookup such as Part 2 and 3.

I also tried Pivot Table Part one, I liked it. Let me try Part 2 and 3.

Thank you Joe



mijooschool.com

You ought to take part in a contest for one of
the finest sites on the web. I’m going to highly recommend
this web site!