Excel charts are very effective when it comes to visually representing data. Sometimes after creating a chart, we often might feel the necessity to reverse it for the sake of better presentation and understanding. Therefore, we will learn in this article how to reverse data in an Excel chart with 4 useful methods.
How to Reverse Data in Excel Chart: 4 Useful Methods
Here is an example of a dataset. It shows the information on sales amount from the month January to June in a year.
Now follow the methods below to create an excel chart from this dataset and reverse the data of the chart.
Method 1: Reverse Data in Excel Chart by Formatting Axis
In this method, we will create a chart at first, then we will reverse the data by formatting the chart axis. Follow the steps below:
- Firstly, select cell range B4:C10.
- Secondly, go to the Insert tab and click on Bar Chart under the Charts group.
- Thirdly, select the 2-D Clustered Bar from the drop-down menu.
- Therefore, the chart will look like this:
- Now, we will reverse the data in this chart.
- For this, right-click on the vertical axis and select Format Axis.
- Then, mark checked the Category in reverse order box.
- Similarly, open the Format Axis panel for the horizontal axis.
- Here, select the option Maximum axis value.
- Along with it, mark the Values in reverse order box.
- After this, make the Label Position as High.
- Lastly, set the Minimum value of Bounds at 3000.
- Finally, you will successfully reverse data in your Excel chart.
Read More: How to Reverse Order of Data in Excel
Method 2: Apply Sort Tool for Reversing Data in Excel Chart
In this method, we will reverse chart data with the help of the Sort tool in Excel. It will allow you to sort the dataset value according to the quantity of sales amount. Let’s see the process below:
- First, insert a 2-D Bar Chart like the above with the same dataset.
- Then, select cell range B5:C10.
- Afterward, go to the Data tab and select Sort under the Sort & Filter group.
- Following, you will see the Sort window.
- Here, insert Sales and Cell Values as Column and Sort On categories, respectively.
- Along with it, set the Order as Largest to Smallest.
- Lastly, press OK.
- That’s it, you will see the chart data has changed automatically.
Method 3: Flip Excel Chart Data Using Select Data Command
In this third method, let us exchange the chart data with the help of the Select Data command in Excel. This command is beneficial when you want to choose values between a large dataset for reordering. Following is the process:
- In the beginning, create the initial chart like before.
- Next, select the chart and go to the Chart Design tab.
- Here, click on Select Data under the Data group.
- Afterward, reverse the column and row by clicking the Switch Row/Column icon.
- Next, use the Move Up and Move Down buttons in the Legend Entries (Series) box to make the position of the values.
- Finally, the reversed data chart is shown in the image below:
Method 4: Insert Excel VBA Code for Reversing Chart Data
The term VBA stands for Visual Basic Application. It is the programming language in Microsoft Excel that allows you to perform many complex tasks in a very short time. At this last stage, we will insert VBA Macro code to reverse the Excel chart data. Let’s see the steps below:
- First, insert a 2-D Column Chart based on the similar dataset we described above.
- Next, go to the Developer tab and select Visual Basic under the Code group.
- Then, select Module from the Insert tab.
- Following, insert this code on the blank page.
Dim Tw As Variant
Dim Lw As Variant
Dim SNum As Integer
Dim LNum As Integer
Application.ScreenUpdating = False
SNum = 1
LNum = Selection.Rows.Count
Do While SNum < LNum
Tw = Selection.Rows(SNum)
Lw = Selection.Rows(LNum)
Selection.Rows(LNum) = Tw
Selection.Rows(SNum) = Lw
SNum = SNum + 1
LNum = LNum - 1
Application.ScreenUpdating = True
- Afterward, click on the Run Sub of press F5 on your keyboard.
- Lastly, click on the Run button in the Macros window.
- That’s it, you will see that the values have turned reverse and the chart also changed in parallel.
Things to Remember
- Prepare your dataset carefully so that it can benefit the process of reversing.
- Make sure there is no blank cell. Otherwise, it will result in false output.
- It is better to keep a copy of the original dataset for future reference.
- You must select the whole dataset before running the VBA code.
Download Practice Workbook
Download this sample file to try at home.
Henceforth, I hope it was an efficient article for you on how to reverse data in an Excel chart with 4 useful methods. Give it a try and let us know your feedback.
- How to Reverse Data in Excel Cell
- How to Mirror Data in Excel
- How to Reverse Text to Columns in Excel
- How to Reverse Column Order in Excel
- How to Reverse Order of Columns Vertically in Excel