The following is the list of queries addressed in this article:
- How to customize the formatting options in the chart?
- How to save the chart as a template and re-use it in any other Excel file?
- How to increase or decrease the categories? How to adjust the settings and still get the desired effect?
- How to increase or decrease the series (Jan, Feb, Mar)? How to adjust the settings and still get the desired effect?
How to customize the formatting options in the chart?
How to save the chart as a template and re-use it in any other Excel file?
1. Right-click on the chart and select "Save as Template"
This opens a dialog box, assign a name to your chart template, and click "Save". This saves the chart in a .crtx format
2. From our Instant Chart Maker file, Copy the data table (CTRL+C) and paste it (CTRL+V) into your chosen Excel file.
3. Enter your information in the data table. Add or remove rows as needed. Please note that green-colored columns are calculated columns - do not enter any data, they are automatically calculated based on the input columns (Blue colored)
4. Select the columns required to build the chart, for this choose all columns with the headers.
5. Go to Insert and choose to see All Recommended Charts
This opens up a dialog box, go to All Charts and click on the Templates folder. Here, you'll find all the charts you have saved in step 1, choose the one that is relevant to the data and click "OK".
How to add the total series back to the chart?
6. By default the template ignores the Total row as a series. To add this, right-click on the chart and go to Select Data.
7. Use the pop-up to add the total series as shown:
8. Click on chart, press CTRL+1. Now from the drop-down choose the Total series.
9. In the series option, modify the gap width to 50% which is the 1/3rd of the other three series. (If you have 4 series then use 1/4th of the gap width of those series for the total series). And, move this to the primary axis as shown.
10. That's it, your chart gets populated with the data from your Excel file and is independent of the Instant Chart Maker template file.
- Chart title and Axis titles: Using a saved template to build your chart does not usually bring the Chart title and axis titles. Read this article to add these elements to your chart.
- To add data labels, click on chart and use the "+" icon to add data labels
With this, the small multiple column chart from a template is ready!
How to increase or decrease the categories?
To increase:
1. Click on the end of the last cell in the table and drag right to increase up to the desired number of columns (categories)
2. Right-click on the chart and go to select data
3. Edit each of the series as shown below to include the newly added categories
Also, ensure to edit the horizontal axis range:
Repeat this for all the series.
4. With this, the chart automatically updates to include the new categories as well.
To decrease the categories,
Click any cell inside the column you plan to delete, right click and the go to delete, Table column as shown:
This adjusts the chart by deleting the chosen category.
After this, choose the deleted cells and clear all (using home ribbon)
How to increase the series (Jan, Feb, Mar, etc.,)?
To increase,
1. Firstly to be able to add or delete more series (i.e. rows), uncheck the Total row from the table.
2. To do this, click on any cell inside the table, go to Table Design, and uncheck "Total Row" as shown
3. Add the series as need be, here I have added one; you can add more
4. Now, go to Table Design again and Check the Total Row to bring it back
5. This would have altered the series in the chart, we need to adjust it to include the newly added series and the Total
6. First, right-click on the chart, and select data. Here, you'll find the total series but this would be pointing to the newly added "APR" series data. Click on the "Total" series and modify the range to point the total.
7. Now, right-click on the chart again and go to Select Data. Add the new APR series.
8. Now, we need to adjust the series overlap of the total and the other series to get the desired result.
Since we have four series, say choose the "JAN" series
And, assign series overlap value of 200%.
9. Similarly, choose the "TOTAL" series, here the series overlap has to be 1/4th (since we have 4 series, use 1/4th; if you have 5 use 1/5th etc.) of the other series, i.e. 50%
With this, the modified chart is:
