We help IT Professionals succeed at work.

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

cdb424ttm
cdb424ttm asked
on
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
Comment
Watch Question

CERTIFIED EXPERT

Commented:
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.
CERTIFIED EXPERT
Most Valuable Expert 2011
Awarded 2010

Commented:
Hello,

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
Barry TiceBusiness Analyst
CERTIFIED EXPERT

Commented:
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.
Barry TiceBusiness Analyst
CERTIFIED EXPERT

Commented:
Looks like teylyn beat me to it!
CERTIFIED EXPERT
Most Valuable Expert 2011
Awarded 2010
Commented:
See attached for the time values converted to real time values and plotted on the secondary axis.

To convert to numeric values, multiply the Vlookup by 1 (see formulas in C3:E3)

Use three empty filler series to make room on the primary axis.
Move the three time series to the secondary axis and use an empty filler series to allow for the space required by the series on the primary axis.

cheers, teylyn
WEBI-CallDetails-Workbook.xls
CERTIFIED EXPERT

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

Author

Commented:
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
CERTIFIED EXPERT
Most Valuable Expert 2011
Awarded 2010

Commented:
done

Explore More ContentExplore courses, solutions, and other research materials related to this topic.