Free Microsoft Excel 2013 Quick Reference

Formatting lost in a Pivot Table after a data refresh

I designed a Pivot table to look at various data, with 2 fields in the
column section. The information is perfectly accurate, but because of
the quantity of information, I have to set a specific alignment for
readability for the 2 fields. If I add information and refresh, I'm
loosing a portion of my formating. What should I do?

Take a look at these 2 images for a better idea of the problem:

http://i141.photobucket.com/albums/r...xcelbefore.jpg
first image, look at line 4, properly aligned.

second image after refresh, line 4 is not the same anymore. sniff
http://i141.photobucket.com/albums/r...Excelafter.jpg
Any help will be welcomed.

Lord Commando Of the Styx


Post your answer or comment

comments powered by Disqus
Can I create conditional formatting rules in a pivot table? I am trying to
create the formatting to a specific pivot field where the result is some
defined rule without having to assign it to the entire column because if I
move the pivot fields around, the conditional formatting doesn't correspond
with the original pivot field I assigned it to. Can someone help me with
this one?

I need help in writing a "IF" statement in a pivot table. My data consists
of sale orders numbers that may have several line items - I am trying to do a
count on the sale orders and Excel count each item as a separate sales order.
Is it possible to write a "IF" statement in a pivot table? Does anyone have
any suggestions as to how I should approach this?

I need help with counting unique dates in a Pivot Table.

The data range is like this:
Date Name Amount
1/1/06 Mark 15.00
1/1/06 Mark 10.00
1/5/06 Mark 35.00
1/7/06 Mark 12.00
1/7/06 Mark 22.00

What I need the Pivot Table to show is this:

Name Amount #of Visits

Mark 94.00 3

I keep getting 5 for the number of unique visits instead of 3
(1/1/06,1/5/06,1/7/06).

Is there any way to do this?

Thanks,

Mark

I use pivots regularly. I work with car data and I want to see if I can create a year range in a pivot table.

The data for example looks like this
Model year
Corolla 1998
Corolla 1999
Corolla 2000
Corolla 2001
Corolla 2002
Camry 1998
Camry 1999
Camry 2002

My data is over 10,000 lines long so doing manually would take forever.

I have the data in two columns. I put the make in the row label. This gives one line for Toyota. If I put the Year next to Make, I get one corolla and a line for each year.
What I want would look like this

Model Year range
Camry 1998-2002
Corolla 1998-2002 (Calculated based on the data).

Does anyone have an idea if this is possible?

Hi:

I'm trying to do a conditional format on some e-commerce stats I have
calculated in a pivot table. The field types are eBL% eBook% eSI%. I
want to say something like if between 0 and .5 collor background red... else
color background green...

I've been able to do this on data outside of a pivot table but I can't seem
to figure out how to do it on a pivot table.

Help??

rgds
David

I am running office 2010.

I am using conditional formatting in a pivot table to highlight rows where the value in the last column meets a particular criterion.

However everytime I update the table the formatting gets lost.

Can anyone point me in the right direction please?

Hi there. I am running office 2007. I am using conditional formatting in a pivot table to highlight rows where the value in the last column meets a particular criterion. However everytime I update the table the formatting gets lost. I have looked through all the threads Can anyone point me in the right direction please?

In my pivot table I have hundreds of unique part numbers, each returning six rows of results, in each block of data the 1st, 5th and 6th rows need to be comma separated and the 2nd, 3rd and 4th need to be currency, is there a non-manual way of formatting this?

I have a table of data with column headers that include; one for a unique reference, one for the time of day and another for incidents. I am trying to create a chart that has the times of the day in one hour intervals on the x axis in the format 3:00 AM from 12:00 AM to 11:00 PM.

The origonal data is in the time format 06:35:19. I have approached this from two angles:

1. To give me the whole spread so that the chart showed zero values, I changed the time format to custom h:mm: AM/PM and added the 24 hour periods to the time column, in the same format and created a pivot table. This gave me the right hour titles but showed the returns that were inbetween the hours ie 6:35 PM. I then grouped by hour which resolved this but lost the formatting for the time ie it showed 11, 10 etc.

2. I then went back to the drawing board and manipiulated the source data by adding an adjacent column to the time column and used a formula string =IF((D5>=TIMEVALUE("23:00:00")),"11:00 PM", and so on for each hour value to give me whole hours. Likewise as in 1, I added the 24 hour periods below. However when I now create my pivot table the time periods are replicated for the rows that I used the formula for ie 6:00 PM but for the rows in the 24 hour period they change to a time format of 06:00:00 even though the source data is saying 6:00 AM

There must be an easier way of creating this chart if anyone can help that would be great

Is there a simple way to make all row subtotals in a Pivot table appear in Bold? for example, my current pivot table has metrics (# of Accounts, Fees, etc.) across the top. Team Members and Team Names are down the side. the Team Name field has subtotals. i would like to make the subtotals stand out from the rest of the data by making them bold. but i would like to avoid having to format one row of subtotals at a time. Thanks in advance

Hi all, I'm having a lot of problems attempting to retain the conditional formatting in a pivot table. It seems that everytime the data get refreshed, the pivot table loses its formatting. does anyone have any workaround to this? I have tried the conditional formatting by formula but still with the result. For now, I need to manually reapply the formatting.

Any help will be greatly appreciated.

Thanks in advance.

Sorry, this is a really basic question but I'm only new to Pivot tables.
I am trying to calculate the frequency of some data and I have some ages in
a pivot table that i want to group but when I click "group" another column
with the heading "Group1" appears with no data in it.
I have grouped data once before but I can't remember how to do it properly

A more fundamental issue is identifying and getting back to the data source
in a pivot table in 2007. Sometimes it is necessary to add new columns or
rows of data which is to be included in the pivot table.

In 2003 you can backpedal by right clicking on any pivot table cell and then
going back through the Wizard to locate the data source. Then I make my
changes to the data source parameters and refresh the pivot table.

I cannot find a method to achieve this within 2007. In order to achieve the
same result I have to blow away the current pivot table and restart the
process. There must be a way but I can not see it. Can you get me an answer?

I am finding Excel 2003 a absolute nightmare to use. IMO it has been changed
to the point of unusability in some respects.
However, I must move with the times. I have found some workarounds thanks to
the suggestions from this forum and I do like many of the features in 2003
but my current problem is driving me absolutely crazy.

I very frequently reference other cells, often cells which are in a Pivot
Table. To do this is Excell 97 or 2000 I would click the "=", then click the
cell i want to reference. The "=" has disappeared, replaced with the almost,
but not quite totally, useless, "fx" button. I now use a custom "=" insert
button, which works fine for me.
Anyway, Pivot tables have always been a problem to chart, you can't use the
data in a scatter plot, so I normally just reference the data in the PT in
the adjacant cells.
What Excel 2003 has done to me, (obviously a personal insult directed at me
specifically) has automatically replaced the A1 cell reference with a load of
unnecessary garbage, WHICH IS AN ABSOLUTE REFERENCE, completely negating the
shortcuts i am accustomed to using when dealing with large amounts of data.

For example, instead of click "=", Click cell (in a pivot table) G12,
producing "=G1"
it produces this:
=GETPIVOTDATA("gm
x",$A$10,"xory","x","focus",9573,"zone",0,"col",1, "row",1,"xnom",6000,"ynom",6000)

An absolute reference, a load of unnecessary crap and basically unusable
when I want to build a formula from this as a starting point. Not only is it
an absolute reference (the $A$10 bit) but it is referencing the cell I
clicked as another, within function, undragable, locked to the text function
argument, cell of the pivot table.

I have to click "=", click and drag a vector , eg =G12:G14 and manually
remove the ":G14" part, in order to make it function "normally".
This has me on the verge of tears on the late nights when I am trying to
deal with the massive amounts of data I am trying to analyse before the
meeting at 9 the next morning...

The question: Can I turn off this automatic "GETPIVOTDATA" function
insertion when I simply want to reference a cell in a pivot table?

Thanks for your patience

Incoherent

Hello,

I have a pivot table that I filter through VBA. The problem is that when I delete the values in the range that the pivot table uses as data source, it still shows the values in the columns dropdown (even if I refresh) and when there is data in the range, the dropdown shows data that doen't exist in such range (the column Year plan only has 2012 values but it shows the 2013 option too, and when you select it the pivot table can't show any data).

The filter area, nevertheless, works correctly and data dissapear when I delete tha values in the data range.

I have checked another excel file and I see that a pivot table in there works the way I wish: no data - empty dropdown in columns and rows.

The code I use to control the pivot table is:

Sub createExpensesPT(Optional CalcPivot As
Boolean, Optional sendprog As Variant, Optional sendyear As Variant)



elemfound = False

Set pt = Worksheets("Reports").PivotTables("PivotExpenses")
    
pt.RefreshTable

pt.ManualUpdate = True


'________________________ FILTERING OF THE PIVOT TABLE
___________________________________________________________________________

filterName = "Programs"
Debug.Print " "
Debug.Print "========= Expenses by program Chart ============"
Debug.Print "filtername is " & filterName
'Debug.Print "elementName is " & elementName


Set pf = pt.PivotFields(filterName)
With pf
    .AutoSort xlManual, .SourceName
    Debug.Print "The source name of the field is: " & .SourceName
    
    '####### IF NO PROGRAMS WERE SELECTED WE MAKE ALL OF THEM VISIBLE ########
    'We make all the elements visible and we warn the user.
    If CalcPivot = False Then
        GoTo makeallvisible
    End If
    '####### MAKING THE SELECTED PROGRAMS VISIBLE ########
    If sendprog(0) <> "" Then
        For i = 0 To UBound(sendprog, 1)
            elementName = sendprog(i)
            For x = 1 To .PivotItems.Count
                itemstr = .PivotItems(x).Name
                Debug.Print "Checking for " & itemstr & " in the " & .SourceName & "
field."
                If itemstr = elementName Then
                    On Error Resume Next
                    .PivotItems(x).Visible = True
                    On Error GoTo 0
                    Debug.Print "Values for " & .PivotItems(x).Name & " are VISIBLE."
                    elemfound = True
                    Debug.Print "elemfound is " & elemfound
                    If titlestr = "" Then
                        titlestr = elementName
                    Else
                        titlestr = titlestr & ", " & elementName
                    End If
                    Exit For
                ElseIf x = .PivotItems.Count Then
                    prognotfound = True
                    Debug.Print "the program " & elementName & "  wasn't found in the " &
.SourceName & " field."
                    If notfoundstr = "" Then
                        notfoundstr = Chr(13) & Chr(149) & " " & elementName
                    Else
                        notfoundstr = notfoundstr & Chr(13) & Chr(149) & " " & elementName
                    End If
                End If
            Next
        Next
        Sheets(Reports_sheet).Cells(12, 2).Value = " Expenses by Program: " & titlestr
    End If
    
    '####### HIDING THE OTHER PROGRAMS ########
    'We go through the pivot field items and go through the programs array searching for the elements.
    'If we don't find the the pivot item in the array we hide it.
    If elemfound = True Then
        Debug.Print "elemfound was true, so we hide the other items in the " & .SourceName & "
field."
        For x = 1 To .PivotItems.Count
            itemstr = .PivotItems(x).Name
            For i = 0 To UBound(sendprog, 1)
                If itemstr = sendprog(i) Then
                    Debug.Print itemstr & " = " & sendprog(i)
                    Exit For
                ElseIf i = UBound(sendprog, 1) Then
                    Debug.Print itemstr & " is HIDDEN."
                    .PivotItems(x).Visible = False
                End If
            Next
        Next
    Else
'----> We make all the programs visible
makeallvisible:
        For Each ptItem In pf.PivotItems
            If ptItem.Visible <> True Then
                On Error Resume Next
                ptItem.Visible = True
                On Error GoTo 0
                Debug.Print "Values for " & ptItem.Name & " are VISIBLE."
            End If
        Next
    End If
    .AutoSort xlAscending, .SourceName
End With


'####### FILTERING BY YEAR ACCORDING TO THE YEAR ARRAY ########

If sendyear(0) <> "All data" Then
    Set pf = pt.PivotFields("Year Plan")
    With pf
        .AutoSort xlManual, .SourceName
        Debug.Print "The source name of the field is: " & .SourceName
        For x = 1 To .PivotItems.Count
            itemstr = .PivotItems(x).Name
            Debug.Print "itemstr is: " & itemstr
            For i = 0 To UBound(sendyear, 1)
                If itemstr = sendyear(i) Then
                    On Error Resume Next
                    .PivotItems(x).Visible = True
                    On Error GoTo 0
                    Debug.Print itemstr & " is the same as " & sendyear(i) & " so we make it
VISIBLE."
                    Exit For
                ElseIf i = UBound(sendyear, 1) Then
                    Debug.Print .PivotItems(x).Visible
                    On Error Resume Next
                    If .PivotItems(x).Visible = True Then .PivotItems(x).Visible = False
                    On Error GoTo 0
                    Debug.Print itemstr & " wasn't found in sendyear() so we make it HIDDEN."
                End If
            Next
        Next
    End With
Else
    Set pf = pt.PivotFields("Year Plan")
    With pf
        .AutoSort xlManual, .SourceName
        Debug.Print "The source name of the field is: " & .SourceName
        For Each ptItem In pf.PivotItems
            If ptItem.Visible = False Then ptItem.Visible = True
            Debug.Print "Values for " & elementName & " are VISIBLE."
        Next ptItem
    End With
End If
   


pt.PivotFields("Quarter Plan").AutoSort xlAscending, "Quarter Plan"

pt.ManualUpdate = False




End Sub
I would much appreciate your help.

I am building a database with multiple records.

-Each record has a Unique ID.
-Each Record provides info about 3 marketing activities
-Each activity is funded by a the same of different fund pot of money.

So in one record I have several fields called fund pot1, fund pot2, fund pot3

Data Validation is A, B, C, D

I need to group the data into one field in a pivot table.

Dave32

I am finding Excel 2003 a absolute nightmare to use. IMO it has been changed
to the point of unusability in some respects.
However, I must move with the times. I have found some workarounds thanks to
the suggestions from this forum and I do like many of the features in 2003
but my current problem is driving me absolutely crazy.

I very frequently reference other cells, often cells which are in a Pivot
Table. To do this is Excell 97 or 2000 I would click the "=", then click the
cell i want to reference. The "=" has disappeared, replaced with the almost,
but not quite totally, useless, "fx" button. I now use a custom "=" insert
button, which works fine for me.
Anyway, Pivot tables have always been a problem to chart, you can't use the
data in a scatter plot, so I normally just reference the data in the PT in
the adjacant cells.
What Excel 2003 has done to me, (obviously a personal insult directed at me
specifically) has automatically replaced the A1 cell reference with a load of
unnecessary garbage, WHICH IS AN ABSOLUTE REFERENCE, completely negating the
shortcuts i am accustomed to using when dealing with large amounts of data.

For example, instead of click "=", Click cell (in a pivot table) G12,
<enter> producing "=G1"
it produces this:
=GETPIVOTDATA("gm
x",$A$10,"xory","x","focus",9573,"zone",0,"col",1,"row",1,"xnom",6000,"ynom",6000)

An absolute reference, a load of unnecessary crap and basically unusable
when I want to build a formula from this as a starting point. Not only is it
an absolute reference (the $A$10 bit) but it is referencing the cell I
clicked as another, within function, undragable, locked to the text function
argument, cell of the pivot table.

I have to click "=", click and drag a vector , eg =G12:G14 and manually
remove the ":G14" part, in order to make it function "normally".
This has me on the verge of tears on the late nights when I am trying to
deal with the massive amounts of data I am trying to analyse before the
meeting at 9 the next morning...

The question: Can I turn off this automatic "GETPIVOTDATA" function
insertion when I simply want to reference a cell in a pivot table?

Thanks for your patience

Incoherent

I want to show a column of data (% of City) as a % of a sub group
total. Here's my example of how the pivot table should look:

State City Carrier Sq Yards % of City (Sq Yards2)
Alabama
Birmingham
Carrier 1 10 20%
Carrier 2 20 40%
Carrier 3 10 20%
Carrier 4 10 20%
Birmingham Total 50 100%
Mobile
Carrier 1 10 25%
Carrier 2 10 25%
Carrier 3 10 25%
Carrier 4 10 25%
Mobile Total 40 100%

I can't get the last column to calculate. The last column(Sq Yards2)
is simply a duplicate of Sq Yards, however, it is displayed as a % of
column. I don't want Column to show, I want a % of City shown. How
can I do this in a pivot table ??

I have data that I would like to organize in a pivot table, but my data for
the data area does not need to be summarized. I am creating a calendar with
tasks by audience, month and method of completion and I would like to display
the tasks by month (column heading), by method (row heading), by audience
(page heading).

I currently have two data fields that I'd like to show in columns in a pivot table. When I generate the table, however, they always show in rows on top of each other for each row field values. Could someone please let me know what properties I need to set in order to see them in columns? Thank you in advance for your help!

Please below see code and screenshot of what I want and what I currently have:

Here is what I need: TempQuestion.JPG

The code below works in creating the pivot table, but I'm not sure why they data fields are not in columns:
    strTemp = "QREs!R1C1:R2800C17"
    
    Set ptCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=strTemp)
    Set pt = ptCache.CreatePivotTable(tabledestination:=ActiveSheet.Range("A3"),
tablename:="PivotTable1")
    
    With pt.PivotFields("State")
        .Orientation = xlRowField
    End With
    
    With pt.PivotFields("Original Wages")
        .Orientation = xlDataField
        .Function = xlSum
        .Position = 1
    End With
    
    With pt.PivotFields("Qualified Wages")
        .Orientation = xlDataField
        .Function = xlSum
        .Position = 2
    End With
    
    With pt
        '.ColumnGrand = True
        .RowGrand = False
        .NullString = "0"
    End With


Folks,

I am linked to a SQL table where we use a field to determine if an object is unique to a certain customer or for general public use. The SQL table is designed so that the customers number is put in this field and if it is blank then it is considered an item open to the public. I am trying to work in a pivot table and it is showing a blank area in that field on the drop down for the filter but it will not let me select it. I have tried in the search box typing "", "(blank)" and "NULL" all to no avail. Any ideas since I can't change the core programing in SQL other than bringing the data into excel and adding qualifying fields?

I'm stuck on a pivot table project.

Data is simple timeclock records
Date, employee, in outbreak, inbreak, out hours

I created the pivot table to report hours per day no problem.

Subtotal will report the hours per week, but of course doesn't break out OT for hours>40.

I have played with "calculated items, and fields" and I can get the breakout per week, but run into trouble for the 2 week sum. I need my report to show a two week frame with hours40 as OT for each week and then the sum of regular hours and OT hours for the two week period. The calculated items and fields apply the same formulas to the totals if the pivot table is filtered for the time period. I know I can add a column to the data with a sumif(week#.... type formula, but it seems there is a more elegant solution from within the pivot table. Any ideas?

Goal
employee/mon,tue....hours/ot/mon,tue,...hours/ot/total hours/total ot

thanks!

can i use a dynamic range name in a pivot table...

e.g.,

=OFFSET(DynamicRangeName!$A$1,0,0,COUNTA(DynamicRangeName!$A:$A),1)

--i need to be able to refresh the pivot and expand the underlying data range...

thank you...

I have been using a fomula like this:

	VB:
	
0") 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
to Average without zeros in a regular spreadsheet, but how do I apply that in a pivot table when I don't know what my column ranges are going to be? For example will they be B7 to B37 or will they be B (some other starting and ending number depending on how the PT is layed out? I'm pretty comfortable with the averaging fomulas, but still a bit new to creating or modifying PT standard Average functions. /Thanks!


No luck finding an answer? You could always try Google.