If you need the original formatting on the sorted data as well, you can either apply it manually or copy and paste the formatting from the original data set to the new sorted dataset Flip Data Using VBA Note: When you use a formula to reverse the order of a data set in Excel, it would not retain the original formatting.
Suppose you have a data set as shown below and you want to reverse the order of the entire table: however, you will have to specify a second argument that would specify the column number from which the data needs to be fetched. You can use the same formula even if you have multiple columns in the data set. Since I have locked the second part of the reference in the ROWS function, in the first cell, it would return the number of rows between A2 and A12, which would be 11.īut when it goes down the rows, the first reference would change to A3, and then A4, and so on, while the second reference would remain as is because I have locked it and made it absolute.Īs we go down the rows, the result of the ROWS function would decrease by 1, from 11 to 10 to 9, and so on.Īnd since the INDEX function returns us the value based on the number in the second argument, this would ultimately give us the data in the reverse order. The real magic happens in the second argument where I have used the ROWS function.
The above formula uses the INDEX function that would return the value from the cell based on the number specified in the second argument.
0 kommentar(er)
