Free Microsoft Excel 2013 Quick Reference

- Making graphs dynamic
- Just a couple of Excel Graph Problems :P
- Plotting Mean and SD on the same Line graph
- How do you plot a range on a bar graph?
- How do you plot a value range on a bar graph...
- Graph axis problem
- Pasting (charts) error from 2003 to 2007 (and 2010)
- Bar Graph Problem
- How to plot the data if I have more than 32,000 points ?
- Side by Side comparison scatter plot
- Coloring the text on x-axis of a plot in Excel
- Stuck with plotting a graph!
- Using named data ranges in charts (Excel 2007)
- VB6 & Excel 2007: Setting Color of Plot Line in Chart
- Graph problem
- Tracking movement on a graph
- How do I plot mean and standard deviation in excel
- Dynamically chart columns of data
- Error 438 - automating text in a text box

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

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

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.

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

PLEASE HELP!

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

PLEASE HELP!

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.

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

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.

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.

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 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

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

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.

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

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

bar graph. How do I do this in excel?

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

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

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