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 modify the size of the bins?
- How to modify the two colors (bins and filled)?
- How to increase or decrease bins (default is 4 bins)?
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 we see the steps involved, please click on the chart and use the "+" icons to remove the data labels as shown:
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 do I add data labels when using a .crtx file?
1. Click on the chart, and press CTRL+1 to open the format pane
2. From the drop-down, choose the "Bin 1" series
3. Use the "+" from chart to add data labels
4. Click on the labels and from the label options in the format pane, uncheck all options and check the "Value from Cells" option alone.
5. Choose the range as "Actual" series as shown:
Your chart is now ready for further analysis.
How do I modify the size of the bins?
The progress tracker with bins is built with the assumption that the maximum possible progress tracking value is 100.
The 4 default bins are sized equally as 25 each. You can change this by modifying the values under each Bin.
Note that, each bin should have the same size, hence the same value.
A modified version of the bins and the corresponding chart are shown below for your reference.
How do I modify the two colors (bins and filled)?
To modify the color of the bins, click on the chart and press CTRL+1 to open the format pane.
1. From the drop-down, choose the bins, from Bin1 to Bin4 one by one
2. In fill & line, modify the fill color as needed
3. Repeat the same for all the bins; your chart is ready with this!
To modify the color of the filled area, similar to the above steps, choose the Bin 1 act to Bin 4 act series and change the fill color as needed.
With this, your chart is ready!
How do I increase or decrease bins (from the default of 4 bins)?
1. Say you want to increase the bins to 5. Right-click on one of the cells inside the Bin 1 act series, and go to insert then to Table columns to the left
2. Add the new bin data, as shown: (here I have made all the 5 bins as 20 each)
3. Now, we need to add a calculated series under which the actual values have to be contained in.
For the 5th bin, this will be the minimum of Actual minus the previous 4 bins' actual or the 5th bin value
=MIN([@Actual]-[@[Bin 1 act]]-[@[Bin 2 act]]-[@[Bin 3 act]]-[@[Bin 4 act]],[@[Bin 5]])
4. Right-click on the chart and go to Select data to add a new series - both the Bin 5 and Bin 5 act
Ensure that the order of the series has to be as follows, use the Up/Down arrows to adjust accordingly.
Similarly, add the Bin 5 act series at the end
5. Now, right-click on the chart go to Change chart type and modify the chanrt types for Bin 5 and Bin5 act as shown:
6. Now, go to format pane, choose the Bin 5 series and modify fill color to match the other 4 bins
7. Similarly, change the fill color of the Bin 5 act series similar to the other calculated columns.
To decrease the bins:
1. Say, you want to delete the 5th Bin. With any cell in the Bin 5 selected, right-click go to delete and Table Columns
2. Similarly, delete the Bin 5 act (that is, the corresponding actual calculated column)
3. Now go to chart, delete the #REF! errors which are still referring to the deleted columns (there will be 2)
4. With this, your modified chart is ready!
