Excel Hacking I - Manipulate Data & Master Formatting (Beginners Intro) | Dragos Stefanescu | Skillshare

Playback Speed

  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x

Excel Hacking I - Manipulate Data & Master Formatting (Beginners Intro)

teacher avatar Dragos Stefanescu, TeacHack Founder - Teaching 1000s Online

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Lessons in This Class

24 Lessons (1h 17m)
    • 1. Excel Hacking Series Promo

    • 2. Getting Started & Auto-Fill

    • 3. Getting Smart With Alignment-Based Formatting

    • 4. Customising The Format With Borders

    • 5. How To Effectively Manipulate Rows And Columns

    • 6. Quick Hack #1: Insert Several Rows More Effectively

    • 7. Beginners' Guide: Cell-Level Formatting

    • 8. The Fundamental Excel Tool You Must Be Aware Of

    • 9. Quick Hack #2: The Double Click Trick

    • 10. Use Hide & Group To Your Advantage

    • 11. Quick Hack #3: Effortlessly Select The Whole Sheet

    • 12. The Tool To Effortlessly Replicate Formulas & Values

    • 13. Quick Hack #4: Auto-Fill... With A Twist!

    • 14. The Easy Solution To Transposing Your Tables

    • 15. Excel Hack #1: The Essential Functionality I Heavily Used To Build Spreadsheets

    • 16. Quick Hack #5: Searching With Wildcard

    • 17. How To Make Sense Of Data-Packed Tables

    • 18. Sorting Efficiently In Excel

    • 19. Quick Hack #6: Fast Navigation With A Few Keys

    • 20. Ultimate Guide: Filtering

    • 21. How To Get Rid Of Duplicates In Your Data

    • 22. Using Subtotals To Your Advantage

    • 23. Quick Hack #7: How To Copy Visible Cells Only

    • 24. Excel Hack #2: Introducing Flash-Fill

  • --
  • Beginner level
  • Intermediate level
  • Advanced level
  • All levels
  • Beg/Int level
  • Int/Adv level

Community Generated

The level is determined by a majority opinion of students who have reviewed this class. The teacher's recommendation is shown until at least 5 student responses are collected.





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:

  • Master the very basics of Excel - formula writing, formatting cells, manipulating rows & columns
  • Using the Format Painter, Hiding & Grouping for better visibility
  • Become an expert in fundamental functionalities such as Find and Replace, Auto-Fill, Copy & Paste Special to perform operations faster & more accurately
  • Ultimate guide to Filtering, Flash-Fill, Sorting ...
  • ...and much more!

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

Meet Your Teacher

Teacher Profile Image

Dragos Stefanescu

TeacHack Founder - Teaching 1000s Online


About Me

I'm an Internet Entrepreneur with a background in both the Management Consulting & Telecommunications industries. The skills that I have acquired during those stints and in previous years in general have unleashed my passion to teach.

I have a wide array of skills that I share with you via my courses

(1) Social Media

One of my true passions, I have invested a lot of time building a presence online. Highlights include:

Creating a connection base of 100+ people in a large Management Consulting firm within weeks of starting the job. Met with a handful of Managing Directors & Senior Managers after approaching them on Social Media Trained Partners on how to effectively make use of LinkedIn & Twitter to network and generate leads Conducted Workshops wi... See full profile

Class Ratings

Expectations Met?
  • Exceeded!
  • Yes
  • Somewhat
  • Not really
Reviews Archive

In October 2018, we updated our review system to improve the way we collect feedback. Below are the reviews written before that update.

Why Join Skillshare?

Take award-winning Skillshare Original Classes

Each class has short lessons, hands-on projects

Your membership supports Skillshare teachers

Learn From Anywhere

Take classes on the go with the Skillshare app. Stream or download to watch on the plane, the subway, or wherever you learn best.


1. Excel Hacking Series Promo: 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. Getting Started & Auto-Fill: in putting values into cells and auto fill. Welcome to the first content based lecture in this course, and I just want to take you from the very basics. Okay, So even if this seems very simple, I hope that you can discover something new in every single lecture. So I just wanted to show you for the ones that are excelled beginners, how you can input values into cells. There's basically two ways you can just go on the cell and just input a value. For example, I can put two. And as you can see, this cell has a two inside of it right now. Or I can go to the very top here at the formula Bar. And this is the main reason why I'm showing you this because people I don't usually know that they can input stuff from the formula bar, and this would be extremely helpful when we want to put in very long formulas. Okay, so this is going to be extremely helpful whenever we decide to do that. But more on that later, the main focus of this lecture is gonna be also fell. So this is one of excels, most intelligent let's say features and I want to show it to you right now. So when you go onto a cell that has content, insider has a certain value. You'll notice here in the bottom right corner that there is a small square, and the cursor changes when you actually go there. Now, if you click this and you drag it down, it is going to replicate the value as far down as you drag it, and that can apply to the right or to the left. Basically, any direction you want to do it, you can actually, um, put all these values on their very easily, and the same can apply to numbers or really, whatever value you have on there. I'm just undoing all these changes that the spreadsheet remains tidy. But here's where auto fill really becomes intelligent. If you have a sequence 123 and you start dragging it down, Excel automatically detects that you want to count numbers and it will go all the way down to seven. It will go even further down if I drag it. As you can see, it goes all the way down to what I tell it to go and this is useful in itself. But what if I have a certain value? Let's say you have a database with names and you just want to number them, OK, you just want to see every single row in that database. See how many of them there are. Well, you could have them all here. Imagine these could be 50 100 rose whatever the case may be. And if you select 123 you can double click on the right hand button, the bottom right cursor change changing corner, and if you double click on it, it will automatically go all the way down until there's an empty row says you can see it went all the way down here. I'm just going to show you that one more time that you get the hang of it so it goes all the way down to seven. But Excel doesn't stop there. It also the text patterns in months. So, for example, if it sees January could just drag it down. And as you can see, it continues onwards, all the way to December. It even recognizes if I put Jan, which is one very common abbreviation, and I can go all the way down to December, and then it goes again, January and so on. So these are a few important tricks and excel that if you're a rookie and excel, you probably don't know the one thing that you need to pay attention with. This is as you can see, there's no particular formatting in what I did here. Everything is called and white. All the cells are called in white and there's no borders or anything like that. But you're fine. As if use this approach with tables, then you might encounter symptom problems. Let's say I want to drag this iPhone valued down through every single roll of this table. Now, as you can see, the formatting changes in a very weird way, and the explanation for that is very easy. This took the top border from this cell and applied it here and here with no bottom border , because the initial sell didn't have anything, and the same thing would happen if I drag down the price. So this is one very important factor. When you dragged down, it will drag down the formatting as well, so you always need to pay great attention Now I'm gonna show you how to replicate these values in Excel without missing with the formatting. But that's another lecture. For now. I hope you're comfortable with auto fill and in putting values into cells. This is just a very, very easy beginning, and I'm looking forward to seeing you on the next lecture. 3. Getting Smart With Alignment-Based Formatting: alignment based formatting in excel. So in this lecture, I just want to show you the basic formatting that you must know an excel. Now, as you can see, I have a table here, and it's very basic. Okay, Again, I don't have any borders. I don't have anything of that sort. Just pure information. Now, as you might observe, this isn't particularly, although, although is quiet in good order, it isn't a particularly nicely formatted table. So I just want to take you through a few features. If we look at these cells right here, as you can see in the formula tab that I mentioned in the previous lecture, we have a lot of text which is not visible. So for that to be visible, we can play with feature that is called wrap text, and you can find wrap text on the home tab in the alignment section. If I press wrap text, as you can see, it will expand the cell to get all the text visible. Okay, so then I can drag the column from here from the very top. As you can see, the cursor changes and Aiken drag it all the way as much as I wanted to be. And then, as you can see, the Roe is expanded as well. And if I simply double click on the bottom, it will reshape so that it has everything in view and it doesn't waste any space. Now I can play with us a bit more and aligning again, and then I can do the same thing with the other three columns. Okay, so I press wrap text here. As you can see, everything happens similarly, and then I can zoom out and I have all the values here. Now that we've mastered wrap text, what can we do to make this for my team? Seem a bit better? Well, firstly, Weaken sensor, everything. You probably know this from Microsoft Word. You can center based on, let's say, the horizontal axis and the vertical one. As you can see, everything now is very nicely censored and weaken center the indicators as well. Now that we've done that, you might notice that the performance indicators okay, we have sales, customer service and admin, but the title is put on column D. Now I could make it like this and that it would kind of resemble that it has all three of them, but it still doesn't quite do the trick. So what we do and this is a feature that I use a lot, is we select the three cells and we press, merge and center. And once we do that, it's created sort of a master sell, let's say, and by this being centred, As you can see, it occupies the space very nicely, and you can see the performance indicators, sales, customer service and admin. Now. One word of warning with that is, if you have cells that have different values here, let's say it puts something here. If you merge the cells, those values are gonna temporarily disappear. As you can see, Excel actually shows you this warning message that they will discard the other values. And as you can see, this kind of swallowed everything else. And then when I unm urgent, the values have disappeared. So that's one thing that you should pay attention to when playing with the merge and center feature. So those were two very important alignment based four mining tools. It's the very simple tools, but you need to be aware of them because, as you might have seen. As I might have mentioned so far. I'm a formatting freak, so I want everything to look absolutely perfect. So these tools, I think, are essential at a very simple level for you to use to make your tables and the information that you put in excel look good. 4. Customising The Format With Borders: adding borders toe are four munching. So we're back at the same table on the formatting sheet right here. And I just want to show you very quickly how you can add borders to this. So there's two ways you can actually do this. You can do the quick way by using this bottom right here on the font tab on the home steps on the fund section in the home tab. And I'll show you this right now. So if I select the whole table, I press this button right here. So the drop down one, you can see all the types of borders they can select. I usually go for the thick box border whenever I put the outside border on the table. So, as you can see, we have it there. We can merge these columns here and add a border as well, and here as well, and I would look all nice and tidy Now, the second way you can actually do this is again you select the area, you right, click. And when you right click, you have this many that pops up and you have the button here that says format cells. Now we're gonna look at this later because you conform a every aspect of the cell. But what we're interested right now is going to the border tab right here. And as you can see, you can choose all types of borders. You can choose the coloring on dso on, so this is extremely helpful. Obviously, you don't have to go too much in death on this. But what I usually do is on in the middle of the table. I would put one of these ones that are a bit more discreet. So in this case, I could put horizontal one. As you can see, I pressed here and between these two squares and a vertical one, and as soon as I press okay, As you can see, the whole table has been formatted, has an external border, and it has an internal one as well. So I hope that's been helpful. I try to keep it very short, As you can see right now, this table looks much better with just one minute of formatting added to 5. How To Effectively Manipulate Rows And Columns: inserting and deleting rows and columns, so hopefully have done the same changes that I have so far. So your table should look something like this by now. So let me show you how you can insert Rose. For example. Maybe we want to insert something after sales and have another indicator to review our employees after. So what you would do is you would select the column from the very top sort. Press the E button right here, right click and press insert. And, as you can see, it inserts a column right before the customer service one. Now the other formatting thing that bugs me on. Before I go into that, I can show you how to insert a new role as well. So does the same thing. The other thing that bugs me is that this table doesn't quite look perfect. You know, it's nicely formatted, but things don't we seem equal. It's not tidy enough. So if you want to delete these blank rows, I simply right click and press delete and do the same here, and I'm back to my initial table right here. So how can I make this look exactly the same? Well, I could try reshaping it automatically. So if you double click on these borders between the columns right here or in between the rows, as I showed you in some of the previous lectures, it will automatically resize the role or the column based on the longest character here. So if I double click here, as you can see, it just takes enough to cover the sales that was written on top. Same for customer service and same for admin. But that doesn't quite do the trick for me. So what I want to do is select all three of them and then which ever one wants to reshape, I can do it and it will set the same size for each and every one of them. So right now, because customer service is my longest one, I'll take customer service. I'll drag it down and just tested a bit. And as you can see, it's actually longer now, even though I made it shorter because the other ones gained a bit of with as well so I can play with this as much as I want. And as you can see right now, it looks pretty good. It looks very tidy. And I can do the same with the rose right here. So if I just dragged us down a bit, everything now is exactly the same size. And I think you might agree that this looks much better than before. 6. Quick Hack #1: Insert Several Rows More Effectively: quick hack, inserting more than one row or one column at a time. So a lot of people make this mistake. And if they want to insert several rows here, they'll just right click insert, right click concert and so on. Let's say they want to do five rows, and that's very time inefficient. So I want to show you the very quick and easy way to insert several roles at the same time . So let's say I want to put seven MAWR employees right here. I'll just select this row, go all the way down to insert seven rows. I think there's seven here and then I'll right click again and press insert, so it's exactly the same. But look what happens when I do that. Bam seven Rose Just like that, and I can do the same for calm. So I want concern something after sorry before customer service after sales. Four more columns. I press insert. Just look how easy everything becomes. And now when I zoom out, you can see the whole table right here with the extra rows and columns. So I hope that was very useful. That was a very, very quick hack and From now on, whenever you want to insert more than one rope or one column at a time, simply use this method and you're going to be much, much faster than you were before. 7. Beginners' Guide: Cell-Level Formatting: cell level formatting. So if seen, how we conform it everything around the cell. What if we actually want to format the contents? Okay, so the actual values that we have inside, we can do that very easily now, Firstly, if you want to color sell, the easiest way to do it is you might have find out from the top in the font section on the home tab, you can simply select the color right here. Now, apart from that, you can also right click and go to format cells. And if you go to the fill tap at the very top, you can do the very same thing. So that was a very quick run through the coloring. Now, what if I actually want to customize these numbers? We'll also you from now the most common formatting types for values and cells are numbers, currency dates and percentages. And I'm just going to show you each and every one of them very quickly. So, for example, the numbers here, as you can see there already formatted. Okay, So if you go to number tab and you choose number, you conform at them as you want, so you can customize the number of decimals because we have student numbers. I'm just going to keep this at zero, and you can choose to put the separated the 1000 separate in there. As you can see, D cells already have it, or you can be selected. The same thing goes for negative numbers. And if you do accounting in excel, then this will be particularly useful. Now, I've taken out the separator, and if I press okay, as you can see, the numbers have been reformatted. Now the same thing applies here. And as you can see here, I don't have the separator in every single cell But here because I have revenues, I'm going to choose the currency. So if I click currency, I have the same options. I'm gonna leave two decimal places because the revenues, obviously they can have this whole place is and you can choose which symbol you want to use . So I can choose English United States, and it's gonna be shown in dollars. And basically that's what it will look like now moving on to dates. I've already formatted these dates. As you can see here, that's how I've impotent the values. But with dates. It's very, very similar. So if I go on format cells, I go on a date. This is actually a custom format that excel automatically applies because it has some built in custom formats, but you can actually change that. So if I go to date, as you can see, it can also calculate the day of the week when that was when that took place. And I have all these different formats that I can choose. So I'm just going to roll with this one because I think it looks cool and I'm gonna expand the cells, so that shows everything. Finally, I have a year on year growth right here for students, and these should be percentages. As you can see, there's a formula which divides these two cells and shows the year on year, um increase or decrease from 2014 to 2015. Now, if I want to make this a percentage, I apply the same process. So I go in formatting number and percentages, and here I can Onley customize the number of decimal places. Let's say I want to put one and Viola everything stays exactly the same. The same values, but now they're shown in percentages. Finally, I want to show you a very quick tip that I personally didn't know for a few months when I was working with Excel, and I saw some people do it, and I thought it was very interesting is if you look at this section right here, which says number and particularly these two buttons will increase the decimals in your formatting. So say, for example, here you want to have three decimals instead of right talking, going on format, going on percentages and change this most from there. You can literally condense them by pressing that button and expand them by pressing this button, and this can apply to the revenues as well. So I can actually condense the revenues and leave no decimals or play the same way with the number of students. So I hope that was helpful. And now you have a good idea about how to format numbers and values that you have in different cells. 8. The Fundamental Excel Tool You Must Be Aware Of: the format Painter is one of the most underutilized features of Microsoft office in general and of Excel in particular and PowerPoint. If we're talking about that and what the former painter does, as the name might just is, it changes the formatting from one cell to the other, basically copies every element of the formatting. So it's gonna copy the borders, detects the way it's formatted, the type of the text and so on. So let me show you exactly how the former painter works, and we have three distinct scenarios for this. The 1st 1 is if I click on the former painter once and on one cell, then I consider pick Click somewhere, and it will apply the formatting or I can selected again and drag it all the way on the table. And as you can see, it won't change the format, see for every single cell and make it the same. Notice how the dates have changed into number formatting, so that's what I meant by the fact that it changes every single aspect of the formatting. Now, a thing to pay attention to is if you select more than one cell and you want to use the four month painter because if I say like this whole table and press the format painter and try to copy it here, it will Onley copy the formatting for the first for cell. However, if I select the whole table here and then drag it on the whole table here, as you can see, it will replicate it exactly as it is above. Pretty nifty, right? Pretty efficient. But here's where it gets really efficient. Let's assume you have 10 such tables that are not formatted. Or maybe you've just changed the for my team for the one on top, and you want to copy it to all of the others. Are you going to select it and press the format painter again and again and again? 10 times? No, actually, if you double click the foreigner painter, it will still stay selected. So similarly, you just select the whole table. Let's assume I have one here and one here, and whenever you don't have data on there, you simply have to click. And as you can see, I'm just clicking and it's applying the formatting to every single table, every single empty space that I find scrolling down so you could build tables that look exactly the same by using the format painter and absolutely no time. It'll just take you a few seconds. One final note with this is if you want to get out of the format painter. So as you can see the Kherson now has the former painter activated simply press escape on the keyboard. So if I press escape now, as you can see, it's disappeared and it goes to the current selection so well. Means if you're not aware, if you've not been aware of the former painters so far, use it because it can save you a whole load of time. 9. Quick Hack #2: The Double Click Trick: quick hack the double click trick. Let me show you one of the features that excel sets by the fault and that you have to change if you want to become serious about using formulas in Microsoft Excel. So as you can see right here, I have a table with sales per quarter courts of 1/4 to to quarter four and this is on the double click cheat. So I hope you're following along in your under DoubleClick sheet. I also have a sales database, and basically all of these figures here for these products are taken as a some. So the some formula from the sales database don't worry about the actual formula, we're gonna learn about him and you time. So if I go here, I actually see that this is the sales database from D 62 F six, which are these three sells right here. Now this is a difference. And this is why this trick is important because, for example, this cell here might have wondered what it is. I just wanted to show an example of what it would look like if it's on the same sheet. If it says G nine and I press the shortcut f to which you have in the pdf shortcuts, which will follow shortly in the course. It will show me that this cell, it's taking the value from G nine. So that's pretty straightforward. And that helps me a lot. But excel spreadsheets are not that simple. And you're gonna be confused with a lot of formulas and you're not gonna know where some of the formulas are taken from. Now, let's see what happens if I actually double click this cell. As you can see, it takes me to modify the formula, which is not particularly useful for me if I want to track down that formula and see where it comes from. Therefore, you need to do the following so click file on Toff and go all the way down to options. As soon as you press options, this pop up menu is gonna come on your screen. Now, if you press advanced on the left hand side, you're gonna notice. End these. You know this list of options this row here, which says allow editing directly in cells. So that's what happens right now. You double, we're gonna sell and you can edit the formula. Now we're going to announce sick that impress. Okay, now look at the difference. And look at what happens when I double click on this cell right now. Bam! It takes me straight to the sales database and highlights the cells that are used to compose that some and again. I can go on every cell here, and it will show me exactly where it goes. I use this to check a lot because I check my spreadsheets. You know, you can make errors of formulas all the time. So if you have this enabled, you can quickly check that you haven't made a stupid mistake. And taking instead of, you know, October to December have taken Sept to November and so on. And this is crucial in my opinion, to you working in an error freeway and excel. So if you paid attention, do this right now going to file de select that option. And I hope this is gonna make your life in excel much, much easier. Moving forward 10. Use Hide & Group To Your Advantage: hiding and grouping rows or columns and Microsoft Excel. So for this lecture, we're going to use the sales database v two and for this example, in particular, I've taken a sales evolution from January all the way to December for three products, but it's actually split for every month between actuals and budget. Now let's see me don't want to see this whole picture, okay? You don't want to see the whole sales, the whole actuals and budget, because the truth is, there's 24 columns, and it's very hard, unless you have a very big screen to see all of them at the same time. What can you do about it? Well, there's two things. The 1st 1 is. You can select a column you can right click, and you can click heart. And as you can see, it's hidden and you can see column E here at all. You can do this for every single cell, and then it's going appear very nice and tidy. However, what I don't like about hiding columns or rows is that for you to recover them, you either have to right click and click on hide, or you can drag the cell from here and just put it and it's former place. Now, the problem with this is that you might not see that the cells are hidden there. You might forget the spread should might become very complicated. And for that reason, I prefer grouping. And as you can see, we go on the data tap right here and all the way to the far right. You have the outline section where you have the group button and let me just show you how this works. I simply click on that column and group it. And as you can see, this thing pops up here, let me just group product Why as well. And this one will pop up here as well. And I can do the same for every single budget. Um, column in here. This is me assuming that where at the end of the year or the beginning of the year in January, we have all the data from the previous year, and we just want to hide the budget because we don't want to see all of that information. If we want to see it, we can expand it, and I'm going to show you exactly how to do that right now. So as you can see all of these symbols of here, here on the top, and if you click the minus button, it will actually collapse them. And you can do this for each and every one, and you can do it for the road as well as you can see, This is much Tidier and much more organized, because you always know that there's a cell that is grouped right here and the short cut it . This is to press the one order two buttons for both rose or columns. So if I press the one button, that's the compressed form. If I press the two button, that is the expanded form, and that's the quick way of you grouping and actually making the cells visible or not visible in their particular groups. Now, if I want to ditch all of this, I'll simply select all the rows or I'll simply select the row that is grouped right here and press on group. And as you can see now, when I drag it, it's still there, and I can do the same for all the ones here. Press on group and they're all hidden in between these cells right here. So that's grouping and on grouping. I think that if you use very data intensive spreadsheet, if you have a lot of information there and a lot of it you can actually compress, then this is a feature that you need to pay particular attention to and get used to it because it can make things much, much easier for you in the long term. 11. Quick Hack #3: Effortlessly Select The Whole Sheet: quick hack selecting the whole sheet. Now you might have instances when you want to select all the data on one sheet, and I'll give you a practical example for this that I use all the time. You might have noticed that all my spreadsheets are blanked out. They have a white background as opposed this classic one from Excel, and I think that just looks much better than this Excel, and I'll show you exactly how you can select the whole sheet. It's very easy. You might know the shortcut, which is control, and a on the keyboard that selects the whole sheet, and then you can blanket out. Now that's one way to do it. And that's the universal way in the whole of Microsoft windows. But the other way in Excel in particular, is to go into the top left corner right here between the one for the row and the A and pressed that button and now automatically select a whole sheet, which you can then blank out, enjoy 12. The Tool To Effortlessly Replicate Formulas & Values: using a copy and paste special. So again, this is something that some people don't use. I think most people don't use and I think it's extremely helpful. And remember, in the first section, when I talked about also feel and how also fill actually drags the formatting with it. This is the remedy for that. So what is Copy and paste Special? Basically, if you copy a selection like I'm doing right now and then you go here on the paste button on the top left of the the home tab, you're gonna have all of these different options which are pasting special. Or you can click pay special and have this menu pop up, which shows exactly what aspects you can copy from that particular selection so you can choose formulas, values, formats, comments, all using source things, all of these different options. You can even perform operations and even transport cells, but we're gonna look at all of these a bit later. The other way to do this is to right click where you want to copy it to say, this is the first cell where I want a copy. And again I have all of these options here, or I can go on pace special, and I end up with the same menu. Now. Why is this important? And what are the most used paste special commands? Well, I would say there's two of them pacing the values that you want to replicate and pasting the formulas. Now, if you get this as a reflex, if you incorporate it in the way Working Excel, that's gonna help you tremendously. So let's stay here for the sales evolution. I want to copy the month names that are on top. So rather than just clicking, you know, writing February here and then dragging everything so that I mess up the formatting, as you can see. And then I have to take all those bars in the middle away, which doesn't take too long, but it's still not tidy enough. I could just select everything from February to December. I just had control, See to copy. I go in the first cell, I go on pay special, and I choose values. And there we go. Nothing in the former team is messed up. The alternative. The shortcut is to just click on this button here, which, as you can see says values, and it does exactly the same thing. Now finding what I want to show you is I have a formula right here that calculates the sales evolution for different products. Now I could do the same thing. I could drag this down and that dragon all the way to December. And there we go have all of the formulas off the values. Um, also filled. However, As you can see, the formatting is not quite right. So if I do this and I simply copy this formula right here, select the whole table right click. And I can either go on pay special and click formulas, or I can just go on this shortcut right here. And as you can see, it pastes all the formulas, and it doesn't take the formatting with it. So now what I have to do is either use the four my painter, but that would take the borders or simply go right click format cells. And then when the menu pops off, just hit percentages one decimal, and then everything is exactly the same. So that's copy and Paste special, a very important feature that you should start using right now if you haven't done so up to this point, 13. Quick Hack #4: Auto-Fill... With A Twist!: quick hack copying with also fill. So remember how I showed you in the first section how it can use also feel to basically let excel figure out all the values that we want to complete. And as you can see here, I'm dragging January all the way to December and I have all 12 months and I can do the same with numbers and so on. However, what if you actually want to copy these values across and you don't want them to progress further? So what if I just want January, February, March, January, February March? For some reason, then the answer is you should hold the control button. So right now I'm clicking the control button on my keyboard and notice how the cursor has changed. So look it, Look here. There's only one black cross. But as soon as I hit the control button, there's a second, smaller black cross right there. And now if I drag it, it does January, February, March. But then every sets to January, February, March, and I could do the same Here. 456 is going to repeat itself all the way to the end, and then may June July. For whatever reason, if I want to do this, I hold the control button and it will just repeat itself. So that's a very useful feature, very useful way off twisting auto fill to your convenience. So just remember any time you want to copy the values rather than let them progress, just hold the control button on the keyboard and dragged them happily as far as you want to . 14. The Easy Solution To Transposing Your Tables: transposing with paste special. So right now I'm in the section three XL on the transposing sheet and what I want to show you here is you know, those situations where you build a table just like I did with this one here. And as you're building and you realize, you know, for the data that I have, this will be much better if it were the other way around. So if I had the months as Rose and the products as columns in this particular example and I'm sure you've encountered this because, you know, tables are most of times, they're either, you know, they go along horizontally or vertically. They're barely, you know, in the former for square. So these kind of situations will occur. And you're gonna wonder, how can I actually transpose all of this and maybe even keep the formulas and put it in the format that I want? Luckily, we can achieve that with copy and pay special. Now, if I just like the whole table and copy it, I'm gonna show the short way of transposing this whole thing. So I'm gonna go to the cell where I want the future table to be placed in. I'm going to right click, and I'm gonna choose transpose. Now this is so good. But as you can see, it's still messes up the formatting because it takes the borders as they were there. And it's very hard for it to realize that the format is not good. So this doesn't quite do it, although it's the fast way of doing it. So what I'm gonna do now, I still have the table selected. I'm going to right Click, choose Based Special and have this menu pop up. And now what I can do here, for example, is I can choose values and Aiken take transpose right at the very bottom. And here's how it looks now. It's just taking the values and all the names, so now it looks much better. Now I just have to form at this table, and it's going to be exactly what I want. Pretty cool right. However, we can even take this further and make the table look better just with one click. So if I go again on pay special now, I can see like all except borders, because remember, the borders were the thing that messed up my formatting in the first place and then also choose transport. And when I click OK, there we go. It looks absolutely perfect. The colors are really good and it's being transposed. Now I have the products ask columns and the months as rose. So everything is exactly as I wanted to be. So remember this quick hack of using paste special and using the transports function because Excel has all these inbuilt tools that you can use to manipulate the data as you want. And once you mastered them, you can literally create the spreadsheets and designed them exactly as you please. 15. Excel Hack #1: The Essential Functionality I Heavily Used To Build Spreadsheets: excel, hack the find and replace functionality. Final replace is something that I use a lot when I build spreadsheet and it saves you a lot of time. I remember a few years ago when I actually discovered it, and, you know, this is a simple example, But when you have tremendous, tremendously big spreadsheets with thousands of rose, this can save you a few hours easily if you know how to apply it. When actually discovered it A few years ago, my mind was blown because I couldn't believe how easy can be with this built in functionality. So let me just introduce you to it. First of all, and you're gonna see this in the pdf with the shortcuts you can bring up. Finally replaced by pressing control. Hey, h on the keyboard. So control hate will bring up. Obviously, if you if you press control f, you're gonna be on the find functionality and the replace. The final replaced functionality can be brought up with control. Hate. Now, the most important thing that you need to know and I want you to set this in your head right now is that whenever you do find the replace it's gonna replace everything from the cell values to the actual formulas. Okay, so you have to be very careful. I'm going to show you examples of how this can go wrong, because you might be trying to replace a certain character. Let's say for a column So you want to switch from the formula Calculating column e two column D. But if you only try to replace the letter E, then if you have a formula, let's say, for example, indirect in that same column. It will replace the E and indirect, and it'll mess up your formula. So you need to be very, very careful when you use this. So let me just show you firstly, how the most basic application, how it can actually replace words. So let me say I select the whole column. B. I press control, hate and for some reason I want to replace Mac book with Apple. Now I just hit replace all. And as you can see it says Old Done made 11 replacements. And as you can see, all of these columns here that had Mac book as a word are now apple. That's the simplest way you can implement this, but let's look at how he can change formulas. Let's say I want to change the profit margin, which, as you can see divides column F By column E. And I actually wanted to divide the profit by the cost for whatever reason. Okay, now here's the trick. And here's how you can avoid messing up any formulas that you might have. You always have to look for these special characters and instead of me just saying, I want to replace the E with a D, which in this case would work because I don't have any other formulas. But as I said, if I had a formula that actually had the letter e such as indirect or any other formula, it will change that e as well and mess it up. Then I have to tie it to the unique character. So here I have the dollar sign, which I will explain later in the course what these dollar signs mean. But basically, if I didn't have this dollar sign, I would tie it to the divide symbol as well. So the forward slash always combined these letters with these unique characters in order to avoid modifying any formulas that you might not want to modify. So let's just do this now. Okay? So we're gonna find and replace. And as I said, we're gonna replace Call them E, but I'm gonna put the dollar sign and turn it into Call him the Okay, So same thing I put dollar sign, e dollar sign D and replace all. As you can see, the formulas, the results have changed straightaway. It's made 15 replacements. And now if I go on this particular cell, you can see it. Divides column D and it still has the dollar sign. OK, so that's the best practice when using, find and replace. Now, I'm just gonna undo that. And I'm gonna show you some more advanced things where you can actually modify the links between your files and how I actually fill in tables that have a large amount of information when I conscious, drag the formulas around because thes dollar signs fix the cells. Okay, you're gonna understand this better in the future sections, but because these cells are fixed because they're linked, toe another file, which you can see here, and you also have this file which is called Section three database. This file is basically the same as the database sheet right here. It has the same layout, everything. And I also have it open. As you can see, I just opened this file right here now, because it's linked to another file, it puts these dollar signs and it fixes the cells. Now you're going to see if I actually go back here. And I tried dragging these cells down, okay? To get the values, you can see that the same because they're going to stay fixed on the road and on the column . That's just how these dollar signs work. So I'm going to show you the quick way for me to link this. This whole formulas from this database excel file to the database sheet in my current Excel file, and then how I'm gonna modify all the rows so that I feel in this table without ditching the dollar signs. Okay, so let's do the first things first. I'm just going to replace the file here. So instead of it pointing to the database file here, I wanted to point to the database sheet within my existing excel file, and this is extremely easy. I'm just going to select everything. I'm gonna press control, hate. And as you can see in the formula, we have these square brackets right here, which point to that file. So I'm just gonna copy this, put it in the final replaced box, and then I'm going to replace it with exactly nothing, because then it's just gonna point a database that is in my excel file. Now, there are a few conditions for this to happen perfectly. The 1st 1 is obviously the sheet to be called the name the same. So it's called Database in both files. And the second condition is that the layout is exactly the same because Excel is not that clever to notice what values I want to take. But if their position on the exactly the same columns in exactly the same rose than this is gonna work flawlessly. So I'm just gonna replace everything well done. We've made four replacements, and as you can see now, when I double click on it, it goes in the same file the database sheet in the same excel file. So now, averaging the 1st 3 months and then these quarter to averages the next three months and so on. So right now, I don't have anything to do with section three database with my other Excel file. That was open. But I still have the dollar signs. So what I'm gonna do is I'm simply going to copy this and copy the formulas, Okay? Some just copying the formulas with Paste Special. Okay. And now I have the same exact rose, the same exact values. But I know that the android average users are exactly the next row after the IOS users and the IOS users, if you look at the formula, are on row 14. So I'm gonna do the same thing right here and now imagine this doing this with, I don't know, 30 columns and about 30 rows down. It's gonna be much, much faster than you, inserting the formulas and just dragging everything along. It's the fastest way it's actually filling this table without losing the dollar signs. So I'm gonna do now is as I showed you before. I'm going to go on the find section here if I can actually select it, and I'm gonna put dollar sign 14. That's what I want to replace and I want to replace it with dollar sign 15. Now it's not gonna replace any values that are 14 or 15 as you can see here, 14 million. If I only put 14 it would replace that as well. But if I'm putting the dollar sign that it's just going to place the formulas. And as you can see, it's done eight replacements because it's too such occurrences in each cell. And now I have the android users right there, and I can do the same for Windows. So now I'm gonna have the 14 on top automatically. I'm just gonna delete the five and put Rose 16. Now I have the Windows users, and now I can do the others, which obviously is gonna be rope 17 and I'm gonna replace all. And that's how you use find the replace. So just a quick recap of this Excel hack. You can use it to replace text in your columns very easily. You can use it to replace formulas. You can use it to change links between files so you can even point to a different file that say, you have version one version to version three of a data retreat. For example, If your database. You can switch from version two to version three just like that, by changing the names in the formulas. Or you can complete tables that have these dollar signs that don't keep their position and are hard to replicate. And you can fill in the tables just like that we find replace. I hope that was helpful, and I strongly encourage you to explore this functionality going for. 16. Quick Hack #5: Searching With Wildcard: quick hack searching with wild cards. Now, I'm going to start off by saying that you can do this when filtering a table as well, but I'm going to show you that a bit later. Basically, searching with wild card means that we're gonna look for certain values in a table when we don't know one or more than one of the characters. And we're gonna use the question mark symbol and the asterisks symbol to make this happen. So what I'm gonna do here is as you can see, I'm on the student list sheet in the same excel file. I'm gonna set the whole column that has to the numbers, and I'm gonna press control f to bring up the find of functionality of Excel. So let's say, for example, I want to pick up everything that's thoughts with 101 But I don't know the last number I'm gonna click find Also, I wrote 11 question mark find all. And here are all the results that come up 11 14 12 11 10 18 15. And by going with the arrow arrows up and down, it will take me to off these different occurrences so that's one way to do it. And then the next way to do it would be to do the same control F. But right now, we're gonna use asterisk now the asterisk If I pressed 11 asterisk, asterisk, This is not necessarily going to find all the numbers that start with 11 and the last two characters are, you know, whatever they want to be. But it's going to look for everything that has to one's in it. So if I press find all, you're going to see this exactly how it plays out. So I have 1111 But then look half 10 11 and I also should also have have in this table Yeah , quadruple ones and so one. Now, obviously, most of the results are going to be one ones, but you can encounter other stuff as well. So I hope that's an interesting hack for you. It's a very quick one, and as I mentioned, pay attention to the filtering lesson where I'm going to show you exactly how to do this while filtering a table and getting all the numbers that you want. The visibility that you want straight away 17. How To Make Sense Of Data-Packed Tables: the freeze panes functionality in Microsoft. Excel. Now, this functionality is gonna make your life much easier if you're dealing with large tables such as the one that I have here, which have a lot of information in them, that you need to see the Heathers to make sense of it. So right here I have different grades of different students, and I have all of the topics. But if I scroll down, unless I really know the table and I know it by heart what each column is I'm gonna look at these grades and I don't mean Okay, What is this? Which topic is this? Maybe this is maps. Okay, let me just go all the way up. No, it's business. Okay, let me just go down. Down? You know, you can have thousands of rose and it can get really confusing. So how can we deal with this? Notice that I'm on the student list sheet in the same excel file. I'm going to navigate to the view tab at the very top. And here I have this functionality, which is called freeze panes. Now, if I click on it, I have three options. I can freeze the top row freeze panes or freeze the first column Now, because of the way I particularly built my tables, I can't use the freeze top row functionality. So what we can do is right. Click on the first row and delete it. And once we do that, we have a top row. I can select freeze panes and freeze top row. As you can see, this line is here now. And when I scroll down, I have all of these, you know, Table Heather's on there. So now I have no problem. I know this is economics. This is business. This in maths and so on. However, maybe I want to customize this even further. Maybe it's not the case here, So in order, ditch it, we can choose unfreeze panes. Maybe it's not the case here, but I could have you know, there's nothing saying that I couldn't have more columns here. I could have a Semenya's 2050 columns here and all of this data here that can be filled in . So how can I actually tell what is to the right and what is that? So I'm going to go in this first cell here, so I want to block everything to the left and on top of it. And I'm gonna go on freeze panes and simply choose the same function. Freeze panes! And now look what happens when I scroll down. I have all the headers locked down, but when I go to the right, I also have this student number and the year locked down. So wherever I navigate on this sheet, I always see the Roe Heather's and the column headers. And that's helping me tremendously. Because if I have data packed spreadsheets, this is extremely useful for visibility purposes. So remember this quick tip right here, freezing pains and by all means, use it wherever you can't. Because the truth is, if you're taking six L course, you're probably dealing with large databases, and this is very, very small tip can make a large difference to your productivity going forward. 18. Sorting Efficiently In Excel: sorting in Microsoft Excel Now, sorting is a function that you're gonna use quite a lot of functionality that is prevailing in Microsoft Excel. And I'm gonna show you exactly what to look out for and how you can sort by numbers alphabetically or by your own rules. So again, I'm on the student lists sheet in the same Excel file. Notice how the Eastern numbers that I have here are in no particular order. What if I actually wanted to put them in ascending or descending order based on their values? Now, the one thing that I want you to pay attention to is if you have a table like this, you can just sort the first column. OK, you can just press soared on the first column because it's on Lee gonna change that first column, and it's gonna mess up everything else in your table so they're not gonna be correlated anymore. And as you can see, XO actually detects this and says it has found data. Next, your selection and it tells you to expand the selection. Okay, so what I'm gonna do is I'm going to select the whole table, and I'm gonna press sort Now it's gonna ask me what I want to sort by, and I'm gonna choose student number notice. I've ticked this box here which says my data has Heather's because I select the table with the headers and everything. I could have selected everything from here downwards from the first student number downwards. But I selected the whole table, and now I'm going to sort on values from the smallest to the largest and press OK. And as you can see, everything is sorted now by the student number, and everything else is correlated. Now I'm gonna do that and I'm gonna show you how I can sort alphabetically. So I'm gonna go select the whole table again and press sort Now I'm gonna sort by year and I'm gonna sort again by values order is a to set. Now, as you can see, I have final, 1st 2nd and third all sorted accordingly. So that's how you consort. Especially if you have names. This is gonna be much more relevant to you. If I had the student names here, I could serve by the names, but it works exactly the same. I'm gonna do this again. And now we're back at the basics. Now, here's a more complex, um, sorting. Well, it's a bit more particular. What if I actually want to have first year students, second year students, third year students and final year students? Now, obviously, that's a That's a naming that I have given to these years. I could have put 1234 So how can I make it work with the names that I have here? So again, I select the whole table and I press sort. Now I'm gonna sort by year. But rather than sorting on the order eight Zet, I'm gonna press custom list. And on the custom list, you can see it already has some stuff impotent there. But I'm going to create a new list. And on this new list, Sorry. Let me just go back on this new list. I'm gonna list all of the entries that I want, so I want first. Then I want second that I want third. And then I want final, all separated by a coma, and then I'm gonna add it to the custom lists. So as you can see our 1st 2nd 3rd and final, and I'm gonna press okay? and then I'm gonna press okay for the sorting. And as you can see everything now, it's sorted first, second, third and final. So that's sorting in Excel and, of course, practice it as much as you can. It's a easy and intuitive feature, but it's not something that you know. It's just easy and doesn't provide you a lot of value on the country. If you have large tables that I'm sure you're gonna find this functionality extremely useful. 19. Quick Hack #6: Fast Navigation With A Few Keys: quick hack fast navigation in Excel with Control Shift and the Arrows. Now that we've reached a point where we look at large tables, you might see you might have noticed how I bounce around from one part of the table to the other. As long as there's data there, I can navigate all the way down. As you can see here, it's roll 159 just with the press of a button. So how did I do that and how you should do the same thing? Basically, if you hold the control button, it's gonna go from one side of the table to the other, as you can see now, impressing right and left with the arrow, some holding the control button and with the arrows, um, navigating table, as I mentioned, as long as there's data there. As you can see, I can go all the way down to the bottom of sheet. As long as there's data there, there's no blanks. It's going to go all the way down. So that's how you navigate fast with control, plus the arrows. Now, if you press control shift and the arrows, it's gonna do the same thing, but it's also going to select the whole table. So if you want to select this whole table, look how much this takes me. Okay, so I go all the way to the end. I go all the way down, I drag the mouse down. Okay, I'm at 159. 159 throw. Imagine if I had 2000 roads right there. That would be pretty slow. Now, just look at how I consider it the whole thing with control shift control shift, right. Arrow and bottom Arrow ban. That's all of it. Selected in one go. So keep this in mind control plus arrows and control shift plus arrows because it's going to revolutionize the speed that you work with an excel. 20. Ultimate Guide: Filtering: filtering in Microsoft Excel Now we've looked at sorting and filtering is similar to sorting, but it just takes things a step forward. Let's say a few steps for and I'm gonna show you exactly all the functionalities that you can access by filtering a table and how this can help you when you're dealing with large chunks of data. So let me just show you first how to select table to filter. And I've just the selected previously. But because I've showed you the control shift way of navigating through your different tables, I'm gonna do the same thing here. So I'm gonna press control and shift. I'm gonna go to write and all the way to the bottom. So right now I've selected the whole table and I simply click filter. And as you can see now, I have these Heather's here, these drop down menus which represent all of the column headers and what filtering is at its most basic form. It's basically you choosing which values out of this range. So all the values that you have in that cell, which ones you want to see, and I'm gonna show you the easiest application of filtering Let's say, for example, I want to see all the final year students. I'm simply going to click on year. I'm gonna untech select also that nothing is selected and I'm gonna click final for final year students. And as you can see, all the final year students here are selected, and it only shows me this. Now the most important thing that you have to realize when you filter is that this doesn't create a separate table or shows it in a different way, but simply hides the cells that you don't need so resistant the temptation to drag formulas up or down because, as you can see, we're going from Row two to roll 109. Now, if I drag economics down here thinking that I'm gonna only put in the cell, I would be wrong because they would affect all of the cells up until Row 109. So be extremely careful. When you delete Rose, we delete columns. And when you drag formulas with auto fill because you might affect themselves that you don't want to, and then you don't even realize and your spreadsheet has errors, so just bear that in mind whenever you filter. Now I'm just going to select all and return to the previous four. Now the other thing that I can do with filtering is if I take the same example of the Year column, I can apply text filters. And if you've worked, I've worked with spreadsheets where had production codes or transaction codes, and they would usually start with some letters. And, you know, it would be very difficult for me to look at 10,000 felt 10,000 transaction codes and see the ones that I need it. So in this case, I could choose a text filter that says begins with or ends with or contains certain letters . And if I say, begins with and put F I, which is going to return me hopefully first and final year students on Lee. That's a way that you can filter on text only on a few letters that you only keep the ones that are relevant to you. And I'm gonna clear the filter from there right now. Now, the other thing that I that you can do is obviously sort and you concert largest to smallest or smallest largest. If you have numbers or you can select. Let's say certain values on different columns that you want to see. So let me give you an example. Let's say I want to see all of the students that do really well in business, maths and accounting. Okay. So I can choose a number filter that this is greater than nine. Okay, so I'm only taking nines and the tens for business. I'm only gonna take the nines and the tents four maths, and I'm only gonna take nines and tens for a county. But as you can see, if I apply that filter here as well, I'm gonna be left with no hope Shin whatsoever with no student that scored that highly in all these different modules. So that's the way you can apply this filtering to several columns at the same time. And right here I have the only students and have performed this well in business. And Max Now I can just clear the filters here and here, and I'm left with what I had before. Now you can also do this manually and choose the ones that you want. So you can I can select everything here and Onley choose the eights, nines and tens or only the ninth intent. Click OK and do that for any other color. You get the idea now. The last thing that I want to show you is I have the average great here. Okay, the average grade of all of these students. And let's say I want to get a bit more fancy with this. I want to go to number filters and let's say I want to choose. Obviously I can choose greater than, let's say, 7.5 all of these indicators, but I can also choose above average, so it's going to calculate the average for all the students and Onley show the ones that are above that particular average. That's pretty cool. Now I can also choose the top 10 and I can Oh, I can choose the top 10 items. So the top 10 students, in which case I'm gonna be left with this or I can choose the top 10 students in terms a of percentage, the top 10% of students, and I'm going to be left with this. So these are just a few of the ways that you can manipulate data and excel, and I'm sure you would agree if he worked in finance. If you work with complicated spreadsheets, which have a ton of values filtering, it's absolutely mandatory. It's something that you have to master, and you have to master it to the point where you can play with it as much as you want and show exactly the data that you want it to show. So I hope that was helpful and by all means practice filtering because, as I said, it's an extremely important feature for most of us when using Microsoft Excel. 21. How To Get Rid Of Duplicates In Your Data: removing duplicates. So I'm sure you've encountered the situation before where you suspect you have some duplicate numbers or, you know you have some duplicate numbers in a certain range in the table and you want to get rid of them. How do you actually do that in excel? Well, it's very simple. We have a dedicated function on the data tab, which is called Remove Duplicates. Now what you have to pay attention to and this is very, very similar to filtering is don't just remove duplicates from one column. So if you have a table, as I have here, and it's a very simple one, just a student number in their year, I'm not only going to select the student number column and choose remove duplicates because , as you can see, Excel warns me about that. There is data next next to my selection. Do you want to expand it? And I'm going to say yes now the process is extremely easy. I'm gonna go control, shift and select the whole table, click remove duplicates, and now it's just gonna ask me which column contains duplicate values Now, obviously the year calling for me it does contain duplicate values. But I don't want to ditch that because I actually want them to repeat the year of each student. So I'm gonna intake that and just keep the student number. And as soon as I press OK, there we go. Seven duplicates values found and removed. 150 unique values remain. So now I have 150 purely unique numbers for student numbers. So something was wrong. Before that, I had seven student numbers that were repeating. Now you can also use this function not on Lee to remove the duplicates, but to also check if you have duplicates in a certain range, and that's that works exactly the same. So I select the whole table. I go on, remove duplicates. I only selects to the numbers, and it tells me no duplicate values found, so you can use it both ways, either to remove the duplicate values from an existing table or to check if you have any duplicate values in that particular table. I hope that was useful. I tried to keep it short. Remember this functionality. Whenever you're confronted with repeating values in an Excel table, 22. Using Subtotals To Your Advantage: sub totals in Microsoft Excel. So what are subtitles If we're faced with a table like this one that we've seen before And we want to add the total of the average or any other function for some elements that have a common value than how can we actually do this without, you know, investing time and building different tables and different formulas. Let me show you concretely what this means. That you understand better. We're going to use sub totals for this. So I'm gonna select the whole table. I'm going to go on the data tab at the very top and at the far right, you can see the sub total button. Now, this will ask you at each change in, and this depends on what you want to change. I'm just going to scroll all the way up, so you understand exactly what I mean. So at e change in year student number or whatever the case may be, this will add a sub total now, because the student number is my unique field, I'm not gonna choose that. I need to choose something that is repetitive. And the only thing that is repetitive here is the year. So I want for every year, so for every changing year, so it's gonna group it and to first year students, second year students and so on. I want to see the average. Or I can choose this some or I can count, or I can see the maximum of every of any of these fields. But I'm gonna choose the average for each subject topic. So economics, business maps, some selecting all the columns here, including the average Great. I want to see the sub total for that. So I want to see the average. I want to see how these students do compared to each other. And when I press okay with sub totals, have Bean added Now notice that this actually groups them. It doesn't change the whole table. So I'm gonna press one to collapse everything or two to show the sub totals, and three will show every single row. So I'm gonna press two, and it's going to show the sub totals on Lee. As you can see the four. My thing is a bit must stop when we can tidy it up very quickly with the tricks that we learned in the previous section, So I'm just gonna collapse thes and, as you can see, have a very nice summary of all of these averages. Now, the one thing to bear in mind is, if you want to copy this and put it into another table, this is gonna copy all of the values that you have in the table, As you can see right here, it's gonna copy the whole database. Now, I'm going to address this in the next lecture, which is gonna be a quick hack on how to copy visible cells on Lee. And this can also apply when you filter when you sword when you group cells and so on. If you only want to copy the visible ones, there's a distinct way of doing that. But let's get back to sub totals and explore a bit more off what they can do now. Obviously, I have these subtitles here, and as I showed you, I can collapse them or, um, group them by pressing the buttons in the top left. Now, what you can also do is, as I have an average here, automatically puts the average word after them I can use, find and replace so I press control page and I can say instead of average, just say years. And now it's going to say, first year, second years, 30 years in five years and I can just remove this one and say total and that's that. So those are sub totals. Obviously you can. You can remove them by pressing sub total and clicking. Remove all and once you remove them, you can play with different other functionalities. You can still like the whole table again. Click sub total and use the Mac's function or whatever you want to do. This is the main idea. This is how you use them, how you customize them. That's up to you. It really depends on the particular situation and on what you need to emphasize data wise. 23. Quick Hack #7: How To Copy Visible Cells Only: copying and pasting visible sells only. So we saw in the subtitles. Subtitles example that when we copy these subtitles even when they're grouped and we try pasting them, we get every single instance of the table itself. So how do we actually only grab these sub totals and just show them in a different table? Maybe you want to take the data, put it somewhere else. Whatever the case may be, you need to figure out a way to copy these Rose only and here's what we're gonna do we're gonna select these rows and then on the home tab on the top, we're gonna go all the way to the right where it says find and select and on finding select , we're going to select Go to special Once we click that on the right hand side table Um, column. We see this option which says visible cells on Lee. We take that and click. OK, and as you can see now, it on lease elects these five rows, which we can then copy and then paste as we want to. And as you can see now, there's only five rows as opposed to 150 or however many the war in that previous table. So that was the quick CAC of only copying and pasting the visible cells. And obviously this could be applied whenever you have hidden cells. When every group sells, whenever you have filtering, sorting and so on, so it's a hack that will be extremely useful in your excel use. 24. Excel Hack #2: Introducing Flash-Fill: Excel Hack Flash Phil. Now I just want to start off this lecture by saying that Flash Phil will only be available for those of you that are using Microsoft Office 2000 and 13. Because it was introduced with this version of office, it won't be available for those of you. They use previous versions of Microsoft Office. Now what is Flash Phil? Flash feel is basically the attempt by Microsoft for Excel to learn the patterns in your data and automatically attempt to fill in the data for you. So imagine you have tables that have, let's say, thousands of rose and you start importing some data that is next to some other information that you have in that sheet. If they're correlated in any way, Flash Phil will detect that, and it will suggest what it thinks is the way to complete the tables moving forward. So I'm just going to show you three examples so they understand exactly what Flash Phil is and what it does. Let's start with this one where I wanna format these phone numbers that I can read them in an easier way, so I'm just gonna format them in the falling matter. I'm gonna put brackets 7 to 2 and then I'm gonna do a dash 536-783 And now look what happens when I press the next bracket. Bam! It automatically detected the pattern. I simply press enter and it fills in the whole table with what it thinks is the way it should do it. And it's actually correct. Let's move on to a more complicated example. Let's say I have surnames, middle names and first names, and with these names, I want to put them in the following order. So I want the surname. So in this case, Oliver Coma, the first name Michael and then the initial off the middle name and a full stop now because I have. For some people have middle names for some, I don't It will take it a while to understand what the pattern is, so let's just start filling in the whole table. So Jordan Coma Robert A full stop Scott Coma, David B. Full Stop and now Brown Koeman, Nathan. And as soon as I go to Nash, it figures out what the pattern is and you can look pain. Donald P. Foster, Antoine G, and even if I only put an initial there, it still does the same thing. And it's still behaves correctly for the ones that don't have a middle name. So sometimes you'll see that you need a certain number of instances. Maybe 45 Maybe even more examples for Excel to figure out what exactly you wanted to do. Now let's go to the last example, which is with dates of birth. And I just want to reverse this and make it 27 4 slash 024 slash 1992. Okay, now I'm gonna do 16 ford slash 074 slash 1988 34 slash 12 and it already picked up the pattern. But as you can see, I have this these weird five figure numbers instead of dates. And that usually happens because the formatting is wrong. Now let's see if I go to format cells, I go to date and format everything in the following manner. What is going to happen? Everything looks the same. Ardis correct. 11th of the 11th 1967 Correct. 12 May 1980 to 23rd of April, 2003 17th of January 2000 and eight of August 1976. So again it realized what the pattern is for the dates of birth, and it automatically filled up everything. Now, I I cannot emphasize enough how useful this feature is. And again, if you have tables with thousands of entries, this will save you a massive amount of time. So just remember the two rules that will make, um, flash Phil behave more naturally, which are, firstly noticed that all of my tables are formatted. So my Heather's my table headers are formatted, and that tells Flash feel that this is the beginning of the table and not the first instance, because this having a different formatting, it will take it as the table heather. And then the next rule is to always, always have the column that you want to be filled in to the right of the columns that you want to combine. Because if you have a blank space here in my not detect that this data escorted to this one . But if it comes straight after it, it will look at all the columns before it or rose before it, and it will try to figure out the algorithm that you fill in those cells after