Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Pivot Tables - Posting Profit Margin as %

I have a pivot table with the categories of Sales, Cost, Profit, and Profit Margin. However, I can't seem to list the Profit Margin category to list as a Percentage of the total sales (In other words, as an actual Profit Margin). To get this column, I simply drag Profit into the values column again and try to change it to % of sales. Every time I try to do this, however, I never get to choose the sum of the sales, but rather individual items that make up the pivot table.

Is there any way to list a total profit margin percentage on a pivot table? Any help on this matter would be greatly appreciated.


Post your answer or comment

comments powered by Disqus
Please help! I'm going round and round in circles.

I've attached the pivot table and source data that I'm working with.

My problem is that I need to express and display the GROSS PROFIT % row as a percentage of (gross profit/revenue)% in the attached pivot table and I end up with either a #DIV/0 error or the whole colume being displayed in % values.

Thanks,
Brent

Example sheet attached.

I have a pivot table that displays widgets on the left column and profit margin as a value. I need to set up a drop down list on another sheet that only gives me ITEMS whose value is lower than x.

This is way over my head, but the example spells out what I am trying to do.

Hello, this is my first post. I have been searching for a solution for two days w/ no luck so I am hoping you can help.

I have a table of purchase order details & I am trying to filter it for aging PO Lines (PO Date is less than "X"). I want the users to be able to choose the period that it is filtered for so I am using "Today" - "no of days" = "PO Date to filter by" where the user chooses the No of Days.

I have it working on the Auto filters in my table (see code below) but I can't get it to work on my Pivot Tables.


	VB:
	
 
If IsDate(Range("W1")) Then 
    dDate = Range("W1") 
    dDate = DateSerial(Year(dDate), Month(dDate), Day(dDate)) 
End If 
lDate = dDate 
 
ActiveSheet.ListObjects("Table_Query_from_corridor").Range.AutoFilter Field:= _ 
21, Criteria1:="

Goal: To create a pivot table via macro that displays only the pivot items defined by the user in certain cells

Example: The pivot row field “Provider” contains hundreds of pivot items drawn from data range “ListRange”. I want to display records limited to the names selected from drop-down menus in cells A2:A5 of a worksheet, where A2 = “XYZ Trucking”, A3 = “SpeedCo.”, A4 = “FastExpress”, etc. At the same time, I want to hide all records with provider names that are not selected from the drop-down menus in A2:A5. I need to repeat this process for pivot items in other row fields, which are to be determined by drop-down menus elsewhere on the same worksheet.

Pivot Table Structure: Row fields are used because all the pivot table data is in long-text format, including the field “June2008Cost”. I duplicated one such row field as a data field in order to make the pivot table results visible by counting the records. The idea is to allow data to be queried without the burden of manually building a pivot table.

Problems: Displaying multiple pivot items per row field as defined by the user; hiding all pivot items that are not selected by the user


	VB:
	
 CreatePivotTable() 
     ' This procedure creates a Pivot Table report.
     
    Dim wksData             As Worksheet 
    Dim wksPivot            As Worksheet 
    Dim rngData             As Range 
    Dim pvtTable            As PivotTable 
     
     ' Return used data range on worksheet.
    Set wksData = ThisWorkbook.Worksheets("List") 
    Set rngData = wksData.Range("ListRange") 
    Set wksPivot = _ 
    ThisWorkbook.Worksheets("Query") 
     
     ' Create PivotTable report.
    Set pvtTable = wksData.PivotTableWizard(SourceType:=xlDatabase, _ 
    SourceData:=rngData, TableDestination:=wksPivot.Range("A25")) 
     
    With pvtTable 
        .PivotFields("Provider").Orientation = xlRowField 
        .PivotFields("Region").Orientation = xlRowField 
        .PivotFields("State").Orientation = xlRowField 
        .PivotFields("City").Orientation = xlRowField 
        .PivotFields("ServiceCategory").Orientation = xlRowField 
        .PivotFields("June2008Cost").Orientation = xlRowField 
        .PivotFields("June2008Cost").Orientation = xlDataField 
        .PivotFields("Provider").Subtotals(1) = False 
        .PivotFields("Region").Subtotals(1) = False 
        .PivotFields("State").Subtotals(1) = False 
        .PivotFields("City").Subtotals(1) = False 
        .PivotFields("ServiceCategory").Subtotals(1) = False 
        .PivotFields("June2008Cost").Subtotals(1) = False 
    End With 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thank you!

I upgraded to Excel 2007 from 2003 this week (using XP) and now my VB macro does not work in the .xlsm version (2007) of the file though the .xls version (2003) still works in 2007.
The error i got first was run-time error 5 and now it is 1004 application-defined or object defined error
Users of my report can select a job name from a drop down list, the code then populates 2 pivot tables with that review name.

	VB:
	
 
    ActiveSheet.PivotTables("Pivot1ReviewbyWK").PivotCache.MissingItemsLimit = xlMissingItemsNone 
    ActiveSheet.PivotTables("Pivot1ReviewbyWK").PivotFields("Job Name").CurrentPage = Range("B3").Value 
    ActiveSheet.PivotTables("Pivot1ReviewbyWK").PivotCache.Refresh 
    ActiveSheet.PivotTables("Pivot2ReviewbyWK").PivotCache.MissingItemsLimit = xlMissingItemsNone 
    ActiveSheet.PivotTables("Pivot2ReviewbyWK").PivotFields("Job Name").CurrentPage = Range("B3").Value 
    ActiveSheet.PivotTables("Pivot2ReviewbyWK").PivotCache.Refresh 
Else 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
the problem would appear to be the syntax of the following line:

	VB:
	
ActiveSheet.PivotTables("Pivot2ReviewbyWK").PivotFields("Job Name").CurrentPage 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
if anyone has any suggestions I would appreciate it as I am all out of ideas
thanks

I have been searching various forums to see if this question has been answered, but have been unable to find anything that I could adapt.

I am using Excel to create a pivot table, based on information provided to me daily. Therefore, the size of the Pivot Table will vary everyday, as will the column and row headings.

Once the pivot table has been created, I want to extract the column and row headings and the sum of only the cells which contain a value.

These items would preferably be then given to me in 3 separate cells.

I can't quite see how to attach a sample workbook to this post, but am happy to do so if someone can tell me how!!

To try and describe it better, my current pivot table (beginning in cell A1) has 7 columns (the totals are in column I) and 5 rows (the totals in row 8). The last column and the last row are both called '(blank)', and this will always be the case (could use to end a loop?)

I only have values in cells B6, C3, D3, E3, F3, F4, F5, F6 and G3 (excluding the totals cells).

I would like to end up with a summary table showing me the following;
A6 row name, B2 column name, B6 value
A3 row name, C2 column name, C3 value
A3 row name, D2 column name, D3 value
A3 row name, E2 column name, E3 value
A3 row name, F2 column name, F3 value
A4 row name, F2 column name, F4 value
A5 row name, F2 column name, F5 value
A6 row name, F2 column name, F6 value
A3 row name, G2 column name, G3 value

Remembering that the table can and will change size both row and column wise every day, and the row and column titles will also change daily.

If anyone can help me with this, I would be very grateful!

Many thanks
Alison

hello all,

before i go further in the development on my workbook I'd like to know whether what i intend to do is achievable easily or if i need to change my strategy.

I have 3 worksheets:
- securities
- accounts
- transactions

"securities" gather all the info on securities (equities / bonds, region: asia, us... each security has a unique numeric Id)
"accounts": the name of the account and the allocation in % by asset class and by region, each account has a unique numeric Id
"transactions": Buy or Sell securities, reference to the account holder, reference to the security, date... each transaction has a unique numeric Id.

my question is as follows:

is it possible to build a pivot table using 3 worksheets as a database knowing that I want to display on a separate worksheet and for each account the transactions done and their allocation by asset class and region.

the worksheet "transactions" will have a reference to the securities involved (unique numeric Id) and reference to the account (unique Id too)

in short:
I would like to display what's in every single portfolio with the breakdown per asset class and region knowing each account has a max exposure to asset classes and regions.

thanks!

I have a Pivot Table created in Excel from a Query in Access, if I create the Pivot in Access the data shows as Y and No but when it is created in excel using the same Access query it shows as a Number (1). Does anyone have any idea how I can resolve this. I much prefer Excels Pivot features to Access. Many thanks

I have another pivot table question. I am trying to calculate turnover as a % of total headcount for each division. I'm only interested in getting the percentage for people who have terminated (not the vast majority).
However whenever I hide the column containing the majority who haven't terminated - the pivot table keeps the total as 100% and recalculates the total % as the % of only those who have terminated.

Is there a way to hide this column and keep the % as a proportion of total headcount. I want to do this so that I can enable show detail on a pivot chart I will base on this pivot table.

Thanks for your help!

G0liath

--Edit:
I'm not sure on the exact protocol, but I've now posted this on another excel forum also. Am i supposed to edit the thread like this?
http://www.mrexcel.com/forum/showthread.php?t=393746

Hi there

I have 2 questions related to using Pivot tables for standard accounting reports:

1. Where values in a pivot table are being used as source data in another report, is it possible to create a formula that will always look for a certain field e.g. Sales (in row field) for Department 1 (in column field) even if pivot table changes format during refresh?

2. Also I can get my pivot table to show actual and budget figures. Is there a way for a simple variance column to appear and remain even when refresh data changes the pivot table values and format?

Regards
a0nghus

For some reason I've been unable to find any resource related to the following problem:
I have a pivot table with two columns of spend amounts ($), but have them displayed as "% of the row total" with the "show values as" option. I want to sort the rows from highest to lowest %, but am only able to sort based on highest to lowest $ amount. If anybody has a solution for this it would be greatly appreciated.

Hey - I'm doing some Save As stuff with a file that has a pivot table (using 2003). The pivot table, which is on one worksheet, is using data from a query that is on another worksheet in the file. I take the original file and "save as" the new file. Then, the next time I bring up the "saved as" file, I get a can't open error, the file then opens and I get a message that say's the pivot table has been eliminated, and as it says, thought the formatting is as if it was still there, I cannot refresh the pivot table, because it really is "gone."

Any ideas?

Hi, Does anyone know if it is possible to get a pivot table summary to
ignore N/As within a table when summing the data?

Nelson

I have formatted the source data, field settings, & pivot table cells as general. The source data cells contain text and numbers. When I try to filter the pivot table, a mixture of numbers, text, & dates are present.

Why are dates showing when no cells contain dates nor are they formatted as date? How can I rectify the issue?

Many thanks in advance....

Is it possible for a pivot table to automatically update as data is added to original spreadsheet? I need to add data rows to my spreadsheet almost daily...will I need to redo the pivot table each time?

Thanks!

Good Afternoon All,

I'm using a spreadsheet I made in Excel 2007 to create a Pivot Table, but I'm running into problems when trying to find/select the proper fields.

Here is how my spreadsheet is formatted:

--------(Column A)----------(Column B)-------------(Column C)--------
(sub a)(sub a)(sub a)(sub b)(sub b)(sub b)(sub c)(sub c) (sub c)

As you can see, I'm using larger, merged Columns and individual sub sections. This helps me organize the spreadsheet better, but when trying to run a pivot table only the sub sections appear as selectable fields.

How do I find and add the larger columns to my pivot table's list of selectable fields?

Please, if I can provide anymore information to help clarify what my issue is, let me know, because the way I'm explaining my issue is a bit confusing. Thanks for your time and have a blessed day!

-Oaki

Hello everyone

I have a problem concerning conditional formatting and pivot tables.

After executing the following macro:

Sub ConditionalFormat()
    Range("A1:" & ExcelColNonRec(ActiveSheet.Columns.Count) &
CStr(ActiveSheet.Rows.Count)).FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
        Formula1:="=0"
    With Selection.FormatConditions(1)
        .Font.Color = -16383844
        .Font.TintAndShade = 0
        .Interior.PatternColorIndex = xlAutomatic
        .Interior.Color = 13551615
        .Interior.TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
End Sub
Function ExcelColNonRec(ByVal intCol As Long) As String
    While (intCol > 0)
        intCol = intCol - 1
        ExcelColNonRec = Chr(65 + (intCol Mod 26)) + ExcelColNonRec
        intCol = intCol  26
    Wend
End Function
on a sheet which contains a pivot table, it happens exactly what I want which is negatives get shaded. But if after that you expand any of the categorys within the pivot table, the selection to which the conditional format is applied changes and it no longer works, even when the categorys are closed again.

Can any of you think of a solution to this, so that the conditional format will be applied to all the cells of the pivot table, even when you expand or close each category?

Thanks in advance

here is the current code i have :

	VB:
	
 Remove_Blank_From_PivotTable1() 
     ' The purpose of this macro is to remove the word (blank) from apprearing
     ' on any pivot table report.'
    Dim counter As Integer ' setting up a counter to loop through the worksheet
    Dim lastRow As Integer, lastColumn As Integer ' creating variables to store the last
     ' rows and column that contain data on the worksheet to limit the loop
    Dim WorksheetWithPivot As String ' declaring a string to store
     ' the name of the sheet containing the pivot table
    Dim wb As Workbook 
    Dim ws As Worksheet ' Declaring a Worksheet variable to use to
     ' access the worksheet with the pivot
     '_________________________________________________ __________________________________________________ _________________
    WorksheetWithPivot = "David Edwards" ' enter the name of the worksheet that contains the pivot table here
     ' replace the words "Master Pivot" with the name fo your sheet with your pivot
     '_________________________________________________ __________________________________________________ _________________
    Set wb = ThisWorkbook ' setting the workbook object to this workbook
     ' note, if you wanted to run this script on another excel file
     ' you would change the workbook and worksheets paramters, or
     ' you could just copy and paste this code into another workbook VBA module
    Set ws = wb.Worksheets(WorksheetWithPivot) ' setting the Worksheet object
    i = 1 
    j = 1 
    lastRow = ws.UsedRange.Rows.Count ' locating the last row used on the sheet
    lastColumn = ws.UsedRange.Columns.Count ' locating the last column used on the sheet
    Do While i

Hi,

I am new to Pivot Table and encountered a problem that need to be solved urgently.
I have a master worksheet that contains a lot of datas. Example below:


	VB:
	
Invoice #     Code    Customer   Money 
1            1101        a        10 
2            1102        a        20 
3            1102        b        30 
4            1103        c        40 
5            1104        d        50 
6            1101        a        60 
7            1103        c        70 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
After creating a pivot table with code, customer as Row and money as Data, I get this:


	VB:
	
Sum of money 
Code         Customer      Total 
1101         a            70 
a Total          70 
1102         a            20 
a Total          20 
b            30 
b Total          30 
1103         c            110 
c Total          110 
1104         d            50 
d Total          50 
Grand Total                   150 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Suppose now, I have another data named Rate that I need to associate with each pair of Code and Customer. See below:


	VB:
	
Sum of money 
Code         Customer      Total   Rate 
1101         a             70       100 
a Total          70 
1102         a             20       200 
a Total          20 
b             30        300 
b Total          30 
1103         c            110       400 
c Total          110 
1104         d            50        500 
d Total          50 
Grand Total                   150 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
It is easier for me to fill in the rates for each code and customer pair in the pivot table since it has been summarised. But how can I fill back the original master worksheet with this additional column of Rate so that it become like this:


	VB:
	
Invoice #     Code    Customer   Money      Rate 
1              1101        a     10        100 
2              1102        a     20        200 
3              1102        b     30        300 
4              1103        c     40        400 
5              1104        d     50        500 
6              1101        a     60        100 
7              1103        c     70        400 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

I tried to create an empty column named Rate in the master worksheet and add Code, Customer and Rate as Row in Pivot Table, but when I fill in the rates myself in the Pivot Table, those values were not reflected back in the master worksheet. Any one can help me solve this? Thanks so much!

I have 2 questions related to using Pivot tables for standard accounting reports:

1. Where values in a pivot table are being used as source data in another report, is it possible to create a formula that will always look for a certain field e.g. Sales (in row field) for Department 1 (in column field) even if pivot table changes format during refresh?

2. Also I can get my pivot table to show actual and budget figures. Is there a way for a simple variance column to appear and remain even when refresh data changes the pivot table values and format?

Regards
a0nghus

Hello everyone

I have multiple pivot tables on one sheet.
What I am trying to do is:

Group the date of every pivot table by month and by year.
I have the code working for one pivot table

	VB:
	
) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
But it is not working for each pivot table.


	VB:
	
 PivotTable 
 
Worksheets("Basic").Select 
For Each pt In ActiveSheet.PivotTables 
    pt.PivotFields("Date").LabelRange.Group Start:=True, End:=True, Periods:=Array(False, False, False, False, True, False,
True) 
Next pt 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
With the above code I get the error:
Run Time error '1004':
Unable to get the PivotFields property of the PivotTable class.

Anyone got an idea what is going wrong?
Thanks in advance!

i am trying to clear the data that is loaded from the pivot table which shows blanks in the data instead if clearing the blanks from the new sheet it clears the pivot table any idea how i can do this as mentioned here :
"when a user click the numbers in the pivot table it opens and creates a new work sheet with all the data in there i want it to blank out all the blank cells in that worksheet but instead the macro fades out the pivot table !!:
http://img20.imageshack.us/img20/2759/macro00.jpg

name of my pivot table is " PivotTable1" "
the code i have used is :


	VB:
	
 Remove_Blank_From_PivotTable1() 
     ' The purpose of this macro is to remove the word (blank) from apprearing
     ' on any pivot table report.'
    Dim counter As Integer ' setting up a counter to loop through the worksheet
    Dim lastRow As Integer, lastColumn As Integer ' creating variables to store the last
     ' rows and column that contain data on the worksheet to limit the loop
    Dim WorksheetWithPivot As String ' declaring a string to store
     ' the name of the sheet containing the pivot table
    Dim wb As Workbook 
    Dim ws As Worksheet ' Declaring a Worksheet variable to use to
     ' access the worksheet with the pivot
     '_________________________________________________ __________________________________________________ _________________
    WorksheetWithPivot = "David Edwards" ' enter the name of the worksheet that contains the pivot table here
     ' replace the words "Master Pivot" with the name fo your sheet with your pivot
     '_________________________________________________ ____________________________________________

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
any ideas how i can work around this ?

Hello,

Im having difficulties implementing a pivot table and using dates as column headings.

For Example my data looks like this:

Animal Price Date
Dog £200 10/08/2007
Cat £150 09/08/2007
Horse £350 07/08/2007
Rabbit £80 08/08/2007
Toad £20 06/06/2004
Donkey £10000 05/01/2007
Dog £200 01/01/2005
Cat £150 29/05/2006
Horse £350 3/03/2003
Rabbit £80 12/08/2007
Toad £20 04/09/2007
Donkey £10000 11/09/2007

What I want to do is have a pivot table with column headings such as '1 Day Old' 'Between 1 week and 1 month old' 'Over a month old' and 'over a year old' and show the correspondings figures with Animal in the row of the pivot table.

So:

Column: '1 day old' etc etc
Row: Animal
Data: Count of price

Cheers,

Hi,

Is it possible to create pivot table from another multiple pivot table.

Example: I have two diff pivot table "Income" and "Expense" as well
and I need to preapare new pivot table using with those two pivot table

Thanks
Udhay


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