How to change the data label format in a chart. Data point format

The text is understood as the actual text, as well as the numbers and dates that are displayed on the diagram.

Text occurs in the following chart elements:

• chart title;
• axis titles;
• legend;
• labels of divisions of an axis;
• data signatures;
• data tables.

In any of these elements, in addition to text, you can customize and style border lines, fill, and other components.

Text change

Change content

The text can be arbitrarily changed only in the titles of the chart and axes. For other elements, the text is generated automatically from the contents of the sheet cells.

To change the header text, you can run the command Chart/Chart Options and in tab Titles dialog box Chart Options(see Figure 28.10) make the necessary changes.

You can also change the title text directly on the chart. To do this, select the heading, and then click on its text so that the text cursor starts blinking in the text, and then make the necessary changes. To finish editing the text, left-click anywhere outside the heading.

Finally, you can make the text of the chart titles generated from the cells of the worksheet. To do this, select the heading, place the cursor in the formula bar, enter the = (equals) sign from the keyboard, select the cell with the heading text with the mouse and confirm the entry. For example, in fig. 28.22 shows the formation of the chart title from the text of the cell A1. Later, when changing the contents of the cell A1 the title text will automatically change.

Rice. 28.22.

It should be noted that in the future such text can only be changed in the tab Titles dialog box Chart Options(see Fig. 28.10), this breaks the connection of the header with the cell. You cannot change the text directly on the chart.

Setting Font Options

Text font settings are configured in the tab Font, which has a common view for all dialog boxes for formatting all elements (Fig. 28.23). Font settings can be set for all elements at the same time or for each element of the diagram separately. In the first case, you should double-click on the element to be drawn with the left mouse button or select it and execute the top menu command Format. The name of this command depends on the name of the selected element. For example, if the legend is selected, then the command is called Highlighted legend.

When setting font parameters for all chart elements at once, double-click on the chart area with the left mouse button or select the chart and execute the command Format/Highlight Chart.

First, the tab has a checkbox Autoscaling. Selecting the checkbox (checked by default) ensures that the font size automatically changes when the chart is resized. When the chart size increases, the font size also increases, and vice versa, when the chart size decreases, it decreases. Unfortunately, autoscaling is poorly configured. As a result, it often happens that with a slight change in the size of the chart, the font size increases or decreases disproportionately. In most cases, especially for charts located on a data sheet, the checkbox Autoscaling should be removed, preferably for all chart elements at once.

Second, drop down Background you can choose the transparency of the background on which the text of the chart element is located. Choice of one of three options ( auto, transparent, opaque) is not important in most cases.

The main font parameters (font, size, color, style, simple underline) of the chart elements can also be changed using the toolbar elements Formatting. If a chart is selected, then the font settings of all elements will be changed at once. If one element is selected, then only the font settings of this element.

If necessary, you can change the font settings for individual words or other text fragments of the chart titles. To do this, select the heading, and then click on its text so that the text cursor starts blinking in the text, and then select the fragment to be drawn up. You can use the toolbar to set font options Formatting or tab Font. To complete the text font design, left-click anywhere outside the heading.

Change text orientation

Usually, the text orientation in the axis division labels is set in the auto-orientation mode, that is, depending on the available space on the chart, Microsoft Excel automatically rotates the labels.

To disable this mode and set the required orientation, in the tab alignment dialog box Axis Format(Fig. 28.24) click on the label of the desired angle of rotation in the field where the word is written Inscription degrees Text.

The chart title, category axis title, and data labels default to horizontal text orientation. The value axis title defaults to vertical text orientation.

To change the orientation in a tab alignment(Fig. 28.25) of the corresponding dialog box ( Chart Title Format, Axis name format, Data Label Format) you can click on the label of the desired rotation angle in the field where the word is written Inscription, or set the value in the counter degrees. To change the orientation to vertical "column", click in the field where the word is written Text.

You cannot change the orientation of the text in the legend.

Change the position of text elements

Changing the Position of Titles and Legends

The chart and axis titles, as well as the legend, are freely positioned in the chart area and can be dragged and dropped to any part of the area.

Change the position of data labels

To change the location of data labels on histograms, bar charts, scatter charts and graphs, double-click on the data labels with the left mouse button or select the data labels and execute the command Format/Dedicated data labels. In the tab alignment dialog box Data Label Format(Fig. 28.26) in the drop-down list Signature Position you can select the desired location.

For histograms and bar charts, you can choose: At the top, outside ; At the top, inside ; In the center ; at the base (

Recently, I had a task - to reflect the level of competencies of employees on a graph. As you may know, it is not possible to change the text of the value axis labels because they are always generated from the numbers representing the scale of the series. You can control the formatting of labels, but their content is strictly defined by Excel rules. I wanted instead of 20%, 40%, etc. the graph displayed the names of competency levels, something like:

Rice. 1. Diagram of the level of competencies along with the initial data for construction.

The solution method was suggested to me by an idea gleaned from John Walkenbach's book "Charts in Excel":

When creating the diagram shown in Fig. 1 used the following principles:

• The chart is actually a mixed chart: it combines a graph and a scatter plot.
• The "real" value axis is hidden. Instead, a scatter plot series is displayed formatted to look like an axis (dummy axis).
• The scatter plot data is in the range A12:B18. The y-axis for this series represents the numerical scores for each competency level, for example 40% for "basic".
• The y-axis labels are row data labels scatter plot, not axis labels!

For a better understanding of how the dummy axis works, look at Fig. 2. This is a standard scatter plot where data points are connected by lines and series markers mimic horizontal tick marks. The chart uses data points defined in the range A2:B5. All X values ​​are the same (equal to zero), so the series is displayed as a vertical line. "Axis tick labels" are simulated by custom data labels. In order to insert symbols in data signatures, you need to sequentially select each signature separately and apply the insertion of a special character by going to the menu Insert - Symbol (Fig. 3).

Rice. 2. An example of a formatted scatter plot.

Rice. 3. Inserting a character in the data signature.

Let's now look at the steps for creating the diagram shown in Fig. one.

Select the range A1:C7 and build a standard histogram with grouping:

Place the legend on top, add a title, set the fixed parameters of the value axis: minimum (zero), maximum (1), price of the main divisions (0.2):

Select the range A12:B18 (Fig. 1), copy it to the memory buffer. Select the chart, go to the Home tab and select Paste->Paste Special.

Select the New Rows and Values ​​(Y) in Columns radio buttons. Check the boxes next to Series names in the first row and Categories (X-axis labels) in the first column.

Select the new series and right-click on Change Series Chart Type. Set the chart type to "Scatter with Smooth Curves and Markers":

For the new series, Excel has created secondary vertical (right) and horizontal (top) axes. Delete the secondary vertical axis. This will set the scale for the scatter plot series to be the same as for the main bar chart. Select the minor horizontal axis and format it by specifying no for major tick marks and no for axis labels:

Select the main vertical axis and format it by specifying no for major tick marks and no for axis labels.

Select the scatter plot series and format it. The color of the line is black, the marker is similar to the tick marks (choose the size of the marker and set the color to black), choose the thickness of the line so that it does not differ from the horizontal line, add data labels (it doesn’t matter which ones). Format the data labels by selecting on the Label Options tab - Left.

Enter the legend, select and delete the description of the series related to the scatter plot.

Select the data labels of the scatter plot series one by one and (as shown in Fig. 3) type in them the words you want (area C13:C18 in Fig. 1).

What is the first thing we think about after creating a chart in Excel? About how to give the diagram exactly the look we imagined when we got down to business!

In modern versions of Excel 2013 and 2016, customizing charts is easy and convenient. Microsoft has gone to great lengths to make the setup process simple and the required options easily accessible. Later in this article, we'll show you some easy ways to add and customize all the basic chart elements in Excel.

3 Ways to Customize Chart Options in Excel

1. Select chart and use tabs from group Working with charts(ChartTools) - Constructor(Design) and Format(Format).
2. Right-click on the chart element you want to customize and select the desired command from the context menu.
3. Use special icons that appear near the upper right corner of the chart when you click on it with the mouse.

Even more options are in the panel Chart Area Format(Format Chart), which appears on the right side of the worksheet when you click Extra options(More options) in the context menu of the diagram or on the tabs of the group Working with charts(ChartTools).

Advice: To immediately open the desired section of the panel for setting the chart parameters, double-click on the corresponding element on the chart.

Armed with this basic knowledge, let's take a look at how we can modify the various elements of a chart in Excel to give it exactly the look we want it to look like.

How to add a title to an Excel chart

In this section, we'll show you how to add a title to a chart in different versions of Excel and show you where the main charting tools are. In the rest of the article, we will consider examples of work only in the newest versions of Excel 2013 and 2016.

Adding a Title to a Chart in Excel 2013 and Excel 2016

In Excel 2013 and Excel 2016, when you create a chart, the text “ Chart Title“. To change this text, simply select it and enter your own name:

You can also link the chart title to a cell on the sheet using a link so that the title is automatically updated whenever the contents of the linked cell change. How to do this is described.

If for some reason the title was not added automatically, then click anywhere in the diagram to bring up a group of tabs Working with charts(ChartTools). Open a tab Constructor(Design) and press Add Chart Element(Add Chart Element) > Chart Title(Chart Title) > Above chart(Above Chart) or Center (overlay)(Centered Overlay).

Or click the icon Chart elements(Chart Elements) near the upper right corner of the chart and check the box Chart Title(Chart Title).

next to the option Chart Title(Chart Title), you can click the arrow pointing to the right (see the figure above) and select one of the options:

• Above chart(Above Chart) – the name is placed above the chart construction area, while the chart size is reduced; this option is used by default.
• Center (overlay)(Centered Overlay) – the centered title is superimposed on top of the plotting area, while the chart size does not change.

For more options, click the tab Constructor(Design) and press Add Chart Element(Add Chart Element) > Chart Title(Chart Title) > Additional header options(More options). Or click the icon Chart elements(Chart Elements), then Chart Title(Chart Title) > Extra options(More options).

Button press Extra options(More Options), in both cases, opens the panel Chart Title Format(Format Chart Title) on the right side of the worksheet, where you can find the options you want.

Adding a Title to a Chart in Excel 2010 and Excel 2007

To add a title to a chart in Excel 2010 and earlier, follow these steps:

Associating a chart title with a worksheet cell

Charts of various types in Excel are most often created with alt text instead of a title. To set your own name for the chart, you can either select the chart field and enter the text manually, or link it to any cell of the worksheet containing, for example, the name of the table. In this case, the title of the Excel chart will be automatically updated every time the contents of the linked cell change.

To link a chart title to a worksheet cell:

1. Highlight the title of the chart.
2. In the formula bar, type an equal sign ( = ), click on the cell containing the desired text, and press Enter.

In this example, we are linking the title of an Excel chart to a cell A1. You can select two or more cells (for example, multiple column headers) and the resulting chart title will show the contents of all the selected cells.

Moving the title in the chart

If you want to move the chart title to another location, select it and drag it with your mouse:

Removing the chart title

If an Excel chart does not need a title, then it can be removed in two ways:

Change the font and design of the chart title

To change the font of the chart title in Excel, right-click on it and click Font(Font) in the context menu. A dialog box of the same name will open, in which you can configure various font settings.

If you need more detailed settings, select the name of the diagram, open the tab Format(Format) and play around with the various options. Here is how, for example, you can transform the chart title using the Menu Ribbon:

In the same way, you can change the appearance of other chart elements, such as , axis labels, and .

Setting up chart axes in Excel

For most chart types in Excel vertical axis(it is also the value axis or the Y axis) and horizontal axis(it is also the category axis or the X axis) are added automatically when creating a chart.

To hide or show the chart axes, click on the icon Chart elements(Chart Elements), then click the arrow in the line axes(Axes) and tick the axes you want to show, or uncheck the boxes next to those you want to hide.

For some chart types, such as combo charts, a secondary axis may be shown.

When creating 3D charts, you can display depth axis:

For each element of the chart axes in Excel, you can configure various parameters (we'll talk about this in more detail later):

Adding Axis Titles to a Chart

When creating a chart in Excel, you can add titles for the vertical and horizontal axes to make it easier for users to understand what data is shown in the chart. To add axis titles, you need to do the following:

To customize the appearance of the axis title, right-click on it and in the context menu click Axis name format(Format Axis Title). This will open a panel of the same name with a large selection of custom design options. You can also use the options offered on the tab Format(Format) Menu ribbons, as we did with .

Associating axis titles with given worksheet cells

Today you learned how you can customize charts in Excel. Of course, this article only allows you to scratch the surface of the topic of settings and formatting charts in Excel, although much more can be said about this. In the next article, we will build a chart from data that is on various worksheets. In the meantime, I recommend that you practice to consolidate the knowledge gained today.

We quickly created a histogram using fictitious input data. It was possible to stop there, the diagram is there and it is quite eloquent. But what if we want more? What else can be done? What design options does Word provide us with? Now I'll tell you everything.

Most Chart Commands appear on the ribbon when you activate the chart(click on it). Command block appears Working with charts, containing two tabs: Design and Format . In the course of the article, I will no longer specify that you need to highlight the diagram.

Change the chart type

It is quite possible that the type of chart chosen during creation does not suit you. To select another - click on the ribbon Working with charts - Constructor - Type - Change chart type.

A standard window will open, make your choice in it. Experiment, look at the result through the eyes of your readers to find the right option.

Chart styles

Already in Microsoft Word There are several preset styles. Often you can use them to work in their original form, or take them as a template and make your own edits. To set the style - expand the gallery Working with Charts - Constructor - Chart Styles. For example, I will choose Style 14. Some fonts, column widths have changed, a shadow appeared under them.

You can change the set of colors that will be used in the style. To do this, click Working with Charts - Constructor - Chart Styles - Change Colors. In the palette that opens, select the appropriate set.

I will clarify: styles do not change the structure of the chart. They only offer different options for its visualization: colors, fonts, object sizes, etc.

Express layouts in Word

As for the chart layout, there is a set of ready-made solutions here too. They are called Express Layouts and are available here: Working with charts - Constructor - Chart layouts - Express layouts.

I will choose layout number 5, it includes a table with the original data, which allows you to remove them from the text.

Adding Chart Elements

You can customize, show and hide individual chart components. I will list them below. Log in button on the ribbon Working with Charts - Constructor - Chart Layouts - Add Chart Element.

I will describe the elements that can be used on the chart.

Chart axes

In the Axis menu, click on Main horizontal and Main vertical to enable or disable the coordinate axes.

Click Additional Axis Options. A menu will be displayed in which you can make a huge number of settings.

There is simply no point in describing everything here. But I'll single out the following:

• Selection of the point of intersection of the axes– by default, the axes intersect at zero values. This is not always advisable. Change the intersection point to improve the look.
• Value of division, minimum, maximum values ​​- control the divisions on the axis. Accordingly, for these divisions, labels of values ​​\u200b\u200band grid lines will be affixed
• Signature Formats– choose how to style the signature, how to position and align it with respect to the axis
• Axis line color, visual effects (shadow, volume, highlight, smoothing, etc.)

In all additional settings, it is better to “rummage through”, select them individually for each diagram.

Another feature: parameters for each of the axes are set separately. To adjust the horizontal or vertical axis, first click on it with the mouse.

Axis names

Turn axis labels on and off (using the corresponding buttons). Click Additional Axis Title Options to make a deeper adjustment, similar to the previous paragraph.

Try to give names to the coordinate axes so as not to confuse uninformed readers. When the axis name is added to the sheet - click on it to select it, and again - to change it. Enter a short but meaningful axis name. After that, click on an empty area of ​​the chart.

Chart Title

Similarly, it is worth giving a title to the entire chart. This will not only be a good tone, but also an excellent hint to the reader.

Choose one of the proposed location options, or make detailed settings, as in the previous paragraphs. Write down the correct name by double-clicking on the field with the name

Data Signatures

The data labels are the numerical values ​​on the chart next to each category. They may or may not be included. Determine individually as needed. I will not include it in the diagram from the example, since there is already a data table under the horizontal axis.

data table

This is an auxiliary table that replaces the x-axis and contains the source data for the chart. Again, its use allows you to remove numbers from the text altogether. Although such tables are rarely used, take a closer look at them. The data table allows you to minimize the description of numerical information in the text.

Error limit

The inclusion of these markers allows you to show on the graph the possible deviation of the data with the statistical error of the experiment.

Grid

Here we enable and disable the grid lines on both axes. Main and auxiliary grid lines are used, which are drawn based on the selected axis division value. In advanced options, you can fine-tune the appearance of grid lines

Legend

The legend is a small table that indicates what color and what marker each of the rows is. It allows you to quickly understand the contents of the diagram without studying the accompanying text.

lines

These are straight lines descending on the axis at reference points where rows and categories intersect. They allow you to quickly and accurately determine the coordinates of the nodes. It will be easier to show the lines in the picture:

trend line

This is a line showing the average data for the selected series. It allows you to evaluate the trends of the described processes. The trend line is built according to one of the proposed laws: linear, exponential, linear with forecast, linear with filtering. I use exponential most of the time.

Up and down bands

Plot the difference between the values ​​of two series in a category. It is very convenient to use, for example, in plan-fact analysis, in order to more clearly see the difference between the set plan and the actual fact of implementation.

Formatting Individual Chart Elements

To change any individual element of the diagram, select it by clicking on the icon. It will be framed.

Now use the Format tab to make your settings:

• Choose Shape Styles. On the tape it's a block Working with Charts - Format - Shape Styles.

If you expand the styles gallery, there will be a large list of predefined design options. You can use it and make additional changes. To the right of the gallery there will be command blocks:

• Shape fill– select the color and fill method for the selected object
• figure outline- for the outline, specify the color, thickness and method of drawing the line
• Shape Effects- set effects: blank, shadow, reflection, highlight, anti-aliasing, relief, rotation

Using three additional buttons to the right of the gallery, we additionally set the text fill, outline type, and additional effects.

You can diversify the text without WordArt. On the Home - Font ribbon, specify: name, size, font color, boldness, slant, underline, etc. You can change the case, make an index or degree (works great when there are dimensions in the axes labels).

The best effect is achieved by combining all or several of the listed options. However, you can often use a style and that will be enough.

Basically, that's all I wanted to tell you. Let me remind you, when designing, put yourself in the place of readers. The result should please them first of all. Avoid bright colors, a large number of elements, overlaying them on top of each other. At the same time, the diagram should fulfill its role as qualitatively as possible - to systematize and simplify the presentation of numerical data. Go ahead, experiment, share your experience in the comments, I will be glad!

Next post will be about. The lesson is simple and easy enough to give it 5 minutes of your time. Therefore, I'm waiting for you there, see you!

Chart data labels are often necessary but can be cluttered. We will display only those data labels (values) that are greater than a certain threshold value.

Let's build a MS EXCEL chart in the form of a histogram, display data labels (see the example file).

Let's assume that we are only interested in values ​​above 350. Those below can be omitted.

There are several ways to achieve this result, in this article we present 2.

Hiding values<=350 через Формат

The fastest way to get things done is to use data signatures.

To do this, select data labels and right-click to open the window Data label format..., go to the tab Number.

In the Format Code field, enter [>350]# ##0; (Enter a space after the semicolon).

Click Add.

Here we have applied a conditional format (values ​​>350 are displayed in numerical format, the rest of the values ​​are replaced by a space).

With frequent changes in the threshold value, this technique is not very convenient, because You have to manually change the format.

Hiding values<=350 через Дополнительный ряд

If the threshold value changes frequently, then it makes sense to enter it in a separate cell ( E25 ).

Create in a column B additional row using =IF(A25>$E$25;A25;)

If the value is above the threshold value, then the formula outputs it, if not, then 0 is returned. Let's add this series to the chart and set the overlap of the series without a gap.

It is necessary to change the color of the histogram of the additional row from green to blue, and change the data label format to # ##0;# ##0; (so that null values ​​are not displayed).

As a result, we obtain the required result, and when the threshold value in the cell changes E25 the chart will dynamically rebuild.