Change horizontal axis data in excel. Change the appearance of chart axes
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 for 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).
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.
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 .
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:
On the chart, select the category axes you want to change, or do the following to select it from the list of chart elements:
On the tab Format in Group Current fragment click the button Selection Format.
In area Axis format
Important:
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 .
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 determines the axis type that is best suited 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.
This action applies in Microsoft Word for Mac only: On 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.
Good afternoon.
When creating graphs based on Excel tables, the program automatically calculates the values and builds a graph based on these calculations, but there is a possibility that the automatically created graph will be inconvenient or unpresentable. To correct its appearance, you just need to swap the axes: set the X axis vertically, and the Y axis horizontally.
How to build a graph and change axes?
For example, let's take a table with a sales schedule for a pet store. The names of animals are written vertically, months of sales horizontally.
Let's build a graph. To do this, select the table with the left mouse button. In the insert tab, select “insert graph”. After clicking on the “Graph” icon, a window with a graph will appear on the sheet. 
But the window with the graph does not look presentable, since the names of the animals are located horizontally, and the months are simply written as the names of the graphs. It is impossible to track the dynamics of sales for each type of product from the graph. 
To change the graph view and swap the axes, follow these steps:
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 a preview of the chart and then at the bottom the Row 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 presentation type.
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 spreadsheet. Everything should work out.
Most chart types allow you to hide or show axes. To make your chart data easier to understand, you can also change its appearance.
Important: This article does not cover changing the scale of chart axes. For information about changing the scale, see the following articles:
More about axes
Charts typically have two axes that measure and organize data: a vertical (value axis or y-axis) and a horizontal (category axis or x-axis). 3D histograms, 3D cone charts, and pyramid charts have a third axis, the depth axis (row axis or z axis), so that data can be plotted to the depth of the chart. Radar charts have no horizontal (category) axes, and pie and donut charts have no axes at all.
Vertical axis (values)
Horizontal axis (categories)
Depth axis (data series)
Here's what you can do with your charts to make them look more impressive and convey information better. For more information about what axes are and what you can do with them, see .
Showing and hiding axes
Setting axis tick marks and labels
Changing the number of categories between labels and divisions
Change the alignment and orientation of labels
Advice You can change the horizontal alignment of an axis label by clicking the axis and selecting from the toolbar home option Align Left , Align Center or Align Right .
Changing the text of category labels
Note
Changing the appearance of text and numbers in captions
Text formatting
On the toolbar home select the type of formatting you need.
Advice home in Group Font.
Formatting numbers
Note quantity / total = percentage 10 / 100 = 0,1 and then format 0,1 10 % .
Change the appearance of chart axes in Office 2010
Show or hide axes (Office 2010)
Set tick marks and axis labels (Office 2010)
Change the number of categories between labels and tick marks (Office 2010)
Change the alignment and orientation of labels (Office 2010)
The alignment of labels can be changed on both the horizontal (category) and vertical (values) axis. If your chart has multi-level category labels, you can change the alignment of each level of labels. You can also change the distance between label levels on the horizontal (category) axis.
Advice To change the horizontal alignment of an axis label, right-click the axis and click mini toolbars select Left , Centered or Right .
Change the text of category labels (Office 2010)
You can change the text of category labels on the sheet or directly on the chart.
Change the text of a category label on a sheet
On the worksheet, click the cell with the signature text that you want to change.
Type new text and press Enter.
Note. After you make changes to the sheet, the chart is automatically updated.
Changing the caption text on a chart
Change the appearance of text and numbers in captions (Office 2010)
You can change the format of the label text on the category axis or the numbers on the value axis.
Text formatting
In the chart, right-click the axis with the labels you want to format.
On mini toolbars Select the formatting options you want.
Advice You can also select an axis with labels and then use the formatting buttons on the tab home in Group Font.
Formatting numbers
Note. Before you format numbers as percentages, make sure that the numbers shown in the chart were calculated as percentages in the source data and are displayed in decimal format. Percentages are calculated in the worksheet using the expression quantity / total = percentage. For example, if you calculate the value 10 / 100 = 0,1 and then format 0,1 As a percentage, the number should be displayed as follows: 10 % .
Add tick marks to an axis
The axis can be formatted to display major and minor tick marks at selected intervals.
This action applies to Word for Mac only: in the menu view select item Page layout.
Click the chart and open the tab Constructor.
Click the button Add a chart element > axes > Additional Axis Options.
On the panel Axis format expand the element divisions and select Options for major and minor tick types.
After you add tick marks, you can change the spacing between tick marks by changing the value in the field character spacing .
All about axes
Different chart types may display axes differently. For example, scatter (xy) and bubble charts display numerical values on both a horizontal and vertical axis. An example would be displaying the amount (inches) of precipitation relative to barometric pressure. Both elements have numerical values, and data points are plotted on the x and y axes relative to those values. There are many options available for value axes, such as setting the scale to logarithmic.
Other types of charts, such as histograms, graphs, and area charts, display numeric values only on the vertical (value) axis and text classifications (or categories) on the horizontal axis. An example would be to display the amount (inches) of precipitation by geographic area. In this example, geographic areas are text categories of data that are displayed on the horizontal (category) axis. Geographic areas that represent text are spaced evenly, unlike values that can be measured. Keep this distinction in mind when choosing a chart type, since the capabilities for value and category axes differ. In such cases, the depth (series) axis is another kind of category axis.
When you create a chart, the axes display tick marks and labels by default. The way they are displayed can be customized using main and intermediate divisions and labels. To avoid cluttering a chart, you can display fewer labels or tick marks on the horizontal axis (categories) by specifying spacing for labeling categories or the number of categories displayed between tick marks.
You can also change the alignment and orientation of labels, or the display format of text and numbers (for example, to display numbers as percentages).
Note: This page has been automatically translated and may contain inaccuracies and grammatical errors. It is important to us that this article is useful to you. Was the information useful? For convenience also (in English).