Free Microsoft Excel 2013 Quick Reference

Graphing Data

Is there a way to graph data in cells that only have data in them? In other words, when you add more data, the graph automatically gets larger without having to edit the chart based on more data entered. If there isn't data entered in a particular cell, i don't want it to graph it as "0".

Post your answer or comment

comments powered by Disqus
Currently I graph data from laboratory tests with results in the y-axis and
dates in the x-axis. I then create another series to display the compliance
level for the test over the same time period. To achieve this I need to
create another column on my data sheet to include the compliance result in
each row.

Is it possible to have the compliance figure in a single cell on my data
sheet, yet still display the compliance level on my graph together with my
data points?

I am trying to figure out how to sort a data table beneath an excel graph.

There might be as many as 70 lines in the graph data table that need to be sorted.

I need to be able to sort them from highest to lowest in value according to the furthest right column in the graph data table which is the latest month in a 13 month rolling monthly report.

I am not looking to change the data source from which this graph is derived. I need to just automatically sort the data in the Graph Data Table. I also do not want to create a pivot table.

Does anyone know if there is any VBA code which can do this.

I currently manually move the data in the graph data table by right clicking the graph, selecting data, then click on each line I need to move up or down depending on the result in the new month which takes a very long time when you are doing this for 50 graphs a month.

Any help or suggestions would be much appreciated as I have scoured the web and it does not appear that anyone has asked this specific question before.

Why won't excel allow you to graph data that is in hidden rows or columns?

Ok, I have a list of data that goes beyond what I want to graph; but I need the list as long as it is incase I make a change. I understand that will be confusing to all so, the list is engine related, intake valve lift to be specific (x). The data associated to it is velocity. If my cam only has .32" of lift and the list for lift goes to .6" (and back to 0" from there) and I want to graph velocity (y) ... I end up with a dead spot the size of the missing lift (x) and then velocity (y) graphs again.

How can I make it so either a range of data is mirrored and able to graph in a continuous line and the x axis labels correct per associated lift, .30, .31, .32, .31, .30, etc?

I can work everything I need manually but it takes too long, and if I select a different cam with more lift it's even more work/typing. The graph should look close to a U in shape with no breaks in the line.

Maybe there's a way to graph ranges of blocks of cells?
I tried using a semicolon but that didn't work, would be perfect if it did .

Because visual aids will help,
This is what I have graphed:

Notice that the X ranges up to 1.20. This is because it was the only way I was able to manually get the lines in the correct orientation, I'm unsure how to use a mirror function.

Written another way,
I want to take a point on the X axis and mirror the data/graph up to that point from this graph/data:

So it would look like the first picture minus the gap in the line.

Hopefully someone can make sense of my rambling/problem?

I have been stuck on this problem for far too long and no one is able to help me.

I have a large vba code already running and I have working graphs that run as well. BUT what I need to account for is that the data that is in column "G" will next time be in another column.

Here is my graphing code right now and the bold data is what I believe needs to be changed:

     ' LVDT Graph
    LR = Range("A" & Rows.Count).End(xlUp).Row 
    ActiveChart.ChartType = xlXYScatterSmoothNoMarkers 
    [B]  ActiveChart.SetSourceData Source:=Sheets("Data").Range( _ 
    "Data!$B$3:$B$" & LR & ",Data!$AE$3:$AH$" & LR), PlotBy:= _ 
    ActiveChart.Location Where:=xlLocationAsNewSheet, name:= _ 
    With ActiveChart 
        .HasTitle = True 
        .ChartTitle.Characters.Text = "LVDT" 
        .Axes(xlCategory, xlPrimary).HasTitle = True 
        .Axes(xlCategory, _ 
        xlPrimary).AxisTitle.Characters.Text = "Time [s]" 
        .Axes(xlValue, xlPrimary).HasTitle = True 
        .Axes(xlValue, _ 
        xlPrimary).AxisTitle.Characters.Text = "LVDT [mm]" 
    End With 
    With ActiveChart.Axes(xlValue) 
        .MinimumScaleIsAuto = True 
        .MaximumScaleIsAuto = True 
        .MinorUnitIsAuto = True 
        .MajorUnitIsAuto = True 
        .Crosses = xlAutomatic 
        .ReversePlotOrder = False 
        .ScaleType = xlLogarithmic 
        .DisplayUnit = xlNone 
    End With 
    With ActiveChart.Axes(xlValue) 
        .MinimumScaleIsAuto = True 
        .MaximumScaleIsAuto = True 
        .MinorUnitIsAuto = True 
        .MajorUnitIsAuto = True 
        .Crosses = xlAutomatic 
        .ReversePlotOrder = False 
        .ScaleType = xlLinear 
        .DisplayUnit = xlNone 
    End With 
     ' Plots and formats the chart
End Sub 

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

As you can see, for this graph, multiple columns of data are graphed.
The names of the headers will always be the same, I just need excel to find those names and graph all the data below them.

Any help would be awesome!

I need to create a dynamic graph that will display from a data range in a "Raw Data" tab in the same xls file. The "Raw Data" will be automatically populated by a server so it is unknown how many lines of data will be input - the graph needs to automatically detect this and then display it.

I.e. If there are 2 lines of data, the graph should display 2 bars of that data. If there are 10 lines the graph should display 10 bars of that data.

In addition, each line of data starts with a date and has values for the different Transport Modes used on that date. The user should be able to select which Travel Modes they wish to view, and the graph should update accordingly.

I have got some way to doing this (with kind help from this forum) but am now very confused.

In the attached, the green cells on the "Raw Data" tab will be populated by the server. “Settings” tab (green cells) will also be populated by the server, based on settings the user has chosen online.

FYI, the server puts a ‘Z’ on the end of the date, so the “Raw Data Edit” copies the “Raw Data” tab, without the ‘Z’.

Thank you very much,

I am creating a timeline using a stacked bar graph. The 3 colums in the source spreadsheet are title,start time and duration. The Two data series are start time and duration. I hide the start time series to give me timelines for each row in the spreadsheet. This works fine. What I'd like to do is to group the rows by title and distinguish by colour in the duration series but this doesn;t seem possible? I hope I've made myself clear ! Any advice or help appreciated

Hi guys, im trying to select data from a table to be very new to VBA so any help will be much appreciated...

I have 4 columns Date, Time and connections A and connections B

- I would like to be able to select a specific date rangeTextBlack to be graphed.

Many Thanks

Is there any way (VBA or other) to prevent the data in a graph from disappearing when you hide the rows that contain the graph's data?
(Particularly without storing the data in a hidden sheet...)



I was hoping you could help me with a problem I am having with my area chart. Since area charts don't recognize #N/A and still go to zero, I need help writing a macro that conditional graphs data that is above 0, but the data will change as their are different inputs that can affect it.

Below is the macro I have now with a variable graphing axis, right now it is graphing A1:G199, but I only want the cells with values in them which can vary each time.

Is their any way to do this?

Thanks for all your help!

    ActiveChart.ChartType = xlAreaStacked
    ActiveChart.SetSourceData Source:=Sheets("GraphData").Range("A1:G199"), _
    ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="DefaultLTV"
    With ActiveChart
        .HasTitle = True
        .ChartTitle.Characters.Text = "Default LTV"
        .Axes(xlCategory, xlPrimary).HasTitle = False
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Outstanding Value"
    End With
    ActiveChart.HasLegend = True
    Selection.Position = xlBottom
    With ActiveChart.Axes(xlCategory)
        .MaximumScale = Sheets("InputDashboard").Range("$D$8").Value
        .MinimumScale = Sheets("InputDashboard").Range("$F$31").Value
        .MajorUnitIsAuto = True
        .BaseUnitIsAuto = True
        .MajorUnitIsAuto = True
        .MinorUnitIsAuto = True
        .Crosses = xlAutomatic
        .AxisBetweenCategories = False
        .ReversePlotOrder = False
    End With

I have data in the following form

WeekNumber - Station1 - Station2 - Station3

I've got a Visual Basic code that fills up the weeknumbers up to the current week. I then use an if sentence that displays some result for Station 1-3 only if there is a weeknumber present in the same line. Otherwise it displays nothing. I copied the if sentence down through the 52 possible weeks. So far, so good.
But now I wan't to graph my data, and I use the offset function in order to obtain the data I want like this


My problem is that the function 'counta' counts all the "empty" cells as well since they include an if sentence, even though said if sentence returns a "".

How can I get around this ?
Please let me know if anything is unclear, and thanks for your help.

Hi all,

For the last couple of weeks, I have been producing 2 graphs as line charts to show (1) the number of items we have and then (2) the amount that they cost. My manager would like me to combine the two to show the correlation between the 2 sets of data, however the value of the items is significantly more than the number of items and I am having difficulty putting this into the one chart. Does anyone have any suggestions?

The data set is as follows:
07-Apr-08 14-Apr-08 21-Apr-08 28-Apr-08
Amount 49,535,934.00 15,187,052.00 20,470,120.00 2,681,828.00
Items 1,465 3,470 5,245 1,599


On a simplified level, assume I have a small bar graph (inserted around cell f1) who's data is found in cells a1:e1 (in truth I have about a hundred of them, but 1 will get the message accross)

Is there a way that I can copy the Graph (say to around cell f10) along with the data that's in a1:a5 so that the data replicates in a10:e10 forming a new set of data and a new graph?

Effectively, I want to duplicate my graph and data without having to copy the graph, then the data and then tell the graph where the new data source is...... 100 times!


I've done an extensive search of the forums, and haven't come across a solution to this tricky little problem!

I've got a 52 week chart that graphs overtime as a variance to plan.

The graph takes the data from the actual column and then charts it for me on the right. The cells in the actual column take data from another spreadsheet in the workbork, and as a result, if they don't have any data in the previous workbook, they show up as a zero in the new column. Typically this is an easy fix with a NA() formula addition.

Here's my problem. I've got an average calculation in the total column to provide me with an ongoing YTD overtime calculation. If I use the NA() in the formula to have the graph ingnore the zero data fields, it seems to mess up my average calculation at the bottom of the column. I've provded an example of my average formula below. Is there any way to build a formula in the "Data Entry" cells that are linked to the previous worksheet that will provide my graph with a NA() type function or the line graph doesnt hit aero, while at the same time not have an impact on my average formula?

I have been graphing data from our call center since the beginning of this year. I have one workbook with two sheets, one with the data and the other displaying the graph. I have six series of data. I right click on the graph and access the source data. I click on the collapsable dialog box for the values which takes me to the data sheet to add the next month's daya For Jan-October there have been no problems. When I went to add the data for November the 11th month the dialog box collapses but stays on the graph sheet and not the data sheet where all the preceeding data is,and will not allow me to add more data. There has been no change in the sheet's name. It's as if Excel has placed limit on how much data can be added.

Any suggestions will greatly be appreciated...Wen

Anyone know of a way to show the data table below the graph, BUT only show series that I select?

For example, if I have graphed FY03, FY04, FY05 & FY06 but only want my data table to show the values for FY03, FY04 & FY05 - can that be done?!? I haven't found a way to exclude something from the data table if it is on the graph...




I need help with my spreadsheet. I have a spreadsheet that has data ranging from January 2006 to December 2007. I graph the data from the previous month to the preceeding 12 months, in order to get a 12 month range. For example, for October, I graph data from October 2006 to September 2007. What I currently do is hide the months that I don't want to show on my graph every month.

Is there anyway that Excel can automatically change the range for me? For example, for November, can the range automatically move to graph figures from November 2006 to October 2007, without having to hide any unnecessary data?

Any help would be greatly appreciated.

Thank you!


I've down loaded XY data labelling tools that work a treat but I'm still having one glitch.

When I filter the data in my table - I'm auto-popualting a risk matrix with risks that are still "Open" - the data labels do not correspond to the XY point that is plotted they just follow their own logical order.

******** ******************** ************************************************************************>Microsoft Excel - Testing John's Risk template.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutE13F13E14F14E15F15E16F16E17F17E18F18E19F19E20F20=
BCDEFG11Ref.Probability*(P)Impact*(I)*Risk*Rating*12*Status13112LLowClosed14244VHVery*HighClosed15333HHighClosed16425HHighOpen17514HHighClosed18622LLowOpen19723MMediumOpen20831LLowOpenRisk Log (2)*
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box

From this table the points on the sctatter graph will be plotted in the correct place for Refs. 4, 6, 7 & 8 but they will be labelled as 1, 2, 3 & 4. I need them to maintain their corrrect reference numbers on the chart once filtered.

Can anyone help? I'm so near, yet so far with this, I was actually thinking about spreadsheets in bed last night... that can't be good!

Is there a way to have scatter graph(data in a column) to display a particular letter letter on the chart based on onther column from the table?

Many thanks for any help

I need urgent help.

I have data in my excel sheet. I am trying to compare the achievement levels of both boys and girls in years KS2 (primary school),7,8,9.
I entered data into 6 columns; Class, Gender, KS2, year 7, year 8, year 9
however, now when i try to compare my data all the males and females are in different entries on my graph (i.e. male, male,male, female, male, female,female, instead of male, female).
I would ultimatley like to have a scatter graph and bar chart displaying the achievement of both boys and girls. Sooo sorry if that is confusing, please ask if you dont understand. ANY help would be greaaaatly appreciated. thanks in advanced

I have data for a graph located between C58 and F97, for 40 rows of data total. Sometimes all 40 rows are populated and other times there are only 5 or 6 lines of data total. I have two questions here:

1) The spreadsheet has a macro that populates the cells in question and creates the graph. How can I tell the macro just to consider the populated rows and ignore the empty ones, when it is creating the graph?

2) Once the graph is created and the macro is finished, I want to give the user the option of excluding some of the lines of data from the graph if they suspect the data is faulty. So to do this, I have run a column (G58 to G97) down next to the columns of data and set them up so that if you click on one of the cells in the G column, a check mark appears and conditional formatting grays out the row in question. Is there a way to make the graph sensitive to the fact that that row is no longer to be considered?

Thanks ahead of time.

Bill Biggs

I used to use excel for matching graph data by dragging the data point to a
point on an existing line. Is this feature is no longer supported in 2007?
Is there a workaround or do i have to manually change the numbers?

I have a graph with data for this year and last year by month. The data for
last year by month has already been pre-populated and the data for this year
is added each month when it is available. Every month when I add the data for
this year, I have to manually expand the data range for last year so that my
year to date comparisons are kept in parity. Is there a way to update the
data range for last years data automatically when I add the data for the new
month this year?

Thank you.


What is the correct formula that will record the actual value retrieved
weekly from an essbase cube AND will not take the line graphed to zero
for future weeks? I've tried several formula options found on this
user group and thought I had it! I've resolved for the line graphing
to zero for future weeks when data is not yet available but now, the
formula I'm using does not pick up the current week's data.

Your suggestions are most welcome! Current formula is:

No luck finding an answer? You could always try Google.