With the help of Excel Sorting Data Tips we looking for solutions for very interesting problems today. How to sort dynamically a list containing two columns (‘Product’ and ‘Sales’ columns) at will only using excel functions?
Excel Sorting Data Tips – Hyperlinks
On the second worksheet we will show you a solution based on the on the fly sorting technique. It is well capable of give us help in creating an excel dashboard or other visualization tasks especially when available space is scarce.
This solution will show you how a super excel trick can help us create a dynamic order. On 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 set ‘2’ in the cell then the order will be according to the ‘Sales’ field.
This sounds pretty easy at first glance, but creating these rank algorithms requires adequate attention. As a little help we placed a drop-down list for choosing values.
Again, we show you a little animation how does the order works and it will become easily understandable.
Excel Sorting Data Tips – Functions
Let’s also talk about the functions! In the course of the work we have used the following functions: The MATCH function searches for a given element in a range, after this gives us the relative position of that element. We determined the values of the ‘REAL SORT’ field by this method.
The values of the ‘Needed Order’ created by the RANK() function. Its advantage gives us the calculation what position is a given number in a sequence.
The elements of the ‘Num for sort’ column we got by using together the CHOOSE() and ROW() functions. Download the help at the end of the article but before you do 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.
Excel Sorting Data Tips – Dynamic Lists
Let’s imagine the situation 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 5 products and its relating values.
The condition is that we would like to choose these 5 products at will from the available list containing 20 rows. For example we’d like to choose between 10 and 14 or between 2 and 6.
Most capable tool for this task is the OFFSET function. With its help we can display values from a given element of the list. It is worth to know more about this function!
In short we wanted to show you this we hope you find it useful. For more tips visit our Youtube channel or visit our Facebook page! See you next week! For everything be most simple, download this excel template sample also.