Remove Descriptions On Pivot Chart
Apr 9, 2008I have a chart which has two labels at the bottom under the x axis. Is there any way to remove these without removing the data?
View 9 Replies
ADVERTISEMENT
Pivot Table With Dynamic, Updatable Chart, But Not A Pivot Chart!
Dec 19, 2008My boss wants me to design a dynamic, updatable chart in Excel 2003. I initially made a Pivot Chart based on a Pivot Table which worked perfectly, but it doesn't look professional enough when printed (or viewed) and she wants me to approach it a different way.
So, I created a graph based on the data in a Pivot Table, and used dynamic ranges as the source for the graph series so that the chart updates when the criteria fields are changed for the Pivot Table. I then added two combo boxes (ie data validation lists) to the Chart sheet, and wrote VBA code so that whenever the combo box values are changed, the Criteria fields for the Pivot Table on the 2nd sheet are updated accordingly, and this in turn causes the graph to be updated as well.
This solution also worked perfectly, but now I've been told to create the graph without macros.
Does anyone have any suggestions? The requirements/details are as follows:
1. The Pivot Table is on sheet "PIVOT", and the graph is on sheet "GRAPH"
2. The Pivot Table has two criteria - School Name and Year Level
3. On sheet "GRAPH" there are two data-validated fields, School and Year, which only allow the selection of valid Schools and Year Levels
Is there any way to make the Pivot Table update when values are changed in the fields on the CHART sheet so that the chart also updates, but without using code nor a Pivot Chart?
Pivot Chart Object: Find Any Suitable Object To Choose From To Make A Pivot Chart In Powerpoint
Mar 21, 20071) i have office 2003 on a laptop. within powerpoint, i can create a 'microsoft excel chart 11' object. to create a link to the excel data source, do i have to go through the odbc sql setup? it works, but i don't want my powerpoint to be dependent on some excel file somewhere. what are the other options to insert/make a functional pivot chart in powerpoint with the data also within powerpoint? the data as sheet option does not result in the chart being a pivot, it's just a plain chart. it has to be a proper object, not an image paste or a chart that updates links with the excel file open.
2) i have office 2007 on my other laptop. i can not find any suitable object to choose from to make a pivot chart in powerpoint. what's the best way to go about in 2007 version?
3) am i going about this the wrong way with the objects? should i be after vba code?
Show Missing Values In Pivot Chart (line Chart) Axis?
Apr 18, 2013How can I add missing values to the axis in a pivot chart line chart? I have a numerical X-axis with values such as 0,1,5,8,14, etc. I have another set of Y values that correspond to the X values. If this was an XY-scatter plot I'd be able to plot X vs. Y and connect the dots for "gaps" in the X values. Since it's a pivot chart I cannot use an XY-scatter plot, I can only use a line chart. The line chart doesn't give me the ability to add the "missing" values, so it gives the impression that my data is more closely packed than it is. The data source is external to my spreadsheet, so I cannot add the values before creating the pivot table/chart.
View 1 Replies View RelatedPivot Chart Error: Unable To Set The _Default Property Of The Pivot Item Class
Aug 15, 2006I have created quite a nice little macro that;
drills through a lot of key figures, updates a pivot chart and copies the chart to powerpoint.
However it crashes on one particular data set every time with this error:
Run-time error '1004':
Unable to set the _Default property of the pivot item class
The code where the debugger stops is the last line below here.
lngKpi = Sheets("Helpfile"). Cells(lngRow, 2)
Sheets("Charts").Select
ActiveSheet.ChartObjects("DK").Activate
ActiveChart.PivotLayout.PivotTable.PivotFields("KPI # (overall").CurrentPage = lngKpi
I can manually change the pivot chart to the keyfigure it crashes on and thereby workaround the problem in the macro. Also it works for more than 50 other keyfigures without problems.
Convert Chart Into Pivot Chart
May 16, 2006Please look at the sample workbook below. The chart you see below is fine BUT I would like in a pivot table chart, so I could select any days on the pivot chart. I have tried it to do it myself but for some reason the time is not displaying on the pivot chart correctly. Any help please ?
P.S. If not possible then can we add a scroll bar or something?
Pivot Chart Changes Chart Upon Refresh
Aug 12, 2006I have created a pivot table and chart. The pivot chart consists of three sets of data from the pivot table. Two of the data sets are lines and the third data set is displayed as columns. I made the third data set into columns by right clicking on the data series line and selecting chart type. I change the chart type for that data set to a column and it worked great. The only problem is that when data is refreshed in the associated pivot table, the entire chart turns into columns with completely different formatting. Does anyone know how I can maintain the graph with the two lines and one column data set when I refresh the data?
View 4 Replies View RelatedSum Duplicate Descriptions When Using Array?
Dec 5, 2013I am currently using the below formula to extract various fields from a spreadsheet:-
{=IFERROR(INDEX(Funding_Stream_All_Data.rdl!$I$1:$I$999999,MATCH(1,(Funding_Stream_All_Data.rdl!$B$1:$B$999999=$C$8)*(Funding_Stream_All_Data.rdl!$D$1:$D$999999=$A19),0)),"0.00")}
Which would be fine if the description in column B of my data only appeared once.
How do I sum the total of all those that have the same description?
Data Example
Column A Column B Column C
Code Description Value
SL10107 Test Data £100.00
SL10107 Test Data £100.00
SL10107 Test Data £100.00
Using the formula {=IFERROR(INDEX(Column C,MATCH(1,(Column A=SL10107)*(Column B=Test Data0,0)),"0.00")} I get the answer £100, i.e. it is Matching the Code SL10107, and then matching the Description of Test Data and then returning the first value, £100. However I would like to sum these entries and get the answer of £300.
Summarize With Group Descriptions..
Aug 25, 2009I think I need a modified SUMIF formula but I can't figure it. The attached spreadsheet is a sample of what I need. I'm using Excel 2003.
View 3 Replies View RelatedCopy And Add The New Descriptions Into The Master
Sep 16, 2009I have a site that sells over 5000 products.
I have two sheets.
One is a Master which has all of the following information in each column.
Product ID = A, Product Name=B, Price=C, Category=D ,Product Description =E,
The other sheet Is a updated product description.
Now I want to Be able to copy and add the new descriptions into the Master but not sure how to add them so they will fall into the proper spot according to the Product ID... There are not descriptions in the Update sheet for all the Product IDs in the Master.
Macro To Extract Specific Descriptions?
Aug 28, 2013I have decriptions in Col B on sheet1. I would like a macro to do the following
1) Extract descriptions that contain the following text to sheet2 (including Cols A:B)
sale, sales, fact assist , discount, DIC & rebates, F & I (for eg sale pine desk, fact assist pine desk, discount retail desk, F & I sales)
2) Extract descriptions that contain the following text to sheet3 (including Cols A:B)
COS, O/allowance (for eg COS pine desk, O/allowance,
Removing Text From Item Descriptions On Mass?
Oct 28, 2013I have about 10,000 part numbers all with descriptions. These descriptions do not follow a uniform logic;
=============
Part 1: 5x10 Red Cotton candy
Part 2: Yellow 6/18 x7 TTC x11 Picture Frame
=============
My dilemma is that I need to figure out a way to remove everything aside from the "5x10" and "6/18 x7 TTC x11" from these descriptions. I just need the the item sizing.
What I've come up with so far is sorting items by description and working with batches of similar descriptions, then doing replace "Red*" with "blank". It works but it still takes me too much time and it's not perfect.
My question is there something else I can do that is easier and more accurate?
Remove Zero Value Data Labels From Pie Chart
Apr 21, 2008I have a pie chart that shows numerical values in dollars and I'm looking for a macro that will delete the $0 data labels. I found this code from Jon Peltier (great website by the way) but I can't seem to get it to work on my pie chart. No errors, just doesn't do anything:
Sub CleanUpActiveChartLabels()
Dim iPts As Integer
Dim nPts As Integer
Dim aVals As Variant
Dim srs As Series
ActiveSheet.ChartObjects("MainChart").Activate
For Each srs In ActiveChart.SeriesCollection
With srs
If .HasDataLabels Then
nPts = .Points.Count
aVals = .Values
For iPts = 1 To nPts
If aVals(iPts) = 0 Then
.Points(iPts).HasDataLabel = False
End If
Next
End If
End With
Next
End Sub
Inconsistent Text Lookup - Return Postcodes From A List Of Descriptions
May 21, 2013I'm trying to write a formula which will return postcodes from a list of descriptions which aren't consistent in their layout.
For example, I need this to happen
UB3 3NQ - APR13
SW3 5RQ - APR13
Jul 12 - apr13 accrual - ME9 4FW
Mar 12 - apr13 accural - SO14 7P2
Returned to another column as,
UB3 3NQ
SW3 5RQ
ME9 4FW
SO14 7P2
The issue I'm having is that the postcodes aren't in the same place in order to use LEFT, RIGHT or MID functions, and they aren't always proceeded or followed by dashes or spaces in the same way.
I need the returned postcodes to come back in a uniform way so that any duplicates are grouped by the relevant pivot table.
Remove Gaps For Missing Values In Column Chart?
May 21, 2014remove gaps for missing values in my column chart. I have tried to adjust series overlap and gap width, but the missing values are still showing as gaps. I have attached the sheet
View 1 Replies View RelatedAdd/Remove Custom Control To Chart Drop-Down Menu
Sep 1, 2007I want to add a control under Chart on the menu bar. This line of code errors with "Invalid Procedure Call or Argument"
With Application. CommandBars("Worksheet menu bar").Controls("Chart")
Change "Chart" to "File" or "Edit" or "Tools" and it's fine.
The Chart item only appears when a chart is selected, but it errors even when a chart on the worksheet is selected.
Remove Totals From Pivot Table
Jan 15, 2009How do I remove the totals and grand total from the pivot table? I only want to show the individual count.
View 3 Replies View RelatedRemove (blank) From Pivot Table
Aug 30, 2007I've attached a sample file.
Really need to remove the (blanks) in all my headings.
Download sample and let me know if you have any ideas.
IF Calculation: Calculating Costs Of Products Ordered Which Imports Quantity And Descriptions From Another Sheet
Feb 9, 2010I have a form for calculating costs of products ordered which imports quantity and descriptions from another sheet. The imported info and basic calculation of appropriate cost based on ordered quantity seems to work okay. These formulas are as follows:
First column: =IF(ORDER!A5>0, ORDER!A5, "") inserts the quantity ordered
Second Column: =IF(ORDER!A5>0, "Part Description and Part Number", "") inserts the product description
Both those formulas return a blank cell if nothing is ordered. Then using an outlying cell (G3) I enter the following formula to determine appropriate cost based on total order quantity e.g. $2.57ea if total ordered quantity is less than 50 and $2.37 if total quantity is greater than 50.. {Q: How do I put a third pricing level in here??}
=IF(SUM(A5:A27)>50,"$2.57","$2.37")................
Remove (blank) Rows From Pivot Table
Apr 15, 2014Pivot tables. I have a dataset from which I make a pivot table. In row labels I have 4 different columns of data below each other.
Level 1
Level 2
Level 3
Level 4
In some cases the data only has three levels, for example:
Earth
Europe
Germany
In other cases it has four levels of data:
Earth
Europe
Germany
Berlin
If I make a pivottable it will give 4 levels for all items. In the first case I mentioned, the data will state:
Earth
Europe
Germany
(blank)
Is it possible to remove the (blank) row as it does not provide any useful information..
Pivot Table - Remove Sum Of In Data Field?
May 26, 2006Is there a way to modify Pivot Table so that "Sum of, Count of", etc is not shown. For example, have Table (& Pivot Chart) show "West Region" instead of "Sum of West Region"
View 13 Replies View RelatedRemove Percentage Of Row Total In Pivot Table?
Feb 7, 2014how to remove the percentage of row total in a pivot table. I only want the sum of the row total but the percentage of row column always say 100% and I want it to be removed.
View 3 Replies View RelatedPivot Table - Remove Sum Of In Data Field
Apr 15, 2014VBA code to remove "sum of" from a pivot table. It works on a regular pivot table. However, when applying this code to a pivot table created utilizing PowerPivot, the code does not work. Here is the code:
Sub ChangePTName()
Dim pt As PivotTable, pf As PivotField, ws As Worksheet, i As Long
Application.ScreenUpdating = False
Set ws = ActiveSheet
For i = 1 To ws.PivotTables.Count
Set pt = ws.PivotTables(i)
pt.ManualUpdate = True
For Each pf In pt.DataFields
[Code]...
Excel 2007 :: Filter To Remove Value In Pivot
Apr 12, 2012I have a pivot in excel 2007 but I have the following problem; I want to exclude values from one variable that have a value lower than zero or blanks:
Month #AVG Employees
Jan 200
Feb 171
But the problem is, if i filter on the variable by placing it in the Report filter (select multiple items to remove blanks and -999), when i refresh the pivot next time for the new month it won't add the new values for the next month. How to have the filter so it applies the criteria but allowing new values in for the next month.
Remove Lines In Chart (object Doesn't Support Property Error)
Dec 12, 2011I'm trying to remove lines in my line charts. I searched the internet and find the following code:
Code:
Sub RemoveLines()
Dim ser As Series
For Each ser In ActiveChart.SeriesCollection
ser.Format.Line.Visible = False
Next ser
End Sub
However, when I run the code, I got "Object doesn't support this property or method". I did select the chart. I have excel 2003 with vba 6.5.
How To Create A Pivot Chart
Apr 7, 2014I am trying to create a pivot chart that show the average response times in hour but for some reason my charts looks not right. The axis show a max of 1 but the graph itself showed us up to 17. I think easier to explain if I attached the worksheet.
View 2 Replies View RelatedAdd Top Five Rank Without Using Pivot Chart?
Oct 13, 2011Within a pretty large key indicator report I would like to add a top five rank without using a pivot chart.
View 3 Replies View RelatedChart Without Pivot Table
Jan 3, 2013If I want to produce a pivot chart using VBA can I do it without producing the pivot table?
I am guessing that the following are a possibility:
Do in a virtual sheet that is not shownhide the sheet (can do already)
I am looking for the virtual sheet option or something similar.
'Top 10 Autoshow' In Pivot Chart
Feb 1, 2010Hi everyone - I'm just starting to get my head around the wonderful world of pivot tables and pivot charts, and I'm stuck ...
I've managed to build a pivot chart exactly how I need it to look ... except that there are 140 different data fields showing, and I only want the top 10 values to be displayed. The top 10 would then update depending on the Page Fields selected.
My searching for an answer keeps returning results that say "click Field Settings, then Advanced, then Top 10 Autoshow" etc but the Advanced option doesn't show in the associated Pivot Table - I only have 'OK', 'Cancel', 'Hide', 'Number' & 'Options'.
So is it possible to use Top 10 Autoshow in this case? And how would I go about it?
This is what the pivot chart looks like at the moment: ....
Pivot Table Chart
Jun 21, 2006Is it possible to use the chart wizard for a Pivot Table yet maintain that chart in the same worksheet and not have it in another? Every time I click on the wizard a chart is created on a separate page.
View 2 Replies View Related