Excel VBA —  How Absolute and relative references in macro work | Introduction to macros and VBA 05

Excel VBA — How Absolute and rel­a­tive ref­er­ences in macros | Intro­duc­tion to macros and VBA 05

Today, we will talk about absolute and rel­a­tive ref­er­ences in macros as well as dif­fer­ent ways to run our macro. 

Today, we are going to record a macro, where we will put the TODAY func­tion. How­ev­er this time, we will press ENTER instead of the Ctrl + Enter short­cut. We can start with cell A5. Let’s use the Record but­ton locat­ed in the sta­tus bar tab this time instead of using the Devel­op­er tab (Fig. 1)

Fig. 1 Record button
Fig. 1 Record button

Let’s call our macro ‘Today2’. We don­t’t need any short­cut but let’s try to add a short­cut here by click­ing the Ctrl + D com­bi­na­tion and see what hap­pens lat­er on. We want to save our macro in the Per­son­al Macro Work­book. When we press OK, Excel won’t allow us to save our macro, because we already have a macro with this short­cut. Let’s delete the short­cut then. When we use a short­cut that already exists in Excel, macro short­cut for macro will be more impor­tant than the default short­cut, e.g. Ctrl key. Thus, we should­n’t use such kind of short­cuts. Let’s press OK (Fig. 2)

Fig. 2 Record Macro window
Fig. 2 Record Macro window

Now, we just write TODAY in cell A5. It’s impor­tant that the Use Rel­a­tive Ref­er­ences isn’t high­light­ed (Fig. 3)

Fig. 3 Use Relative References not highlighted
Fig. 3 Use Rel­a­tive Ref­er­ences not highlighted

After we fin­ished writ­ing TODAY, let’s press ENTER. After writ­ing the for­mu­la, we can see that Excel went down one cell by default. Now, we just press STOP on the sta­tus bar (Fig. 4)

Fig. 4 Stop on the status bar
Fig. 4 Stop on the sta­tus bar

After we stopped our macro, how can we run it now? We can go to the Devel­op­er tab and press the View Macros com­mand or just click the Alt + F8 key short­cut (Fig. 5)

Fig. 5 View Macros command
Fig. 5 View Macros command

In the win­dow that has appeared, we can see all avail­able macros, which are in All Open Work­books. Now, we have only two. We can see that before the macro name, we have the file name. We can choose macros from many files (Fig. 6)

Fig. 6 File name and files

For us, it’s impor­tant to run the Today2 macro, so let’s select it and press the Run but­ton (Fig. 7)

Fig. 7 Run button
Fig. 7 Run button

Excel has writ­ten the TODAY func­tion in our select­ed cell, which is C5 and has gone to cell A6 (Fig. 8)

Fig. 8 Excel in cell A6
Fig. 8 Excel in cell A6

Let’s try a sim­i­lar thing with the firs macro. Let’s press Alt + F8 short­cut, select the Today2 macro and press RUN. Now, let’s see what exact­ly our macro has done. Let’s press Alt + F11. We should see our first macro and our new macro Today2, as well as some com­ments (Fig. 9)

Fig. 9 Our macros
Fig. 9 Our macros

Let’s delete the com­ments as we don’t need them and let’s locate the lines so that they are clear­er to read. Now, we have two lines of the code. The first line of the Today2 macro is very sim­i­lar to the Today macro. How­ev­er, instead of ‘Selec­tion’, we have ‘Active­Cell’. It means that our for­mu­la is writ­ten in the active cell. After the line that con­tains our macro, Excel will always go to cell A6, We can see it in the next line of ‘Range (“A6”). Select. ‘Select’ means the action, i.e. that we have select­ed the cell (Fig. 10)

Fig. 10 Code lines
Fig. 10 Code lines

This is how macros are work­ing. If we select a range, the TODAY func­tion will be placed only in the active cell. Then, Excel will go to cell A6.

Let’s try a sim­i­lar macro. The only dif­fer­ence is that this time we will be using rel­a­tive ref­er­ences. So let’s do it once again: click on the Record Macro but­ton (1), let’s call it Today3 (2), This Work­book (3) and press OK (4) (Fig. 11)

Fig. 11 Recording a new macro
Fig. 11 Record­ing a new macro

We’re writ­ing every­thing once again: TODAY func­tion in cell A7 (1), then Enter (2), then the Stop Record­ing but­ton (3) (Fig. 12)

Fig. 12 The process of recording
Fig. 12 The process of recording

This time we were using rel­a­tive ref­er­ences. Let’s press Alt + F11. Where is our macro? It’s not in the PERSONAL.XLSB file but in our Macro file, where we record. It means that we have to find Mod­ules (1), where Mod­ule 1 should be added (2). Let’s dou­ble-click it and we can see our macros (Fig. 13)

Fig. Module 1 with our new macro
Fig. Mod­ule 1 with our new macro

We don’t need com­ments so let’s delete them again. Let’s also adjust the size of the win­dows so that we can see it clear. We have the TODAY 2 and TODAY 3 func­tions here. We’ve found our macro (Fig. 14)

Fig. 14 Both macros
Fig. 14 Both macros

There is one more way to find our macros. Let’t go to the Devel­op­er (1) tab and go to the View Macros but­ton (2). In the win­dow that appeared we can see that we have many options there like Run, Step into, Edit, Delete and Options (3). For our macro, the Options but­ton is the most sig­nif­i­cant one, because here we can see short­cuts for our macro (4) (Fig. 15)

Fig. 15 Macro options
Fig. 15 Macro options

If we delete D, this macro will delete the short­cut if I press OK. How­ev­er, we’re not going to do that. Let’s close the Macro Options win­dow and let’s focus on the Today3 macro. Since this file is still open, we don’t see any names before. Let’s go to the Edit option of this macro (Fig. 16)

Fig. 16 Edit option
Fig. 16 Edit option

This option leads us to the exact win­dow our macro is stored. The first line in the Today3 macro is exact­ly the same as in Today2 macro. How­ev­er, the sec­ond line is total­ly dif­fer­ent. First of all, both lines start with ‘Active­Cell’. Then, we have Off­set (1, 0) — by one row and 0 columns. It means that we went one cell down. Then, VBA tells us that we select­ed Range A1. In fact, we did­n’t select A1 or any oth­er cells. Some­times, it’s hard to under­stand VBA due to some inputs. It’s true that we did­n’t select A1, but when we went one cell down from the active cell, we select­ed a range that is the same size as range A1 cell. 

Once again. First we start with Active Cell, then there is a dot, which means that we take anoth­er step, then choos­ing the Off­set, one row, 0 columns, then we have the Range A1 and what we do with this range, which is select­ing the range (Fig. 17)

Fig. 17 Today3 macro
Fig. 17 Today3 macro

Let’s go back to our work­sheet and select cell A8, then go once again to our macro by click­ing Alt + F11. This time, we press Play (Run Sub) in our VBA edi­tor or F5 as a short­cut (Fig. 18)

Fig. 18 Run Sub command
Fig. 18 Run Sub command

What we notice here is that Excel has writ­ten the Today func­tion in cell A8 and went one cell below (Fig. 19)

Fig. 19 TODAY function and once cell below
Fig. 19 TODAY func­tion and once cell below

Let’s select a range this time and press F5 (Fig. 20)

Fig. 20 Range selected
Fig. 20 Range selected

What we’re notic­ing is that the val­ue has been writ­ten only in the active cell, although we had select­ed the whole range. Then, Excel went one cell down (Fig. 21)

Fig. 21 TODAY function and one cell down
Fig. 21 TODAY func­tion and one cell down

Sum­ming up, there is a small dif­fer­ence between rel­a­tive and absolute ref­er­ences, but still, we have to con­sid­er whether we want to use the first one or the second. 

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

Excel VBA — Where is my macro | Introduction to macros and VBA 04

In this post, we will see where our macro is saved.

Excel VBA — Where is my macro | Intro­duc­tion to macros and VBA 04

Let’s go to the Devel­op­er tab. On the left, we can see the Visu­al Basic com­mand. We can also press the Alt + 11 short­cut. So, let’s click it. Now, in the worst case sce­nario, all we would see is a grey win­dow. In this case we need some more infor­ma­tion. Let’s turn on a small­er win­dow by open­ing the View menu, and select the Project Explor­er com­mand (Fig. 1)

Fig. 1 Project Explor­er command

What we should see here is all Excel files that are opened, as well as our PERSONAL.XLSB files, which are our per­son­al files for macros (Fig. 2)

Fig. 2 Files

In each file, we should also see some work­sheets and sim­i­lar things, espe­cial­ly mod­ules. Our code should be writ­ten as Mod­ule 1 by default. Before we click on it, let’s go to the View tab one more time and click on the Prop­er­ties Win­dow com­mand (Fig. 3)

Fig. 3 Prop­er­ties Window

This win­dow shows us infor­ma­tion about files or work­sheets (Fig. 4)

Fig. 4 Work­sheet information

Let’s go now to Mod­ule 1. After dou­ble-click­ing on it, we should have a small win­dow opened. Our first macro was very sim­ple, how­ev­er we have more rows than we need. We don’t need the Option Explic­it line, so let’s delete it. Next, we have the Sub key word which starts our macro and the phrase End Sub which stops it. Only the ele­ments that are between those two lines cre­ate our macro. Our macro is called TODAY. The oth­er time I men­tioned that the TODAY func­tion is a DZIŚ func­tion in Pol­ish, how­ev­er no mat­ter what ver­sion I have on my com­put­er, VBA is Amer­i­can and will use Amer­i­can names and pro­ce­dures in macros. When we look at our macro, we can see the line with the TODAY func­tion, as well as a few green lines start­ing with green sin­gle quo­ta­tion marks. They are just com­ments. In VBA, com­ments start with a sin­gle quo­ta­tion mark, and are not part of the VBA code (Fig. 5)

Fig. 5 VBA code with comments

Hav­ing the above in mind, I can just delete them and leave only the line that is an actu­al VBA code. Now, it’s quite sim­ple when you read it. ‘Selec­tion’ means that we just select­ed some­thing, a dot means the next step after select­ing. Then, we have the For­mu­la, which means that we put a for­mu­la. The R1C1 isn’t impor­tant for us. Then, our for­mu­la equals the TODAY func­tion. That is the whole VBA code (Fig. 6)

This image has an empty alt attribute; its file name is fig.-6-1024x293.png
Fig. 6 An actu­al VBA code

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

Excel VBA — Recording the first macro | Introduction to macros and VBA 03

With this post, we will record our first macro!

Excel VBA — Record­ing first macro | Intro­duc­tion to macros and VBA 03

First of all, we want to open a file with macros. What we have here is a piece of infor­ma­tion that says ‘Mar­cos have been dis­abled’. You need to click the Enable Con­tent but­ton (Fig. 1)

Information concerning macros
Fig. 1 Infor­ma­tion con­cern­ing macros

If you don’t want to click the Enable Con­tent but­ton con­stant­ly, like me, go to the Devel­op­er tab, then click on the Macro Secu­ri­ty com­mand, then the Enable VBA macros radio but­ton (Fig. 2)

Enable VBA macros command
Fig. 2 Enable VBA macros command

We can also go to the Trust­ed Loca­tion part and press OK. It will also work for all files and help us with too many noti­fi­ca­tions or mes­sages (Fig. 3)

Trusted Location
Fig. 3 Trust­ed Location

Today, we want to record very sim­ple macro. We just want to write the TODAY func­tion in cell A2. This cell should be select­ed before we click the Record Macro com­mand. Since the cell is select­ed let’s write the equal sign, then TODAY, then click the Ctrl+Enter key com­bi­na­tion. It’s impor­tant to use this short­cut. That’s all we need in our first macro (Fig. 4)

 Ready function
Fig. 4 Ready function

Now, let’s delete this val­ue and then press the Record Macro. In the win­dow that has appeared, the first thing we do is choos­ing the macro name. We can use the name of our func­tion’s name. In most cas­es we should’t do that, how­ev­er our case is real­ly sim­ple. It’s only macro, not a VBA func­tion. Then, we choose whether we want a short­cut for our macro or not. If the box is emp­ty, it means that our macro has­n’t got a short­cut. If we write there a let­ter or a sign, this will mean that we have a short­cut. The Ctrl key is a default key for each short­cut. It also applies to the Shift key. That’s why, let’s hold the Shift key and press D at the same time. I’m using D, as ‘today’ is ‘dziś’ in Pol­ish. If we don’t want any short­cuts, we just press the Back­space but­ton. Next, we have the loca­tion of our macro. We have a choice of Per­son­al Macro Work­book, New Work­book or This Work­book. Let’s choose the Per­son­al Macro Work­book, as it’s the most sig­nif­i­cant one. We can also add a descrip­tion in the next part, how­ev­er, in most cas­es the name should be enough to tell us what this macro does. We have already cho­sen the name, short­cut and stor­age place, so we can press OK. You need to be care­ful when press­ing OK, because that’s the point when the record­ing process starts. It means that each action you do in this work­sheet will be record­ed (Fig. 5)

Record Macro window
Fig. 5 Record Macro window

Now, our Record Macro com­mand changed into the Stop Recording com­mand. The but­ton in the bot­tom left cor­ner of the sta­tus bar also changed. It means that we don’t need to go to the Devel­op­er tab to stop the record­ing (Fig. 6)

Stop Recording button
Fig. 6 Stop Record­ing button

Let’s write =TODAY and press Ctrl + Enter again. Then press the Stop Record­ing but­ton. It’s cru­cial to press this but­ton so that Excel does­n’t record any unnec­es­sary code. 

Now, since we added a short­cut to our macro, let’s use it in cell C2 by press­ing the Ctrl+Shift+D short­cut. Some­thing actu­al­ly appeared in the cell, but we need to change the for­mat­ting to get the date (Fig. 7)

Formatting modification
Fig. 7 For­mat­ting modification

Now, we can see that we have our date, wher­ev­er we use the short­cut (Fig. 8)

Dates
Fig. 8 Dates

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

Excel VBA — Preparation before recording first macro | Introduction to macros and VBA 02

In this post, we will work on some­thing we should con­sid­er before start­ing record­ing our first macro. 

Excel VBA — Prepa­ra­tion before record­ing first macro | Intro­duc­tion to macros and VBA 01

First of all, in the Devel­op­er tab, we have the Record Mar­co com­mand. We can also find this com­mand in the sta­tus bar on the left side. It make it eas­i­er to start our record­ing (Fig. 1)

 Record Macro command in the status bar
Fig. 1 Record Macro com­mand in the sta­tus bar

Then, we should con­sid­er rel­a­tive and absolute ref­er­ence. We have the Use Rel­a­tive Ref­er­ences com­mand. If it isn’t high­light­ed, it means that we work with absolute ref­er­ences. For exam­ple, if I select cell B9, then select cell B12, Excel will record this as ‘select cell B12′. When I turn on the Use Rel­a­tive Ref­er­ence com­mand, then select cell B9, and then cell B12, Excel will record this step as ‘go three rows down’. This issue is very impor­tant when record­ing our macro. 

Let’s click on the com­mand one more time to turn it off and work with absolute reference. 

There is one more com­mand to look into. It’s called Macro Secu­ri­ty. After click­ing it, we have a Trust Cen­ter win­dow. In the Mar­co Set­tings area, we can see an option of Dis­able VBA macros with noti­fi­ca­tions (Fig. 2)

Disable VBA macros with notifications option
Fig. 2 Dis­able VBA macros with noti­fi­ca­tions option

This option should be select­ed by default. It means that when we open a file with macros (with an xmls exten­sion), we will have infor­ma­tion that this file con­tains macro, and some code can be run with­out our knowl­edge. How­ev­er, in most cas­es, we should­n’t be afraid of this code. It’s a code which we copy from secure sources, like Google. Google is quite a secure source, as it shows you good web­sites, not dan­ger­ous ones. 

When you start work­ing with VBA, you should know some­thing about the VBA code. If you see some­thing unfa­mil­iar, you should­n’t run this code. 

I per­son­al­ly don’t like noti­fi­ca­tions, espe­cial­ly the ones that show up every time I open a file con­tain­ing macros. That’s why, in most cas­es I’m work­ing with the Enable VBA macros option. It’s not rec­om­mend­ed, how­ev­er, from my point of view it’s prac­ti­cal. I’ve been work­ing with macros for many years, and I haven’t come across any dan­ger­ous code. The most dan­ger­ous thing a VBA code has done to me is rewrit­ing my cur­rent data. 

Hav­ing the above in mind, when you run macros and you don’t ful­ly know what they are doing, you should always cre­ate a back­up copy before.

The above are the most sig­nif­i­cant pieces of infor­ma­tion you should con­sid­er before record­ing your first macro. In the next post, we will be record­ing our first macro. 

https://www.youtube.com/watch?v=L9TDg-vRC48

Excel VBA — What is a macro and enabling the Developer tab | Introduction to macros and VBA

This post starts a series about an intro­duc­tion to macros and VBA code. 

Excel VBA — What is a macro and enabling the Devel­op­er tab 

What is a macro? In most cas­es it’s a syn­onym for VBA code that we run. It’s almost the same thing. Some­times we dif­fer­en­ti­ate it and we call macros the VBA code that was record­ed by Excel — Record Mar­co com­mand. If we want to use this com­mand, we should turn on the Devel­op­er tab. In order to do that, we have to right-click on our rib­bon and go the Cus­tomize the Rib­bon com­mand (Fig. 1)

Customize the Ribbon command
Fig. 1 Cus­tomize the Rib­bon command

In the Excel Options win­dow that has appeared, we have to find the Devel­op­er tab on the right side, press it and press OK (Fig. 2)

 Developer tab
Fig. 2 Devel­op­er tab

Now, we can see that in the Devel­op­er tab we have the Record Mar­co com­mand (Fig. 3)

 Record Macro command
Fig. 3 Record Macro command

This com­mand allows us to record actions we are doing on a work­sheet, such as cell for­mat­ting, putting val­ues in a cell, size mod­i­fi­ca­tion, etc. It’s a good place to start learn­ing about the VBA code. 

Macros are good for two main reasons. 

First, they are good for repeat­ing tasks. If we want to make reports on a dai­ly basis, we can just record all actions we are doing in a work­sheet and then we are able to use it again, prob­a­bly with tiny mod­i­fi­ca­tions. We will have a report for­mat by run­ning macro. 

Sec­ond, the VBA code allows us to do the things that are not pos­si­ble or very hard when using Excel func­tions. Those things will be much sim­pler using the VBA code. It may even hap­pen that some­body has already writ­ten a prop­er code to this task. 

If we want to save macro, we have to use the ‘xlsm’ exten­sion, where ‘m’ stands for macros. We can run macros on ‘xlsx’, which is a stan­dard exten­sion for Excel, how­ev­er Excel won’t save macros in those files. Remem­ber, only ‘xlsm’ works for macros. 

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

Extracting only letters from text

If I need to extract only let­ters from my text, what should I do? Lets find out.

Extract only let­ters from text 

When I need to extract only let­ters from my text, it means that I also need to extract spaces and enters (new lines). 

With Pow­er 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 com­mand in order to take our data to Pow­er Query (Fig. 1)

From Table/Range command
Fig. 1 From Table/Range command

Here, I have four sim­ple pieces of text. One in Eng­lish and three in Pol­ish. We need to go to the Add col­umn tab, then to the Cus­tom col­umn com­mand (Fig. 2)

Custom column command
Fig. 2 Cus­tom col­umn command

In the Cus­tom Col­umn win­dow, we have the space to name our col­umn. Let’s call it Clean, and below we have the Cus­tom col­umn for­mu­la box, where we write the Text.Select func­tion. In the func­tion we need to work with text, so let’s select the right col­umn (1). Then, I need let­ters. Since there will be string of char­ac­ters, I have to use curly brack­ets, where we write upper case let­ter in the first sequence, which is “A”..“Z”. Then let’s write low­er case let­ters, which is “a”..“z”. Writ­ing two dots between let­ters means that there is a sequence. Let’s close the curly brack­et and the paren­the­sis. Now, it should be fine (Fig 3)

Whole function
Fig. 3 Whole function

After press­ing OK, the sys­tem extract­ed for us only let­ters. How­ev­er, the text pre­sent­ed like this is just gib­ber­ish. That’s why we have to add spaces. To do that, we need to go to the View bar, then select the For­mu­la bar check­box and start mod­i­fy­ing our for­mu­la. Let’s write a com­ma and a space in dou­ble quotes (1). If we need oth­er 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)

Modifying the formula
Fig. 4 Mod­i­fy­ing the formula

How­ev­er, I don’t need dots in my text. What I real­ly need are enters. We can remove the dot from the for­mu­la and write #(lf) in its place. After enter­ing the for­mu­la, we can see that we have extract­ed enters, low­er case let­ters, spaces. But, I did­n’t extract Pol­ish let­ters. In row 4 there are strict­ly Pol­ish let­ters. In rows 2 and 3 there are some of them. The ques­tion is: how can I extract unusu­al signs from text using the Text.Select func­tion. First of all, we have to check the Uni­code. We can see that let­ters from A to Z is a sequence of num­bers from 65 to 90. It’s sim­i­lar in low­er case let­ters. The most impor­tant les­son from it is that we have a sequence (Fig. 5) 

Unicode
Fig. 5 Unicode

Now, let’s go back to our for­mu­la. If I want to have Pol­ish let­ters, I can write a sequence of “Ó”..“ż”. After enter­ing the for­mu­la, we have extract­ed also Pol­ish signs (Fig. 6)

 Extracting Polish signs
Fig. 6 Extract­ing Pol­ish signs

Now, that we have extract­ed all signs we need, we can go to the Home tab and click on the Close&Load to com­mand to trans­fer our results to Excel (Fig. 7)

Loading results to Excel
Fig. 7 Load­ing results to Excel

In the Import Data win­dow, we have to select a cell (Fig. 7)

Choosing the target cell
Fig. 7 Choos­ing the tar­get cell

As we can see, the text is writ­ten in one line, so we need to wrap it. Let’s go to the Home tab and click on the Wrap Text com­mand (Fig. 8)

Wrap Text command
Fig. 8 Wrap Text command

Now, we clear­ly see upper case let­ter, low­er case let­ter, spaces, enters. I removed signs that I did­n’t need (Fig. 9)

Final table
Fig. 9 Final table

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

Hiding scroll bars, formula bars and column headers

When we need more space while work­ing in Dash­board in Excel, we can just hide unnec­es­sary options. How? Let’s find out.

Hide scroll bars, for­mu­la bars, col­umn headers

The options we can hide are e.g. scroll bars, sheet tabs, for­mu­la bars as well as column/row head­ers. If we want to hide then we just go to the File menu and choose the Options com­mand (Fig. 1)

Options
Fig. 1 Options 

In the Excel Options win­dow, we choose the Advanced tag. If you pre­fer using short­cuts, you can press Alt + T + O, which also opens the win­dow. Lets go to the Dis­play area and uncheck the Show for­mu­la bar check­box. Remem­ber that it works for all Excel files (Fig.2 )

Show formula bar checkbox
Fig. 2 Show for­mu­la bar checkbox

Then, we can go to the Dis­play options for this work­book area. We have to uncheck the Show hor­i­zon­tal scroll bar, Show ver­ti­cal scroll bar, Show sheet tabs check­box­es (Fig. 3)

Three checkboxes
Fig. 3 Three checkboxes

We can even go to the Dis­play options for this work­sheet area where we can uncheck the Show row and col­umn head­ers check­box and press OK (Fig. 4)

Show row and column headers checkbox
Fig. 4 Show row and col­umn head­ers checkbox

We can also dou­ble-click on the Home tab to hide the rib­bon, which gives us even more space than before. 

Now, I can press Ctrl + Page Down to go to anoth­er work­book. Here, we can see that col­umn and row head­ers are vis­i­ble. How­ev­er, when we go back to our work­book by press­ing Ctrl + Page Up, we can see that there are no row or col­umn head­ers. That’s how you make more space in your Dashboard. 

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

Drop-down lists

How to add a drop-down list in Excel? It’s a real­ly sim­ple 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 Val­i­da­tion com­mand (4) (Fig. 1)

Preparing data
Fig. 1 Prepar­ing data

In the Data Val­i­da­tion win­dow, we have to select the List option in the Allow bar, then we’re select­ing the range and write it into the Source bar. Then OK (Fig. 3)

Data Validation window
Fig. 2 Data Val­i­da­tion window

Now, we have a drop-down list (Fig. 3)

A drop-down list
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 select­ed col­umn will still have the drop-down list. What’s more, in the newest Excel ver­sion, we have even Lookup options. We just have to start writ­ing the first let­ter of a word we’re look­ing for, then press the Tab key.

Lookup option
Fig. 4 Lookup option

This func­tion, how­ev­er, does­n’t work when we start from the mid­dle of our text. It works only with the first letter. 

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

KPI in Power Pivot Challenge 01

Today, we are going to address our first chal­lenge. The chal­lenge is to cre­ate KPI in the Pow­er Piv­ot bas­ing on an aver­age blood pres­sure. We want to cal­cu­late our aver­ages bas­ing on weeks, as it’s more com­pli­cat­ed that cal­cu­lat­ing month­ly averages :).

For indi­vid­u­als — deter­mine the aver­age month­ly ( for ambi­tious week­ly) upper blood pres­sure 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 Pow­er Piv­ot | Chal­lenge 01

We have our Piv­ot with one cell select­ed. Let’s go to the Pow­er Piv­ot tab (1), then to the Add to Data Mod­el com­mand (2) (Fig. 1)

Add do Data Model command
Fig. 1 Add do Data Mod­el command

We can change here our date for­mat into the one with­out time (Fig. 2)

 Changing the Date format
Fig. 2 Chang­ing the Date format

Now, we have to cre­ate an aver­age bas­ing on the Upper blood pres­sure col­umn. We basi­cal­ly select one cell in the cal­cu­la­tion area (1), then go to the Home tab (2), then choose the Aver­age com­mand (3) (Fig. 3)

Average command
Fig. 3 Aver­age command

The Pow­er Piv­ot did the cal­cu­la­tion auto­mat­i­cal­ly for us. I’ll just change the name so that it’s short­er. Let’s leave only ‘pres­sure’. Now, let’s change the for­mat into Dec­i­mal Num­ber (Fig. 4)

 Decimal number command
Fig. 4 Dec­i­mal num­ber command

When we have the aver­age with two dec­i­mal places, we can start cre­at­ing a week col­umn, because it’s hard to group by weeks in the Pow­er Piv­ot. Let’s call this col­umn Week­Num. Then we can use exact­ly the same func­tion, which is the WEEKNUM func­tion bas­ing on the Date col­umn. If we want our week to start on Mon­day, we have to write 2 (Fig. 5)

=WEEKNUM([Date],2)

Creating a WeekNum column
Fig. 5 Cre­at­ing a Week­Num column

Let’s press Enter, and we have our col­umn with week num­bers. All weeks start from Mon­days. For some peo­ple this solu­tion would be enough. How­ev­er, I pre­fer to have the start day and the end day of the week. Let’s cre­ate anoth­er col­umn. This time I’ll call it week­Text. Let’s write ‘Date’. Then, we have to sub­tract the num­ber of the week­day. To do so, we can use the WEEKDAY func­tion again bas­ing on the Date col­umn. Now, we have three options of num­ber­ing the week­days. 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. Mon­day will be the first day, but it has got zero. It means that we will start count­ing from zero which is exact­ly what we need (Fig. 6)

=WEEKDAY([Date],3)

Creating a weekText column
Fig. 6 Cre­at­ing a week­Text column

After press­ing Enter, we have our new col­umn with only Mon­days. In all sev­en rows of the week we have only Mon­days. Now, let’s add the last day of the week. Let’s add a minus sign with spaces as a delim­iter. It allows us to delete the time from our col­umn. Then, let’s copy our WEEKDAY for­mu­la and paste it after the delim­iter. Then, we need to write +6 days to have Sun­day (Fig. 7)

First and last days of the week
Fig. 7 First and last days of the week

We can see the results. If the date for­mat is prop­er for us, this is all. But, if we want to change the date for­mat, we should add the FORMAT func­tion to the first and sec­ond cal­cu­la­tion (Fig. 8)

Adding the FORMAT function
Fig. 8 Adding the FORMAT function

How­ev­er, the cur­rent form is OK for me, so we can cre­ate a Piv­ot Table based on our mea­sures and cal­cu­la­tions. Let’s click on the Piv­ot Table com­mand in the Home tab (Fig. 9)

 Creating a pivot table
Fig. 9 Cre­at­ing a piv­ot table

Let’s select the New Work­sheet radio but­ton (Fig. 10)

Create PivotTable window
Fig. 10 Cre­ate Piv­ot­Table window

If I start with the Date col­umn and go to Rows, most new­er ver­sions will group data auto­mat­i­cal­ly by months. Let’s try to group it by days (Fig. 11)

Group option
Fig. 11 Group option

We can see that in the Pow­er Piv­ot the num­ber of days is grayed out and we can­not use it (Fig. 12)

Days grayed out
Fig. 12 Days grayed out

That’s why we should crate addi­tion­al columns to address this prob­lem. I should use the week­Num or the week­Text col­umn in our Rows area. Let’s also add Per­son to the Columns area and the Grand total by going to the Design tab and click­ing on the Grand Totals com­mand. Let also add our mea­sure, which is the fx pres­sure into Val­ues (Fig. 13)

Creating additional columns
Fig. 13 Cre­at­ing addi­tion­al columns

Now, we can see aver­age blood pres­sure for each per­son and each week. We also have the grand total for all peo­ple in a week. It’s time to add KPI to our piv­ot table. Let’s go to the Pow­er Piv­ot tab (1), then to the KPIs com­mand (2) and choose the New KPI option (3) (Fig. 14)

Creating KPI
Fig. 14 Cre­at­ing KPI

In the Key Per­for­mance Indi­ca­tor (KPI) win­dow, in the KPI base field val­ue, we have only pres­sure to choose from, so let’s leave it. In the Absolute Val­ue bar let’s write 120. In the Define Sta­tus Thresh­old graph­ics we should write our assump­tions, then select an icon style (Fig. 15)

Key Performance Indicator (KPI) window
Fig. 15 Key Per­for­mance Indi­ca­tor (KPI) window

Now, my mea­sures have changed into KPIs (Fig. 16)

KPIs
Fig. 16 KPIs

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

 Switching on icons
Fig. 17 Switch­ing on icons

Now, we can change the Pres­sure Sta­tus head­er name into KPI. We can also align the icons using the Cen­ter option in the Align­ment area (Fig. 18)

Center option
Fig. 18 Cen­ter option

We can see our aver­age week­ly mea­sure and KPIs cor­re­spond­ing to that mea­sure rep­re­sent­ed by icons. 

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

2D Lookup — INDEX and MATCH or XLOOKUP functions

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

2D Lookup | Excel Tips 68

When we want to find an inter­sec­tion between a row and a col­umn bas­ing on head­ers, we can do it in two dif­fer­ent ways. The first one will be in Lega­cy Excel, and the sec­ond in Dynam­ic Array Excel.

The first solu­tion uses the MATCH func­tion as well as its close friend, the INDEX function. 

Let’s start with look­ing for the coun­try. In this case we want to find the row num­ber. Let’s write the cell, the range where we will be look­ing for and 0 for an exact match (Fig. 1)

=MATCH(B8,A3:A6,0)

MATCH function for countries
Fig. 1 MATCH func­tion for countries

We also want to find the col­umn num­ber. In this case we also use the MATCH func­tion. Let’s the cell, the range where we will be look­ing for and 0 for an exact match (Fig. 2)

=MATCH(B9,B2:G2,0)

MATCH function for months
Fig. 2 MATCH func­tion for months

When we have our row and col­umn num­bers, we can use the INDEX func­tion, select the whole dataset, then write the row and col­umn num­bers (Fig. 3)

=INDEX(B3:G6,D8,D9)

INDEX function
Fig. 3 INDEX function

Just like that, we did the 2D lookup, or we have found the val­ues we want­ed (Fig. 4)

Results
Fig. 4 Results

So far, we have had three for­mu­las. If we want to have only one, we need to imple­ment some mod­i­fi­ca­tions. We have to copy our MATCH func­tion and paste it in the place refer­ring to cell D8. Then, we have to copy the sec­ond MATCH func­tion with­out the equal sign, and paste it in the place of the ref­er­ence to cell D9. Now, we have one, big­ger for­mu­la instead of three small­er ones (Fig. 5)

=INDEX(B3:G6,MATCH(B8,A3:A6,0),MATCH(B9,B2:G2,0))

One, big formula
Fig. 5 One, big formula

Now, let’s focus on the solu­tion in the Array Excel. We will be using here the XLOOKUP func­tion twice. 

First, we are look­ing for a row in the coun­try head­ers. Then, as a return­ing array, we give Excel the whole dataset (Fig. 6)

=XLOOKUP(B8,A3:A6,B3:G6)

 XLOOKUP function
Fig. 6 XLOOKUP function

Since I’m using the XLOOKUP func­tion, and we are work­ing in the Array Excel, Excel will return the whole row. 

In such a case, we have to find a prop­er col­umn. We can also do it with the XLOOKUP func­tion. Let’s write the XLOOKUP func­tion once again. This time we will be look­ing for a month in the month head­ers. As a return array, we have the row we found pre­vi­ous­ly (Fig. 7)

=XLOOKUP(B9,B2:G2,XLOOKUP(B8,A3:A6,B3:G6))

Another XLOOKUP function
Fig. 7 Anoth­er XLOOKUP function 

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

Results
Fig. 8 Results

Even if we change the val­ues in our for­mu­la, the result is still cor­rect (Fig. 9)

Checking the formula
Fig. 9 Check­ing the formula

The results are cor­rect both in XLOOKUPs and INDEX with MATCH functions. 

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