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 don't want to color when actual = target?
- What if we need to have three distinct colors (equal, above, below)
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) is referred to correctly in your input sheet.
(or)
Hardcode the target value in the calculated columns as in the next step.
3. Enter your information in the data table. Add or remove rows as needed.
Here, I have hardcoded the target value in the ABOVE and BELOW calculated column formulas.
4. Select the columns required to build the chart, for this - all of the columns are needed.
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.
What if I don't want to color when actual = target?
The formula for the ABOVE calculated column that decides whether to highlight a given column checks if the actual value is greater than or EQUAL to the said target value.
=IF([@ACTUAL]>=$D$4,[@ACTUAL],NA())
If you do not want to color the columns that have just achieved the targets (i.e Actual = Target), then the ACTUAL column only takes values that are greater than the targets:
=IF([@ACTUAL]>$D$4,[@ACTUAL],NA())
With this, the columns that have just achieved the targets won't be highlighted.
What if we need to have three distinct colors (equal, above, below)
The default Column chart that highlights above target 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]>$D$4,[@ACTUAL],NA())
2. Click on the chart and press CTRL+1 to open the format pane.
3. From the drop-down choose the "BELOW" series
4. From fill & line, change the fill color to pink
With this, we have a column chart with three different colors for above, below and equal to the target columns
