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 number of series?
- How to show % instead of absolute numbers as labels?
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?
Before getting into the steps for saving a chart as a template, please click anywhere on the chart and use the "+" icon to remove the data labels.
Now, follow the steps below:
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 one, choose - all columns except Total, along with 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".
6. That's it, your chart gets populated with the data from your Excel file and is fully 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.
How to add the data labels correctly?
A. Click on the chart, and use the "+" icon to add data labels.
B. Click on the "110%" labels of the Total Position and press CTRL+1 to open the formatting pane.
C. Here, under label options - choose the position as "Centre" & uncheck all options and check the "Value from Cells" option
D. The range should be the Total series.
E. Modify the label size, color, etc using the formatting options in the home ribbon
With this, your chart is ready for analysis!
How to increase or decrease the number of series?
1. Right-click on any cell inside the Total Position column, go to Insert, then click "Table columns to the left" as shown. (Repeat for as many columns as needed).
2. This will add a new column, enter your data.
3. Now, the total column formula needs to be adjusted to include the newly added columns as well. Click on the first cell in the Total column, go to the formula bar, and add the newly inserted series's value (do this for the first row only)
4. Right-click on the chart and go to Select data. Here, add the new "Central" series
5. Right-click on the chart and go to Change Chart Type.
Here, change the new "Central" series to a 100% stacked column type
6. With this, your chart with the added series is ready!
To remove series from the data,
1. The Total column contains the sum of all the series involved. So before deleting a particular series, ensure to remove the corresponding values from the formula.
2. Say, you do not want to display the "North" series anymore. The original Total column formula is:
=[@East]+[@West]+[@North]+[@South]+[@Central]
Modify this to:
=[@East]+[@West]+[@South]+[@Central]
3. Now right-click on any cell inside the "North" series and go to delete, Table Column
4. Now, the Excel throws a #REF! error, this is due to the presence of the deleted series in the chart data.
Right-click on chart, go to Select Data and remove the #REF! error as shown:
Format the chart, as needed and the chart is ready for further analysis:
How to show % instead of absolute numbers as labels?
Please check our video on explaining how to include % as labels in your 100% stacked column chart.
https://youtu.be/9KZRK22AzuM?t=719