motion

Adding a Second Series to Show a Time Comparison in Excel 2007:-


In the Figure (given below), two series are plotted on the bar chart. The original series presents sales data from this year. This data was moved to Series 2 in order to continue to be plotted as the top bar. The new Series 1 contains sales data from last year, for reference.



(In this chart, the sales for this year are moved to Series 2.A new Series 1 shows sales from last year; a bit of additional data to show whether sales are increasing or decreasing.)

Because the main message is current-year sales, those bars are formatted with a black fill to draw attention to them. The bars for last year are shown with white outlines. The addition of data from the previous year adds a bit of context to the chart. The reader can tell that Products 2, 3, and 5 are new this year and have no sales history from the previous year. Product 4 was the previous market leader, but its sales have fallen off sharply. Product 1 has experienced good year-to-year growth; it will be interesting to see if the newly emerging products show similar growth in Year 2.

To create the chart in the above Figure, you follow these steps:

1. Enter product names in column A.

2. Enter last-year sales in column B and this-year sales in column C.

3. Sort the data so that the largest sales from this year are at the bottom of the chart.

4. From the Insert ribbon, choose Bar, 2-D Bar, Clustered Bar.

5. Choose Layout, Gridlines, Primary Vertical Gridlines, None.

6. Choose Layout, Axes, Primary Horizontal Axis, None.

7. Choose Layout, Legend, Show Legend at Top.

8. Choose Layout, Chart Title, Above Chart.

9. Click in the title area. Type the first line of the title, press Enter, and type the second line of the title.

10. Click the outline of the title to exit Text Edit mode. On the Home ribbon, click the Left Align button and the Decrease Font Size button. Drag the title to the left.

11. Click a bar from this year. On the Format ribbon, choose Shape Fill and then select black.

12. Right-click the black bar and choose Format Data Series. Change the Series Overlap setting to 25%.

13. Click one of the bars for last year. On the Format ribbon, choose Shape Fill, White. On the Format ribbon, choose Shape Outline and then select black.

14. Grab one of the resizing handles in the corner of the chart and drag outward to resize the chart