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?
- What if I want to have three distinct colors for highlights - equal, above, below?
- How to modify colors for the different series?
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. 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)
3. Enter your information in the data table. Add or remove rows as needed.
4. Select the columns required to build the chart, for this choose - the time series (here, Month), the Actual, Target, and the ABOVE columns in that order 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".
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, when using a saved template?
A. Click on chart, press CTRL+1to open format pane
B. From the drop-down, choose "Actual" column
C. Use the "+" icon form chart to add data labels:
Click on the data labels and use the format pane to position it:
Similarly, you can also add the data labels to the Target line (if needed).
The column chart with the target line is ready!
What if I want three distinct colors for highlights - equal, above, and below?
The default Column chart with line for time series & highlight has two distinct colors: highlighted for columns that have reached target or more (in blue); columns that have not achieved their target (in gray).
If you need to have three distinct colors for the three different scenarios, as shown:
1. Achieved and more than target in blue
2. Exactly equal to the target in gray
3. Below target value in pink
This can be achieved easily by the following steps:
1. Modify the ABOVE formula to
=IF([@ACTUAL] > [@TARGET],[@ACTUAL],NA())
2. Right-click on chart, choose Select Data:
3. In the dialog box that opens up, choose add the "BELOW" series
4. Right-click on chart and go to Change Chart Type
5. In the dialog box, change the chart type of Below to "Clustered Column"
6. Click on the chart and press CTRL+1 to open the format pane.
7. From the drop-down choose the "BELOW" series
8. From series options, increase Series overlap to 100%
9. In fill& line, choose a fill color as needed
10. With this, the column chart with line will have three different colors to indicate Above (greater than achieved value), Below (lesser than achieved), and Actual columns that are exactly achieved.
How do I modify colors for the different series?
1. Click on the chart, and press CTRL+1 to open the format pane
2. From the drop-down, choose the series you'd want to change the color of (except the Target series):
3. From Fill & Line, choose the fill color as required.
With this, the required chart with the modified Above column color is ready:
