So I have a graph with years present on the x-axis. I'm trying to format the axis from the years 1913-2012 (right now it's like 1900-2020). In the minimum and maximum range in the 'format axis' box these dates are listed as values instead of years (0-45,000). Is there a way to make these boxes just represent the years so i don't have to convert 1913 to the excel number equivalent?
View 1 RepliesIs there a way to display only the current month and the past 11 months in a Line Chart in Excel? So if I was to print Jan 2008 Excel graph, it would only display Feb 2007 - Jan 2008 data charting.
The spreadsheet of the data contains data for Jan 2005 - Jan 2008 so far, but only the current month with the past 11 months should display in the line chart depending on what is the current month.
I want to use a macro to change attributes of a series in a chart. Unbelievably, if the series has been pasted into the chart from another chart, and although the macro can address the series and even return values (eg name) correctly, the selection simply cycles to one of the original series on the chart.
View 3 Replies View RelatedI have a simple piece of code that creates a chart using the the values in column A as the x-axis and the values in column E as the y-axis. The values in Column A are all dates in this format: mm.dd.yyyy. However, when I collect the x-values they always appear as numbers (1,2,3). Even if I format the axis to Dates, they appear as 1.00.1900, 1.02.1901, etc.
Here is the code (also, I would like a VBA solution but if this is with excel's chart tools, so be it).
Code:
Sub Macro2()
ActiveSheet.Shapes.addChart.Select
ActiveChart.ChartType = xlXYScatter
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Name = "=""Rate of Productivity"""
ActiveChart.SeriesCollection(1).XValues = "='Sheet1'!$A$2:$A$4"
ActiveChart.SeriesCollection(1).Values = "='Sheet1'!$E$2:$E$4"
End Sub
I have some weekly data that I need to produce a graph from (100% Stacked Column). Each time I create the graph using the dates as the x axis i only get a thin line which looks like it represents one day, the other space looks like it is for the other 6 days of the week. I know that you could add an apostrophe to the beginning of the date but this is to be an automated report.
View 9 Replies View RelatedI have 3 different series: S, J, and D. Each series has a list of different dates, and I need to plot the results for each date, while comparing the 3 series. I have created 3 different charts to display the results for each series - list of dates. However, I'd really like to chart all 3 series on one chart, so that you can see how the 3 compare.
I believe my obstacle is that the list of dates are not consistent between the 3. I tried to get around this by creating one table with all the dates, and leaving blanks where necessary (on Sheet 2). However, this created a problem because the blanks showed on the charts as 0s.
I am trying to create a chart with dates from the past 6 weeks (one week at a time). The horizontal axis shows the dates from my data page correctly until I make one of the other data fields a secondary axis and then my dates change from the actual dates on the data page to 1-Jan-00, 2-Jan-00, through 6-Jan-00.
When I do the select data and look at the dats in the Horizontal Axis labels box, it shows the real dates. Why won't they show up on the chart as actual dates?
I have data that shows titles as 2-Feb, 2- Mar; which is really typed in as 2/5/2012 and 3/15/2012. My chart when graphed keeps showing the X-axis as 1-FEB and 1-MAR. How can I get the graph to show the x-axis titleas as 2-Feb and 2-Mar?
View 3 Replies View RelatedI need to format dates on the data labels of my pivot chart. My table has the correct format: dd mmm yy but the chart is showing dd/mm/yyyy.
How do I change this?
I have searched for a 'simple' solution to this but I only ever seem to find complex and indepth examples.
I summarise web hit information then chart it so I can look for trendlines. I get good info from the stat company so I can see where folk are coming from, search engine, weblink etc etc.
Once I have summarized it I have date running along the column headers then it is broken down through the rows so I have
Total Hits
Total Paid Advertising
Total Search Engine
And so on for my rows
As the days go on the data gets harder and harder to read so I was hoping I could use some kind of OFFSET function to select my data. So if my dates run from 2/11/07 to today I would like to perhaps say to Excel that I might want to see 20/11/07 to 20/12/07 in my chart.
I have plenty space to put the start and end date and I have a basic block of data.
Is there anyway for a chart to show only a certain range, ie the past 12 months, without having to update the range every month?
We need to show our scrap rate over the last 3 years. At the end of every month we add a new line. When we enter the number of pieces scrapped for this month I want the chart to drop the number scrapped in January '07
selecting dates and data and makinga graph
and thought I could learn this one myself. I was wrong. Andy Pope came up with a nice little spreadsheet but I could not follow it (being a novice).
The problem is, I want the user to be able to input a start date and an end date and a graph to update automatically between these dates (for the x-axis) but also the y data adjust to suit.
The input cells are C23 and C30, and the 5 Y-axis data are the coloured columns.
I have attached a zip file with the excel spreadsheet inside.
Using Excel 2007. Would like to format X-Axis values to display 1st month and 13th month as Feb 2, '08 for example. I would like the other 11 tick marks to remain. Sample attached ( Saved in 2003 format, I .xlsx was invalid file type)
4th micro chart uses F1:F13 for values for x axis
I want to draw a chart in Excel, in X axis Dates and in Y axis some other data. The dates which fall Saturday and Sunday will show in RED colour. Other dates will show in default colour.
View 7 Replies View RelatedI've tried for some time now to create a chart where the x-axis make up for irregular dates. Making the line between 2 points longer if it's long time between them and short if it's short time between them.
I've finally understood line charts doesn't support that and I have to use scatter graph. It seems to work but the dates, the get all messed up. I type 2014-11-11 but the x-axis in the graph shows 2283-12-12 or something like that.
Got a notion that I might have to transform it to the date value manually first but I get #VALUE ERROR
i.e. 2014-11-11 in A1, then I type in A2 =DATEVALUE(A1).
How do I get a chart to work with irregular dates correctly?
When creating bar charts i add the data I need and the bar thickness is as it should be, but when i cange harizontal axis to include the correct dates the lines turn really thin?
View 2 Replies View Relatedsee attached image. Chart is recognizing the 20 dates but not using the real dates listed in the column. Changed settings multiple ways to try to get it to use the actuals - no luck. I realize 'month's is checked in the image - but I tried 'days' and still no luck. What needs to be changed to make it report actuals listed in the 2 columns?
View 3 Replies View RelatedI am creating a chart with more than 2000 dates (each day). How can I format the axis so it only displays the 1st day of each quarter (01/01/2010, 01/04/2010, 01/07/2010...).
At the moment I cannot format the axis and minor/major units remain numbers.
I am using Excel 2010 and European date format.
I got a problem with displaying my dates correctly on the X-axis of my combined column - line graph. For some reason X-axis labels appear as consecutive numbers with regards to number of data points. Please see attached.
Also, why does the indent change as seen in this screenshot?
Last but not least, how can I prevent my secondary axis label from coinciding with the labels as seen here?
May_Daily_and_Total_Revs_by_date.xlsx‎
I have a database where I would like to plot graphs from data of different periods and different series.
My current method is to go to the data set formulas and changing data range. The tricky part is that I might skip data series because I am selecting the series required for each chart.
How to do plot the different graphs efficiently and quickly.
I have a chart that needs to be positioned at the bottom of a table of data. (The table of data is created by some VBA which extracts data from another sheet based on user input). The chart is a pivot chart built on a pivot table from another worksheet. I've read the suggestion that the chart should be positioned above the table of data which I would do, however as there is already a series of tables and charts above, it would be too confusing for the user to see the chart out of sequence.
As I already have a fair bit of VBA to generate my existing sheet, I’d like to have something very simple that I can insert in various places depending when the user makes a change, which requires the chart to be repositioned. Something like:
Change chart position To Range ("B65536").End(xlUp).Row
I am working with the Ganntt chart and horizontal bar charts but can't seem to figure out a way to force the X-Axis to behave properly.
In Excel 2007, I am trying to get the X-Axis to show major units of Months. However, in my chart options I can only change the Y-Axis to be Date/Monthly.
Changing my data layout (from the below) to be a vertical format produces the same problem, just in the other direction.
My data looks as follows:
System
Blocker
Production
Migrataion
Retired
Sys1
1/1/2012
780
Sys2
1/1/2012
400
60
90
Sys3
2/1/2013
30
5
1
The Blocker column is formatted as "No Fill" in order to cover a portion of the time-scale.
My X-Axis displays as random dates throughout the period and all of my data displays properly in terms of the scale on the grid.
What is NOT working is that the dates shown on the X-Axis really need to be based on a Monthly scale instead of the randomly selected dates Excel is using.
20120823.xlsx
I can't seem to find the right combination of options to make that happen or force a scale on the X-Axis.
Please see the attached workbook.
I would like to create a bar chart which is linked to a drop down box (from data validation) that allows me to choose of the 3 sets of data to show in the x axis.
So if i choose "HALF YEAR", the bar chart will have two bars (one is "jan-jul" the other is "jul-dec") with the green section data if i choose "QUARTER YEAR", the bar chart will have four bars ("jan-mar", "apr-jun", "jul-sep", "oct-dec") with the blue section data if i choose "INDIVIDUAL MONTHS" i have twelve bars....with the red section data.
The attached file has a scatter chart in which all of the data is properly displayed except the X axis. How can I get my X axis to reflect the data I have selected?
View 2 Replies View RelatedI'm attempting to write a code that will change the colour of a series in a chart based on the name of each series. i.e. I have Roads, Water and Rail as three of my series names. can i somehow assign the colours yellow, blue and red to these series using macro code.
View 5 Replies View RelatedI am having a hard time figuring how to change the color of all the different text groups in a chart, header, axis, labels etc, and even the macro recorder has failed me this time - I have recorded the following, where I selected the graph, changed textcolor to white and then ended the recording - but without changing anything in the code, it returns an error on the second line (starting with "with"):
Run-time error '-2147024809 (80070057)':
The value is outside of the boundaries (translated from danish)
ActiveSheet.ChartObjects("Chart1").Activate
With ActiveSheet.Shapes("Chart1").TextFrame2.TextRange.Font.Fill
.Visible = msoTrue
.ForeColor.ObjectThemeColor = msoThemeColorBackground1
.ForeColor.TintAndShade = 0
.ForeColor.Brightness = 0
.Transparency = 0
.Solid
End With
From what I have read, it might be something with the textframe2 property.
How do i change a title in legend on a chart. i.e. I want the label to be MGRS 2%
Job Category
Category %
EE Total
Mgrs
2%
15
Supvr
6%
51
[code]....
I have created a column chart and am now trying to change the colour of one
of the bars. Here is the code I am using:
With chtChart
ActiveChart.SeriesCollection(1).Points(2).Select
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
End With
I maintain Production data in an Excel Sheet and have the code which will create Chart for each Employee. Sample Sheet Attached. However, I want the Chart Range to be updated in a Single Chart and exported to a .Gif File.
View 2 Replies View RelatedI have 3 charts on 3 seperate worksheets. The data for each is sourced from a list of up to 100 but only the top 20, or all if less than 20 in full list, are displayed on the chart. A seperate worksheet ("Calcs") is used to sort all the data so that just the top 20 are sourced for the charts and they are displayed in descending order from max to min and alphabetically in the case of 2 or more names having the same value.
I have attached one ot the charts.
I need the y axis scale to auto-adjust to suit the max and min series 1 values. I need the y axis maximum to be 5 more than the max series 1 value (unless the value is 5 or less when axis should be 10) and the min y axis to be 3 less than the min series 1 value (unless the value is 3 or less when the axis should be 0)
I have done the formulae to sort out these conditions and put the required y axis values in a cell which the copies across to the chart sheet with the required max in cell M1 and the min in cell L1. These values will change when another macro runs.
I searched this site for the code to auto-adjust the scales and found one which I have tried to edit to suit my needs. This is the code I have ended up with, placed within the worksheet containing the chart....