Auto Generate Graphs in Excel

Asked By toni boyer 0 points N/A Posted on - 11/29/2011

Please enumerate the steps on how to auto generate graphs in Excel that will display the results found during a test.

The number of issues and type of issues found in the test should also be seen on the auto generated graph.

Is this possible?

• Status: Open
• Question Views: 2391
• Answer Count: 2
• Vote Up 0 Vote Down

• Answer Accepted: Yes
• Question Category: MS Excel

Best Answer by worshiper2012

Go To Solution

Best Answer

Best Answer

Answered By worshiper2012 0 points N/A #105753

Auto Generate Graphs in Excel

Hello Toni,

 

Good day!

It is possible that can generate test result to a graph in Excel.

You can set this an example below.

I hope this will help you.

Goodluck.

1. Input this into worksheet.

1.         A1:  Month    B1:  Sales
           A2:  Jan      B2:  10
           A3:  Feb      B3:  20
           A4:  Mar      B4:  30				

2. On the Formulas tab, click Define Name in the Defined Names group.
3. In the Name box, type Date.
4. In the Refers to box, type =OFFSET($A$2,0,0,COUNTA($A:$A)-1), and then click OK.
5. On the Formulas tab, click Define Name in the Defined Names group.
6. In the Name box, type Sales.
7. In the Refers to box, type =OFFSET($B$2,0,0,COUNTA($B:$B)-1), and then click OK.
8. Clear cell B2, and then type the following formula:
9. =RAND()*0+10
10. Note This formula uses the volatile RAND function. The formula automatically updates the OFFSET formula that is used in the defined name "Sales" when you enter new data into column B. The value 10, which is used in this formula, is the original value of cell B2.
11. Select cells A1:B4.
12. On the Insert tab, click a chart, and then click a chart type.
13. Click the Design tab, click the Select Data in the Data group.
14. Under Legend Entries (Series), click Edit.
15. In the Series values box, type =Sheet1!Sales, and then click OK.
16. Under Horizontal (Category) Axis Labels, click Edit.
17. In the Axis label range box, type =Sheet1!Date, and then click OK.

Best Regards,

Elwyn II

About worshiper2012

Questions
0

Answers
6

Best Answers
1

• Vote Up 0 Vote Down

• Posted on - 12/01/2011
• Question Category: MS Excel

Answered By pankajvishwakarm 0 points N/A #105754

Auto Generate Graphs in Excel

If all that exists on the worksheet is your data, we can write code that
plots each row of the used range, as this macro does:

Sub OneChartPerRow()
Dim rCat As Range
Dim rVal As Range
Dim rUsed As Range
Dim iRow As Long
Dim cht As Chart

Set rUsed = ActiveSheet.UsedRange
Set rCat = rUsed.Rows(1)

For iRow = 2 To rUsed.Rows.Count
Set rVal = rUsed.Rows(iRow)
Set cht = Charts.Add
cht.Name = rVal.Cells(1, 1)
With cht
.SetSourceData Source:=Union(rCat, rVal)
.HasTitle = False
.HasTitle = True
With .ChartTitle
.Text = rVal.Cells(1, 1)
End With
End With
Next
End Sub

That's it.

About pankajvishwakarm

Questions
0

Answers
26

Best Answers
3

• Vote Up 0 Vote Down

• Posted on - 01/17/2012
• Question Category: MS Excel