Customize Excel Charts (8 Effective Ways)

In this article, we are going to briefly discuss how to customize charts in Excel. While Microsoft Excel’s default chart settings allow you to quickly generate basic charts, personalizing these charts can take your presentations and reports to the next level. You can successfully express your insights, highlight patterns, and make your data-driven information more attractive by adjusting the design, layout, and data representation of your charts.

In this tutorial, we’ll look at different strategies and features that allow you to customize every aspect of your charts, from selecting the correct chart type and formatting axes to adding data labels and modifying colors. Whether you’re a data analyst, a business professional, a student, or anybody who deals with data, understanding the skill of designing Excel charts will allow you to more effectively convey your results and bring your data to life.

Please follow the whole article to get basic ideas on how to customize Excel charts.

Download Practice Workbook

Customize Charts.xlsx

Chart Elements in Excel

Before customizing Excel charts, let’s be familiar with the chart elements in Excel.

There are 9 different chart elements available in Excel. These are:

Axes
Axis titles
Chart titles
Data labels
Data table
Error bars
Gridlines
Legend
Trendline

Customize Excel Charts: 8 Effective Ways

Here is a chart we created for sales and profits over six months. It’s a column chart that compares sales and profit amounts for each month. To create this chart,

First, select the whole data table and go to Insert >> Insert Column or Bar Chart >> 2-D Column Chart.
This command will create a Column Chart showing the comparison between sales and profits in each month.

Now, we will describe the procedures to customize this chart in the following sections.

1. Adding Chart Title

Whenever you create a chart, you will see that there’s a box on top of the chart area which contains the text ‘Chart Title’. It means that you can insert the name of your chart in this box. Let’s name it ‘Customize Charts’ for now.

You can also make the chart title dynamic by referring to another cell in it. See the image below.

Here, we selected the chart title and typed the formula in the Formula Bar. Keep in mind that you should not type the formula in the text box. To develop the formula, you just have to select the chart title box, type = (Equal symbol) in the formula bar, and select the cell containing the chart title.

Now, whatever data you insert in the B2 cell of the Dataset sheet of this workbook, it will update the chart title.

There are more options for Chart Title which you can find by clicking on the Plus (+) icon beside the chart. See the picture below. You can modify the position of the title box, apply fill color, change font style, etc.

Let’s select the More Options… button for now. You will see the Format Chart Title pane will appear. To apply fill color to the title box, select the Fill icon under the Title Options, check the Solid fill option, choose a fill color of your preference and you will see the title box now has a background color.

You can explore the Text Options shown in the figure above and edit the text color and style in the Chart Title box. Here, we want to set a color for the text in the title box.

For that purpose, select Text Options >> Text Fill & Outline (marked as 2 in the image below) >> Solid Fill >> Fill Color. This command will change the text color of the title box.

Now, we will show you how you can apply text effects to the chart title. Follow the image below.

If you follow the image above, you can see that we selected the Text Effect option first.
Then, a style is selected from the Presets dropdown and it changes the view of the Chart Title.

If you want to get more text styles for the Chart Title, just right-click on the box and select a Theme Style of your preference.

Thus, you can customize the appearance of the Chart Title.

2. Changing the Appearance of the Chart

Now, we will show you how you can change the visual appearance of the chart. Excel has some default styles for charts. Let’s go through the description below.

First, select the chart. It will initiate the Chart Design tab.
Next, click on the drop-down icon marked by 3 in the picture.

After that, you will see more styles. Select any of them of your preference.

You can also choose custom colors for the vertical bars of the chart, Just click on the Change Colors drop down, and it will display various types of colors for the bars.

You can also explore the Quick Layout options. It has some default layouts for the chart that may make it look better.

Here, we chose Layout 1 which rearranges the Axis Labels and Legends in the chart, making the appearance of the chart unique and attractive.

There are tons of options to modify the appearance of the chart in the Add Chart Elements drop-down. See the image below.

Let’s just add some error bars to the chart. For this purpose, select Error Bars from the Add Chart Element drop-down and choose any of the options that appear. Here, we selected the Percentage Error Bar.

Notice that there is another button named More Error Bar Options… in the above image. You can customize the Error Bars by using the features available in the More Error Bars Options.

Note: Error Bars are useful when we calculate percentage increases or decreases in the data. Here, I just happen to use Error Bars to show the features in the Add Chart Element dropdown.

3. Customizing Chart Axis

Here, we are going to show features and options to customize the axis and axis data in Excel charts. First, I’ll format the vertical axis.

To format or customize the axis, you need to right-click on the Axis box and select the Format Axis
You can see the Format Axis pane will appear. There are some default values for Bounds and Units parameters of the chart.

You can change them to edit the chart the way you want. Here, the maximum value in the dataset is $600.00, so let’s set the Maximum Bound to 650. I changed the Major Unit from 100 to 50 so that I get more gridlines to visualize the level of the column bars of the chart.

There are more options available if you scroll down the Format Axis pane. We don’t usually use them, so I’m going to skip that part.

Now, I’m not going to give you the heavy details on the following options, but you should know about these. In the following image, you can see that there are three more features that can open more options to customize the axis. You can apply fill colors, effects and modify the position of the axis using these options. The existing formatting on the chart is fine to me, so I won’t use any of these options.

You can also change the number format of the vertical axis. For this reason, you need to scroll down under the Axis Options feature. You can find the drop-down for different number formats under the Category. You can also modify the code for the number in the Format Code section.

Now, if you want to work on the horizontal axis, you may not find the same options as the vertical axis. It’s because, for the column or bar charts, the horizontal axis contains texts or dates rather than numeric values. Have a look at the following picture.

However, you can also change the text data of the axis labels from a different data range. Let’s say, you want to modify the horizontal axis labels from the following data.

Now, place the cursor on the horizontal axis box and right-click on it.
After that, select the Select Data option from the Context Menu.

Next, the Select Data Source dialog box will pop up. Click on the Edit button and proceed to the next operation.

In the Axis Labels dialog box, place the cursor and select the Labels range (B5:B10) and click OK.

Finally, your desired axis labels replace the previous ones.

Thus, you can customize axis and axis labels in an Excel chart.

Note: You can insert a Data Table to a chart. Just check the Data Table option under the Data Labels and it will add a table in the chart.

4. Editing Data Labels

Sometimes, you may need the exact data of a parameter from the chart. For that purpose, you need to insert Data Labels.

First, select the Plus Icon (+) beside the chart and check Data Labels. This will add the Data Labels outside the end of each bar by default.

Let’s go to the More Options menu next.

Under the Label Options part, you can find different features to customize the data labels like what additional data you want to show with the labels or their position or the number format. As my data labels are ok, I will not use any of these features and options.

Furthermore, you can see that the Data Labels are blocking the view of the chart. So, we will rotate them by 270 degrees which will make them easy to view.
To do this, select the Size & Properties option and then choose Rotate All Text 270⁰ from the Text direction drop-down.

In the next picture, you will learn how to customize the margins of the data labels, so the labels don’t overlap the bars. Just change the values of the margins with suitable data.

Fill color can also be used to differentiate the data labels of the two types of parameters.

In order to do this, select the Fill & Line option while the Data Labels are selected.
Then choose any of the options Solid or Automatic.
After that, choose a suitable color of your choice.

Note that you have to apply fill color to the data labels of two parameters (Sales & Profit) separately.

And finally, we get nice formatted data labels in the chart.

Thus, you can customize the Data Labels of a chart.

5. Customizing and Formatting Legend Data

Legends are indicators of the parameters used in the chart. For example, in our chart, the Legends are Sales and Profit. The bars for Sales and Profit have different colors, and it’s indicated by the Legend shown in the picture below. Here, we positioned the Legend at the Bottom of the chart.

Next, we applied fill color to the Legend.

Now, the texts of Legend are linked to the column headings of the numeric data in the data table. So if we change the Sales header to Revenue, the Legend will automatically be updated with this text. See the image below.

Thus, Legends can be customized if the user prefers this. You can delete it if you don’t need it.

6. Formatting Data Series

Another important feature of the charts is the Format Data Series feature. We can modify the gap width between bars or change the bar/line colors by using the options of Format Data Series.

First, click on any of the bars once. It should make all the bars selected.
After that, open the Format Data Series pane by right-clicking on any of the bars and selecting Format Data Series from the Context Menu.

In the Format Data Series pane, insert suitable values for the Series Overlap and Gap Width. Make sure that the Primary Axis checked under the Plot Series On section.

You can also go through the other options to add effects or fill colors. If you select the Fill & Line option, you will find features to change the color of the bars.

Thus, you can customize a chart by the Format Data Series feature.

7. Changing Chart Type

You can also change the type of a chart. For example, you can change this chart to a 3-D Column Bar or Line or Scatter chart. Here, we will open the Change Chart Type window first. In order to do that,

Select the bars and right-click on any of them. The Change Series Chart Type option will appear in the Context Menu. You can also open it from the Chart Design tab too.

After that, choose chart types of your preference. Here, we selected Line chart for the Sales data and 2-D Area chart for the Profit data.
Later, click OK.

Here is the final view of the chart.

Thus you can customize the chart type of a column or any other chart in Excel.

8. Adding Trendline to Chart

Trendlines can be useful for forecasting purposes. Here we have shown a quick technique to add a trendline to the chart.

Things to Remember

Avoid cluttering the chart with excessive data or components. To avoid confusion, keep everything clean and concise.
Axis labels, data labels, and chart titles should all be clear and descriptive. Use meaningful wording to assist the audience in comprehending the chart without ambiguity.
Maintain visual convenience by using a consistent color palette throughout your chart. Colors that are too bright can detract from the data.
Place the legend such that it does not obscure the data on the chart. If altering the legend improves clarity, do so.

Conclusion

In the end, we can conclude that you will learn basic ideas of how to customize Excel charts after going through the article. Customizing charts is necessary as the default form of the charts in Excel is not always understandable or approachable. If you have any questions or feedback regarding this article, share them in the comment section. Your ideas will help me enrich my upcoming articles.

Frequently Asked Questions

1. How do I resize and position my chart on the worksheet?

Answer: By clicking on the chart and dragging the sizing handles or transferring it to a different area on the worksheet, you can resize and position it.

2. How do I format individual data series in a chart?

Answer: Right-click on the data series to be formatted, select “Format Data Series,” and then tweak properties like color, marker style, and line style.

3. How can I create a 3D chart?

Answer: While 3D charts are conceivable, they are not advised due to the possibility of distortion. By selecting your data, navigating to the “Insert” tab, picking a 3D chart type, and then modifying it, you may build a 3D chart.

Customize Excel Charts: Knowledge Hub

<< Go Back to Excel Charts | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!