2 Interesting Functions For Excel Table Sorting

Published on 1 Apr, 2019 by Milos Gregor

One of the most important spreadsheet functions is to sort table values by selected columns. This post shows you how to sort Excel data within the Reshape.XL add-in using two interesting functions.

If you have data loaded in the Reshape.XL add-in, you can sort it as needed during the data processing. For this purpose you can use two functions - Arrange and Arrange_All. An example of their application is shown in the following figures.

Both function have a very similar character but different results. In the case of Arrange function, you can sort the data in the table in ascending or descending order by one or more selected table columns. This feature is similar to the ORDER BY function in SQL. Its use is very simple and intuitive. When applying, it is necessary to pay attention to the variable selection order.

Arrange_All has a slightly different meaning. Its significance is evident in the “visual” exploration of the dataset or in the analysis of extreme values of several variables at once. Imagine you have a large dataset of chemical analysis. The individual chemical analyzes are stored in rows and the measured parameters (e.g. calcium, magnesium, nitrate or chloride content). When you get this dataset you are interested in exploring general information such as averages, extremes or the frequency of analyzes by selected categorical variables.

The Arrange_All function is useful for evaluating extreme values of measured parameters. When applied to a dataset, all columns are sorted in ascending or descending order, individually - independent or each other. Although you break the record structure, you can immediately see the highest or lowest measured variables in the table at once. You can then select the top 10 values and work on them. In the case of categorical variables, you can simply by scrolling to determine the frequency of measurements in individual categories.

Using the two described functions is very fast and efficient. The following short video-tutorial will familiarize you with the functions for efficient and easy Excel spreadsheets data sorting.