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 do I modify colors for target, actual, positive, and negative variances?
- How do I modify the label (fill color and font color) for positive and negative variances?
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 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 data labels, if the chart is built using the .crtx file?
A. Click on the chart, and press CTRL+1 to open the format pane
B. From the drop-down, choose the "TARGET" series
C. Use the "+" from the chart to add data labels
D. Click on the labels and from the format pane, position them on "Inside Base"
E. Repeat the same steps (from B-D) for the "ACTUAL" series
F. Now, choose the "POSITIVE VAR" series from the format pane drop-down and use "+" from the chart to add data labels
G. Click on the labels, and use the home ribbon to format it by changing the font & fill color that denotes positive variance.
H. Similarly, select the "NEGATIVE VAR" series from the format pane drop-down and use "+" from the chart to add data labels, use home ribbon to format it by changing the font & fill color that denotes negative variance.
With this, your chart is ready!
How do I modify colors for the target, and actual, columns?
1. Click on the chart, and press CTRL+1 to open the format pane
2. From the drop-down, choose the "Target" series.
3. From fill & line, choose an apt fill color per your requirement.
4. Follow the same steps for the Actual series, your chart is ready!
How do I modify the label (fill color and font color) for positive and negative variances?
1. Click on the chart, and press CTRL+1 to open the format pane
2. From the drop-down, choose the "POSITIVE VAR" Data Labels series.
3. Use the home ribbon to format by changing the font & fill color that denotes positive variance as per your need.
4. Similarly, choose the "NEGATIVE VAR" Data Labels series and format the negative variance font and fill as needed.
The modified chart, is ready for further analysis!
