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

Free Microsoft Excel 2013 Quick Reference

How To Preserve Borders of pivot tables in worksheet when it is refreshed

Hi, I have some pivot tables. AND I formated them.

when I referesh them. the border formatting get lost. the other formatting like color and font is as it is but the bordering is going off when refereshing them.

Is there any solution for this ?


Post your answer or comment

comments powered by Disqus
How to make a checkbox on a chart invisible when it is clicked? I have some
code which hides a coulmn when a checkbox is clicked. Now my users want not
only hide the column, but also get the checkbox itself to "go away" - I
translate it as "make invisible "

Now I have the code:

Sub Check_Box_Click()

Sheets("Summary_Worksheet").Select
Select Case Application.Caller
Case "cbI40"
Columns("I").Select
Case "cbJ40"
Columns("J").Select
Case "cbK40"
Columns("K").Select
Case Else
End Select

If Selection.EntireColumn.Hidden = False Then
Selection.EntireColumn.Hidden = True
Else
Selection.EntireColumn.Hidden = False
End If

Sheets("Star-plot").Select

End Sub

I want to get something like that working:

Sub Check_Box_Click()

Sheets("Summary_Worksheet").Select
Select Case Application.Caller
Case "cbI40"
ActiveSheet.Shapes("cbI40").Select ==> I know that is wrong (it does
not work)
Selection.Hidden = True
Columns("I").Select
Case "cbJ40"
ActiveSheet.Shapes("cbJ40").Select ==> I know that is wrong (it does
not work)
Selection.Hidden = True
Columns("J").Select
Case "cbK40"
ActiveSheet.Shapes("cbK40").Select ==> I know that is wrong (it does
not work)
Selection.Hidden = True
Columns("K").Select
Case Else
End Select

If Selection.EntireColumn.Hidden = False Then
Selection.EntireColumn.Hidden = True
Else
Selection.EntireColumn.Hidden = False
End If

Sheets("Star-plot").Select

End Sub

the plan is that appropriate checkbox will became hidden first and then
appropriate column will be selected and in the If than else whichever column
is selected, will get hidden as well (part with columns works fine)

Thank you

Hi

I have never used pivot tables in 2003, so nothing of that sort... but whenever I use pivot tables in excel 2007, it irritates me, and people around me who generally claim that they could perhaps do it in excel 2003.

So here goes my questions.

1. How to make it remember what I have done right now? (Or may be how to change default settings?)
eg- In first column I have some classification, and then I have 12 months data, in 12 columns.
So I put first column in Row Label. Then I put 2nd column (first month data) in "Values" as "sum". Then I want to add all the months into the "values" column as sum, just by ticking in the checkboxes present in Field Section. But somehow excel behaves badly, it sometimes add the field into row label, or sometimes it will put the field in "values" but not as "sum" but as "count".

2. Is 24 month data too big to handle?
If I have 24 month data, and I want to add all the 24 fields in "values", it seems pretty irritating to me to drag each field into the right area and then set up the position of field section and area sections so that you can put next field in right place. (Or if you are dragging to pivot table itself instead of area sections, you will have to set up the position of worksheet so that you can put the next field in right place.

Please help with these two problems, it seems that I might ask more questions in future.

I am very much comfortable with Excel 2003 Pivot table. But i am using Excel 2007. The problem with excel 2007 pivot table is for each Row Position is not showing in Seperate column (only tab space is coming). If i try to copy & paste the Pivot table in a new sheet, then each row is not copied in seperate column in Excel 2007 Pivot table. But in excel 2003 Pivot table, each rows are copied in seperate column. How to overcome this problem in Excel 2007. Please Help! Thanks in advance

Greetings,
I'm new to the forum and have a question about pivot tables. I'm using excel 2003 and I am in the process of trying to convert a ton of pivot table based spreadsheets that were used for reporting into reports in Microsoft SQL Reporting Services. The pivot tables pull from external data sources within our data warehouse. I did not create these spreadsheets initially so I have to look at the query within the data source to find out what the SQL code is, and what tables the data is pulled from. I have found several spreadsheets that contain numerous pivot tables in them. My question is does excel have a utility that will give me a summary or list of pivot tables that are in a spreadsheet? Thanks in advance,

cooleddie

Hi,

Is there an easy way of determining the names of pivot tables in a workbook?. I have approx 20 pivot tables in a workbook.

Thanks,

Daniel

how to Show Formula of one Cell in NEXT CELL,

I mean i have formula in cell A2 = degrees(B2)

BUT NOW I WANT TO SHOW IN CELL A5 = WHAT FORMULA WAS THERE IN A2
MEANS

A5 SHOULD SHOW DEGREES(B2), BUT SHOULD NOT GIVE RESULT.

THIS PURPOSELY FOR SHOWING EQUATION ONLY.

HOW TO DO THAT.

Hi All,

I have runa macro to autoformat all pivot tables in my workbook as follows:

Code:
Option Explicit

'---------------------------------------------------------------------------------------
' Module    : RefreshAllWorkbookPivots
' Author    : Sourced from Ozgrid (modified slightly to allow for autoformat of the
'             pivot tables Refreshed).
' Date      : 15/01/2008
' Purpose   : Refresh All Chosen Pivot Tables in a Workbook
'---------------------------------------------------------------------------------------

Sub RefreshAllWorkbookPivots()

    Dim pt As PivotTable
    Dim ws As Worksheet

    For Each ws In ThisWorkbook.Worksheets

        For Each pt In ws.PivotTables
            pt.RefreshTable
            pt.Format xlReport2 ' here we are aplying the autofrmat number 10,
                                ' found from Format > Autoformat
        Next pt

    Next ws

    Application.Calculate

End Sub
I want to go back top the classic pivot table view, with no formats, as they are produced by default.

Could anyone please explain how to do this, macro, or otherwise?

Thanks

Hi. I am trying to use VBA to copy and paste by criteria from a pivot table. That is, I want to select the area for each Book below and its corresponding IDs and Totals.

I know how to build the pivot table in VBA but once the pivot table is done, I do not know how to distinguish each range for each book, thereby enabling me to copy and paste that range elsewhere in my spread sheet (such as in the spreadsheet below -- I want to take the pivot table on the left and ). Any suggestions would be appreciated. Thank you, Mike.

Step 1: Build Pivot Table (this I can do in VBA)

Sum of Profit
BOOK ID Total
Book1 32 2
84 7
Book2 23 5
43 3
Book3 34 2
21 87
56 23
76 6
45 34
34 5
Book4 23 6
34 7
98 1
67 2
Book5 56 6
23 34
51 23

Step 2: Copy by criteria (book)/Paste using VBA (This is my question)

Book1 32 2
84 7

Book4 23 6
34 7
98 1
67 2

Book2 23 5
43 3

Book3 34 2
21 87
56 23
76 6
45 34
34 5

Book5 56 6
23 34
51 23

After many hours of learning how to do calculated fields in my pivot table, I hit a major setback when I read in Pivot Tables Data Crunching that there is no solution to the example given on page 119 of calculated fields giving the wrong subtotal or total.

The work-around given on page 119 takes away the effectiveness of pivot table calculated fields since it says to eliminate subtotals and totals and then calculate your own totals.

Now I concerned that my calculated fields may give wrong sub-totals and totals. I have to manually recheck my totals and subtotals of calculated fields since I cannot rely on the accuracy of calculated fields.

I have a pivot table in excel 2007 that is based on an external data source.
Assume the pivot table is arranged as the following: Sales by country,
region (of the country), by state, then county within the state.
I want a pivot table to extract the data and display a summarized value by
country with the ability to expand the segments as needed.
For example display sales by Canada, Mexico, USA. Then from that a user
will expand a country to display sales by region. Then expand a region to
display sales by state, then allow expansion to display by county.
As the report is run, the data should be presented in summary based on
country (for example Canada, Mexico, US)
Currently, after the data is refreshed, all columns are expanded showing a
very detailed report rather than a very summary report allowing the ability
to expand as needed. Currently, I need to collapse the columns before
presenting the data.
So… How do I get a Pivot table to default certain columns as collapsed,
rather than expanded?

Hi all,
here i am again with a question that sounds easy but, for me, it´s really tricky.

Ok , i had a perfect functional Pivot Table. Few days ago I had to change its source (just added some fields and updated old ones on the MSACCESS Database). For my surprise my "new" pivot table keeps showing all the old fields on my pivot filters. I know that in order to hide them all I have to do is "uncheck" them... bu guys, believe me, I have a lot of Pivot Tables... a lot!

Is there anyway that I can clean my pivot tables so it show only my new fields of the DataBase?

Many thanx in advance!
Brites

Hi everyone.
I created a pivot table in Excel 2007. It had 5 columns and over 1800
rows (almost 10000 cells all together) and use to work fine in both
Excel 2007 & 2000.

There was one feature that I didn’t like however. It use to sort data in
one of the columns (column no. 2). Unfortunately these data should be
presented exactly as they were entered (without any sorting). To
workaround I added 1 extra column. It contains numbers in ascending
order. This column was placed in front of the column no. 2 and hidden.
Although I got rid of sorting, a new problem occurred – pivot works OK
in Excel 2007 only. Excel 2000 doesn’t accept such pivot table and I get
a message that it’s incorrect. If I want to reconfigure the pivot I get
a message that there is to many columns or rows and some of them should
be removed from the pivot.
Any ideas how to solve the problem of sorting without adding a new
column? Or maybe there is a way to force Excel 2000 to work with pivot
in conjunction with larger amount of data? Thanks in advance for any help.

gordom

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 using Pivot table's advance function to select "top 10" choosing
variance Up/(Down) as selective field for top 10 and gives me more than 10
customer listing as well as zero values. How can I eliminate zero values out
of Pivot table and secondly why it is listing more than 10 customer listing?

Data
Region Customer Name Feb Jan Variance Up / (Down)
Dublin Charles Taylor Consulting Plc 11,791 - 11,791
ABN AMRO Bank NV 94,494 84,736 9,758
Cap Gemini UK Plc 11,968 11,785 183
Vauxhall Motors Ltd 2,594 2,555 40
Telecity UK Ltd - -
Synstar Computer Services (UK) Ltd - - -
SKYNET SYSTEMS LTD - - -
Siemens VDO Automotive Limited - - -
Redstone Communications Limited - - -
Nescot Corporation - - -
Microsoft Ireland Operations Ltd - - -
LAN Communications - - -
GFI Informatics Limited - - -
Erinaceous Group Plc - - -
Easynet Group Plc - - -
Comstor Ltd - - -
Xpert Systems Ltd - - -
Unisys Limited - - -
Cablecom Networking Limited - - -
BSW Timber Plc - - -
Dublin Total 120,848 99,076 21,772

I am using Pivot table's advance function to select "top 10" choosing
variance Up/(Down) as selective field for top 10 and gives me more than 10
customer listing as well as zero values. How can I eliminate zero values out
of Pivot table and secondly why it is listing more than 10 customer listing?

Dinesh

Hi all,

Would like to know is it possible to apply filter on source data of Pivot table in Excel 2007? My scenario as below:

The source data contains sales figures and we'd like to have a Pivot table to sum & count rows with sales amount > 10K. We tried to set sales amount to the Report Filter but we have manually mark the selection.

Thanks,
Frankie

Hi,

I have a workbook that has a load of pivot tables in it. When I share the
workbook I can't use the pivot tables anymore. Please would someone tell me
why, and how do I get around this. I need to be able to share this
spreadsheet so that other users can make use of the pivot table I have
created.

TIA,

Jarryd

Hi,

First time posting here, glad there are places on the web to ask questions with excel and macros because I am totally lost at the moment.

I have a spreadsheet that contains raw data (attached). The raw data is on sheet 1 and on sheet 2 is an example of what I would like to achieve.

Basically what I would like to do is have the objectives go along the top (removing duplicated names) and have the risks go down the side (also removing duplicated names).

In the centre is the sort of "Pivot table" where if the risk is in the objective from the raw data then that square is coloured.

Objective Risk Objec 1 Risk 1 Objec 1 Risk 2 Objec 2 Risk 3 Objec 3 Risk 4 Objec 4 Risk 1
Would give

Objec 1 Objec 2 Objec 3 Objec 4 Risk 1 Coloured Coloured Risk 2 Coloured Risk 3 Coloured Risk 4 Coloured
I hope that makes some sort of sense, I really have no idea where to start with this. I looked at the pivot table function in Excel but cant see it doing what I need.
Another note is the amount of Objectives and Risks can change.

Hope someone can help.

Thanks in advance

Glenn

Hi Experts!

I have created Pivot Table in the Excel 2003 using the OLAP cube "Food Mart 2000" from the SQL server 2000. Then the cube has been migrated to SQL Server 2005.

I am trying to change the pivot table datasource to connect to the SQL 2005 cube. How to change the Datasource in Excel2003 connection string for this pivot Table to connect to SQL server 2005 Database.

Thanx,
siva

I am using Pivot table's advance function to select "top 10" choosing
variance Up/(Down) as selective field for top 10 and gives me more than 10
customer listing as well as zero values. How can I eliminate zero values out
of Pivot table and secondly why it is listing more than 10 customer listing?

Data
Region Customer Name Feb Jan Variance Up / (Down)
Dublin Charles Taylor Consulting Plc 11,791 - 11,791
ABN AMRO Bank NV 94,494 84,736 9,758
Cap Gemini UK Plc 11,968 11,785 183
Vauxhall Motors Ltd 2,594 2,555 40
Telecity UK Ltd - -
Synstar Computer Services (UK) Ltd - - -
SKYNET SYSTEMS LTD - - -
Siemens VDO Automotive Limited - - -
Redstone Communications Limited - - -
Nescot Corporation - - -
Microsoft Ireland Operations Ltd - - -
LAN Communications - - -
GFI Informatics Limited - - -
Erinaceous Group Plc - - -
Easynet Group Plc - - -
Comstor Ltd - - -
Xpert Systems Ltd - - -
Unisys Limited - - -
Cablecom Networking Limited - - -
BSW Timber Plc - - -
Dublin Total 120,848 99,076 21,772

I am using Pivot table's advance function to select "top 10" choosing
variance Up/(Down) as selective field for top 10 and gives me more than 10
customer listing as well as zero values. How can I eliminate zero values out
of Pivot table and secondly why it is listing more than 10 customer listing?

Dinesh

Is there a maximum number of pivot tables that can be in one file?

I have a data set of about 40,000 rows for each month, and 19 months. I want to have a tab for each month, plus a pivot in a new tab for each month. That would be a total of 38 tabs and 19 pivot tables.

Or does this depend on system resources?

I' ve got a spreadsheet with events as row headings, people's names as
column headings and dates in the cells. E.g.
Person 1 Person 2
Birthday 01/07/61 05/10/67
Anniversary 10/08/90 10/11/92

(Actually the list is much longer....)

Now I want to make a new list list converting the above data to [dates
(in column 1; event in column 2; person in column 3]. Sort of pivot
tables in reverse......!!

How to?

Wim

Hi All,

I have got a scenario where i have 6 different worksheets, each sheet has 2 pivot tables.
I have got a data display page for each of the 6 pivottable worksheets and this display page should have a button and clicking this button should refresh the pivot tables in the corresponding worksheet.I tried different ways but nothing seems to work.Can we do this?
Should we use the Form Control or ActiveX control button to do this?

Hello all!

My task is to export data from Access database to excel file via VB.NET. That excel file contains several sheet, in one sheet (let's call it sheet1) I export the data and other sheets contains pivot tables with various statistics that relay on data that is contained in the sheet1. After I export data to sheet1 I need to refresh pivot tables in other sheets, so that they are aware of new data. I do that with the following code:
...
workBook.Worksheets(2).PivotTables(1).RefreshTable()
workBook.Worksheets(3).PivotTables(1).RefreshTable()
...

That part is O.K., but the thing that troubles me is how to define range of pivot table before refreshing it?

The number of rows that I insert in sheet1 is not fixed and it changes very often. I've put that the range of rows, in pivot table, is 5000 which should be enough, but then the problem is that in exported data some fields in rows are empty and pivot table shows those fields which I want to avoid...

It looks something like this:

a b c
1 2 3
4 5 6
7 9

I would like to limit the range of pivot table, in this case, to first 3 rows...

If the range can not be changed from program is there a way to tell pivot table to avoid blank rows?

Thanks in advance for response...

Hi, I have a Pivot Table in Excel 03. It is available for multiple users to view. It's print format must not change regardless of the user or PC. I have figured out how to lock down the PT from a visual standpoint when on a PC. However, when people try to print the pivot, they get different results. How do I lock down the printing options using VBA? Is there a better way than using VBA?[/img]


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