Read More Post for Excel Chart
Last week, a friend dropped me a message and asked me how to create an Excel Chart like the following picture.
At the first look, the chart is a vertical line chart. In fact, after discussing with some chart experts, it’s a kind of dot plot. Many market research centers use it to compare two different objects and list some special differences in one chart.
In our last post, we give an detail steps how to alternate plot area background in Excel. I will continue using this technology in this post. The final dot plot looks like the follow picture.
[Notice: The chart is created on Excel 2010]
Last week, a friend dropped me a message and asked me how to create an Excel Chart like the following picture.
[Notice: The chart is created on Excel 2010]
In order to complete this chart, we have to add two auxiliary data sequences. The first column will be used as alternative background and the second column will be used control the layout of dot plot.
Pre-request: Suppose we have the following raw data sequences and one legend sequence. We add two auxiliary data sequences and fill them like the following table. Column D is used as alternative background bar. Column E will force the dot plot to a correct layout.
Step 1. Select legend column and column B, C & D and Insert a 2D clustered-bar chart into your worksheet.
Step 2. Change the Maximum value of “Primary Horizontal Axis” to 100 so the bar will fill out the “Plot Area”
Step 3. Change the show direction of “Primary Vertical Axis”.
Step 4. Select “Series 1” and change it’s type to “Scatter with Straight Lines and Markers”. Now, the chart looks very strange. But don’t worry. We will change it to a normal chart at next step.
Step 5. [Key Step]Still select “Series 1” (the strange series) and right click to launch context menu. Choose “Select Data…” to open the “Select Data Source” dialog. In the dialog, select Series 1 and click “Edit “ button.
• Give this series a name. Here, we name it “Cost”.
• Re-assign values of Column B to series X. This operation is same as copying values from series Y to series X.
• Re-assign values of Column E to series Y.
After this key operation, the strange line disappear and a basic dot plot has appeared.
Step 6. OK, no other and let’s repeat step 5 for series 2
Step 7. Change Gap Width for series 3 (the bar series) to 0 and select “Second Vertical Axis” to change the Minimum of Second Vertical Axis to 1. A dot plot appears and you can present it to other people now.
Step 8. Apply professional color and theme from business week classical template library. The final chart appears. Does it look a little professional?
Pre-request: Suppose we have the following raw data sequences and one legend sequence. We add two auxiliary data sequences and fill them like the following table. Column D is used as alternative background bar. Column E will force the dot plot to a correct layout.
Step 1. Select legend column and column B, C & D and Insert a 2D clustered-bar chart into your worksheet.
Step 2. Change the Maximum value of “Primary Horizontal Axis” to 100 so the bar will fill out the “Plot Area”
Step 3. Change the show direction of “Primary Vertical Axis”.
Step 4. Select “Series 1” and change it’s type to “Scatter with Straight Lines and Markers”. Now, the chart looks very strange. But don’t worry. We will change it to a normal chart at next step.
Step 5. [Key Step]Still select “Series 1” (the strange series) and right click to launch context menu. Choose “Select Data…” to open the “Select Data Source” dialog. In the dialog, select Series 1 and click “Edit “ button.
• Give this series a name. Here, we name it “Cost”.
• Re-assign values of Column B to series X. This operation is same as copying values from series Y to series X.
• Re-assign values of Column E to series Y.
After this key operation, the strange line disappear and a basic dot plot has appeared.
Step 6. OK, no other and let’s repeat step 5 for series 2
Step 7. Change Gap Width for series 3 (the bar series) to 0 and select “Second Vertical Axis” to change the Minimum of Second Vertical Axis to 1. A dot plot appears and you can present it to other people now.
Step 8. Apply professional color and theme from business week classical template library. The final chart appears. Does it look a little professional?
