Excel: Top 10 Tips to Become Better in Excel | Leila Gharani | Skillshare

Excel: Top 10 Tips to Become Better in Excel

Leila Gharani, Excel Instructor, Business Consultant

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
12 Lessons (2h 3m)
    • 1. What You Will Learn

    • 2. Tip 1 - Use Keyboard Shortcuts

    • 3. Tip 2/1 - Master the Simpler Tasks

    • 4. Tip 2/2 - Master the Simpler Tasks

    • 5. Tip 3 - Quickly Navigate Larger Files

    • 6. Tip 4 - Inspect Workbooks Before Distributing

    • 7. Tip 5 - Use Protection Appropriately

    • 8. Tip 6 - Import Text, CSV & Web Data Properly

    • 9. Tip 7 - Audit & Debug Formulas

    • 10. Tip 8 - Customized Number Formatting Tricks

    • 11. Tip 9 - Solve Problems With Goal Seek

    • 12. Tip 10 - Speed up Your Excel Files


About This Class

Do You Want to Improve Your Excel Skills? To Become more confident and Advanced in Excel?

This course is your starting point! You will gain new knowledge that will help you save time when you work in your Excel files. If you are already advanced in Excel, this course will allow you to "fill in the gaps".

The top 10 will increase your awareness of the most useful Excel features to work faster and smarter. Only when you know the extent and possibilities Excel offers you, only then are you able to handle any type of analysis with the best method.

Audience for this course:

Specifically designed for people using Excel on the Job. Advanced Excel skills are highly relevant in business. No matter which role you’re currently in or you’re planning to take on. Whether you’ll be in Finance, consulting, IT, in project management, your advanced Excel skills will set you apart from the competition.

What is the difference between an Advanced Excel and Average Excel user?

An advanced Excel user is aware of the tools and features available and can apply the right tools for the right task, instead of relying on their go-to tools and making them work for every situation. Advanced Excel users, always find the optimal solution for every task, and they find it fast!

What differentiates this Excel course from the others?

The reason I created this specific Top 10 Tips Excel course, is because there are many advanced Excel users who are unaware key features. I understand that when you take an Excel training it can be so broad, and a lot of courses cover too many topics. It’s easy to get lost. You might also not immediately appreciate why some features are useful and how you can practically apply them. We are creatures of habit and it’s difficult to get our head around learning new techniques. As a result you might be missing out on some features that can majorly simplify the way you use Excel.

Differentiation factor #1

Focus - The top 10 brings focus to the key Excel features which will provide you with a solid & a strong foundation in Excel. If you are currently advanced in Excel, you might have missed some of these features. 

Differentiation factor #2

Learn by doing - This Excel course is well structured and organized. You can download my Excel Demo workbook which I use to demonstrate each feature and practice along with me. You also have an exercise book to test your knowledge. Real learning happens when you attempt to solve problems on your own. Answers are provided, but do ask any questions if you get stuck. Your questions will always be answered.

Differentiation factor #3

Engaging - I have made every attempt to keep the course interesting and engaging by mixing talking head explanations with screencast demos. Each course is split by topic & feature. This allows you to easily pick what you want to learn now or review later.

Excel Version:

The videos are filmed using Excel 2010, but the techniques I show you apply to older as well as future versions of Excel.



1. What You Will Learn: Welcome to my Excel advanced training, where you will learn the top 10 tips to become better in Except I'm Leila and help companies use Excel better with the help of dashboards and house visualization techniques and also customized training. I designed this course for anyone seeking to improve their Excel skills. While there are a lot of functions and tools available in Excel, there are a few that are extremely helpful when you use Excel on a frequent basis. But in this top 10 tips training have included topics that are probably not in a basic Excel training. And also, if you've taught yourself excel and you worked yourself up to advance status, you might have missed some of these features. Now, how did I put this list together? You're not going to see topics like sorting and filtering here or search and replace or adding comments to cells. The way I selected the top picks for the top 10 is purely based on my experience on what I've seen as generally missing knowledge for existing Advanced Excel users. So if you're currently quite advance and Excel takes the lectures in this course as a kind of ah fill in the gaps type of exercise. Here's an overview off my top 10 tips that I'm going to cover with you. You're going to see that these air, not just 10 topics. Some of these include a combination of topics. But all in all, I wanted to squeeze thes points into 10 easy to digest main topics. There are some important features that I don't mention here. And you might be wondering why, for example, charts conditional formatting name, manager pivot tables and excels great functions. Now, the reason for this is that each of these topics are so broad that they need to be covered on their own. The purpose of the top 10 is for you to be faster in your analysis by introducing you to some features that you might have missed up till now, which could actually really help you in your work. And the training is hands on. You're gonna download my demo workbook where you can follow my demonstrations. And at the end of the training, you're going to complete your project. Just remember, the main challenge here is that you understand how you can apply the techniques I show you to your own files. So take which ever tip you find useful and try to immediately apply it on your own data. So let's get started with tip number one. 2. Tip 1 - Use Keyboard Shortcuts: Why keep brochure cuts useful? Well, it's not just to be cool, although there's something to it. I'm generally suspicious of people who tell me I never use the mouse. I think everything has its place, but there definitely is an advantage to using shortcuts because they allow you to navigate larger files much faster. They also let you input your formulas more quickly, man. Some people generally rely on the keyboard to move around. Some generally rely in the mouse, and some rely on kind of both, depending on the task at hand. I'm one of thes people now with excel. There's so many shortcut keys available, so don't get lost in the maze. Pick a few tasks. A handful is enough that you usually do and use the shortcut keys for them. Here's a list of tasks on their shortcut keys that are my personal favorite, and they helped me be faster. When I work with Excel, you can print out this list from your Excel Dem a workbook. Let me take you through some of these. This one control one is my most used trickery. Keep basically, you go to the format cells you do control one and you come here and you can do all your formatting in one place. It's it's instead of going here, former cells, the other one is also enter is if you want to insert a new line within this cell, so you have to be insights. Elissa inside here, and I actually want to have a line break here. If a presenter I go out off the cell. If I want to insert a break here, I have to do also enter, not to go back. There's a shortcut for that. You don't have to do this. You can do controls that the kid that's down here, no control plus is something I use when I want to insert a new role. Let's say you come to this lace, and then you can use your hierarchies as well. Do you want to insert an entire row or entire column in this case, a say entire row and then control minus delete the entire room. So if you're working with larger spreadsheets with many taps like this one, you can use the control page down and up to move around. So if I do, if I hold control down and now I go page down, I move in my taps page up the other way. Since I'm here, let me show you some other shortcut keys. If I am on top of me data and I want to see what's the last line here? I can do control and then click the arrow, The down arrow key. I jumped down to the bottom. Control up goes back up also You can do sideways if I'm here and do control right, They go here Control left. I go back. If I wanted to highlight the entire range, I can do control shift down And when I hold control, shift down. If I click the right our key I highlight my entire range I'm going to click home and they go back up also from here. Control shift, right. Iraqi is just this line. I hold control shift right on down. Then it's the entire range. Now if I wanted to turn this into a table, there's also shortcut key for that is control T and it turns it into a table. Que if I want to go back What was it? Control set. Think we covered some of the major ones here print preview is control F two, so I recommend that you print is sheet on. Do you put it beside you? Try to memories the shortcut keys that are most relevant for you. And when you do print this sheet, do use the shortcut Control F two. Then we have, find and replace so you can do control. F. You're probably familiar with fine. So it just finds a text in your Excel sheet, depending on your options that you've said here, if it's just within this sheet or your workbook, if you want to replace something, you can come directly not by control our butts by control H. Now most people are familiar with finding replaced because if you've taken any basic Excel training, you've already come across this. That's what I've noticed is that a lot of people have missed the fact that you can also find and replace formatting. Let's say you have a specific formatting in your files, a new companies bought by another company. They have a different color scheme, different local, and they ask you to change all your files you can use, find and replace. To do that, all you have to do is you don't need to write Texas, the Texas not relevant. You can look for the format that you want to find. Either you find it here, or you can actually highlight the cells or choose former from the cell, and then you choose what you want to replace it with. So if you already have the format in some cell, the new four months, you can just select it. If you don't, you can just highlight the former that you want. So let's say we want this green. Basically, I'm searching for this for months and replacing it with this one, or you can do replace all and do it all at the same time controls that to go back if four is quite a useful key. One is that it helps you change the cyber Francis between absolute and relative values That's a were referencing the cell, and I want to fix this cell. So when I pulled this formula down, the cell stays as J 23. I can use a fore, and I get the dollar sign on both the J under 23. If I press before again, it's a partial fixing room fixing the road. And if I precedent gain and fixing the J the column president gain, it's gone presage once more. It comes back now if four comes again here because it's great for repeating the last formatting action. If there's something that I four months, so let's say right, Layla here and make you bold and then read. And here I'm going to write Sarah and I want to just get the red Pierre. But not the Bulls won. I go to Sarah and I press F four. It repeats the last formatting action. So if I wanted a full thing, I can go like this. Therefore, just as the last part. And that for me is quite handy because sometimes I designed tables that have a different colors for the headers, but they have the same border. And then I decided, changing the border for one of the tables on. I want that same border to apply to all the other ones. I just use a four. That's a real time saver. Another one that is really useful for me is the time stamp, because if I'm working with task list and add something update something, I want to get a time stamp. I use this shortcut, and the way you use it is you have to be inside the cell says as if you're typing in it. Then you press control and then the semi Colin and you see you get your time stamp. So don't do this. Don't be outside and do control. Plus because then it thinks that you want to add a new row or column, so be inside yourself. Control Semi Colin. This is the list of my favorite shortcuts in Excel. Mr Turned to practice as a starting point, I would suggest that you pick out three keyboard shortcuts that you need and are currently not using and practice these for three minutes after that. Used them the next time you're working in your own Excel files. 3. Tip 2/1 - Master the Simpler Tasks: I've named this tip master The simpler tasks because there are a handful of very useful tools that can make working with Excel much easier. You might be familiar with some of these from a previous Basic Excel training where you happen to stumble on this feature by yourself. I just had to be sure that you knew of thes because it's important for any Excel user to know them and be familiar with them. Here's a list of features will be covering Always update your quick access bar by including the buttons and the commands that you most use. You can also add features from third party Adan's. Basically, this toolbar acts like your favorites. It saves your time so you don't need to switch between the different ribbon taps and look for the button you need. This is my quick access bar, and it includes the features I need most to add something to this bar. All you have to do is to right mouse, click and select. Add two quick access toolbar. You can also add buttons for specific features that are only available if you have that feature activated. So, for example, if you have charts to get more options. If you have pivot table, you get different options. So if you'd like to add a pivot table button if you already have a pivot table in this case , I have one here you just find the button that you want. Let's say I would like to add this field list. I right mouse click and they select at too quick access toolbar. They can see it here. It's only activated if I'm working in a pivot. So if I would go back to May sheet here, you can see it's great out now if I didn't have a pivot included in this workbook. But I would like to have a specific pivot table feature. I can go to this little Ira here and select more commands. Here you get control over everything. It's sorted by popular commands, and then you can see Commons not in the ribbon on all commands Macros. And here is the order that you see your taps in, and these were the ones that are generally hidden from view and only appear if you have that feature activated. In our case, let's say we want to add the refresh button for the pivot table. I can find it under people Table tools, Options tab, and it's always alphabetically sorted. So we go down toe are and we can see the refresh options. This is the main option. You see the IRA here. These are the sub options. I'm going to select the main one and added to my list. What helps to organize this list is to add the separator, and the separator is basically this line. Here. You can see I have one here and here. It's a little line that provides a visual separation between your buttons. So if I wanted to add one right before my pivot, I just click on the separator, put ad and then push it up using these buttons. The other option that you have is to select if you want your quick access to a bar to be the default for all your workbooks, or if you want to add specific features on Lee for this specific workbook. So if this is a workbook where I work with him, it's a lot, but in general I don't need them. I can add the pivot features only to this, and it will only show up. When I opened this workbook, I would basically remove them from here and then go to this view and push them back in here . The other advantage of using the quick access toolbar is that each command has a specific keyboard shortcuts. So if you click the all ski, you can see that you have the 1234 Here. This is my printer preview one. So if I would know press four, I come to print preview. Now, if I wanted the print preview to be Ault one or all to, I just have to change the order in the customers quick access bar section. They pushed it here and now all too, is going to be print, preview and print. Now let me show you how you can update your ribbon. Just be aware that updating the ribbon is only possible from Excel 2010 onwards. You can see that of updated mine and have included a Layla tap where I have included the features I need most. In this case, these air relevant for charting the advantage of creating it in the ribbon and nose up here is that you have the ability to organize it more to customers. Your ribbon, you goto file options. Customize ribbon. Here you have all the commands on these air your tabs so you can see this is mine and I can uncheck it. It will disappear from there, but it will not delete it. It's still there. So if I would ever want to activate it, I can come back on activated again. Let's add a new one. The first step is to select new tab. Then we can rename it. I know we can add different groups to this. So let's say I would like to have a group for formatting. Gonna need to search for the feature of the common that I need. So let's say I would like to have this. I can drag and drop it under here. I can go to comments not into ribbon, select some pay special options such as paste formatting paced values. This one I also have in my quick access bar because a lot of times I need to copy data that have formulas behind them. But I want to paste it just as values without the formulas. Then to add a second group rename this to for example, shapes now to find the shape options. I can go to all tabs on down here. I see the ones that again only get activated. If that specific feature is activated, I will no see my favorites here. This helps me to become faster because I don't always have to switch between the different tabs to find the comments I use most. I just come here and have access to my most needed ones. If you don't want your additional tap anymore, you can easily remove this by going back. To fully remove this, you click. Remove taking the time to update your quick access toolbar and your ribbon options really pays off because it makes you faster and gets your commands more organized. Grouping rows or columns together makes it easy to organize larger spreadsheets with a lot of data. So when you group rows or columns together, Excel collapses them and shows only the last row or column. I would suggest that you generally group cells instead of hiding them from view. The advantages are one visibility because that immediately signals if you have any hidden rows or columns and it avoids errors. Two. Is that groupings support multiple levels, which means that you can have multiple levels of higher keys and that's not possible. If you were hiding rows or columns from view to group sells, you just have to highlight the ones you want to group and go to data group. You get this minus plus button so you can collapse it or expand it. You can have sub groups as well. So if I wanted to include a subgroup here at Click Group again, you can see I have this sub group and this group. You also get these additional buttons here so you can collapse everything at once. You can expand everything at once is well, or you can decide to just expand or collapse the subgroups to own group. All you have to do is highlight the section that you want to own group go here and select clear outline. Aside from groupings allowing you to hide columns and rows in a better way and hide does, it also allows you to create automatic sub totals. So let's say in this case I have my company's location, items, price and sales value. I would like to get a total for company a company B and companies See, while I have to do is highlight the section quick on Sub Toto. I can select if I want the sub total to be by company or by location in this case is by company. I wanted to be the some You have additional options here in this case will stick to some and then I can decide what do I want to some? So let's say I do want to some sales and I do want to some of the items so tick mark this as well. And here you also get the option to include a page break between groups. So if you wanted to print this out, let's say if this was an invoice that you were creating, you wanted to print it out. You can take Mark. This is well and you can also say if you want the summary below the data automatically, I get all these subgroups and the main group. I also get the page breaks included, which makes it easy if I want to print this. If now I wanted additional sub totals for the location in there, I can go back to suck Toto and select location. I want to some items in sales. I don't want to replace the current sub. Toto and I also don't want to have additional page breaks here. Now I get it sub total for Germany, for Denmark, UK and so on. To remove all of this, you go back to sub total and select removal. Two. Don't forget about the outline options that you have because they're quite useful in automatically generating sub totals and organizing larger spreadsheets. The pay special options provide some useful tools that allow you to manipulate the values before pasting. Many people overlook some of thes options. Here, let me show you the most useful ones, Piece says. Values this heart quotes the values and takes away the formula Behind them, paced comments basically paste only the Commons attached to the cells without the values in the cell undersell formatting pay. Skip blanks is a tricky one because of the way it's called. What it does is that it paste only the cells that aren't empty. So if you're pasting blank cells over cells that have data, the values will not be pasted over. This will make more sense to you when I show it in action paced, transposed changes the orientation of the copied entries. So if you're copying data in a single column and you pasted as transposed its paces in a single roll paste for marching pace only two formatting without the cell values paced link pastes formula links to the copied cells. You can also use operations on cells to add, subtract, multiply or divide. So let's have a look at these in action. Pacing is values is something you're probably familiar with. So if you have a formula here, you want to only copy to sell and not the formula reference. Because if I just copy and paste, I get relative formula reference. So if I just want the value, I can do control, see pay special and so like values. So this cop is it without the formula to copy comments? So let's say I have a comment here, and I only want to copy this comment without copying the cell or the formatting of the cell . I do control, see on the cell and go here under pay. Special select comments. The pace skip blanks option does this. So let's see. This cell is empty. If I copy this and paste this Here. You see, it copied their empty cell as well. Now, if I copy this, and instead of pasting this normally skip blanks. Okay, look what happens. My 53 stays so it skips this blank cell and leaves my value That was originally into cell untouched killers to control said paced transposed. So I copy this year and they go here and select pay Special transposed. It puts everything from the column in the room paced four moting paste Only the formatting without the cell values. So I If I do control, see here and highlight this pay special for months. My formatting is updated. My cell values are untouched paced link. Let's copy these here paced link is down here. You can see they are no formulas that are attached to this cell. So if I change this value, this changes as well. And lastly, we have our operators. So if I copy this and go here So let's say these are my new sales values coming in deserve a existing sales values on. I want to add this to this without a formula. I can do that with the paste special options by selecting ad. There was no formula needed. It adds the value off this cell to this one and notice the way I did it. First I highlighted it. Then I press control. See, I then went to the cell where I want these values to be added to this. And then I clicked on add So you have additional options. You can subtract, multiply and divide. These options can come in handy because they can help you avoid some intermediary steps. So I really suggest that you take some time and have a more detailed look at the pace special options. 4. Tip 2/2 - Master the Simpler Tasks: next is to include validations and drop downs. So let's say you were creating your report and you wanted the user to select the months to include this drop down box. You can go to data data validation. You have some options here. The one we need right now is the list. Now you have two options. You either type them in so I could do this, say, John, and split them with a comma, Fab March and so on. So let's have a look at that before I show you the second option. Or you can do a cell reference in this case. I have my months up here, so I just have to highlight them. And I have my months. If someone comes in and type something else, you get an Excel error saying the value entered is not valid so they can retry or cancel. You can also add your own messages. If we go back to data validation, you can have an input message on an error alert message as an input message. I could put make this election and select month as an alert message. I can also select if I want a warning or information sign. Please select correctly select from the list. So if someone comes here, they can see Okay, make a selection select month. If they try to input something, they get My numerous said say please sell it correctly, select from the list. If they say no, they have to put something. They can also type it in as long as it also exists in the validation. Obviously, the problem is they can copy sells on top of this if they want. So to get rid of this, you can do a control C and control V and it's gone. But obviously, if you're creating a template that you want to send out, you're gonna be activating some protection in there, which we're going to learn in a later lecture. For road to qualify as duplicate, it needs to be identical to the cells above or below it. In this case, if you wanted to get the unique values, only one option is to highlight the area, go to data tab and quickly remove duplicates our day towns, headers. In this case, we want to look at the dupe the kids for both company and agent, and you get the information that to duplicate values were removed. I'm left with a unique list, but you have to be aware that it does overwrite your data so you might want to copy and paste this somewhere else and then use this option. Another way you can remove duplicate values is to use the advanced filter. I have the option off filtering the list in place or copying it to another location. So let's say I copy to another location. That's my range. Where do I want to copy, too? Select this cell and I want unique records. Only the other option that you saw there was to filter the list in place. I could also do that instead of copping to another location. Check Mark Unique records only, and it filters it in place here. A good understanding off absolute and relative referencing is really important in setting up a reliable spreadsheet, a relative references when excel just a formulas based on where you copy the formula. So, for example, here, if we type of formula, it equals to this cell and we copy this on pace. This here it updates it based on the relative reference of this cell to the cell where I have my formula. But if I want to fix it completely, I have to use this dollar sign or the short Karki F four. So if I click before Monday's, it fixes completely the reference. So in this case, it fixes the be on the 78. So if I will copy this no, here it's still be 78 because they're both fixed. Partial referencing is when you fix just one of them. So if I don't fix the be just the 78 No, a copy and paste this year, what do I get? I get D 78. Dee's this one. Know him because the distance between these is, too? No, it's too. So it's D and 78 stays 78. So now instead, I'm gonna press the A four key again. Now maybe is fixed. What do I get? B 83? Because my 78 is no relative. My roll number is relative, but my column is fixed. It's really, really important in Excel Toe. Understand this absolute and relative referencing. And like I said, the shortcut key really helps. If you're in your formula, just press F four when you press it the first time you get the $2 signs, then you get one for the role. You get one for the column, and then you get nothing. So this is a fully flexible formulary, friends and then eight repressive gain and it goes back to step one. In this example, we're going to quickly practice this absolute and relative referencing. So we have a report here where we want to multiply these numbers by the global factor. So the original formula is basically this times this that's I want to be able to copy this across and copy this down. And it doesn't work right now because I'm not properly referencing the cells. If I click on this formula, you see that my global factor has moved. I always wanted to be this one, but without me having to do this all the time. So what would I have to fix here, this one, right, And do I have to fully fix it or partially fix it? I have to fully fix it because I don't wonders cell to move at all, no matter where I'm pulling my formula across, this cell should always be here because that's where my global factor is. So I'm going to press before once and now I'm going to copy this down. You can see that my references are good. In the next example, we're going to multiply by a monthly factor. So this is the factor for January, and this is the factor for February. How would I write this? I would say this times this I haven't fixed anything. So let me pull this across and see if it works. Yeah, this works fine because both of them moved, which is what I wanted to do. Can I copy this down? I get problems. Why? Because this is shifting down as well. So what do I have to fix in this case? The role? Because I don't want the road to move down. And the room is my 75. So now let's try it. And let's check this one. It works great. I know this is pretty much a basic topic that you probably learnt in a basic training, but I have seen some Excel users who are not so comfortable with this absolute and relative referencing. So please practice this if you think you need to as you can see these simple features can end up really saving you A lot of time knows, returned to practice to master these. 5. Tip 3 - Quickly Navigate Larger Files: When you work with large excel workbooks or worksheets, it's important to know some tips and tricks on how to efficiently cruise between the various taps on work simultaneously with different workbooks. Here are seven things you can do to make dealing with large files much easier to handle. Now let me take you through days. I've put here a list of the most useful ones. If you have a larger spreadsheet and you press control page down, you move along your spreadsheets on page up, you move backwards. If you have different workbooks open and you need to switch between them, you can hold on control and then press tap and you can switch between different Excel workbooks that have been opened from the same Excel. Session control and home takes you to the first cell and control and takes you to the last populated cell. The name box is this, and it's quite useful in jumping to different locations within the same workbook, so you can directly type an address in here. Let's see now I want to jump to sell be 400. I type it here and they get to be 400. I type a one, I come back here. You can also type Addresses in this form are for rule. So they save Rule 100 column 100. So are 100 c. 100 and I get to see V 100 Kandinsky's air shirt, Cookie control home to go back. What you can also do is provide names to yourself. In this workbook. I've given a cell a name called back if I type him back from anywhere on press Enter. I, Joan, back to the index page and I footed here. If you type back in the name field from any tab, you come here. Why? Because the name of this cell is back. And how do you name cells? You just go to any cells and let's say we always want to jump to here. So this is my special table. This is how I give it a name. So you just type it in and you press enter? No, this cell a 74 is called Special Table. So wherever I am, I can type in special table. Well, I can select it from this list and they jump to a 74 in t 10. If you have tables in your Excel spreadsheet there, also named Ranges, so you can select table one and you jump to that table directly. If I know, want to jump to T three a one, I can type in T three a one to delete name sells. You can go to formulas in May. Manager. You see the full list. Let's say I don't need this special table anymore. I can delete it. You can organise your taps by dragging and dropping them, and you can also give them different colors to be able to differentiate between them and find what you're looking for. Faster in this workbook, you can see that I've colored the tips in green on the formulas in blue is just my way off organizing this workbook. Just be careful that you don't over color things to color your tap. You just write most. Click on it, click on tap color and select a color that you want. You can also hold them control and select more than one tap and change the color of those taps. The existence of the tap menu is sometimes missed by some Excel users because it's not easy to get to What it is is if you write most quick here right here, you can get access to your taps, so sometimes when you have a lot of them and they have longer names, so they basically go over and you need to always cruise in this way, and this way it makes sense to right mouse click and find it here. By clicking on more sheets, you get access to all of them so that you can easily jump to the tab you want. And don't forget it's a right mouse Quick when you have many rows and or columns of data. Freezing pains is a very handy tool because it allows you to scroll down and across your worksheet while keeping your specify table headers and rose intact. If you go to a bigger table here, I have a table that will later on use for the pivot if I want to move down here but always be able to see my headers. Here I go to this cell, a four click on view and freeze panes, and now you can see that if I go down, I always see the part that's been fixed to remove this. You just have to unfreeze. If I wanted to frieze this on this, I have to select a cell where I want to create my cut. So in this case, it would be this cell and then I can click on freeze panes and now you can see that this one is also frozen. So when I move this way, I can always see my role information. You also have the option to just freeze the top roll or freeze the first column. When you're working with different spreadsheets, you can arrange them horizontally or vertically beside each other so you can easily move between them to arrange different spreadsheets beside each other. We go to view and you click on arrange all you can decide how you want to be arranged. In this case, I'm gonna select were Tico and they get my second workbook here. If you do need to work on different workbooks pretty much at the same time, this is a good method. If you work with different taps in the same workbook, you can also use this trick. First you need to select new window and then click on arrange all and this way you can arrange the two different windows beside each other. In this case, I'm going to select Vertical. And now I have open two different sessions of the same workbook. You can see that the first session has this one in front of it. And this is my second session, too. And now I can go to different taps that this is something I do when I'm designing, say dashboards of some reports where my data is in a separate tab. I'm a report is somewhere else. So let's say we go to this tap here and I want to create a report here and say, My Christ is in fact, this cell divided by this cell. I'm all in the same workbook. So if I would go back to t three in here in my second session and scroll down, I can see the same thing. If I delete something from here, it deletes it from here. A swell to close this window. You can just close this one and you're back to your main session. Creating a table of contents for your bigger workbooks is a great way of simply funding navigation for these workbooks. And it's quite easy. We can see that I've done it here in the index page. So I have the title of each of the tips on. I have created a hyperlink so that you can click on it and you get to just have faster. To do this, you just have to create a hyperlink to the place in your document. So let's say I want to create a hyperlink to t eight were typing t A's here. White must quick hyperlink select place in this document and then find you a tap here. So in this case, t eight, I can also decide if I wanted to jump to a one or two a different cell in T aids. So then when I click on it, I jumped to t eight to a one. But if you have a bigger workbook with a lot of tabs and there is a certain logic behind them. So in my case, for example, I always have the title in cell a one you can, of course, create a V B A that does this routine for you. What you can also do is to create hyperlinks to other external locations like a website or a folder on the Internet, they insert a hyperlink. I can select existing file or Web page. If it's a webpage, I just have to type in the Web address. So when you click this, it goes to my website. If you were linking to a place on the Internet, you'll just put in the address in here. If you were linking to a different foul, we can find the file by looking it up in the directory here and clicking on the one that you want to link to. 6. Tip 4 - Inspect Workbooks Before Distributing: In most cases, when you start to work at a new company, you take over the existing workbooks of your predecessor, and you continue to update and expand on this workbook. Normally, these files are quite large, and they contain many tabs. And it's not always clear if there is hidden information like common, some personal information, hidden rose and so on in the foul. You never know. Some hidden information can be deliberate, whereas some might be completely unintentional. But to find all this out manually can be really time consuming. That's where you can use the Inspect document feature. This is an overview off what Excel actually inspects. No, it's not a complete list. When I switched it a demo, you're going to see you have more options and more was actually also added since Excel 2010 . I put the key wounds on here. One. Is it checks for comments in your workbook. The other one is document properties, including personal information. Has anyone felt, let's say, the author name, the subject title or some additional custom information. There's also information on pivot tables or external links or links to other files. And if there any existing Mac Rose. Also, if you're headers and footers have information because generally, if you're working in a foul, you might not notice that you have fixed headers or footers until you go in print preview or you print out your document. It also tells you if you have any hidden rows or columns or worksheets, some of this might be intentionally hidden some unintentional and would just be carried over from other users, and he might not actually be necessary anymore. The other thing it does is to check for invisible content when objects or even charts can be made invisible from view. To check for this, you will go to foul check for issues. Inspect document in Excel 2007. You have the office button and then you go to prepare and you find this there. You're then asked if you want to save the document before inspection or not. Now, just to have a look at what it finds, you don't necessarily need to save, but I do suggest that you save your foul before you start telling the inspector to remove these information. I'm gonna say no in this case because we just want to see what options we have on what it finds. Here. You can see the list is much longer. This is where you find stuff. Here. We found something in comments if found, some properties under author, pivot table, some hidden rows columns have found a hidden worksheet. It's one on some invisible contents or two objects that are invisible. That the downside of this is that it doesn't tell you exactly where you can find things. Some of them can be obvious, like document properties. You know where to go for that. But comments and they could be anywhere in your sheets. So some are harder to find because it doesn't tell you. The comments we found is in this tab into cell. But it's not that hard to find things. I'm going to show you simple ways of getting around this and finding the information yourself. Now for the document properties. That's easy. That's actually what here? If you go to advance properties, you can see the name the general summary such a six and solo. So here you can see this. The author. There's a company here so you can check for this information and see if it's okay, Before you send this out on there might also be some custom information as well. No, to check for comments. That's something that you have to do sheet by sheet. You don't have to cruise around and and find them. You can do it faster. If you go to find and select, you can look for comments. Here it jumped. There is a comment here which says Hello there. I wouldn't be able to highlight all these tabs and look for the comments in them. I have to actually do it tab by tap. The truth cut for this for to go to special is actually controlled G and the new quick and special and you get to these auctions. Looking for hidden worksheets or taps is easier, so you just have to, like, write most. Click anywhere down here, click on unhygienic, and then you will see if there any sheets that are hidden this case and have to 11 is hidden. You can also get to it by going to format hide and un hide, and you can see on height sheet or hide sheet. We were also told that we had some hidden columns or hidden rose to find those. It's more tricky because if I highlight this whole thing, I still can't see if something is hidden. I could highlight this and go to format unhygienic rose and on height columns and just open everything up. But let's say if I wanted to actually just see is there anything here hidden or not? What you conduce you is to go back to go. Two specials a control G click on visible sells only Miss. Okay. And then you get this line. So this is an indicator that something is hidden there. They see a home. There was my bonus. I definitely don't want toe unintentionally send this out. Okay, so that's one way of getting an indication of which rows and columns are hidden. Next is for the hidden objects we have to hit in objects in this sheet. Can you find them? Well, they're actually sitting right here right now, and I can't see them if I go to find and select and go to selection pain. You get this and this shows you all the objects, including comments that are on this tap and because I don't see the I in there. Look if I click on it, there's this eye icon and the I means it's visible. No, it's not visible. I have something called my bonus and confidential sitting right here. And I make them visible and they see Well, okay. I have these two right here. I didn't notice. And again, this is applicable to each tab. But once you have this activated, you can quickly jump through your tabs and see if you have anything here. We have some pictures. If I go here, I also have pictures, but they're all visible. That's a good one to check. A swell before you distribute any workbooks. So now that I know what type of information I haven't here, I can go back to inspect documents and remove this. Now I do want to take it back up and I do want to save my foul. Say yes. Inspect. You don't have to remove everything all at once. You can decide what to remove. The comments are fine, but remove all of these document properties. I don't want that in there and form a hidden rows and columns. Now that I know what is in there, I definitely want to remove that the hidden worksheet? Yes, let's remove that and my two objects that I had definitely remove those. A swell. Let's go back here. You see, my objects are gone. My comment is still there because I chose not to remove them. My hidden information is also gone. McCollum is no longer hidden, and they can check also lay properties, and you can see everything is gone. Author Company I'll go. As you can see, it's a good idea to inspect your spec sheet for these types of hidden information before you share or you distribute your workbook. 7. Tip 5 - Use Protection Appropriately: protecting an entire workbook, sheets or specific ranges within a workbook are a good way to make sure that templates and important files are not unintentionally or intentionally changed. They're different methods and level of protection. You can fully protect the contents of your workbook. You can allow users to open as read only so you don't allow them to overwrite your original . You can protect the structure of your workbook. You can protect a sheet but leave specific ranges open for input, which is actually perfect for templates that people need to complete. This way you make sure they don't overwrite any of your formulas and that they only input in the sections they should. You can also protect a sheet, but leave some specific features open. Let's go through these one by one. First, let me show you how to fully protect the workbook. You go to foul protect workbook encrypt with password. If you have Office 2007 you look to the office button and you will see Prepare and you'll find it. There. Encrypt with password means that no one can open your workbook unless they have the password so they can't even view to contents. Protection here doesn't necessarily mean security. So it doesn't mean that your father is secure because there are a lot of third party tools that can crack these passwords. Be aware of that when you use this protection. Once you put a password, you just have to reconfirm its Save it. No one can open it without this password. That's the highest level of protection. Next is to save this, as read only for others to open so that they can't overwrite the original. And you can do that directly by going to save as and here under the tools, options, general options, you can provide a password to open like we did before. And you can provide a password to modify the spreadsheet. You can also take more. Read only recommended. So if I put a password to modify this again, you shouldn't forget your passwords. I put coffee here. Do you have to reconfirm and then we save this. Gave replace? Yes. If I close this and re open this, I get the message that I want to modify it. I need to put a password. Otherwise I can open this as read only. So if I'm just there. Any user who is not responsible for this while I look like read only and you can see it here in the square brackets. And I can work in this file like normal. That's I can't save directly on it because it says you can save a copy because this file is read only so I can see okay and then save my version of it somewhere to take away this protection. I need to go in as the user who can modify this workbook. So I'm going to close this and let's go back here and we can put coffee. And it said it's recommended that you opened. This is read only, but I don't want to. So I say no. And now I can again to a save as and take away that security if I don't want it anymore and save over it. The third method of protection is to protect the structure of your workbook, which means that users can't shift stuff so they couldn't move this tap to hear back and forth or even add new taps. To do that, you go to foul again. Same please. And you say protect workbook structure you will obviously give a password. I'm just gonna leave that empty for now. If I try to shift this one, it doesn't move. They don't move. And if I want to add a new sheet, I can't. So that's a good way of making sure your tabs don't get messed up. I'm just going to remove that. One other useful feature is when you hide tabs and you probably notice when you hide something when you goto unhygienic, you can see the list and users can easily unhygienic hidden sheets. If you want to hide something really, really well that no one finds it. You can do that by right. Most clicking and click on view code takes it to the V B A. But you don't need to do any VB a here. All you do is on this sheet. Here, you can see the list of your sheets. See Ellen T. Five And the last option is the visibility. No visible. Hidden is the normal Excel Hiding and Excel sheet. Very hidden, completely disappears. Okay, so now my t five is gone. And if I write most click and it unhygienic, I can't even see that it's there right now. If I then go and protect the structure of my workbook, no one can bring it back. Can bring it this back yourself by going to view code on finding your tap from here. In this case, we hid t five we can see is very hidden. We can put it back to visible and it pops up here. Next is to protect a worksheet. So let's assume you want to send some information out and you don't want any user that opens this to be able to change anything. We just want them to read it. What you can do is you go to review because that's where you find the protection options and you select protect sheets. You will put a password here, but for the sake of demonstration, I'm just going to leave that empty is just going to take too much time of me in putting the password and then un protecting and changing the settings. So I leave that empty and these two by default, are checked. You can see, it says, allow all users of this worship to still be able to select lock cells and select unlocked sales. What this means is that people can click everywhere. That's I'm going try to delete this. I can't go to try to input here. I can't, but I can click on it and I can see the formula, for example. Here. No, let's see. I take away those check marks I have to un protect the sheets because remember, you need to have the password to this to make any modifications. I'm going to go back to protection, and I'm going to take away the check marks. Leave that empty and say, OK, look, I can't click anywhere. I can't even beauties formulas, So that's what those check marks are for. In a more realistic case, you're going to be sending out a template to people that you want them to fill. You want them to complete it. You want to give them some place where they can input stuff. That's you also want to protect your sheet so that they don't by mistake, go here and delete the formulas will change some formulas here or insert columns and add their own things to this. To do that, what you can do is highlight the area that you want them to be able to input and then click on Allow users to edit ranges, then click on New. Now you can give it even arranged title, so this could be like input by agents. This is the area that I want, and I can even put a password for people to modify this. If I wanted to. Let's say in this case, I don't want a specific password for anyone to modify this. I'll just leave that empty and I say, OK, then it's not enough If I just say OK because nothing has changed. I haven't protected my sheet yet. You can see I can input everywhere. I still have this option available so nothing happens until I protect the sheet. I still do want the people to select locked cells. Now I say, OK, let's see if I can input here. So price is say, 20 and quantities 300 and revenues calculated. Can I change this? No. Can I change the agent? I can't. Can I insert a column? I can't spots. I can input here, and I can input here without a password. And then that's saying the same sheet. You want to provide a special field where some special people or agents can fill this, and you have provided them with the password. So you want this part to be password protected? What you can do a game is toe. Add this range to their list that allows the users to edit ranges. Now, why is this unavailable to me? This feature? Because I need toe on protect my sheet first so that they make sure I have the access rights to make these changes. Now, remember, I didn't put a password. That's what I'm working really fast through this. I'm gonna go to allow users to edit ranges. I can see this is input by agents. And now I'm gonna add a new range. I'm going to call this. It's a special agents. The cells that I warned are these. And now I do want to put a password so they should No coffee reentered the past forward. So now I have two areas, and again nothing is protected unless I protect my sheet. And I can do that directly from here again. You can put a password here. So this is going to be the main password that only you would know to be able to make these changes. What happens is and here everyone can enter because remember, we didn't put a password, But in here I want to put a comment and says, OK, the sell, your typing is password protected, put in the password. So now I'm a special agents and I'm gonna put coffee. And now I can see Hello there. Now I can type in there. This is a good way of creating input templates by controlling ranges where only specific people can input in certain areas. So it's basically not security. I would say different rights, but you give people different rights to input in different fields, and you can fully control that by using this feature. Now, let's on protect this Another option if you're not providing different passwords for different ranges, but you just want to leave parts of your worksheet open. What you can do is to go to former cells. So I'm gonna go with our shortcut key Control one. Go to protection. Do you see this check mark here, You can take it away. What this means is that once you protect this sheet, this cell remains open. I'm gonna say OK here. I'm going to just type something in So we know which sell this Waas. Now everything is open, right? I can type in everywhere. I'm going to go and protect my sheets, just like normal. Let's see, I can't type here. Can I type here? Yes, I can because I changed the cell protection. So if I want to go back to it, I can't. I can't go back to format cells on this. I own protective sheet and then I can take areas. I could have taken this range and just go here on and uncheck this. I could have done that as well. It was the same thing and going here. This just gives me more control by allowing me to also put in additional passwords for different ranges and also name and group my ranges. I have a good overview off the parts that are open. You can also apply the same concept on anything, so on graphs on objects. So if I had an object in here, it's just insert a shape. Let's put in some text. If I protect this sheet, my object is also protected by default. See, I can't click on it. It's fully protected. If I did want people to change the text or change the shape, they go to the proper cheese and they take away the locking here and I get an additional control over the text. So if I also want to give the ability to the people to change the text, I can take away this check mark. Now I'm going to protect the sheet, and this object is open. I can change this, but everything else is protected except, of course, the cell. Now, the last thing is that you can protect your worksheet but also allow certain features to be open. So in this case, remember we left this part open. But let's say we told the agents, Please highlight the role where you think you will have some problems with the product. If we had done this protection like we did before and I want to highlight this rule because that's a problem product. I can't. Everything is disabled for me to allow them to make this change. What I can do is to go back to review when I want to protect the sheet. You see format cells here. I can put a check mark on that because I can say, well, some features I do want them to control. If I wanted them to insert columns or insert Rose, I can let them do this. So you have some options here. In this case, I'm going to say, Let them format the cells and then they put a password for it, and then I protect it. This make sure is protected, Yes, but now let's say I want to go and say, This is my problem product. I go here, these options are available and I can highlight this. As he can see, you have a lot of options here to control the extent of your protection. Protecting workbooks and worksheets are must if you're distributing a template for others to fill in complete no proper protection reduces the mistakes and minimizes the need to make any corrections, you get to actually concentrate on data quality. I'm not on copying and pasting and correcting data. So do practice this one 8. Tip 6 - Import Text, CSV & Web Data Properly: Excel has various tools that can help you easily clean up your road data for for their analysis. So, apart from using formulas and formatting there, too Useful Excel features that let you easily cleanup. One method is text to columns, and the other is importing external data. What I've seen people do when they export data from a system and they want to bring it into excel is that they copy and paste it in there. Don't do that because you can run into problems. One of the problems can be with the decimals and thousands separators because, especially if you're dealing with international companies because in the US the decimal is a dot and the karma is a thousands separator and generally in European countries, it's the other way round where the comma is the decimal and a death is a thousands separator. Now it's always safest to import it properly, using Excels wizard, because you can control this. What you can also do if your data is on the Web, is that you can imported directly using a Web query, which you can actually set to refresh automatically based on your preferences. Let's have a look at these in action. Let's start with text to columns first. So in this case, and have a very small sample off full name, and I want to split them to first name and last name into two separate columns. Many people are familiar with this feature, but they actually forget to use it when the time comes, and instead they try to use formulas to do this, which can be more time consuming. But instead, what you can do is to highlight the column that you want to split and know that it has to be one columns. They can't be many columns like this. Everything has to be in one place, and then you go to data text to columns. And here you kind of see a preview of your data as a first option. In this wizard you have to select if it's still limited or fixed with generally, things will be deal emitted. Fixed with is when everything would have the exact same with everything would be four characters and say here and then here. Five. If it's not, if there's some other logic that you can use to make this split, you will use delimited to them legal next and here we have the option to select what this logic is. So in our case, a logic is a space. You have these options here, but if you have some other split that is not here. So let's say you have this one. You can actually type it in here. It looks good in the preview. In the last step, we get to choose the column data formats. So if these should be formative specifically as text or general or date, we can select each column and select a formatting here. In this case, I'm fine with excels, General. So I believe that, as is here, you can select the destinations. If you just leave it as is, it's going to overwrite what you had here. If you don't want it to overwrite, select another destination here, okay? And then you say finish and you have your name split as a next step. I'm going to show you how you can import external data in the former of a CSE foul or a data file or a note pat file. Usually you can get data extracts from any system. So whether you have a finance system an ear piece system. You're able to get some type of data extract in a pre defined structure into a note path file that can have many names. So some people call it data files or CSP files for coma separated files. They're all the same things. As long as you can get some type of file with some structure, you can import it in Excel. And you should do that using the wizard from the data tap. In this example, I'm just going to show you the file that we're gonna import. It's a very small sample data, but it has some structure and some large because you can see here. So going back to her excel, I'm going to go to data from texts. This is something you have to be careful with because excels filter always defaults to text files and those should have an X session like that. See, SV, if your file doesn't have that and in my case it doesn't. It's a dot de 80 fell that fell. I can't see that until I change it toe all files. So just remember this when you are importing text files, that's the one I need I'm gonna import. So here I have to choose. If it's still limited or fixed with In this case, I can see it's not a fixed with its. Actually, there's some logic that's that's giving me the split, and that's a coma. So I'm going to go next and here. It's not a space, my deal emitter, but instead it's a coma. So I'm gonna select that next here again. I can select each column and they can say, Is this a text a date? Or I leave it as general. In most cases, General is fine, But there are cases when you might have product labels, starting with zeroes 000 something. And if you leave that as General Excel is going to strip out the zeros when it imports because it's going to think it's a number. If you don't want that to happen, do highlight that column and change this to text. That way you can keep your zeros. The other good setting that you can control here is this one. And here is what I mentioned before, where you can control your decimal and 1000 separator. So if your excel is different to your source, system. You can actually change that here. In my case, it's fine. I'm going to leave that as is on finish. And now I have the choice of importing right here if I want to, where I can go to a brand new worksheet, especially recommended to use a brand new work shit, Especially if you're planning on changing the numbers, re saving it s C s, we or data file and then reloading back to your system. And now I have each category sitting in its own column. I can do some Sundays filtering Hyundais or use it to feed my dashboard. This is not a one time import notice that you have connections and refresh here highlighted . What it's done now is that it's built a connection to this text file that you've saved in your folder sheets. So if you have some type of specific routine that saves the file always with that name into that folder, you can refresh it here on you will get your new numbers. What you can also do in properties is to set the refresh times Here, you see, you have the control you can say refresh this every 60 minutes or refresh data when you open the file, and here you have some additional layer options that you can control. One other tip is that you can place control T, and what this does is it turns your data into a table. It just created some headers for me. We just control T. I get this whole table tools options that I can use to quickly, like filter on accounts if I wanted to. And I can also add a total rural here. So if I would, then that's a filter on these two accounts. See, my total also automatically updates, says Really need tool to analyze your numbers. You are also able to come birds back to range. So let's say you did some analysis on this, and then you want to save this again as a DAT file or a cease FIFA on low back to your system. I'm just going to delete these because I don't need the's Rose. Delete this. Then they would change these two. Let's say 400 600 only have to do is do foul savers save it here. And instead of an Excel workbook, I want a CSP file. It's this one, you just give it a name. You get this, that it doesn't support multiple worksheets, but that's fine. We just want this one worksheet. It also tells you some features are not compatible with. That's because the colors and so on that's not compatible. But that's fine. Then if I go back to my foul, I can see the updated CSP file, which I can use to upload into my system as the last step. I also want to show you that you can do a Web Aquarius well, So if there's some data that you need from the Web, you can use this. Let's try to get some data from still Google Finance, and you see this year's is click this next to the tables you want to select and then import . Not every single webpage and data is gonna work with this, but a lot of them do. So here's the world markets. Let's see what we have here currencies. So let's say we want the currency's. I'm going to click on this and I'm gonna say import case. In this case that's just imported here. I have my Web query. I can set the properties that it refreshes every whatever minutes I want or that it refreshes when I open my foul and then I can use the state. Obviously, since they're sitting in separate cells to manipulate or to use as I want, this feature can come in quite handy when your data is on the Web. 9. Tip 7 - Audit & Debug Formulas: Once you've mastered Excel functions and you start to build longer and more complex Excel formulas, you're likely to run into two problems. One is that you have mistakes in your formulas, but because there's so lang and complex, you can't find where the problem is and to the results of your formula chosen error for some cells, but not for the others, and you're not sure why. Now in this lecture, I'm going to give you a guide of the audit tools better available and how they can help you track and d block your formulas. Let's assume you get this foul from your colleague, so you open it and you try to make sense of it. The first thing I would try to do is maybe to try to find out where are the formulas here and what they are. I would go to the formulas tab under formula auditing tools. You have something called show formulas. It's basically just a toggle button. You can turn it on and off, and here I can quickly have an overview off the type of formulas that are used. So I know these are a bunch of ifs. There's a some and that's just this cell multiplied by that one. Then they can turn it off if I want to analyze the relationship between the cells. So, for example, if I want to say which cell is feeding this and which cell is dependent on this, I can use the trace precedents and trace dependence features here. So if it will click on trace precedents, I can see this range is feeding this. And if I click on trace dependence, I can see that this one is dependent on this number. Kind of gives you an overview off the dependency roll the cells play. You can remove the I rose by just clicking this. If there were some errors in this foul, I could use the error checking feature here to get a bit more information on the type of error so this error checking feature can come in handy. Another one that can come in handy is the watch window. The watch window is great for people who work with bigger files and who worked with cells that are dependent on other formulas in other worksheets. So, for example, in this case, the revenue is basically just this quantity multiplied by the average price that I've typed here. But if, for example, this average price was being derived from another sheet, let's do it as the average it's go to here. They have price. Yes. So not this cell is actually dependent on this. And this one is dependent on this sheet. If I want to kind of watch this cell and watch the value of the sell, what I can do is to watch this cell in the watch window. I'd watch tells me if I went out a watch here. You just say add here. I can see my excel workbook sheet the cell on the value in the cell. So if I goto f four now, so this is kind of a window that comes with you Wherever you move, you can make it smaller or bigger if you want. Keep your eye on this value. I'm going to change this to let's see 200. You see this value changes. I don't need to go back here and have a look. I can stay here and look at myself, and it also works across workbooks. So if you have formulas that air linked to other workbooks you can always take your watch window with you to the other workbooks and watch the cell that you want to keep an eye on. You can also use it on multiple cells. So let's say, if I wanted to watch these, I can highlight that and say Add watch creates a watch window for each separate one. If I want to delete them, I just like click on the one I want to delete and say Delete, watch or I hold shift down, then go down and delete Watch here when you can close the watch window. Next is the evaluate formula feature. This could be very helpful. When you have bigger formulas, I'm going to show you how you can use it here. You're gonna appreciate it more, though. When you work with more complex formulas what it does, it helps you debug your formulas if you get stuck. So it's kind of like stepping into your formula and translating it for you, kind of showing it to you in slow motion. Evaluate and you see it changes me self reference to the number this is if 300 is less than 200. So is that true or false. That's false. So basically, if it's falls, then it should do nothing because the flag one that comes right after here is what it should do when it's true. So in this case, it should do nothing if I would move to this cell and one my evaluate formula, he sees 100 less than 200. True, So true, then flag. So it flags. This one is a very simple formula, but just let's try it. Is this times this? Basically, it's 38 10 multiplied by F three. That's the number in there, and that's her answer. Don't forget to try it out on your own more complex formulas in your own files. 10. Tip 8 - Customized Number Formatting Tricks: custom formatting is a great way to manipulate the presentation of your data without actually changing the data. Now many Excel users try to use intermediary steps to change the look off the data, and these steps congenitally be avoided with the smart use of custom formatting. The Main Excel formatting types are these. You have the general, which is this one on. By choosing General, you basically put it in excels hands to guess the best suitable number formatting for your cells. Now, in most cases, it's OK because it recognizes that you input a date or a time or that you put a number or text the other types of for mounting or specific, like number, currency, accounting, date, time, percentage and song. And these are generally self explanatory, and the best is for last all the way in the bottom. You see custom, and this is used to create your own type of formatting. You have a lot of flexibility here. You can add text your number. You can tell Excel how to display positive numbers, what to do with negative values on how to deal with your zeros. You also have some limited options on defining the color of your font. If it's positive, and if it's negative to understand and work with customized formats, this syntax is what you need to keep in mind. First comes the positive number. So how you want a positive number to be formatted? It's split by this semi Cullen. Then how you want your negative value to be formatted than zero and then text. Now, you don't necessarily need to have all of the four parts included. So if, for example, you just have the 1st 2 Excel is gonna use the positive number to form a your zeros to keep that in mind. And if you just specify only one formatting so you don't use semi colons at all, then it applies that formatting to positive and negative values. In the demo workbook, I've included some examples that we can go through to get you familiar with the most important characters that air needed when you design your custom formats. But first, let's just have a quick look at them, so control one or right mostly format cells on custom is down here, so let's have a look at what is available. I have these in mind you can see there is this hash sign or this number sign, and then you have zero as well. That's your semi colon that splits the positive and negative formatting. Some have the dollar sign. You can see red being used here. Let me just explain you the basics. And the basic is what's the difference? First, between this hash sign on the zero, I'm going to create a new formatting with just the hash sign. So you just go to custom and here under general, you type. Or you can click on an existing one and modify it, which is actually my preferred version when I am designing my own custom formats. In this case, I'm gonna informant only with this hash sign in a suitcase that that's this cell. And let's input a number two Hate Nothing happened. What this sign is is that it's just a digital placeholder. For your number, zero is the same. It's also a digital placeholder for your number. The difference is that zero is fixed. It forces your value to be as long as you type in here. Let me just demonstrate. So in this case I have hash hash can do a preview here, so that's two. But if I change it now 200 it becomes zero to, he said. Its forces it to be that long, whereas the hash doesn't the purpose of thes are just placeholders for your number. Another place holder is the question mark. It's not very common, and all the question marks does. You can see if I put two times. It adds a space before it for 20 but it's basically also a placeholder, but it's used mainly if you want to align the decimal places, and we'll get to that in an example. Up here, the two main was to remember by this number signed a hash sign under zero. The comma is the thousands separator, and the dots will be the decimal. This formatting basically means display commas for every three digits. No, let's have a look at expanding on this and let's say in putting text, if I wanted to add something here, I have to put it in quotation marks. You can see the sample here. What happens is that I see great, too, but the value in the cell is actually to it's still a number, even though it doesn't look like a number anymore, but it is because I can say this multiplied by two, is for it works. You recognizes it as a number, and that's the good thing about it, because in some cases you might want to add text to your numbers. For example, if you want to show in millions or the currency, but you still want it to be recognized as a number, these were the basics off the custom number formatting. So let's go through our examples to practice some. In this case, we don't want to show zero values. So I highlight this and I go to control one. I can see the first number I have in place, but I will have to leave the custom formatting to see what happens to the other numbers. So let's assume I don't want a thousands separator. I just want a number. This is basically how I want my positive values to be formatted. How do I want me negative to be formatted with this, and how do I won't lay zero to be formatted? I don't want to show it like this, and I say OK and it's gone and why because the first part is my positive second part is me negative. Third is how zero should be. So if I wanted the zeros to be like this, I see that what's the last part? That's text. The placeholder for text is this sign. Generally, you don't have that many options. If you're certain things you want to add to your text, you can put it here in quotation marks. So, like hello and then your texts. In case you have text in there, it will add a hello to it. So let's say okay, and let's change this to there and it's hello there, even though it's only there. But because of our formatting, whenever it's his text, it adds hello to it. You can leave it empty if you don't want to form a text in any special way. This would be the four munching for days. Next, we want to align the decimal places here. That's when we can use the question mark for this. And here we can say the 1st 1 is the number. Then it's a dot and let's say we want to a line based on this one's. I put three question works on. Okay, You can see they're all aligned where the decimal is here. We want to add to trailing zeros before this number, which is something we might need to do if we want to load our numbers to another system. And excel always strips them off unless you format them as text to make them look like they have to trailing zeros. What do you need to do here? You can say 00 and three zeros. Next. We want to get this effect. We want our positive numbers to be formatted as green are negative to be shown in brackets , the zero as a zero. This is where we can use this square brackets, red and green. Let's have a look at this formatting. We have 2000 separators. I can start with this one from a positive number. I wanted to be green, and all you have to do is to put square brackets here and type in green and close the square bracket. Then you have your number. That's how it should be when it's positive. When it's negative. I wanted to be in brackets, I put the brackets and then I can copy the same number form in here. I'm close brackets. And what do I want for zeros? Just normal zero. So if I don't do anything, let's just leave it like this and see what we get. C zero is green. I don't want that. I actually have to specify what I want for zero. And I just want a zero. If I wanted this to be read, all they have to do is add red right here. I generally don't like excels, Defaults green. The red is fine, but the green is too bright. So you have more options. So let's go pick our format. And your for matching his memories is down here. So we use this one instance of green. You can put color 10 and that's the better green. How do you know these codes? I have the link for you here. So you click this. You come to this link and you have the color index that you can use to get the color you want. Now, if you've noticed some off excels formatting have this underscore with the bracket. What this means is that the values shouldn't touch the cell. There should be just a little bit of space between them on the border. Let me show you if I want to add a space right after this, I will put this underscore and the bracket. So now watch this. They moved a bit and you can do it from both sides because no is just one sided. If I have it this way, they're stuck there. So if I go back and do it before, there was also not stuck to the border. You can apply the same thing for negative values as well. You just they need a double bracket. So let's apply the formatting to this one. And then let's go back. And here you just need to add it before the bracket. And then after the bracket, No, from this side on this side. Next, we're going to look at how we can create a custom format for millions. So let's say we have this table. This is our volume. That's a revenue, and we want to show the price here. But we want these cells to look like 300 m in it. So what I can do to format numbers for millions or thousands is to come here. We can pick one of our basic ones. Let's go with this one to show this in thousands. All they have to do is to add one comma to show it in millions to add to commerce, see strips off zeros, and then I can even put a text in it. They are all formatted to look like this, but in reality, this number is actually 300 million. And my formula still works because it recognizes this as 300 million. In her last example, we want to create a custom format that shows our currency on the left hand side of the cell . The currency should be aligned just like you see in the screen shot here, and the numbers should be on the right hand side. Maybe we can check what we have here under currency. We have the dollar. Let's check for euro. Here it is. We can take that and take away two decimal and say OK, no, I don't want the minus values to look like this. Let's go and change the custom formatting of this. You can see that's the four Magic it's applying right now. I wouldn't change that. It doesn't matter in this case, but I wouldn't put it like this. It's just a bit cleaner for the positive values. I'm fine with this for negative. I don't want my negative sign to be here, so I'm gonna change that. Basically, I just have to copy this and put minus here. That looks better, but I still haven't aligned it yet because I want the You were to be undecided. The trick for aligning is to use the multiply sign and you make sure there's a space. What this operator does is that it repeats the next symbol. And the next symbol here is just empty is nothing. It repeats it to fill the cell. And we want our cell to be filled with emptiness between euro on between her number. I'm going to copy that. Put it also here. No, you see, you get this nice format I can add on to the formatting, as I want. I can add also green to this and if it's negative, it should be read. And maybe in brackets. If it's zero show nothing. So now let's have a zero. It's nothing. And all this flexibility you get with custom formatting here you can find the list of the characters that you can use and custom formatting along with their descriptions. We can print this out and have it handy whenever you're creating new custom formats. 11. Tip 9 - Solve Problems With Goal Seek: What's the purpose of gold seek? Well, it's when you know the end result, but you don't know what the input should be to get to your end results. Now GLC can be found in the data tools tab as part of the section. What if analysis the following Describe Gold six purpose. It's the opposite of a formula. Instead of finding the end results, give it to end result and it gives you the import it automates. The trial and error process is actually something like guessing on speed. It's useful for situations when you really want quick answers. And using will seek instead of a formula can be much faster to set up if you know how to use it. And the main advantage of gold sick is that you don't have to change your formulas or turn a formula field toe an input field. This assume we have the falling situation, your current revenue ISAs follows, and then your boss comes to you and says, Well, how many units do we need to sell to reach a turnover target off 3 50? Sure, you can turn your formula around or you can do a second calculation or you can use grand seek. That's what we're going to do now. It's very simple. You can use it in stress situations, even when someone is standing behind your shoulder and they're staring at your screen. All you have to do is this. You're going to go to gold seek by going to data. What if analysis and you find it here. If it's something you find yourself using more often just right mouse click and add it to your quick access toolbar. So you click on Goal Seek. Here you can see I have a formula. So that's the units sold times Price equals my turnover. I've been asked to find out how many units we need to sell to get a turnover of 3 50 So I need to change the value of this cell to 3 50 by changing, which so the units gonna see. Okay, then it tells me it's found a solution and then say OK, and I can see I need to sell 73 units to get to the turnover of 3 50 and it does this without overriding anything. My formulas are still intact. The good thing about it is that if you're reports are longer and this formula is feeding into other formulas. You want to change the say your last formula and see what is the original input you can use goal seek for that as well. So in this case, I'll turn over here. We're analyzing it. We're using a working capital information on We're calculating a working capital percentage . So ultimately, the unit is feeding in this one. This is feeding into here, and this is fitting into this. And now we're told we need to reduce working capital to 10%. What should be the amount of units we need to sell so again you go back to go seek. We're setting this cell. In this case, it's a value. We need to input him because it's a percentage here. They have to put 0.1 for 10% by changing our units sold. The number of units we need to sell is around 84 85. To get to a working capital off 10% you can also use gold seek to calculate things like loan payback. So let's say you want to borrow alone worth $10,000 you have an interest rate of 8%. The amount of months is 60 so you can pay this back in five years. And how much do you need to pay back on a monthly basis? Now there's a formula for this in Excel, and it's called the PMT function. So if I go in it here, it gives me more information about the parts I need to input. You can see calculates the payment for a loan based on constant payments and a constant interest rates. In this case, the rate is my interest rate, and the interest rate here is an animal one. I need to divide my rate by 12. The number of periods, the months I want to pay back is this one. They present value. My loan is this one the future value and type. I can leave empty. The amount I need to pay back is this, and it's negative because it's a payment. So now let's say we look at their expenses and we say, Well, we can't We can't pay back this much on a monthly basis. The maximum we can pay back is $150 per month. What can the influence what we need this amount of money. Let's say in this case we can't negotiate anymore the interest rates, but we can change the months and again we're gonna use goal, seek to find out how many months we need to be able to pay back this loan. If we can only afford to pay back 150 per month, we go back to go seek. That's the cell is fine. Our value is 150. I have to put in minus 150 by changing this one. So that's 88 a half months, which basically translates. It's somewhere between seven around 7.5 years that I need to pay back this loan. If I can only afford to pay this much back, that's cool. Seek. It allows you to find out what inputs you need to get a different, calculated outcome 12. Tip 10 - Speed up Your Excel Files: in this lecture, I'm going to show you some straightforward techniques you can use to significantly improve any slow, calculating workbooks. These are the steps you need to take to rejuvenate your slow workbooks just before you do them. Take it back up of your workbook first. As a first step, go to formulas. Name. Manager. Do you have any invalid references there? Anything with hash ref? If yes, sort on these and delete them completely now, go to data connections. Do you see the edit links button grayed out. If it's great out, you can skip the step. If it's not great out, click on it. Do you have any workbooks that are connected to this already? Still valid? If you don't need them, delete them from here as a next step, check and clear any unused cells in your worksheets, and the way you do that is, you go to each worksheet and then press control end. You should end up at a spot just at the end of your data or your report. If you see that you're far beyond this, that there's too much empty space between the control end cell and your actual report, then go and highlight these cells. Basically highlight. Go to the end of your report. Click on the rule right after it. And then press control Shift on the down arrow key. And then right. Most click and delete. These cells do the same for columns if you need to. Then press, save and then press control and again. And now you should end up just at the bottom of your data or your report. And with this, you have cleared any unused cells from memory. Next is to double check your formulas. Are you using too many workarounds? Is there a better function that can be used? Are you referencing too many cells? If yes takes some time and implement the new formulas last is the usage of volatile functions and conditional formatting. If you have too much of thes, they can be a factor in slowing down your files. So if you followed all of these other steps and you still have a slow file, then double check the use of these functions to understand what volatile functions actually are. Let me explain to you how normal Excel formulas work so normal known wool, it'll formulas get recalculated any time you change a cell that impacts the formula. So if you change a cell that has no impact into formula, nothing gets recalculated. Let's assume you have a large Excel foul that has thousands of formulas, and many of these formulas reference other cells, which might in themselves also be formulas referencing other cells. Now this dependency chain is memories and stored by excel in what is called a dependency tree. So Excel uses dis dependency tree to decide which sells to re calculate and which cells to ignore for normal formula calculation. Excel always refers to this dependency chain, so if you make it change to a sell, it also updates this dependency tree. On the other hand, volatile functions are formulas that get calculated any time you enter any data in your workbook, so they get recalculated. Independent of whether you're formula is referencing these cells. It could be anywhere in any cell that has nothing to do with this formula. They also get calculated anytime you insert a new row or column you rename worksheet or you change the position of your worksheets. Any task can run the calculation of these volatile functions, and that's what can make your workbooks slow. Some examples of volatile functions are offset today. Now indirect rand and rand between. And so now, if you're an Intermediate Excel user, chances are that you already are using some of these regularly, especially to offset function in your reports. I have a lot of offset functions in my reports, but this doesn't mean that offset is going to slow down your file. So you should just be aware that it could have an impact depending on the complexity and the overall size of your workbook. If it does use alternative functions or methods, we can use index and matching in lot of cases. Or you can write VB a quote that does the job for you. These are the main steps to follow to turn around any slow workbook, Dixon says. For this, one needs to get done on your own existing fouls. But as a precautionary step, don't forget to take a backup of your file before you follow these