Free Microsoft Excel 2013 Quick Reference

Omitt the grid lines when working with Pivot tables

I created a pivot table which is pulling data from an external data source. Prior to creating the pivot table, I omitted the excel grid lines by using the "fill color" white on the entire sheet so it appears as a clean all-white sheet. However, once the pivot table fields are placed in the cells and are manipulated by either dragged down and pulled up to analyze, the grid lines re-appear evertime the fields are moved. Anyway to keep the entire sheet with a pivot table not showing the grid lines ? Any help is appreciated.

Thanks


Hi, I can't seem to remove the grid lines from inside my pivot table. When I go to View > Grid lines and uncheck the box it just removes the grid lines OUTSIDE of the pivot table for some reason.

How can I remove the grid lines inside the pivot table?

Thanks!

In the past 2 weeks, a co-worker and I have each run into a problem when working with Pivot Tables in excel 2003. We both use pivot tables every week, and we both never ran into this problem before.

The problem:
When running pivot tables from a data tab, the pivot table doesn't recognize certain numbers if the format is different. We've tried copying & pasting the data (paste values, paste formatting), and we've tried formatting all of the data so it's the same, but there are only two fixes that we've found that actually work: 1) retyping the data (not a good solution when the data tab has thousands of rows) and 2) copying the entire column, pasting as unformatted text into word, and then pasting back into excel. While the second solution works, it is obviously not ideal. The bigger problem is that you don't even know that your pivot table isn't returning correct data unless you are keen enough to double check it.

Has anyone else run into this problem recently? Is there a patch that can fix it?

Hello Everyone,

I am working with VBA to create a pivot table, and have done just fine so far. However, I need to create a two buttons that will run the following macros:
1. A macro that will remove the selected header (either row or column) from the pivot table.

2. A macro that will put the removed header back into the pivot table.

I need to be able to click on the header (whether its the row or column header) and then press the button to remove it from the table. The second button should then add that header back into the table. My code for creating the table is fine, I just need to work out the buttons. Below is what I have so far. I was think that if I had variable for the header name it would be able to tell which header to remove (so I used Set iField = ActiveCell.Value), I also tried ActiveCell.Text. All I need to do is put the text in the selected field into the PivotFields range to make it hidden. However, I keep getting an error (Compile Error: Object Required) on the line Set iField = ActiveCell.Value. Below is my current code. Any help would be much appreciated.


	VB:
	
 PivotTable() 
     
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _ 
    "'Pivot Table Data'!R1C1:R1892C7").CreatePivotTable TableDestination:="", _ 
    TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10 
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1) 
    ActiveSheet.Cells(3, 1).Select 
    ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Product", _ 
    ColumnFields:="Location" 
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Sales").Orientation = _ 
    xlDataField 
    ActiveWorkbook.ShowPivotTableFieldList = False 
End Sub 
 
____________________________________________ 
 
Public Sub RemoveHeader() 
    Dim iField As String 
    Dim PT As PivotTable 
     
    Set iField = AcvtiveCell.Value 
     
    PT.PivotFields("iField").Orientation = xlHidden 
     
End Sub 
___________________________________________ 
 
Public Sub AddHeader() 
    Dim iField As Integer 
    Dim PT As PivotTable 
     
    With PT.PivotFields("iField") 
        If iField = "Location" Then 
            .Orientation = xlColumnField 
        Else 
            .Orientation = xlRowField 
        End If 
         
    End With 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


When I type in a particular cell, the grid line disappear. This happens for
all the cell in this column.

How to change the default width of line when deal with chart?

Hi,

I have data that I have pulled out of an external source which includes dates on which sales are made in a particular month. I want to convert that date to show the month and year only for pivot table purposes, for if I access the raw data number and format it, when I pivot it, it will show the actual date rather than the month and year.

As an example, this is how I currently need to work around it. The data set has in one column a date of 15/08/2010. I want the data in the pivot table to show only Aug 2010 when I reference this column. If I format the actual data column with 15/08/2010 with mm-yyyy it does not work when it is extracted into the Pivot and still shows the original date of 15/08/2010.

To work around this at the moment I have to do a separate 'month of year' reference on another sheet, to get in this case '8' for August, & also split out '2010' for year, then set up another column which will convert '8' into the month of August and will combine with '2010' to show 'Aug 2010' in a separate column referencing the line of data. I then reference this new column in my pivot to get the required 'Aug 2010' to show.

This is a long winded way of converting the date and was hoping there might be an easier way to do it so that I don't need to go through these multiple conversions to get the right format to show in the pivot table?

Thanks in advance for your help

Regards

Bill

Excel 2003 does not allow me to work from a pivot table in the same way Excel
2000 did. When pointing and clicking to a cell in a pivot table, the formula
is returned as "get pivot data" and actually goes to the pivot table data,
rather than the cell that I have clicked. I am no longer able to copy and
paste this. I would like to disable this function and return to the
functionally that I had in version 2000.

(Excel
How can I fill labels in a chart, so that the grid lines of that chart do
not interfere with the text in those labels?

With Excel 2007 if I have a pivot chart set up using an olap data source with
4 Values (measures) which are the row values and each being graphed as a
separate line in a line graph - lets just say they are A, B, C, D.

If those items are listed in the Values box on the bottom right of the pivot
table fields list window as C, A, B, D when I hover over the legend for any
of those in the actual graph it will display the wrong one on the hover over.
If I hover over the first item in the legend "A" it will show me the hover
over details for the first item in the values box "C".

Not only that , but even worse it was displaying incorrect values in the
graph. If I resorted the values inside the "Values" box in the bottom right
of the Pivottable field list window it seemed to correct them, but it all
seems to be acting up.

I have a screen shot I could take of this if it will capture the hover over
and I could try to recreate the wrong values.

Has anyone seen this before? I have extensive experience with pivot
tables/olap and have never seen anything like this in prior versions.

Is there a problem with using macros with pivot tables? I tried to create a pivot table using macros, but for the data part when I used "Average of Sales", it came up as "Sum of Sales" when I ran the macro.

Another issue is that my data could change from time to time in terms of the number of records in the sheet. I thought I could take care of that my using CTRL+A. However, this does not seem to be working. It is only reading the original number of records that I used in creating the macro.

Please help

I don't want to see the grid lines when I open up an excel worksheet. How do
I make the lines disappear, as in the templates?
Thanks.

I am looking for a possible solution or bump in the right direction
when working with excel on the web. Currently I am using and developing
in visual studio 2005. Here is the situation I have a lot of
spreadsheets with macros that I would like to deliver to people via a
web application.... Some problems that I am running into are:

1) macros do not work when embedding the excel sheet in the browser.
2) Trying to figure out charting and graphing..

Sorry I am still a bit new to all this and am looking for some
advice....

Thanks

Excel 2003 does not allow me to work from a pivot table in the same way Excel
2000 did. When pointing and clicking to a cell in a pivot table, the formula
is returned as "get pivot data" and actually goes to the pivot table data,
rather than the cell that I have clicked. I am no longer able to copy and
paste this. I would like to disable this function and return to the
functionally that I had in version 2000.

Hi,

I work with pivot tables and one of my pivot table added "2" at the end of a row field name. I checked all the data source and I can see nowhere the field ending with 2.

i.e., In my data coloumn "Group" I have entries tagged with "cash". When I look at my pivot table under group, I can't find "cash" but it is listed as "cash2".

Anyone know how to correct this? I tried removing the field and entering it again but did not solve the problem.

Thanks,

Please tell me why the following two scenarios show different results:

Scenario One:
When working with just one worksheet:
In cell A1 I have a completely blank cell with no value. In cell A2, I type
"=A1" (without quotes). The result shows in A2, naturally, another blank
cell.

Scenario Two:
When I have two worksheets Wrksht1 and Wrksht2:
In Wrksht1 in cell A1 I have a completely blank cell. Now I go over to
Wrksht2 to cell A1 and type "=Wrksht1!A1" (without quotes). It shows the
value 0.

Why is the result in A2 of Wrksht2 not showing a completely blank cell like
in scenario one? Why does it change to a zero?

i only want to lock (protect) certain cells when using a pivot table - eg: when working in or with a pivot table the person / staff member can fill in date then tab fill in the next data required - tab but do not want then to change / modify data due to the next two columns or what ever having formulas in them
thank you

Reply With Quote

Hey everyone-

When I use Fill Color to highlight sections of a spreadsheet, it washes out my grid lines. Is there a way to preserve the grid lines without having to manually go back and add borders?

I have several large spreadsheets that have some linked formulas between
them. Sometimes, if a row gets added on one spreadsheet, the other
spreadsheet with a linking formula now pulls from one line above where it
should (it's not recognizing that a row was added). Do I ALWAYS need to open
all the linked files when working with them? I click to update when I open
the file. I'm working in Excel 2002. Please help! - I'm sick of re-doing my
linked formulas. Thanks.

(Excel
How can I fill labels in a chart, so that the grid lines of that chart do
not interfere with the text in those labels?

Hello,

I have the following code to build a pivot table:

(...)
Set WSPT = Worksheets("Pivot for Expenses")
Set pt = WSPT.PivotTables("PivotTable1")
pt.RefreshTable
pt.AddFields RowFields:="Expense Owner", ColumnFields:=Array("Year Plan", "Quarter Plan"),
PageFields:=Array("Expense Group", "Budget", "Lab")
pt.AddDataField Worksheets("Pivot for Expenses").PivotTables("PivotTable1").PivotFields("Amount
Plan"), "Sum of Amount Plan", xlSum
(...)
But I get a 1004 Run-time Error (Application-defined or Object-defined error) in the AddData field line.

I need to include two data fields, but I can't make it work to add even one.

I have found the following code everywhere:

With ActiveSheet.PivotTables("PivotTable1")
        .AddDataField ActiveSheet.PivotTables( _
            "PivotTable1").PivotFields("Score"), "Total Score"
End With
But I need that every time the code executes, the pt rebuilds from scratch.

I would much appreciate your assistance.

I am trying to work with a list of data and working out the number of items within that data which tally to set criteria.

If I have a list of 10 items which value to 100,000 and I need to look at 80% of this (80,000) and then find the number of items which add up to this value in that range.

I am trying to use pivot tables as I have multiple result criteria.

Any help gratefully received.

Yeah, sorry this isn't really a joke. But you still might smile, chuckle or whatever.

--I have re-read over Dave's information about spreadsheet designs so many times. And, I admit, it was like trying to understand Worf in his native language in Star Trek. I know some people did learn the basics of that language, but I couldn't. It was the same way with Pivot tables. I did use it, but in a very small way. I had seen a large number of responses talking about it. It has always been stuck in another language to me.

--Then I will say that somehow Norie spoke my language, it took me a minute, cuz I understood it, but couldn't realize how. This particular thread from two years ago, is what opened my eyes or woke me up, whichever I was in. I do know that I have seen many, many things that kept saying do this and do that. But it never sunk in.

--I have been messing with Excel for a few years, but could never get the hang of it, all I was doing was piecing pieces together and somehow I look like the guru in my company for Excel. I do know what my goal is for the Excel projects, and I can reach them. But I always had felt that I was like 15 years behind everyone. Now it feels like I am about 7 years behind, which is a relief.

I applaud and thank everyone here for doing what they do, it is a wonderful community and I am happy to have found this when I did.

geez, I'm going to end this now before I get carried away. LOL

Hi,

This is probably a very simple fix but I have just started working with Pivot Tables and I had a search of this forum but couldn't find anyone with the same problem.

I have data for dates in say April/May/June of 2007 as well as April/May/June of 2008.

I can group into years/months/days fine.

But problem is, when I expand April 2007 to show the full list of data for that month, it also expands April 2008. All other months stay collapsed.

Is there any way to get it to only expand April 2007?
Am using Excel 2007.

Thanks.

I have a seen this happen 2 times in the past. I work with pivot tables all the time and this one is stumping me.

Has anyone ever had a pivot table showing incorrect data? I am not excluding any fields in the page by or unchecking them any where. I have 2 different tables in which the data range is the same as well so I am pulling all the same rows.

Example:

I have a pivot table that sums sales by region. When I look at table 1 it shows $10,000 for region 217. When I look at a different table is shows $12,000. In this example I know the $10,000 is correct.

When I click on region 217 to see what the details are to look for any differences the new worksheet pops up and the region showing is now 125 and giving me the details for region 125 even though it is clearly 217 in the table.

Is there some kind of cache or command to clear out something that may be causing this?