If I need to extract only letters from my text, what should I do? Lets find out.
Extract only letters from text
When I need to extract only letters from my text, it means that I also need to extract spaces and enters (new lines).
With Power Query this task is quite easy.
I need to select one cell in my table, then go to the Data tab, and click on the From Table/Range command in order to take our data to Power Query (Fig. 1)
Fig. 1 From Table/Range command
Here, I have four simple pieces of text. One in English and three in Polish. We need to go to the Add column tab, then to the Custom column command (Fig. 2)
Fig. 2 Custom column command
In the Custom Column window, we have the space to name our column. Let’s call it Clean, and below we have the Custom column formula box, where we write the Text.Select function. In the function we need to work with text, so let’s select the right column (1). Then, I need letters. Since there will be string of characters, I have to use curly brackets, where we write upper case letter in the first sequence, which is “A”..“Z”. Then let’s write lower case letters, which is “a”..“z”. Writing two dots between letters means that there is a sequence. Let’s close the curly bracket and the parenthesis. Now, it should be fine (Fig 3)
Fig. 3 Whole function
After pressing OK, the system extracted for us only letters. However, the text presented like this is just gibberish. That’s why we have to add spaces. To do that, we need to go to the View bar, then select the Formula bar checkbox and start modifying our formula. Let’s write a comma and a space in double quotes (1). If we need other signs, we should also write them, e.g. a dot (2). As we can see, we have spaces and dots in our new text (Fig. 4)
Fig. 4 Modifying the formula
However, I don’t need dots in my text. What I really need are enters. We can remove the dot from the formula and write #(lf) in its place. After entering the formula, we can see that we have extracted enters, lower case letters, spaces. But, I didn’t extract Polish letters. In row 4 there are strictly Polish letters. In rows 2 and 3 there are some of them. The question is: how can I extract unusual signs from text using the Text.Select function. First of all, we have to check the Unicode. We can see that letters from A to Z is a sequence of numbers from 65 to 90. It’s similar in lower case letters. The most important lesson from it is that we have a sequence (Fig. 5)
Fig. 5 Unicode
Now, let’s go back to our formula. If I want to have Polish letters, I can write a sequence of “Ó”..“ż”. After entering the formula, we have extracted also Polish signs (Fig. 6)
Fig. 6 Extracting Polish signs
Now, that we have extracted all signs we need, we can go to the Home tab and click on the Close&Load to command to transfer our results to Excel (Fig. 7)
Fig. 7 Loading results to Excel
In the Import Data window, we have to select a cell (Fig. 7)
Fig. 7 Choosing the target cell
As we can see, the text is written in one line, so we need to wrap it. Let’s go to the Home tab and click on the Wrap Text command (Fig. 8)
Fig. 8 Wrap Text command
Now, we clearly see upper case letter, lower case letter, spaces, enters. I removed signs that I didn’t need (Fig. 9)
When we need more space while working in Dashboard in Excel, we can just hide unnecessary options. How? Let’s find out.
Hide scroll bars, formula bars, column headers
The options we can hide are e.g. scroll bars, sheet tabs, formula bars as well as column/row headers. If we want to hide then we just go to the File menu and choose the Options command (Fig. 1)
Fig. 1 Options
In the Excel Options window, we choose the Advanced tag. If you prefer using shortcuts, you can press Alt + T + O, which also opens the window. Lets go to the Display area and uncheck the Show formula bar checkbox. Remember that it works for all Excel files (Fig.2 )
Fig. 2 Show formula bar checkbox
Then, we can go to the Display options for this workbook area. We have to uncheck the Show horizontal scroll bar, Show vertical scroll bar, Show sheet tabs checkboxes (Fig. 3)
Fig. 3 Three checkboxes
We can even go to the Display options for this worksheet area where we can uncheck the Show row and column headers checkbox and press OK (Fig. 4)
Fig. 4 Show row and column headers checkbox
We can also double-click on the Home tab to hide the ribbon, which gives us even more space than before.
Now, I can press Ctrl + Page Down to go to another workbook. Here, we can see that column and row headers are visible. However, when we go back to our workbook by pressing Ctrl + Page Up, we can see that there are no row or column headers. That’s how you make more space in your Dashboard.
How to add a drop-down list in Excel? It’s a really simple task.
First of all, we have to have our list (1). Then, we have to select a cell or cells to which we want to add the drop-down list (2). Then, we’re going to the Data tab (3) and click on the Data Validation command (4) (Fig. 1)
Fig. 1 Preparing data
In the Data Validation window, we have to select the List option in the Allow bar, then we’re selecting the range and write it into the Source bar. Then OK (Fig. 3)
Fig. 2 Data Validation window
Now, we have a drop-down list (Fig. 3)
Fig. 3 A drop-down list
Since our cells with data are in a table, when we add a new line to the table, it also involves the drop-down list.
Even, if we cut and paste our new data, the cells in the selected column will still have the drop-down list. What’s more, in the newest Excel version, we have even Lookup options. We just have to start writing the first letter of a word we’re looking for, then press the Tab key.
Fig. 4 Lookup option
This function, however, doesn’t work when we start from the middle of our text. It works only with the first letter.
Today, we are going to address our first challenge. The challenge is to create KPI in the Power Pivot basing on an average blood pressure. We want to calculate our averages basing on weeks, as it’s more complicated that calculating monthly averages :).
For individuals — determine the average monthly ( for ambitious weekly) upper blood pressure KPIs. We assume that if it is in the range of 110 — 130 it is Good, if it is in the range of 100 — 110 or 130 ‑140 it is OK, and if it is above or below these ranges, it is Bad.
KPI in Power Pivot | Challenge 01
We have our Pivot with one cell selected. Let’s go to the Power Pivot tab (1), then to the Add to Data Model command (2) (Fig. 1)
Fig. 1 Add do Data Model command
We can change here our date format into the one without time (Fig. 2)
Fig. 2 Changing the Date format
Now, we have to create an average basing on the Upper blood pressure column. We basically select one cell in the calculation area (1), then go to the Home tab (2), then choose the Average command (3) (Fig. 3)
Fig. 3 Average command
The Power Pivot did the calculation automatically for us. I’ll just change the name so that it’s shorter. Let’s leave only ‘pressure’. Now, let’s change the format into Decimal Number (Fig. 4)
Fig. 4 Decimal number command
When we have the average with two decimal places, we can start creating a week column, because it’s hard to group by weeks in the Power Pivot. Let’s call this column WeekNum. Then we can use exactly the same function, which is the WEEKNUM function basing on the Date column. If we want our week to start on Monday, we have to write 2 (Fig. 5)
=WEEKNUM([Date],2)
Fig. 5 Creating a WeekNum column
Let’s press Enter, and we have our column with week numbers. All weeks start from Mondays. For some people this solution would be enough. However, I prefer to have the start day and the end day of the week. Let’s create another column. This time I’ll call it weekText. Let’s write ‘Date’. Then, we have to subtract the number of the weekday. To do so, we can use the WEEKDAY function again basing on the Date column. Now, we have three options of numbering the weekdays. 1- Sunday=1 through Saturday=7, 2 — Monday=1 through Sunday=7, 3 — Monday=0 through Sunday=7. The last option is the one we need. Monday will be the first day, but it has got zero. It means that we will start counting from zero which is exactly what we need (Fig. 6)
=WEEKDAY([Date],3)
Fig. 6 Creating a weekText column
After pressing Enter, we have our new column with only Mondays. In all seven rows of the week we have only Mondays. Now, let’s add the last day of the week. Let’s add a minus sign with spaces as a delimiter. It allows us to delete the time from our column. Then, let’s copy our WEEKDAY formula and paste it after the delimiter. Then, we need to write +6 days to have Sunday (Fig. 7)
Fig. 7 First and last days of the week
We can see the results. If the date format is proper for us, this is all. But, if we want to change the date format, we should add the FORMAT function to the first and second calculation (Fig. 8)
Fig. 8 Adding the FORMAT function
However, the current form is OK for me, so we can create a Pivot Table based on our measures and calculations. Let’s click on the Pivot Table command in the Home tab (Fig. 9)
Fig. 9 Creating a pivot table
Let’s select the New Worksheet radio button (Fig. 10)
Fig. 10 Create PivotTable window
If I start with the Date column and go to Rows, most newer versions will group data automatically by months. Let’s try to group it by days (Fig. 11)
Fig. 11 Group option
We can see that in the Power Pivot the number of days is grayed out and we cannot use it (Fig. 12)
Fig. 12 Days grayed out
That’s why we should crate additional columns to address this problem. I should use the weekNum or the weekText column in our Rows area. Let’s also add Person to the Columns area and the Grand total by going to the Design tab and clicking on the Grand Totals command. Let also add our measure, which is the fx pressure into Values (Fig. 13)
Fig. 13 Creating additional columns
Now, we can see average blood pressure for each person and each week. We also have the grand total for all people in a week. It’s time to add KPI to our pivot table. Let’s go to the Power Pivot tab (1), then to the KPIs command (2) and choose the New KPI option (3) (Fig. 14)
Fig. 14 Creating KPI
In the Key Performance Indicator (KPI) window, in the KPI base field value, we have only pressure to choose from, so let’s leave it. In the Absolute Value bar let’s write 120. In the Define Status Threshold graphics we should write our assumptions, then select an icon style (Fig. 15)
When we want to find an intersection between a row and a column basing on headers, we can do it in two different ways. The first one will be in Legacy Excel, and the second in Dynamic Array Excel.
The first solution uses the MATCH function as well as its close friend, the INDEX function.
Let’s start with looking for the country. In this case we want to find the row number. Let’s write the cell, the range where we will be looking for and 0 for an exact match (Fig. 1)
=MATCH(B8,A3:A6,0)
Fig. 1 MATCH function for countries
We also want to find the column number. In this case we also use the MATCH function. Let’s the cell, the range where we will be looking for and 0 for an exact match (Fig. 2)
=MATCH(B9,B2:G2,0)
Fig. 2 MATCH function for months
When we have our row and column numbers, we can use the INDEX function, select the whole dataset, then write the row and column numbers (Fig. 3)
=INDEX(B3:G6,D8,D9)
Fig. 3 INDEX function
Just like that, we did the 2D lookup, or we have found the values we wanted (Fig. 4)
Fig. 4 Results
So far, we have had three formulas. If we want to have only one, we need to implement some modifications. We have to copy our MATCH function and paste it in the place referring to cell D8. Then, we have to copy the second MATCH function without the equal sign, and paste it in the place of the reference to cell D9. Now, we have one, bigger formula instead of three smaller ones (Fig. 5)
=INDEX(B3:G6,MATCH(B8,A3:A6,0),MATCH(B9,B2:G2,0))
Fig. 5 One, big formula
Now, let’s focus on the solution in the Array Excel. We will be using here the XLOOKUP function twice.
First, we are looking for a row in the country headers. Then, as a returning array, we give Excel the whole dataset (Fig. 6)
=XLOOKUP(B8,A3:A6,B3:G6)
Fig. 6 XLOOKUP function
Since I’m using the XLOOKUP function, and we are working in the Array Excel,Excel will return the whole row.
In such a case, we have to find a proper column. We can also do it with the XLOOKUP function. Let’s write the XLOOKUP function once again. This time we will be looking for a month in the month headers. As a return array, we have the row we found previously (Fig. 7)
=XLOOKUP(B9,B2:G2,XLOOKUP(B8,A3:A6,B3:G6))
Fig. 7 Another XLOOKUP function
And just like that, we have our results (Fig. 8)
Fig. 8 Results
Even if we change the values in our formula, the result is still correct (Fig. 9)
Fig. 9 Checking the formula
The results are correct both in XLOOKUPs and INDEX with MATCH functions.
