Clear data from pivot table

Hello all.

Usually when building a pivot table report I throw the data sets in
the pivot table a few times to find the best way to show data.
So I wonder - is there a way to quickly erase all the data sets so the
pivot table is again empty as just being created (so I do not have to
select each data set and remove it?
Macro or some other trick?

The closest I got to the answer was the idea to just create another
pivot table from the original data=85but it would be so much easier to
click a button and all the data is cleared from the pivot table.

Thank you as always people!
0
klemen25 (61)
6/18/2008 12:25:15 PM
excel 39879 articles. 2 followers. Follow

2 Replies
936 Views

Similar Articles

[PageSpeed] 31

In Excel 2007 there's a Clear command on the PivotTable Options tab of 
the Ribbon.
You could use a macro to clear the pivot table, in Excel 2003 or 
earlier. This examples clears the pivot table for the active cell.
'====================
Sub ActiveCellClearPivot()

On Error Resume Next

Dim pt As PivotTable
Dim pf As PivotField

Set pt = ActiveCell.PivotTable
For Each pf In pt.VisibleFields
       pf.Orientation = xlHidden
Next pf
'pt.RefreshTable

End Sub

'========================

Klemen25 wrote:
> Hello all.
> 
> Usually when building a pivot table report I throw the data sets in
> the pivot table a few times to find the best way to show data.
> So I wonder - is there a way to quickly erase all the data sets so the
> pivot table is again empty as just being created (so I do not have to
> select each data set and remove it?
> Macro or some other trick?
> 
> The closest I got to the answer was the idea to just create another
> pivot table from the original data�but it would be so much easier to
> click a button and all the data is cleared from the pivot table.
> 
> Thank you as always people!


-- 
Debra Dalgleish
Contextures
www.contextures.com/tiptech.html
Blog: http://blog.contextures.com

0
dsd1 (5911)
6/18/2008 1:55:01 PM
Great!
Works like a charm.
I had in mind to mention that I use Excel 2003 but it slipped my mind.

Thank you!
0
klemen25 (61)
6/18/2008 2:03:53 PM
Reply:

Similar Artilces:

How do I INSERT data into an excell spread sheet ODBC?
I am trying to use the ODBC excell driver and trying to insert data into my spread sheet as if it was a data base. How do I do this and are there any publications of the functionality of the ODBC driver used to access EXCEL FILES? If you go to the Data menu, Get External Data, New Database Query, th wizard will start to walk you through the rest of it. You will see list of databases to choose from and once you select your database yo will be presented witht he list of tables. Click on a table and yo will see the list of fields available in that table -- Message posted from http://www.E...

NullReferenceException after Data Migration.
Hi, I already posted this question here but not solve my problem. After a simple test migration some pages of WEB GUI show this error: NullReferenceException: Object reference not set to an instance of an object. I migrate one appointment and one activityparty with participation mask of organizer for a user. After the migration I can see the appointment on CRM in the activity list, the calendar day view, the calendar month view, but in the calendar week view I get this error: [NullReferenceException: Object reference not set to an instance of an object.] Microsoft.Crm.CalendarUtil.BuildCo...

Can't clear print queue
How do I clear the print queue? I've tried "net stop spooler" and deleted all in /WINDOWS/System32/spool/PRINTERS, to no avail. The OS is XP Home SP3. The printer is HP psc 1200. "Herzl Regev" <HerzlRegev@discussions.microsoft.com> wrote in message news:03C416F4-BE1D-4CE5-A865-4F5D0FD3EC57@microsoft.com... > How do I clear the print queue? > I've tried "net stop spooler" and deleted all in > /WINDOWS/System32/spool/PRINTERS, to no avail. > > The OS is XP Home SP3. The printer is HP psc 1200. What happened when you...

Help
I've pasted tables from an old document into another document. The tables now have extra boxes around the text. It looks like a table in a table, but I can't get rid of the extra boxes. Does anyone have any ideas? Kare_M, what type of document have you pasted into? It is possible to do exactly what you think has happened in some applications: paste a table into a cell in another table. Most likely candidate would be pasting into a table in a Word document. "Kare_M" wrote: > I've pasted tables from an old document into another document. The tables > no...

Data Labels on Filtered Data
I am trying to put Data Labels on a Stock Chart, using a different data source for the chart and the labels (in adjacent columns) I have tried various add-ins and trials (Jwalk chart tools and power pack, XY labeler) I have also searched high and low for an answer but all roads seem to lead here.... The problem is that when I change the data source using the filters the data source changes ok but the data labels go skew-wiff. is there a solution for this? or am I missing something obvious? Any help would be appreciated. Thanks Dave Excel 2003 Hi, This looks like a bug in the c...

How would I fill blank cells with the data from a previous cell?
Need to fill blank cells in a column with the data from the previous non-blank cell in Excel 2000. Used to do this in Lotus. Can't seem to get it in Excel try edit>fill>series (select one) Lotus's is simpler. >-----Original Message----- >Need to fill blank cells in a column with the data from the previous >non-blank cell in Excel 2000. Used to do this in Lotus. Can't seem to get it >in Excel > > >. > One way, assuming you mean you have data blank data blank or something like that and you want the cell with data to fill in the blanks below ...

Hidden source data
Hi. I wonder.... Why does my graphs go blank when I hide the cells containing the source data? Is there a way to omit this annoying occurrence? -- Hilsen �yvind Granberg Hi, when you go Tools|Options|Charts is there a tick in the "Plot visible cells only" box? If there is then deselect that option. Ken Johnson Thank you, Ken! That did it! I fumbled around a little until I found out that I had to mark an graph prior to follow this procedure. I'd say my spreadsheets looking much nicer when a lot of number collecting cells are hidden -- Hilsen �yvind Granbe...

Clear
Due to a malformed e-mail message or other malformed item in the information store, the Microsoft Exchange Information Store service may crash periodically. It happens when a staff try to move his mail from exchange mail box to his personal folders. Now I have to restart the services - Microsoft Exchange Information Store every 1 min. I attempt to clean his mail box (not delete) in the exchange server such that to fix this problem. Can you perform an "online backup" of the information store? If this is successful, then the problem is probably related to the structure of ...

Re: Where should be the best place to stored Applcation Data Files?
"Giovanni Dicanio" <gdicanio@_NOSPAM_email_DOT_it> ha scritto nel messaggio news:... > BTW: this post by John Robbins is very interesting reading: > http://www.wintellect.com/CS/blogs/jrobbins/default.aspx copy-and-pasto - the correct link is this: http://www.wintellect.com/CS/blogs/jrobbins/archive/2008/07/10/in-defense-of-vista-and-the-challenges-facing-windows-7.aspx Giovanni And then there's this Vista security discussion from last Friday (cool date, 8/8/8): http://it.slashdot.org/article.pl?sid=08/08/08/1155208 -GB On Fri, 8 Aug 2008 18:50:23 +0200, ...

clear the list og "open other users folder"
Hi When you use the open an other user folder, it could be a calendar, the persons name and folder is put into a list under the menu File/Open. I would like to clear that numbered list of folders, does anyone know how to do that? thanks, Rikke ...

Why won't data hide?
Why does hidden data still appear when a chart is generated from a pivot table? How can I make a series disappear when I hide the column in the pivot chart? Hi, If you hide the column manually it will still be displayed in the chart. Because the chart is liked to the pivot table. If you hide the column via the field filter the information should disappear from the chart. Cheers Andy Margie wrote: > Why does hidden data still appear when a chart is generated from a pivot > table? How can I make a series disappear when I hide the column in the pivot > chart? ...

locking data #3
I have 2 column of info. My first column has 100 different items and my second column has numbers 1 through 100 in sequence. I want to concatenate the 2 columns and not have the second column of numbers show in the concatenated column. I formatted the second column's numbers to be white, to match the cells white backgroung. When I concatenate the columns the resulting column has the numbers in black again, I need for them to stay white any ideas Thank you in advance Excel doesn't support that kind of formatting with formulas. If you converted to values, you could format eac...

Query from RMS data
I am trying to create a Pivot table in Excel, using Microsoft Query linking to RMS SQL database. SQL stores the date as Date/Time. It becomes impossible to group in Excel using Pivot Table, by date/time. I want to create a record in Microsoft query using the date field, but convert to Date only as dd/mm/yyyy and retain the format as Date and not as Text. I have tried Left(Date,11) and Convert(VARCHAR,1,101), none of them work. Could someone help me with the correct syntax. Microsoft query does not recognize MID. Thanks I've had a similar problem. The solution I used was to convert ...

How to link cell for value in another cell of pivot table?
In Excel 2003, when I link a cell in pivot table, it copies Getpivotdata formular. And when I drag or copy this cell, again it copies the formular. Can you help how to just get the value, then be able to drag to link next cell and its value? thanks. There are instructions here for adding the Generate GetPivotData button to a toolbar, and toggling the feature on and off: http://www.contextures.com/xlPivot06.html JamesChoi wrote: > In Excel 2003, when I link a cell in pivot table, it copies Getpivotdata > formular. > And when I drag or copy this cell, again it copies th...

Sorting Data..HELP!
I am trying to sort my speadsheet by the date. When I sort it only moves the information in the colum with the sort information instead of the whole row. How do I get the rows to move with the sorted information? Thank you so much! <jill@kristendistributing.com> wrote in message news:1125416164.646580.88570@g44g2000cwa.googlegroups.com... >I am trying to sort my speadsheet by the date. When I sort it only > moves the information in the colum with the sort information instead of > the whole row. How do I get the rows to move with the sorted > information? > > Thank ...

Using Excel data to create mail merge/report
I have an excel data file which has 4 columns - employee number, employee name, orgcode, and # per org I have entries for 1 to 14 employees in each orgcode, and the first entry has the total for that org in column D "# per org code", but the rest of the entries in that org code don't have an entry in the 4th column If it were a simple merge I would know what to do, but what I need ideally is to be able to merge into a letter the information for each org code - or at least to print out a page which lists an org code and the names in that orgcode Is this possible to do this? I&#...

Pivot Table help! #2
I'm hoping someone can give some pointers to a pivot table newbie. I have a bunch of data in three colums: Player, Score, and Date. I want a pivot table that will show me the average Score for every Player, given an arbitrary Date range that I want to be able to specify. I'm partway there. I set up a pivot table, pull Player onto the Row Fields, and I put Score into the Data area. If I put Date into the Row Fields, and then group these by month, it almost gives me what I want -- I can click on "Date" on the pivot table and I have little boxes that I can check off if I...

print envelopes from excel data
I have an excel database of addresses and I want to print off envelopes with the addresses I have listed in this document. I have read about linking excel with mail merge, but is there a faster method? More often I want to be able to print off envelopes 2 or 3 at a time, so to use mail merge seems time-consuming. Is there a way through formulas to somehow transfer for example one row of data into word and it generate into an address format. Or even better is there an envelope printing function in excel, as in word? If each row of your database occupies columns A to K; design your envelo...

Clear data from pivot table
Hello all. Usually when building a pivot table report I throw the data sets in the pivot table a few times to find the best way to show data. So I wonder - is there a way to quickly erase all the data sets so the pivot table is again empty as just being created (so I do not have to select each data set and remove it? Macro or some other trick? The closest I got to the answer was the idea to just create another pivot table from the original data=85but it would be so much easier to click a button and all the data is cleared from the pivot table. Thank you as always people! In Excel 2007 ther...

Use Form button to move current record to another table
I have a database which consists of computer names and model numbers. As time goes on the computers get old and are no longer in use. I also have an identical table that I have been copying the old computers to. Once copied to the old computer database I delete the computer record from the current computer database. Is it possible to accomplish this task with one button? Say you had a button on each record called cmdArchive, and it's purpose is to move this record to an archive table, and delete that same entry from your current table. Each record should have a unique identifying ...

Import data from Excel to Outlook
I have been trying to import contacts from an excel spreadsheet to Outlook and my attemps have been unsuccessful. I really would appreciate any help or tips on how to manage this operation. thanking you in advance. -- Richard K Mahan Sr "Imagination is the Father of Creativity" Richard With your Contacts info in columns in Excel.................. Name.........Address..........emaoil.........whatever Save the workbook after giving your list a defined name through Insert>Name>Define. Outlook likes that. With Outlook open go to File>Import Export>"Import...

help with dynamic tables
This is a bit complicated to explain but I'll try my best. In columns A, B, C I have different drop down lists. Column A has Store1, Store2, Store3, etc. Column B has Dept1, Dept2, Dep3, etc. Column C has ProductA, ProductB, ProductC. As of right now, these lists are not dependent on each other, I can choose anything from any list regardless of the previous category. Also, the length of these lists is undefined, meaning I will constantly be adding to them in sequential rows below. And then columns D and beyond have data such as Sales, Profits, # of items, etc. What I...

Problems opening spreadsheets after importing data
Hi, Thanks in advance for any help you can give me. I have a rather annoying problem with excel. After I import text data into Excel (whether via a macro, or doing it manually from the menu), I can no longer open a new excel workbook by double-clicking it from the folder view. Even right clicking on the file, and selecting Open does nothing, Excel simply goes back to the previously active workbook and does nothing. Here's what I've tried so far: The file > open command works ok. Closing the worksheet, (but keeping Excel open), fails Closing and re-opening Excel, works ok. Changing o...

automatic tranfer of data from worksheet to time sheets
i use excell to both keep reports and i would like to have data such as time/dates from the reports transfered to a separtate worksheet to track time spent on different projects ...

Help with using data
Hi I would really appreciate some help with extracting data from Outlook. I have been building up a database of clients/prospectives, these are in various categories. I would like to extract/search on various fields as well as my own categories, all i can do with export is extract the whole lot to a tab delimited file. Or export categories to an rge file which I can't use. Any ideas out there? thanks Outlook's Search doesn't suffice? There are 3rd party search applications that might help. What sprcifically stops you from exporting to a format other than TSV? There are other...