Excel is a tool that everyone should know how to use, whether they are in school, run a business, or just like to look at charts and graphs. One of the most common questions is how to change X-axis values in Excel, which is also called the horizontal axis. In this article, we will learn How to Change the X-axis Value in Excel with very simple steps.
Download Practice Workbook
You can download the practice workbook from here.
Step-by-Step Procedures to Change X-Axis Values in Excel
Any Excel plot has separate data on the X-axis or horizontal axis. You can see how much each category is worth on the vertical axis. For example, here is a dataset with the Profit of a company separated by Months that we will use to change X-axis values in Excel.
And here is the respective bar chart of the dataset above.
Now we want to replace the Month by the serial number of respective months and see the chart only for Odd serial numbers. To do so we need to follow these steps below.
Step 1: Open Source Data Selection Panel
To start changing the X-axis value in Excel, we need to first open the data editing panel named Select Data Source. To do so we will follow these steps:
- First, select the X-axis of the bar chart and right click on it.
- Second, click on Select Data.
- After clicking on Select Data, the Select Data Source dialogue box will appear.
Step 2: Selection of New Data to Change X-Axis Values
To select new data and replace old data, we need to follow these steps:
- At first, on the Select Data Source panel, click on the Edit. A data selection box will appear.
- A data selection box called Axis Labels will appear.
- On a separate column type the month’s respective numbers serially and select the data like below image. After selection press OK.
- After pressing OK we can see the horizontal axis data has been replaced in the Select Data Source panel like this.
Read More: How to Add Secondary X Axis in Excel (with Quick Steps)
Step 3: Modify Data for Changing X-Axis Values in Excel
To modify data or filter data according to our need, we can select or deselect which data to show on the plot from the Horizontal (Category) Axis Labels option. For our case, we need to show only the Odd serial number’s data. So we need to deselect the Even numbers from the list like in the image below.
Read More: How to Change Axis Labels in Excel (3 Easy Methods)
Step 4: Exit Select Source Data Panel
- After all of the above steps are done, we can exit the Select Data Source panel by pressing OK.
- Finally exiting the panel, we can see our bar chart having data of only Odd serial number months like below.
This how we change the X-axis values in Excel.
Changing Intervals to Change X-Axis Values in Excel
When we have a large number of data or datasets having large intervals, the plot can be too lengthy in Excel. So to adjust the intervals we have these following specifications.
1. On Text/Number Based X-Axis Values
Consider our first dataset and bar chart again. Let’s say we want to see the Month’s name with 3 intervals in between. For example, if we want to see January, then the next label should be April. When we have a dataset containing Numbers or Texts as X-axis data like the bar chart, we can follow the steps below to do so.
- In the beginning, double click on the X-axis. That will bring out the Format Axis option.
- Then, in the Format Axis option, find Labels. Here the intervals are by default selected Automatically.
- For our case, we want the interval to be 3. To do so, select Specify interval unit and set it to 3 and press Enter.
- After pressing Enter, we will have a graph like below.
Read More: How to Switch X and Y-Axis in Excel (2 Easy Ways)
2. On Date Based X-Axis Values
If we have Date type data and we want to do the same thing with our plot, that is, we want an interval of 3 between 2 labels, we have to follow a different approach since Excel has different options for Date type data. To get our expected result for Date type data, consider the below dataset. It shows the Profit of any store from 1 May till 7 May.
Also, we have a bar chart of the given dataset in the following image.
Now to get our expected result, we need to follow these steps.
- First, double click on the X axis to enable Format Axis Here we will see a bunch of extra options for date specified data.
- Next, since our unit is in Days, we’ll set the Major to 3 or desired intervals and press Enter.
- After this step, we’ll have our previous bar chart in the following form.
Read More: How to Add X and Y Axis Labels in Excel (2 Easy Methods)
Things to Remember
- While replacing our X-axis values in the mentioned process, we need to make sure our new replacing values are the same in number as previous values.
- Changing Interval will not remove the rest of the columns having no label. If we want to do so, we will have to follow the previous method.
- For Date type data we need to carefully look at the Interval Unit. For units in Days, if we select Months, we will get the wrong output.
Still facing any problems to change X-axis value in Excel? Feel free to ask through the comment box. Our team will be happy to answer your questions. For any type of Excel based problems, visit our website Exceldemy.com.