Excel Hacking III - Manipulate Text, Tables & Conditional Formatting | Dragos Stefanescu | Skillshare

Excel Hacking III - Manipulate Text, Tables & Conditional Formatting

Dragos Stefanescu, TeacHack Founder - Teaching 1000s Online

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
22 Lessons (1h 9m)
    • 1. Excel Hacking Series Trailer

      1:44
    • 2. Introduction

      2:13
    • 3. Ensure Proper Formatting With These Formulas

      3:02
    • 4. Here's How To Join Text Strings Together

      3:48
    • 5. Quick Hack: Join Text Strings Without Concatenating

      1:15
    • 6. 3 Ways To Extract Bits Of Text

      3:08
    • 7. Ultimate Guide: Extracting Text By Identifying Specific Strings

      5:37
    • 8. Quick Hack #10: Ensure Text Accuracy

      5:15
    • 9. The Built-In Functionality To Customise Text Strings

      4:09
    • 10. Intro

      1:48
    • 11. Beginners' Guide: Excel Table Creation

      2:33
    • 12. Table Design Basics

      2:03
    • 13. Quick Hack: Adding Total Rows To Tables

      1:23
    • 14. Quick Hack #15: 3 Awesome Table Functionalities

      3:57
    • 15. Conditional Formatting Essentials

      3:17
    • 16. How To Manage Conditional Formatting

      2:21
    • 17. Conditional Formatting With Formulas

      2:49
    • 18. Excel Hack #6: 3 Features To Take Conditional Formatting To The Next Level

      4:53
    • 19. Excel Chart Creation Tutorial

      2:36
    • 20. Quick Hack: How Do I Know What Type Of Chart To Choose?

      2:08
    • 21. Customising Excel Charts

      5:14
    • 22. Excel Hack: How To Make Snap Judgements On Large Sets Of Data

      3:45

About This Class

Are You Ready To Take Your Excel To The Next Level?

I was an Excel Rookie too at some point. I put in a lot of hours to hone my skill and I managed to be recognised as an Excel expert in the companies that I worked for. Since then, I've gone on to teach thousands of students how to master Excel in the fastest possible time.

How Did I Do This?

By taking a different approach. I don't want this to be the regular Excel course, where I take you through every single different formula and functionality. I want you to:

  • Focus on the benefit of each feature, rather than the means of getting there
  • Understand the key elements that Excel experts use 80% of the time (my so-called Excel Hacks)
  • Do this in a practical & applicable manner by following along the spreadsheets and completing them yourself

The End Result

I've differentiated the lectures between normal ones, Quick Hacks and Excel Hacks. These last 2 are designed to give you a great understanding of the must-know Excel features.

Here's what you will be able to do by the end of this class:

  • Know exactly how to Manipulate Text Strings in Excel - join text strings together, identify specific strings or ensure automatic proper formatting.
  • Master Excel Tables and all the functionalities that come with them
  • Become an expert of Conditional Formatting and even make use of added features such as Sparklines, Data Bars or Icon Sets
  • Understand how to create & manipulate Excel Charts, decide which type to use and be aware of the plethora of choices available.

Enroll now and let's start building your Excel journey together!

Transcripts

1. Excel Hacking Series Trailer: V lookups pivot tables, conditional formatting, sorting and filtering. These are just a few of the aspects. Let's face it. Every Excel course out there teaches boring theoretical concepts without any practical applications. Let's think different. Let's think. What are the top 20 Excel hacks so I can produce mind blowing investment banking quality spreadsheets in record time without spending eight boring long hours Learning Excel theory . That just leaves me confused, perplexed and over. Well, what if you could learn everything twice as fast, skyrocket your productivity and even advance your career earlier than you imagine possible ? So how much are a few 100 hours worth to you? Here's how this course is different. You learn hacks such as one Excel Essentials will cover the quickest waste navigate. And when Nipple E data in Excel is that you can master the basics straightaway to 20 plus quick and killer hacks. Individual lectures focused on particular Excel hacks, which 90% of people don't know about three Bullseye formula blueprint Master Excel formulas by shifting the focus away from how they work to what their usability is. Impractical scenarios. If we truly want to accelerate your progress and hack excel in the shortest amount of time . Then this is for you. Take this course now and meet me in the virtual classroom in a few seconds. 2. Introduction: manipulating text in Excel. So this section contains all the things that I think are the most important when it comes to manipulating text in Microsoft Excel. Now I've encountered different situations where you need to change the text in one way or the other. And it's not your fold most of the times because you get some spreadsheets and out by some people, and the data is not quite organized in the way you would want it to be. Or you need to apply some formulas and need the text to be different. Whatever the case may be, I'm going approach all of these topics, and we're gonna look at how to group text together. So using either can Katyn eight or other ways to put things together, extracting characters or important information. So how do you take away something? For example, from an email address? How do you take the company name from in between the at sign and the dot com sign? How can you deal with all that stuff and not on Lee? For a few instances, we're looking for solutions that are going apply to thousands and thousands of entries and finally, text formulas that will ensure that everything is aligned in your spreadsheets. I mean, have a practical example here, which I'm going to mention in the particular lecture. But in short, when I was working consulting, um, and in finance there were two very different situations. Because in finance, when you exchange spreadsheets with people that use excel all the time every day than everything you'll see is aligned. Everything is accurate most of the times. Okay, when you work in consulting, where there's a variety of people that create excel spreadsheets just because it's a much more organized way to put data through and you have 5000 instances where the names are not properly aligned, then you can get a lot of trouble. We do formulas because the names aren't lined. Well, you're expecting something the former doesn't come through and you start wondering, why is it not working? So I'm gonna give that example in the particular lecture, and I'm gonna show you how to make sure that everything is aligned and in the same format without being said. Let's get started with this section, as it's the important precursor before the Advanced Formulas section 3. Ensure Proper Formatting With These Formulas: the basic Excel text manipulation formulas that you need to know. So in this very short lecture, I want to show you how to use upper lower and proper, and I'm gonna show you exactly why I think proper is an essential tool to know and how you can also use upper and lower. So this is gonna be very quick. Let's assume you have this table right here to the left. Had left hand side and Okay, fair enough. I've exaggerated the way these names look. You're probably not going to get a table with the names formatted this way. But I wanted to make this exaggeration more visible is that you understand exactly what these formulas do. And as I mentioned, introduction, introductory lecture. Don't assume you're going to get all the tables and everything in excel perfectly formatted and looking nicely. You always have to check, and these formulas do the job for you. So let's say I want to have every everything formatted properly. So by formatted properly, I mean having a capital letter when the name starts and lower case letters afterwards. It's pretty easy right now. All we have to do it's press equal right proper. As you can see, it comes up and this will put it and you guessed the proper format. So once you select a cell that you want to format in this case, I'm going to the first text based cell that I have a right proper put in the cell there and hit, Enter. And as you can see, it's changed the format from all capitals, Mike to the way it should be. And then if I selected and copy it and do the same paste special trick. I can copy it for all of these instances, and I'm gonna copy the formulas. And as you can see, everything is formatted nicely and everything looks good. Now, the other variation that you can do for this obviously you wouldn't have lower case letters fully for names, so I'm not gonna show you lower, but it works exactly the same. I'm just going to assume you want, for whatever reason, to make the last names, the surname, all caps. Now I could write up her and copy the formula, but I thought I'd show you this since we practiced Find the replace before I'm just going to write proper and open a bracket. Remember what I told you about having symbols in there, even though it probably won't replace anything that has proper in there. I just want you to get used to this. And then I'm gonna go to replace with and right upper and open the brackets and then as a hit, replace all. As you can see, it's made 18 replacement, and all the surnames are now all capital letters. That's how easier it is to use proper and upper, and you can apply them to these formulas, even if you don't see a mistake. If you have 5000 occurrences and you want to make sure their format exactly as you want, then you can do this, and then you can simply copy and paste the values on top. And that's your main table. If you don't want to do that, if you don't want to lose this information here, then you just great separate columns. And that still does the trick 4. Here's How To Join Text Strings Together: joining text strings from different cells into one. So I've alluded to this in the first introductory lecture, and I want to show you exactly how he can use formulas to put different cells together now the same way that I've taken the first and the last name. As you can see there in different columns, I could put them together and put them in one different column. But I want to show you the phone prefix and the phone number because I want to add a few symbols in between. Just that you understand exactly how this can work now. It's very, very easy. There's one formula that will do everything for you just need to understand a few basic tricks and a few basic rules. So the formula is called con Katyn it and you've probably heard of it. Once you Preston cabinet, you'll see it joints several text strings and you can see the syntax is very easy. The first bit of text coma, the next bit of text, and so on as many of them as you want. Now I want to show you a quick trick because I want you to understand how you can manipulate this formatting to make it look even better. So let's assume I want this phone prefix. Remember the flash feel that I showed you in the previous sections? I want this to look something like this. Okay, so I want the prefix in brackets and then I want to 39279 with six digits, one after the other. That's what I wanted to look like. And here's how you can do exactly that. So we're gonna open the formula, right? Can Captain eight. Press tab and it opens the bracket. Now, as soon as it does that notice, my first character is the bracket. So what I'm gonna do is this apostrophe sign The double possibly sign is the one that you use to insert characters. So it will read it as a text string. So that's the fault in Microsoft. Excel for text strings and was gonna literally follow the format. So I'm gonna put a bracket and close the text string, So I've opened it. Now I close it and then I'm gonna press coma, right? That's my first element. Now, as I told you before, I'm gonna insert a few spaces that I see exactly what I'm inserting. Then one follows is the phone prefix. So I simply click on this cell right here, and it takes D four. As you can see, they're gonna press coma again, and I'm gonna go to the next element, which is another bracket. So I'm closing the bracket right now, So I'm opening the text string, putting the bracket and then closing the text drink. Then I put another coma because I need to indicate the next element and notice. I just forgot here, toe actually put hyphen as well. So I'm gonna put a bracket and a hyphen, and then I'm gonna go all the way to the end. And all that I have left is the six digits here which, coincidentally, are in this cell right here. As soon as I do that, I can close the bracket and the formula's done once I pressed Enter, as you can see, the numbers formatted. And this is a format that's the best thing. Flash Field does good things because it manually completes everything in a table. So if you have 1000 instances than it will automatically fill that in for you, but with the formulas. If you add to those columns, if you change them in any way, you're gonna have the format there. So it's just a matter of copying the formula across and it's gonna work. And, as you can see, as soon as I double click all of these instances, all these formulas are completed and they're ready to go. So that was kind Captain. Eight. It's very easy to use. Just remember the apostrophe signs that show a text string because you don't have to use them on Lee within. Captain it. You can use them with any other formula in excel that can input text. So keep that in mind because it's a very nifty trick and it will prove useful to you. 5. Quick Hack: Join Text Strings Without Concatenating: joining text strings without using concoct in it. So I've showed you how to use the formula. And this a quick hack to show you how to join tech strings without even using the Comcast Nate formula. Now, the actual formulas probably longer to use but easier to apply. But I'm just going to show you this because I want to emphasize the to apostrophe signs that you can use and the end sign which confuses example so very quickly. I have the first and last name here, and I want to combine them in the full name. It's as easy as this. You press equal and then you select the first cell. So right now it's going to say, Mike, now I'm gonna put the and sign, and if he can see clearly, then I should human. So I have the ensign, and then what I'm gonna do is I'm gonna manually put a space here, so I'm gonna open your apostrophes space. Close the apostrophes. So that's the second element. And then I say and again and I put the third element, which is the last name. That's how easy it is. And that's how you can combine text strings in an Excel document. As you can see now, I have everything here. It's as easy as that and you can just drag it down and it's gonna fill everything in. 6. 3 Ways To Extract Bits Of Text: extracting text strings with the three formulas from either the left, the right or from the middle. Now, in this very quick lecture, I want to show you how you can separate text strings even further. So we have three formulas that we're gonna use which are left mid and right. And as you might guess, left takes all the characters from the left hand side of the instance of the value of the text string that is in a cell. Mate takes all the text between two points that you mentioned and right takes everything from the right hand side. Now let me show you very quickly how these work and I'm just going to go to the basic functionality where you show exactly the character where they start. Let me explain. So the syntax is as follows. Let me use left. Firstly, so I type left. And as you can see, it says it returns to specified numbers of characters from the start of a text string. Now I just have to select the text, which is this cell right here. And then I have to indicate the number of characters so very easy. We're just gonna go with number of characters right now. So I have two brackets plus three digits inside of them. So I have a total of five characters. I closed brackets, close the formula and press enter and, as you can see, is taken the prefix right here. Easy, right? Let's go on to mid. Maybe I want to get the prefix, but without the brackets next to it. So I'm going to write Mitt. Admit is slightly different to left, but not too much different. So I still have to select the text. Obviously, I said like the same cell and then I just have to indicate because it's in the middle. I have to indicate the starting number. So the starting character, in my case, this will be number two because I have the bracket, which is the first character. So I'm gonna start Carter number two and then I'm gonna indicate the number of characters. I have three digits that I want to include. So that's number three. I closed the bracket, and as you can see, it returns 986 Finally, you might have guessed by now, right? Operates just the same as left. Only it starts from the end of the instance. So I'm gonna open the formula, right? And again, I still like the same exact text and then the number of characters that I want to show. And as you can see, I have six digits in every phone number. So I'm gonna press six and I'm gonna close the bracket. And right now, I have everything here. I can simply drag everything down, and I'm gonna have the same thing for every occurrence in this file. I just want to mention that this particular example we know the number of characters that we have, and that's mostly when you're going to use left, right, Admit now I'm gonna show you how you can delimit this by the character or asserts an occurrence or even in a certain point in the cell space wise. But that's gonna follow in the next electric. For now, this is the very basic way for you to split up text strings in Excel 7. Ultimate Guide: Extracting Text By Identifying Specific Strings: extracting a specific text string where the character count is not the same. So I've showed you how we can use left Mitt and right to extract a certain text string out of what we have in there. But how would you use that right here? I mean, it's very hard because all these names, although they have the same format, um so the first initial full stop surname at the company name dot com. How could I extract that company name? Because I can use right because I haven't dot com after each and every one of them. But the company name the length of that company named varies. And also the actual people's names vary, so I can use met I can use left I can't use right. So we're going to use a combination of formulas here, and I'm gonna show them to you step by step. So in every each and every one of these cells, but basically, when you want to look for a character, you can use search or find, and I'm gonna show you search purely because it's not case sensitive. There's very few differences between these two formulas, but I prefer to use search. So here's the thinking behind what we're gonna do here. So search is gonna show me what position at certain text string started so I could look for ABC here and it would return the first position where ABC starts, Or I can look for a specific character. And if you notice here, I have the at sign. So I'm gonna look for the at sign and see exactly where that start that I'm going to look for the dot com and see exactly where that starts. And then I'm gonna use met because I have the starting point in the end point of the text string. I'm gonna use mid to extract the company name from there. So let's get let's give it a go. So I'm gonna type search here. And as you can see, the syntax is again very, very easy. We have to find, um you have to have the text that we have to find. And as I said, I'm gonna use the at sign. So I put the outside in here in between the apostrophes and I hit the coma within text, which is this cell right here, C five and then you have an optional field, which is starting number, which you can use to customize the search to be just after a certain amount of characters So it could start at character five in the email address and so on. But we don't actually need that. So I'm gonna close this bracket here. And as you can see, I have the number 10. So the at sign here is the 10th character in the email. And I can also fill this all the way down. And I see where the at sign is for every email address that I'm going to the same exact thing for dot com. So I'm gonna find dot com within this cell right here. I'm gonna close the bracket, and then I'm gonna also fill it all the way down. Now I have my two points, a starting point, and the mid point Let me just sent to this that it looks nice. And now I'm gonna go into the company name. So what do I have to do here? I'm gonna use Mitt, so I press mid and then I have to select the text. Obviously, I'm still working with the email address. However, Here's where it gets a bit tricky. I mean, it's not tricky, but you just have to understand this. The starting number is not gonna be 10 because that's the actual character where the outside is OK, I can actually show you this. I'm going to start with this character 10 right here, which is the at sign, and I can end it with the dot com or before dot com. But I have the number of characters. Okay, let's let's do it this way. Let's assume the number of characters is going to be the dot com. So 17 where it ends minus the at sign. Let's see where it gives us. It's going to give us at ABC Inc and that's it. Now I don't want seven characters. I want six characters in this case, and I don't want the at sign. I want everything that comes after it. So because these beginning and end points that I found with search contained the actual character, I need to subtract one out of each of them or add one, depending on what the what the state is. So I'm gonna add one here, so it's not going to start from the at sign. It's going to start from the A right after it and because I'm going right after it. That's one less character that I'm taking into account. So the number of characters I'm gonna need to subtract one and when I pressed enter. As you can see, I have the company ABC Incorporated. And if I also fill this all the way down, I have all of the company's four letter companies three letter companies, whatever they are. I'm just using the at sign and the dot com to see where they start and where they end and put them in the company field. So that's the way you can identify certain points in text strings based on different characters, and you can identify their position and then create this formula to extracted texting that you want. If you're asking yourself yes, you can actually use this in one whole formula. You just need to replace the D 53 E five in the D five here with the formulas that you have in these respective cells, and they will work just the same. It will just be a longer formula, but if you want to ditch these two columns here, you can either group them or use one single formula 8. Quick Hack #10: Ensure Text Accuracy: quick hack ensuring accuracy of inputs. So I've made this point before from a text based perspective, I'm gonna make it again. You need to be sure the text, the inputs that you receive from other people are accurate when you deal with excel. And there's a reason why I tell you this because I've encountered this before and it's given me a massive headache. So when I was working in consulting, I had these enormous spreadsheets with people that had to be trained on different programs and because these were thousands of occurrences, Okay, thousands of people that need to be trained, obviously, and I would never do it manually. Anyway, I couldn't have done it manually. So I had to find some formulas and, you know, we're gonna learn V. Look up. We're gonna learn for me just like that. Don't get scared. Just because if you look up isn't in here, but you need to have an exact match most of the times. And if these texts are not accurate than I 100% accurate, you're not gonna find those matches. So what happened to me? I was looking for these names in the spreadsheet and I couldn't find them, and I thought, OK, this is an area that person doesn't actually exist. But then I used find and replace. And when I tried to use find the replace, I find a person if I only look for a few characters of their surname and that I wouldn't understand why the formula wouldn't pick it up and I would search for half a Knauer. And our test my formulas thinks something is wrong with the way I'm thinking about it on Lee to discover, had the people that filled in those spreadsheets would accidentally dropped two spaces. As you see here, this guy has two spaces. It's not something that you would normally notice. And the thing that annoys me the most is when someone accidentally puts a space at the end of one text instance. So, like here, for example, you see this little space here? I mean, as I said before, if you work in finance, maybe it's not gonna is not gonna happen. But if you deal with people that just use excel regularly, if they import those names manually, them. I just put a space here and there's no way you're going to detect that space unless you actually click on that cell and you actually see there's an extra space there and your formula doesn't work. So what did I do here to show you exactly how to deal with this. So I used the V look up formula to bring up the email address of this particular person and this one and this one and so on. So the way the V look of formula works in this instance, and again, don't worry about it is it takes the name and wearing the trim sheet. It goes to search sheet and takes the email address that corresponds this person. However, if there's an extra space sign in there, it won't pick it up. And as you can see, we have quite a lot of errors. Now I'm gonna show you exactly how to deal with this. But first I want introduce you to a very quick formula, which is going to give you the length of a particular 10 text drink, and it's called L E n from length. So I'm just gonna put the length here just so you see the difference after we apply the formula. This is one way that I checked text strings. It's just something that I came with came up with on the spot. But it serves as a really good test in this particular example. So, as you can see, here is how many characters each of these names half. Now, what I'm gonna do is I'm gonna copy all of these names, and I'm gonna put them here as values. And then I'm gonna copy all of these formulas, and I'm gonna put them here as formulas. Now, I wouldn't want to. I wouldn't expect anything different now. What I'm gonna do is I'm gonna modify these cells and use the function that you should use to make sure that everything is OK and it's called shrimp. And as you can see, what trim does is it removes all the spaces from a text string, except for single spaces between words. See if you have different space. If you have two spaces here, for example, from my prey low, then it's going to eliminate one of those spaces. If you have space is at the end of the name, it's gonna eliminate those as well. She's gonna make sure it's the right format for particular names. So I'm gonna select that and press enter. And as you can see, the formulas or their the name is spelled properly and it's exactly the same as in the search sheet right here. So you go back to trim. I'm gonna also fill this all the way down. And as you can see, everything is perfectly accurate. Right now, everything is fine. And if I look at this, um, cell and test the length of these formulas, as you can see, they are different for those that gave air. So everything that has an error has an extra character here and there, which ultimately makes the difference between your V. Look up, working your whatever formal. You want to use the technique name and it's not working. So I hope this was It's a very simple formula, but I wanted to give you this sort of story to it because it's a very, very tiny aspect. But it's something that can waste you a ton of time if you don't know why these errors occur. If you don't know how to accurately input the text in the spreadsheet, and if you don't know how to solve it in the most effective manner 9. The Built-In Functionality To Customise Text Strings: separating text in Excel using its inbuilt functionalities. Now, Excel has some pretty nifty functionalities that you can use. But what I would say before actually show you how to use the text to columns. Functionality is that I still prefer you to use formulas because at the end of the day, if used text columns you will just separate this field or this field, for example, and you're just gonna end up with some values in these columns. But you're never going to be able to check exactly how those values got there, and you're not gonna be able to replicate them automatically. So say, for example, I insert 200 more instances right here. I would just drag down the formula and get the same result, whereas with text to columns after applied the whole thing again on the new rose that have been inserted. But with that being said, let me show you exactly how texted column works. So notice it's on the data tab right here under that, the tools. Now you just have to select the whole column first, as I did right here and then you press text columns and you have two different options. When doing this, you can either choose the Limited. So we have special characters that separated, which is our case right here, or when you have fixed with. So that's where the format is exactly the same. For all of the entries in the column. Now, we're gonna roll with the Limited for now, and I'm gonna press next. And as you can see, I can choose a the limiter I'm gonna choose, for starters, the at sign. So I'm gonna put the at sign here and you can see on the bottom exactly how it would separate these two, um, into two columns, this whole column into two different columns. And then when I press next, I can choose the formatting of my data and its particular destination. Now I can choose for it to start in C five and then it's gonna expand into C and D columns into two different instances. And that's exactly what I want. If I press finish is already data here, do you want to replace it? Yes, I do. And it's split it like this after that. If I want to get the company name, I could do the same for this instance The Limited again on Lee instead of the symbol instead of the at sign, I'm gonna put a full stop. And as you can see, it separates them like that. Click next. Keeping the same column. Do you want to replace the data? Yes, I do. And there you go. I have everything here at the company name. I can get rid of this column right here because it doesn't serve me any purpose, and that's it. Let me show you how you can use, uh, this functionality the other way as well for a very quick split off this text. So I'm gonna go on fixed with and when I go on fixed with, you're going to see, I can choose exactly where I want to put these lines creator line, delete a line or move a brake line. So I put a brake line here. I put another brake line here, so I want the full four number K no spaces, nothing. I'll put another brake line here and another one here. So I'm only interested in getting 986239279 And then when I pressed next I can choose again the formatting. I keep it as general and press finish. Now it's gonna expand it into all of these different columns from where I can believe whatever I don't need. And then, as you can see, I have everything the limited as it should be, then I can simply use Come Katyn eight and just combine these two instances into what? Sorry didn't really like that into these into one single column. And as you can see, I have the whole phone number very quickly. However, as I mentioned, as you can see, this is a very suitable situation for, um whenever you have to do something quick, something more manual. But I would still prefer use the formulas to extract that information because in the long run, it's gonna ensure your more accurate, and it's going to save you much more time than doing it with text to columns 10. Intro: tables, charts and conditional formatting. Welcome to this new section. We're going to discuss Useful Excel features. I just want to take you very quickly through what we're going to cover in this section, and they were going to get started. So firstly, excel tables now you might wonder. Okay, I use excel tables all the time, but you probably don't know about this functionality in Microsoft Excel, which gives you a lot of advantages over just building tables yourself with the borders and having them there. So I'm gonna cover all that in the next few lectures. They were going to go into conditional formatting. So you might know conditional formatting is a way for you to insert certain rules depending on the cell values or whatever formulas you want to cover. And this will help you distinguish certain cells when you have a lot of data this congee particularly useful. So we go into both conditional formatting with rules and formulas. Then we're gonna look at Excel charts. So you probably if you work in excel a lot, if you have data heavy spreadsheets, you might need excel chart. Now, in a later section, we're gonna cover how to build these charts in Power Point using Think Cell, which is a PowerPoint and Excel Adan. But you should also know how to build charts in Excel directly, how to build different types of charts, how to know which type of chart to choose and so on. So this is what we're going to cover in this part of the section and finally sprinkled in between all of these lecturers. We're gonna have our usual Quick and Excel hacks, which are designed for you to rapidly learn certain features that I believe are the most important or are a bit special and would seriously improve your Excel skills with having said Let's get going and let's go through with the next lecture. 11. Beginners' Guide: Excel Table Creation: creating excel tables. So a very quick one. For those of you that don't know about this functionality, I'm gonna show you very quickly how you can create an excel table and how you can disabled . So you might think that this is a table. I mean, in the true definition of the word, it does look like a table. It has borders. It has information, columns rose and so on. But it's not a defined excel table. And let me show you how you can create one. So I'm just gonna select this whole area using control shift and the arrows, or you can select it with your mouth. And then I'm going to go into the insert tap. And once I go into the inserts have notice. Here on the far left, you have this section which is called tables. And if I click table, then it inserts a table on this particular area. Notice it asks me if I want this area to be my table, I will say yes. And I will also take that my table has Heather's. As you can see, I have category, parlor, cost, price and so on, so I'll leave that ticked on because I actually have Heathers and there we go. Now I just want to emphasize. And if you've read the apartment with the slides, you know already that tables have a lot of differences from a pure data range in Excel, which it was before actually enabled dysfunctionality. So I want you to be aware that this is just not not just changing the formatting of the actual table. There's a lot of stuff that we can do, but that's how you can actually tell that. Now it's a table because as soon as I click on it, you see this design tap pops up at the top, and I can customize everything that pertains to this table. But I'm gonna show you that in the next lecture. For now, just remember that this is how you create a table so it's as easy as that. Select a range that you want to convert toe a table and then simply insert and hit table. And if you want to convert it back to arrange, if for whatever reason, you don't want it to be a table and you wanted to convert back to arrange, you either select the whole thing or right click on it wherever, when you have a cell selected or several cells selected, you go to the table option here and you press convert to range. It's gonna ask you, Do you want to convert the table to normal range? You say Yes, and that's that. Now it's a range. As you can see, the design tab doesn't pop up anymore. So that's how easy it is to Tuggle the table functionality on or off an excel. I hope that's clear enough in the next Listen, I'm gonna show you exactly how you can design it and filter it and do whatever you want with it. 12. Table Design Basics: customizing excel tables. So moving on from the past lecture now, I recreated this as an excel table. So as you can see, I have to design tap available to me again. And what I want to show you is exactly this what the design tap can do to aid you in working with tables in Excel. So firstly, what you're gonna notice is you have all of these, um, check marks here where you can do different stuff to the table itself so you can choose to have the head of road displayed or not. That's the first thing. Then you can actually choose different types of design from the right now you might notice they don't look anything special because you can choose to have banded Rose or bandit columns. What does that mean? As you can see, this world make a distinction between every two roads that it's easier for you to follow along and similarly banded columns will do the same. Now, as soon as you select one of those or both of them, you can go in this tab right here and choose a different design. So as you can see, there's a lot of appearances you can place on your table. You can choose literally what you like. Darker colors, medium colors, lighter ones and so one. It literally depends on what you prefer. One last thing that I want to show you in this lecture is the filter button. So, as you can see, one functionality that excel tables have, which is quite cool is that you don't need to select the whole table again if you had just a data range and press the filter, but it they're automatically equipped with the filter button here in the design tap. So if I click that filter button, as you can see now, I have the option to filter on all of these products. And as we saw earlier in the course, we can filter accordingly. Um, however, we want to do it and unfiltered er as we want to. So those are a few of the things that you can do to customize your tables. So I hope you can see already what the benefits are, and I'm gonna show you a few more of these in the following lectures 13. Quick Hack: Adding Total Rows To Tables: quick hack Add Inc total rose to the tables, so you might have seen this box right here in the previous lecture and wondered why I didn't say anything about it. It's because I wanted to show it to you in a different lesson to see exactly what its functionalities are, so we can click the total row and have a another rope added to the end of the table where it will total the columns that we wanted to and what I want to show in this is that Excel will actually try to look at the data and figure out what kind of some is more appropriate for it to add at the bottom. However, let's see what happens if you click on the bottom cell. As you can see, there's a drop down menu that pops up here, and if you click on it, you can actually modify what you want Excel to show. So say, for example, I obviously don't want all the profit margins in percentages to be added up. I instead want an average what's the average profit margin of all my products. So if I click that as you can see it automatically compiles the average of all of these entries here, So those are a few examples of the functionalities that tables can have. And this is a very quick one that you can use because let's face it, all of us, we do a table. We probably want a sum at the end to show us exactly how things stand. So this is a very quick way to do this with Onley the click of a button. 14. Quick Hack #15: 3 Awesome Table Functionalities: table auto fill and named ranges. Let me show you three cool things that Excel tables do. Let's start with the 1st 1 Let's say, for example, I have this table and I want to add another entry to it. Let's say I want to add an iPhone. Check out what happens when I write iPhone on the row that follows the last roll of my table. Bam! Another row has been instantly created, so Excel recognizes that you're writing data right underneath that table and inserts a new row completely. However, look what happens. It actually copied the formulas, so all the formulas that I had here have actually being copied down. So if I were to insert, if I were to copy and paste 10 different sets of values here for 10 new products, then the formulas would also fill themselves trade away. Let's say I have a new iPhone custom, and I put a cost of $200 a price of 500. As you can see, the formulas have been computed straight away. That's the first thing. Let's go to the 2nd 1 Now. Notice how these formulas look here in the formula Bar they look pretty much the same as you would expect them to be in excel, right? And the reason for that is that I had the formulas in there before I created the table. So I created same old table manually in Excel as data range. I didn't insert any functionalities for it. And then I created these two columns and I calculated them accordingly. However, watch what happens when I actually compute these formulas after I've inserted the table functionality. So I'm just gonna make this blank right here so that we don't have any formulas. We don't have anything. And then I'm going to compute the profit, which is obviously the price minus the cost. Look at it here. Look at the top and the formula bar. Now I don't have f six minus sorry e six minus d six anymore. I have price minus cost. Now that's a big difference, and that's what excel does. And it's tables. And as soon as I press enter, just look how it populates everything. Here. Everything is price minus cost, so you already have the named ranges computed automatically, but on Lee within this table, so everything is available within this table. It's not available. University in Excel. All of these named Ranges are available in this table, and I can do the same with the profit margin. I simply divide the profit by the price and I get the profit margin. And that's how easy it is. Now. The third thing is just something that it is aesthetic aesthetically pleasing. Um, on my side, I knew I like it and I'm gonna show it to you right now. Let's say I have I want you to imagine this table has 10,000 rows and we've learned how to freeze panes before. So we've learned how to keep this role always visible if we scroll down to know what each column represents. But look what Exhale does when we scroll down. Obviously, if this table was bigger, I can actually replicate it. So copy it down so that you see this exactly so as you can see, this table is replicated. Now I have a larger amount of entries and as you can see the category product cost, price, profit and profit margin, all of these columns take the names of columns A, B, C, D, and so on and you can actually filter from this column bar. How cool is that? And how much more aesthetically pleasing is it? Not to mention the fact that the functionality is great. Whenever you scroll down, you don't lose the ability to filter, and you don't lose the ability to see the top off the column. The name of the column just because your table is too long. So it's exactly like freezing pains on Lee looks even better. So those are my top three tricks for Excel tables, and I hope you enjoyed them. 15. Conditional Formatting Essentials: conditional formatting in excel. So as I mentioned, introductory lecture, conditional formatting is a way for you to distinguish certain cells via the formatting based on predetermined rules or observations that you want to make. So say, for example, imagine this table has 100 entries and I want to spot all of the entries that have a profit margin of over 55%. How am I gonna do that, exactly? Well, I'm going to select the whole range. So this is what I want a color. And I'm gonna go on conditional formatting, which is on the home tab. So as I click conditional formatting, I can either choose something that is preset. So these are the most common conditional formatting, um, patterns or I can choose to insert a new rule. Now, I'm not gonna make it complicate for now, I'm just gonna make something out of their pre set rules. So I'm just gonna go with the basic example which is greater than so. I want all the cells greater than 55%. And as you can see, this pops up and it's gonna tell me format cells that are grated done and actually read 55% because that's the first value. So that's good for me. I could enter any value here. I could enter 45% for example, and everything would light up. I could enter 70% and nothing would light up, but I can keep it at 55%. And as you can see, it already shows me a preview. And then I can choose what I wanted to color. I want I want how I want it to look like So I can choose between all of these different options that they have here the preset options. Or I can go on custom foreman and as soon as you go on constant format, this is very similar to the many day again when you want to format a cell normally in excel so you can play with what type of number it is, you can play with the font size. You can play with the borders, or you can play with the Phil. So let's just say we want to fill it with this green color So all the cells that are above 55% are going to be filled with this shade of green and as soon as we press okay, the conditional formatting sinks in, so that's the easy way to do it. Let me just show you now other ways to do this conditional formatting. So other patterns that you might choose to have, because this is just a basic higher than a certain value. Now, obviously, this is for you to explore in your own time off these different options so I can go on conditional formatting again, and I can choose top and bottom rules. And I could use, for example, the top 10 items or the top X items. However many I want or the top 10% or the items that are above the average. Let's just say I choose the top 10 items. As you can see it. Highlights hit the top 10 value wise, but I can always play with that until it show me the top five. You know, showing my five best products, the ones that bring me the highest profit margin, and then I simply play again with the formatting and click OK, that's how easier is to put this conditional formatting based on their precinct rules. But we're going to look at how you can use formulas or your own custom rules, or even go into data bars, color scales and I concepts, but that is going to follow in the next few lectures. 16. How To Manage Conditional Formatting: managing conditional formatting rules. So now that I've told you how to add conditional formatting rules to it, you might want to think of how you can remove them or customize them. And obviously, if you want to remove them, if I just added them, I could just play with the undo button right at the top, and that would solve my problems. But let's say I want to customize them as well, or simply see what kind of conditional formatting rules I have. I'll simply select the table or selective cells, which have been conditionally formatted and click on conditional formatting and manage rules. And as soon as I press that, notice how this window pops up and it will show me formatting rules for this table. So because I am in the table in the actual table, it chooses it as an area. As a selection, I could also select this worksheet. I can also select charts based data so other worksheets and so on. As you can see here, I even have some icon sets that I added earlier on. So I'm just going to go with the current selection, and as you can see, I have the top five values for this column right here. And I have the green shading. Now I might choose to edit this rule, and in this case, you go again from the beginning so you can choose different options here. You can go into formatting and choose the color that they're actually formatted with or change the font or the formatting of the numbers themselves. Whatever it may be that you want to do to it, you can actually change it here. So that's the way you actually go in there and edit the rules that you've applied so far. As far as the leading, it's pretty easy. You simply click the league rule, then hit, apply, and okay. And as you can see, the formatting rules have disappeared. We have nothing left in this table. That's how easier it is to manage your conditional formatting. Once you apply that, it's simply a matter of going to conditional formatting and clicking manage rules. Now, in the next lecture, I'm going to show you exactly how to use formulas to do conditional formatting, and you might find this has a application to your large sets of data wherever the preset conditional formatting in Excel doesn't do the trick for you. But more on that in the next lecture 17. Conditional Formatting With Formulas: using conditional formatting with formulas. So let's say you want to customize this completely. You want to use an excel formula that you know really well, determine whether you want to format certain cells in this table or not. How do we go about that? Let's take once an area. Let's see. I want to color the profit margin. If the prophet is larger than the cost, how am I gonna do this? I'm going to select the cells that I want to format attention, pay attention. So I'm not going to select the whole thing With cost, profit, price and profit margin, I'm only going to select the cells that I want to form it. And then I'm gonna go on conditional formatting and choose a new rule. Now I'm going to go all the way to the bottom here and say, use a formula to determine which sells to form A. And here I'm gonna put in the formula that I wanted to form A T cells by. So I click here and then I go into the spreadsheet and I'm gonna click the first cell for the profit. And now I'm gonna use F four toe Onley locket on the columns to remember how we use F four to lock in normal excel formulas. We use F four to lock the column, the road or both. And I'm going to see if this is bigger than this, and I'm gonna the same thing. I'm gonna lock it on the column. Then what I want to do is I want to fill it with green, and I'm gonna press, okay? And as you can see, all the instances where the profit is large into the cost or the profit margin is greater than 50% that's the same thing. Are colored with green. Everything else. This one is probably slightly smaller than this one and east, which are obviously smaller than these two occurrences are not colored. So that's how you insert formulas for conditional formatting. Now, pay attention to one thing. You only have to refer to the first cell. So if I select this whole range, notice how I went about it. So I selected the formula F six. So the prophet bigger than D six. So the cost and then excel automatically know that I'm talking about this rope and with every other occurrence it will take the next road. So if seven versus D seven and so on, and that's because I only locked the columns, I didn't lock the roads. Had I not done that, then it would have kept the first devalues into have probably colored everything there, so I hope that was clear. I hope you understand now how to use formulas. It's very similar, almost the same as if you were doing formulas in Excel, but it's a very neat way to customize what you format and what you don't. 18. Excel Hack #6: 3 Features To Take Conditional Formatting To The Next Level: conditional formatting with a twist. So last. Look at other ways that you can condition the format your cells with data bars, color scales and I concepts. So I'm going to show you the simplest one first, which is data bars. So what dates of ours will do is they will color part of your cell, and Excel will judge the value of that cell based in a relative manner on the value of all the other cells. So my top cell here might help. Value is 62.5% and all the others are colored relative in size. To this, 62.5% says against the 47.5 is the lowest one and so on. Now I cannot tell you how valuable this is when you have, let's say 50 or 100 values and you see the numbers here. But it's very hard to compare them in your brain straight away, and this graphical representation does wonders. Now let's undo that, and let's move on to the next type of conditional formatting, which is color skills so it can basically do the same thing. But with color scales. If I go into manage rules, you'll see that you can actually customize what the midpoint is and how these colors evolve . So I have a three color scale, or I can even choose a two color scale. And I have the midpoint, which is represented by the color yellow, and I put this at the 50% off 50%. All this doesn't mean 50%. In my case, it means the exact middle off my values. So as you can see, if you look for the yellow here, it's not going to be at 50%. It's actually at 55% because Excel looks at all of these values and chooses the one in the middle. So I could immediately tell that the ones that are in red are in the inferior half or third of my values, Let's say, and the ones that have a shade towards green are in the top third. Everything else is kind of in the middle, and again, when you have large sets of data, this is extremely valuable. Now I'm gonna delete this rule, and I'm gonna add the last one that I want to show you, which is I concepts and I concepts can be particularly helpful if you follow a certain value. So I just put these icon sets here. But again, I'm gonna go into conditional formatting, manage rules and double click on this. And as you can see, I can choose the icons that I'm showing exactly. And I can even customize the value at which they appear. So a practical use of this would be for me to either choose a number, so this would be an absolute number or a percentile. So having percent, a percentile or a number again, the difference is very easy. A percent. This is an absolute number, but it will be in percentage points. So let's say I'll put the green circle for everything that is above 60%. And I'll put the yellow circle for everything in between 55 60 different. Everything that is below 55. I'm gonna put the red one. And as you can see, this has been applied. But I can also choose to do it by percentiles. So this will actually look at the values again, and it will attribute the top third so everything that is above the 67 percentile of values . So 1/3 of these values are going to be green, 1/3 are gonna be yellow and 1/3 are going to be read depending on their values. That might be more or less of those according to the values. So this is how you can add a certain flavor to your conditional formatting and make it more colored. Make it easier for you to distinguish the values that you have there. One more thing with Icahn sets you can actually choose the values that you show here so I can play with it as much as I want. I can put this symbol or if I don't like that one, I can put this one. Whatever meaning you want to attribute to it, you can put it there. A practical application of this, if you ask me, would be if, for example, you have monthly trend analysis or wiki trend houses and, for example, that see instead of my profit margin, I'd have a year over year growth. And let's say that growth says 2%. Then I put the green arrow, which says that has grown and it has grown by 2%. I can put the yellow one which says it has stagnated in between 2% and minus 2% and I can put the Red Arrow for anything that has declined. That's just one example of how you can use these I concepts. They're very valuable addition to conditional formatting, in my opinion, and I strongly encourage you to use both them and the two other tools to customize your tables moving forward. 19. Excel Chart Creation Tutorial: Excel charts. Obviously, charts are a large functionality in Excel, and in this chart sheet on the Excel file they have for the section. You can see different types of charts and what they look like. And I want you to practice this as I do it and try to create discharge yourself or create different ones, but that express the data that is within those tables as you wish it to be. So what I have here is different monthly evolutions, different kind of formats. I try to choose a different chart based on that data, so make it a bit more varied to reflect real life situations. So let's look at how we can insert charts in Excel. So firstly, what do you have to do is you have to select the area that you want to represent as a chart with the headers both the column and the Row Heather. So that's very important. And then you're gonna go on the insert tab right here and at the top in the right. In the middle, you can see chart, and here you have all the different kinds of charts that you concert you have column charts . You have lying charts. You have pie charts and so on. Horizontal eso bar charts and so on. And we can choose whatever you want. And as soon as you click on the type, you're going to get, um, different variations on it in different ways. You can customize it. So as you can see here, I chose a stacked column chart, Um, which I can choose now as well. Or I can play with it Mawr and choose this one, which shows each row as a separate column in the chart. And as you can see, Excel inserts the charge straight away. You can play with it, resize it, do whatever you want. You have the indicators. Here's the legend. You have the months here and the values to the left. So this is how easy it is to insert a chart. Now I'm gonna show you in the next few lectures how you can customize it, how you can make it to look exactly as you want it to look. But for now, just get accustomed to creating a chart. You can also play with source of this chart. So as you can see, I can drag, um, these little highlighters. Let's call them up and down, and the charge will modify automatically based on the source data that I choose. As you can see, the chart modifies as I move this area around. That's the basic way of creating chart, but I'm going to show you how to customise them and had to choose exactly the right type that you need in the next few lectures. 20. Quick Hack: How Do I Know What Type Of Chart To Choose?: how to choose the chart type that you need no money. What I would say when you want to choose a chart is look at your data and establish how you wanted to look. In this case, you know you want four different entities or one, depending on how you want to show the information and 12 different months and you can go on the insert tab and see exactly how this would look like. So preview the charts is very easy to do just that, and you can see exactly, very quickly how the chart would look like and how your data would be shown. That's the easiest way for you to go just by having this quick preview. As you see, some of them work. Some of them don't work. Some of them show interesting stuff. Some of them don't. And this is how you can tell which chart is for you. Now, For those of you that are using Microsoft Office 2010 or 2013 we have this feature that is called recommended chart, and this is really interesting because if you select the source data, so I selected the table that I want to create a chart from on I press recommend the charts . You can see exactly what kind of charts Excel recommends, and it even has description below that, telling you exactly what every type of chart shows and why you should choose it. So this is very interesting to me because this provides you a snapshot straight away. These are the 56 for whatever types of charts that you could use for your particular data. Have a look to them, see what their advantages are and choose them accordingly. So this is how, in my opinion, you should choose your charts and excel. The fastest way is to look at the recommended charts from Excel, in case you don't have an idea already the longer way, but still quite intuitive, is to simply go in the charts section on the insert tab and look at all of these different types of charts and what they would look like. You don't have to click on them. I'm just hovering with my mouse over them and see which one suits you best 21. Customising Excel Charts: customizing an Excel chart. How Exel offers you a lot of functionalities to customize your charge. You can literally make them look like you want them to look. And I'm gonna leave this to you to experiment a bed because you know better. What? Your charts, what you want your charts to look like. But I'm going to show you how you can customize them. So as soon easy click on a chart. Notice how you have this design and former type appear on the top ribbon. And here you can do a ton of stuff. You can add chart elements. You can sketch a quick layout. You can choose different charts style. So as you can see, I click on them and the chart changes entirely. Um, even do something like this. You can change the color so it against you have preset colors chosen by Microsoft that you can use the tailor, this particular chart and so on. Now, the other changes that you can do regard not only the way it looks but the data itself so you can switch the row and the column data, or you can select the data itself. You can change the chart type and so on. This is what you can do from the charters. I'm going very quickly through these because in terms of the data, I think the chart itself provides better functionalities. But I'm gonna explain that in one second. Firstly, I need to mention that you also have the former tap where you can play with it as you want . This is very similar to the normal format tab O R section that you have when you customize cells in excel normally without going into the chart functionality. Now let me show you what happens when you click on the chart. You might have noticed that you also have three buttons to the right, so the 1st 1 is chart elements. And with these elements, you can customize a lot of stuff so you can customize the axis. Do you want them to appear or not? Simply take them and untech them and you can click the arrow to see exactly what that entails. So I can even put the titles for the charts. I can put the chart title or not, and I can put it centrally. I can put it above the chart as it is here, or I can choose more options. And he can simply when, whenever you go on more options, you can simply customize everything at shadows, alignment, all of that. It gets extremely complicated. I don't really bother with it normally because part of it is because I don't really use Extra Excel charge that much. I use thing cell to create charts and PowerPoint, but I think it gets a bit too much detail heavy, so I'm not going to go into that. So this is how you can add different elements to the chart. You can add grid lines, data tables, error bars, the legend, have it there or remove it. You get the idea my suggestion would be. Play with this and see exactly what you can add to it, how you can delete it, because it can be insanely valuable to the data you want Express. Next up is chart styles, and we've seen this on the top bar as well. But it's a bit easier to me. It's a bit more intuitive because I don't have to go all the way to the top. I can just change the style of the chart from here and I can also change the colors of the same colors that we had on the top bar. We have them here as well. Next up, the most interesting part of this is filters. So remember how I told you you can play with the source data by just modifying those ranges there, just dragging them up and down a few lectures ago. Well, with the filters, you can actually customize what data you want to show and what they tell you don't want to show. So, for example, I can choose to have Asia Pacific in there. And, as you can see now that Asia Pacific role which wasn't there prior to this has appeared. Or I can just choose to show Europe and North America hit, apply. And that's that I only show Europe and North America. How about me showing Europe and North America for the first half of the year on Lee? Then I just diesel, like those six months hit, apply, and I'm left with this. So that's how you can customize your charts. And as I said and I mentioned already, you can customize both the elements. You can customize the formatting and you can customize the data inside of those trust the data that has shown. So it's quite a lot. It's a lot of. There's a lot of options in there, and I'm sure you might be a bit confusing. Might get lost in them. But the easiest way to learn off this is to do your own chart. So do one. Right now. Try to do one the same way as mine or choose different type if you want to, and just start playing with it. Start seeing what every button does what everybody and adds to a try imagining war kind of data you would want to show. And after you do that a few times, you get more accustomed to it and you're gonna know exactly in the future how to tell your charts in the fastest possible time that they express exactly the data that you want them to express. 22. Excel Hack: How To Make Snap Judgements On Large Sets Of Data: Excel hack spark lines. So this is one of my favorite features in Excel, and that's why I call it a hack. And let me tell you why you would use spark lines before I show you what they actually do. It's a bit of the other way around, but just roll with it and you'll understand. Let's say, for example, you don't want to create a chart to understand all of this data here. By the way, I'm on the base data sheet in this Excel spreadsheets. What is this sheet in the middle? Let's say, for example, I have a ton of data here and, you know, maybe you're exposed to the first time, maybe changes all the time. You just want to get a feel of the evolution of these numbers. You want to see how they go month to month now. Obviously, that's what charts are for. But charts are usually something that you show someone else or you used to do extensive analysis on, you know, the occupy a ton of space. They occupy space in your spreadsheet. You have to maintain them. You have to update them. It can get complicated. What if you just want a quick snapshot and just say, OK, I want to see what this train is like a 14,000,015 15 16 17 18 18 1919. Okay, it seems that it's growing. But if I just start and read every single cell here and for all the other rose, not to mention if I have a larger table, it's gonna be very, very long winded. So here's what I'm gonna do. I'm just gonna select this data here, and I'm gonna add spark lines and I'm gonna add a line. So this is my data range, right? So it's this whole table here, and I'm gonna put the location right there and as soon as I press okay, Sorry. I need to select a range that is similar to that one. As soon as I press OK, notice how excel puts lines. This looks like a heart beating actually puts lines that show the evolution of this data. Now for the 1st 1 this is my initial guessed that it's a constant growing line. But look how interesting the other ones become because I see exactly how they vary from month to month and again this can only be used. What? Sorry. One of the functionalities is that it gives you a quick snapshot of what happens there. Let me remove the spark line. And after I remove it, I'm gonna add the other type of spark line that is available to me. So I'm going to select this whole data again, and I'm gonna go on the insert tab and at columns and actually prefer columns. So for this data here, I'm gonna add the columns in this area here and look at look at them. How nice they're looking, How nice they're. So I contract this column and make them a bit more white spread. And again, this is a bit like the date of bars in conditional formatting. I see exactly their size relative to each other. So for all of these, I see how they grow, how they go down. But this also shows me the size as opposed to the line sparked line, which only kind of showed me an evolution. It didn't really take into account the relative size of all of these cells. So I think spark lines are an extremely important functionality for you to play with a free half certain tables like this where you just need, you know, a snap judgment. You just need two seconds to look at that table until Okay, this trend looks OK up there. Is something wrong with this one? Um, let me investigate further or just the results and say, OK, this is surprising. Let's dig a bit deeper. So that's why I think spark lines are important. And I strongly strongly encourage you to use them in your day to day Excel activity.