#### Help Requested with Pivot Table

```Hi,

I've created a pivot table. It looks like this:

Item1   Sum of Total         9
Count of Names   2
Average of Total  4.5
Item2   Sum of Total        12
Count of Names    3
Average of Total   4
Item3   Sum of Total         3
Count of Names    1
Average of Total   3
.......

You get the idea.
1) I find this to be a very awkward way to present the data. It would
make much more sense to present it as follows:

Name   Sum of Count of  Average of
Total  Names     Total
Item1   9       2         4.5
Item2  12       3         4
Item3   3       1         3

So how can I get the data in the pivot to be presented in a way, which
to me, is far more readable?

2) How do I get a Pivot to actually sort based on the data values. For
example, I want to sort the observations so that the observation
(Item3) with the smallest "Sum of Total" actually appears first in the
list.

Thanks for any help that you can provide.

Best Regards, Jim
http://www.marsartgallery.com/
```
 0
2/6/2007 10:50:18 PM
excel 39879 articles. 2 followers.

3 Replies
799 Views

Similar Articles

[PageSpeed] 3

```#1.  You can drag that grey looking Data button to the right.
Debra Dalgleish shows how:
http://contextures.com/xlPivot02.html#Multiple
and a video!
http://contextures.com/xlVideo001.html

#2.  Right click on the Item "Button" header.
Choose Field settings
choose the Autosort option you want
(Ascending and using field "sum of total")

Jim wrote:
>
> Hi,
>
> I've created a pivot table. It looks like this:
>
> Item1   Sum of Total         9
>             Count of Names   2
>             Average of Total  4.5
> Item2   Sum of Total        12
>             Count of Names    3
>             Average of Total   4
> Item3   Sum of Total         3
>             Count of Names    1
>             Average of Total   3
> ......
>
> You get the idea.
> 1) I find this to be a very awkward way to present the data. It would
> make much more sense to present it as follows:
>
> Name   Sum of Count of  Average of
>        Total  Names     Total
> Item1   9       2         4.5
> Item2  12       3         4
> Item3   3       1         3
>
> So how can I get the data in the pivot to be presented in a way, which
> to me, is far more readable?
>
> 2) How do I get a Pivot to actually sort based on the data values. For
> example, I want to sort the observations so that the observation
> (Item3) with the smallest "Sum of Total" actually appears first in the
> list.
>
> Thanks for any help that you can provide.
>
> Best Regards, Jim
> http://www.marsartgallery.com/

--

Dave Peterson
```
 0
petersod (12005)
2/6/2007 11:15:49 PM
```Hello Dave,

On Tue, 06 Feb 2007 17:15:49 -0600, Dave Peterson
<petersod@verizonXSPAM.net> wrote:

>#1.  You can drag that grey looking Data button to the right.
>Debra Dalgleish shows how:
>http://contextures.com/xlPivot02.html#Multiple
>and a video!
>http://contextures.com/xlVideo001.html

Excellent. Worked like a charm.

If only I could have found the answer in Excel's Help.

>
>#2.  Right click on the Item "Button" header.
>Choose Field settings
>choose the Autosort option you want
>(Ascending and using field "sum of total")

Ah, that button. I had tried clicking on the various fields and the
only thing sorting did was to reorder the fields associated with the
items without reordering the items themselves.  Following your
instructions, I was able to resort the data the way I wanted to.

>Jim wrote:
>>
>> Hi,
>>
>> I've created a pivot table. It looks like this:
>>
>> Item1   Sum of Total         9
>>             Count of Names   2
>>             Average of Total  4.5
>> Item2   Sum of Total        12
>>             Count of Names    3
>>             Average of Total   4
>> Item3   Sum of Total         3
>>             Count of Names    1
>>             Average of Total   3
>> ......
>>
>> You get the idea.
>> 1) I find this to be a very awkward way to present the data. It would
>> make much more sense to present it as follows:
>>
>> Name   Sum of Count of  Average of
>>        Total  Names     Total
>> Item1   9       2         4.5
>> Item2  12       3         4
>> Item3   3       1         3
>>
>> So how can I get the data in the pivot to be presented in a way, which
>> to me, is far more readable?
>>
>> 2) How do I get a Pivot to actually sort based on the data values. For
>> example, I want to sort the observations so that the observation
>> (Item3) with the smallest "Sum of Total" actually appears first in the
>> list.
>>
>> Thanks for any help that you can provide.
>>
>> Best Regards, Jim
>> http://www.marsartgallery.com/

Best Regards, Jim
http://www.marsartgallery.com/
```
 0
2/7/2007 12:18:19 AM
```Glad it worked for you.

Jim wrote:
>
> Hello Dave,
>
> On Tue, 06 Feb 2007 17:15:49 -0600, Dave Peterson
> <petersod@verizonXSPAM.net> wrote:
>
> >#1.  You can drag that grey looking Data button to the right.
> >Debra Dalgleish shows how:
> >http://contextures.com/xlPivot02.html#Multiple
> >and a video!
> >http://contextures.com/xlVideo001.html
>
> Excellent. Worked like a charm.
>
> If only I could have found the answer in Excel's Help.
>
> >
> >#2.  Right click on the Item "Button" header.
> >Choose Field settings
> >choose the Autosort option you want
> >(Ascending and using field "sum of total")
>
> Ah, that button. I had tried clicking on the various fields and the
> only thing sorting did was to reorder the fields associated with the
> items without reordering the items themselves.  Following your
> instructions, I was able to resort the data the way I wanted to.
>
> Your advice is gold. Many thanks.
>
> >Jim wrote:
> >>
> >> Hi,
> >>
> >> I've created a pivot table. It looks like this:
> >>
> >> Item1   Sum of Total         9
> >>             Count of Names   2
> >>             Average of Total  4.5
> >> Item2   Sum of Total        12
> >>             Count of Names    3
> >>             Average of Total   4
> >> Item3   Sum of Total         3
> >>             Count of Names    1
> >>             Average of Total   3
> >> ......
> >>
> >> You get the idea.
> >> 1) I find this to be a very awkward way to present the data. It would
> >> make much more sense to present it as follows:
> >>
> >> Name   Sum of Count of  Average of
> >>        Total  Names     Total
> >> Item1   9       2         4.5
> >> Item2  12       3         4
> >> Item3   3       1         3
> >>
> >> So how can I get the data in the pivot to be presented in a way, which
> >> to me, is far more readable?
> >>
> >> 2) How do I get a Pivot to actually sort based on the data values. For
> >> example, I want to sort the observations so that the observation
> >> (Item3) with the smallest "Sum of Total" actually appears first in the
> >> list.
> >>
> >> Thanks for any help that you can provide.
> >>
> >> Best Regards, Jim
> >> http://www.marsartgallery.com/
>
> Best Regards, Jim
> http://www.marsartgallery.com/

--

Dave Peterson
```
 0
petersod (12005)
2/7/2007 1:15:11 AM

Similar Artilces:

Button/Macro Not Working, Help!
I have a macro that pulls up Report Manager and prints out pre-define views. The macro works fine only after I use Report Manager the firs time. If I exit my workbook and Excel, then return to it, th button/macro does not function. Nothing happens until I manully us Report Manager again. Is there a bug with this or am I missin something -- Message posted from http://www.ExcelForum.com Post your code. Gord Dibben Excel MVP On Thu, 20 May 2004 08:38:55 -0500, baston <<baston.16k4at@excelforum-nospam.com>> wrote: >I have a macro that pulls up Report Manager and prints out pre...

query with inline dummy table
Is there a way in Access to create a query that contains the table records within the query itself? I want to avoid creating a dummy table and just use values within the query definition. I was thinking about using syntax similar to the insert into statement I would use to populate the dummy table, but I'm not sure if I have a syntax problem or I'm trying to solve an impossible problem. The query I'm thinking of might look something like this: select * from values("test"); -or- select * from ( ("1/1/07","2/1/07","3/1/07","4/1/07&qu...

Create form similar to MS Access 2000 Help
Is it possible to create a form that works similar to the Help - Index form in Access 2000? My StreetTable consists of the following fields: Street_ID(autonumber), StreetName(text), Directions(memo). I want to be able to type the beginning letters of the street and the List of StreetNames continually reduces until I see the street I need in the StreetNames window. I would then click on that street and the Directions would appear in the third window panel. Since I'm dealing with approx. 5000 streets, scrolling really isn't an option that I want to consider. I found that a combo ...

workaround for non normalized table
I've inherited a database that relies heavily on a non normalized table. Until I can convince the general manager that I can normalize the data without losing any records, I've got to have a workaround for certain situations. Namely, I need to ensure that data is not being badly reproduced at various stages of our operations. I would therefore like to reference the information directly from the main table in order to populate certain information in related tables. In this case, the main table uses [Order Number] as its primary key, and ties it to a bunch of information like ...

HELP: Excel userform version control issue
Hi All, I have an excel userform that is posted on my companys intranet. The issue I am having is, users are saving this form to their hard drive and then using it(Instead of launching it everytime from intranet), SO basically when I have a new version of form on intranet they still use the old version as they have a habbit of saving it on their hard drive. Is there a way to resove this? something like they would see a popup message saying that "This is an older version that you are using, Please launch the form from Intranet for newer version". Thanks in advan...

Outlook attachment problems
Dear Our oversea office sender using Outlook Sending message (HTML) insert a picture sending message to my office, when I have received the attachment auto duplicate one set in the message. I have asked the sender try remove the picture and FORWARD to my e-mail, but the attachment sill in the message and auto duplicate one set attachment. Out overseas office using MS SBS , user setup WG mode for outlook download message. I am not sure which part problems or virus effect. Please advice how to solve out the problems ASAP. JACKIE ...

Error in code exportin tables to excel
I'm tryin to export a table to excel with the code: Private Sub Command4_Click() On Error GoTo Err_Command0_Click Dim stDocName As String stDocName = "F01_UT" DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, F01_UT, "C:\TRY\Tbls", True Exit_Command0_Click: Exit Sub Err_Command0_Click: MsgBox Err.Description Resume Exit_Command0_Click End Sub However, Access displays the message: Compile error : Syntax error What I'm doing wrong? If I want to export more tablas do I have to write more lines (DoCmd.TransferSpreadsheet acExport, acSpr...

Field service table structure and field definitions
The 9 SDK does not reference or define the fields or tables for the Field service component. Where can I find this information? I need to move RMA into a data warehouse and am having significant trouble identifying the data flows. Thanks, Will You may want to contact I.B.I.S. They wrote the application. -- Charles Allen, MVP "WS" wrote: > The 9 SDK does not reference or define the fields or tables for the Field > service component. Where can I find this information? I need to move RMA > into a data warehouse and am having significant trouble identifying th...

Help with
I am currently in the process of migrating exchange 2000 to exchange 2003 on new hardware. I have transfered the RUS role, offline address book role and replicated the public folders ect. and now am beginning to move maiboxes. I noticed 3 very repetive errors in the application event log however. I was wondering if someone could tell me whether these messages are somthing I should be concerned with. See them below. So far I have no user complaints about outlook. Thanks. Event ID: 9320 Source MSExchangeSA Type Warning Description OALGen could not generate full details for some en...

Help with Inbox repair tool please
I have a problem with my outlook 2000 and have been advised to use the inbox repair tool. I have opened the inbox repair tool, and I am asked to enter the folder name or to browse and enter the location of the folder, I need to enter the delete items folder but do not know where to find it. Any help appreciated. John The Inbox Repair Tool works against Personal Folders Files (.PST files), not against individual folders within the .PST file. If you don't know where your .PST file is located, you can find it this way: In Outlook, make sure the Folder List is visible (View | Folder...

VBA code to hide all the tables on form open
I don't want people to use a blank mdb to import my tables. I manually hide them all. However, after running the macro to delete all records and import from .txt, the table become unhide. I do the importation on daily basis. I posted to macro newsgroup and asked way to hide table after importation action macro but got no answer. Maybe it cannot be done in macro? If so, I need VBA code to hide all the tables on form open. Thanks. Hiding your tables won't prevent people from being able to import them into a blank mdb. All they have to do is ensure that they've set the datab...

Help; how do i change the waiting circle (in IE tab) to anything e
I am making a website and i have a lot of wait time for my server-side pages to do their work. The waiting circle icon gives me a headache so I would like to change that to something else please. I remember IE6 had a great Microsoft Flag logo that would wave while a page would load. Could I get IE8 to use this logo? Sorry Trozza, you can't. "Trozza Mited" <Trozza Mited@discussions.microsoft.com> wrote in message news:9C401644-2391-4317-92BD-D7F3DFD2B06C@microsoft.com... > I am making a website and i have a lot of wait time for my server-side > p...

Help With Problem: Nested IF Function, Office2K v Office2003
Hello Group, I'm looking for some help with a nested IF worksheet function. Shown below is a formula that I'm using in a model. =SUM(IF(worksheet!\$A\$3:\$A\$54=DATEVALUE("9/1/2005"),IF(worksheet!\$B\$3:\$B\$54="<90",worksheet!\$C\$3:\$C\$54,0),0)) If short, what I'm trying to do is scan the data in two columns and for any cells which meet the TRUE case in both columns, then sum the third numbers for all cases returning TRUE-TRUE. If either cell returns a FALSE, then enter 0. I created the original formulas and model in Office2000 and have used the model for more tha...

Newbie to New Database Query - Can you change a spreadsheet to a table if so how? I have a lot of spreadsheets that I would like to work with in New Database Query. Can this be done. Any help will be greatly appreciated. In general, if you select a range and give it a name....MS Query will recognize it as a data source: Example for data in Cells A1:Z500, with column headings (EmpID, FName, Lname, etc): Select A1:Z500 In the Names box (just above the Col_A heading) type rngMyData1 Press [Enter] Next, save your workbook. Then...Data>Import External Data>New Database Query When y...

dynamically filtered pivot table
I'm trying to make a pivot table that will dynamically hide a section o its contents based on a boolean operator the user can set. Becaus this boolean is used in several places, I don't want to require th user to manually set the visibility parameters for the pivot table. It seems like the only way to do this effectively is to have th booleans set by a button, and have the button not only toggle th boolean, but also change the visibility in the pivot table. here's the code i've tried: Sub ToggleButton1_Click() If ToggleButton1.Caption = "Include" Then 'C...

MS Query help required
Hi I have a spreadsheet on my notebook which I use for DBA services at a particular client. It connects to database server via an existing ODBC database connection extracting data from system tables. I would like to be able use this spreadsheet at other clients, WITHOUT having to create a new ODBC connection. Is there any way that this can be accomplished? The ideal scenario would be that the logon credentials and server address reside on one sheet and upon arrival at another client, one merely changes this information and then queries the database. Any help would be appreciated. Regards -...

Exporting a table from Access 2003 to Excel 2007
I am trying to export a table from Access 2003 with more than 100,000 records into Excel 2007 and am not having any luck. I do not find the 2007 file extension in the drop down and choosing the latest version only exports part of the table. Any thoughts on whether this is possible to do? -- Carol Hi Carol, This is even hard to do with Excel 2007. You won't be able to do it directly with Excel 2003. You could export the file as a comma separated value with a csv suffix. By default Excel usually opens csv files. However you may lose formatting and other stuff. I ju...

2 Axis Pivot Chart
Is it possible to create a 2 axis (a combination of line & bar) chart from a pivot table? Sure is, did you try? Right click a series that you want on the secondary axis, choose Format Series, and on the Axis tab (Excel 2003 and earlier) or the Series Options tab (2007) select the Secondary Axis option. To change the chart type, right click on the series, choose (Change) Chart Type, and select the type you want. Of course, the pivot chart is likely to lose this formatting when it is refreshed, Which even Microsoft admits is a problem. They suggest recording a macro next time you f...

opencurrentdatabase error/help
dears, im opening an access03 database with vb6 like application.opencurrentdatabase("c:/documents and settings/admin/folder/database.mdb",true,"password") application.docmd.open"myform" application.docmd.maximise docmd.maximise included in form open event also in the database kindly help me with (1) after this code runs access opening with small rectangular window(half of screen vertically) even all access databases are opening this way (2) if the above said path changes for the database error 7866 occuring in error event i would like to include some c...

Text in pivot table limited to 255 characters
I am using a pivot table as an efficient way to aggregate text responses from a large data set. However, the pivot table cuts off the text after the first 255 characters (similar to when you copy a worksheet by using the move/copy option). How can I overcome this? I have tried putting the pivot table on the same sheet as the dataset, but that does not work. I should also note that these pivot tables are then fed into an automated report through a complicated set of VLOOKUPs, etc. The pivot table aggregates several questions and responses from many areas of the datset into one discr...

Need help with a formula 01-23-10
I am looking for a formula that will compute an average of a number of non contiguous cells such as G8, G16, G24, G36, etc. Each of these cells has a formula which computes an average of a range of cells. With the helpm of this forum, I have been able to find a formula which does that AND uses values only when they are greater than zero and does not display #DIV/0!. But I cannot fin a fromula that will do the next step- Take an average of those specific cells AND use only the ones where the cell is >0, Example G8=100, G16=85, G24 is blank, G36=75, then this formula would ca...