How to insert Emojis

Today, we will learn how to put emo­jis into Excel. We will have a look at three solutions. 

How to insert Emo­jis in Excel

The first one is just copy­ing emo­jis from the inter­net into Excel in the edit mode and past­ing it. If the emo­ji we past­ed is too large for a cell, we have to change the font into small­er (Fig. 67)

Emoji's size modification
Fig. 1 Emo­ji’s size modification

The sec­ond solu­tion involves insert­ing sym­bols by using the Sym­bol icon (1) and choos­ing the emo­ji you want (2). Then press the Insert but­ton (3) and that’s it (Fig. 2)

Inserting emojis from symbols
Fig. 2 Insert­ing emo­jis from symbols

The third solu­tion is using the Touch key­board (Fig. 3 and Fig. 4)

Touch keyboard button location
Fig. 3 Touch key­board but­ton location
Exact location
Fig. 4 Exact location

You have to press the Emo­ji key, (Fig. 5)

Emoji key
Fig. 5 Emo­ji key

…and choose emo­jis you want (Fig. 6)

Chosen emojis
Fig. 6 Cho­sen emojis

When we have our emo­jis in Excel, we can use them in for­mu­las. Some­times such a for­mu­la will look dif­fer­ent­ly than in a cell (Fig. 7)

A formula with emojis
Fig. 7 A for­mu­la with emojis

One last thing. In Excel online, emo­jis are more col­or­ful 🙂 (Fig. 8)

Colored emojis
Fig. 8 Col­ored emojis

https://www.youtube.com/watch?v=gja6eVWxnx4

How to Split a column into rows | Excel Power Query 02

Do you want to split cells into sep­a­rate rows and repeat val­ues from oth­er columns? I will show you how.

How to Split col­umn into rows | Excel Pow­er Query 02

In our exam­ple, we have names of pub­lish­ers and series. In short, we want to go from this table (Fig. 1):

to this table (Fig. 2):

The task is real­ly sim­ple when using Pow­er Query. We need to go to the Data tab and choose the From Table/Range com­mand (Fig. 3). 

It allows us to import our table to Pow­er Query. In our exam­ple, we have two columns from which we want to select the Series col­umn and split it into rows. When this col­umn is select­ed, we need to go to the Home tab and find the Split Col­umn com­mand, then choose the By Delim­iter option (Fig. 4). 

Pow­er Query will choose a space as the delim­iter for us. How­ev­er, in our case the delim­iter is a semi­colon and a space. It means that we need to select the Cus­tom option and write the semi­colon and a space in the prop­er bar (Fig. 5). 

Then, we should select the Each occur­rence of the delim­iter radio but­ton. Now, let’s open Advanced options. We can see that columns are select­ed as the default option. Let’s change it into rows. Now, we can press OK (Fig.6) .

And we have the result. Now, we can go to the Home tab, then to the Close and Load com­mand and choose the Close and Load to option. This way, we can load impor­tant data from Pow­er Query to Excel (Fig. 7). 

In Excel, we are choos­ing the Table radio but­ton, then the Exist­ing work­sheet and then we have to select the tar­get cell, which in our case is D1. Let’s close it with OK (Fig 8).

As we can see, we went from the table on the left to the table on the right (Fig. 9). 

https://www.youtube.com/watch?v=OjeedTaBReg

Combine all values within group with delimiter | Excel Power Query 03

Today, we want to com­bine all val­ues from the same group into one cell. Let’s go.

Com­bine all val­ues with­in group with delim­iter | Excel Pow­er Query 03

In our exam­ple, we want to com­bine all pub­lish­ers. It means that we’re doing an oper­a­tion reverse to the one in the pre­vi­ous post. This time, we want to go from this table (Fig. 1):

 Source table
Fig. 1 Source table

to this table (Fig. 2):

Target table
Fig. 2 Tar­get table

We can do it with Pow­er Query. First, select the table. Then, go to the Data tab and select the From Table/Range com­mand (Fig. 3).

 Going to Power Query
Fig. 3 Going to Pow­er Query

We can see that we have our two columns. The first thing we want to do is group our pub­lish­ers. Let’s select the Pub­lish­er head­er, then go to the Home tab and then Group By com­mand. In the new win­dow, we can see that we are group­ing by the Pub­lish­er col­umn. We still need a new aggre­ga­tion. Let’s change the Oper­a­tion bar into All Rows and write ‘Series’ in the New col­umn name bar. We can­not change any­thing in the Col­umn bar. Let’s press OK (Fig. 4).

Group By window
Fig. 4 Group By window

Now, we have the Pub­lish­ers group, and in the cells from the Series col­umn, we have all rows from the pre­vi­ous stage. The whole table is in one, sin­gle cell (Fig. 5).

Whole table in one cell
Fig. 5 Whole table in one cell

How­ev­er, we don’t need the whole table, which con­sists of two columns. We only need the Series col­umn. We can change it by mod­i­fy­ing the code. Let’s go the View tab, and let’s check whether the For­mu­la bar is ticked. If it is so, we can start mod­i­fy­ing the code in the for­mu­la bar (Fig. 6).

Going to the Formula bar
Fig. 6 Going to the For­mu­la bar

Look­ing at the code, we can see that we don’t want to choose under­score for each step. It means that we need only one col­umn. That’s why we must write ‘Series’ in the code (1). Now, let’s delete the type from the code (2) and write ‘type as list’. It’s impor­tant to write ‘list’ with a low­er­case (Fig. 7). And we see that we have only lists when we click on any of the Series cells. 

 Code modification
Fig. 7 Code modification

Now, as Pow­er Query treats val­ues as a list, we can extract them by using the Extract Val­ues option (Fig. 8).

Extracting values
Fig. 8 Extract­ing values

We are extract­ing the val­ues by com­bin­ing them with a delim­iter. We want to use a com­ma and a space, so let’s select the Cus­tom option first and then write what we want (Fig. 9). Let’s close it with OK.

Delimiter
Fig. 9 Delimiter

Now, we can see that we com­bined all Series names into one cell for each pub­lish­er. We can now go to the Home tab, then Close & Load com­mand, and press the Close & Load to option (Fig. 10).

Loading data to Excel
Fig. 10 Load­ing data to Excel

Let’s select the Table and the Exist­ing Work­sheet radio but­tons in the Import Data win­dow (Fig. 11)

Data importing
Fig. 11 Data importing

We can see that all val­ues with­in one group are com­bined into one cell (Fig. 12)

Grouped values
Fig. 12 Grouped values

https://www.youtube.com/watch?v=GFHsSJXeEaA&

Combine all unique values within a group | Excel Power Query 04

Let’s assume that we are in the future and we sell space­ships. We want to find out what class of a space­ship each coun­try has bought bas­ing on our table of unique space­ship classes. 

Com­bine all unique val­ues with­in group | Excel Pow­er Query 04

With Pow­er Query, this task is very sim­ple. We have to select our main table, then go to the Data tab and choose the From Table/Range com­mand in order to export our data from Excel to Pow­er Query (Fig. 1).


Fig. 1 Exporting data to Power Query
Fig. 1 Export­ing data to Pow­er Query

Since we are inter­est­ed which coun­try has bought which space­ship, we want to group our data by the coun­try. We have to select the Coun­try col­umn and go to the Home tab (1) and choose the Group By com­mand (2). In the win­dow, we need to choose the Advanced option (3) because we want to add more aggre­ga­tion. I want to count how many ships each coun­try has bought as well as know the space­ship class names. Since there isn’t any prop­er oper­a­tion, let’s choose the Sum option (4), which we will mod­i­fy lat­er in the code. In the col­umn bar, we’re choos­ing the Ship class (5). As a final step, let’s write a new name, which is going to be ‘Unique ship class­es’ (6) (Fig. 2)

Data grouping
Fig. 2 Data grouping 

In the new table, there are some errors in the last col­umn, because we want­ed to sum up text, which is impos­si­ble. It means that we need to mod­i­fy our code. We need to go to the View (1) tab and tick the For­mu­la bar check­box (2). We can see that the for­mu­la uses the List.Sum func­tion (3) (Fig. 3)

Formula bar
Fig. 3 For­mu­la bar

What we want to do is com­bine all val­ues, so let’s change the List.Sum into the Text.Combine func­tion (1). We also need to add a sep­a­ra­tor, which will be a com­ma and a space (2) (Fig. 4)

Code modification
Fig. 4 Code modification

Now, we can see that Pow­er Query has changed its cal­cu­la­tions. All ship class­es are com­bined. How­ev­er, as I said before, we need only unique val­ues with­out repeats. We just need to add one func­tion, which is the List.Distinct func­tion. (Fig. 5)

List.Distinct function
Fig. 5 List.Distinct function

Now, we can see there are no repeats. We can go to the Home tab (1) and to the Close & Load com­mand (2) and the Close & Load to option (3) (Fig. 6)

Going back to Excel
Fig. 6 Going back to Excel

In Excel, we have to remem­ber to select the Table (1) and Exist­ing work­sheet (2) radio but­tons. Then choose a tar­get cell (Fig. 7)

Data importing
Fig. 7 Data importing

As we can see, we have count­ed how many ships each coun­try has bought as well as the prod­uct class (Fig. 8)

 The final table
Fig. 8 The final table

https://www.youtube.com/watch?v=A0fWOeDUXNA

List of file names from a folder | Excel Power Query 05

Today, we want to find all file names from a folder.

List of file names from a fold­er | Excel Pow­er Query 05

Let’s see our fold­er. We have dif­fer­ent kinds of files and even a sub­fold­er. There is also an emp­ty fold­er called Emp­ty, which must stay emp­ty. Our task is to find all files con­nect­ed to Excel, which means that we will need to do some fil­ter­ing (Fig. 1)

Folder content
Fig. 1 Fold­er content

We know the path, so we can copy it, then go to Excel, and then use Pow­er Query, which is the sim­plest solu­tion I know. We need to go the Data tab (1), then to the Get Data com­mand (2), then to the From File (3) and From Folder (4) options (Fig. 2)

From Folder option
Fig. 2 From Fold­er option

In the new win­dow, we can find our fold­er, for exam­ple, by the nav­i­ga­tion bar, where we can paste the whole path and press Open (Fig. 3) 

Navigation bar
Fig. 3 Nav­i­ga­tion bar

We can see that Pow­er Query has pre­pared a whole batch of infor­ma­tion from the giv­en fold­er, as well as from the Sub­fold­er for us. We don’t see the Emp­ty fold­er, because there aren’t any files. In our sit­u­a­tion, it’s fine for us. Then, we need to click on the Trans­form Data but­ton (Fig. 4)

 Information prepared by Power Query
Fig. 4 Infor­ma­tion pre­pared by Pow­er Query

The Trans­form Data option gives us the chance to add some fil­ter­ing to get the data we are inter­est­ed in. In the win­dow that appeared, we can see that we can fil­ter the infor­ma­tion by the Fold­er Path (1). If we are inter­est­ed only in the main fold­er, we can check only the main fold­er (2) (Fig. 5)

Filtering by the Folder Path
Fig. 5 Fil­ter­ing by the Fold­er Path

How­ev­er, we want all fold­ers, togeth­er with the Sub­fold­er, so let’s close this fil­ter­ing and go to the Exten­sion fil­ter­ing. Since we are inter­est­ed in Excel files, we are look­ing for the ones with an .xls exten­sion. Remem­ber that Pow­er Query is case sen­si­tive, so if we have some sus­pi­cions that there may be some upper case let­ters, we have to trans­form our Exten­sion col­umn. We need to go to the Trans­form tab, then to the for­mat com­mand and the low­er­case option (Fig. 6)

Lowercase option
Fig. 6 Low­er­case option

Now, we can fil­ter this col­umn by Text Fil­ters and the Begins with option (Fig. 7)

Begins with option
Fig. 7 Begins with option

We want the text to begin with .xls, which should get all exten­sions con­nect­ed with Excel (Fig. 8)

.xls extension
Fig. 8 .xls extension

We can see that we have our cho­sen exten­sions as well as the file names also with exten­sions. This is the infor­ma­tion that we need­ed, so let’s right click on the Name bar, and select the Remove Oth­er Columns option (Fig. 9)

Remove Other Column option
Fig. 9 Remove Oth­er Col­umn option

We are left only with one col­umn, where we have file names with exten­sions. Some­times, we don’t need any exten­sions. In those cas­es, we can add one more step to Pow­er Query. We have to select the col­umn, then go to the Trans­form tab, then to the Extract com­mand and the Text Before Delim­iter option (Fig. 10)

Text Before Delimiter option
Fig. 10 Text Before Delim­iter option

Our delim­iter is just a dot, so let’s write it (Fig. 11)

Delimiter
Fig. 11 Delimiter 

Now, we have only file names with­out exten­sions. How­ev­er, let’s say that I still want those exten­sions. In such a case I need to delete this step (Fig. 12)

Deleting the last filtering
Fig. 12 Delet­ing the last filtering

Now, I go to the Home tab, click on the Close & Load com­mand, then the Close & Load to option (Fig. 13)

Going back to Excel
Fig. 13 Going back to Excel

Let’s choose the tar­get cell and select the Table radio but­ton (Fig. 14)

Data importing
Fig. 14 Data importing

Here’s the final result (Fig. 15)

 Final table
Fig. 15 Final table

https://www.youtube.com/watch?v=Dey4PzGj46A

Advanced Filters and between condition

Today, we want to learn how advanced fil­ters work in Excel, espe­cial­ly between conditions. 

Advanced Fil­ters and between condition

Let’s start. When we have our dataset, we write our advanced fil­ter con­di­tions in cells. We need to have a head­er and a con­di­tion for this head­er. If we write con­di­tions in one row, all of them are con­nect­ed with the final cri­te­ria. It means that each con­di­tion should be met in order to take out the whole row from our dataset (1). When we add con­di­tions to fur­ther rows, it meas that it’s an ‘or’ con­di­tion (2). In this case, we will look for straw­ber­ries or plums. If we want to cre­ate a between con­di­tion, we need to write the head­er two times (3) in order to cre­ate a com­mon part. In our case, we are look­ing for straw­ber­ries that are equal or greater than 500 and small­er or equal to 750, and in the case of plums we want the plums that are equal or greater than 300 and small­er or equal to 500. We have to remem­ber that those con­di­tions should make a com­mon part (Fig. 1)

Dataset with two headers
Fig. 1 Dataset with two headers

When we want to go to advanced fil­ters, we need to select one cell in our dataset (1), then go to the Data tab (2), then Advanced fil­ters (3). In the Advanced fil­ter win­dow press the Copy to anoth­er loca­tion radio but­ton (4), and select the cri­te­ria range (5). The last step is select­ing the tar­get cell, which will be cell E6 (6) (Fig. 2)

 Creating a table
Fig. 2 Cre­at­ing a table

Remem­ber that if con­di­tions are writ­ten in one row, they are com­bined as an ‘and’ con­di­tion, and when they are in sep­a­rate rows, they are com­bined as an ‘or’ con­di­tion. More­over, we can add an emp­ty con­di­tion for dif­fer­ent head­ers. But, since there isn’t any con­di­tion in the Date col­umn, we can select the range with­out it. In fig­ure 3, we can see the final table with sales of plums that are between 300 and 500 and sales of straw­ber­ries that are between 500 and 750, togeth­er with all pos­si­ble dates for those con­di­tions (Fig. 3)

 Final table
Fig. 3 Final table

https://www.youtube.com/watch?v=PihjS8Bq-vI&t

Advanced Filters and wildcards

Today, we want to learn how wild­cards work with advanced filters. 

Advanced Fil­ters and wildcards

First of all, we have to remem­ber that an aster­isk replaces any text string, even an emp­ty one. A ques­tion mark replaces any sin­gle char­ac­ter. A tilde dis­ables a wildcard. 

Let’s start with find­ing ‘Red’ in the Prod­uct col­umn. To do that, we have to select one cell in our data, then go to the Data tab (1) and click on the Advanced fil­ter com­mand (2). In the Advanced fil­ter win­dow, we have to select the Copy to anoth­er loca­tion radio but­ton (3), then write the Cri­te­ria range (4). In the Copy to bar, we select our tar­get cell (5).

Advanced filter window
Fig. 1 Advanced fil­ter window

After press­ing OK, we have our results. In advanced fil­ters, we don’t have a sim­ple con­di­tion with text. This con­di­tion is not an equal con­di­tion, but a ‘starts with’ con­di­tion. In our tar­get col­umn, we have the ‘red’ word, how­ev­er, advanced fil­ters chose whole cells that start with ‘red’. If we want only the exact word, we have to write it with a sin­gle quote, then equals, then our word. Let’s write it. We should make anoth­er advanced fil­ter, so let’s make the same steps as in the pre­vi­ous exam­ple, start­ing with the Data tab and chang­ing the Cri­te­ria range to anoth­er col­umn (1) (Fig. 2)

Advanced filters with an exact word
Fig. 2 Advanced fil­ters with an exact word

In the tar­get cell, we can see only one cell with Drag­on­Fly, even though there are three cells with Drag­on­Fly in our data. 

Now, let’s try the aster­isk option. We write an aster­isk before ‘bee­tle’, but we have to remem­ber that it is always con­nect­ed with the ‘starts with’ option. We are work­ing with wild­cards as well as with the ‘starts with’ cri­te­ria. It works as if an aster­isk was also at the end of ‘bee­tle’. Let’s make a prop­er advanced fil­ter for this case star­ing with the Data tab and chang­ing the Cri­te­ria range into anoth­er col­umn (1) (Fig. 3)

 Advanced filters with an asterisk
Fig. 3 Advanced fil­ters with an asterisk

As we can see, ‘bee­tle’ can be at the end, at the begin­ning or even in the mid­dle of our cell.

Now, let’s work with ques­tion marks. We need to remem­ber that one ques­tion mark rep­re­sents only one sign. It can be a dig­it, a let­ter or any oth­er sign. For exam­ple ‘Ant ? — ? mm’ can­not rep­re­sent ‘Ant 6 — 10 mm’, because there are too many signs in 10. Let’s make an advanced fil­ter for that. We start with the Data tab, and we change the Cri­te­ria range (1) and the tar­get cell (2) (Fig. 4)

Advanced filters with an asterisk
Fig. 4 Advanced fil­ters with an asterisk

We can see that we found two cells where a ques­tion mark rep­re­sent­ed only one sign. 

Now, let’s move on to a tilde. We want to find the exact phrase of ‘*Ant’. That’s why we put a tilde at the begin­ning. If we put only an aster­isk and an Ant, like this ‘*Ant’, Excel would find any Ant, regard­less of its posi­tion. The tilde makes the aster­isk stop being a wild­card. We do the same as in pre­vi­ous cas­es, chang­ing only the Cri­te­ria range and the tar­get cell (Fig. 5)

 Advanced filters with a tilde
Fig. 5 Advanced fil­ters with a tilde

We can see that Excel found only one Ant with an aster­isk at the begin­ning. It’s because we turned off wildcards. 

https://www.youtube.com/watch?v=oRH3-S9v-8Q

SORT by months using functions

Today, we want to learn about sort­ing by months with the SORT function. 

SORT by months using functions

Let’s start with writ­ing the SORT func­tion, with an array as the first argu­ment. In the sec­ond argu­ment we need to write 2, as we want to sort by months, which are locat­ed in the sec­ond col­umn. Now, we can close the func­tion (Fig. 1). 

=SORT(A2:C145,2)

SORT function
Fig. 1 SORT function

Just like that the SORT func­tion sort­ed data by the Month col­umn. It’s impor­tant that the SORT func­tion does­n’t con­sid­er the cus­tom list. Excel does it, but the SORT func­tion does­n’t. Now. let’s have a look at how we can prop­er­ly sort by months using the SORT func­tion. First of all, we can­not use the SORT func­tion, but the SORTBY func­tion. In this func­tion, even if we change the sec­ond argu­ment to a month col­umn, we still get an ascend­ing sort­ing, from A to Z. It means that the sort­ing is based on the alpha­bet, not on months of the year. The thing is we can­not do the sort­ing based on argu­ments. We need to use the MATCH func­tion. It will look up a giv­en month in a list of months. In the third argu­ment we write 0, as we want to have the exact match (Fig. 2)

=MATCH(B2,$J$2:$J$13,0)

 MATCH function
Fig. 2 MATCH function

The MATCH func­tion starts the sort­ing from the top on the Month col­umn. As we can see in col­umn D, we have a prop­er num­ber for each month. The col­umn acts as a helper col­umn, how­ev­er, some­times we don’t want any helper col­umn, so we can cre­ate a helper col­umn in our for­mu­la. We can match each month from the Month col­umn (col­umn B) using the MATCH func­tion, where we need to write the lookup val­ue in the first argu­ment, which is our data col­umn. In the sec­ond argu­ment, we need to write the whole list of months on the right, then let’s write 0 as the exact match (Fig. 3)

SORTBY(A2:C145,MATCH(B2:B145,J2:J13,0))

SORTBY function
Fig. 3 SORTBY function

It works. We see that the SORTBY func­tion found a prop­er num­bers for each month. It’s impor­tant that it sorts by months, not by years. How­ev­er, we can add the year as well. But, I want to show you some­thing else. First of all, if we don’t like the cus­tom helper col­umn list, we can just click F9 in the lookup array argu­ment to hard code the array list in our for­mu­la (Fig. 4)

=SORTBY(A2,C145,MATCH(B2:B145,{“January”;“February”;“March”;“April”;“May”;“June”;“July”;“August”;“September”;“October”;“November”;“December”},0))

Hard coding month names
Fig. 4 Hard cod­ing month names

We can even delete the cus­tom col­umn. As we can see, it works prop­er­ly. When we look at col­umn D, we can see #N/A error. It’s because the MATCH func­tion is try­ing to find months in the cus­tom col­umn cells which are emp­ty (Fig. 5)

=MATCH(B2,$J$2:$J$13,0)

Empty cells in the Month column
Fig. 5 Emp­ty cells in the Month column 

Now, let’s focus on dates based on years and months. Some­times, our date in a dataset isn’t writ­ten only in one col­umn, but it can be sep­a­rat­ed into the year, month and day columns. We can cre­ate a full date from the data we have. We will try it in the Helper col­umn. First of all, we write =, then 1 com­bined with a month, com­bined with a year (Fig. 6)

=1&B2&A2

Creating a date
Fig. 6 Cre­at­ing a date

And just like that, we com­bined the 1st of Jan­u­ary 2022. Our date, how­ev­er, is text. We want Excel to under­stand it as a date, so we just need to add paren­the­ses and a 0 (Fig. 7)

=(1&B2&A2)+0

Changing text into numbers
Fig. 7 Chang­ing text into numbers

Excel mag­ic has just hap­pened! It changed text into num­bers. Now, to change the num­bers into dates, we need to go to the Home tab (1), then change the for­mat­ting (2) into Short Date (3) (Fig. 8)

Numbers into a date
Fig. 8 Num­bers into a date

Since we have dates based on the year and the month columns, we can sort them. Let’s write the SORTBY func­tion. In the first argu­ment we write the data col­umn, and in the sec­ond col­umn we have to com­bine 1 with the Month col­umn, then with the Year col­umn. When we close the paren­the­ses we need to add 0 to change text val­ues into num­bers (Fig. 9)

=SORTBY(A2:C145,(1&B2:B145&A2:A145)+0)

SORTBY function
Fig. 9 SORTBY function

Now, we have only six Jan­u­ar­ies at the begin­ning, because we have have only six Jan­u­ar­ies in 2022. Next six Jan­u­ar­ies are in 2023. This way we sort­ed our data by months and years using a helper column. 

Excel SORT Function

Today, we want to learn how the SORT func­tion works in Excel. 

Excel SORT Function

The SORT func­tion is avail­able from Dynam­ic Array Excel, which is around 2021. Let’s start with the sim­plest ver­sion of the SORT func­tion, where we only need to write an array. We have to remem­ber that the array must be giv­en with­out head­ers. Let’s select our data and put it as an argu­ment and let’s put our for­mu­la into a cell (Fig. 1)

=SORT(A2:C169)

SORT function
Fig. 1 SORT function

And we have the results. The most impor­tant thing about for­mu­las in Dynam­ic Array Excel is that they spill. It means that our for­mu­la is in only one cell, but the results spill. The for­mu­la is only in cell E2, but when we click on cell E3, we can see that the for­mu­la is grayed out in the for­mu­la bar. It means that it con­tains results of this func­tion, but does­n’t con­tain the func­tion itself (Fig. 2)

Formula grayed out
Fig. 2 For­mu­la grayed out

When we add an array with data to be sort­ed, Excel’s sort­ing will be based on the first col­umn, which is the col­umn with dates. In the sort­ed array we don’t see dates but num­bers because Excel does­n’t know how to copy the for­mat­ting in array for­mu­las. If we want to have prop­er for­mat­ting, we can copy the for­mat­ting using the For­mat Painter (2) from the Home tab (1). We will also high­light the cell with the for­mu­la (3) (Fig. 3)

Using the Format Painter command
Fig. 3 Using the For­mat Painter command

Now, our results are for­mat­ted. Since we sort­ed by the first col­umn, our results are basi­cal­ly the same as in our orig­i­nal col­umn. To change that, we can add the sec­ond argu­ment to our SORT func­tion, which is the sort index. It’s the num­ber of a col­umn by which we want to sort our data. Let’s write 2, mean­ing the sec­ond col­umn (Fig. 4)

=SORT(A2:C169,2)

SORT formula with the second argument
Fig. 4 SORT for­mu­la with the sec­ond argument

We can see that the sort­ing has changed. Now, we can see that only Chan­dlers are at the top because it’s an ascend­ing sort­ing. This way we can sort by any col­umn. Let’s move on to the third argu­ment, which is the sort order. Let’s change it to a descend­ing sort­ing by writ­ing ‑1 (Fig. 5)

=SORT(A2:C169,2,-1)

Descending sorting
Fig. 5 Descend­ing sorting

Now, the first sales­man in Ross. Let’s add some­thing more. Let’s sort by Sales from the largest to the small­est. Now, the sales are writ­ten ran­dom­ly in the Sales col­umn. We can add the third col­umn to the sec­ond argu­ment. We can also add a ref­er­ence to cells where num­bers of columns are writ­ten. It’s very impor­tant to write them in the cor­rect order. In our case the first cell from the top con­tains 2 and the sec­ond one con­tains 3. It means that first, we will sort by the sec­ond col­umn, and then by the third col­umn (Fig. 6)

Adding a cell reference
Fig. 6 Adding a cell reference

And we have the results. We can see that Ross is sort­ed by val­ues in the Sales col­umn. The same is with the rest of the sales­men. Now, let’s focus on the third argu­ment once again. We have two columns of sort­ing in the sec­ond argu­ment, but only one num­ber (-1) in the third argu­ment which is the sort order. It means that the sort­ing is descend­ing for each col­umn. When we want to have an ascend­ing order for one col­umn and a descend­ing order for anoth­er col­umn, we can do the same as we did with the sec­ond argu­ment. We have to refer to some cells in Excel. In our case we have a sort­ing order for each col­umn (Fig. 7)

Referring to other cells
Fig. 7 Refer­ring to oth­er cells

Now, we have an ascend­ing sort­ing for the Sales­man col­umn, and a descend­ing sort­ing for the Sales column. 

When we don’t want any addi­tion­al cells in our sheet, we can hard code them in the for­mu­la by press­ing F9 key. It changes the argu­ment into an array. We can do the same with the sort order argu­ment. When we look at the for­mu­la now, we can see that the sec­ond col­umn in ascend­ing, and the third col­umn is descend­ing (Fig. 8)

Hard coding the values
Fig. 8 Hard cod­ing the values

Now that we have every­thing we need­ed, we can can­cel the unnec­es­sary cells. We don’t even need the third argu­ment, as it is reserved for hor­i­zon­tal sort­ing. Here, we are sort­ing only by columns, so we can leave it like that. Here are our results (Fig. 9)

Results
Fig. 9 Results

https://www.youtube.com/watch?v=RzxTurQaF5I&t

Compare 2 lists — Legacy Excel classic formulas

Today, we want to com­pare two lists and find ele­ments that are on both lists as well as on only one. 

Com­pare 2 lists clas­sic for­mu­las Lega­cy Excel

We are going to use Clas­sic Excel, Lega­cy Excel and a clas­sic for­mu­la with the AGGREGATE func­tion. It means that this for­mu­la will work from 2010 and thanks to this, we won’t need to use the Crtl + Shift + Enter key combination.

Let’s start with find­ing ele­ments. We will be check­ing whether ele­ments from My son’s favorite movies list are on My daugh­ter’s favorite movies list. We can use the MATCH func­tion here. We are look­ing up ele­ments from my son’s list (list B) on my daugh­ter’s list (list A). We’re click­ing 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)

MATCH function
Fig. 1 MATCH function

Excel has just giv­en us the results. I have the Dynam­ic Array Excel, which means that Excel spilled the results. We can see that How to train a Drag­on is in list B with num­ber 5 which means that it’s on posi­tion num­ber 5 in list A. We also have Spi­der-man on posi­tion num­ber 7. If we want to extract those num­bers, we have to use the AGGREGATE func­tion. In the first argu­ment of the func­tion, we have to use a func­tion num­ber 15, as this func­tion under­stands an array for­mu­la. Then, in the sec­ond argu­ment, we have to write 6 to ignore errors. At the end, in the k argu­ment let’s write ROWS from E2 to E2 and press F4 key to lock only the first one. This will allow the for­mu­la to expand while copy­ing down (Fig. 2)

=AGGREGATE(15,6,MATCH($C$2:$C$9,$A$2:$A$11,0),ROWS($E$2,E2))

AGGREGATE function
Fig. 2 AGGREGATE function

Now, we have the results, how­ev­er there are some errors. To remove that, we can add the INDEX func­tion and write list A in the first argu­ment, then press F4 key to lock it (Fig. 3)

=INDEX($A$11:$A$11,AGGREGATE(15,6,MATCH($C$2:$C$9,$A$2:$A$11,0),ROWS($E$2,E2))

INDEX function
Fig. 3 INDEX function

Now, we have the ele­ments that are on both lists. We can remove the errors. Let’s add the IFERROR func­tion (Fig. 4)

=IFERROR(INDEX($A$11:$A$11,AGGREGATE(15,6,MATCH($C$2:$C$9,$A$2:$A$11,0),ROWS($E$2,E2))),“”)

IFERROR function
Fig. 4 IFERROR function

We have our results in the tar­get list, and there is still some back­up space in case we have some more results (Fig. 5)

Results with extra space
Fig. 5 Results with extra space

Now, we can cre­ate a for­mu­la that will look up ele­ments only from list B. Let’s start with giv­ing each ele­ment a num­ber. We’ll use the ROW func­tion here and write the whole list as the first argu­ment. Then F4 key to lock it (Fig. 6)

=ROW($C$2:$C$9)

ROW function
Fig. 6 ROW function

As we can see, Excel has spilled the results and each ele­ment has got its own num­ber. How­ev­er, we want to start from 1, not from 2 (Fig. 7)

Numbers for each element
Fig. 7 Num­bers for each element

To do this, we have to sub­tract the head­er row and press F4 key to lock it (Fig. 8)

=ROW($C$2:$C$9)-ROW($C$1)

Subtracting the header row
Fig. 8 Sub­tract­ing the head­er row

And we have all ele­ments num­bered from 1 to 8. Now, we want only num­bers of ele­ments that aren’t on list A. How can we do it? We can divide our list by the MATCH func­tion. In the first argu­ment, we write list B and press F4 key to lock it, then list A in the sec­ond argu­ment, as that’s the place where we will be look­ing up our val­ues. Then F4 key. As the final step, we write 0, as we want to have the exact match and the look­ing up process will start from the top (Fig. 9)

=(ROW($C$2:$C$9)-ROW($C$1))/MATCH($C$2:$C$9,$A$2:$A$11,0)

Dividing the list
Fig. 9 Divid­ing the list

Just like that we have num­bers only on those posi­tions that are on both lists. How­ev­er, we want a reverse sit­u­a­tion, where we will have num­bers in #N/A posi­tions. To to that we have to put our MATCH func­tion into the ISNA func­tion (Fig. 10)

=(ROW($C$2:$C$9)-ROW($C$1))/ISNA(MATCH($C$2:$C$9,$A$2:$A$11,0))

ISNA function
Fig. 10 ISNA function

If the MATCH func­tion returns an error, the ISNA func­tion will change this error into TRUE. And a num­ber divid­ed by the val­ue of TRUE will change the TRUE val­ue into 1. If the ISNA func­tion returns FALSE, the math­e­mat­i­cal oper­a­tion will change FALSE into 0. Since we can­not divide by 0, we will have #DIV/0! errors (Fig. 11)

#DIV/0! errors
Fig. 11 #DIV/0! errors

Since we have a list with num­bers which are the posi­tions of ele­ments we want to extract and errors, it’s time to put our whole for­mu­la into the AGGREGATE func­tion. Just like before, we start from the small­est val­ue, so let’s write 15, then 6, as we want to ignore errors. At the end of our func­tion, we write the ROWS func­tion with G2 cell. Then F4 key to lock the first one (Fig. 12)

=AGGREGATE(15,6,(ROW($C$2:$C$9)-ROW($C$1))/ISNA(MATCH($C$2:$C$9,$A$2:$A$11,0)),ROWS($G$2:G2))

 AGGREGATE function
Fig. 12 AGGREGATE function

Now, we have posi­tions with­out errors, which means that we can add the INDEX func­tion to our for­mu­la. As we take posi­tions from list B, we have to select the list, then press F4 key to lock it (Fig. 13)

INDEX function
Fig. 13 INDEX function

After press­ing Enter, or Ctrl + Enter, we have our results. Now, we can add the IFERROR func­tion to put emp­ty text strings in the places where the for­mu­la returns errors (Fig. 14)

=IFERROR(AGGREGATE(15,6,(ROW($C$2:$C$9)-ROW($C$1))/ISNA(MATCH($C$2:$C$9,$A$2:$A$11,0)),ROWS($G$2:G2)),“”)

IFERROR function
Fig. 14 IFERROR function

And we have our results (Fig. 15)

Results
Fig. 15 Results

This way, we have two for­mu­las. The first one to extract ele­ments that are on both lists, and the sec­ond one to extract ele­ments that are only on one list. 

https://www.youtube.com/watch?v=gb7zo6lPZSs&t