Free Microsoft Excel 2013 Quick Reference

Pivot Table/Report in Shared workbooks

I used a shared workbook for an order entry file located on a server, but
then generate sales reports using a series of defined Pivot Tables. I just
discovered that shared workbooks won't let you use PT's? Is there a way
around that?
What if I unshare the entry workbook, in which the order posting is done.
Can I then access the same file, in order to do the reports? Can the Pivot
tables be posted automatically in that case? Will I be still be able to
manipulate the Reports?

Post your answer or comment

comments powered by Disqus
When I run the macro below in an excel 2010 shared workbook I get run-time error '1004' indicating i can't create or make changes to pivot table reports in shared workbook. my other macros work fine, so any ideas how i can edit this macro to get it working properly?

Sub AddCO()
' AddCO Macro

    Selection.EntireRow.Hidden = False
    Selection.Insert Shift:=xlDown
    Selection.EntireRow.Hidden = True
End Sub

I have one example pivot table report. In the source worksheet, there is
columns named as Account and Department.

When used this data(worksheet) as pivot table source data, on pivot table
feilds list there are two feilds created named as Account2 and Department2. I
want to know how I can create and use the feilds in pivot table report those
have no columns in source data.


In this pivot table report, I would like to show the data with standardized column format such as:

Reporting Quarter Reporting Period Site CCM targets total encounters.

However, in the pivot table report in the attached file, I couldn't get the "total encounters" column to display actual total number of encounters because it is using the same CCM target filter, which is set to = "yes".

That is why CCM targets column show exactly the same value as the total encounters column, which is NOT desirable.

So my question it possible to do what I need to do using pivot table? or is there any other way to do it that I'm not aware of?

Could anybody point me in the right direction please?

Thank you

Hi, I've got multiple pivot tables reports in an excel workbook that all
share the same data source, which is a microsoft access query. I now want to
add a couple of extra fields to the report ... so, i can add this to the
microsoft access query, but it doesn't show up in the pivot table report
automatically when I refresh it. So I open up the pivot table wizard on the
first report and edit the data source to include the extra data fields.
However it's only updated on the report i edited and not any of the other
ones that share this data source. If i go into each of the other reports and
repeat the process, the workbook quadruples in size. Surely there must be an
easier way to update all the reports without having to redo the entire
workbook from scratch.

I have a workbook that is shared by a number of users and it contains a Pivot Table.
Usually you can right click on the table and manually refresh (as it doesn't update dynamically as the data set changes.....or does it?)
Can anyone help with either:
a) getting the pivot table to update automatically when the data set changes
b) being able to manually refresh the pivot table in shared mode


I'm running into an issue with my pivot table. This is a shared workbook for
a few people to enter data. I have 4 different sheets with pivot tables that
refer to other sheets in the workbook. I have the refresh on open selected,
but after I share this, nothing will refresh and havent figured out how to do
this or if possible? Thanks!

I have a pivot table report that includes a series of ratings out of 10.

These ratings are listed against the staff member for each job they do.
Ie Bob Job-1 9.6, Bob Job-2 9.4, Bob Job-3 9.2 and so on.
Currently i have the chart showing all the ratings by staff member which is in a simple column chart.
Is it possible to include an average of the ratings on that same chart preferably as a line or as something to show its the average?

I have a database with information about the courses that were taught by different instructors, as well as the dates and costs of the courses. I created a Pivot Table Report based on that data and am trying to use the IF Function in the report.

I have the Instructor and Course name fields in the row area of the Pivot Table and the Cost in the data area. Using the If function I want to offer a 3% commission to the Instructors who taught Access.

I have created a calculated field but the result are zeroes for everything.

My formula is:


I am not sure if I get an incorrect answer because I am using the data from within a field as my logical test (Word, Excel and Access are the courses in the Course column).

Please help. Thanks!

I am unable to use pivot tables in a spreadsheet that was tagged 'shared'?
Is there a reason or a way to get around this?

Existing Excel 2003 macro creates a xls with pivot tables from a .csv file.
This works in 2003. When macro run from Excel 2007 the pivot tables diplay
an message "this pivot table report is not valid" when a 2003 user opens the
xls file and clicks on any of the list boxes. The macro code has 'version10'
for the pivot table coding and I have used the -4143 in code when saving file
in an attempt to save in the 97-2003 format and have attempted to have a Save
As dialog box appear so I can specify the .xls format when saving from 2007
but error still happens. I don't know if the file is being saved in the
97-2003 compatible mode or how to get the pivot table to work in 2003.

I have the code below which refresh's all pivot tables found in a workbook however I would ideally like to edit this so the macro will open all worksbooks found in a set folder, then run the 'refresh pivot tables' code.


Dim PT As PivotTable
Dim WS As Worksheet

For Each WS In ThisWorkbook.Worksheets
For Each PT In WS.PivotTables

Next PT
Next WS

ActiveWorkbook.Close SaveChanges:=True

End Sub

I am having trouble figuring out how to incorporate both these into one macro (if possible)

So the outcome of the macro would be, open 1st workbook, update pivot tables, save & close. Open 2nd workbook, refresh pivot tables, save & close etc... until all works have been opened/refreshed.

Does anyone know a way to do this?


I have a few pivot tables on a shared workbook. I have it set to refresh
when the workbook is open, which that works until I Share the workbook.
After its shared, it will no longer update/refresh. Is there a way to do
this or possible? Thanks!

Hello. I have an excel spreadsheet that I use to create pivot table reports. Once the reports are refreshed they are copied to a new workbook to send to each individual salesperson to evaluate. The problem I am having is the drop down menus that are originally in the reports before copying to a new work book are lost once they are moved to a new workbook. I understand that this is because the cell reference is in the original workbook but is there a way to have these drop downs stay in the work sheet?

I have a sheet that records registration data. It is supported by different
Pivot Table reports. The data in the main sheet changes twice a year and I
need to find a way to clear the references that the reports use (as in 'show
all', 'blank', etc.). I can clear the data from the main sheet, but the
reports hold the data references. It is not affecting the reports
functioning, it is just creating a HUGE reference list.


We are using a pivot table to run a weekly payroll report. It is organized by a roster code with all of the individuals that fall within that last code (by last name). The pivot table report needs to be in chronological date order for each person. Here is a link to an example of the report:

Ideally, for each person, we would add information on the total number of hours in each Category for the week, so for Baker, that would be 35.25 Regular and 4.75 Sick.

Is there any way to add this kind of weekly subtotal by Category for each person to the pivot table while still keeping a similar (tabular) format to what we have above? Ideally it would be as automated as possible because we have the payroll data refreshing automatically from the database so that we can update the pivot tables.

Thanks so much in advance for your help!!!!!!!

Hello everyone, There is most likely a real easy answer to my road-block and looking to the 'experts' for advise.

1. CSV formatted data source file successfully created out of MS Access 2003 with approximately 200K records and uploaded to SharePoint 2007 directory.

2. Macro button created in MS 2003 Excel report contain master pivot table worksheet to successfully download/export this SharePoint CSV file to a customers hard drive (example: C:ReportsCSV_Filessourcefile.CSV) .... (the catch I need to address is) but this requires that each person run the odbcad32 or go to Control Panel -> double-click the ODBC Data Sources and set up a new data source for Microsoft Text Driver (*.txt,*.csv) ????

My goal is to have customers and users:
1. Download from SharePoint location ( Library/filename.xls) the Excel 2003 pivot table report to (C:Reportsfilename.xls)

2. Open the newly downloaded Excel 2003 pivot table report C:Reportsfilename.xls

2. Click on excel Macro button contained within the C:Reportsfilename.xls to download the SharePoint location CSV data source file Library/CSV_Files/sourcefile.CSV to C:ReportsCSV_Filessourcefile.CSV without requiring any additional steps (like creating new ODBC Data Sources for Microsoft Text Driver (*.txt,*.csv)) and making it transparent to users and customers. This is where I have no idea to go from.

As you know the simplier the better and there is no way for me to sustain setting up ODBC Data Sources on everyones computers.

Now I am a beginner so please take this into consideration when responding.

I really look forward to your assistance.

(by the way the code for successfully downloading from SharePoint to hard drive was provided by an expert user on this forum... big thanks!)

Thank you, Margie.


First post so please be gentle

I have created a pivot table report in excel that goes like this
Raw Data Sheet
Columns 22 - Mixture of text, dates,formulas and blanks
Rows will be anything up to 2000

Pivot Sheet
Row Fields 11 Mixture of Text, Dates and Numbers
Column Fields 0
Page Fields 0
Data Field 1 - Count Type

I created this in Excel 2003 and saved both as standard and 97-2003 Workbook.

When I run this in 2003 on any machine it works fine, when I run it on anything below 2003 it fails - haven't tried 2007, error I get is ...

"Excel cannot make this change because there are too many rows or column items" and that's followed up with "PivotTable Not Valid"

I've googled about and seen mentions of restrictions etc on pivot tables but have not been able to get this working.

Can anyone help ?


this is probably a simple question but I cannot figure it out. I want to
create a pivot table report with two data items (e.g., density and cover) in
a column field (date). any thoughts?


consider an examle of data and a pivot table like in the page of famous jon:, namely the most left
table in the third row.

I want to report all product that have sales above 8000. Other product i do
not want to see in the table.

I know how to report 3 best products but it is not i need.

Thank you


I have a spreadsheet that has some cells with no data in them.
Some will never have data, others will as the project progresses.
The pivot table report puts the word (blank) in the report for those cells.

The only solution I have found is to go to each empty cell in the source
data sheet and hit the space bar to force a character into each cell. This
does work but is extremely tedious ( and time consuming). Some other users
(my bosses) of the file add new rows, but are not willing to spend time
fussing around like this.

I tried these two suggested solutions, neither solve the real problem.

1) If I right click the cell and select hide the whole row disappears. I
want the row displayed.

2) In the formatting options menu 'FOR EMPTY CELLS SHOW - _____ " I
checked the box and put various characters in the box. I put the characters
inside quotes, not inside quotes, tried numbers, special characters, just a
blank, any combination I could think of - but the display never changed
from the word (blank) in the pivot table report.

Is there a practical way to change the way these blank cells display in the
Pivot Table.

Hi All,

Can anyone help me with writing a macro whe
From a Defined Range (e.g. Sheet1! B9-B20), for each cell value, I would
like a pivot table page on another worksheet to change and then print the
pivot table report. In other words, I would like to automatically print the
pivot table report for each of the pivot table's page contents.

David P

I would like to build a pivot table report that has a layout of
one field in the row region
multiple fields in the data region
each data field, reported side by side with appropriate column titles
the resulting report would could then look like:-
ROWTITLE SUM(Values) Count(Values)
A 100 3
B 200 5

Going a little bananas with trying to get a Pivot Table Report to accept the
order of the fields as they are in the Pivot Table. It's importing them in
ascending order by default, and I can't select Data Source Order from the
toolbar's Sort and Top 10/AutoSort options. The "manual" selection is the
default, but I don't want to have to drag every field around each time.
What's up?

In my pivot table report, I have two variables in the "data" field, TRIALS
and CONVERSIONS. The two variables are summarized by sums, which is what I
want. However, I would like the second variable, CONVERSIONS, to be expressed
as a % of TRIALS.

What I've tried already: In the Pivot Table Field for the CONVERSIONS
variable, I clicked on Options >>; I clicked on Show data as "% of". Base
field=TRIALS. Here's where the problem is (I think): I don't know what to
click on for Base Item; I tried clicking on both (previous) and (next), but I
got back a #N/A for the values in the pivot table.

How can I get it so that I can show CONVERSIONS as a percentage of TRIALS???

Thanks for the help

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