 So, hello everybody and welcome to our webinar, our Dibranh Consulting Power BI and Excel monthly webinar. Sorry for starting today late. We had some technical issues. We thought we could use a new computer and apparently the browser doesn't work. The new browser doesn't work with our setup. I should have checked that out. Anyway, so just to check that everyone is here. Could you just say hello on the chat? Just say hello and then we start. I want everyone to just say hello hello and then we start on the chat. So today we're going to talk about conditional formatting. It's quite a cool stuff. So let me go through what we're going to see, what we're going to see in conditional formatting. So the first thing, of course, okay, I'll just introduce myself. Not everybody knows who I am. So I'm David, David Brown. I'm the principal consultant, managing partner of Dibranh Consulting. I've been using Excel now for like 22 years, really using Excel for a long time. I'm also a master trainer and master instructional designer. And I've also worked with, okay, so I've also I'm a master trainer, master instructional designer. I've trained thousands of people on various uses of Excel and Power BI. And I'm also a chartered accountant. So that's all about me. So let's quickly move. So about us is Dibranh Consulting sponsoring this webinar. So Dibranh Consulting, we do training, consulting and payroll. And the training, we're just the analyst hub. So some of you have already gone to our online e-learning platform that is on officetraininghub.com. So that's pretty cool. If you check that out, officetraininghub.com. I have something, a gift for you at the end of this webinar. So just stick around to the end to get that gift. And we have affiliations with Microsoft, we're Microsoft partners. We have affiliations with Financial Modeling Institute and ATD. Association for Talent Development. Those are some of the affiliations we have. Right. So courses we do, we do classroom courses as well as online. As I said, we concentrate on the analyst. So we have financial modeling courses, Office 365 courses, financial analysis courses, business intelligence and Excel and BI courses, business intelligence and Excel courses. Great. So what are we doing today? Well, today we're going to talk about Excel and formats. It's mainly formatting. We're going to talk about formatting in Excel. What's it all about and how do we do it? So formatting in Excel, formatting is a really interesting topic. And in fact, there's one single sentence I'm going to use to explain formatting in Excel. Right. So watch out this sentence. I'm sure a lot of you already know this sentence. A format can never change what is in a cell. It only changes what you see. And what you see is not necessarily what is in the cell. So I'll repeat that. A format can never change what is in a cell. It only changes what you see. And what you see is not necessarily what is in the cell. So I've put up a poll for everybody. is not necessarily what is in the cell. So I've put up a poll for everybody here. I would like everyone to tell me who knows about formatting in Excel. How well do you know formatting in Excel? Could you answer that poll question? And then we continue after you answer. How well do you know formatting? Do you know the basic stuff? Do you know quite a lot about formatting? Are you a pro? Or you don't really know anything about formatting? So just answer the poll and we'll move on. Okay, I can see that. Quite a few of us were pretty good. Well, we know the basics and a few of us know something called formatting code. If I have some time, I'll show you a little bit about that. What formatting code means, very interesting topic, okay? Right, I'm gonna end the poll now. 80% of us know the basic stuffs in formatting, which is good, not bad, it's not bad at all. 80% of us know the basics. So end that poll, let's move that to next. So keep on on the chat. Just any questions you have, please just type it in the chat immediately. Just type in the chat, any questions you have for me, right? We're gonna jump into the demo very shortly. So we're gonna do an introduction to conditional formatting in Excel. What exactly is conditional formatting and how can we use it efficiently? Then I'll show you some five hot tips, very cool hot tips in conditional formatting in both Excel and also in Power BI. So Excel and formats. As I said, if it's not in Excel, it is not Excel. That's one statement we do about Excel. If you want to understand Excel with one sentence, that is the sentence. If it's not in Excel, it is not Excel, right? So I'll go into the demo shortly, Excel and Formats. The next thing we'll do, we're gonna do a small introduction to conditional formatting in Excel. And once we do an intro to conditional formatting in Excel, I'm going to take you through the five hot tips for conditional formatting in Excel. So let's jump into Excel now. I'll just quickly jump into the application and so we can start off with some of the demos. I have some data. So I have region, market, store, trade date, fiscal period and stuff like that. And I want to do some conditional format. Let's see, what's a good candidate? Let's say candidate for my conditional format is, I want to format my revenue. I just want to go through and just format my revenue by the amount of revenue. The highest should be green and then the lowest should be red, something like that. So once you have a table, by the way, if you have a table, it should follow seven golden rules. If you don't know what that is, go to our YouTube channel and check for seven golden rules of beta. You'll see that there. So to use conditional formats, we go to home. If I go to home here, I double click home so that this can come up, right? Then if you look at to your right, you'll see clipboard, you see this is clipboard, font, alignment, number, styles, cells, and editing. I'm using Excel 2016, but it's exactly the same for Excel 2010, 2013, 2016. I'm actually using Office 365. If you want to know what version of Excel you're using, you go to file, you go to file, you click on account. You click on account, you will see the version you're using. I think so the version I'm using is Office 365 ProPlus. ProPlus is really cool because that's the one that has power pivots. ProPlus, yep, okay. So back here, so under home, you have conditional formatting under the styles section. So if I click on conditional formatting and you have your highlight cell rules, this is where you now do conditional formatting. There are some preset rules here already, some of them just walk out of the box, yeah? Highlight cell rules, you have your top and bottom rules, you have your data bars, you have your color scale, you have your icon sets. And then if you want to go very creative, you can use formulas to do your conditional formatting when you go to manage rules or create a new rule if you want to use formulas. So these are already preset. So how do I use them? So highlight cell rules, you can say greater than a certain number, right? So let me just check. Before I do that, I need to kind of highlight what I want to conditionally format. So let's highlight this. I then come here and say, hey, conditional format, I want you to highlight maybe greater than, let me see, greater than, let's just say greater than 7 million or something, 7,000,000,000. Something just give me highlights of greater than 7 million. Can you see the highlights? You see it's just highlighting two cells already, yeah? So that's conditional format. When I click okay, when my data changes, let's assume something here is quite a large number. It'll just color it, right? So that's conditional format. That's how it works. At least the basic form of conditional format. Highlight what you want to conditionally format and then you format it. So basics, that's fine. So let's see our demo. What should we do? What's our first demo for conditional format? Well, our first demo, let me show you. Let's quickly jump to the slides. So our first demo is, for the five hot tips, is data entry. So I'm going to demo data entry. We're going to do a conditional format for data entry. So someone kind of knows, okay, I haven't entered data here. This place is gray. Anywhere that's gray, we need to enter data. And then when we enter the data, the gray conditional format or the gray format just disappears. So that's the first demo we're going to do. So let's have a look at that. Let's see how that works. All right, so I'm back to Excel. I'm back to Excel. So I'll go to data entry. But explaining our data for starters, this is the data we're using. So the data, and how many of you want this file? Just shout out on the chat, say yes or no. Do you want this file? Should I share it? Maybe before the end of the webinar. This is our data. And if you look at data, I have a start date here. If I have a start date, I can change my end date. Let me change my end dates to maybe 25, that's May, May 28. So once I enter, you'll see that my data is changing because the formulas and everything working pretty nicely. By the way, if you want to learn how this works, you can go to our YouTube channel. We have something there. So here we're going to do data entry. So what I want to do, just like you have in my report here, I put a date, I put a start date, I put an end date on my report changes. I want to be able to put some data entry here. Put a start date, let me put a start date. Let me see, I'll just try and replicate this. First of January, 2015. So I'm going to say first of January, 2015. And then maybe 31st of May, 2015. I figure in the US, you guys do month first. So in Europe and a lot of parts of the world, we do day first. So my format, my system is set up for day first. And then here is my store. If I come to store, let me pick a store name. If I go to my control, let's say abadji is a store. So let's say abadji. Guess what? So now, so this is like a data extractor. I put in my data entries and then my figures come out. So the thing is we want to be able to let people know that these are data entries. So we're going to highlight these three and tell Excel a conditional format that if there is nothing in here, format it gray. So we come to conditional format. I've highlighted my cells. I'll say highlight cell rule. Is there anything for that? Not really. We need to go to new rule. So we go to new rule. It brings up a set of rules, table for rules. And then under new rule, we want to tell it to do something when it's blank. So we go to format cells. We go to the second one. You have format cells based on their values. You have format cells based on what they contain, what the cell contains. Format only top or bottom cells. Format only values that are above or below average. Format only unique or duplicate values. And then you have user formula. So here we need to go to format only cells that contain something. And right now we want it to contain nothing. So here we have cell value is between, really cell value is not between anything. What we want is under cell value, we want blanks. Can you see blanks? So we have cell value, specific text, occurring, blank, no blanks, errors, no errors. So blanks, we just want blanks. Format only cells with blanks. Then under preview, we say format. So we go to format. And if the cell is blank, I want you to go to the fill and maybe let's say color gray or something like that. Let's say color gray. So conditional format is based on a condition. What's my condition? My condition is that the cell is blank. And if the cell is blank, please make it gray. So click okay. And that's what happens. So now when you type in a date, let's say one January, 2015, the blank goes off, it's not blank anymore. I go to 31st December, 2015, and it's not blank anymore. And then here for my store, I can remember the name of the store. Now for this, it's better to do a validation. So let's quickly do a validation. We may not have too much time, but let's rush it. We're gonna put a validation. So people don't have to remember what the name of the store is. We have a dropdown validation. So I go to data for that. And then I go to my data validation button here. Then I click on allow. I'm not allowing any value right now to allow a list. And on the list, I go to the source. What's the source of my list? Well, on my control sheet, I listed out some stores. So I'm just gonna highlight these stores. Contra ship down arrow key, and I've already called it L store. So I click okay, and now I have a dropdown. When I pick on the dropdown, my data works. So this is a nice tool. So that's all for this. Very simple, very straightforward. Let's go to our next demo, yeah? Do you understand this demo? If you like this demo, put your thumbs up. So we can now go to our next demo. All right? So what's our next demo? Let's look at it. So data entry was the first demo. We did that, and we even did some data validations. So our next demo is highlighting alternating rows. Highlighting alternate, or highlights for alternating rows. So sometimes when you have large data, it makes a lot of sense to kind of put some shadings on alternate rows. How do you do that? Now anyone that knows Excel very well, we've used the table tool where this is also using just Excel, want to make it alternate in rows in just Excel, controlling how the rows are alternating. So let's go back to the demo and see how that works. So you should see the demo on my screen right now. You should see the demo file. So we go to alternating rows. So again, we're going with our same theme, start date, end date, and this is the data. But what I want to do is this. I want this data to just maybe shade this. This is how you do it manually. Now if you had very large data sets, it's gonna be difficult tedious to shade it like this. Then I come here and shade it like this. And then come here and shade it like this. I want you to do it automatically. Now, how do we do that? But to do that automatically, can imagine if you had a thousand rows, you can be doing it manually. That's pretty inefficient. So what you need to do is some small mathematics. Let's think about it. I want to shade this, maybe this row, then leave this one, then shade this row, then leave this one, then shade this row. So I'm shading an alternating row. So the first step really in this process is, okay, what row am I in? So I'm gonna do some formulas to the right here. What row am I in? This is row six. So I want row six not to be shaded. Then I want to now shade row seven, right? So I want to shade row seven, row eight, row nine. Okay, so the one that I want to shade is I want to shade this row. How do I identify this row? If you think about it, row seven, and what's the difference between row seven and six? Well, row six is an even number. Row seven is an odd number. Row eight is an even number. Row nine is an odd number. So we found a way to kind of differentiate the two. So one way to identify whether you're an even number or odd number, well, you could say equals to even, right? Or really the best way to, if you say equals to even, by the way, that's a function that says this is six. Well, six is an even and that's the drop down. That's counter D. Well, the next closest even number to seven is eight. Then eight, the next closest is 10, 10. This wouldn't really help us. So there's a function called mode, very cool function. How many of us have used mode? Can you tell me in the chat, how many of you have used a function called mode? Can you type in the chat, have you used mode before? Let's move on. So here we're gonna use mode. So the mode function is a cool function. It says it returns the remainder after it's a number is divided by a divisor. Long story. One thing about Excel, right? The way they explain stuff is so technical, isn't it? It's too confusing. Now, there's a new, there are new functions. There's mode, mult and mode, mode, sing, SNG. These are new functions to Excel. If you don't have, I think if you have Excel 20, 10 or 13, you won't have these functions, right? So this is a mode, mode. Anyway, but let's say mode, M-O-D, returns a remainder after a number is divided by divisor. Well, let's just do it. So I tab and you have two syntax has two arguments, number and divisor. So let's click on this and say this is the number. We'll put a comma and put a divisor of two. Let's just say your divisor is two, right? So if six divided by two is what? Three, right? Six divided by two. So if I enter, look at this, the answer, my mode F62 is giving me an answer of zero because there is no remainder. But if you divide seven by two, if I copy this formula, in fact, copy this formula down, right? If I drag this formula down, you see you have one. Can you see that? Then if I drag it down more, let's continue, you see you have one, one, one, right? Now, if I make it three, if instead of my divisor being three, if my divisor is three, you would see that your remainder is one, then there's two, then there's one, then there's two. So really, so if I want to say, look every other row, if I copy this formula down, you can see zero, one, zero, one. Now, now that I have identified, uniquely identified the rows that I want to color, I can now use this formula inside my conditional format. So to do that, I highlight my data this way, exactly what I want, for example, I highlighted this way. Once I highlight, I go to conditional formatting and this time around, I'm going to a new rule, new rule. But under new rule, I can't use any of these options up here. I need to use the last one called user formula to determine which cells to format. Now, under format values where this formula is true, so what do we want? We want it to be true to color those cells that, or shade those cells that we wanted to shade and those alternative cells. And we're using modes to differentiate it. Now, when you are in conditional formatting and you're typing a formula, note that you're not typing a formula inside a cell. But you know our golden rule, if it's not in a cell, in Excel, it is not Excel. So even though you're typing a formula inside here, that's not a cell. It's true Excel and ask yourself, what is the active cell in this spreadsheet right now? What's my active cell? Can someone type it into the chat? What is my active cell here? Just type it quickly into the chat. So yeah, my active cell is cell B6. So if you look at it, if I go to my active cell, it is B6. You see it up here in the name box, so you're gonna say in here, you're gonna say equals to B6. Yeah? So well, not B6 really. B6 is the active cell, but the cell that we want to check is a function, based on a function, so I'm saying mode. I have to actually say mode. So mode and then it has number and divisor. Now, if you remember, the number is going to be our row construct. Just say the row, whatever row you are in. So then function we're putting there is row, open and close brackets. So row. So mode, what row are we in? And whatever row we are in, we want to divide it by two. Now, if you notice B6 is here, this is B6, right? So B6 divided by two and not give me a remainder. So the one that will give me a remainder is when it's equal to one. So when we divide your row that you are in by two, and if that answer is equal to one, then I want you to format and shade, fill it in with a gray, all right? So see, it's not simple really. We're saying, hey, give me the mode row, whatever the row is, comma two equals to one. So click, okay. And you see that it just shades. It shades perfectly, shades this, shades this, shades this, and that works perfectly, yeah? Make sense, guys? All right, super. Let's move to our next demo. This one, we're done with this demo. It's a pretty straightforward demo. Nice, interesting demo. Any questions, just type it into the chat. We're gonna move to the next demo. So what's our next demo? So our next demo is, we're going to do data privacy. Data privacy. So if you build financial model, say yes, I do or no, you don't. One thing is, we have a financial modeling webinar starting at 11 today, 11 o'clock. So someone is going to, colleague of mine is going to drop the link. So they're just going to drop the link. So if you click on that link, you'll be able to register for the next demo which starts at 11, which is for financial modeling. And for any talent development person or HR person here, there's also a demo starting at two o'clock on talent development, very interesting demo. By the way, guys, we do these webinars every third Thursday of the month. Every third Thursday of the month, we do these webinars. So let's do the next demo. The next demo is a very cool one. It's called data privacy. How do we manage data privacy in Excel? So let's look at that. So again, back to our demo sheet, let's go there. So we're going back to our Excel. And so if I go to these privacy button here, let's go here, privacy. So we have the same theme. And what we want to do is, the only time someone will be able to see this sheet is when they type in a password. So if I go to my control sheet, I want someone to be able to come here and type a password in here. So this cell, we're gonna make it a password cell. So in fact, let me quickly do our blank trick. That's our conditional formatting trick. Yeah, so let me come here and say, this is blank. And if it's blank, please shade it. Maybe green, okay. So if I delete this cell now, you can see it's green. You remember we just did that. So let's agree that our password is password, a very terrible password, right? Initial Caps password is our password, yeah? So if someone types password here, he should be able to see this sheet. If he doesn't type password, he shouldn't see anything in that sheet. How do we do that? So to do that is a nice cool trick. Firstly, we need to understand that we're going to protect, we need to protect the sheet. So once we protect this sheet, because all we want them to do is be able to just see, maybe just see this data and maybe type some few things in here. Just be able to type a few things in here. So for these two cells to be able to type in there, let's make these cells, let's not allow protection to protect these cells. We're not gonna protect these two cells. We're gonna protect the entire sheet, except these two cells. So to do that, you highlight the cells you do not want to protect. And then you go to format cells. Format cells is control one, that's a shortcut. Or you can right click, let me just do the old school way, right? Right click. Okay, so let me right click. My right click button is not working too well today. Oops, something is, so I'm just gonna do my shortcut. I already know the shortcut control one. So control one shortcut, the thing is you go to the far right, you see protection, and then you say, hey, don't lock these cells when I protect this sheet. Don't lock it. So click okay, you remove that lock and say, okay. Then we're not gonna protect the entire sheet. To do that, we go to review, and then we say protect sheet, right? Protect sheet, we're not gonna put a password for now. Let's just leave it. And we say, okay. So what this protect sheet has done is I can't type anywhere. See that I can't type anything anywhere. Can't type. So, and then, but I can come here and change this to anything I want. I can change this to, I can actually type inside this one. It allows me to type here, okay? So for 1, 3, 2015, oh, there's no 1, 3, no revenue there. 1, 3, 2015, obviously, because this is probably March. I can't start something in March and end in April. That doesn't make sense, does it? Because this is not, by the way, to make this look better, let me give you a shortcut. Shortcut is control shift. Just to make it look much better is control hash. So control hash on some keyboards, control hash is on, what is it that is on? So it's like control shift three. So you should be able to do that. Let me see what keyboard my settings, what are my keyboard settings on this computer? My keyboard settings are funny, funny set of settings on the computer. So, if I could to do what I want, decides not, and rather work for me, unfortunately, let's get it to work. Let's hold on. Now it's not allow me to format because it's formatted, protected. So if I unprotect the sheet, let me unprotect, and then highlight this too, and then I do my conditional format, which is control hash. Control hash is the conditional format. Control plus your hash key. Hash key, now that's the conditional format, control hash. So here I'll say one, John or let's say three, John 2015, and it works perfect. Okay, now everything is fine, but I don't want people, when I protect this sheet with my conditional format, I don't want people to type, people are typing, but I want to also protect it is here. I want to call, and then what you do is you highlight the entire sheet. Once you highlight the entire sheet, you're gonna put a conditional format that paints the whole thing black if they don't see the password, right? So if you don't see a password in here with the word password, it's gonna make it all black. And what I advise you do is, you come into this cell, and maybe you can name this cell, but I will leave that for now. Let's come into here. We highlight the entire sheet. By the way, password is control A to highlight the entire sheet, control AA. Then we go to home, we got a conditional format, click on conditional format, and we're going to do a new rule. And the new rule says, under the formula, you say equals to, yeah, the only equals to, when I want to color it black, and the only time that is not gonna be black is if the person puts the password, then it's going to remove it from being black. So if the only time that it will not be black is if the password is wrong. So what I'm gonna say is equals to, this cell under control, I go to this cell. When this cell is not equal to, now not equal to in Excel is this. I mean, close bracket, I mean, greater than or less than. So less than and greater than. That's how you write not equal to. You put less than and greater than. So it's not equal to what? Password, right? So you type that in, type password. Remember it was an initial cap. But this is the code. You say this cell is not equal to password. Yeah? Or for those that are really cool in Excel, you could just say not this cell. Well, let me not go to not. Let's just leave it like that. Let me not confuse you guys. This control is not equal to password. Now, if it's not equal to password, go to format and let's fill the entire thing black. I'm gonna fill the entire sheet, fill it out black. Yeah? Not equal to password. Click okay. So if I click okay to this, see what's going to happen. Let's see. The whole thing is black. But look at this. Can you see some text that's showing? We don't want anything to show. I don't want text to show. So we go back to the conditional format and we manage rules. And we come here and we edit this rule. So we're editing it. So use formulas. We say, okay, let's edit this rule. And not only that, also, I don't want the text to show. So one way you could do is come to font and maybe make the font black as well. Yeah, you can make the font black. Yeah, so black on black one show, right? But I kind of like a trick when you go to number, right? And then go to custom. So under number, you go to custom. And under custom formatting, under general, you put the special disappear format called semicolon, semicolon, semicolon. Three semicolons under custom format will not show anything in all the cells. All the cells will show anything. So if I click okay and say okay, and then apply, you'll see that nothing shows. But there's a problem still. Yeah, that's still a problem. If you go into the cell, let's now protect the sheet. If I protect the sheet, view, review, protect sheet, no passwords, say okay. Now, be looking at your formula but look at your formula bar here. If you come in there, can you see, you can see the formula. Someone can see the formulas. Yeah, you can see the formulas. And really, I don't want that. In fact, I don't want him to be able to see anything, nothing really. You shouldn't be able to see the formulas. Now, the way you do that is let's on protect the sheet. If you don't want people to see your formulas, you highlight the entire sheet. Let me remove, let me go back to control. Let's remove this black. So if I come here and say password, right? Come to privacy, everything is back. Can you see that? Everything is back. If I come to control and put the wrong password, come to privacy, okay, all black. Come here and let's put the correct password. Password, then we come here, it's back. Now, if you don't want to show the formulas, think we've protected the sheet. What you do is you highlight everywhere, you don't want to show formulas. Let's just highlight the entire sheet, yeah? And then you go to control format cells. Now under your protection, not just locked, let's also say hidden. Hidden means that when someone clicks on the cell, they will not see the formula. That's when you protect the sheet. Right now they can see it is fine, but when you protect the sheet, they won't see the formula. So let's protect the sheet again. How do you protect it? Review, protect sheet. Now you can put a password here as well, but I'm not going to do that, yeah? If I click okay, you'll notice that we can't see the formula anymore, right? So we've protected our sheet, that's one step. And as well as we've put a password cell. So if I delete this password, guess what, this is a very nicely locked sheet. One, they can't see anything, they click, they can't see anything in the formula bar. Two, the whole thing is black. Let me see if I have some time to add one little nice twist to it. So I'm going to add a little twist to this. And what do I do when I build models, right? Let me put a password, password. I kind of do a small trick as well. I mean, unprotect, unprotect. So I also include maybe a text box. I'm going to insert a text box. And this text box, I'll just draw it here. And I'll say, please, I'll just say you access denied. Let me just say access denied. Enter password. And when I say enter password, this cell here, I'm going to hyperlink it. So this box, I'm going to hyperlink it, control K. Control K is hyperlink guys, control K. I'm going to hyperlink it to a place in this document. I'm going to hyperlink it to the control sheet. And I'll say, under my screen tip, I'll say, click to enter password. Then I say, okay. Then I say, okay. So this whole box is like an access box. It gives people access to it. Now, I'm going to color this white. Guess what? Since I'm white here right now, if I color this white, you won't see it, right? You won't see it. And I'm going to make the borders of the no outline for the borders, right? So right now you can't see that. You can't really see it. But the way you see it is when I delete this and I come to privacy, you will, I didn't make the text white, error in programming. Let's check. And put a password, thank you back. Password, come back. Come back. All right, click it. So now I can't see the guy. I click, okay, let's protect. All right. Right click button has failed me today. Right click button is not working. Come on, right click. Work, work, work. Right click. Okay. Now, since my right click is not working, let me show you another trick, see? If you go to home and I come to the far right of home, you will see something called selection pane. If I click on selection pane, it tells me where objects are because this is an object. So if I click on this, selection pane, it will highlight it for me. There's so many ways to stop Excel being silly. Yeah. So here we go. We have this white. So what do you need to do is tell the cell formats, don't feel white, shape fill should be no fill. So, and then hopefully this time around it should work, let's check. So white on white, let's go to delete this and come back to privacy. Now it works. So when it comes to privacy, if we have protected the sheets, review, protect sheets, say, okay, let's allow hyperlinks. So it's work hyperlinks, click, okay. So when someone comes here, it says, oh, what's happening? There's no password. Okay, click on this. It takes them here. And then he can type the password. When he tries the password and comes back to privacy, he now sees access to his files. So when there is a no password or the wrong password, he can't see anything when he comes to privacy and he will see this, he will click on this and it's taking him here to enter the password. How cool is that? Pretty cool, very long window stuff, but we're gonna have this online on our online tool. So you'll be able to see how to do this. So I know it's quite a long stuff. How was that? How was that? So we have two more short. I know we started a bit late today. If you don't mind, we have two more things to go through. Yeah, it's pretty long, pretty long. So I'll go through the, go through it. Now let me show you guys a quick video. I want to show you a video of what Microsoft have done in the new updates for me. The new updates for me, they had something very cool for conditional formats. I'm gonna play that video for you now. Yo, Adam Saxon with the Power BI Cat team and I have got a special treat for you. It's another month and we've got another Power BI Desktop update. This is the May 2018 release and we're gonna dig in what's inside. Let's go. You may be asking where Amanda is. She has asked me to do a guest spot for this month's release and I get really excited about every month when Power BI Desktop comes up because there's so many good things inside of it and I wanna walk through that, share that with you and we're gonna find out what it is. As we go through this, be sure to let us know down in the comments below what's your favorite item out of this month. We're very curious and or was there something you were hoping for that we didn't see? We always love feedback as well. With that, let's go dig into the items that are in this month's release and before we do that, I do need to put on my glasses. Sorry about that. First up on the list is an update to conditional formatting. So the way it used to work is we could set background colors on items or just adjust colors and it was based off of the field that we had in there and whatever aggregation type was going on in there. So let's take a look at what we can do now. So what I've got is in this table we've got sales amount and sales amount itself right now is being based on some. But what I can do is if I go into conditional formatting and we look at the background color scales, in here we're actually setting the color based on average, not on some. So we're displaying some but we're doing the conditional formatting based on average. So it gives you a little more flexibility. That's nice. Not only that, but we can take this to the next level. We like doing that, right? Let's always go to the next level. So I can do this on text now too. So I've got coloring format on the text and because I can use a different field for the conditional formatting based on what's being displayed, I can do this in a lot of different places. So in the same table I've got product name. This is the product name is the text that's being displayed but if I come down to conditional formatting and go to font color scales, the actual coloring is based on the average of NSAT. Pretty cool. Next up on the list is an improvement to the way that drill through works. So when this was first introduced, you use categorical fields that could be listed as a drill through field on a detail page report. Then when you go to your overview page and you select a chart that has that same field, you can drill through into the detail page and it'll make that relationship for you. So the improvement here is that we can now use measures for that drill through. So we can list measures in the drill through items as well as categorical fields. There is another item that I will show you in a second as well. That's really cool. So let's dig in so I can show you what's going on here. So from my scatter plot, I'm going to select on the deluxe class item and what I'm gonna do here is right click on my bar chart, go to drill through and then go to the details page. Awesome. I'm now drill through on the details page and you can see that sales amount was part of the drill through. And the other item that's included in this is the ability to pass filters as part of that drill through action, which is awesome. So the caveats on this is you can toggle whether or not we're gonna pass those filters, whether it's on or off, if you're using categorical fields. If you're including a measure, it will always be on and we will always pass the filters because we need filter context when we're doing that. And so we can see here, I selected in that scatter chart, I selected deluxe for class and we can see that as part of the drill through items that it's being filtered on that item. So, awesome. Give it a try. Sync slicers got an update as well and the idea here is that we can group slicers together and what this does for us is it allows us to do some interesting comparisons between the same slicer type that we wouldn't have ordinarily been able to do. For example, if we take a look at my average sale amount by class, what I wanna do is I have net satisfaction right now defined as one to three on the slicer. And I want to compare, let's say my deluxe class that is also at a different value. So I wanna see what the one to three detail is when I drill in, but I also wanna see what that would look like for other values as well. So if I right click on deluxe, let's go to drill through. Remember right now, net satisfaction is one to three. We're gonna go to my satisfaction comparison page. On the left here, the net satisfaction slicer is the one that is grouped with the slicer on the overview page, right? So those two are grouped together. On the right, I've got another net satisfaction slicer that is on its own. So I can use different values in this item, which is very cool. And the way we can set this up is if we select the given slicer, underneath, I'm gonna show the sync slicer's pane. And then within the sync slicer pane, if I expand advanced options for this slicer, I can put it in a group, right? So I've labeled it as NSAT and I'm gonna sync the items between whatever's in that group. So if we go back to the overview page, we'll see also that the net satisfaction slicer there is also in my NSAT group. And so those two are synced together, whereas my other NSAT, or my other net satisfaction slicer is not grouped. So I can have a completely independent value for that. Another thing that's part of this, and I don't have a good example for you, is the ability to actually sync items from different fields. So the example that Amanda shared with me is that we could have different date fields that are there and those values could sync between the two different slicers, even though they're from different fields if they're in the same group. So that's pretty cool. Next up is log access improvements. Instead of explaining it, let's look at it real quick. So I've got a chart here that is sales amount by class. And so if we look at this and I go to the paint brush, and then we go down under y-axis, I can change my scale type from linear to log. And when I do that, the improvement here is you're gonna get what you... So guys, we're back and that was good. So those are the updates to Power BI Desktop for this month. That's for May, May 2018. There are quite a lot more updates. If you go on to Microsoft's Power BI YouTube channel, you'll be able to see that there. And also if you check our YouTube channel later, we'll send you links. You'll have all the links so you can get access to these things, right? So the next demo, which you just saw, you just saw some things in the Power BI. So I'll just quickly show you how to do conditional formats in Power BI. And then we call it a day from here. If you want to join us later, you join us on the webinar for financial modeling. You see the link there. Just check the chat. Go down through the chat, you will see the link. Okay, so everybody good? Can you all hear me? Okay, I think you can. You can hear me. Good. Can you all hear me? Just put your thumbs up. Or somebody say, yes, you can hear me so I can move on. Chat is taking a while to come. So let's just wait for feedback. Cool, good. Got feedback. Nice. Heat maps on Power BI. Let me just quickly go through the two. So there's heat map on Power BI and there's also data bars on Power BI. Let me give you a little secret about Power BI. Power BI kind of started off with Excel. So there's Power Pivot in Excel. There's a new tool called Power Pivot. For those that don't know, go to our YouTube channel, D-Brown Consulting on YouTube and subscribe. There's so much good stuff there. You learn about Power BI, our Power Pivot. So all the developments in Power BI are really an update to, they're just getting stuff there. It's already in Excel and making it work in Power BI. Most of what they're doing is that. So if you look at it, let me just share my screen. I'm gonna share a Power BI screen with you right now. So let me just share the entire screen. All right, so if you look, you should see Power BI on your system. Now you should see me, a view of my Power BI. It's coming up on your screen shortly. Okay, if it's on your screen, just shout a yes. So I move on. Can you see Power BI? So if you've watched some of our Power BI webinars, the previous webinars, you will understand how Power BI works. I'm going straight into conditional formatting. I'm not going to show you how Power BI works. This is a nice visual I really like. This visual tells a story about what's happening between revenue and transactions. If I click on play, for example, you'll see, this is what's happening between revenue and transactions over a period of time. So you could see these things going up. And hey, you can pause it for a while and say, what's this particular product here? Or this is, this is Northern, North East region. They had 20 transactions and they made 33 million Naira on that day 14th of January. And if I click it, you can see the path it has flowed. All this is the path it has been going on a daily basis. It's all over the place. So this is how you could tell stories with data. But what I want to do is let me create a simple, it's a simple, empty canvas and let's create a small conditional format. So I'm going to use the matrix visual. That's the best visual for conditional formatting. So the right here, I'm going to use something called the matrix visual. So here's my matrix, make it big. And then let me come to calendar. Let me pick month. Let me just pick month. Where is month? I tick month. So it gives me month, but it's quite small. So typically when it's so small like this, you need to increase the size. So what I advise you just go to format and there's a search box under the search box, just type size and then anything about size will come up. Oh, so you see tech size. I can increase the tech size. Good, there we go. So we've increased the tech size. Good. So now let's put some figures. Where do we get figures? Let me say we go to revenue scenarios. Let me pick, you know, let me pick figures from data. I think we can have something in data. I have so many things to pick from. This was one of our old webinars we did on visualization. Go check it out on YouTube, on our YouTube channel. Show you different visuals. So let me expand this to see what it's called. Let me just pick one, just pick anyone is fine. Tick. So you can see some figures in there, very nice. So what you do with conditional format is I have this visual. I go to my visualization section, right? And then you have conditional format right there on the bottom, conditional format. And under conditional format you have background color, font color, and data bars. So background color, you just put it on and that's just colors, quite ugly color, right? Colors the highest green, the lowest red, and the middle. Let's make the middle yellow, right? So for that, to make it yellow, we go to advanced controls. And once we go to advanced controls, we should be able to see a button coming up right now on the screen. Okay, decided to go on a separate screen. Let me try and bring it to our screen. Okay, I went to a different screen. Here you go. So you can tell it to look advanced controls. I want to change the colors, not the lower. So let's do a diverge color. So we'll have a middle color. And then make the middle color yellow. Yeah, make it a bit yellow. But this red, let's make it a bit lighter red. Let's make the green a bit of a lighter green. And let's see how that looks. So, well, maybe not too nice, but that's it. So here I've done by background. So the last tip was you can also do it by a visual background now. Remember, I'm under conditional format here. Conditional format. I can say, do you know what do it by data bar? So if I tick data bar, you see this is a data bar. And with this, you can see it's now making it bars. The numbers are very close together. So you won't see a big difference. But if the numbers where you see different data bars. And you can also do by font color. Font color is actually cool. Font color just changes the color of the font. And that's your conditional format. And you can combine them. You can combine all of them, although you look terrible, right? This is font color, so you won't see anything. So if I remove font color and put on this, and then put on data bars, it really looks odd. But that's what you can do. You can combine. I don't advise you combine. Just stick with one. Whichever one you want, stick with it. And that's conditional format. So that's it guys. We have actually done all our demos. I hope you enjoyed it. I'm gonna leave you with a video. And that's the last time. I'm just gonna leave you with a video for those that want to stay. I'll leave you with a video. One very important update they've done to Power BI Desktop for this month. Very important update. And it allows you to refresh selectively. It's like selective refresh. Because some people complain that with a massive file, they have a file that's maybe 15 gigabytes. And when you click refresh, it's gonna go and refresh an update or selective updates. It's gonna update those 50 gigabytes. When all you've done is change, only a tiny thing that's maybe two megabytes. So what they've done is more like a selective update or selective refresh. So watch this video. But after the video, we're gonna close. So thank you guys for watching. And anyone that's joining us for financial modeling, I look forward to seeing you in less than an hour's time. So I'm gonna leave you with a video, which is the very big updates they've done to Power BI Desktop or Power BI in general. But it's only for premium clients. Power BI, so it's called an incremental refresh. That's what it's called, incremental refresh. So I'll leave you with that video, guys. And thank you very much for watching our webinar for May 2018. I'll see you guys in next month. But yo, Adam Saxon with the Power BI Cat Team. And I have got a special treat for you. It's another month. And we've got another Power BI Desktop update. This is the May 2018 release. And we're gonna dig in what's inside. Let's go. You may be asking where Amanda is. She has asked me to do a guest spot for this month's release. And I get really excited about every month when Power BI Desktop comes up, because there's so many good things inside of it. And I wanna walk through that, share that with you. And we're gonna find out what it is. As we go through this, be sure to let us know down in the comments below. Yo, Adam Saxon with the Power BI Cat Team. And I have got a special treat for you. It's another month. And we've got another Power BI Desktop update. This is the May 2018 release. And we're gonna dig in what's inside. Let's go. You may be asking where Amanda is. She has asked me to do a guest spot for this month's release. And I get really excited about every month when Power BI Desktop comes up, because there's so many good things in there. Okay guys, seems that Gremlins got us. So the video unfortunately didn't play. So I promise to put the links for you on the YouTube channel once we get there. So thank you guys. And I hope you enjoyed these five hot tips for using conditional formatting in Excel and Power BI. So see you guys next month. Thank you everybody. Bye-bye.