Link to home
Create AccountLog in
Avatar of cdb424ttm

asked on

Charting In excel I can't get the chart to work only pulls one column.

Charting In excel I can't get the chart to work only pulls one column.
Please see attached file.  I need it to show all the data not just the first column. WEBI-CallDetails-Workbook.xls
Avatar of ScriptAddict
Flag of United States of America image

It is pulling all the columns but the X is showing such a high value in the first column it doesn't show the rest.  Try values as minutes (551) in the second column and see how it works for you.

The values for columns C to E are text. Even if they were real time values, it's a question of scale on the Y axis. Total calls is 348, talk time is a value of time, 5:59 minutes. This equals a number of 0.0042. This would be way too small to appear visibly on the chart.

Convert the text to time values and chart them on the secondary axis.

cheers, teylyn
You've got a couple things going on here, cdb424ttm.

First of all, it appears you have your cells on your Agent tab formatted as "General" which means Excel doesn't know how to interpret them as numbers because of the colons in them (indicating time). So, you're going to have to convert them to time values rather than General values, so Excel can plot them.

The next issue is, you're then trying to plot very small times (00:00:10) on a chart that scales up to 400. You'll need to add a secondary vertical axis to the chart to do that.

Here's the text from the help file that you can get yourself by pressing F1 and then searching for "secondary vertical axis":

Add a secondary vertical axis
You can plot data on a secondary vertical axis one data series at a time. To plot more than one data series on the secondary vertical axis, repeat this procedure for each data series that you want to display on the secondary vertical axis.

1.In a chart, click the data series that you want to plot on a secondary vertical axis, or do the following to select the data series from a list of chart elements:
1.Click the chart.
This displays the Chart Tools, adding the Design, Layout, and Format tabs.

2.On the Format tab, in the Current Selection group, click the arrow in the Chart Elements box, and then click the data series that you want to plot along a secondary vertical axis.

2.On the Format tab, in the Current Selection group, click Format Selection.
The Format Data Series dialog box is displayed.

 Note   If a different dialog box is displayed, repeat step 1 and make sure that you select a data series in the chart.

3.On the Series Options tab, under Plot Series On, click Secondary Axis and then click Close.A secondary vertical axis is displayed in the chart.

4.To change the display of the secondary vertical axis, do the following:
1.On the Layout tab, in the Axes group, click Axes.
2.Click Secondary Vertical Axis, and then click the display option that you want.
5.To change the axis options of the secondary vertical axis, do the following:
1.Right-click the secondary vertical axis, and then click Format Axis.
2.Under Axis Options, select the options that you want to use.
 Tip   To help distinguish the secondary axis, you can change the chart type for just one data series. For example, you can change one data series to a line chart. For more information, see Present your data in a combination chart.


Until you get your data to be numeric, though, you're not going to be able to chart it.
Looks like teylyn beat me to it!
Avatar of Ingeborg Hawighorst (Microsoft MVP / EE MVE)
Ingeborg Hawighorst (Microsoft MVP / EE MVE)
Flag of New Zealand image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
This should give the values in minutes.  Then they should all display ok.
=TIMEVALUE(VLOOKUP("ARay",'Agent '!$D:$Z,3,FALSE))*86400

Open in new window

Avatar of cdb424ttm


Taylyn, can you please up load the file again, I'm getting a error that the file can't be found when I try to save it?   Thanks