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 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.
Do you want to put a Pareto chart in Excel? I will show you how.
How to Create A Pareto Chart
If you want to put a chart on which you have columns that represent income and a line that represents the cumulative percent income, follow me.
From Excel 2016, you can simply go to the Insert tab and choose the Pareto chart from the Histogram command (Fig. 1)
Fig. 1 Inserting a Pareto chart
And you have the Pareto chart ready. However, the charts from Excel 2016 have some drawbacks. The line isn’t an actual series of the chart. It means that we cannot add data labels there. That’s why I don’t prefer using this type of chart. What I prefer is the earlier version that gives me more freedom concerning value changing, although it requires more calculations. We have to calculate the cumulative percent income on our own (Fig. 2)
=SUM(B$2:$B2)/SUM($B$2:$B$9)
Fig. 2 Cumulative percent calculations
Now, we can insert a simple column chart. Since the income is enormously bigger that the percentage, we aren’t able to see the income columns at all, but we want to select them (Fig. 3). How can we do it?
Fig. 3 No % Income columns
We can select our chart and go to the Format tab. On the left we can see all elements of the chart. We are interested in the Series “% Income” option (Fig. 4)
Fig. 4 Series “% Income”
We can see now that the series is selected on the chart. Let’s press Ctrl + 1 and go to Format Data Series, Series Options, Plot Series On, and select the Secondary Axis option. Now we have % income on a different axis (Fig. 5)
Fig. 5 Secondary Axis options
Now, we can change the chart type into a chart that will represent income better. We have to click on the chart element once, then go to the Insert tab and the Line chart with Markers option (Fig. 6)
Fig. 6 Line Chart with Markers
Since we are creating a Pareto chart ourselves, the values aren’t sorted and we have to do it manually. We just select one cell in our data, then go to the Data tab and choose the from Z to A option (Fig. 7)
Fig. 7 Data sorting
Now the data on the chart is nicely sorted and it looks more like a Pareto chart. We still have to add modifications to make it a real Pareto chart. First of all, let’s select a column and press Ctrl + 1. On the right, we have the Gap Width option. Let’s slide it to 0%. Now, let’s go to the secondary axis. It goes up to 120%, however our maximum is 100% (Fig. 8)
Fig. 8 Gap Width modification
We have to select the axis, press Ctrl + 1, go to Axis Options and write 1 in the Maximum bar, which equals 100%. We also change our Major Units to 0.2 which means that there will be less percent numbers showed on the axis (Fig. 9)
Fig. 9 Less percent numbers
We still don’t need the 0 after dot in our percentages, so let’s go to Numbers and change the Format Code from 0.0% to 0% and click Add (Fig. 10)
Fig. 10 Format Code change
Now, let’s go to the Income Format Axis. Press the axis and Ctrl + 1. Let’s change the Major from 100000 to 200000. This way the chart will show less numbers (Fig. 11)
Fig. 11 Less numbers
What I care about the most right now are data labels for the line. Let’s click once on the line, click on the plus sign and we have Data Labels option. Let’s place them above our line (Fig. 12)
Fig. 12 Data Labels
We still need to set a proper title. Let’s just write Pareto. After implementing the most important changes, the Pareto chart looks like that (Fig. 13)
Today, we are going to learn how to group columns and rows. In our example, we have month by month sales, and their summaries by the first quarter, the second quarter and half a year with the use of the SUBTOTAL function.
How to group columns and rowsFig. 1 Columns
Sometimes, we don’t want to show lower values, i.e. values from individual months. We can do it by hiding those columns (Fig. 2).
Fig. 2 Hide option
And sometimes, we want to show those values (Fig. 3).
Fig. 3 Unhide option
Those tasks, however, can sometimes be a bit tedious. That’s why we are going learn how to group those columns to make it simpler. First, you have to select the columns you want, then go to the Data tab and click on the Group command. Now, we can see a line above column names, which allows us to hide the columns just by clicking on the minus sign (Fig. 4).
Fig. 4 Grouped columns
We can also unhide them by clicking on the plus sign (Fig. 5).
Fig. 5 Unhiding the grouped columns
Now, the task is quite simple. You can hide or unhide many columns at once. We can group even more columns this way, however, it’s important to have a space of at least one column between those groups so that Excel doesn’t combine those groups in one (Fig. 6).
Fig. 6 Groups separated by one column
When we want to ungroup our data, there is a simple solution. You have to go to the Data tab, expand the Ungroup command, and choose the Clear Outline option (Fig. 7). This way, Excel will clear all groups in the sheet. Usually, it’s easier to clear all groups and start creating new groups from the beginning than trying to fix it.
Fig. 7 Ungrouping data
Due to the fact that our values are combined from different columns, we can make Excel group the values for us. You just select any cell, go to the Data tab, expand the Group command and choose the Auto Outline option (Fig. 8).
Fig. 8 Auto Outline option
This way, Excel has created three groups for us. Those groups aren’t on the same level because each lower level group combines three columns, and the upper level group combines all the columns from the lower groups and a few more (Fig. 9).
Fig. 9 Groups on different levels
When we click on the group level number, Excel will expand or collapse all groups in this level (Fig. 10).
Fig. 10 Group level numbers
Now, let’s group some rows. We don’t have any function that sums up or combines rows, so we have to do it manually. We have to select the rows we want, then go to the Data tab, then the Group option (Fig. 11).
Fig. 11 Row grouping
If we select a range, not whole columns or rows and press the Group command, Excel doesn’t know which ones we want to group. Then we have to select the proper radio button (Fig. 12).
Fig. 12 Selecting the proper option
We can also select more rows and group them by pressing the Group option to create a group on a higher level. This group will consist of groups from a lower level. We can expand and collapse them the using also level number buttons (Fig. 13).
