Today, we want to learn how the SORT function works in Excel.
Excel SORT Function
The SORT function is available from Dynamic Array Excel, which is around 2021. Let’s start with the simplest version of the SORT function, where we only need to write an array. We have to remember that the array must be given without headers. Let’s select our data and put it as an argument and let’s put our formula into a cell (Fig. 1)
=SORT(A2:C169)
Fig. 1 SORT function
And we have the results. The most important thing about formulas in Dynamic Array Excel is that they spill. It means that our formula is in only one cell, but the results spill. The formula is only in cell E2, but when we click on cell E3, we can see that the formula is grayed out in the formula bar. It means that it contains results of this function, but doesn’t contain the function itself (Fig. 2)
Fig. 2 Formula grayed out
When we add an array with data to be sorted, Excel’s sorting will be based on the first column, which is the column with dates. In the sorted array we don’t see dates but numbers because Excel doesn’t know how to copy the formatting in array formulas. If we want to have proper formatting, we can copy the formatting using the Format Painter (2) from the Home tab (1). We will also highlight the cell with the formula (3) (Fig. 3)
Fig. 3 Using the Format Painter command
Now, our results are formatted. Since we sorted by the first column, our results are basically the same as in our original column. To change that, we can add the second argument to our SORT function, which is the sort index. It’s the number of a column by which we want to sort our data. Let’s write 2, meaning the second column (Fig. 4)
=SORT(A2:C169,2)
Fig. 4 SORT formula with the second argument
We can see that the sorting has changed. Now, we can see that only Chandlers are at the top because it’s an ascending sorting. This way we can sort by any column. Let’s move on to the third argument, which is the sort order. Let’s change it to a descending sorting by writing ‑1 (Fig. 5)
=SORT(A2:C169,2,-1)
Fig. 5 Descending sorting
Now, the first salesman in Ross. Let’s add something more. Let’s sort by Sales from the largest to the smallest. Now, the sales are written randomly in the Sales column. We can add the third column to the second argument. We can also add a reference to cells where numbers of columns are written. It’s very important to write them in the correct order. In our case the first cell from the top contains 2 and the second one contains 3. It means that first, we will sort by the second column, and then by the third column (Fig. 6)
Fig. 6 Adding a cell reference
And we have the results. We can see that Ross is sorted by values in the Sales column. The same is with the rest of the salesmen. Now, let’s focus on the third argument once again. We have two columns of sorting in the second argument, but only one number (-1) in the third argument which is the sort order. It means that the sorting is descending for each column. When we want to have an ascending order for one column and a descending order for another column, we can do the same as we did with the second argument. We have to refer to some cells in Excel. In our case we have a sorting order for each column (Fig. 7)
Fig. 7 Referring to other cells
Now, we have an ascending sorting for the Salesman column, and a descending sorting for the Sales column.
When we don’t want any additional cells in our sheet, we can hard code them in the formula by pressing F9 key. It changes the argument into an array. We can do the same with the sort order argument. When we look at the formula now, we can see that the second column in ascending, and the third column is descending (Fig. 8)
Fig. 8 Hard coding the values
Now that we have everything we needed, we can cancel the unnecessary cells. We don’t even need the third argument, as it is reserved for horizontal sorting. Here, we are sorting only by columns, so we can leave it like that. Here are our results (Fig. 9)
We are going to use Classic Excel, Legacy Excel and a classic formula with the AGGREGATE function. It means that this formula will work from 2010 and thanks to this, we won’t need to use the Crtl + Shift + Enter key combination.
Let’s start with finding elements. We will be checking whether elements from My son’s favorite movies list are on My daughter’s favorite movies list. We can use the MATCH function here. We are looking up elements from my son’s list (list B) on my daughter’s list (list A). We’re clicking F4 key to lock both lists. Then we write zero to have the exact match and that’s it (Fig. 1)
=MATCH($C$2:$C$9,$A$2:$A$11,0)
Fig. 1 MATCH function
Excel has just given us the results. I have the Dynamic Array Excel, which means that Excel spilled the results. We can see that How to train a Dragon is in list B with number 5 which means that it’s on position number 5 in list A. We also have Spider-man on position number 7. If we want to extract those numbers, we have to use the AGGREGATE function. In the first argument of the function, we have to use a function number 15, as this function understands an array formula. Then, in the second argument, we have to write 6 to ignore errors. At the end, in the k argument let’s write ROWS from E2 to E2 and press F4 key to lock only the first one. This will allow the formula to expand while copying down (Fig. 2)
Now, we have the results, however there are some errors. To remove that, we can add the INDEX function and write list A in the first argument, then press F4 key to lock it (Fig. 3)
We have our results in the target list, and there is still some backup space in case we have some more results (Fig. 5)
Fig. 5 Results with extra space
Now, we can create a formula that will look up elements only from list B. Let’s start with giving each element a number. We’ll use the ROW function here and write the whole list as the first argument. Then F4 key to lock it (Fig. 6)
=ROW($C$2:$C$9)
Fig. 6 ROW function
As we can see, Excel has spilled the results and each element has got its own number. However, we want to start from 1, not from 2 (Fig. 7)
Fig. 7 Numbers for each element
To do this, we have to subtract the header row and press F4 key to lock it (Fig. 8)
=ROW($C$2:$C$9)-ROW($C$1)
Fig. 8 Subtracting the header row
And we have all elements numbered from 1 to 8. Now, we want only numbers of elements that aren’t on list A. How can we do it? We can divide our list by the MATCH function. In the first argument, we write list B and press F4 key to lock it, then list A in the second argument, as that’s the place where we will be looking up our values. Then F4 key. As the final step, we write 0, as we want to have the exact match and the looking up process will start from the top (Fig. 9)
Just like that we have numbers only on those positions that are on both lists. However, we want a reverse situation, where we will have numbers in #N/A positions. To to that we have to put our MATCH function into the ISNA function (Fig. 10)
If the MATCH function returns an error, the ISNA function will change this error into TRUE. And a number divided by the value of TRUE will change the TRUE value into 1. If the ISNA function returns FALSE, the mathematical operation will change FALSE into 0. Since we cannot divide by 0, we will have #DIV/0! errors (Fig. 11)
Fig. 11 #DIV/0! errors
Since we have a list with numbers which are the positions of elements we want to extract and errors, it’s time to put our whole formula into the AGGREGATE function. Just like before, we start from the smallest value, so let’s write 15, then 6, as we want to ignore errors. At the end of our function, we write the ROWS function with G2 cell. Then F4 key to lock the first one (Fig. 12)
Now, we have positions without errors, which means that we can add the INDEX function to our formula. As we take positions from list B, we have to select the list, then press F4 key to lock it (Fig. 13)
Fig. 13 INDEX function
After pressing Enter, or Ctrl + Enter, we have our results. Now, we can add the IFERROR function to put empty text strings in the places where the formula returns errors (Fig. 14)
This way, we have two formulas. The first one to extract elements that are on both lists, and the second one to extract elements that are only on one list.
Today, we want to compare two lists. Being more precise, we want to find elements that are on both lists, as well as elements that are only on one list.
Compare 2 lists dynamic array formula
This time, we will use Dynamic Array formulas. It makes the operation easier than using Legacy Excel formulas.
We’re starting with the XMATCH function. In the first two arguments, we write a list of elements that we are looking for, and the list that those elements are looked up in (Fig. 1)
=XMATCH(C2:C9,A2:A11)
Fig. 1 XMATCH function
In the results, we can see that we have #N/A errors in the places where Excel didn’t find any match, and numbers where it found the proper match (Fig. 2)
Fig. 2 Errors and matches
Now, we want to extract the values where Excel has found the match. To to this, we can add the ISNUMBER function to the XMATCH function (Fig. 3)
=ISNUMBER(XMATCH(C2:C9,A2:A11))
Fig. 3 ISNUMBER function added to XMATCH function
Now, our results are FALSEs and TRUEs. What we can do now, is add the FILTER function. In the first argument, we have to write the array with elements that we are looking for (Fig. 4)
=FILTER(C2:C9, ISNUMBER(XMATCH(C2:C9,A2:A11))
Fig. 4 FILTER function
And we have our results. Now, we want to find elements that are only on the My son’s list. All we need to do, is take the same formula and change the ISNUMBER function into ISNA function (Fig. 5)
=FILTER(C2:C9, ISNA(XMATCH(C2:C9,A2:A11))
Fig. 5 ISNUMBER function into ISNA function
And just like that we have found elements that are on both lists and elements that are only on one list (Fig. 6)
Today, we want to calculate the age basing on the date of birth.
Calculating the age based on date of birth
It’s a really simple task. We just want to know one hidden function. It’s called DATEDIF. Excel won’t show its syntax, but it will calculate it properly, if we give it proper arguments. The first argument of this function is the start date, which is our date of birth. The second argument is the TODAY function. The last argument is ‘Y’, as we want to calculate whole years (Fig. 1)
=datedif(A2,TODAY(),“Y”)
Fig. 1 Whole formula
And just like that, we have calculated the age basing on the date of birth (Fig. 2)
Today, we want to learn how to add a Target Line to our chart and control it.
How to Add a Target Line to an Excel chart
Sometimes, we want to lower the line, sometimes we want to raise it. But, firs things first.
Let’s start with writing the target. Let’s write 700. Then, we have to create a simple formula without any dollars in the first cell below (Fig. 1)
=C2
Fig. 1 A simple formula
Now, we can copy the formula down. And what’s happened? Each cell is referring to the cell above it. At the end, they are going to the first cell (Fig. 2)
Fig. 2 Each cell refers to the cell above
It means that when we change the value in the first cell, the values in the cells below also change (Fig. 3)
Fig. 3 Values changed
Now, when our data is properly prepared, we can select the whole data, go to the Insert tab (1), then go to Charts and choose the column chart option (2) (Fig. 4)
Fig. 4 Creating a chart
We have a chart but no line. To create one, we have to select the Target series, go to the Insert tab (1), and choose a line chart from the Charts area (2) (Fig. 5)
Fig. 5 Creating a line
When we have the line, we can right-click it and choose the Change Series Chart Type option from the pop-up menu (Fig. 6)
Fig. 6 Changing the chart type
In the window that has appeared, we can change the chart type to any we like (Fig. 7)
Fig. 7 Chart type options
However, we have already chosen the type we wanted. Let’s change the value in the first cell in the Target column. What we can see is that the line has changed its position (Fig. 8)
Fig. 8 Value change
We can also calculate the average of our data using the AVERAGE function (Fig. 9)
=AVERAGE(B2:B13)
Fig. 9 Calculating the average
Now, we have an average line for our data (Fig. 10)
Today, we want to learn how to create a chart on which we have unique targets for each month, group, shop, etc.
Target Chart with Unique targets
The most important task for us is to create proper data. When we do it in the right way, the rest will be much simpler. Let’s start with creating a Base series, which will be the foundation of our chart. We have to check if the income is larger than the target. It means that we have to make a logical test with the IF function. If it’s correct, we want the smaller value, which is the target value. In the other case, we want the income (Fig. 1)
=IF(B2>C2,C2,B2)
Fig. 1 IF function for the Base series
And just like that, we have our foundation. Now, we can move on to the next step. If the income is larger than the target, we want to know how much larger it is. We will create it in the Upper column. We start with the IF function again, and we are checking if the income is larger than the target. If so, we want to deduct the target from the income. In the opposite case, we want nothing, i.e. we want our chart to show nothing. It means that we cannot write just an empty text string in the formula in two double quotes. We have to return an error. The simplest way I know is using the NA function (Fig. 2)
Fig. 2 IF function for the Upper series
And just like that, we have our Upper series. The last thing to do is the Lower series. It is very similar to the previous formula, All we need to change is the ‘larger than’ sign into ‘smaller than’ sign, and deduct the income from the target (Fig. 3)
Fig. 3 IF function for the Lower series
Just like that, we have the second series.
Now, when we have a value in one of the Upper cells, we will have an error in the same row in the Lower cell, and vice versa. When our data looks OK, we can move on to inserting a chart. We have to select the Months column, as well and the Base, Upper and Lower series. Then, we go to the Insert tab (1), then to the bar chart, where we select the Stacked Column option (2) (Fig. 4)
Fig. 4 Creating a stacked column
Now, we have all we need. We have our Base. When our income is larger than the target, we have a blue part, and when we didn’t reach the target, we have green. Let’s change the colors, so that the differences are more visible. I also don’t like the horizontal lines, so let’s delete them by selecting them and clicking the Delete key. I will also widen the columns by selecting them and pressing Ctrl + 1. In the Format Data Series window on the right we can change the Gap Width to 70% (Fig. 5)
Fig. 5 Gap Width option
Now, I want to add our target. Since we have only column charts here, we can copy our target column, select the chart and press Ctrl + V. Now, we have our target, however, we don’t want to have another column. We can change it into a line chart with marks. We can do it by right-clicking on the target series in the chart. In the pop-up menu, we have a Change Series Chart Type option (Fig. 6)
Fig. 6 Change Series Chart Type option
In the Change Chart Type window, we can change our target by clicking on proper options (Fig. 7)
Fig. 7 Line with Markers option
However, there is one more option. We can go to the Insert tab, and when the series is selected, we choose a proper chart. The result is the same (Fig. 8)
Fig. 8 Line with Markers option
Now, we have to select our line with markers, then press Ctrl + 1 to open the Format Data Series window. When our line chart is selected, we have to go to the bucket part, and select the No line option in the Line part (Fig. 9)
Fig. 9 No line option
In the Marker part, we can use some built-in markers, like a line. However, when I’m using the line, it’s too thick for me, especially in a bigger size (Fig. 10)
Fig. 10 Bigger size
In such a case, we can do another trick. Let’s go to the Insert tab (1), then the Shape part (2), and select a simple Line (3) (Fig. 11)
Fig. 11 Line option
Now, while holding the Shift key, we can draw a line with our mouse (1), and modify it (2) (Fig. 12)
Fig. 12 Line modification
Now, we can copy the line, then select markers and paste it. I can see that my line isn’t long enough, so i’ll just make it longer, then paste it once again (Fig. 13)
Fig. 13 Longer line
Now, let’s insert data labels. In the Chart Elements window we have to select the Data Labels option. Excel,then will give me data labels for each series (Fig. 14)
Fig. 14 Chart elements window
However, I don’t want data labels for the target series, so I have to select it and just delete it. Now, It’s important that we have data labels for our upper part only when there is an upper part. In places with errors, there won’t be data labels. The same is with lower parts. Errors won’t show anything on the chart. Let’s change lower data label color by going to the Home tab and selecting white (Fig. 15)
Fig. 15 Color change
Let’s add the chart title. Let it be the contend of cell B1. We write = in the formula bar, then click the cell and press Enter (Fig. 16)
=‘E061’!$B$1
Fig. 16 Chart title
There is one more thing. I sometimes don’t want to show the target series in the legend. To remove it, we have to click on the legend, then once more on the target series in the legend, then press Delete (Fig. 17)
Fig. 17 Final chart
Now, I’m happy with my chart with unique target for months, groups, shops, etc.
Today, we want to learn how to insert a bar or a column chart in Excel.
Creating a Column Chart
For a start, we can select a cell in our dataset. We can also select the whole set. Then, we go to the Insert tab and choose a proper chart. I personally like 2 D columns, so I’ll select it (Fig. 1)
Fig. 1 Selecting a 2 D column chart
And just like that, we have our chart. We can modify its size by dragging the borders. When you drag the chart holding the Alt key at the same time, Excel will match the size of the chart to cell borders, which is a nice trick.
Now, that we have our chart, let’s change the way it looks. Let’s go to the Chart Design tab and choose one from the Chart Styles (Fig. 2)
Fig. 2 Selecting a proper chart style
We can also decide what individual chart elements we want to change. If we want to delete horizontal lines, we can just click on them once, and press the Delete button. If we want to have wider columns, we have to click on them, then press Ctrl + 1, which will open the Format Data Series window, where we can change the Gap Width. Let’s change it to 55 %. Now it looks good (Fig. 3)
Fig. 3 Gap Width to 55%
I will also add data labels. Let’s go to the Chart Elements (1) , and choose Inside End Data Labels (2) (Fig. 4)
Fig. 4 Inside End Data Labels
Now, let’s change the font color into white. You can find this option in the Home tab (1) , in the Font area (2) (Fig. 5)
Fig. 5 Font color
And just like that we’ve created a column chart. When we change some values in our dataset, our values in the chart will also change (Fig. 6)
Today, we want to learn how to create a step chart in Excel.
Step chart
This task is really simple. All we have to know is one, simple trick. Our dataset has got two columns. One with dates and one with numbers. Let’s copy the set so that we can leave the original one as it is and paste it next to it. Now, let’s copy the original set once more, but this time without headers and paste it just under the new one. Now, we have to delete two cells using the Shift cells up option. Let’s right-click on the first cell in the first column and choose the Delete option. It will take us to the Delete window, where we have to select the Shift cells up radio button (Fig. 1)
Fig. 1 Shift cells up option
Let’s do the same with cell E10, but this time using the Crtl + - shortcut, which will take us straight to the Delete window (Fig. 2)
Fig. 2 Delete window
After removing those two cells, we have our dataset prepared for the step chart. Now, let’s select one cell in our dataset with dates, then go to the Insert tab (1), then select the Line Chart with Markers option (3) (Fig. 3)
Fig. 3 Line with Markers option
I chose a line with markers to show that almost each date has got two points. It means that there is a straight horizontal or vertical line from one day to another.
From this point on, we can modify our chart if we need to, however our basic step chart is ready (Fig. 4)
Today, we want to learn how to show a hundredth of a second or a milliseconds in Excel.
Milliseconds and Hundredths of a second
Let’s start by writing our time in a cell. Let’s assume that we want 0 minutes, 12 seconds and 45 milliseconds. Let’s enter it in our cell. We can see that we have two places for minutes and only one digit after it. It’s not a hundredth of a second, but it’s only a starting point. Since we’re working with time, a simple Increase Decimal option wont’ work here, as they’re not real numbers. Let’s press Ctrl + 1. It will take us to the Format Cells window. We are in the Number tab, Custom Category, where the custom format is mm.ss.0. We can see that the decimal part is only one 0 (Fig. 1)
Fig. 1 Time format
Let’s add two more zeros. Three zeros is the maximum number of decimal places for time (Fig. 2)
Fig. 2 Three zeros
Now, we have more precise time (Fig. 3)
Fig. 3 Precise time
We can do the same with other cells. We can even add hours (Fig. 4)
Fig. 4 Adding hours
However, in our case we don’t need it. Let’s test our setting and write 00:45.670 in the next cell, and 00:00.001 in another one. We can see that Excel is showing the time precisely. Now, let’s add it all up and check if the result is also precise (Fig. 5)
=SUM(C2:C5)
Fig. 5 Summing up
Here we go, the time is precise also in this case.
Today, we want to learn how to add a trendline to a chart.
How to add a trendline to a chart
It’s a really simple task. We just have to select our chart, then press the green plus sign (Chart Elements) and go to the Trendline option. We can see that there are many types of trendlines, starting from Linear. Let’s use it (Fig. 1)
Fig. 1 Linear option
We can see that the trendline is of the same color as our columns, which we don’t want. Let’s click on our trendline, then press Ctrl + 1. In the Format Tredline window, we can see that we can modify it in many ways. Let’s click on the bucket icon, and change its color (Fig. 2
Fig. 2 Color modification
Now, I can see my trendline on the columns. When we select our trendline once more and press Ctrl + 1 again, we can even add an equation and some statistical information (Fig. 3)
Fig. 3 Statistical information
If we don’t want it, we can press Delete. It’s important that the trendline doesn’t work with all chart types, only the most common ones, like a column chart or a line chart (Fig. 4)
