To make complex calculations much easier there are functions within Excel that allow you to perform calculations of specific values in a particular order. So the predefined formulas will save you time writing them on your own.
Then, the Excel spreadsheet is optimized to facilitate the operations that are executed on it, therefore; Excel functions are grouped by categories to use as needed. Among the most outstanding functions are the Financial, Date and Time, mathematics and trigonometrics, statistics, search and reference, database, text, logic and information.
Index( )
- What is the best option you should use to sort and filter data?
- How to apply one of these functions in Excel?
- These are examples of the SORT and SORT BY functions to help you understand them.
- SORT example in Excel
- SORT BY function example
- How to fix any error conditions the formula might have?
What is the best option you should use to sort and filter data?
Excel presents multiple options so that you can sort and filter data , in order to eliminate noise and find what you are looking for more easily. Here are the most used methods.
To sort, you should start by going to the spreadsheet and within it, click on the cell at the top of the column that you want to sort. Now, select the tab “Data” from the upper Excel toolbar, there also look for “Filter” (The funnel icon).
Next, an arrow should appear at the top of each column, so click on the arrow for the column you want to sort to display a drop-down menu that allows you to sort or filter the data. You can choose to sort the data from highest to lowest (numeric data), sort from oldest to newest, sort alphabetically , etc.
To filter, you must select the arrow of the column to filter, and select the option “Numeric filters” then “Less than”, or you can filter by font color, cell, or icons.
How to apply one of these functions in Excel?
Excel is a very complete application that has more than 400 functions to use in any type of calculation. With the command «insert function» you can find the desired function that is useful for the complex calculation you need.
To insert functions, click where you want to insert the formula or function, then select the button «Insert function», search for the function you want by means of a keyword or directly in its list category, now select the function you want and do click OK, and finally, in the “arguments dialog” specify the range of data or parameters and click OK. The result of the formula is shown below.
These are examples of the SORT and SORT BY functions to help you understand them.
With the function Sort or «ORDER» it will allow you to order the content of an array or range ; while with the function SortBy or “SORTBY” you will sort the content of a range or an array based on the data of a corresponding array or range.
SORT example in Excel
SORT (A1: B10; 1; 1), means that the argument A1: B10 is the range of data to be ordered , the following argument denoted with the “1” indicates the number of columns where you want it to start sorting, and the last argument is “1” indicates the way in which you want to sort ascending (1), descending (0).
SORT BY function example
SORT BY (A1: D10; B2: B20; 1; C3: C30; 0), it means that the first argument A1: D10 is the data range you want to sort, and B2: B20; 1 is the data range of the column you want to sort in the first instance along the way that is ascending (1), and finally C3: C30; 0 is the data range of the column to sort in the second instance and the way in descending (0).
How to fix any error conditions the formula might have?
Errors such as # DIV / 0 !, # NAME ?, # NULL!, # REF !, # NUM !, # VALUE! and # N / A can appear in the realization of some formula or function in Excel, but for this we have the solution.
We will start with the first; The # DIV / 0! It means that the formula is trying to divide by 0 and it is a wrong mathematical operation, and in most cases the cause of this is a blank cell that is supposed to have the value of 0; and to solve it you must modify the formula so that it does not divide by 0 or eliminate all the empty rows or columns.
The error # NAME ?, appears when you incorrectly enter the name of a formula, so Excel does not recognize it and is not able to produce a result, so that error will be displayed; it can also be shown when you enter an incorrect range name, do not use quotes or periods in the arguments, syntax errors . To solve this error you just have to enter the texts correctly or activate the “Autocomplete formula” tool.
The error #NULL !, usually appears when you use the intersection operator with two ranges that do not have cells in common; in this way you only have to place the correct ranges. On the other hand, the #REF! Error indicates that the formula you just entered contains an incorrect cell reference that may have been deleted, cut off, or has no arguments; To solve it, you just have to try to recover the deleted cell or check the references of the formulas.
Now you will find the #NUM! Error, which informs you that there is a problem with a number in the formula, or you have entered an incorrect argument; and to solve it you must check the formulas and verify the arguments. The #VALUE! Error indicates that the argument numbers present a problem that should be due to an incorrect data type that was entered, a range of cells that only supports a single cell; so you must correct any of those possible problems.
Finally, the # N / A error refers to the fact that the formula is not capable of giving a correct result due to an error within it; To solve it you will have to check the cells of the formula to see if any of it shows that error and correct it.
