Zehad Rian Jim
This tutorial will demonstrate how to flip data in an excel chart. While dealing with lots of data or showing excel in any report, flipping data can be very useful. Sometimes after doing lots of work, it seems that it will be more effective if the data is flipped. If you try to manually do that, it not only will kill time but will create the opportunity to make mistakes. So, we have to focus on learning flipping data.
Download Practice Workbook
You can download the practice workbook from here.
5 Easy Methods to Flip Data in Excel Chart
We’ll use a sample dataset overview as an example in Excel to understand easily. In this case, we have Salesperson in Column B and Sales in Column C. We will use this dataset to describe all the methods. If you follow the methods correctly, you should learn how to flip data in an excel chart on your own.
1. Flipping Data Only Vertically
In this case, our goal is to flip data in an excel chart only vertically. To do that we have to follow the below steps.
Steps:
- First, go to selected data > Insert > 2-D Column options to create a chart.
- Then, you will the desired chart.
- After that, select the whole dataset and go to the Data tab.
- Next, in the Data tab, click on the Sort option.
- Afterward, the Sort dialog box will open on the screen.
- Furthermore, select Sales in Sort by option and Largest to Smallest in Order option and press OK.
- Finally, you will get the desired result.
Read More: How to Flip Data Upside Down in Excel (4 Ways)
2. Flipping Data Only Horizontally
Now, our aim is to flip data in an excel chart only horizontally. It is similar to the previous method with a slight change. The steps of this method are.
Steps:
- At first, create the data chart following the first method and you will the below chart.
- Next, select the dataset > Data > Sort options.
- Second, the Sort dialog box will open on your display. Click the Options to open the desired dialog box.
- Third, select the Sort left to right option in the dialog box and press OK.
- Fourth, select Row 4 in the Sort by option and press OK.
- Last, you will get the desired result. In this case, after flipping you won’t find any graph in the chart.
Read More: How to Flip Excel Sheet from Left to Right (4 Easy Ways)
3. Using INDEX Function
The INDEX Function returns a value or a reference to a particular cell. We will use the INDEX Function to flip data in excel chart in the following way.
Steps:
- To begin with, create the data chart following the first method and you will the below chart.
- insert the following formula in cell E5.
=INDEX($B$5:$C$11,ROWS(B5:$B$11),COLUMNS($B$5:B5))
- In addition, press Enter to get the desired result.
Read More: How to Flip Axis in Excel (4 Easy Methods)
4. Use of SORTBY Function
If you are using Microsoft 365 then, you will get an extra option called the SORTBY Function. Using this function is very helpful as it is much easier to under than the previous INDEX Function method. The steps of this method are described below.
Steps:
- At first, create the data chart following the first method and you will the below chart.
- insert the following formula in cell E5.
=SORTBY($B$5:$B$11,ROW(B5:B11),-1)
- Then, press Enter to get the desired result.
Read More: How to Flip Data in Excel from Bottom to Top (4 Quick Methods)
5. Applying VBA Code
We can also flip data in an excel chart by using the VBA code. VBA code is very efficient in flipping data in every situation. The steps of this method are.
Steps:
- Firstly, create the data chart following the first method and you will the below chart.
- Then, press Ctrl+F11 to open the VBA window and go to the Insert > Module options.
- Secondly, insert the following code in the window.
Sub FlipData()
Dim TRw As Variant
Dim LstRw As Variant
Dim SrtNum As Integer
Dim LstNum As Integer
Application.ScreenUpdating = False
SrtNum = 1
LstNum = Selection.Rows.Count
Do While SrtNum < LstNum
TRw = Selection.Rows(SrtNum)
LstRw = Selection.Rows(LstNum)
Selection.Rows(LstNum) = TRw
Selection.Rows(SrtNum) = LstRw
SrtNum = SrtNum + 1
LstNum = LstNum - 1
Loop
Application.ScreenUpdating = True
End Sub
- Thirdly, press the Run option to use the code.
- Lastly, you will get the desired result.
Read More: How to Flip Bar Chart in Excel (2 Easy Ways)
How to Reverse Axis in Excel Chart
In many cases, you need to reverse the axis of any Bar chart in excel. To fulfill that, you have to follow the below steps.
Steps:
- First, go to select data > Insert > 2-D Bar Chart options.
- Second, you will get the desired chart.
- Third, right-click on the y-axis of the chart and select the Format Axis option.
- Fourth, the Format Axis option will come on the screen. Select the Values in reverse order option from the Axis Options.
- Last, you will get the desired result.
Things to Remember
- In the first two methods, we have flipped the data only vertically and horizontally individually. So, these methods are helpful while flipping data to one side only.
- In the case of using the INDEX Function, you have to be careful about the selected cells otherwise it won’t work.
- The SORTBY Function isn’t available in all office files. It is only available for Microsoft 365 users.
- Using VBA code is the most efficient way among all the methods. But it is recommended to understand and apply it correctly.
Conclusion
Henceforth, follow the above-described methods. Thus, you will be able to flip data in an excel chart. Let us know if you have more ways to do the task. Follow the ExcelDemy website for more articles like this. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.
