Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

My team has a spreadsheet in which we track counts of incidents of known issues from week to week. The sheet is organized with the issue title in column A and "Week 1" through "Week [N]" in columns B through [N].

           | Week1 | Week2 | Week3 | ...
-----------+-------+-------+-------+---------
 Issue1    |   50  |   40  |   30  |
-----------+-------+-------+-------+---------
 Issue2    |   10  |   30  |    0  |
-----------+-------+-------+-------+---------
 Issue3    |  100  |  200  |  150  |

It seems that every chart type assumes that you're going to place dates in a column and enumerate the issues across the first row. This doesn't work for our data, though, as we have hundreds of issues, and we're only trying to track the past 12 weeks or so.

The line graph I want has the weeks listed in the X axis, the counts in the Y axis, and the error titles as the various series. I was able to (almost) create the effect I want using a connected scatter plot, but I had to enter each series by hand, and (as I mentioned) there are a lot.

Is there a way to create a transposed graph without having to transpose my data?

Thanks.

share|improve this question

2 Answers 2

up vote 0 down vote accepted

FWIW, I laid out a data table as you described, and in Excel 2007 got the default chart to appear like you described wanting. Here's my example:

enter image description here

If that's not working, you can try this (to avoid Excel being "to smart" and applying all it's own formatting decisions).

  1. Create a blank chart-click in a blank cell and choose insert line chart.
  2. Right-click on chart and choose Select Data.
  3. Add a new series.
  4. For the label, choose your first series; for the series choose your cells B:N
  5. In the Horizontal Axis Labels area of the dialog (right side), choose your Week Labels.
  6. Select OK for everything and your first series should be set.
  7. Select the rest of your series, including your series labels.
  8. Re-select your chart and paste the values. If you Special Paste here you can select your values in rows and your first column contains series names.
  9. Click OK to finish pasting your remaining series to the chart.

Also, you can try the Switch Row/Column button in the Select Data Dialog box.

share|improve this answer
    
Yeah, I don't know why it wasn't working for me. It may be that my data wasn't contiguous. I'm over it. It was just an eye-candy enhancement anyway. –  gregsdennis Oct 11 '12 at 20:32

Make the chart. If it wants to use Issues as category names and Weeks as series names, switch rows and columns using the button on the Chart Tools > Design tab.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.