excel axis options. Customize charts in Excel: add title, axes, legend, data labels and much more. Changing the appearance of text and numbers in captions

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 is also additional options, with which you can configure the axial 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 more carefully configure the axis by clicking on ADDITIONAL PARAMETERS.

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

Share our article on your social networks:

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:

Click anywhere on the chart.

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

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

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

In the area Axis format

Important:

Axis parameters and check the box categories in reverse order.

Axis parameters and select text axis or date axis in the 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 is minimum and maximum values Each axis on 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 the 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 the 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 in the best possible 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.

It is not always possible to immediately create a graph and chart in Excel that meets all user requirements.

Initially, it is difficult to determine which type of graphs and charts is best to present data in: a volumetric chart, a stacked bar chart, or a chart with markers.

Sometimes the legend is more of a hindrance than a help in presenting data and it is better to turn it off. And sometimes you need to connect a table with data to the graph to prepare a presentation in other programs (for example, PowerPoint). Therefore, it is worth learning how to use the settings for graphs and charts in Excel.

Changing graphs and charts

Create a table with data as shown below. You already know how to build a graph in Excel using data. Select the table with the data and select the “Insert” - “Histogram” - “Histogram with Grouping” tool.

The result is a graph that needs to be edited:

remove legend;
add table;
change chart type.

Chart legend in Excel

You can add a legend to the chart. To solve this problem we perform the following sequence of actions:

Left-click on the graph to activate it (select it) and select the tool: “Working with Charts” - “Layout” - “Legend”.
From the Legend tool's drop-down list of options, select the option: "None (Do not add legend)." And the legend will be removed from the chart.

Table on graph

Now you need to add a table to the chart:

Activate the graph by clicking on it and select the “Chart Tools” - “Layout” - “Data Table” tool.
From the drop-down list of options for the “Data Table” tool, select the option: “Show Data Table”.

Types of graphs in Excel

Select the “Chart Tools” - “Design” - “Change Chart Type” tool.
In the “Change chart type” dialog box that appears, specify the names of groups of chart types in the left column - “With areas”, and in the right section of the window select “With areas and stacking”.

For complete completion, you still need to label the axes on the Excel graph. To do this, select the tool: “Working with Charts” - “Layout” - “Axis Titles” - “Name of the Main Vertical Axis” - “Vertical Title”.

Near the vertical axis there is a place for its title. To change the vertical axis title text, double-click on it with the left mouse button and enter your text.

Delete the schedule to move on to the next task. To do this, activate it and press the key on the keyboard – DELETE.

How to change the color of a graph in Excel?

Based on the original table, create a graph again: “Insert” - “Histogram” - “Histogram with grouping”.

Now our task is to change the fill of the first column to gradient:

Tools for complex design of gradient fills on charts are now available to you:

name of the workpiece;
direction;
corner;
gradient points;
color;
brightness;
transparency.

Experiment with these settings, and then click “Close”. Please note in the "Name of the workpiece" are already available ready-made templates: flame, ocean, gold, etc.

How to change data in Excel graph?

A graph in Excel is not a static picture. There is a constant connection between the graph and the data. When the data changes, the “picture” dynamically adapts to the changes and thus displays the current indicators.

We will demonstrate the dynamic connection of the graph with data at finished example. Change the values in the cells of the range B2:C4 of the original table and you will see that the indicators are automatically redrawn. All indicators are automatically updated. It's very convenient. There is no need to re-create the histogram.

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 right-click on the border area of the diagram and select this command from 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 new look presentations.

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. Compose your Excel chart in your new table. Everything should work out.

Recently I had a task to show the level of employee competencies 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 numbers indicating the scale of the series. You can control the formatting of labels, but their content is strictly determined by Excel rules. I wanted that instead of 20%, 40%, etc. The graph displayed the names of competency levels, something like:

Rice. 1. Competency level diagram along with initial data for construction.

The solution method was suggested to me by an idea gleaned from John Walkenbach’s book “Diagrams in Excel”:

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

The chart is actually a hybrid chart: it combines a graph and a scatter chart.
The "real" value axis is hidden. Instead, it displays a scatter plot 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 custom row data signatures scatter plot, not axis labels!

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

Rice. 2. Example of a formatted scatter plot.

Rice. 3. Inserting a symbol into a data signature.

Let us now look at the steps to create the diagram shown in Fig. 1.

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

Place the legend at the top, add a title, set fixed parameters for the value axis: minimum (zero), maximum (1), value of the main divisions (0,2):

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

Select the radio buttons for new rows and Values (Y) in columns. Select the Row names in the first row and Categories (X-axis labels) in the first column check boxes.

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 created auxiliary vertical (right) and horizontal (top) axes. Remove the secondary vertical axis. This will set the scale for the scatter plot series to be the same as for the main histogram. Select the secondary horizontal axis and format it, specifying none for major tick marks and none for axis labels:

Select the main vertical axis and format it by setting the major tick marks to None and the axis labels to None.

Select the scatter plot series and format it. The line color is black, the marker is similar to the axis divisions (select the marker size and set the color to black), select the line thickness so that it does not differ from the horizontal line, add data labels (no matter what). Format the data signatures using the Signature Options tab – Left.

Enter the legend, highlight and delete the series description associated with the scatter plot.

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