Osman Goni Ridwan
If you are searching for the solution or some special tricks to flip data in Excel from bottom to top then you have landed in the right place. There are some very easy ways to do this. This article will show you every step with proper illustrations so, you can easily apply them for your purpose. Let’s get into the central part of the article.
Download Practice Workbook
You can download the practice workbook from here:
4 Methods to Flip Data in Excel from Bottom to Top
In this section, I will show you 4 quick and easy methods to flip data in Excel from bottom to top
on Windows operating system. You will find detailed explanations with clear illustrations of each thing in this article. I have used Microsoft 365 version here. But you can use any other versions as of your availability. If anything of this article doesn’t work in your version then leave us a comment.
1. Flip Data Using the Sort Feature
Excel has a Sort feature by default in it so you can easily reverse the serial from bottom to top. But to use this feature you have to create a helper column and insert serial numbers like 1 2 3. Then sort the dataset through this column. Here, I am describing the full procedure step by step:
📌 Steps:
- Create another blank column at the end of the dataset.
- Then, insert 1 and 2 in the first 2 cells of the column.
- After that select the first cells, where you have inserted 1 and 2, and drag the Fill Handle icon to the last cell of the column so this column will be auto-filled by the serial numbers.
- Now, you have the helper column filled with serial numbers. So, you can now sort them from bottom to top using the Sort feature.
- Now, select a cell from the Helper column. And go to the Data tab.
- Here, you will find both A → Z which means sort from Lowest to Highest, and Z → A means sort from Highest to Lowest.
- So, in this case, you will select Z → A option to sort the help column from the highest to the lowest. Click on the Z → A
- Now, you will see the magic. The whole dataset is flipped from bottom to top
Alternatively, you can sort a dataset by a column from Sort & Filter option in the Home tab. Follow the steps below to sort using this option.
- Select a cell from the helper column similarly.
- Then, go to the Home tab in the top ribbon and go to the Editing menu, and select the Sort & Filter
- Then, choose the “Sort Largest to Smallest” option here.
- As a result, the whole dataset will be reversed from bottom to top.
Read More: How to Flip Data Upside Down in Excel (4 Ways)
2. Flip Data in a Table
You can also flip data from bottom to top using the Table feature. For this, first, you have to convert the dataset into a table then from the header menu, you can sort the dataset from top to bottom or bottom to top. Follow the steps below to use this feature:-
📌 Steps:
- First, select any cell of the dataset.
- Then go to the Home tab and select the Format as Table
- After that, a list of styles will appear. Select any table style from them.
- After selecting the Table Style, a pop-up window will appear.
- Here, you will see that the range of datasets is selected automatically. Recheck the range with the dataset and press OK.
- As a result, the dataset will be converted into a table and you will see a drop-down arrow in each column header.
- Click on the drop-down arrow of the Helper column header.
- And, select the option “Sort Largest to Smallest” to flip the dataset from bottom to top.
- As a result, the whole dataset is flipped from bottom to top.
Read More: How to Flip Rows in Excel (4 Simple Methods)
Similar Readings
- How to Flip Columns and Rows in Excel (2 Easy Methods)
- Change Excel Sheet from Right to Left (4 Suitable Ways)
- How to Flip Excel Sheet from Left to Right (4 Easy Ways)
3. Flip Data of a Column Using Formula
You can also use Excel functions to create a formula for flipping data from bottom to top. But, the formula won’t allow you to flip the full dataset at once. You can flip only one column at once. So, if you want to flip the full dataset then you have to apply formula columns one by one. Follow the steps below to do this:
📌 Steps:
First, create a new column outside the dataset.
- Then, paste this formula into the first cell of the new column “Formula Output”
=INDEX($E$5:$E$20,ROWS(E5:$E$20))Formula Breakdown:
Syntax of INDEX function: =INDEX (array, row_num, [column_num])
- Array=$E$5:$E$20 : This is the range of array from where the INDEX function extracts output.
- Row_num = ROWS(E5:$E$20) = 16: The ROW function will give the count of rows in the selected date range E5:E20 which is 16.
- INDEX($E$5:$E$20,ROWS(E5:$E$20)) = 3.95: The INDEX function will return the 16th cell of the data range which is 3.95.
- Now, drag the Fill Handle icon to paste the used formula respectively to the other cells of the column or use Excel keyboard shortcuts Ctrl+C and Ctrl+V to copy and paste.
- In the formula, you may see that I haven’t used the absolute reference for the start cell of the range of the ROWS function. So it will change through the cell reference.
- As a result, you will have the Formula output column filled with the flipped data of the Rating column from bottom to top.
- Then, copy the whole column “Formula Output” using Ctrl+C.
- Then, you have to paste the value only into the Rating column. For this, right-click on the first cell of the Rating column and select the Paste Value option under the Paste menu.
- As a result, the Rating column data will be flipped from bottom to top.
- If you want to flip data of other columns in the dataset then, you have to do the same thing for other columns individually.
Read More: How to Flip Data in Excel Chart (5 Easy Methods)
4. Flip Data from Bottom to Top Using VBA Code
You can also use a VBA code to flip the dataset from bottom to top. Follow the steps below to insert and run the code for this.
📌 Steps:
- For this, first, go to the top ribbon and press on the Developer then press on the Visual Basic option from the menu.
You can use ALT + F11 to open the ‘Microsoft Visual Basic for Applications’ window if you don’t have the Developer tab added.
- Now, a window named “Microsoft Visual Basic for Applications” will appear. Here from the top menu bar, press on the “Insert” And a menu will appear. From them, select the “Module’” option.
- Now, a new “Module” window will appear. And Paste this VBA code into the box.
Sub FlipData()
Dim Ran As Range
Dim Work_Range As Range
Dim x As Variant
Dim p As Integer, q As Integer, r As Integer
On Error Resume Next
xTitleId = "Flip Columns Bottom to Top"
Set Work_Range = Application.Selection
Set Work_Range = Application.InputBox("Range", xTitleId, Work_Range.Address, Type:=8)
x = Work_Range.Formula
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For q = 1 To UBound(x, 2)
r = UBound(x, 1)
For p = 1 To UBound(x, 1) / 2
xTemp = x(p, q)
x(p, q) = x(r, q)
x(r, q) = xTemp
r = r - 1
Next
Next
Work_Range.Formula = x
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
- To run the code go to the top menu, press on the Run option, and here will open some other options and select the Run Sub/UserForm also you can simply press F5 to run the code.
- After running the macro code, there will appear a pop-up window,
- Select the cell range of the dataset in the Range box and press OK.
- As a result, the full dataset will be flipped from bottom to top.
Read More: How to Flip Axis in Excel (4 Easy Methods)
Things to Remember
- Using the formula method, you can flip data of a column only at once.
- Don’t forget to change the cell reference in the formula method, while using your dataset.
Conclusion
In this article, you have found how to flip data in Excel from bottom to top. I hope you found this article helpful. You can visit our website ExcelDemy to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.
