How to Flip Data in Excel from Bottom to Top (4 Quick Methods)

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.

How to Flip Data in Excel from Bottom to Top


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.

How to Flip Data in Excel from Bottom to Top

  • 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.

How to Flip Data in Excel from Bottom to Top

  • Now, you have the helper column filled with serial numbers. So, you can now sort them from bottom to top using the Sort feature.

How to Flip Data in Excel from Bottom to Top

  • 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

How to Flip Data in Excel from Bottom to Top

  • 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.

How to Flip Data in Excel from Bottom to Top

  • As a result, the whole dataset will be reversed from bottom to top.

How to Flip Data in Excel 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

in a 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.

in a Table

  • 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.

in a Table

  • As a result, the whole dataset is flipped from bottom to top.

in a Table

Read More: How to Flip Rows in Excel (4 Simple Methods)


Similar Readings


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.

Using Formula

  • 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.

Revert Data of a Column Using Formula

  • Then, copy the whole column “Formula Output” using Ctrl+C.

Revert Data of a Column Using Formula

  • 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.

Revert Data of a Column Using Formula

  • As a result, the Rating column data will be flipped from bottom to top.

Revert Data of a Column Using Formula

  • If you want to flip data of other columns in the dataset then, you have to do the same thing for other columns individually.

Revert Data of a Column Using Formula

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.

Using VBA Code

  • 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.

Using VBA Code

  • 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

Using VBA Code

  • 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.

Using VBA 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.

Using VBA Code

  • As a result, the full dataset will be flipped from bottom to top.

Using VBA Code

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.


Related Articles

Osman Goni Ridwan

Osman Goni Ridwan

I am Ridwan, graduated from Naval Architecture and Marine Engineering Dept, BUET, currently residing in Dhaka, Bangladesh. And my passion is to grow up my skillsets with industry demands. My prime goal is to be a data analyst as I do love to solve problems and play with data.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo