Newbie with pivot tables, rows are auto sorted?

[Webrookie]

Hi, I am working with pivot tables for the first time in excel 2000. I'm having an issue.

In my data spreadsheet, for example, I have a question column, answer column, and a count representing number of people who chose that answer.

So for example:

Question Answer Count
1. How long have had DSL? 0-3 months 12
1. How long have had DSL? 4-6 months 13
1. How long have had DSL? 6-12 months 14
1. How long have had DSL? 1-3 years 15
1. How long have had DSL? 3-6 years 16

So that's basically my spreadsheet if you can picture it.

when I set up a pivot table, I put Question in the 'page' thingy (excuse the highly accurate technical jargon ). I put Answer in the 'row' section, and count in the 'data' section.

when I click on the question, it shows the answers and the counts associated with them, but they're out of order. I want them to appear in the exact order they are in the spreadsheet. which is chronologically by timeframe. Excel, however, has different ideas. It put the answers in the following order:

0-3 months
1+ years
10-12 months
4-6 months
7-9 months

which for presentation purposes is like, "huh?"

Now I know I can put "1." or "A." in front of the answers to force them in order, but that just looks ugly to me. Is there an option somewhere in excel that I'm just missing?

help me?

 

[DaleWatson123321]

Webrookie,

I suspect you're probably a typical Excel user - i.e. one who has responded to the the "hype" over Pivot Tables.

As a result of this "focus" on Pivot Tables, and Microsoft's "less than acceptable" attention paid to Excel's "database functions", many Excel users have NOT (yet) discovered the EXTREME power and usefulness of these database functions.

The =DCOUNTA function, for example, seems to be ideal for your situation.

Another reason for Excel users to "become discouraged" is because Microsoft has refused to eliminate a couple of BUGS. One can surface when attempting to place the "criteria" for a database formula on a SEPARATE sheet. In fact, it's "preferable" to place the criteria on a SEPARATE sheet. Keeping the criteria on the SAME sheet will cause Excel to "get confused" between the criteria and your field names for the database (array). This probably results from Microsoft having programmed Excel to "anticipate" what the user intends to do.

If you'd like help with the DCOUNTA function, specific to your spreadsheet, the best route would be for you to email me your file. I'll then modify and return it. If you happen to have sensitive data, replace it with fictitious data that still reflects the type of data you're working with. I could also email example files.

I hope this helps.

Regards, ...Dale Watson

HOME: nd.watson@shaw.ca
WORK: dwatson@bsi.gov.mb.ca

 

[acron]

Just for the record you can drag the contents of you PT into any order you like. To see if this option is enable in the Field options selcted advanced and select it.

A.C.

 

[xlbo]

To back up Acron - there should be no reason why you can't drag and drop anywhere you like - just click on the right or left side of the column / row you want to move, hold down the mouse button and drag n drop it. Also, whatever sort you apply will stay applied until you change it - thru refreshes n everything

n Dale - I know you love your database functions but I think in instances like this, a pivot table would be the quickest and easiest solution - that's my only reservation about database functions and advanced filter - they do take a little time to set up
Rgds
~Geoff~

 

[DaleWatson123321]

~Geoff~

I readily agree with your comment... "in instances like this, a pivot table would be the quickest and easiest solution".

However, I still firmly believe that Excel users have been "short changed" - by Microsoft having, to a large extent, IGNORED the database functions and Advanced Filter options, in favor of emphasizing the use of Pivot Tables.

Microsoft has failed to recognize that, while Pivot Tables are often "the quickest and easiest solution", there ARE "limitations".

While database functions can require a little practice and time to set up (made MORE difficult primarily because of Microsoft's "less than acceptable" help and examples), this "extra effort" will often "pay big dividends" - because the end result will be a realization that using the database functions can enable MUCH MORE FLEXIBILITY and ANALYSIS CAPABILITY than is afforded by Pivot Tables.

The "bottom line" for ALL Excel users is that they are "missing out" on an EXTREMELY POWERFUL component of Excel if they limit themselves to Pivot Tables and don't invest the time to explore and utilize Excel's database functions.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[xlbo]

Absolutely Dale - didn't want to suggest that db functions are not useful and I totally agree about the lack of decent documentation
but sometimes I can't resist just wopping a pivot table over a big set of data to get the quick n dirty stuff done Rgds
~Geoff~

 

[Webrookie]

Geoff, you are correct in assuming I am a typical Excel user. While I am a programmer, I have never done anything too advanced with Excel.

This is actually for a project I'm working on for a client. My boss went to one programmer with a question about reporting for the project, and then came to me and my team mate about putting it together. Unfortunately, neither of uf has ever done anything with pivot tables before.

I'd like to be able to deliver this Excel document to the client without them having to make any changes to it. They should just be able to select a question, and the answers should show up along with how many people chose each type of answer for the question. So I'm looking for presentability. I'm also looking for this to be highly automated, one time ole programming, and then little manual interference in the future.

Thank you for your replies, I will keep checking back here.

 

[xlbo]

Sounds like you're gonna have different sets of data feeding the pivot table - in which case, you will need either a dynamic range name or some code to update the area referenced by the pivot table (same goes for database functions)
While pivot tables are a good tool (esp if you want some drill capability), I would suggest that they are not too great presentationally
Personally, I think you have 3 options:
Database functions
Array Formulae
Pivot Table

Which you decide on will be down to how you want the report to look - each has advantages and disadvantages. Can you give us more of an idea of the functionality needed for this reporting and the preferred layout / size of data area / number of questions ?? Rgds
~Geoff~

 

[Webrookie]

I will have code updating a data set. That's not a problem. My main problem is that the rows are auto sorted. Excel tries to do too much.

Well I have three surveys which have common core of questions, but there are also other questions that make them different. Client would like to see how many people picked what for each question for each survey.

Number of questions are 23,27, and 31. Two questions on each have 26 answers each. My first go at this was just entering a couple questions and their answers and making a pivot table and pivot chart. That's when I noticed the answers being sorted.

Also, I added Question/Answer pairings for two of the surveys on the same sheet. I added a column indicating survey type, and added this column to the 'page' area on the pivot table. I assumed this meant, I'd click the survey type, see a list of questions particular to that survey, etc.. Well, when I clicked the Survey type, it had questions from both surveys listed. So now I'm down to having question/answer pairings in separate data sheets, and 3 different pivot tables.

My flexibility on this project is not great. Client expects Excel reports, my boss told her we'd do Pivot tables, and now here I am.

 

[xlbo]

Sounds like fun :-(
As acron and myself have mentioned - you can drag and drop to create a custom order and the pivot table should remember that order but in instances of different data sets, it may well forget Rgds
~Geoff~

 

[pkhoo]

webrookie,
if you change the sort to manual, the order of the rows will not change during change of the page thingy.
regards
pkhoo

 

[Webrookie]

It's already set up as manual.

It's taking the rows from the spreadsheet and sorting them on the new sheet containing the pivot table.

 

[xlbo]

Yes but you can change the sort order manually - once changed, it'll stay that way Rgds
~Geoff~