Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Create a line in chart Results

Hello all,

I have about 50 columns I would like to make identical charts for, with different data based on the column. I am a VBA noob, and this is way beyond my abilities. So first off, I don't even know If it's possible.

However, I have a sneaking suspicion one of you smart fellows out there one excelforum.com knows how to solve my problem.

Anyways, back to my problem. I would like a code such that I can do it all once, and then "drag" the chart across to all the columns (i.e. have the chart code in a while loop for a range of columns). The attached sheet 2 posts down shows information on two tabs and shows the example chart something similar to what I would like. I would also like to position the chart and reletave to a cell in the coloumn while making the cahrt the same width as the column.

You will notice I used X error bars to make lines for my LCL,CL,and UCL control limits. I am sure there is a more elegant way to make a line from a single datapoint.

Thanks in advance!

Hi,

I'm trying to reproduce graphs with some vba code. However, I'm coming unstuck where the series' have automatic colours/styles set. While I understand that for each automated series Excel uses a different palette colour (starting with the Chart Fill colours along the bottom line of the palette), but I can't figure out where the index of the series is stored - i.e. which colour from the palette to use.

For example, if I have 3 series on a line chart then they're initially setup with automatic colouring and given the first 3 automatic colours...if I then delete series 2, the 3rd series still has the third colour...i.e. somehow Excel still knows that the series was created as the thrid series, even though there are now just two. And if you then add another series, it is given the 2nd colour in the automatic list. But searching through the series' properties, I cannot for the life of me see how Excel is recording what the index number for each series is.

Anyone any suggestions? Any help would be much appreciated.

Thanks,
Phil.

I have a requirement to develop a VB6 application, when I click a button the
application should automatically generate a Line Graph, Column Chart and a
Map on separate tabs of an Excel workbook using an existing cross-tab report
data (Report tab) from an Excel worksheet. I have installed MapPoint software
on my machine and I could see a MapPoint icon like Chart wizard in insert
menu.

Using Macro record feature and Chart Wizard I was able to develop an
automized code for creating Charts…Using MapPoint feature inherent to Excel
I was able to create a Map manually but I was not able to record any thing in
the Macro the moment I click on MapPoint Icon the macro recording stops. Can
any one help me in finding how to generate a Map from Excel cross tab report
using MapPoint Software? I appreciate all your help…thanks much in advance…

--
Have bugs free to have peace of mind!

I am in the process of teaching myself Visual Basic and programing in
Excel.

I have been modifying a program that pulls data from a spreadsheet and
creates a set of bubble charts. The original program was dealing with
4 categories of data. I had added an additional two.

This worked out fine for creating the categories and inputing data into
a spreadsheet via a form. But in the subroutine to create the chart I
hit a bug. I copied and modified some lines of code that I don't
entirely understand, and not surprizingly the subroutine will not run.

The portion of the code that has me stumped is below. The jj in the
comments is me just so I can keep track of the comments I was putting
in while I figured out the program

The code difines objects - shapes - rectangles with a number (11-14).
Then it applies methods to to them. I had added. I added 15 and 16.
The editor did not recognize these objects. Probably because they
either don't exist in the chart or they are not defined anywhere. My
problem is that I have looked everywhere trying to find these shapes
and have pretty much hit a wall. The documentation has not been
helpful.

Can anyone point me in the right direction? Where should I be looking
for these rectangle shapes, and how can I define/create the additional
two I need to chart the two other data categories I have created?

Thanks in advance.

Jon

--------------------------The
code----------------------------------------

'To show the legend if the type of report is all
If strType = "All" Then
'this is for a full report "All"
Charts(conChartName).Shapes("Group 16").Visible = True 'not
sure what Group 16 is JJ

'jj makes shapes visible
Charts(conChartName).Shapes("Rectangle 11").Visible = True
Charts(conChartName).Shapes("Rectangle 12").Visible = True
Charts(conChartName).Shapes("Rectangle 13").Visible = True
Charts(conChartName).Shapes("Rectangle 14").Visible = True
'Charts(conChartName).Shapes("Rectangle 15").Visible = True 'JJ
added for SVC
'Charts(conChartName).Shapes("Rectangle 16").Visible = True
'JJ added for SVC

'jj these set a group of shapes on the chart and associate
them with the security imperatives defined in cells BF1-6 on the
projects sheet
'which are set from frmInvSetup It provides the caption on
the chart key
Charts(conChartName).Shapes("Rectangle 11").DrawingObject.Text
= Sheets("Projects").Range("BF1").Cells
Charts(conChartName).Shapes("Rectangle 12").DrawingObject.Text
= Sheets("Projects").Range("BF2").Cells
Charts(conChartName).Shapes("Rectangle 13").DrawingObject.Text
= Sheets("Projects").Range("BF3").Cells
Charts(conChartName).Shapes("Rectangle 14").DrawingObject.Text
= Sheets("Projects").Range("BF4").Cells
'Charts(conChartName).Shapes("Rectangle 15").DrawingObject.Text
= Sheets("Projects").Range("BF5").Cells 'JJ added for SecureValueChain
'Charts(conChartName).Shapes("Rectangle 16").DrawingObject.Text
= Sheets("Projects").Range("BF6").Cells 'JJ added for SVC
Else
Charts(conChartName).Shapes("Group 16").Visible = False
Charts(conChartName).Shapes("Rectangle 11").Visible = False
Charts(conChartName).Shapes("Rectangle 12").Visible = False
Charts(conChartName).Shapes("Rectangle 13").Visible = False
Charts(conChartName).Shapes("Rectangle 14").Visible = False
'Charts(conChartName).Shapes("Rectangle 15").Visible = False
'JJ added for SVC
'Charts(conChartName).Shapes("Rectangle 16").Visible = False
'JJ added for SVC

End If

I am trying to use a macro to make a line graph of certain cells within
my worksheet (Excel 2004:mac). The data will always be 143 rows. I
want to be able to select the starting cell, and have the macro
automatically select the 142 rows below it and create a line graph. I
have tried using relative referencing within my macro, but the graph
that is created always results in my original selected data being
graphed. I made sure relative referencing is selected. What am I
doing wrong?

This is the code that is generated from my macro:

Sub Macro4()
ActiveCell.Range("A1:A143").Select
Charts.Add
ActiveChart.ChartType = xlLine
ActiveChart.SetSourceData Source:=Sheets("data").Range("G79:G221"),
PlotBy _
:=xlColumns
ActiveChart.Location Where:=xlLocationAsNewSheet
With ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
End Sub

I found an earlier thread that talked about this same situation but I
could not get it to work. This was the code that was suggested:

Sub Macro1()

Dim r As Range
Dim s As String
Set r = ActiveCell.Resize(10,0)
s = ActiveCell.Parent.Name

Charts.Add
ActiveChart.ChartType = xlLineMarkersStacked
ActiveChart.SetSourceData Source:=r
ActiveChart.Location Where:=xlLocationAsObject, Name:=s

End Sub

I got a syntax error when trying this out...

Thanks for the help.

Hello,

I'm trying to extract the coefficients from the text box created when doing a trendline on an excel chart. I've attempted 2 solutions from this forum, neither of which I can get to work.

(a) I've tried Dave Braden's VBA code that creates function TLcoef, but it returns a "value" error. I believe I've fixed the word wrap issues, but I'm not a VBA programmer, so can't be sure. I've also tried entering it as an array formula, to no avail. I've pasted Dave's code at bottom of this post.

(b) I've also tried Tom Ogilvy's macro solution. this "almost" works. the problem is that my equation has a negative coefficient, and his code does not account for negative's, only positives. I've modified the code slightly, so that the negative coefficient is listed in the spreadsheet, but I can't figure out how to preserve / display this value as a negative. in other words, all the coefficients are assumed positive. the code mod I made is to simply add the line :

sFormula = Application.Substitute(sFormula, " - ", ",")

Any help would be appreciated.

Thank You,
=========================================
Const cFirstNumPos = 5 ' pos. of first integer in displayed eqn
Const cMaxFormat = "0.00000000000000E+00"

Function TLcoef(vSheet, vCht, vSeries, vTL)

'Return coefficients of an Excel chart trendline, *to precision displayed*
'
'Note: While Trendline seemingly always reports subsequent terms from
'a given one on, sometimes it reduces the order of the fit. So this function
'returns, for a poly-fit, an array of length 1 + the order of the requested fit,
' *not* the number of values displayed. The last value in the return array
'is the constant term; preceeding values correspond to higher-order x.

Dim o As Trendline

Application.Volatile
If ParamErr(TLcoef, vSheet, vCht, vSeries, vTL) Then Exit Function
On Error GoTo HanErr
Set o = Sheets(vSheet).ChartObjects(vCht).Chart.SeriesCollection(vSeries).Trendlines(vTL)
TLcoef = ExtractCoef(o, cFirstNumPos)
Exit Function

HanErr:
TLcoef = CVErr(xlErrValue)
End Function

Function TLeval(vX, vSheet, vCht, vSeries, vTL)
'DJ Braden
' Exp/logs are done for cases xlPower and xlExponential to allow
' for greater range of arguments.
Dim o As Trendline, vRet

Application.Volatile
' If Not CheckNum(vX, TLeval) Then Exit Function
If ParamErr(TLeval, vSheet, vCht, vSeries, vTL) Then Exit Function

Set o = Sheets(vSheet).ChartObjects(vCht).Chart.SeriesCollection(vSe*ries).Trendlines(vTL)

vRet = ExtractCoef(o, cFirstNumPos)
Select Case o.Type
Case xlLinear
vRet(1) = vX * vRet(1) + vRet(2)
Case xlExponential 'see comment above
vRet(1) = Exp(Log(vRet(1)) + vX * vRet(2))
Case xlLogarithmic
vRet(1) = vRet(1) * Log(vX) + vRet(2)
Case xlPower 'see comment above
vRet(1) = Exp(Log(vRet(1)) + Log(vX) * vRet(2))
Case xlPolynomial
Dim l As Long
vRet(1) = vRet(1) * vX + vRet(2)
For l = 3 To UBound(vRet)
vRet(1) = vX * vRet(1) + vRet(l)
Next
End Select
TLeval = vRet(1)
Exit Function

HanErr:
TLeval = CVErr(xlErrValue)
End Function

Private Function ExtractCoef(o As Trendline, ByVal lLastPos As Long)
Dim lCurPos As Long, s As String

s = o.DataLabel.Text

If o.DisplayRSquared Then
lCurPos = InStr(s, "R")
s = Left$(s, lCurPos - 1)
End If

If o.Type <> xlPolynomial Then
ReDim v(1 To 2) As Double

If o.Type = xlExponential Then
s = Application.WorksheetFunction.Substitute(s, "x", "")
s = Application.WorksheetFunction.Substitute(s, "e", "x")
ElseIf o.Type = xlLogarithmic Then
s = Application.WorksheetFunction.Substitute(s, "Ln(x)", "x")
End If

lCurPos = InStr(1, s, "x")
If lCurPos = 0 Then
v(2) = Mid(s, lLastPos)
Else
v(1) = Mid(s, lLastPos, lCurPos - lLastPos)
v(2) = Mid(s, lCurPos + 1)
End If

Else 'have a polynomial
Dim lOrd As Long
ReDim v(1 To o.Order + 1) As Double

lCurPos = InStr(s, "x")
If lCurPos = 0 Then
v(o.Order + 1) = Mid(s, lLastPos)
Exit Function 'with single constant term
End If
'else
lOrd = Mid(s, lCurPos + 1, 1)
Do While lOrd > 1
v(UBound(v) - lOrd) = Mid(s, lLastPos, lCurPos - lLastPos)
lLastPos = lCurPos + 2
lCurPos = InStr(lLastPos, s, "x")
lOrd = lOrd - 1
Loop
'peel off coeffs. for affine terms in eqn
v(o.Order) = Mid(s, lLastPos, lCurPos - lLastPos)
v(o.Order + 1) = Mid(s, lCurPos + 1)
End If
ExtractCoef = v
End Function

Private Function ParamErr(v, ParamArray parms())
Dim l As Long
For l = LBound(parms) To UBound(parms)
If VarType(parms(l)) = vbError Then
v = parms(l)
ParamErr = True
Exit Function
End If
Next
End Function
=========================================

I have the following code which produces a line graph, exports the chart to a pic, and then displays the picture...this works great; however, when I try to change the chart type it poops out saying:

Error Type:
Microsoft VBScript runtime (0x800A000D)
Type mismatch
/kfc/welcome.asp, line 281

This is due to the fact, I am assuming, that I am trying to display the incorrect data to a graph...or vise versa. My code is here:

<%Sub HandleRepeatVisit()
	Dim ExcelApp 	' Our Excel App
    Dim WorkbookApp 		' Our Workbook within the Excel App
    Dim WorksheetApp		' Our Worksheet within the Workbook	
    Dim ChartApp 	' The chart object
    Dim SourceRangeApp ' The Source Range for the chart object
	
    Const xlWorkSheet = -4167 
	Const xlChartType = 1
	
		' -- Create an instance of Excel Application
    Set ExcelApp=Server.CreateObject("Excel.Application")
    ' -- Create a new workbook
    Set WorkbookApp=ExcelApp.Workbooks.Add(xlWorksheet)
	' -- Grab the first worksheet of the new workbook
    Set WorksheetApp=WorkbookApp.Worksheets(1)
	' -- Insert the data the user requested
	' -- First, the title
    WorksheetApp.Range("A1").Value = "Category Percentages" 
	' -- Then the data in two vertical columns
        WorksheetApp.Range("A2").Value = "C"
        WorksheetApp.Range("B2").Value = "=" & CPercentage  
        WorksheetApp.Range("A3").Value = "H"
        WorksheetApp.Range("B3").Value = "=" & HPercentage
        WorksheetApp.Range("A4").Value = "A"
        WorksheetApp.Range("B4").Value = "=" & APercentage
        WorksheetApp.Range("A5").Value = "M"
        WorksheetApp.Range("B5").Value = "=" & MPercentage
        WorksheetApp.Range("A6").Value = "P"
        WorksheetApp.Range("B6").Value = "=" & MPercentage
        WorksheetApp.Range("A7").Value = "S"
        WorksheetApp.Range("B7").Value = "=" & SPercentage
        WorksheetApp.Range("A8").Value = "Score"
        WorksheetApp.Range("B8").Value = "=" & RoundedActualScorePercentage
' -- Set our source range	    
    Set SourceRangeApp = WorksheetApp.Range("A2:B8")
	' -- Create a new Chart Object
    Set ChartApp = WorksheetApp.ChartObjects.Add(20, 20, 300, 200)
	' -- Generate the Chart using the ChartWizard
	' -- Syntax is: 
	' -- crt.Chart.ChartWizard Source:=SourceRange, gallery:=x1Line(4), PlotBy:=xlColumns(default), _
	' -- categorylabels:=1, serieslabels:=0, HasLegend:=2, Title:="Test"
	
	 ChartApp.Chart.ChartWizard SourceRangeApp , 4, , 2, 1, 0, 2, Session("username") & " Category
Averages"
    ' -- Configure the Chart 
    ChartApp.Chart.ChartType = xlChartType 
    ChartApp.Chart.SeriesCollection(1).Name = "=Sheet1!R1C1"
    ChartApp.Chart.HasTitle = True
    ChartApp.Chart.Axes(1, 1).HasTitle = True
    ChartApp.Chart.Axes(1, 1).AxisTitle.Characters.Text = "Tests"
    ChartApp.Chart.Axes(2, 1).HasTitle = True
    ChartApp.Chart.Axes(2, 1).AxisTitle.Characters.Text = "Values"
	
	' -- Determine the name to save this chart as. Use the current Seconds value, overwriting previous
	' -- ones	
	FileName =  "test" & Second(Now()) & ".jpg"
	' -- Save the chart on web server 
    ChartApp.Chart.Export Server.Mappath("kfccharts") & "" & FileName, "jpg"
	' -- Fool Excel into thinking the Workbook is saved
    WorkbookApp.Saved = True
	' -- Set all objects back to nothing
    Set ChartApp = Nothing
    Set WorkbookApp = Nothing
	' -- Quit Excel to conserve resources
    ExcelApp.Quit
    Set ExcelApp = Nothing
	' -- Make sure the Image is not cached but is loaded fresh from the web server
	Response.AddHeader "expires","0"
	Response.AddHeader "pragma", "no-cache"
	Response.AddHeader "cache-control","no-cache"
	
End Sub
%>
Code to notice is Const xlChartType = 1...this produces an area line graph, if I change it to 4, then it produces a regular line graph. If I change it to 2, which should be a bar graph, then I get that error...the line it points to is:
line 281 is ChartApp.Chart.ChartType = xlChartType

What am I doing wrong and what do I need to change? I am thinking it is this line:

ChartApp.Chart.ChartWizard SourceRangeApp , 4, , 2, 1, 0, 2, Session("username") & " Category Averages"

but, I am not sure...help!

Hi,

I have a chart that is made up of dates (4/15/11, etc.) and a list of values. I can create a chart with this data - no problem. My issue is that Excel is including "in between dates" along the X axis of the chart. I want to only see the dates that have corresponding data.

For example, if my table has two dates, there should only be two values along the X axis.

I would also very much like to have vertical lines at those date points if possible.

Any pointers would be much appreciated!

Thanks!

Good Afternoon,
I am creating a progress chart in an excel dashboard which shows current progress of a project and whether or not it is in front of or behind schedule.

My table is as follows

A B C D
Project name Target Completion Current Completion Today
Date

I have a number of projects each with a name, a taret completion date (an integer representing a month), a current completion date (an integer representing a month) and todays date (an integer as a month, using the
 command) which remains constant for all projects

Currently I have a blue bar representing column B, and a green bar representing current progress. I have formatted the "todays date" series so it is invisible and added a trendline which appears as a vertical red line down all of the graph.

What I want to do is shade the plot area from the left of the graph up to this point to represent the time that has passed from the start of the year to today.

I think this

HTML Code: 
 is close

Any suggestions?

Good morning. =)

I am trying to create a combination chart in Excel for copying into PowerPoint for a presentation.

There are three data series: projected quarterly sales, actual quarterly sales, and monthly sales. The first two are depicted as bar chart; the monthly sales are a line graph. I was able to edit the data source by to get a multi-level category axis.

I would like for the bars to correspond only to the appropriate quarterly mark on the horizontal axis, but they appear above a particular month in that quarter due to their positioning on the spreadsheet. How do I tweak this chart so the bars correspond only to quarterly labels on the multi-level category axis and the line corresponds only to the monthly labels?

My file is attached. Thanks! =)

I created a chart in excel 2002, I included text box and arrows that pointed
to different parts of a line graph. It looks fine on the screen but when you
go to print preview the arrows are pointing to the wrong place. It also
prints the messed up print preview version.

Please help!

I want to create a stock chart (with 3 series) and then add another serie in
form of line. Is this possible and how?

--
Gordana Jankovska
Head of Controlling & Budgeting

TITAN Group - Cementarnica "USJE" AD Skopje

Prvomajska bb, 1000 Skopje

tel: +389 2 2786 138
fax: +389 2 2782 535
e-mail: gordanaj@usje.com.mk

I have my tables which are reguarly added too but it is a pain going through the 20 odd charts updating the data range.

What i'd like to do is just select a huge area for the data range so i don't have to touch it again and the charts would auto update. The problem is i have told it to ignore blank cells but it still creates them on the chart space.

Is there any way to select a heap of cells but have it completely ignore the empty ones until there is data entered in them.

Hello all,

I'm looking for suggestions on the best way to go about getting the
information I want to display in a chart. Depending on the response, I may
also need help implementing the suggestion. Here's what I want to do:

On Sheet3 I have a range of data, for example:

Columns
A B C D
20-Sep R Y G
21-Sep Y n/a R
22-Sep n/a G Y
23-Sep R R n/a

On Sheet7, I want to have 16 line graph charts that:
1) X axis = Column A

2) Y axis to be a label that says, "Red, Yellow, Green" but the actual data
to be based on the range of Column B, C, D, etc.

3) In my example above, I have 3 columns of data. In my real spreadsheet
there are 16 columns of data. I will have a separate chart based on each
column.

So for example,
one line chart with the X axis = Column A and Y axis = Column B,
then a second chart with the X axis = Col A and Y axis = Col C, etc.

But, I only want to plot dates with R, Y, or G. If there is an N/A in
Column C, I don't want to plot that date in the line chart. Likewise for the
rest of the charts. So based on my sample data above, I would have my first
chart like this:

X axis = Column A
Y axis = Column B
Y axis label = "Red, Yellow, Green"

Green |
|
Yellow | x
|
Red | x x
_____________________________________________
9/20 9/21 9/23

Here's what I'm currently doing:

On Sheet3:
In column BA I have the formula =A1 to get the date.
In column BB, BC, BD, etc. I have the formula
=IF(B3="R",1,IF(B3="Y",2,IF(B3="G",3,"")))

On Sheet7 I have a line chart:
The Y axis = scale 1-4 with Word Art placed at Red = 1, Yellow = 2, and
Green = 3.
The X axis = Sheet3!BA1:BA400
The first series = Sheet3!BB1:BA400

This plots the dates on the X axis and if a date has a G, then it plots the
marker for the date at the Y axis 3 gridline, if the date has a Y it plots it
at the 2 gridline, or if it has a R, it plots the mark at the 1 gridline.
But if there was an n/a, from the original data set, my IF statement ignores
that and leaves a blank in the range. Then when the chart sees a blank, it
plots a 0 for that date and my line graph takes a dive. So I want to skip
the dates with blanks or n/a's from the original data set in my graph.

Is there an easy modification to what I'm currently doing or is there just
simply a more efficient way to go about this? I'm a little afraid to create
a chart in VB since I don't have any experience doing that, but not to say if
someone told me how to do that I wouldn't try it.

Thanks for any input,

Sharon

Hi all,

I'm doing some IT coursework and have a macro that creates a chart from some
data in a worksheet. All works well until you run the macro again - now it
says the chart doesn't exist... I've sinced looked at it in visual basic, the
highlighted line says to select "Chart 22" my guess is that every time I run
this macro it gets bigger by one, but even so, it now has the wrong name, and
the macro crashes :'(

Anyone know how I can set each newly created chart to be called "Chart 22"
or whatever else I tell it to look for?
Please?

Hi,

I created a Vertical bar chart with percentage on the right or as my secondary axis. I have to datasets referencing the secondary axis.

For whatever reasons, I cannot fine one of these datasets, which is suppose to be a straight line going across the chart & representing the goal - in this case 90% to reach.

How to find & unhide this dataset?

Thanks

Hi all,
I'm trying to make a chart in excel 2007.
I want a bar chart and then overlaying that, I want a line that is on a different scale.
The tricky part is that I don't want the line to start at 0, is this possible?
I have drawn a picture of what i'm trying to create and attached it
Many thanks!

Hi,

I'm creating a Pareto Chart.

I have a primary data table that totals up total defects & graphically displays this through the data table & vertical bars on my primary axis.

I have a secondary axis that I would like to display the defect types or products as lines. I don't want these in my primary data table.

Is there a way to make another or second legend to display my secondary data collection? I don't need to include a data table. I attached a picture for reference.

Thanks

Hi guys would appreciate any help,

I am constructing a line chart and one of my data series has 2 values which fall in the same part on the x-axis.

one of my x labels is '95%' and there are two values which fall in this value, so i need two points. One is a higher value on the y axis so it will create a straight line.

How do i do this on excel???

Many thanks

James

*using excel 2007

Hello everyone,

my first post here, and I'm in with a bone question straightaway, but I hope you can help.

I have 2 columns, one for altitude, going from 0 - 25000' in 5000' increments, and another for the absolute temperature, in K, falling from 288.15 to 238.65 linearly, iaw an equation for temp vs altitude which I am happy to say I managed without difficulty.

What I want to achieve, is a chart with Altitude on the 'y' axis and Temperature on the 'x' axis, and a plot of the temperature falling with increasing altitude - ie a negative gradient line.

What I actually get when I go through the chart wizard is as follows:

http://i290.photobucket.com/albums/l...ipps/Graph.jpg

where as you can see, the x axis appears to be some random control numbers from 0 - 6 and the y axis is altitude. However, the temp is also plotted wrt to the same y axis scale as the altitude, which means (due to the small temp numbers in volved) that the temp line is more or less flat across the bottom of the chart.

I have tried all sorts of messing around to get this to work, and I know now that I am missing something - probably to do with the original layout of the data and the way it is then selected to create a chart from.

Please help - I need this skill for a new job and even the Excel tutor on my level 2 course today couldn't help me!

Thanks in advance,

Russ


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