 So hello everybody and welcome to our webinar or the brand consulting power BI and Excel monthly webinar 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 say what we're going to say in conditional formatting great. So what are we doing today? Well today, we're going to talk about Excel and formats is 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 Excel and formats as I said if it's not in a cell in Excel It is not Excel That's one statement to do about Excel if you want to understand Excel with one statement one sentence That is the sentence if it's not in a cell 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 going to 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 Some data to 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 Maybe 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 Double-click home so that this can come up, right? Then if you look at you to your right, you'll see clipboard You see this is clipboard font alignment number styles cells and editing I'm using Excel 2016 was 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 see the version you're using I think so the version I'm using is office 365 pro plus pro plus is really cool because that's the one that has power Pivot Pro plus yep, okay So back here so under comb you have conditional formatting under the styles section So if I click on conditional formatting and you have your highlight cell rules You this is where you now do conditional formatting. There are some preset rules here already stuff They'll 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 set 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 Seven million or something seven zero zero zero zero zero zero Something just give me highlights or greater than seven 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 do you want to conditionally format and then you format it? So basics, that's fine. So let's see our demo. What what should we do? What's the 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 This is our data and if you look at it, 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 these 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 dates. 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 If you're in the US you you guys do month first So in Europe and a lot of parts of the world we do day first So my format in 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 extract 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 Formatted 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 a new rule. It brings up a set of rules Table for rules and then on the 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 use a 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 on the cell value. We want blanks I see blanks. So I have cell value specific text dates occurring blank. No, not no blanks errors No errors. So blanks. We just want blanks. See format only sells with blanks Then under preview we say format. So we'll go to format and if the cell is blank I want you to go to the fill and maybe let's say color it gray or something like that Let's say color agree. 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 is make it great. So click okay, and that's what happens So now when you type in a date, let's say one January 2015 if 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 is 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 drop-down 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 and 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 going to highlight these stores Contrast ship down arrow key and I've already called it L store So I click okay, and now I have a drop-down when I pick on the drop-down my data works So this is a nice tool 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 large data, it makes a lot of sense to kind of put some Shavings on alternate rows. How do you do that? Now anyone that knows Excel very well? You'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 we go to alternating rows. So again, we're going with our same theme Started ended and this is the data. Well, what I want to do is this I want this data to just maybe shade this We shade this is how you do it manually now if you had very large data sets It's gonna be difficult tedious to want to shade it like this Then I'll 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 going to 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 or eight or nine Okay, so the ones 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 can say it calls the even, right? Or really the best way to if you say it calls the even by the way, that's a function that says this is Six. Yeah, well six is an even and let's see drop down. That's contra D Well, the next closest even number to seven is eight then eight the next closest is ten ten 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 say tell me in the chat? How many of use a function called mode? Can you type in the chat? Have you used mode before? Let's move on. So here we're going to use mode. So the mode function is a cool function It says it returns the remainder after it's in number is divided by a divisor long story See one thing about Excel right the the way the way they explain stuff is so technical, isn't it? It's too confusing. Now, there's a new their new functions as mode malta mold mold sing Sng these are new functions to excel if you don't have 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 modic recalls a remainder after a number is divided by divisor. Well, let's just do it So I tab and you have to Syntax has two arguments a 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 f6 to 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 just continue you see have one one one Right now if I make it three things said to my device of being two if my device or is three You would see that your remainder's One then there's two then there's one then there's two. So really so if I want to say look every Other rule if I copy this formula down, you can see zero one zero one zero one now That I have identified Uniquely identified the 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 use a formula to determine which cells to format now under format values where the This formula is true. So what do we want? We want it to be true to color those cells that or shade those 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 or typing a formula notes 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? 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 in the from up here in the name box b6. So we're going to say in here. You're going to 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 a number and device. So now if you remember the number Is going to be our row construct. You'll see the row whatever row you're in So then function we're putting there is row open and closed bracket. So row So mode what row are we in and whatever row we're in we want to divide it by two Now if you notice b6 Is here this 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 is that 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 shapes it shades perfectly shades this shades this shades this And that works perfectly 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 um, we have a financial modeling webinar starting at 11 today 11 o'clock So so someone is going to um 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 is called We're data privacy. How do we 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 On 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 All right, so this cell we're going to make it a password cell So 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 We'll 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 going to protect these two cells We're going to 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? All right, right click Okay, so let me right click My right click button is not working too well today. Oh something is So i'm just going to 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 it click. Okay. You remove that lock and say, okay Then we're not going to protect the entire sheet to do that We go to review and then we say protect sheet right Protect sheets. We're not going to 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 type so And then what 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 one three 2015. Oh, there's no one three no revenue there one three 2015 Obviously because this is probably March I can't start something in march and end in 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 a shortcut is control shift Um Just to make make the make it look much better is control hash So control hash on some keyboards control hash is Is on what? That is on so it's like control ship three So you should be able to do that. Let me see what keyboard my settings or my keyboard settings on this computer My keyboard settings are funny funny set of settings on the computer. So Uh If I could to do what I'm Besides 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 And then what you do is you highlight the entire sheet Let's you highlight the entire sheet You're going to 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 going to 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 a a 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 going to 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 going to 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 Open I mean close bracket. I mean On 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 is not equal to What password right so you type that in type password remember it was an initial cap So this is the code you see this cell is not equal to password Yeah Or for those that really cool in excel you could just say not This cell Well, let me know let me know go to not let's just leave it like that. Let me know confuse you guys this Control is not equal to password. Now if it's not equal to password go to format and let's feel the entire thing black I'm going to feel 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 are showing? We don't want anything to shadow and 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 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 sheets no passwords. Say, okay Now be looking at your formula bar. 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 No, the way you do that is let's unprotect 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 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 a wrong password Come to privacy All black Come here and let's put the correct password password And 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 the formula bar to 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 I mean on protect On protect 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 haven't you you Access denied. Let me just say access denied enter password And when I say enter password This 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 So wait now I'm going to color this white. Guess what since I'm 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 oh, I didn't make the text white. Oh Error error in programming. Let's check And put a password Thank you, but 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 a 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 to highlight it for me. There's so many ways to stop Excel being silly. Yep. So Here we go. We have this white So what do you need to do is tell the cell formats? Don't feel white ship feel should be no feel So and then hopefully this time around it should work. Let's check So why don't white let's go to delete this and come back to privacy Now it works. So once comes to privacy if we had protected the sheet review protect sheet 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 him here and then he can type the password When he tells 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 it comes to privacy and he will see this He would click on this and it's take him here to enter the password. How cool is that? Pretty cool very long window stuff, but we're going to have this online on our online tool So you're 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 Yes, 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 a microsoft have done in the new update for me The new update for me. They had something very cool for conditional formats. I'm going to play that video for you now Yo Adam sacksen 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 going to 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 want to walk through that share that with you and We're going to find out what it is As we go through this be sure to let 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 you know 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 be is based on the average of inset Pretty cool Next up on the list is an improvement to the way that drill through works So when this was first introduced you used 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 let's dig in so I can show you what's going on here So from a given from my scatter plot, I'm going to select on the deluxe class item And what I'm going to 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 going to 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 sink 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 want to 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 want to see what the one to three detail is when I drill in But I also want to 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 going to 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 going to show the sink slicer's pane And then within the sink 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 going to 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 date 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 Paintbrush 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 going to get what you So guys we're back and that's was good. So that that's those are the updates of Updates to power bi desktop for this month. That's for may may 2018 There's 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 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 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 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 as 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 going to 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 on your screen just a 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 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 see this is what's happening between revenue and transactions over a period of time So you could see these things going up and I 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 have 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 is 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 So you see text size I can increase the text size Good there we go So we've increased the text 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. 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 down the bottom conditional format an 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. It's 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 you 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 Oh, yeah, 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 low. Let's do a diverge color. So we 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 that's it. So here I've done by background So the last tip was you can also do it by a future background now Remember, I'm on the 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 wouldn'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 going to leave you with a video and that's the last time just going to leave you with a video For those that want to stay leave you the video one very important updates They've done to power bi desktop for for this month very important update and It allows you to refresh selectively it's like selective refresh Because some people complain that if the massive file they have a file that's maybe 15 gigabytes And when you click refresh it's going to go and refresh an update or or selective updates It's going to update those 50 gigabytes when all you've done is change only a tiny thing There's maybe two megabytes. So what they've done is this is more like a selective update or selective refresh So watch this video, but after the video we're going to close. So thank you guys for Watching with our 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 sacks in with the power bi cat team and I have got a special tree for you It's another month and we've got another power bi desktop update This is the may 2018 release and we're going to 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 want to walk through that share that with you and We're going to find out what it is As we go through this be sure to let let us know down in the comments below Yo Adam sacks in with the power bi cat team and I have got a special tree for you It's another month and we've got another power bi desktop update This is the may 2018 release and we're going to 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 Okay, guys seems like 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