With the help of Excel Sorting Data Tips, we are looking for solutions for very interesting problems today. For example, how to sort dynamically a list containing two columns (‘Product’ and ‘Sales’ columns) at will only using excel functions?
Excel Sorting Data Tips – Hyperlinks
We will show you a solution based on the fly sorting technique on the second worksheet. It can help us create an excel dashboard or other visualization tasks, especially when available space is scarce.
This solution will show how a neat excel trick can help us create a dynamic order. In the figure below, we supplemented the columns waiting to be set with a scroll-down strip.
In our case, ‘on-the-fly sorting’ means that we bring the mouse towards the desired to be set column and the elements of the given column become organized. Do you like it?
Let’s say a few words about the tables and names: the usage of the ‘Sort order’ field is the following: if we set ‘1’ in the cell, then the order will be according to the ‘Product’ field, if we put ‘2’ in the cell then the order will be according to the ‘Sales’ area.
This sounds pretty easy at first glance, but creating these rank algorithms requires adequate attention. As a bit of help, we placed a drop-down list for choosing values.
Again, we will show you a little animation of how the order works, which will become easily understandable.
Sort Data using Functions
Let’s also talk about the functions! In work, we have used the following functions: The MATCH function searches for a given element in a range after giving us the relative position of that element. So, for example, we determined the values of the ‘REAL SORT’ field by this method.
The RANK () function creates the ‘Needed Order’ values. Its advantage gives us the calculation of what position is a given number in a sequence.
The elements of the ‘Num for sort’ column we got by using together with the CHOOSE() and ROW() functions. Download the help at the end of the article!
Before doing that, take a look at the dynamic lists in the next paragraph!
The dynamic list a lot of times can help to realize our ideas.
Sort Data using Dynamic Lists
Let’s imagine that out of a list containing twenty elements. We only need some – we only want to portray these as a part of an excel dashboard – for example, we only would like to list five products and their related values.
The condition is that we would like to choose these five products at will from the available list containing 20 rows. So, for example, we’d like to choose between 10 and 14 or between 2 and 6.
The most capable tool for this task is the OFFSET function. With its help, we can display values from a given list element. Therefore, it is worth knowing more about this function!
In short, we wanted to show you this. We hope you find it helpful. For more tips, visit our Youtube channel or visit our Facebook page! See you next week! For everything to be most straightforward, download this excel template sample also.