How to change the scale in an excel chart. Change the scale of the horizontal axis (categories) in the chart. Changing the number of categories between labels and divisions

If you want to change the axes in a chart in Excel to highlight your data under a different aspect, you do not need programming knowledge. On the contrary, you are only a couple of clicks away from the desired image.

Replacing chart axes in Excel versions prior to 2003

Select your chart and call “Data Generator” through the “Diagram” menu item, which appears after selecting it in the menu bar. Alternatively you can click right click mouse in the border zone of the diagram and select this command in the context menu.

The Data Generator window appears. On the "Data Field" tab you can see preview chart and then at the bottom the "Series in:" command with the "Rows" and "Columns" options. Click on "Strings" and see a preview. Excel changed the axes and automatically coordinated the explanation entries. By clicking on OK you confirm the new kind representation.

From Excel 2007: replacing x and y axes

This is even easier in newer versions of Excel. Once you select a chart, the three chart utility tabs in the menu area automatically open. On the Structure tab, in the Data group, click the Replace Row/Column command to replace the axes. We recommend replacing the columns and rows of the original table.

If the appearance of the chart after changing the axes does not correspond to your ideas, as an alternative you can also first change the columns to rows in your table of values. To do this, select the table and copy it. (Important: This does not work with the "Cut" command!).

Click a cell in another area of ​​your spreadsheet and select Move next to or under the Insert button. Column values ​​appear in columns, and column values ​​appear in rows. Create your Excel chart in your new table. Everything should work out.

What's 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 simple and convenient. Microsoft has put a lot of effort into making the setup process simple and the necessary options easily accessible. Later in this article we will show several simple ways How to add and customize all the basic chart elements in Excel.

3 Ways to Customize Chart Options in Excel

1. Select the chart and use the tabs from the group Working with charts(Chart Tools) – Constructor(Design) and Format(Format).
2. Right-click on the diagram element that needs to be configured and select the desired command from context menu.
3. Use special icons that appear near the right top corner diagrams if you click on them with the mouse.

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

Advice: To open immediately required section panel to configure chart parameters, double-click on the corresponding element on the chart.

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

How to Add a Title to an Excel Chart

In this section, we will cover how to add a title to a chart in different versions Excel, and show you where the main tools for working with diagrams are located. In the rest of the article, we will look at 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 “ is automatically inserted into its title. Chart title“. To change this text, simply select it and enter your own name:

You can also link the chart title to a cell in the worksheet 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 name was not added automatically, then click anywhere on the diagram so that a group of tabs appears Working with charts(Chart Tools). Open the tab Constructor(Design) and press Add a chart element(Add Chart Element) > Chart title(Chart Title) > Above the diagram(Above Chart) or Centered (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 parameter Chart title(Chart Title) you can click the arrow pointing to the right (see the figure above) and select one of the proposed options:

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

To find more options, open the tab Constructor(Design) and press Add a 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 parameters you need.

Adding a title to a chart in Excel 2010 and Excel 2007

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

Linking the chart title to a worksheet cell

Diagrams various types in Excel are most often created with alt text instead of a title. To set your own title for a chart, you can either select the chart field and enter the text manually, or bind it to a worksheet cell that contains, for example, a table title. In this case, the title of the Excel chart will automatically update every time the contents of the associated cell change.

To associate a chart title with a worksheet cell:

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

IN in this example we associate the Excel chart title with the cell A1. You can select two or more cells (for example, multiple column headers) and the chart title will display the contents of all selected cells.

Moving the title in the diagram

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

Removing the chart title

If you don't need a title for an Excel chart, you can remove it in two ways:

Changing the font and design of the chart title

To change the chart title font 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 parameters.

If you need more detailed settings, highlight the chart title, open the tab Format(Format) and play with different parameters. Here's how, for example, you can change the title of a chart using the Menu Ribbon:

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

Setting up chart axes in Excel

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

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

Some chart types, such as combo charts, may show a secondary axis.

When creating 3-D diagrams, 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 to the vertical and horizontal axes to help users understand what data is shown in the chart. To add axis titles, you need to do this:

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

We associate the names of the axes with the given cells of the worksheet

Today you learned about how you can customize charts in Excel. Of course, this article only scratches the surface of the topic of setting up and formatting charts in Excel, although there is much more that can be said about this. In the next article, we will build a chart from the data that is in different worksheets. In the meantime, I recommend that you practice to consolidate the knowledge you have acquired today.

The chart's horizontal axis, also called the category axis and x-axis, displays text labels instead of numeric intervals; In addition, it supports fewer zoom options than the chart's vertical axis (the value axis, or y-axis).

However, you can set the following axis parameters:

Spacing between tick marks and labels.

Location of signatures.

Axis type (dates or text).

Location of divisions.

The point of intersection of the horizontal and vertical axes.

To change the scales of other axes, see Change the scale of the vertical axis (values) in a chart or Change the scale of the depth axis (data series) in a chart.

On the chart, select the category axes you want to change, or do the following to select it from the list of chart elements:

1. Click anywhere on the chart.

   A tab will open Working with charts with additional tabs Constructor And Format.

   On the tab Format in Group Current fragment Click the arrow in the box at the top and select .

On the tab Format in Group Current fragment click the button Selection Format.

In area Axis format

Important:

• Axis parameters and check the box categories in reverse order.

  Axis parameters and select text axis or date axis in field Axle type

  Note:

  Axis parametersintersect the vertical axis, install in room category in the maximum category

  By divisions spacing between marks.

  Note:

  By divisions main And additional type.

  signatures and then in the section signature spacing select Interval unit

  Advice: Enter 1 2 3

  stickers distance from axis.

  Advice:

Change the scale of the horizontal axis (categories) in a chart (Office 2010)

Adviсe:

By default, the scale of the minimum and maximum values ​​of each axis in the chart is calculated automatically. However, you can adjust the scale to better suit your needs. For example, if all data points in a data table are between 60 and 90, you can change the value (y) axis to have a range of 50 to 100 instead of 0 to 100. When the value axis covers a very large range, you can also change the logarithmic scale (also known as log scale) of the axis .

This action applies in Microsoft Word for Mac only: In the menu View select item Page layout.

Click the chart.

On the tab Format click the button horizontal axis (categories) in the drop-down list and click the button Format area.

In area Axis format Do one of the following:

Important: These zoom options are only available when the category axis is selected.

• To change the order of categories, expand Axis parameters and check the box categories in reverse order.

  To change the axis type to text or date axis, expand Axis parameters and select text axis or date axis in field Axle type. Text and data points are evenly spaced on the text axis. The date axis displays dates in chronological order in specified time intervals or base units, such as number of days, months, or years, even if the dates on the worksheet are not ordered or in the same base units.

  Note: Default value Automatically select based on data defines the type of axis that the best way suitable for the selected data type.

  To change the points where you want the vertical axis (values) to cross the horizontal axis (categories), expand Axis parametersintersect the vertical axis, install in room category and enter the desired number in the text field or select in the maximum category indicate that the vertical axis (values) crosses the horizontal axis (categories) after the last category on the horizontal axis.

  To change the tick spacing, expand By divisions and enter the number you want in the dialog box spacing between marks.

  Note: This number determines the number of categories that are displayed between the tick marks.

  To change the position of the axis tick marks, expand By divisions and select the parameters that should be in the fields main And additional type.

  To change the spacing between axis labels, expand signatures and then in the section signature spacing select Interval unit and enter the number required in the text box.

  Advice: Enter 1 to display a caption for each category, 2 to display a caption for every second category, 3 to display a caption for every third category, etc.

  To change the location of the axis labels, expand stickers and enter the number you want in the field distance from axis.

  Advice: To position the labels closer to the axis, enter a lower number. To increase the distance between the label and the axis, enter a larger number.

  To change the location of the label, expand marks and select one of the options from the drop-down list.

An axis is a scale that is used to plot values ​​on a chart. The horizontal axis is called the category axis and the vertical axis is called the value axis, except for a scatter plot in which the horizontal axis is also the value axis. When creating a chart, the parameters of the category and value axes are set automatically based on the displayed values ​​(see Fig. 3.17), which can then be changed different ways. In the process of changes, the chart's category axes most often change the interval between its divisions, as well as the coordinates of the place where it intersects with the chart's value axis. When you change a chart's value axes, you most often change the scale used, as well as the number formatting you choose for the units plotted on that scale.

Algorithm 3.6. Formatting a chart's value axis

To make changes to the format of chart elements Vertical or Horizontal axis (values) you need to do the following:

1. Select the value axis on the chart - click in the value axis area of ​​the chart, or, having selected the chart area, click on the corresponding element in the button drop-down list Chart elements, which is in the section Current fragment tabs Layout from the contextual tab Working with charts. This combo box displays the name of the current item in the chart. The value axis on the chart will be marked with highlight markers (see Algorithm 3.6).

2. You can change the placement of the label on the chart axis by selecting an option in the button drop-down menu Axles(Fig. 3.17) on the tab Layout (None, Default, Thousands, Millions and etc.).

Rice. 3.17. Button Dropdown Menu Axles on the tab Layout

3. To change the settings of the value axes, you need to call the dialog box Axis format with tab selected Axis parameters(Fig. 3.19) in one of the following ways:

3.1. click on the button Selected Object Format In chapter Current fragment tabs Layout;

3.2. select from the button drop-down menu Axles on the tab Layout parameter Additional horizontal axis options;

3.3. select from the context menu that is associated with the current element Axles diagrams, command Axis format(Fig. 3.18).

4. To change the appearance of the value axis for each of the following axis parameters, you can set the switch fixed, and then enter the new value in the appropriate text field.

4.1. Minimum value - the parameter is intended to determine the point from which the axis begins (a value greater than zero is entered);

Rice. 3.18. View of the context menu that is associated with the selected horizontal value axis

4.2. Maximum value - the parameter is intended to determine the most distant point displayed on the vertical axis (a value greater than zero is entered - data values ​​greater than the maximum value will not be displayed on the diagram);

4.3. Main price or intermediate divisions - the parameter is intended to change the distance between the main or intermediate horizontal divisions (enter a new distance value);

5. The vertical axis intersects - The parameter is intended to change the position of the point at which the vertical axis intersects the horizontal. The parameter value is set by setting the switch

6. Axis value followed by entering a value that determines the coordinates of the intersection of the axes.

7. To display scale numeric values ​​on the value axis, the default format is General. To assign a new number format to scale values, click the tab Number dialog box Axis format, which looks the same as a dialog box Data Signature Format(Fig. 3.12), and then set the number of decimal places that will be displayed on the diagram.

Rice. 3.19. Tab Options Axis parameters with selected axis (values) for a typical 2D chart

Exercise 3.7. Formatting the scatter plot's value axes y 1 =sin(x) and y 2 =cos(x)

Following the instructions of Algorithm 3.6, perform the following actions.

1. Formatting the horizontal value axis.

1.1. Select the horizontal axis of values ​​in the chart (Fig. 3.19).

1.3. Set fixed minimum value equal to -4, and the maximum value equal to +4.

1.4. Enter a new value for the distance between the main and intermediate divisions on the horizontal axis equal to 0.5.

1.5. Set parameter value Axis labels- at the bottom.

1.6. Set the display format for numeric values ​​for axis labels – Numeric,1.

1.7. Change default axis label font (Calibri, 10) to font Arial, 8, bold.

2. Formatting the vertical axis of values.

2.1. Select the vertical axis of values ​​in the chart (Fig. 3.19)

2.2. See paragraph 1.2.

2.3. Set a fixed minimum value to -1 and a fixed maximum value to +1.

2.4. Enter a new value for the distance between the main and intermediate divisions on the vertical axis equal to 0.2.

2.5. See paragraphs. 1.5-1.7.

3. As a result of formatting, the diagram will look like in Fig. 3.20.

Rice. 3.20. Formatting a chart's value axes

The chart in the table editor is built on the basis of data, which is always displayed along the axes. The axes display the values ​​of this data. But, by default, the editor may leave the axes blank or display numbers in the wrong format. To fix this you need to understand how to correctly label axes in Excel using the built-in chart settings functions.

There is a LAYOUT tab in the program menu, which allows you to customize elements. Find the AXES NAME button and select VERTICAL AXIS NAME from the drop-down list. Next, the editor offers a choice regarding the placement of the inscription from three options: rotated title, vertical or horizontal.

After selecting the first item, an inscription will appear in the picture, placed as required. You need to click on the field and change the text as you wish.

Below you can see how the title is positioned after activating the vertical layout option.

And when choosing horizontal placement, the inscription will look like this.

Horizontal axis name

We perform the actions by analogy with placing the name of the vertical axis. But, there are some differences. The user is provided with only one way to position the label: the title under the axis.

After the horizontal inscription appears, we change its contents.

How to label axes in Excel? Horizontal Axis Labels

The axis guide of the chart is usually divided into several equal parts, which allows you to define the ranges of the curve. They can also be signed in your own way.

On the AXIS button there is an item MAIN HORIZONTAL AXIS. After clicking on it, a caption settings menu appears, thanks to which you can completely remove signatures by selecting NO SIGNATURES or NO from the list.

By default, Excel displays text from left to right, but you can change this by clicking on the RIGHT TO LEFT menu item.

There are also additional parameters with which you can configure the axis of data in detail: the interval between labels, the type of axis, its location relative to the text, recording format, etc.

Vertical Axis Labels

To configure the vertical axis, you need to select the MAIN VERTICAL AXIS item of the AXIS button. You can remove the signatures or display them according to the numerical characteristics:

Millions.

Billions.

Logarithmic scale.

The figure below shows captions in thousands.

It is also possible to configure the axis more thoroughly by clicking on ADDITIONAL PARAMETERS.

These are the main ways to configure the division scale and axis names. By following this algorithm, even a novice user can easily cope with the process of editing a diagram.

Share our article on your social networks: