Grrrr! ARgh!!!!! Excel Pivot Tables!!!!!

Is there a guru in the house, or does someone know of a good resource for learning how to work with these things??? That is all...........

This one is a pretty good basic tutorial: routes.tyretotravel.com/

And of course read through the Microsoft one: http://office.microsoft.com/en-us/excel-help/pivottable-reports-101-HA001034632.aspx

Start your own practice on a very basic tab and table. Nothing complex. It's very visually functional. Once you have the light bulb go off, you're good to go, until you forget what you did in a year or so.

http://www.techonthenet.com/

thank me later.

Thanks folks!!! Will look at these later tonight.

Honestly, this place is better than teh googles!!!!

What are you trying to do?

Dr. Hess wrote: What are you trying to do?

This... I wouldn't say I'm a guru by any stretch but I use them daily. I may be able to help depending on the problem

I have a master spreadsheet that has a bunch of columns of data in it (har!). The important ones now are users, activities, activity time and quote time. Quote time gets populated if the activity listed is a quote and is separate from the activity time.

I want to create a table where I can show by user number of quotes created and average time to create quote. Additionally, per user, there are other activities where I want to show either a total count (number of times the activity was performed) --or-- total time spent on the activity.

Wow you've gone waaaay past the "should be using a database" point

Right click the cell where you want the pivot table.

click Insert Pivot Table.

Select the columns you want in the table.

Now it becomes very visual. This can trip you up!

Column labels, DRAG the "user" to it.

Sum values, drag count of activities to this.

Leave activity and quote time in row labels for the moment.

Look at the table that is created, and see what it's done. Once you understand it, you'll see how to change it.

Now, click on Count of Activities, which is a sum value. Click Edit, now you can change it to average.

Do the same thing to the other fields to see how to edit them.

I don't use it, but there is a way to have the data fields update constantly with a data change in the reference cells. Otherwise you need to "refresh data" to have it pick up changes.

GameboyRMH wrote: Wow you've gone waaaay past the "should be using a database" point

Yeah -- I thought so too -- and me being somewhat database illiterate I ask the following:

• Is there a way to populate a database using data pulled in from multiple Excel spreadsheets and then use the database to operate on that data? Going with free as in beer here -- will OpenOffice Base or another freely available db work with somethings like this?

Yes, I use pivot tables exactly that way. Don't know about open office.

If you google "mde toxamatic" you'll get a spreadsheet with a pivot table on tab 2. Play with it and see if you understand what it's doing.

Caviat, it's created in an older version of Excel, 97 as I recall.