Pivot Table With Dynamic, Updatable Chart, But Not A Pivot Chart!
Dec 19, 2008
My 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?
View 9 Replies
ADVERTISEMENT
Mar 21, 2007
1) 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?
View 4 Replies
View Related
Jul 1, 2014
i have a dynamic pivot chart. It works well, except the chart area size, it remains the same. Is there any way how the chart area can be automatically adjusted/resized (see the example attached)? Maybe with VBA?
View 8 Replies
View Related
Jan 3, 2013
If 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.
View 2 Replies
View Related
Jun 21, 2006
Is 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
Sep 17, 2009
I have attached a portion of my Pivot Chart in Excel 2003 and have been trying to recreate this in 2007 with some difficulty. I am particularly interested in displaying the field list headings (specifically 'Count of Hit or Miss') in the pivot chart in 2007 but cannot make it work.
View 4 Replies
View Related
Mar 29, 2007
I have a data table and want to create six charts. In theory pivotcharts would be the way to go. However every time I refresh the charts they revert to excel's default chart and all my colour scheme and nice layouts are lost.
View 3 Replies
View Related
Apr 28, 2014
I have a list of customer satisfaction scores that are pulled in from a sharepoint list. The list is then used to create a pivot chart that is used in a web part on a dashboard in sharepoint. The chart is supposed to show average customer satisfaction scores per category per month.
The problem I have is that I can't filter the categories using a slicer, I can only filter the categories' values (i.e. the scores). I can filter by date though.
When I try to filter the various score categories/series (Support, Change Management etc) I only get the option to filter their values i.e. 1,2,3,4,5.
I want to be able to use a slicer or similar to be able to display either all of the series or just selected ones.pivottable.JPGpivotchart.JPG
View 14 Replies
View Related
Apr 30, 2014
Was working on this problem for a poster: [URL] ......
I can't seem to get the pivot table/chart to format exactly as I desire. It seems that as soon as I add 'group by hour and day' Excel forces the formatting to AM/PM and I want to keep it military. I want to group by hour, so that data that occurs at 6:00 and at 6:30 are grouped, and I had to group by day so that 6:00 on 1/1 was separated from 6:00 on 1/2.
Attached is a worksheet which shows the desired chart format (not a pivot chart), and the attempted pivot chart. I want the pivot chart to match the 'simple' chart in look and feel. Any attempts to change the formatting of the row labels to 'h' is promptly ignored by Excel.
Note the two tasks that occur at hour 18 (one at 18:00 and the other at 18:20 (you will need to see the formatting to truly see the minutes)). Those should be combined in the pivot table (and they are) and on my 'adjusted' table (where I used SUMIFS).
View 2 Replies
View Related
Aug 14, 2014
Or at least with a formula?
This is the chart formula for the report:
=SERIE(Report!$B$10;Report!$A$11:$A$30;Report!$B$11:$B$30;1)
View 3 Replies
View Related
Mar 3, 2009
I have created some pivotcharts in Excel 2007, but I have a serious problem with them The labels (usernames actually) in the data table are impossible to read (pls see attachment) If I make the letters smaller it is unreadable, if I make them larger it is still the same. how I can make the data table "resized"?
View 4 Replies
View Related
Feb 4, 2010
I have recently noticed a problem with a pivot table and assoicated pivot chart. The table has numerous columns from date, location and one entitled route. The route column has a value which is either a number or 'other'. The formatting of the column has been set to general (although the same problem was present under text). The problem is as follows:
When a new row of data is entered where the route is the same value as previous rows it should be grouped together. However when I go into my pivot table and look at the options for route I find the same value repeated twice. ie a route value of 155 will be listed once at the top and then lower down the list. A temporary fix is to rename all values of 155 to x155 and then back to 155. This then has everything grouped again under one value in the pivot table. As soon as new entries are added though the problem repeats itself. Does anyone know why this might be?
View 4 Replies
View Related
Dec 4, 2012
My pivot table shows Towns as the Row headings and Month as the column headings. When I do a Chart, I get Towns on the X axis and Months as Y. I want the chart to show all my May data in a stacked column, then all my June data in another stacked column, etc. If I pick "Select Source Data" the options are greyed out. What's a mother to do?
View 1 Replies
View Related
Mar 17, 2008
I can not get my Pivot Chart to count, sort and categorized my data. I have included a sample file for your viewing. I basically want the data in cells C2:E18 counted, sorted and totaled by categories and locale. I thought the pivot charting was simple, but I'm having quite a challenge with this one. The pivot chart data should reflect as my sample indicates in cells E24:K27
View 3 Replies
View Related
Apr 18, 2013
How 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 Related
Jul 30, 2012
Can Pivot Table chart add another data from another sheet? I attached the link for this file (Add data1), it is because the file consists of several sheets and I do not know how to show here.
I would like to add the data from the "Rate" sheet into the Pivot Table chart (Chart.PT). I made an example by using normal way (Chart.Case (9)), the column series in the chart is the one I added from the "Rate" sheet. I wonder if I could do the same in pivot table chart.
View 9 Replies
View Related
May 8, 2007
I have an Access application with a form containing an Excel chart based on a pivot table based on an Access query based on an Access table which is refreshed every 5 minutes using a Timer event. When the table is refreshed the Excel chart is refreshed. The chart monitors some processes and is active throughout the day. As the day progresses more and more data points are available for plotting on the x-axis. The x-axis labels get very crowded as the day progresses and I need to programmatically change the x-axis scale at various times of the day. To that end I am trying to find an event that is activated at some point during the automatic refresh of the chart/pivot table.
I have tried the Workbook_Change event, the Worksheet_Change event, and the Chart_SeriesChange event. None of these are executed during the refresh process or I have done something wrong in trying to use them. At the moment they only have a MSGBOX message in them so that I can determine if they are even being executed.
View 9 Replies
View Related
Aug 15, 2006
I 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.
View 9 Replies
View Related
Aug 13, 2014
I have table with companies across in columns, months down the side in rows, and 6 parts to the data in addition to company and month. I have quotes from 3 different sources, and won deals from those 3 different sources. How do I create a pivot table/chart to analyze the data quickly, or find out Top 10 companies quoting with value and top 10 companies with won deals?
I have attached an example, taking out the company names with A, B, C, and source with A, B, C. The cells do have an array formula which picks data from another spreadsheet. Which, by the way, is really slowing my machine down each time I type something into the spreadsheet.
Quotes.xlsx
View 14 Replies
View Related
Oct 31, 2008
I have linked a graph to update from a pivot table. I would like to print a copy of a graph and pivot table for each item in the page field.
Can a macro loop through each page field and print?Is there a way to send them to the printer all at once? (I noticed the printer hangs when manually printing page by page)
I've tried "view all pages of pivottable" but then I lose the view of the chart. I tried recording the events but the number of items in the page field changes with each data update and I'm not sure how to write code to accept this.
View 8 Replies
View Related
Feb 26, 2014
I am trying to display data on a chart and dynamically change the items to display by manipulating the filters. What I cannot figured out is how to display the Pivot Table Grand Total column on the chart. This is the total that the pivot creates and there is no total field in the data. Hope this explanation I gave is clear.
View 1 Replies
View Related
Jun 4, 2012
I have a user that uses pivot tables and charts every month to do a report. He wants to copy his charts every month and then just change the month in the data for the pivot table, but not matter what he tries its tied to the previous month and then chart data range in the select data source properties is grayed out. how to get this to work?
View 3 Replies
View Related
Sep 24, 2011
I am stuck trying to sort data that looks like the following, into a format that I can use as source data for pivot tables/charts.
Excel2007ABCDEFGHIJK2MalayMalayMalayMalayMalayMalay
ChineseChinese3MaleFemaleFemaleMaleFemaleFemaleMaleMale
436-4536-4536-4525-3525-3525-3536-4536-455
DateArticleContentLapseCurrentCurrentCurrentLapseLapseCurrentCurrent612-SepRelieving 40 years - Oct 15, 1972
Thick Haze continues to blanket MalaysiaHistory42712-SepRelieving 40 years - May 5, 1972
All Sharifah wants is a pair of legsHistory1222812-SepA man and his agonyHistory3223912-SepA lesson on Sept 16History22Sheet2
I will need to group Data multiple ways. However, I cant work out a good way to sort it. Can I have a suggestion on layout that I will be able to arrange data appropriately.
View 2 Replies
View Related
May 16, 2006
Please 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?
View 6 Replies
View Related
Aug 12, 2006
I 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 Related
Feb 7, 2014
I have an export from a database that I'm bringing into Excel 2010 of about 30K records. Data points are recorded numerically and I have their associated text "value label" (what it would be called in STATA, for example, not sure what it's called in Excel). I want to create various charts/pivot tables with the data and want the labels to be the text label, not the number.
For example, variable ASSIGNMENT has the following possibilities:
1
2
3
4
Here's what each of those "mean" (I have this in another table):
1 - Sick
2 - Overtime
3 - Court
4 - Present
How do I create a chart or pivot table where the labels are "sick", "overtime", etc., and not "1", "2", "3", "4"?
View 8 Replies
View Related
Apr 7, 2014
I 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 Related
Oct 13, 2011
Within a pretty large key indicator report I would like to add a top five rank without using a pivot chart.
View 3 Replies
View Related
Feb 1, 2010
Hi 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: ....
View 9 Replies
View Related
Oct 10, 2006
I'm trying to Create a pivot chart from Data. I've attached a sample of the data I'm using. What happens when i try to create a chart is test 1-9 get assigned to Open which is fine. I can then drag Open to the position in the chart i want using the layout feather. I could then uncheck test 7 if i didn't want it or whatever. I want that same process to run horizontally for the numbers from 1-10 to go under the header WW so that i can unclick any WW that i don't want in the chart if you get my meaning. I included an example of what i'm talking about. Sheet 1 contains the raw data and Sheet 5 contains a small explanation.
View 5 Replies
View Related