A chart is one of the tools in Excel that can be used to visualize the dataset. Creating a chart from a given dataset is one of the most straightforward tasks in Excel. However, have you ever tried creating a chart from two or more sheets? If yes, you can concur it is a challenging task. This article has made the workarounds of creating a chart from multiple sheets easier.
Using a 2-D Column chart
Steps to follow:
1. Open the Excel application.
2. Open the Workbook that contains the worksheet with your dataset. Add your dataset to the sheets if you’re using a new workbook.
3. Click on Sheet 1. Highlight the dataset on sheet 1. Then click the Insert tab on the Ribbon.
4. In the Chart column, click the Insert Column drop-down button. From the menu, choose any 2-D chart that fits your dataset.
5. Double-click on the Chart Title section and add the title of your chart. Then, right-click on the insert chart. From the menu, choose the Select Data button.
6. On clicking, a Select Data Source dialogue box will open. Click on the Add button in the Legend Entries section to open the Edit Series dialogue box.
7. In the Series Name section, add the Sheet’s name and the column that contains your values, as shown below.
8. In the Series Values section, add the values of your dataset that are found on the other Sheet. Hit the Ok button.
9. Click the Ok button to close the Select Data Source dialogue box. Then, click on the Plus sign. Then, check the Legend checkbox. That is all you need to do
Using the 3-D Column chart
Steps:
1. Open the Excel application.
2. Open the Workbook that contains the worksheet with your dataset. Add your dataset to the sheets if you’re using a new workbook.
3. Click on Sheet 1. Highlight the dataset on sheet 1. Then click the Insert tab on the Ribbon.
4. In the Chart column, click the Insert Column drop-down button. From the menu, choose any 3-D chart that fits your dataset.
5. Double-click on the Chart Title section and add the title of your chart. Then, right-click on the insert chart. From the menu, choose the Select Data button.
6. On clicking, a Select Data Source dialogue box will open. Click on the Add button in the Legend Entries section to open the Edit Series dialogue box.
7. In the Series Name section, add the Sheet’s name and the column that contains your values, as shown below.
8. In the Series Values section, add the values of your dataset that are found on the other Sheet. Hit the Ok button.
9. Click the Ok button to close the Select Data Source dialogue box. Then, click on the Plus sign. Then, check the Legend checkbox. That is all you need to do.
Customizing chart made from multiple Sheets
1. Click the Plus sign in the chart and check the Legend checkbox.
2. To add the Trendline, check the Trendline checkbox. Choose the dataset from the dialogue box you wish to use as the Trendline.
3. Lastly, check the Axis and Axis Title checkboxes.