Free Microsoft Excel 2013 Quick Reference

Can you save a pivot table layout?

I have a report with 6 pivot tables on it from a workbook with 200 column headings to choose from. It takes me an hour just to select all the row and data fields and they are hard to read because the whole title doesn't fit in the field button.

Is it possible to save a "template" or something that I can just point to my workbook to populate the pivot charts?

This would save me about three hours every morning!

Thanks for any suggestions!!!

Post your answer or comment

comments powered by Disqus
I am cleaning Excel worksheets for input into another program (Recorder 6). I
have found a routine involving pivot tables which does the best job, however
I need to run the wizard for each new sheet. Is there a way of saving the
pivot table layout & then simply run it for each new workbook?

There are usually 100 columns of up to 10 rows (after a transition) involved
here, each of which apparently needs to be manually edited (from 'count of'
to 'Sum of' ) in order to work.

Cheers now, Rob


I routinely get reports in CSV format that I open with Excel. In order to analyze the data I create a new pivot table in the "new" workbook that is created by opening the CSV file. In the end, I have a workbook with two sheets, one with the CSV data and the other with the Pivot table.

I find it laborious creating the same pivot table each time I receive a new report. Is there a way I can save a pivot table and then instantiate it in each workbook as needed? I am basically looking for a more automated way to build/define the pivot on the new reports as I receive them. I need to keep separate workbooks for each report/pivot table.

Thank you in advance for your help.



can I get a pivot table to show only data with the date field during
working hours e.g. 9am-5pm mon-fri excluding holidays


Hi All,

Have succeeded in converting 40,000 lines of raw, useless data into a very tidy Pivot Table, but I have one more requirement!!!

Does anyone know a way I can set the account number (on the left) to be on its own line where I can use a V lookup to also add the account name.

At the moment I have what is in the pdf attached "Screenshot.pdf"

I would like to get to what is shown in "Book2.pdf"

Can I tell the pivot to place the heading at the top or will I have to record a macro to grab the data and manipulate it accordingly??

I am not so much interested in getting the account names right, I am just keen to be able to get excel to format the pivot table automatically without any need for a macro.,

Ideas anyone?



I have a program in which I can create pivot tables to get data. The problem
is that I want the data so I can merge it with some other data. Is there a
way to export pivot table data?


Can anyone tell me if you can link a pivot table chart from excel to
powerpoint while keeping all the drop down functionality of the pivot chart
or likewise I have some charts that I have attached control boxes to which
help the user select the data they wish to see and the chart updates with
whatever data selection they choose when I paste-special this into powerpoint
even though its liked the functionality of the control box goes.

When you move a pivot table graph to a new workbook the formatting will
change. How can I keep the exact formating of the graph?

I want to have interactivity on a published excel workbook. My
Excel sheet gets the data from a query and dumps the data in a sheet with
which I create a pivot table and pivot chart. I have saved this chart as a
html page and also included option to add interactivity but it gives me error
when I open html page as :
The Query could not be processed Error opening data file .. it gives the
path of .xml file in the html folder which is created when we save it as html
I am sure I am missing something as the query works absolutely fine normally
in any excel sheet ...
Any help would be appreciated.

Can anyone help me pls..
Is it possible to use a Pivot table to filter a list of data so that it shows the top ten for example.. there are ten columns:
Column 1: Report Number
2: Name
3: Date
4: Site
5: Department...etc

I would like to display the top ten offending people or sites and filter any relevant data that goes with it...

Is it possible to do this..??

Or quarters?

I'm trying to get a pivot that shows projections for 2007 and 2008 to display the data as quarters as well. At present, I've created Q1-Q4 as seperate columns on the source data, which works, but it's not precisely what I want.

Can you teach a pivot table to divide a year/12 to get an average month?


Here is my code:


Private Sub Worksheet_Change(ByVal target As Range)
Set isect = Application.Intersect(Range("b1"), target)
If isect Is Nothing Then
Exit Sub
Sheets(2).Range("a2:d" & Sheets(2).Range("a65536").End(xlUp).Row).ClearContents
Test = Range("b1").Value
l = 0
c = 0
For i = 1 To 10
For k = 1 To Test 'dilution
For j = 1 To 2
Sheets(2).Range("c2").Offset(l, 0) = k
Sheets(2).Range("c2").Offset(l, -1) = i
Sheets(2).Range("c2").Offset(l, -2) = l + 1
Sheets(2).Range("c2").Offset(l, 1) = j
l = l + 1
Next j
Next k
Next i
End If
Sheets(2).Range("A1") = "ID"
Sheets(2).Range("B1") = "DAY"
Sheets(2).Range("C1") = "NUMBER OF DILUTIONS"
Sheets(2).Range("D1") = "NUM OF REPEAT"
Sheets(2).Range("E1") = "RESULTS"
End Sub VBA tags courtesy of

With this code, when an user change the value of one cell
(position B1 on Sheet1), the second page called sheet 2 is automatically updated...four columns (from A to B) are filled and the operator
has just to enter the results in the column E

Now, I would like to create a pivot table with the following positions:

Row Field = Day
Column Field = Number of dilutions
Data items = Results

What can I do?

Thanks a lot


Hi all.

Does anybody of you know, how I can show in a Pivot table
accumulated values (example)in each row:

Day Amount Accum. Value
1 50 50
3 75 125
8 105 230
.. .. ..
.. .. ..

and so on.

Maybe someone give me a tip.

Thanx a lot

Regards Kurt

I have a workbook with 9 worksheets that I need to pull together for a
report. Can I use a pivot table for this? It would need to pull from all
those sheets and I don't know what the limits are.



I have a workbook with 3 sheets: Sheet1 has a drop down list of 57 departments, Sheet 2 has all of the company 360 employees with columns for their name, title, email, phone, etc. Can I use a pivot table on SHeet3 so that when I select a department on Sheet1 (OHR) all of the employees and their associated information will populate a table on Sheet3?
If not, how else can I do this?

My question is two part.

Part I:
Can you, in a pivot table, create "running averages"? I have a table where I am summing dollar amount data by officer, & by region, measured by month end date. If I pull in a second time the data field which I'm already summing and choose average, I get the average of the dollar amounts that make up the total. I want the totals averaged by month, both by officer and by region if possible. I had been doing this with a large table of sumproduct formulas except that if a new officer was added, their data would not flow into my table until I added a line for the new officer. Otherwise my table would be off by the amount of the unidentified officer's totals. Pivot tables automatically bring in all the data thereby eliminating my having to check for new officers each month. However, now I have to figure out a way to calculate monthly averages.....

Part II:
If Part I can't be done, then can I use the GETPIVOTDATA function to extract either column or row totals that are automatically inserted in a pivot table? I have played with it a little and thus far have not been able to get it to work.


It seems pivot table questions don't get a lot of response here on the board. I guess in part due to there not being as widely used as many other Excel features.....

Joe Dowski
Connecticut, USA.

I have been able to successfully link a pivot table in Excel to data in an Excel file sitting on a server. When I do that using the Pivot Table wizard in Excel, if see I can even choose which field to bring over to the pivot table and can filter content as well.

I would like to do this, but instead of putting the source data in Excel in a folder on a server, I would like to post the data on a Sharepoint site (the reason is simply that it’s convenient, and the site exists already). When I post a file to sharepoint, its address seems to be an http:// address, not simply a server name. I cannot figure out how to link to the files posted on sharepoint….when I try to set up a pivot table pointing to the data, Excel does not seem to accept the address in http:// form.

So, two questions:
• Can you link a pivot table to data on a server, using an http:// addressing method?
• If not, presumably the data is on a server in a folder somewhere……can you still use a regular server address instead of http:// to get to the file?

How can I delete a Pivot table?!

I have one on a sheet, though it has formulas and a couple of event macro's on the sheet, and I want to be able to delete the PT without deleting the whole sheet

Any ideas?!



I have a pivot table report which has stats for one person and I want to prevent them from seeing other people's stats. Their name is in the Page Field. Is there any way to lock the Pivot Table while still allowing the Pivot Table to update.

Your help is appreciated.


Hi all,

First time posting,
I'm working on a projet for work to determine how many of each component is needed and when to order it.

I've created a pivot table to summarize the data and I'm wondering how to use a vlookup or hlookup to find data associated with a component number. As you know a pivot table puts the component number (reference) in the upper left then 2 columns to the right is the first datum point. Then sequentially lists them below so they are 2 columns over and 1,2,3,4 and 5 rows below the reference cell (component number). I attempted using offsets in conjunction with v/h lookups but I had no success... Anyone know how to use these funcitons together or know of another work around?

Any help would be much appreciated.


I'm having a major issue saving pivot tables as Excel 97-2003. When I
insert calculated fields into a pivot table, and save the file, my pivot
tables turn into static tables!! When I used the previous version of Excel,
I had no problems with my calculated fields.

How can I save a pivot table in previous format, whilst using Excel 2007,
but still have the pivot table function?

Thanks for your help,

I have ten different pivot tables on one spreadsheet all based on the same
data. I made 11 copies of the spreadsheet to make one tab for each month of
the year. I then changed the month on each table on each tab to capture just
the month I wanted. Now when I click in table 1 on the January tab to refresh
it, it tries to refresh table 1 on every month's tab. Is there anyway around
this? I really don't want to recreate 10 pivots each for 12 months!


In my newest version of Excel I can't use a pivot table to sum totals of a
group but, in the old version I can. Does anyone know of how to turn this
feature back on? My pivot tables give me a count total of the items in the
group I don't need this I need the sum of these items.

A user has sent me an Excel document (349kb). It is a pivot table with drop down options at the top and and a second sheet, lots of data. When she tries to refresh the data she gets: “Excel cannot complete this task with available resources, choose less data or close other applications”.

If I open this in Excel 2003 (fully-updated, sp2 e.t.c), right-click anywhere on the sheet and select refresh it says “there are too many records to complete this operation. I think the refresh is supposed to show new totals or something like that.

I tried 2 machines at work, both running Excel 2002 (she also runs 2002, SP3) and when I attempt to do the same thing I get:

“Excel cannot complete this task with available resources, choose less data or close other applications”. It then asks if I want to continue with no undo option, but it just produces another error and gives up. At first I though it was a lack of memory, but the other machines I tried it on have plenty and all have the same issue.

Thing is, I emailed it to another person and she said she could refresh it! I can’t figure it out! I didn’t have time to check her machine, but I’m sure I’ll find no obvious difference with anything. It must be a setting in Excel, but I doubt I’ll find it anytime soon.

Any help with this one would be much appreciated. Thanks.

Hi Guys,

i'm trying to work out a way of subtracting items from one excel sheet onto another...

basically I have a SHIPPED SHEET, containing three fields,:


the other sheet is called SHORTFALL, what i'm trying to do is minus the figures from the shortfall sheet onto my SHIPPED SHEET, this way i'm left with a SHIPPED figure MINUS SHORTFALL (if this makes any sense)
once this is done i can then build a pivot table up and add all SKU's together to get rid of duplicates, i hope this makes sense...

kind regards scott

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