Free Microsoft Excel 2013 Quick Reference

Excel plotting graphs Results

Hi,
I'm using Excel 2003 on Windows XP(SP2). I currently plot a load of data (values over a time period) in a Stacked Area type graph. I'm now being asked if I can "grab" a slice of the area graph and drag it to the right to see what effect it has on the overall shape/top line.
Effectively, I want to do some scenario visualisation using the graph view.
I'm stumped!

Any suggestions?

Many thanks

Hey guys, I'm new to this forum, so I hope you can help me out

I am currently doing my chemistry coursework, and I am drawing graphs for my data, but I have encountered a few problems:

1) When I convert my scale to logarithmic, the y axis moves to the right, and the grid lines become all skewed. I have attached a picture of what I mean.

2) When I create a bar chart with many names, some of the names are missed out!

3) Our graph has to cover more than half the page. However, I find that when I stretch my graph in word, they become too blurry, and when I make the graph bigger in Excel, the points I am plotting become smaller and harder to see. So would there be a way to preferably make the graph fit the whole page and yet make the points easy to read?

4) Also, the point markers are diamonds, but is it possible to change it to a cross?

Thanks for all your help

P.S I have attached a few examples of the problem.

Graph problems.doc

Hello,

I have a requirement in which I need to plot the average and the Standard Deviation (Error bar) in the same graph. I am using Excel 2010.

I am making a line graph. I am able to plot average easily but with standard deviation, I am facing problems.

For every point on the line graph, I have different standard deviation and when I select "Custom" on the Standard Deviation Options and I specify the range of SD values for each point, I get the SD lines of the same size and they are so huge that it crosses the chart itself. I am sure I can't select "Fixed", "Percentage" as each of the SD value is different corresponding to each point (average value) on the line graph.

Any help on this will be highly appreciated.

Thanks!

EDIT: Sample File Attached - As you see in the chart, the line graph is plotted based on the "Average" values, I need to plot SD on those points as well.

I'm trying to plot salary ranges in to a graph but don't know how to input info into excel

ex. Less than 1 year $24,417 - $32,938

PLEASE HELP!

I'm trying to plot salary ranges in to a graph but don't know how to input info into excel

ex. Less than 1 year $24,417 - $32,938

PLEASE HELP!

I'm trying to make a graph with a y-axis that has both positive and negative values and I cannot get the graph to display any of the major axis marks nor the fixed maximum and minimum.

The graph is a scatter xy plot with values ranging from -1*10^-19 to 1*10^-19. I have no idea how to get excel to display the max and min values nor do I know how to get it to display any of the major tick marks. At this point I would honestly be fine with just the max and min values. I can get the min to display but not the max.

Can anyone help me? I have attached the file in question.

Hi,

I have a macro that I wrote in 2003 but gives me an error (Run-time error '1004' Paste method fail) when I try to run it in in 2007 or 2010. I'm really not sure why. I'm basically creating a bunch of charts that comes from a varying amount of data. I have also attached a workbook so you can see what I am doing, The part where the error is thrown is wsCharts.Paste. Please any help is greatly appreciated.

Variables
'wsCharts and wsData are the two worksheets
' conSpacing is the constant for the spacing between graphs (20 rows)
' this is based on how excel creates graphs on my machine, it changes if
' excel isn't full screen
' i and j are just counters
' LastRow is the final data entry in the table in column C

    Dim wsCharts As Worksheet
    Set wsCharts = Worksheets("Charts")
    'Dim wsCUM As Worksheet
    'Set wsCUM = Worksheets("***")
    Const conSpacing = 20, conStartInRow = 6
    'Dim i, j, LastRow As Long
    
'Delete all existing charts on the page

    wsCharts.ChartObjects.Delete

'Find the last data entry in column C

    With wsCUM
        LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
    End With
    
'Add a blank chart
'Linemarkers is the chart type
'set a dummy data range, and tell the graph to plot by rows
'Put the chard in the charts worksheet
'Tell the sheet it has a title
'Set the x and y axis as Date and Hours
'Cut the sample chart

    Charts.Add
    ActiveChart.ChartType = xlLineMarkers
        ActiveChart.SetSourceData Source:=wsCUM.Range("E6:BE10"), PlotBy:= _
        xlRows
    ActiveChart.Location Where:=xlLocationAsObject, Name:=wsCharts.Name
    With ActiveChart
        .HasTitle = True
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Date"
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Hours"
        
         End With
         
         With ActiveChart.Axes(xlCategory)
        .MinimumScaleIsAuto = True
        .MaximumScaleIsAuto = True
        .BaseUnitIsAuto = True
        .MajorUnit = 14
        .MajorUnitScale = xlDays
        .MinorUnit = 7
        .MinorUnitScale = xlDays
        .Crosses = xlAutomatic
        .AxisBetweenCategories = True
        .ReversePlotOrder = False
    End With
    ActiveChart.Axes(xlCategory).Select
     With Selection.TickLabels
        .Alignment = xlCenter
        .Offset = 100
        .ReadingOrder = xlContext
        .Orientation = xlUpward
    End With
    
    With Selection.Border
        .Weight = xlHairline
        .LineStyle = xlAutomatic
    End With
    With Selection
        .MajorTickMark = xlOutside
        .MinorTickMark = xlInside
        .TickLabelPosition = xlNextToAxis
    End With
  
  With ActiveSheet.ChartObjects
  .Width = 650
  End With
  
  
    wsCharts.ChartObjects.Cut
    
'For 1 to the number of groups of four

    For i = 1 To (LastRow - conStartInRow) / 4
    
'If it is the first i, select cell A1, else select the cell according to the spacing
        
        If i = 1 Then
            wsCharts.Cells(1, 1).Select
        Else
            wsCharts.Cells((i - 1) * conSpacing, 1).Select
        End If
        
'Paste the chart to this location

        wsCharts.Paste
        
'Set the series 1 to 4, according to the 4 data ranges in the table
        
        For j = 0 To 3
    
            ActiveChart.SeriesCollection(j + 1).XValues = "=" & wsCUM.Name & "!R6C5:R6C57"
            
            ActiveChart.SeriesCollection(j + 1).Values = "=" & wsCUM.Name & "!R" & _
                                (i * 4) + (conStartInRow / 2) + j & "C5:" _
                                & "R" & (i * 4) + (conStartInRow / 2) + j & "C57"
        
            ActiveChart.SeriesCollection(j + 1).Name = "=" & wsCUM.Name & "!R" & _
                                    (i * 4) + (conStartInRow / 2) + j & "C3:" _
                                    & "R" & (i * 4) + (conStartInRow / 2) + j & "C4"
            
        Next

'Give the chart a title of the first label of the data series

        With ActiveChart
            .ChartTitle.Characters.Text = wsCUM.Cells(i * 4 + conStartInRow, 3)
        End With
    Next


I have (what I hope) is a simple Excel charting problem. I have several simple bar graphs in Excel 2007, one graph for each day of the week, and the data plotted on each daily spans many weeks. So let's take the Tuesday graph as an example -- see data below:

Criteria Day Date Tot Msg Vol (A+B) A B
0 Tues 09/21 59206 0 59206
0 Tues 09/28 57295 0 57295
0 Tues 10/05 57717 0 57717
0 Tues 10/12 84918 0 84918
New CDRPTues 10/19 85903 9 85894
0 Tues 10/26 64854 0 64854
0 Tues 11/02 58646 0 58646
0 Tues 11/09 60161 0 60161
0 Tues 11/16 60353 0 60353

So based on the above data, I have successfully created a graph with one color (green) for each bar, and each bar represents each date. Now what I want to do is to have a different color (yellow) for each date where the Criteria is "New CDRP", while leaving all other dates' bars as green. Any suggestions as to how I can easily do this? I know very little VBA, so therefore I am hoping for a solution that doesn't utilize VBA/macros...unless that is my only option. Any help anyone can give is appreciated.

Hello All,

I have 221442 rows of data in excel spreadsheet but I cannot create a graph. Excel pops up a message that the maximum number of data points you can use in a data series for a 2-D chart is 32,000. But I want to visualize the all data points on same graph. Is there a way to plot the large number of data sets ?

Thank you.

Hi

I have very little knowledge with charting in excel and I hope to find the solution simple enough

I have done a bit of searching for side by side comparison graphs and I can not really determine how to get the desired result

Attached is a workbook illustrating what I would like to achieve

It is basically a side by side comparison chart (scatter chart) as well as the average displayed of two test results

I have seen some ways of producing zero or fake values in between the data chart to create space between the graphs, but i'm pretty clueless.

I would appreciate it if somebody could shed some light or even provide a useful link to help me out

Thank You

I made a graph in Excel which contains some numbers vs. texts. I managed to changed the color of numbers on y-axis but how can I make the texts on the x-axis colorful ? Seek detail answer, plz.

Basically iv made a graph - concentration vs absorbance values (dont know if it means anything to you)

i then put a trend line in
then i want to put another series of data (a set of average means) onto the graph but... just for the y axis (so just for absorbance values) then, using the trend line i can work out what the concentration would be (theoretically)
so my question is how do i plot the means onto the graph.

Thanks for any help
and apologies for the alwful explanation, new to excel so pretty much useless

Dear all I hope you can help me - because my boss will eat me tomorrow if you can't!!!

I am having a bit of trouble in Excel 2007 using named data ranges. I have attached a small file to explain the problem - i expect one of you will be able to solve the problem very quickly and easily.

Basically, I want to plot the graph included on the right (which i plotted manually) using the named data labels which I have created (see name manager). However, I do not seem to be able to make any progress.

The named ranges are intended to plot all the values in the Power output bins
column and the corresponding values in the Values for plotting column. The number of points that need to be plotted changes as the user alters the parameters in the top left hand corner - therefore I need to resort to named data lables/ranges. However, I am having the following problems:

1) The named labels do not seem to work/select the correct ranges

2) I am not sure how I make the graphs plot the named/labelled data ranges - do I have to use a special syntax in the chart select data section? Could someone provide me with a list of idot proof steps?

If anyone out there could help me I would be extremely grateful!!! I really need to get this done tonight, so a speedy response would be even more welcome.

Thank you so much in advance,

Sam

The Situation:
We graph data from data files in a Visual Basic 6 app by creating an Excel application object and having it generate the 2-D line chart. This application was developed about 6 years ago and the engineer who originally wrote it is long gone, so I have inherited it.

It has worked fine until we tried to use it on Excel 2007. We go with a lot of the default chart settings. When it was written, Excel 97 and Excel 2000 were current and it still works fine with Excel 2003. But now with Excel 2007, some of the default settings seem to have changed. For example, the chart title's default color has changed from black to white. I've solved that problem by explicitly setting the color to black, thus overriding the new default.

It's the other change that I can't get a handle on. The plot line itself used to be thin and black, but now it's thicker and blue. Blue isn't so much a problem, but we want to make it thin again so as to not hide the details.

The Visual Studio 6 help files provide no information on working with Excel objects. I have been using the Excel 2003 help files to see what objects are available, but when I try most of what it provides VB6 chokes on it. Here's the latest that I've been able to find and try:

    With wkbExcel.ActiveChart
      .ChartType = xlXYScatterLinesNoMarkers
      .SetSourceData Source:=wkbExcel.Worksheets(1).Range(strSourceData), _
                     PlotBy:=xlColumns
      GoSub AdjustProgress
      .Location Where:=xlLocationAsNewSheet
      .HasTitle = True
      .Shapes.SelectAll
      sr = Selection.ShapeRange
      sr.Line.ForeColor.RGB = RGB(255, 0, 0)
sr is Dim'd as ShapeRange, as per the help file. It chokes on the Selection.ShapeRange, which was also written precisely as per the help file.

BTW, it's just for test purposes that I'm trying to set the line to red, so that I can see whether the change I'm trying to make does actually appear. Of course I'll change it back to black once I'm there.

afternoon

i have the following problem (we do this long hand on graph paper)

we take some readings and then plot a graph then slect the two highest points and draw a line through them (with all of the remaining points under the line) then we record the intersection point on the y axis and then calculate the gradient

i have attached a sketch which should help

i would like to automate this into a excel spreadsheet, i can basically do the simple graph but cant plot the line, intersection point or the gradient

any help would be appreciated

thanks

Hi

As part of some courseowrk I have been observing cells in culture and plotting their movements as simple x-y co-ordinates. I have ploted the data on a scatter graph in excel which shows the tracks of the movement of each cell.

However my tutor wants me to show the directionality of the cells by plotting them from the same point of origin to create a kind of "sunburst" effect - do all the cells splay out in different directions or are they all moving the same way towards something?

The data I have is the x and y co-ordinates and the distance each cell has travelled between all the co-ordinate points.

Is this even possible? Hope someone can help
Regards,
Nik

I want to take a data series and plot the mean and standard deviation on a
bar graph. How do I do this in excel?

Hello -

Could someone please manipulate a data set for me? I received some valuable instructions on this thread http://www.ozgrid.com/forum/showthread.php?t=152022&goto=newpost** but I am not able to get the result when i do it.

I would like to offer £10 if someone could do the following:
1. Make sure the dates in Column B/C are 'true dates' / numbers and right aligned
2. Make sure the prices in Column E are recognised as numbers too, rather than text
3. There are multiple entries at the same price for many dates. 25/9/2008 has 264 entries at the same price (3.07), for example. Could these please be condensed into one cell per date-price pair, with the corresponding number of allowances aggregated?
4. Eliminate any lines where the price is returned 'N/A'

In the end I would like to plot a price vs date scatter graph in a program called Stata, which is why I would like to do the above.

Rather than instructions, I would appreciate it if someone who knows Excel could do the above for me on the spreadsheet. It is approx 900kb and so I am not able to attach it here. I offer £10 because I think this should be quick if you're an Excel whizz, but please let me know if I'm wrong.

Many thanks

Please help me to develop macro VBA code to plot multiple graph by loading excel sheet data. The data to be used are highlighted with red color. The date column to be used as x-axis and other column data as y axis. By selecting one parameter from cell column.

I have created some graphics in a loop to plot data for different locations. As part of the graphics, I have inserted a text box and I want the contents of the text box to vary through each loop so that it says a different location per loop. e.g. say "London" for first loop and "Hendon" for second loop.

I want to run this each day and when I switch on my PC and open Excel and try and run my macro I get the run-time error 438: Object doesn't support this property or method. When I re-run the macro straight away (altering nothing) it then works fine. So it appears that when I run it the second time, Excel/VBA has the information it requires. How do I make sure it has all the information when I first open Excel?

Here are the necessary parts of the code:


	VB:
	
 
 
For k = 1 To 2 
     
    If k = 1 Then 
        t = "London" 
         
    ElseIf k = 2 Then 
        t = "Hendon" 
         
        EndIf 
         
        Sheets("Graphs").Activate 
        ActiveSheet.Shapes("Text Box 1").Select 
        Selection.Characters.Text = "Daily Report for " & t   ***PROBLEM LINE 
         
        With Selection.Characters(Start:=1, Length:=23).Font 
            .Name = "Verdana" 
            .FontStyle = "Regular" 
            .Size = 9 
            .Strikethrough = False 
            .Superscript = False 
            .Subscript = False 
            .OutlineFont = False 
            .Shadow = False 
            .Underline = xlUnderlineStyleNone 
            .ColorIndex = xlAutomatic 
        End With 
         
    Next k 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I'm sure it is a straight forward simple problem - but I can't solve it - so any help would be great.


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