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 display a different label?
- How to rename the measures (actual, budget, forecast)?
- How to modify the colors of each 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 proceeding, please click on the chart and use the "+" icon to remove the data labels
Now, follow the steps explained here:
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 - the category(REGION), the three measures (i.e Actual, Budget & Forecast) and the BACKGROUND 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 to the chart?
1. Click on the chart, and press CTRL+1 to open the format pane
2. From the drop-down, choose the "Actual" series from the drop-down
3. Use the "+" from chart to add data labels
How to display the % from Forecast or Budget label?
1. Say you want to add the "% from Forecast" label, for this, from the drop-down, choose the "Forecast" series
2. Use the "+" icon from chart to add data labels now, Click on the labels, go to the label options. Here, uncheck all options ad choose the "Value from cells" option.
3. Add the range as the % from Forecast series.
4. Format the labels as needed using the formatting options available in the Home ribbon
Your chart is now ready for further analysis.
How to rename the measures (actual, budget, forecast)?
To change the context from actual, budget, or forecast, you just need to change the corresponding column headers.
With this, the chart gets updated automatically.
How to modify the colors of each series?
In this chart, the Actual series is a column chart with the column visible where as the other two series (Forecast and budget) has only the borders on top visible. To ,odify the colors follow the steps below:
1. Click on the chart, and press CTRL+1 to open the format pane
2. From the drop-down, choose the "Actual" series from the drop-down
3. Under fill & line, change the fill color as needed.
With this, the chart is:
For either budget or forecast series:
1. Choose the Budget series from the drop-down. In fill & line modify the Border color
With this, the chart with modified budget line is:
