Show Missing Values In Pivot Chart (line Chart) Axis?
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
ADVERTISEMENT
Oct 9, 2006
I have a spreadsheet where I track income (positive) and expense (negative) items and compare budget to actual amounts. I want to create a simple bar graph comparing budgeted amounts to actual amounts for each item.
However, when I do this, I see all of my income items above the line (pointing up) and all of the expense items below the line (pointing downward).
Is there an option I can select to just use absolute values for the items so they all appear above the line?
View 3 Replies
View Related
Jan 4, 2008
i want 0 values to start at x and y axis.
basically i want time in secs on x axis starting a 0 upto 4.4 secs
View 9 Replies
View Related
Aug 5, 2014
the y-axis break between 0 and 0.8.
View 3 Replies
View Related
Aug 14, 2014
I am plotting a line chart wherein the X-axis is based on % and Y-axis is based on $. I have 5 scenarios wherein they have different ranges. I want to have the X-axis plotted up to 100%. How to get the X-axis stacked at 100%. See attached file.
View 1 Replies
View Related
Jul 18, 2014
With a scatter chart, you can use the following vba to set maximum and minimum category values on a scatter chart. Is the same possible for a line chart and if so, how?
With ActiveChart.Axes(xlCategory)
If Range("S6").Value "" Then .MinimumScale = Range("S6").Value
If Range("S7").Value "" Then .MaximumScale = Range("S7").Value
View 7 Replies
View Related
Jun 18, 2007
I have an Excel chart as a separate worksheet. It has 10,000 data points. Is there a way to Zoom in on the X-axis only. I am looking to expand or zoom the X-axis to take a closer look at the individual data points. Since I have 10,000, I need a way to take a closer look at an area defined by the mouse pointer. Can this be done with VB code? I am not looking to expand or zoom the "Y" axis, this can be done with the scale values. Can this be done with a mouse drag or double click on the area of the chart I want to expand.
View 3 Replies
View Related
Mar 1, 2014
I have a spreadsheet with golfers handicaps, golfers names down the left in column A in rows 3-35. Row 2 has the event numbers titles 1 to 18 (18 events in a year). i need a graph that has the person as the vert axis and the horizontal axis needs to be the event numbers, so i can read down for the person and across to see how there handicaps change from each different event. i have attached the data below.
View 1 Replies
View Related
Mar 3, 2008
I have a chart that feeds from dynamic ranges which contain whole rows of null enteries displayed as #N/A. The charts do not plot these enteries (as it shouldn't) however it does display the category label even if it too is #N/A. Is it possible to remove the category label if all data for that row is null. ie the label is also removed from the chart.
View 2 Replies
View Related
Dec 17, 2013
I have created a pie chart showing top 10 best selling categories. However, on this pie chart, I want %age of overall sales to be shown.
At the moment say I put in the following
Catalogue 1 100
Catalogue 2 98
Catalogue 3 92
Catalogue 4 85
Catalogue 5 84
Catalogue 6 75
[Code]....
I add Data Labels and select the percentages. This shows at Cat 1 having 12.5% of the sales (100 of 800 sales), Cat 2 having 12.25%, and so on..... However, what you don't see is the following data:
Catalogue 11 60
Catalogue 12 58
Catalogue 13 57
Catalogue 14 57
Catalogue 15 56
So based on 1,088 sales, Cat 1 at 100 is actually only 9.19% of TOTAL sales.
I understand what is happening, the chart is giving the percentage based on the data inputted into the chart.
I did consider adding an 11th category to the chart, which was the total of Catalogues 11-15. Whilst this does give a more accurate percentage figure (as now all sales have been inputted), it makes the chart look ****
Is there a way of either adding the 11th category, and then "hiding" it, so that the chart doesn't display it, but uses it's value to calculate Catalogues 1-10s percentage?
BTW: The reason I ask is that I could be dealing with anywhere from 30 to over 100 catalogues. So it's not ideal showing the others in 1 chart.
View 1 Replies
View Related
Apr 13, 2012
I have got a lot of data to put in a chart that updates every 5 minutes with new data - i have a chart that Line chart that shows this.
It shows the cost of something every 5 minutes.
I have put this in a chart using offset but obviously i want to show the cost over the course of the day - because this updates every 5 minutes, the axis at the bottom (which has the time of the data) is massive and doesn't look good.
Is there a way to show the axis at an hourly interval but keep all the data in the chart?
View 5 Replies
View Related
May 15, 2014
I have a data set where a number of pieces of technology are listed in rows. For each piece of technology it can relate to a "Network" or a "Terminal" and the columns are yes/no as follows:
Tech Network? Terminal?
Tech 1 Yes No
Tech 2 Yes Yes
Tech 3 No No
Tech 4 No Yes
When I plot this on a pivot chart, I get the attached chart, where the x-axis relate to the hierarchies, i.e. Yes/Yes, Yes/No, No/Yes or No/No. As you can imagine, this is not very useful for the viewer as it's not clear what the yeses and nos refer to. It would be much better if I could rename the axis labels: "both"; "network only"; "terminal only"; or "neither".
Is this possible? And is there a solution that doesn't require me to change the source data? I'm happy to use a power pivot if that would make things easier.
View 1 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
Sep 21, 2006
I made a pivot chart. On the X axis are days of the year. I would like the axis to give the months rather than the days, because it is easier to read. But when I select the chart's axis, I don't have acces to any "number" sheet in the "Axis format" section. I could just change the scale.
ActiveChart.Axes(xlCategory).Select
With Selection.TickLabels
.Alignment = xlCenter
. Offset = 100
.ReadingOrder = xlContext
.Orientation = 45
.NumberFormat = "[$-41D]mmmm;@" 'I added this line
End With
ActiveChart.HasPivotFields = False
View 3 Replies
View Related
Sep 15, 2014
I had created a pivot table and pivot chart for the data, the pivot table shows the row label's as percentages which is correct, but it when comes to the pivot chart it shows it as decimal, for example a 10% shows 0.1, I tried of formatting the x axis and choose percentages but it doesn't want to change.
View 6 Replies
View Related
Nov 19, 2013
I have a pivot chart which has one Axis field (Cell Ref2) and one Sum of Values field (which is a table series of percentages calculated from formulas)
I see the graph like I'd expect, but I'm struggling to get it to show percentages rather than basic integer units on the y-axis? Usually I would imagine to just right click on the y-axis : Format Axis, and expect to see something to change the display units, but it is only for hundredths thousands etc rather than a percentage ? The units themselves in the table for that value series are in percentages so I don't see why it isnt showing the percentage units in the first place.
View 1 Replies
View Related
Jul 3, 2014
I have a set of data with values and dates (call the dates 'raw dates'). I have added 3 columns with formulae in them as follows:
Year =YEAR('raw date')
Month =TEXT('raw date',"mmm")
Day ='raw date', formatted as "d".
I have a pivot chart with three Axis Fields. In order they are: Year, Month and Day. This is so that the chart itself has three layers of x-axis names (year, month and day) rather than a whole load of dates which looks messy.
I would like to show the values as a running total "in" the raw dates of the data.
When I show the values as "Running Total in" 'raw dates', I get a load of #NAs in the pivot table because the raw dates are not in the Axis Fields. If I select Running totals "in" the Days (which is included in the pivot table setup), I get running totals over each month, starting at zero on the first date in each month. I need to have the running total run over the course of the whole data set, not mini running totals in each month.
When I choose only the 'raw dates' in the axis fields (i.e. taking out Year, Month and Day), running total works fine. The only thing is that I lose the nice looking x-axis. Adding the raw dates to the other 3 Axis Fields also doesn't work - it doesn't even try to give a running total at all, just the original values.
how to achieve a running total without sacrificing the x-axis?
View 1 Replies
View Related
Jan 5, 2009
I have created a line chart that plots weight of a person, but want to show on the plot area what a certain range of weight represent, Ive attached images to show this concept.
View 4 Replies
View Related
Jan 29, 2009
how to add an average line to pivot chart?
View 2 Replies
View Related
Aug 1, 2007
I got a set of data which is only 2 colums collecting data problems. The output i get is a time stamp and a fault. Im looking to set a chart up with the time been on the X - axis and number of problems per hour on the Y - axis.
View 7 Replies
View Related
Jul 17, 2013
I have a chart with percentages and most of them are >40% so I would like te values to start at 40 in the y axis but I would also want to have the 0% show (as it stands there is nothing there) It is a double y axis chart where the % is shown with just a dot and the other Y axis is the actual column. Im not sure if I'm making sense but I would just like to have the 1st value in the secondary y axis be 0 and the 2nd value be 40, and from that point forward scale in intervals of 5%.
So the values in the y axis are: 0%, 40%, 45%, 50%......
View 1 Replies
View Related
Jun 25, 2007
column chart displaying various pieces of data.
At the top of my worksheet I have a combo box which allows you to select a local authority name; and all relevant information in the worksheet is highlighted (using conditional formatting, extra series in charts to make relevant points highlight in red, etc).
I've added a series to the chart and changed it's type to line - then with the data labels positioned beneath the points on the line I can get conditional formatted x category labels - all outlined here: [url]
Because my x category labels are fairly long I've had to give them an orientation of -90 and make the minimum y-axis value equal to -200 to give enough room beneath the chart to fit the text in.
As I said above, the values below 0 are just there to provide space at the bottom of the chart, so my question is: is there a way to hide these negative values? I'm sure I've done it before, but can't remember how - I thought maybe a custom number format of #;[White]# would work, but it just inverted my text (white text on black background).
View 11 Replies
View Related
Dec 18, 2008
is does anyone know if there is a way of marking the y-axis in a chart with data from a table, so rather than having a fixed segmentation width have the distances between the data points marked on the Vertical to conform to a range in a spreadsheet? I know you can do that for the x-axis. Is there any way to do that for the y-axis as well?
View 9 Replies
View Related
Nov 20, 2009
how I get the maximum and minimum values of the axis from and excel chart? What I mean is I want to specify a chart, and output in the same excel worksheet the values for the maximum and minimum of the two axis.
I have tried various searches, but cannot come up with the answer, although I have a sneaky suspicion it in here somewhere.
View 9 Replies
View Related
Oct 8, 2011
Why the recorded code doesn't work? I recorded the code below to format the Y-axis values of a chart with the recorder.
Code:
ActiveChart.Axes(xlValue).Select
With Selection.Format.TextFrame2.TextRange.Font
.NameComplexScript = "Arial"
[Code].....
View 6 Replies
View Related
May 21, 2014
remove 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 Related
Nov 27, 2011
I am looking for a creative way to display a pie chart within a data point marker of a line chart.
My database has 3 value columns, Type1, Type2 and the Total (Type1 + Type2)
these are recorded per day (Date, in Column A)
I have a line chart that displays the total by date, but I want to find a way to display the percentage split of a particular day by type.
I was thinking to load the chart image into the Data point marker, but i don't think that is the best way as the data is updated daily and I would have to do it each day for a few line charts.
The other way I was thinking about was to have a generic Pie chat in the Line chart (Maybe in a corner) and the pie would update depending on way date series was selected or Mouseover'd)
the way that I am approaching it at the movement (Not the best way and by far not the coolest way. Is to have a list of all the dates in a column next to the Line chart and using some VBA, what ever date is selected in the column the pie chart displays the corresponding data. But eh challenge is that when there is alot of dates, I am going to be scrolling up and down.
I am using Excel 2010, but I cant not use the slicer's as the other users do not have 2010, they have 2007.
View 1 Replies
View Related
Jul 14, 2012
I have created a population pyramid and want to add another chart on the secondary axis which I can do. The part which I seem not to be able to complete is getting the line chart secondary axis to read from the primary y axis. It seems to add a new axis, even when I delete this the line chart does not read correctly.
View 1 Replies
View Related
Nov 6, 2013
I have a pivot table/chart that I need to show the sums as top 20 (or any other number) and All others. How can I do this.
I have an example file here: [URL] ...........
View 4 Replies
View Related
Feb 15, 2012
I am trying to overlay a line chart on top of a stacked column chart. The stacked column chart is a chart where the x axis is dates and the y axis is amount. The line chart is a projected amount that I will reach in few years; the x axis is the date while the y axis is an amount. I can get a chart that has a line and stacked columns, but ever time, the dates get messed up. The line has an extremely wide date range while the columns only have about a month of data.
View 9 Replies
View Related
