Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
format numeric data in pivot table
I just created program to make pivot table.
How can I format the 'numbers' part of the pivot table to be comma delineated with two significant digits after the period? Note, I'm not even sure how to select just the 'data' part of the pivot table. -- Richard |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
format numeric data in pivot table
Here is all you do:
right-click inside the pivot-table. Select PivotTable Wizard. Then select Layout. Then select the Item in the Data area of the resulting layout screen. Double-click it and then click the Number button. Check the 1,000 separator box (comma). That is it! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
format numeric data in pivot table
Mike,
I need to do this in the macro that created the pivot table. So I can't right-click inside of the data. -- Richard "Mike H." wrote: Here is all you do: right-click inside the pivot-table. Select PivotTable Wizard. Then select Layout. Then select the Item in the Data area of the resulting layout screen. Double-click it and then click the Number button. Check the 1,000 separator box (comma). That is it! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
format numeric data in pivot table
Mike,
I need to do this in the macro that created the pivot table. So I can't right-click inside of the data. -- Richard "Mike H." wrote: Here is all you do: right-click inside the pivot-table. Select PivotTable Wizard. Then select Layout. Then select the Item in the Data area of the resulting layout screen. Double-click it and then click the Number button. Check the 1,000 separator box (comma). That is it! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
format numeric data in pivot table
I don't think you can do it to the pivot table but if you select the columns
that the data resides in and format it within your macro and then add code to the worksheet calc() function to reselect and reformat each time the pivot table changes you can accomplish the same thing. Here is how I did it recently: 'this function goes in the "ThisWorkbook" Objects List in the VBA editor Public OutletVal As String Private Sub Workbook_SheetCalculate(ByVal Sh As Object) 'put code in here to reformat the cells if the value of a "critical cell" in the pivot-'table, outletval, changes. if range("outletval").value<outletval then 'put code here end if End Sub HTH |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
format numeric data in pivot table
Only problem with you solution is that every time I run my program I will get
a different number of rows. I guess I cold just format a number of rows larger than I would likely expect to ever create. -- Richard "Mike H." wrote: I don't think you can do it to the pivot table but if you select the columns that the data resides in and format it within your macro and then add code to the worksheet calc() function to reselect and reformat each time the pivot table changes you can accomplish the same thing. Here is how I did it recently: 'this function goes in the "ThisWorkbook" Objects List in the VBA editor Public OutletVal As String Private Sub Workbook_SheetCalculate(ByVal Sh As Object) 'put code in here to reformat the cells if the value of a "critical cell" in the pivot-'table, outletval, changes. if range("outletval").value<outletval then 'put code here end if End Sub HTH |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
format numeric data in pivot table
if you know your pivottable starts on row 5 you could do this:
let x=5 do while true if cells(x,1).value="Grand Total" then 'pivottable total at bottom row... exit do end if x=x+1 Loop 'now you know your pivot table goes from rows 5 - X. "Richard" wrote: Only problem with you solution is that every time I run my program I will get a different number of rows. I guess I cold just format a number of rows larger than I would likely expect to ever create. -- Richard "Mike H." wrote: I don't think you can do it to the pivot table but if you select the columns that the data resides in and format it within your macro and then add code to the worksheet calc() function to reselect and reformat each time the pivot table changes you can accomplish the same thing. Here is how I did it recently: 'this function goes in the "ThisWorkbook" Objects List in the VBA editor Public OutletVal As String Private Sub Workbook_SheetCalculate(ByVal Sh As Object) 'put code in here to reformat the cells if the value of a "critical cell" in the pivot-'table, outletval, changes. if range("outletval").value<outletval then 'put code here end if End Sub HTH |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
In Office 07, how do I format my pivot table data? | Excel Worksheet Functions | |||
Pivot Table Format from Source Data | Excel Discussion (Misc queries) | |||
Format Extracted Data from Pivot Table | Excel Discussion (Misc queries) | |||
Pivot Table format changes when data is refreshed | Excel Discussion (Misc queries) | |||
Pivot Table Data format | Excel Programming |