Excel and Adam - Excel And Adam

KPI in Power Pivot Challenge 01

by Excel and Adam | Jan 29, 2024 | Excel

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)

We can change here our date format into the one without time (Fig. 2)

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)

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)

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)

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)

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)

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)

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)

Let’s select the New Worksheet radio button (Fig. 10)

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)

We can see that in the Power Pivot the number of days is grayed out and we cannot use it (Fig. 12)

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)

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)

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)

Now, my measures have changed into KPIs (Fig. 16)

We still don’t see any icons. To switch them on, we need to uncheck the Status checkbox and check it again (Fig. 17)

Now, we can change the Pressure Status header name into KPI. We can also align the icons using the Center option in the Alignment area (Fig. 18)

We can see our average weekly measure and KPIs corresponding to that measure represented by icons.

https://www.youtube.com/watch?v=lsyi8XEw2y0&t=3s

2D Lookup — INDEX and MATCH or XLOOKUP functions

by Excel and Adam | Jan 24, 2024 | Excel

How to do a 2D Lookup in Excel? Let’s start.

2D Lookup | Excel Tips 68

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)

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)

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)

Just like that, we did the 2D lookup, or we have found the values we wanted (Fig. 4)

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))

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)

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))

And just like that, we have our results (Fig. 8)

Even if we change the values in our formula, the result is still correct (Fig. 9)

The results are correct both in XLOOKUPs and INDEX with MATCH functions.

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

How to insert Emojis

by Excel and Adam | Aug 1, 2023 | Excel

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)

Emoji's size modification — 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)

The third solution is using the Touch keyboard (Fig. 3 and Fig. 4)

You have to press the Emoji key, (Fig. 5)

…and choose emojis you want (Fig. 6)

When we have our emojis in Excel, we can use them in formulas. Sometimes such a formula will look differently than in a cell (Fig. 7)

One last thing. In Excel online, emojis are more colorful 🙂 (Fig. 8)

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

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

by Excel and Adam | Jul 31, 2023 | Excel

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 — Fig. 1 — Source table

to this table (Fig. 2):

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).

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).

By Delimiter option — 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).

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) .

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).

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).

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

by Excel and Adam | Jul 28, 2023 | Excel

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):

to this table (Fig. 2):

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).

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).

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).

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).

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.

Now, as Power Query treats values as a list, we can extract them by using the Extract Values option (Fig. 8).

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.

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).