I have four series of results all on a scale of 1-5. I need to display these in a chart that shows each of the four series separately (i.e. four horizontal lines ranging from 5 on the left to 1 on the right), with the results plotted at the relevant points along the scales. I have mocked up a very crude example in the file - ideally would have the 'feature1, 2...' automatically labelled not in manual text boxes as I have it here obviously.
Hope this makes sense. Thanks!
Your picture looks like a scatter chart, so I first need to take the "series n" text information and make it numbers. The way you have it, I simply pick the numbers 1 to 4 for the series numbers. I arrange some numbers in the spreadsheet and make a scatter chart:
1) I enter the series numbers I've chosen into K2:K5. 1 in K2, 2 in K3, and so on.
2) There are eight "feature" values, so I want 8 copies of the series numbers, so I select K2:K5 and copy/paste into K2:R5. These are going to be the "Y values" for my scatter chart.
3) I can now create my scatter chart, using the "Feature" numbers in B2:I2 as the X values and K2:R2 as the Y values. Unfortunately, Excel's chart engine will not automatically understand this data arrangement so I cannot just select these cells and insert the chart.
3a) Select K2:R2 and insert a scatter chart (I chose markers with lines, for now). Excel should create a scatter chart with a horizontal line on it.
3b) Bring up the Select Data dialog.
3c) Select the one data series and edit the series. Give it a meaningful name. In the X values field, point it to B2:I2.
3d) Add another data series, using B3:I3 for the X values and K3:R3 for the Y values. Repeat for each row/data series. Dismiss the Select Data dialog.
Now you should have a scatter chart with four horizontal data series. Eventually, you can format these four data series to be marker without line.
In order to get the four horizontal line, you will add four more data series that use 5 and 1 for the x values and the same 1 through 4 for the y values.
4) In S2:S5, I enter the number 5. In T2:T5, I enter the number 1.
5) Select the chart and bring up the select data dialog again. Add a data series, using S2:T2 for the X values and K2:L2 for the Y values. Repeat until you have all four lines added to the chart.
Now, all of the data are added to the chart, so we just need to format the chart elements the way you want them.
6) Select each data series and format as desired. For the "line" series, use "no marker" and choose the desired line color. For the "feature" series, use "no line" and choose the desired marker and color.
7) The axes can be formatted with "values in reverse order" so that they go from 1 and the top/right and 4 or 5 at the bottom/left.
How did we do?
Originally Posted by shg
That's got it, thanks! Is there an easy way then though to add the labels (Feature 1, Feature 2 etc.)? As it is important to show where each feature sits on the scales. I appreciate this might make it look a bit messy but I can probably just move the labels around manually once they've been added if I have to.
I don't know how you define "easy," but the built in tools for adding and formatting data labels are described here: https://support.microsoft.com/en-us/...2-f467c9f4eb2d
I mean when I add the text labels (by using 'Value from cells' option), the text is quite long in parts and the data points quite close together, so the labels overlap making them unreadable. Short of manually dragging each of the labels around to make them readable, is there a way to stop them overlapping? Some of the data points are too close together for just shortening the text to be a viable solution.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks