Today, we will learn how to put emojis into Excel. We will have a look at three solutions.
How to insert Emojis in Excel
The first one is just copying emojis from the internet into Excel in the edit mode and pasting it. If the emoji we pasted is too large for a cell, we have to change the font into smaller (Fig. 67)
Fig. 1 Emoji’s size modification
The second solution involves inserting symbols by using the Symbol icon (1) and choosing the emoji you want (2). Then press the Insert button (3) and that’s it (Fig. 2)
Fig. 2 Inserting emojis from symbols
The third solution is using the Touch keyboard (Fig. 3 and Fig. 4)
Do you want to split cells into separate rows and repeat values from other columns? I will show you how.
How to Split column into rows | Excel Power Query 02
In our example, we have names of publishers and series. In short, we want to go from this table (Fig. 1):
Fig. 1 — Source table
to this table (Fig. 2):
Fig. 2 Target table
The task is really simple when using Power Query. We need to go to the Data tab and choose the From Table/Range command (Fig. 3).
Fig. 3 From Table/Range command
It allows us to import our table to Power Query. In our example, we have two columns from which we want to select the Series column and split it into rows. When this column is selected, we need to go to the Home tab and find the Split Column command, then choose the By Delimiter option (Fig. 4).
Fig. 4 By Delimiter option
Power Query will choose a space as the delimiter for us. However, in our case the delimiter is a semicolon and a space. It means that we need to select the Custom option and write the semicolon and a space in the proper bar (Fig. 5).
Fig. 5 Writing the proper delimiter
Then, we should select the Each occurrence of the delimiter radio button. Now, let’s open Advanced options. We can see that columns are selected as the default option. Let’s change it into rows. Now, we can press OK (Fig.6) .
Fig.6 Advanced options
And we have the result. Now, we can go to the Home tab, then to the Close and Load command and choose the Close and Load to option. This way, we can load important data from Power Query to Excel (Fig. 7).
Fig. 7 Data loading
In Excel, we are choosing the Table radio button, then the Existing worksheet and then we have to select the target cell, which in our case is D1. Let’s close it with OK (Fig 8).
Fig. 8 Import Data window
As we can see, we went from the table on the left to the table on the right (Fig. 9).
Today, we want to combine all values from the same group into one cell. Let’s go.
Combine all values within group with delimiter | Excel Power Query 03
In our example, we want to combine all publishers. It means that we’re doing an operation reverse to the one in the previous post. This time, we want to go from this table (Fig. 1):
Fig. 1 Source table
to this table (Fig. 2):
Fig. 2 Target table
We can do it with Power Query. First, select the table. Then, go to the Data tab and select the From Table/Range command (Fig. 3).
Fig. 3 Going to Power Query
We can see that we have our two columns. The first thing we want to do is group our publishers. Let’s select the Publisher header, then go to the Home tab and then Group By command. In the new window, we can see that we are grouping by the Publisher column. We still need a new aggregation. Let’s change the Operation bar into All Rows and write ‘Series’ in the New column name bar. We cannot change anything in the Column bar. Let’s press OK (Fig. 4).
Fig. 4 Group By window
Now, we have the Publishers group, and in the cells from the Series column, we have all rows from the previous stage. The whole table is in one, single cell (Fig. 5).
Fig. 5 Whole table in one cell
However, we don’t need the whole table, which consists of two columns. We only need the Series column. We can change it by modifying the code. Let’s go the View tab, and let’s check whether the Formula bar is ticked. If it is so, we can start modifying the code in the formula bar (Fig. 6).
Fig. 6 Going to the Formula bar
Looking at the code, we can see that we don’t want to choose underscore for each step. It means that we need only one column. 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 important to write ‘list’ with a lowercase (Fig. 7). And we see that we have only lists when we click on any of the Series cells.
Fig. 7 Code modification
Now, as Power Query treats values as a list, we can extract them by using the Extract Values option (Fig. 8).
Fig. 8 Extracting values
We are extracting the values by combining them with a delimiter. We want to use a comma and a space, so let’s select the Custom option first and then write what we want (Fig. 9). Let’s close it with OK.
Fig. 9 Delimiter
Now, we can see that we combined all Series names into one cell for each publisher. We can now go to the Home tab, then Close & Load command, and press the Close & Load to option (Fig. 10).
Fig. 10 Loading data to Excel
Let’s select the Table and the Existing Worksheet radio buttons in the Import Data window (Fig. 11)
Fig. 11 Data importing
We can see that all values within one group are combined into one cell (Fig. 12)
Let’s assume that we are in the future and we sell spaceships. We want to find out what class of a spaceship each country has bought basing on our table of unique spaceship classes.
Combine all unique values within group | Excel Power Query 04
With Power Query, this task is very simple. We have to select our main table, then go to the Data tab and choose the From Table/Range command in order to export our data from Excel to Power Query (Fig. 1).
Fig. 1 Exporting data to Power Query
Since we are interested which country has bought which spaceship, we want to group our data by the country. We have to select the Country column and go to the Home tab (1) and choose the Group By command (2). In the window, we need to choose the Advanced option (3) because we want to add more aggregation. I want to count how many ships each country has bought as well as know the spaceship class names. Since there isn’t any proper operation, let’s choose the Sum option (4), which we will modify later in the code. In the column bar, we’re choosing the Ship class (5). As a final step, let’s write a new name, which is going to be ‘Unique ship classes’ (6) (Fig. 2)
Fig. 2 Data grouping
In the new table, there are some errors in the last column, because we wanted to sum up text, which is impossible. It means that we need to modify our code. We need to go to the View (1) tab and tick the Formula bar checkbox (2). We can see that the formula uses the List.Sum function (3) (Fig. 3)
Fig. 3 Formula bar
What we want to do is combine all values, so let’s change the List.Sum into the Text.Combine function (1). We also need to add a separator, which will be a comma and a space (2) (Fig. 4)
Fig. 4 Code modification
Now, we can see that Power Query has changed its calculations. All ship classes are combined. However, as I said before, we need only unique values without repeats. We just need to add one function, which is the List.Distinct function. (Fig. 5)
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 command (2) and the Close & Load to option (3) (Fig. 6)
Fig. 6 Going back to Excel
In Excel, we have to remember to select the Table (1) and Existing worksheet (2) radio buttons. Then choose a target cell (Fig. 7)
Fig. 7 Data importing
As we can see, we have counted how many ships each country has bought as well as the product class (Fig. 8)
Today, we want to find all file names from a folder.
List of file names from a folder | Excel Power Query 05
Let’s see our folder. We have different kinds of files and even a subfolder. There is also an empty folder called Empty, which must stay empty. Our task is to find all files connected to Excel, which means that we will need to do some filtering (Fig. 1)
Fig. 1 Folder content
We know the path, so we can copy it, then go to Excel, and then use Power Query, which is the simplest solution I know. We need to go the Data tab (1), then to the Get Data command (2), then to the From File (3) and From Folder (4) options (Fig. 2)
Fig. 2 From Folder option
In the new window, we can find our folder, for example, by the navigation bar, where we can paste the whole path and press Open (Fig. 3)
Fig. 3 Navigation bar
We can see that Power Query has prepared a whole batch of information from the given folder, as well as from the Subfolder for us. We don’t see the Empty folder, because there aren’t any files. In our situation, it’s fine for us. Then, we need to click on the Transform Data button (Fig. 4)
Fig. 4 Information prepared by Power Query
The Transform Data option gives us the chance to add some filtering to get the data we are interested in. In the window that appeared, we can see that we can filter the information by the Folder Path (1). If we are interested only in the main folder, we can check only the main folder (2) (Fig. 5)
Fig. 5 Filtering by the Folder Path
However, we want all folders, together with the Subfolder, so let’s close this filtering and go to the Extension filtering. Since we are interested in Excel files, we are looking for the ones with an .xls extension. Remember that Power Query is case sensitive, so if we have some suspicions that there may be some upper case letters, we have to transform our Extension column. We need to go to the Transform tab, then to the format command and the lowercase option (Fig. 6)
Fig. 6 Lowercase option
Now, we can filter this column by Text Filters and the Begins with option (Fig. 7)
Fig. 7 Begins with option
We want the text to begin with .xls, which should get all extensions connected with Excel (Fig. 8)
Fig. 8 .xls extension
We can see that we have our chosen extensions as well as the file names also with extensions. This is the information that we needed, so let’s right click on the Name bar, and select the Remove Other Columns option (Fig. 9)
Fig. 9 Remove Other Column option
We are left only with one column, where we have file names with extensions. Sometimes, we don’t need any extensions. In those cases, we can add one more step to Power Query. We have to select the column, then go to the Transform tab, then to the Extract command and the Text Before Delimiter option (Fig. 10)
Fig. 10 Text Before Delimiter option
Our delimiter is just a dot, so let’s write it (Fig. 11)
Fig. 11 Delimiter
Now, we have only file names without extensions. However, let’s say that I still want those extensions. In such a case I need to delete this step (Fig. 12)
Fig. 12 Deleting the last filtering
Now, I go to the Home tab, click on the Close & Load command, then the Close & Load to option (Fig. 13)
Fig. 13 Going back to Excel
Let’s choose the target cell and select the Table radio button (Fig. 14)
Today, we want to learn how advanced filters work in Excel, especially between conditions.
Advanced Filters and between condition
Let’s start. When we have our dataset, we write our advanced filter conditions in cells. We need to have a header and a condition for this header. If we write conditions in one row, all of them are connected with the final criteria. It means that each condition should be met in order to take out the whole row from our dataset (1). When we add conditions to further rows, it meas that it’s an ‘or’ condition (2). In this case, we will look for strawberries or plums. If we want to create a between condition, we need to write the header two times (3) in order to create a common part. In our case, we are looking for strawberries that are equal or greater than 500 and smaller or equal to 750, and in the case of plums we want the plums that are equal or greater than 300 and smaller or equal to 500. We have to remember that those conditions should make a common part (Fig. 1)
Fig. 1 Dataset with two headers
When we want to go to advanced filters, we need to select one cell in our dataset (1), then go to the Data tab (2), then Advanced filters (3). In the Advanced filter window press the Copy to another location radio button (4), and select the criteria range (5). The last step is selecting the target cell, which will be cell E6 (6) (Fig. 2)
Fig. 2 Creating a table
Remember that if conditions are written in one row, they are combined as an ‘and’ condition, and when they are in separate rows, they are combined as an ‘or’ condition. Moreover, we can add an empty condition for different headers. But, since there isn’t any condition in the Date column, we can select the range without it. In figure 3, we can see the final table with sales of plums that are between 300 and 500 and sales of strawberries that are between 500 and 750, together with all possible dates for those conditions (Fig. 3)
Today, we want to learn how wildcards work with advanced filters.
Advanced Filters and wildcards
First of all, we have to remember that an asterisk replaces any text string, even an empty one. A question mark replaces any single character. A tilde disables a wildcard.
Let’s start with finding ‘Red’ in the Product column. To do that, we have to select one cell in our data, then go to the Data tab (1) and click on the Advanced filter command (2). In the Advanced filter window, we have to select the Copy to another location radio button (3), then write the Criteria range (4). In the Copy to bar, we select our target cell (5).
Fig. 1 Advanced filter window
After pressing OK, we have our results. In advanced filters, we don’t have a simple condition with text. This condition is not an equal condition, but a ‘starts with’ condition. In our target column, we have the ‘red’ word, however, advanced filters chose whole cells that start with ‘red’. If we want only the exact word, we have to write it with a single quote, then equals, then our word. Let’s write it. We should make another advanced filter, so let’s make the same steps as in the previous example, starting with the Data tab and changing the Criteria range to another column (1) (Fig. 2)
Fig. 2 Advanced filters with an exact word
In the target cell, we can see only one cell with DragonFly, even though there are three cells with DragonFly in our data.
Now, let’s try the asterisk option. We write an asterisk before ‘beetle’, but we have to remember that it is always connected with the ‘starts with’ option. We are working with wildcards as well as with the ‘starts with’ criteria. It works as if an asterisk was also at the end of ‘beetle’. Let’s make a proper advanced filter for this case staring with the Data tab and changing the Criteria range into another column (1) (Fig. 3)
Fig. 3 Advanced filters with an asterisk
As we can see, ‘beetle’ can be at the end, at the beginning or even in the middle of our cell.
Now, let’s work with question marks. We need to remember that one question mark represents only one sign. It can be a digit, a letter or any other sign. For example ‘Ant ? — ? mm’ cannot represent ‘Ant 6 — 10 mm’, because there are too many signs in 10. Let’s make an advanced filter for that. We start with the Data tab, and we change the Criteria range (1) and the target cell (2) (Fig. 4)
Fig. 4 Advanced filters with an asterisk
We can see that we found two cells where a question mark represented 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 beginning. If we put only an asterisk and an Ant, like this ‘*Ant’, Excel would find any Ant, regardless of its position. The tilde makes the asterisk stop being a wildcard. We do the same as in previous cases, changing only the Criteria range and the target cell (Fig. 5)
Fig. 5 Advanced filters with a tilde
We can see that Excel found only one Ant with an asterisk at the beginning. It’s because we turned off wildcards.
Today, we want to learn about sorting by months with the SORT function.
SORT by months using functions
Let’s start with writing the SORT function, with an array as the first argument. In the second argument we need to write 2, as we want to sort by months, which are located in the second column. Now, we can close the function (Fig. 1).
=SORT(A2:C145,2)
Fig. 1 SORT function
Just like that the SORT function sorted data by the Month column. It’s important that the SORT function doesn’t consider the custom list. Excel does it, but the SORT function doesn’t. Now. let’s have a look at how we can properly sort by months using the SORT function. First of all, we cannot use the SORT function, but the SORTBY function. In this function, even if we change the second argument to a month column, we still get an ascending sorting, from A to Z. It means that the sorting is based on the alphabet, not on months of the year. The thing is we cannot do the sorting based on arguments. We need to use the MATCH function. It will look up a given month in a list of months. In the third argument we write 0, as we want to have the exact match (Fig. 2)
=MATCH(B2,$J$2:$J$13,0)
Fig. 2 MATCH function
The MATCH function starts the sorting from the top on the Month column. As we can see in column D, we have a proper number for each month. The column acts as a helper column, however, sometimes we don’t want any helper column, so we can create a helper column in our formula. We can match each month from the Month column (column B) using the MATCH function, where we need to write the lookup value in the first argument, which is our data column. In the second argument, 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))
Fig. 3 SORTBY function
It works. We see that the SORTBY function found a proper numbers for each month. It’s important that it sorts by months, not by years. However, we can add the year as well. But, I want to show you something else. First of all, if we don’t like the custom helper column list, we can just click F9 in the lookup array argument to hard code the array list in our formula (Fig. 4)
We can even delete the custom column. As we can see, it works properly. When we look at column D, we can see #N/A error. It’s because the MATCH function is trying to find months in the custom column cells which are empty (Fig. 5)
=MATCH(B2,$J$2:$J$13,0)
Fig. 5 Empty cells in the Month column
Now, let’s focus on dates based on years and months. Sometimes, our date in a dataset isn’t written only in one column, but it can be separated into the year, month and day columns. We can create a full date from the data we have. We will try it in the Helper column. First of all, we write =, then 1 combined with a month, combined with a year (Fig. 6)
=1&B2&A2
Fig. 6 Creating a date
And just like that, we combined the 1st of January 2022. Our date, however, is text. We want Excel to understand it as a date, so we just need to add parentheses and a 0 (Fig. 7)
=(1&B2&A2)+0
Fig. 7 Changing text into numbers
Excel magic has just happened! It changed text into numbers. Now, to change the numbers into dates, we need to go to the Home tab (1), then change the formatting (2) into Short Date (3) (Fig. 8)
Fig. 8 Numbers into a date
Since we have dates based on the year and the month columns, we can sort them. Let’s write the SORTBY function. In the first argument we write the data column, and in the second column we have to combine 1 with the Month column, then with the Year column. When we close the parentheses we need to add 0 to change text values into numbers (Fig. 9)
=SORTBY(A2:C145,(1&B2:B145&A2:A145)+0)
Fig. 9 SORTBY function
Now, we have only six Januaries at the beginning, because we have have only six Januaries in 2022. Next six Januaries are in 2023. This way we sorted our data by months and years using a helper column.
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.