The following is the list of queries addressed in this article:


  1. How to customize the formatting options in the chart?
  2. How to save the chart as a template and re-use it in any other Excel file?
  3. If you save it as a template, then how to edit the label?
  4. How to control the position of the target label? 
  5. What if the targets are different for each category?
  6. How to modify the target line color, line type, and label font?


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. 

This chart contains calculated columns (in green), which refer to other input cells from the same page. Ensure that the target value (D4) and target label position value (D5) cells are referred correctly in your input sheet.


3. Enter your own information in the data table. Add or remove rows as needed. 

 


4. Select the columns required to build the chart, for this -the category and the actual and the target value (numeric value) columns in that order. Do not select the Target column.


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 totally 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 edit labels, when saved as a template?


7. With the previous step, your chart gets created except for the target labels (since we have not added that column in the chart creation). 


Follow the steps here to add the correct label in it's desired position:

1. Click and delete the data labels that say "Add text"

2. Click anywhere on the chart, and press CTRL+1 to to open the format pane

3. From the drop-down, choose the "TARGET VALUE" series.


4. Using the "+" icon form chart, add data labels.


5. Click on the labels, go to format pane and under label options, uncheck all others and click on "Value from cells" option


6. Choose the range from the "TARGET" column

7. Position the label as needed in the label options 

and format it using the home ribbon formatting options.


With this, your chart with a single target label is ready!


How to control the position of the target label?


In the column chart with a single target line, the position of the "Target Value" label can be adjusted as needed. To do this, use the Target Label Position input in cell D5. 


The number is the column position, that is, in the below screenshot "5" is the position that corresponds to the 5th series, "Operations".


What if the targets are different for each category?


Please check the chart, "C006 - Column Chart with Multiple Targets" if you have a different target for each of the categories.


How do I modify the target line color, line type, and label font?


Line color & Type:


1. Click on the chart, and press CTRL+1 to open the format pane.

2. From the drop-down, choose the "TARGET VALUE" line series.


3. Under fill & line, choose a suitable fill color and/or a suitable Dash type to modify the line type as shown:



Label font:

From the format pane drop-down, choose the "TARGET VALUE LABELS" 



This selects the target label, go to the Home ribbon and modify the font, text color, etc.

With this, the line type, color and label font is changed: