Here's the collection of the frequently asked questions of the Instant Chart Maker Template:
- How to change colors?
- How to add and delete rows? Using mouse
- How to change chart and axes titles?
- How to add or remove data labels?
- How to change the data label position?
- How to export or save a single chart?
- How to use the chart in PowerPoint?
- How to change font size?
- How to remove the x-axis line?
- How to re-order the x-axis category?
- How to modify Axis bounds and units?
- Can I add more columns to the data?
- How to change the gap width on the x-axis?
- How to modify the number format of the Y-axis (for large numbers)?
- How to modify the data label format (for large numbers)?
Let us look at each one of these below:
1. How to change colors?
There are two ways in which you can modify the colors in your charts:
I. If you want to change the color of an individual item in a chart, say for the 2022 series in the column chart here:
Click anywhere on the chart and press CTRL+1 to open the format pane on the right side.
From the drop-down, choose the 2022 series
Change the color as desired in the Fill & Line option
II. If you wish to change the overall colors of your template/Excel workbook, you can modify the theme colors.
Check this article on changing the theme colors/fonts.
2. How to add and delete rows? Using mouse.
All the charts in the Instant Chart Maker Template are connected to their corresponding table present next to the chart.
In the template, to add or delete rows of data to the table, please follow the below-described method ONLY.
To add additional rows of data, use your mouse to click and drag the right-end corner of the table to your desired number of rows needed.
After this, add the new data in the rows created:
Similarly, to delete rows of data, click and drag up, the number of columns you want to remove from the table, as shown:
Once this is done, select the data from the cells that are no longer in the table and use the Clear All button from the Home ribbon to delete them.
Note: Since the Chart Maker template has multiple tables present in a single sheet, clicking inside any cell to add/delete rows will throw an error. Hence, please refrain from doing this.
Also, please keep in mind NOT to increase the row count by using the Tab button, since this modifies the range of the table.
3. How to change chart and axes titles
Each of the chart's titles in the template is taken from the light blue color input cell present on top of the chart, modifying this will automatically modify the chart title.
If you do not make any changes to your title, once a chart is created, you can hard-code it by clicking on the title and changing the title using the formula bar.
For the axes titles:
In the chart maker template, the Y-axis titles are taken from the "Metric" input cell
Change the input Metric cell, to modify the y-axis title.
In case, you need to hard-code the axes name, click on the same and use the formula bar to achieve the same.
The same applies to the X-axis as well.
4. How to add or remove data labels
To delete the 2022 data labels, for example from the below column chart with multiple series
Click on the chart, and press CTRL+1 to open the format pane.
From the drop-down, choose the data labels you want to delete and press Delete key
Note: You can directly choose the series labels from the chart and press the Delete key. In case there are multiple labels, the above method is ideal to select the correct series's data labels.
To add data labels, say, for the Target series in the column chart with multiple targets, use CTRL+1 to open the format pane.
From the drop-down choose the series you would want to add data labels.
Immediately, use the "+" icon that appears on the top of the chart to add data labels.
With this, the desired data labels get added:
5. How to change data label position
The position of data labels in the chart can be easily changed per your need:
Click on chart, press CTRL+1 to open format pane.
From the drop-down, choose Data Labels of the series, you wish to re-position. Here, let us position the target data labels above the lines.
From the Label Options, choose the position as needed:
6. How to export or save a single chart
If you are looking to incorporate any chart from the Instant Chart Maker template into a presentation or a document, right-click on the chart and save the same as a picture.
Note: If a chart is saved as a picture, any changes made to the data WILL NOT be reflected in the image.
If you need to use one or a few charts from the template in your Excel workbooks, you can:
Right-click on the chart and click "Save as Template"
This will save the chart as a .crtx file as shown:
Open the Excel workbook which contains your data and go to Insert - > All Charts
You'll find the saved templates in the Template folder:
Note: For a chart saved as a template (.crtx) format to work, please ensure that the order of columns remains the same as it is in the Instant Chart Maker Template.
7. How to use the chart in PowerPoint?
There are two ways of doing this:
1. As seen in the previous FAQ, firstly save the chart as an Image on your computer and you can easily insert the chart as a picture in a PowerPoint Slide.
Note: If a chart is saved as a picture, any changes made to the data WILL NOT be reflected in the image.
2. The second method is to right-click on the chart and copy it from the template file
And, pasting it by "Keep Source Formatting & Link Data" on the PowerPoint slide:
This ensures that the chart stays connected to the data and reflects any changes in the same in the PowerPoint presentation.
Note: Pasting the chart with a link to data sometimes tends to be clumsy, hence we do not recommend this method of pasting your chart.
8. How to change font size?
To change the font size of any element like data labels, titles, axes titles, or legends in the chart, click on the same and use the Home Ribbon to modify the font size and color:
9 How to remove the x-axis line?
To remove the horizontal (X-axis) line from the below multiple series column chart,
Double-click on the X-axis to open the format pane options to the right of the chart.
Under the Fill & Line options, choose No Line as shown:
This removes the X-axis line from the chart:
10. How to re-order the x-axis category?
To re-order the horizontal (X-axis) line from the below multiple series column chart,
Click on any cell in the corresponding data table, then from Table Design check the Filter Button.
Now sort the category column (here, Department) as needed,
This automatically re-orders the x-axis in the chart:
11 How to modify axis bounds and units?
Excel assigns the vertical axis bounds and units based on the corresponding data. However, it is easy to modify/limit the same.
Consider the vertical container filler chart (progress tracker), as shown:
Say, if your target is always 100 in performance tracking and If you need to modify the Y-axis bounds to end at 100:
Doube click on the Y-axis to open format pane (you can also do this by clicking the chart and pressing CTRL+1 and choosing the Vertical axis from the drop-down).
In the Axis Options, reset the maximum bounds to 100.
Note:
Minimum Bounds is the number at which the axis starts
Maximum Bounds is the number at which the axis ends
Major Units are intervals at which the values against the axis, grid lines appear, and also tick marks appear (if chosen to display)
Minor Units are secondary intervals at which minor tick marks appear (if chosen to display)
12 Can I add more columns to the data?
The Instant Chart Maker is created wherein, all of the data (columns) corresponding to a chart are used. Few of these columns are calculated columns based on inputs from other cells of the table.
Also, in the Instant Chart Maker template, there are multiple tables in a single sheet of Excel for the multiple charts present.
Inserting a new column in between in any one table may tend to break the other tables or calculations, hence it is not recommended to add new columns.
If there is a need to add new columns to any table, do so by adding the same at the end of that particular table, as shown:
13 How to change the gap width on the x-axis?
To adjust the gap width of the columns, say the target column in the column within column chart, as below:
Click on the chart and press CTRL+1 to open the format pane. From the drop-down choose the series of which you'd want to increase/decrease the gap width of.
Under the series option, adjust the gap width % accordingly:
Increasing the gap width% makes the columns narrower, hence away from each other.
Decreasing the gap width% makes the columns wider, hence closer to each other.
14 How to modify the number format of Y axis
Consider a sample data of region and their sales figures and the corresponding Simple column chart as shown
The Y-axis numbers can be formatted to show in units like K, M, etc for better readability. This can be done as follows:
a. Double-click on the Y-axis to open the format pane:
b. In the axis options, under Numbers, change the category to Custom:
c. Add a format code, and click on Add to add the same.
Here, since these are very large numbers, it would be ideal to format them as K (for thousands) and/or M (for millions)
To format as thousands, use #," K" in the Format code, and if you want to format as M (in millions), then use #,," M"
This modifies the axis labels as shown here:
15 How to modify the data label format
Consider the previous example where the vertical axis labels are formatted but the data labels are still not. This can also be easily modified to a better readable format by following the steps given below:
a. Right-click on the data labels and go to format data labels
b. In the Label options, under Number, change the category to Custom
c. Add the format which you need the labels to be in.
In my case, it is in thousands (K) as shown below:
This modifies the labels on the chart as follows:
