Microsoft Excel - Become real Excel specialist!

1. Excel interface: Hello everyone. Welcome to Microsoft Excel 2016. For those who don't know, Excel spreadsheet program that allows you to store, organize, and analyze information. While you may believe Excel is only used by certain people to process complicated data, anyone can learn how to take advantage of the programs powerful feature. Whether you are keeping a budget, organizing a training log, or creating an invoice. Excel makes it easy to work with different types of data. If you've previously used Excel 2010 or Excel 2013 than Excel 2016's should feel familiar if you are new to Excel or have more experience with older versions, you should first take some time to become familiar with the Excel interface. In this part, we will see the Excel interface. When you open Excel 2016 for the first time, the Excel start screen will probably look like this. From here, you will be able to create a new workbook or to twist the template and access you recently edited workbooks. From the Excel start screen. We will now locate and select blank workbook to access the Excel interface. Now see how it is to work with the Excel environment. We will start from the ribbon and quick access toolbar, where you will find the comments to perform common tasks in Excel. Also, the backstage view here, which gives you various options for saving, opening file, printing in sharing your document. So let's start with the ribbon. Excel 2016 uses a tab ribbon system. Instead of traditional menus. That means that the ribbon contains multiple tabs with each with several groups of comments. For example, group for font, group for alignment, group for number, group for South, and group for editing. For example. You will use these steps to perform the most common tasks in Excel. Each step will have one or more groups. As I can, as I already told you. Here is for font, font size, bold, italic underline, it sets it up. Some groups will have an arrow, which you can click for more options. For example, like this. Here you have Format Cells, number, alignment, font, border, feel, protection, etc. So for other tabs, you can just click on tab to see more commons in more options. As you can see here. For example, data where we have existing connections, queries, table, etc. You can also adjust how the ribbon is displayed with the Ribbon Display Options, which is this up arrow here. As you can see here, Ribbon Display Option. There are three options, how the ribbon can be displayed. The ribbon is designed to respond to your current task, but you can choose to minimize it if you find that it takes up too much screen space. For example, click the Ribbon Display Options in the upper-right corner of the ribbon. To these play the drop-down menu. There are three modes into Ribbon Display, Options menu. First, aldehyde ruin. This place your workbook in full-screen mode and completely hide the Ribbon. To show the ribbon, you will just click on the expand ribbon comment at the top of the screen. Second option, which is Show tabs, will enable you to see just tabs. By clicking on tabs, you will see comments. But also third option will give you also tabs and comments, as you can see here. So you will decide which option do you want. Second thing we will talk about is the Quick Access Toolbar or this option here. It is located just above the Ribbon, and it lets you access common comments. No matter which tab is selected. By default, it includes safe option, undo option, and Redo option. I just added these cut option. How you can add comments to the Quick Access Toolbar. You have drop-down arrow to the right for objects Quick Access toolbar. By clicking on it, you can select comment you'll want to add from the drop-down menu. To choose from more commons, just select option more common. And you will see here all comments did you can add to your Quick Access Toolbar, for example, or we remove cut. But I will add Email option by clicking Okay, it will just appear here. Option, that's Excel 2016 head has an otter. Excel didn't have the option. As you can see here, we have, tell me, what do you want? How to use? Tell me the Tell Me box work like a search bar to help you quickly find tools or comments you want to use. For example, what is the best here is that you can type in your own words whatever you want to do. For example, Ed, a chart. By typing this in my search box. I will just hit option here through cells will give me a few relevant option to use one, I will just click it like you would a common under ribbon. For example, insert column or bar chart. I will have more options here. And if I don't like those options, I can choose more column charts and see what else I have. Next thing we want to know about Excel worksheet. Worksheet views. Excel 2016 has a wide variety of viewing options that change how your workbook is displayed. These views can be useful for various tasks, especially if you are planning to print the spreadsheet. To change worksheet views, you will locate the comments in the bottom right corner of the Excel window and select Normal view of which is already selected here. But you have also Page Layout View or Page Break Preview. Normally over is the default view for all worksheets in Excel. This is, as you can see here. Now, Excel worksheet layout. View this place how your worksheet will appear. One printed. You can also add headers and footers here, as you can see. Third option, which is Page Break Out. Break view, allows you to change the location of page breaks, which is especially helpful when printing a lot of data from Excel. And the last thing in this lecture about Excel worksheet is backstage view of next day to view gives you various options for saving, opening a file, printing, and sharing your workbooks. As I told you, how to access backstage view of, it is simple by just clicking File on the Ribbon. Backstage view will appear. As you can see, we have a lot of options here. Now we'll see what is every. So we have first this arrow, which will give us return to our Excel worksheet. But we don't want. Now, if option will give us Excel spreadsheet and all our information about our current Excel spreadsheet. Option new will give us the ability to choose a new blank workbook or to choose any template we want to use for the Excel documents option, open will give us our recent files. Our one tried files also fires on our PC. And of course, our current Files option Save will give us the option to save our current document. Save As will give us more options. Option print will give us ability to print an information that we need about our Excel document we want to print. Option share will give us the ability to share our document which other people by e-mail or saving to cloud, etc. Option Export will give us the ability to create a PDF document or to change file type in any other option, Publish will give us the ability to save our document for business OneDrive and publish our document to power business intelligence. Options, clothes will give us ability to close our Excel current document and account will give us information about our office document. Options. Will open a new tab, which will give us more options about Excel documents we meet. This was all for this lecture. Hope you enjoy it and see later in next one. 2. Creating new and pinning documents: Hello, Welcome to Microsoft Excel 2016. Excel files are called workbooks. Whenever you start a new project in Excel, you'll need to create a new workbook. Today's topic is how to create a new workbook or to open an existing one. There are several ways to start working with a workbook in Excel. You can choose to create a new workbook, either with a blank workbook or a pre-designed template or open an existing workbook. Now we will see how to create a new blank workbook. First step in that way is to click on Tab file. And backstage will appear. Then we will click on File New and choose blank workbook. As you can see here, we have now opened blank workbook and that's it. To open an existing workbook. We will see now steps. Because in addition to creating new workbooks, you'll often need to open a workbook that was previously saved. And you will do it in this way. First, you will navigate to backstage view and click Open. As you can see here, you have various options from recent documents. The OneDrive and the SPAC will now click on computer and then click Browse. As you can see here. Open dialog box will appear. You can now locate and select your workbook and click Open, for example, like this. As you can see here, I now have my contact list. If you open the desired workbook recently, you can browse in your recent in your recent documents as I already told you. Rather than search for the file. Now, we will see how to pin a workbook. Because if you frequently work with the same workbook, you can pin it to backstage view for faster access. And you will do it in this way. First, you will navigate to backstage view as I'm already here. Then click Open. I'm already here. And you recently edited and workbooks will appear, as you can see here, recent. And from today. By hover the mouse over the workbook, you want to pin. A pushpin icon will appear next to the workbook. You will just click on the pushpin icon. As you can see, now, my contact list is pinned. The workbook will stay in recent workbooks. And to unpin a workbook, you will just simply click the pushpin icon again. You can add any number of documents that you want. And again on peanut. In this way. This is all for this lesson. And see you later in next one. 3. Using templates: Hello, Welcome to Microsoft Excel 2016. Today's topic is using templates. A template is a pre-designed spreadsheet you can use to create a new workbook quickly. Templates often include custom formatting in predefined formulas. Soda can save you a lot of time and effort when starting a new project. Now, we will see how to create a new workbook from a template. First step is to click the File tab to access backstage view. Then we will click on New. And as you can see here, several templates appeared below the blank workbook option. You can just click on a template to review it. And if you like this, you can just click on Create. A new workbook appeared. We deselect a template. You can also browse template by category or use the search bar to find to find something more specific. For example, if I don't have here budget templates, I will just click on budget and Microsoft Excel, we'll search thousands of online templates and give me some of them to see which one I want and which one is a dumbest compatible with my projects. So what is important to note here that not all templates are created by Microsoft. Many are created by third party providers and even individual users. So some templates may work better than others. Next thing we'll talk about is a compatibility mode. Sometimes you may need to work with workbooks that were created in earlier versions of Microsoft Excel, such as Excel 2003 or Excel 2 thousand. When you open these types of workbooks, they will appear in compatibility mode. Compatibility mode disables certain features, so you will only be able to access to access commons found in the program that was used to create the workbook. For example, if you open a workbook created an Excel 2003, you can only use tabs and comments found in 2 thousand in Excel 2003, for example, like this option. As you can see here, it is written compatibility mode. In order to exit compatibility mode, you will meet to convert the workbook to the current version type. However, if you are collaborating with others who only have access to an earlier version of Excel, it's best to leave the workbook incompatibility mode so the format will not be changed. We now see how to convert a workbook. If you want access to all of the Excel 2016 filters, you can convert the workbook to the 2016 file format. But note that converting a file may cause some changes to the original layout of the word book. And steps for converting a workbook is like this. First, we will click on the File tab to access backstage view. And as you can see here, first option is to convert. By clicking on a convert comment, we will get our warning that the workbook conversion to the current file format completed successfully. As you can see here, it is asked, Do you want to close and reopen the workbook now? I will click Yes. Here you will see now my document in 2016 Excel. This was all for this lesson and see you later in the next one. 4. Saving workbooks : Hello, and welcome to Microsoft Excel 2016. Today's topic is saving workbooks. Whenever you create a new workbook in Excel, you'll need to know how to save it in order to access and edit it later. As with previous versions of Excel, you can save files locally to your computer. But unlike older versions of Excel, 2016's also lets you save a workbook, the Cloud, using OneDrive. You can also export and share workbooks with others directly from Excel. Excel offers two ways to save a file, save and save as these options work in similar ways with a few important differences. Save. You will use when you create or edit a workbook to save your changes. You will use this comment. Most of the time. When you save a file, you will only need to choose a file, name and location the first time. After that, you can just click the Save common to save it with the same name and location. Same as you will use to create a copy of a workbook while keeping the original. When you use Save As you will need to choose a different name and or location for the copied version. We will now see how to save a workbook. It is important to save your work book whenever you start a new project or make changes to an existing one, saving early and often can prevent your work from being lost. You will also need to pay close attention to where you save the workbook so it will be easy to find later. So first step is to locate and select, Save comment on the Quick Access Toolbar. I will now have one of my documents, so I will click on File. Save on my Quick Access Toolbar. If you are saving the file for the first time, the save as pain will appear in backstage. As you can see here. You will then need to choose where to save the file and give it a file name to save the workbook to your computer, select computer, then click Browse. Alternatively, you can click OneDrive to save the file to your OneDrive. But if you click on Save, As in your computer, as you can see, the Save As dialog box will appear selected the location where you want to save the workbook. For example. I will see desktop. Then enter the file name for the workbook. For example, sales data. And click save. The workbook will be saved. You can click the Save comment again to save your changes as you modify the word book, for example here. You can also access the safe common by pressing Control plus S on your keyboard. Now we will see how to use Save As if you want to save a different version of a workbook while keeping the original, you can create a copy. For example, if you have a file named sales data, you could save it as sales data to or sales data edited. So you will be able to edit the new file and the steel refer back to the original version. To do this, you will click the Save as common in backstage view. For example. Dislike once saving a file for the first time, you will need to choose where to save the file and give it a new file name. For example, I will choose again, desktop sales data editor it safe. And finally, see how to change the default save location. If you don't want to use OneDrive, you may be frustrated that OneDrive is selected as the default location when saving. If you find it inconvenient to select computer each time, you can change the default save location. So computer is selected by default. You will do it in the next way. You will click the File tab to access backstage view. Then you will click options. And the Excel Options dialogue appeared. You will select Save and click Save to computer by default. So default, the default save location will be changed. You will just click, Okay? And now, every time you save your document, it will be on your computer, not your OneDrive. 5. Auto recovery and exporting workbooks: Hello and welcome to Microsoft Excel 2016. Today's topic is how to recover and exporting workbooks. Excel automatically saves your workbooks to a temporary folder while you are working on them. If you forget to save your changes or if Excel crashes, you can restore the file using outer to recover. Now we will see how to use R to recover. As you can see here. First step is to open Excel. When you open Excel and if outer save the versions of a file are found, did a document recovery pain will appear, as you can see here, document recovery and available recovered files. So to click, click to open on available file. And I will see my file. Or by default Excel, I'll say every ten minutes, one, we need to know if you're editing a workbook for less than ten minutes, Excel may not create an outer saved version. Awesome. One note that if you don't see the file you need, you can browse all out-of-phase file from the backstage view of just click the file type. One. Note that if you don't see the file you need, you can browse all out to save files from backstage view of just click the File tab in FAR and manage workbook, recover unsaved work books. By clicking on recover unsaved. You can see here in new dialog box all unsaved files that you can recover. Second thing, we will see how to export workbooks. By default, Excel workbooks are saved in the XLSX file file type. However, there may be times when you need to use another file type, such as PDF or Excel 972003 workbook. It is easy to export your workbook from Excel to aware with your file types. Now we will see how to do it. Exporting your workbook as an Adobe Acrobat document, commonly known as a PDF file, can be especially useful if you are sharing a workbook with someone who doesn't have Excel, a PDF will make it possible for recipients to view but not edit the content of your workbook. Steps for exporting a workbook as a PDF file are as follows. First, you will click on File tab to access backstage view. Then click Export and create a PDF or XPS file. As you can see, the Save As dialog box appeared. You will select location. I will click on documents where you want to export the workbook. Enter a file name. I will just Contact List, edited, convict lease, edited, and then click Publish. So my workbook is saved as PDF file. One node is a two. Whenever you export a workbook as a PDF, you will also need to consider how your workbook data will appear on each page of the PDF. Dislike printing a workbook. And finally, we will see how to export a workbook to other file types. You may also find it helpful to export your workbook to other file types, such as an Excel 972003 workbook. If you need to share with people using an older version of Excel or CSV file. If you need a plain text version of your workbook, steps for exporting a workbooks to other file types are as follows. You will click the File tab to access backstage view. Then you will click Export and change the file type. Select a common file type, for example, Excel 972003 workbook and click Save As. I have my dialog box Save As. And in documents, saved, my workbook. You can also use the Save As Type drop-down menu in the Save As dialog box to see workbooks in a wide variety of file types, such as here in this drop-down menu, you can use it. This was all for this lesson and see you later in the next one. 6. Understanding Onedrive : Hello and welcome to Microsoft Excel 2016. Today's lesson, our understanding, OneDrive and sharing documents in workbooks through OneDrive. Many of the theaters in office are geared towards saving and sharing files online. Onedrive is Microsoft's online storage space you can use to save, edit, and share your documents and other files. You can also access OneDrive from your computer, smartphone, or any of the devices you use. To get started with OneDrive. All you need to set up a free Microsoft account. If you don't already have one. Once you have a Microsoft account, you will be able to sign into Office. Just click sign in, in this right upper corner of the Excel window. By clicking on it, we have a new window where we need to type our e-mail address, which is related to our Microsoft account. And by clicking on Next, we need to add our password. Once you're signed into your Microsoft account, here are a few of the things you will be able to do with OneDrive. First, use, you can access your files anywhere. When you save your files to OneDrive, you will be able to access them from any computer, tablet, or smartphone that has internet connection. You will also be able to create new documents from OneDrive. Second benefit of using OneDrive is to back up your files. Saving files to OneDrive gives them an extra layer of protection. Even if something happens to your computer. One dry, we'll keep your files safe and accessible. Third benefit is to share files. It is easy to share your OneDrive files with friends and coworkers. You can choose whether they can add it or simply read files. This option is great for collaboration because multiple people can edit a document at the same time. This is also known as core authoring. When you are signed in to your Microsoft account, OneDrive will appear as an option whenever you save or open a file, you still have the option of saving files to your computer. But saving files to your OneDrive allows you to access them from any other computer. And it also allows you to share files with friends and co-workers. For example, I will go on file to access backstage. And by clicking on Save, as you can see, one derived personnel As option and also the SPC. And finally, we will see how to share our documents or workbooks. Excel makes it easy to share and collaborate on workbooks using OneDrive in the past, if you wanted to share a file with someone, you can send it as an e-mail attachment. While convenient, this system also creates multiple versions of the same file, which can be difficult to organize. When you share a workbook from Excel, you are actually giving us access to the exact same file. These, let's you and the people you share with added this same workbook without having to keep track of multiple versions. In order to share a workbook, it must first be saved to your OneDrive. And now we'll see steps how to share a workbook. First of all, we will click on the file and access our backstage view. Then we will click on Share. As you can see, because my workbook isn't saved to my cloud, I have option to save it to Cloud-first. As you can see, once my document is saved to my OneDrive, I have option share with people. By clicking on share with people, excel will return to normal view of an open, this sheer panel on the right side of the window. From here, you can invite people to share your document, see a list of who has access to the document and said, What are they can add it or only the view of document, document he is here in this drop-down menu, I can add it or just can be off. You can invite people. How many people do you want? And in this way, you will share the same document with them, but just by clicking on Share button. This was all for this lesson and see you later in the next one. 7. Cell Basics - Understanding cells : Hello and welcome to Microsoft Excel 2016. In this lesson, we will cover all basic information about South. Whenever you work with Excel, you will enter information or content into cells. Cell are the basic building block of a worksheet. You will need to learn the basics of cell and cell contents to calculate, analyze, and organize data in Excel. We will now see basic information about South. Every worksheet is made up of thousands of rectangles, which are called cells. A cell is the intersection of a row and a column. In other words, where a row and column meet, columns are identified by letters. As you can see here, a, b, c, d, e. While rows are identified by numbers 12345, each cell has its own name or cell address based on its column and row. For example, I will click on cell C9. So the cell is rest is C9. Note that the cell address also appears in the name box here in the top-left corner. And that a cell's column and row headings are highlighted when the cell is selected. You can also select multiple cells at the same time. A group of cell cells is known as a salary range rather than a single cell address. You will refer to a cell range using the cell addresses of the first and last cell in the salary range separated by a colon. For example, a cell range that includes cell from A1 to A4. Or A6 would be written as A1 cone A5, or for example, salary range A1 to F1. Second thing, what we'll see here is how to select a cell to input or added to sell content. You will first need to select the cell. For example, just click a cell to select it. I will just click any, for example, again, D9. And as you can see, a border appeared around the selected cell. In the column heading. In row heading will be highlighted. The cell will remain selected until you click on another cell in the worksheet. And also, you can select cells using the arrow keys on your keyboard. Without your mouse. After selecting a cell, we will now see how to select a salary range. Sometimes you may want to select a larger group of cells or a salary range. For doing it, you will just click and drag the mouse until all of the adjoining cells you want to select our highlighted. For example. I want to highlight this. Click and drag the mouse until the end of the adjoining cells you'll want to select. Then release the mouse to select the desired salary range. The cells will remain selected until you click another cell. In the worksheet. 8. Cell Basics - Cell content : Hello and welcome to Microsoft Excel 2016. In second part of cell B6 will cover cell content. Any information you enter into a spreadsheet will be stored in a cell. Each cell can contain different types of content, including text, formatting, formulas, and functions. When we are talking about text. It will be something like this. Cells can contain text such as letters, numbers, and dates. When we're talking about formatting attributes, cells can contain formatting attributes that change the way letters, numbers, and dates are displayed. For example, percentages can appear as 0.47 or 47%. You can even change a cell's text or a background color. And when a third option is in question, which is formulas and functions. Cells can contain formulas and functions that calculate cell values. In this example, we have weak sum of sales, urine a whole week by the sum formula equals sum B12, B9. So some B2 to be nine, adds the value of each cell in the salary range B2 to B9, and displays the total in cell B 10. Second thing we'll see is how to insert content. First step in this patch is to click a cell to select it. For example, I will click nine F. Just type something into the selected cell. For example, I will just click X and click Enter on your keyboard. The content will appear in the cell and the formula bar. You can also input here in the formula bar anything you want to change and add it to sell content. Now steps to delete or clear cell content. First step is to select the cell or more cells with content you want to delete. For example, I will choose this. I'll choose this row. Select the clear comment on the Home tab, and click on Clear contents. As you can see, everything disappeared, it is deleted. You can also use the Delete key on your keyboard to delete content from multiple cells at once. The backspace key will only delete content from one cell at a time. Now to see how to delete cells, there is an important difference between deleting the content of a cell and deleting the cell itself. If you delete the entire cell, the cells below it will shift to fill in the gaps and replace the delete it sells. To delete the cells, you will select the cells you want to delete, for example, like this. And select the Delete common from the Home tab in the ribbon. Click on Delete self. Now, you see the cells below shift up and fill in the gaps. 9. Cell basics - Copy, paste, fill handle: Hello and welcome to Microsoft Excel 2016. In the third part of lesson cell basics, we will cover how to copy and paste cell content. Excel allows you to copy content that is already entered into your spreadsheet and paste that content to other cells, which can save you time and effort. To do it. You will select the cell or cells you'll want to copy. I will choose this F9. We'll click Copy on the ribbon and just select one or more cells. You want to paste it and click Paste. As you see now, I have my content pasted into the selected cells. To access more paste options, we'll just click on the drop-down menu of paste and see that we have three different options for pasting. For example, formulas, formulas and number formatting, base values and number formatting and so on. Instead of choosing comments from the ribbon, you can access commons quickly by right-clicking, simply select the cells you want to format. Then right-click the mouse. A drop-down menu will appear where you will find several comments that are located on the ribbon. For example. As you can see here, different paste options. Second thing what we'll see is how to cut and paste cell content. Unlike copying and pasting, which duplicates cell content, caching allows you to move content between cells. For example, I will just click on those two and click Cut. Right-click on the mouse and select Cut common. Or alternatively, you can use the common on the home tab or press Control plus X on your keyboard. Second step is to select the cells where you want to paste the content. For example, I will select F 1011 and just click based. As you can see, now, my two cells are removed in F column. Alternatively, you can use the common on the home tab or press Control plus a we on keyboard to cut content will be removed from the original cells and pasted into the selected cell, as I already told you. Next thing we will do is how to drag and drop cells. Instead of cutting, coping and pasting, you can just drag and drop cells to move their contents. For example, the first step in this drag and drop cells is to select the cells you want to move. I will choose, for example, this, all these cells. And you will hoard the mouse over the border of the selected cells until the mouse changes to a pointer with four arrows. As you can see, now, you will just click and dropped. Oh, sorry, just click and drag the South to the desired location. I will choose G for G valve. And as you can see now, just releasing the mouse, the cells will be dropped in the selected location. And at the end, we will see how to use the fill handle. If you are copying cell content to adjacent cells in the same row or column, the fill handle is a good alternative to the copy and paste commons. Steps in using the fill handle are as follows. You will select the cells containing the content you want to use. For example, I will choose g 12 and hover the mouse over the lower right corner of the cell. So the fill handle appears, these little cross is called fill handle. And click and drag the fill handle until all of the cells you want to, Phil's are selected. I will select G7 as last one. And as you can see, after releasing the mouse, they feel are selected to fill the selected cells. So the selected cells are filled with your choice. There is one more thing and it is a continuing a series with a fill handle. To fill handle can also the fill handle can also be used to continue to continuous series. Whenever the content of a row or column follows a sequential order, numbers 12 tree or days Monday, Tuesday, Wednesday. The fill handle can guess that what should come next in the series. In most cases, you will need to select multiple cells before using the fill handle to help Excel determine the series order. For example, let's take a look at an example. You will select the cell range that contains the serious you want to continue. For example, I will choose this part 12 entry or EFG columns. And I will just click and drag feel to continue this series. For example, by fill handle. I will just go on tree and release the mouse. If Excel understood the series, it will be continued in the selected cells. For example, Excel added part 456. This is all for the cell B6. And see you later in the next lesson. 10. Modifying rows, columns and cells: Hello and welcome to Microsoft Excel 2016. Today's topic is modifying columns, rows and cells. By default, every row and column of a new workbook is set to the same height and width. Excel allows you to modify column within a row height in different ways, including wrapping text and merging cells. We will now see how to modify column wheat in our example here. As you can see, column C is too narrow to display all of the content in these cells. We can make all of these contents visible by changing the width of column C. First step in that way is to position the mouse over the column line in the column headings. So the cursor becomes a double arrow. For example, like this, click and drag mouse to increase or decrease the column width. As you can see, I now increase my column width. The column wheat is changed. Now we will see how out-of-date column width function in Excel. To autofill feature will allow you to set a column sweet to fit its contents automatically. You will do it in the next way. For example, if it was like this, what will position the mouse over the column line in the column headings. So the cursor become double arrow. Again. Double-click the mouse. And as you see, the column width is changed automatically to fit the content. Note here is that you can also out of the weeds for several columns at the same time. Simply select the columns you want to outfit. For example, I will choose C, D, and E. Then select the outer feed column. We'd come in from the Format drop-down menu here, out-of-date column width. So this mattered can also be used for rawhide and it is the same in the same way. Next thing we will see how to modify row height. First step in this way is to position the cursor over the row. So the cursor becomes a double arrow. As you can see here. You will just increase or decrease your raw. Really after losing miles, the height of the selected row will be changed. As you saw. Now, to see how to modify all rows or columns, instead of resizing rows and columns individually, you can modify the height and width of every row and column at the same time. This method allows you to set a uniform size for every row and column in your worksheet. In my example, we will set a uniform row height. First. You will locate and click the Select All button just below the name box to select every cell in the worksheet. By clicking on this, will select every cell in the worksheet, then position the mouse over a role line. The cursor becomes a double arrow like this. And then click and drag the mouse to increase or decrease the row height. Then release the mouse when you are satisfied. The row height will be changed for the entire worksheet. 11. Modifying rows, columns and cells - moving and hiding: Hello and welcome to Microsoft Excel 2016. In this lesson, we continue with modifying columns, rows and South. And today we will talk about inserting, deleting, moving, and hiding South rows and columns. After you've been working with a workbook for awhile, you may find that you want to insert new columns or rows, or delete certain rows or columns, move them to a different location in the worksheet or even hide them, will now see how to insert rows. First step is to select the row heading below where you want to do new row to appear. In my example, I will start with, I want a row between rows 45. So I will click on row five. By clicking on row five. Then I will just click the Insert comment on the Home tab. As you can see, the new row appeared above the selected row. What you need to know is that when inserting new rows, columns, or cell, you will see a paintbrush icon next to the, next to the inserted cells. This button allows you to choose how Excel format the cells. By default, Excel formats in inserted rows with the same formatting as the cells in the row above. To access more options. Hover the mouse over the icon, then click the drop-down arrow. As you can see, we have more options. Now, we will see how to insert columns. To insert column, you will just select the column heading to the right of where you want the new column to appear. For example, if you want to insert a column between columns D and E will select column E. Then second step is to click the Insert comment on the home tab, like this new column appear to the left of the selected column. What do you need to know is that when inserting rows and columns, make sure you select the entire row or column by clicking the heading, like this, by clicking on heading and selecting entire column. If you select only a cell in the row or column, the insert com old will only insert a new cell. Next thing, what we'll see is how to delete a row or a column. It is easy to delete a row or column that you no longer need. In our example, we'll delete a row, but you can delete a column the same way. For example, I will choose my row nine. By selecting it. You will select All and then just click the Delete command in the Home tab. So as you can see, the selected row is deleted and those around it will shift. In our example, row nine has moved up, so it is now row nine. What is important to understand here is that difference between deleting row or column and simply clearing its content. If you want to remove the content from a row or column without causing others to shift. You will just right-click heading, then select Clear, then select Clear content and all contents who will be deleted. Next thing we will see is how to move a row or column. Sometimes you may want to move a column or row to rearrange the content of your worksheet. In our example, we'll move a column, but you can move around in the same way. You all do it in the next swim. Selected the desire to call them padding for the column you want to move. Then click the cut comment on the home tab or on right-click, or just pressing Control plus X on your keyboard. Then select the column heading to the right of where you want to move the column. For example, if you want to move a column between columns, in between columns E and F, just select the column F and click the Insert comment on the home tab. Insert cut cells. As you can see, now, we have our phone column moved to the selected location and the columns around it shift. You can also do is to access the cut and insert comments by right-clicking on the mouse and selecting the desired comments from the drop-down menu. Finally, we will see how to hide and unhide a row or column. It's times. You may want to compare certain rows or columns without changing the organization of your worksheet. To do this, excel allows you to hide rows and columns as needed. In our example, we'll hide a few columns, but you can hide rows in the same way. First step to do this is to select the columns you want to hide. For example, I will choose c and d columns and just click on Format, drop-down menu and hide columns. As you can see, this green line suggests that there is a high columns here. To unhide columns, you will select the columns on both sides of the hidden columns. For example, in our example it will be b and e. And so clicking on dropdown menu or format and clicking on unhide columns, I will reveal my hidden Collins. This was all in this part of lesson and see you later in the next one. 12. Modifying rows, columns and cells - wrapping and merging: Hello and welcome to Microsoft Excel 2016. Today's lesson is the third part of modifying columns, columns, rows and cells. And we will talk about wrapping text and merging cells. Whenever you have too much cell contents to be displayed in a single cell, you may decide to wrap the text or merge the cell rather than resize a column. Wrapping the text will automatically modify ourselves row height, allowing cell content to be displayed on a multiple lines. Merging allows you to combine a salary to adjust an empty cell to create one large cell. First step in wrapping the text in cells would be to select the cell you'll want to wrap. In this example. I'll select the cell in column C, and I will haul columns C. Select that. Click the word text comment on the Home tab. And as you can see, my text in the select itself is wrapped. To unwrap the text, you will just again click on Wrap Text. And as you can see, we unwrap text. Next thing we will see is how to merge cells using the merge and center command. To do it, you will select the cell range you want to merge. In this example, I will choose my first row from a to E one and just click the merge and center command on the home tab. As you can see, now, my cells from E to E are merged and my heading east centered. To access more Merge options, you will click the drop-down arrow next to the Merge and Center. And as you can see, we have four options. From here. You can choose to merge and center, which is the selected cells into one cell and centers the text, or merge across, which emerges the selected cells into a larger cell. So while keeping each rule separate, merge cells, which merges the selected cells into one cell, but doesn't center the text. And unmerged cells, which on Merge selected cells. One to note here that if you will want to be careful when using this feature, if you merge multiple sounds that all contain data, excel will keep only the content of the upper left cell and discard everything else. And finally, we have option to center across selection. Merging can be useful for organizing your data, but it can also create problems later on. For example, it can be difficult to move, copy, and paste content from merged cells. A good alternative to merging is center across selection, which creates a similar ethics without actually combining cells. To do center across selection, you will select a desire salary range. In our example, I will again choose this one. This range from A1 to E1. And note here that if you already merge these cells, so you should unmarked them before continuing, continuing to step two. Then you will just click small arrow in the lower right corner of the alignment group here. On the Home tab. As you can see, a dialog box appeared. You will just locate and select the horizontal dropdown menu and select center across selection and click, Okay. The content will be centered across the selected cell range. As you can see, these grades, the same visual results is merging and centering, but it preserves each cell within A1 to F1. This was all for this lesson and see you later in the next one. 13. Formatting cells - font, size, color and bold: Hello and welcome to the Microsoft Excel 2016. Today we'll talk about formatting South. Also, content uses the same formatting by default, which can make it difficult to read a workbook with a lot of information. Basic formatting can customize the look and feel of your workbook, allowing you to draw attention to specific sections in making your content easier to view and understand. First thing we will see is how to change the font size, and we'll do it in next week. First step is to select the cells you'll want to modify. In my example, I'll choose these cells from a to E, one. On the Home tab, click the drop-down arrow next to the font size and choose the desired one size. I'll choose 24. So I need to make my row one a little bigger. The text will change to the selected font size as USE. Next thing, what we'll see is how to change the font. But one note is that you can also use they increase the font size and the font size commands or enter a custom font size using your keyboard. For example, like this. I'll know just type and go back. Or increase and decrease. Next thing, or changing the font. By default, the default of each new workbook East had to Calibri, as you can see here. However, Excel provides many other fonts you can use to customize your cell that acts in my example. Or we'll format out without title cell to help distinguish it from the rest of the worksheet. And we'll do it in the next way. First, select the cells we want to modify. Then on the home tab, again, these little down arrow from the font. We can choose the font we want. In my example, I will choose, for example, Century Gothic. As you can see, my font is changed to the selected fond. Why nodes? Again, is that when creating a workbook, either workplace, you will want to select a font that is easy to read. Along with Calibri, standard rating funds include Cambria, Times New Roman, Arial. Next thing is changing. The font color. Will do it in the next, we'll select this house you want modify. Again, choose my title. On the Home tab, click the drop-down arrow next to the font color. This is here, font color. Click the arrow and choose whatever you call it color you want. I will choose, for example, this green color. But you have a lot of more options here in more callers, standard work Austin. Okay. The text will be changed to the selected quantum color. As you can see. Next thing, Google CEs, how to use the bold, italic and underline commands. Again, we'll select this house we wanted to modify to cycle again, then click the bold italic underlying Home tab menu. The selected style will apply to the text. I'll choose bolt. You can also press Control plus B on your keyboard to move the selected text bold, cultural plus e to apply italics and Control Plus you to apply and underline. 14. Formatting cells - cell borders and cell styles: Hello and welcome to Microsoft Excel 2016. Today we will continue with formatting South. And first thing, we will cover these cell borders and fill colors. Cell borders in field colors allow you to create clear and defined boundaries for different sections of your worksheet. We'll add cell borders and fill color to our header style to help distinguish them from the rest of the worksheet. And we will do it in next way. First of all, we'll see how to add a field color. I will choose my, this row. I will choose just fuel cells from a to E, As you see. And I will choose Fill color. I will choose this one. And as you can see, I can now change my text font to white so it will be more visible. So you saw first step is to select the cells you want to modify. Then on the home tab to fill color, you will choose the drop-down menu and choose the color you want, and color will be changed. Second thing to see is how to add a border. Again, we will select the cells we want to modify. I will now choose the cells. And on the Home tab, click the drop-down arrow next to the borders command. This is border command. And I will choose old borders. The selected border style, as you saw, east appeared and I have now all my borders. You can draw borders and change the line style and color are borders with the draw borders tools at the bottom of the borders drop-down menu here. As you can see, more borders or borders. Next thing we will see in this lesson, our cell styles. Instead of formatting cells manually, you can use Excel's pre-designed Cell Styles. Cell styles are a quick way to include professional formatting for different parts of your workbook, such as titles and headers. So first, we will see how to apply a cell style. In our example, we will apply a new cell style to our existing title and header cells. So I will choose those two. And as you can see in the sales part of the home tab, we have drop-down menu and we will choose whatever style we want. In my example, I will choose this one. So as you can see, the selected cell style, a feared note here that is applying a cell style will replace any existing cell formatting except for text alignment. You may not want to use Cell Styles if you have already added a lot of formatting to your workbook. Next thing we will cover ease text alignment. By default, any text entered into your worksheet will be aligned to the bottom left of a cell. Well, any numbers will be aligned to the bottom-right. Changing the alignment of your cell content allows you to choose how the content is displayed in any cell, which can make your cell content easier to read. So as you can see here, we have a left alignment which aligns content to the left border of the cell, center alignment, which is the same distance from the left and right side. And write a line which aligns content to the right border of the cell. We have also here in up top align, which aligns content to the top border of the cell, middle online, which is the same distance from the top and bottom and bottom align. Now we will see how to change horizontal text alignment. In my example here, I will modify the alignment of our title cell to create a more polished to look and further distinguish it from the rest of the worksheet. So I will choose this one and select one of the three horizontal alignment commands on the home tab. I will choose all files and click middle or center. As you saw, the text is realigned. Now to see how to change our vertical text alignment, again, we will select the cells we want, which are all cells. Select one of the three vertical alignment and choose the text is realigned. You can apply both work go and horizontal alignment settings to any cell. And this is all for formatting cells lesson and see you later in the next one. 15. Understanding number format - applying: Hello and welcome to Microsoft Excel 2016. Today's lesson ease, understanding number formats. Whenever you're working with the spreadsheet, it is a good idea to use appropriate number formats for your data. Number format style you Spreadsheet exactly what type of data you're using, like percentages, currency, times the weights, and so on. Why use a number formats? Number formats don't just make your spreadsheet easier to read. Also make it easier to use when you apply a number format, you're telling your spreadsheet exactly what types of values are stored in a cell. For example, the date format tells the spreadsheet that you are entering specific calendar dates. This allows the spreadsheet to better understand your data, which can help ensure that your data remains consistent and that your formulas are calculated correctly. If you don't need to use a specific number format and the spreadsheet will usually apply the general number format by default, however, the general format may apply some small formatting changes to your data. Let's now see how applying number formats a function in Excel, dislike types of formatting such as changing the font color, you will apply number formats by selecting cells and choosing the desired formatting option. There are two main ways to choose a number format. First one is to go to the Home tab. Click the number format. As you can see here, drop-down menu in the number group and select the desired format. Second way is that you can also click one of the quick number formatting comments here. Below the drop-down menu. You can also select the desired cells and press Control plus one on your keyboard to access more number formatting options. In this example, we will apply the currency number format, which adds currency symbols and displays two decimal places for any numerical values. For example, will enter dollar sign and 45. As you can see. Now, my format will be currency and not general. If I click 148 will be just added dollar sign. And because of the currency or any outer else. Sorry, just to click on currency. One currency, old time. So here, like this, if you select any cells we number formatting, you can see the actual value of the cell in the formula bar. The spreadsheet will use this value for formulas and add our calculations. For example, like this, 14 here, 65, and 78. We will now see how to use number formats correctly. There is more to number formatting, then selecting cells and applying a format spreadsheets can actually apply a lot of number formatting out automatically based on the way you enter data. This means you will need to enter data in a way the program can understand and then ensure that those cells are using the proper number format. For example, in the image I show you here, is that how to use number formats correctly for these percentages and time, how you should enter data in your Excel spreadsheet so that your Excel spreadsheet looks and apply it in the way that will help you. So now that you know more about how number formats work, we'll look at a few different number formats in action. First number formatter, we will see East percentage format. One of the most helpful number formats is the percentage format. It is placed values as percentages such as 20% or 55%. This is especially helpful when calculating things like the cost of sales tax or a tip. When you type a percent sign after a number, the percentage number format will be applied to the cell automatically. For example, I will click 15 and resend sign. And as you can see here, my format ease percentage. As you may remember from math class, a percentage can also be written as a decimal. So 15% is the same thing as 0.150.5 per cent is the same as 0.075 and so on. There are many times when percentage formatting will be useful. For example, in my spreadsheet, here, we eat these examples I will show you. You can see how it is useful. For example, I will now enter five, just five but without percentage and changing. As you can see, my sales tax is 112.50 because here is my formula, formula that b2 times before. But this is with no percentage formatting. We will see now with percentage formatting, 5% sign will give us real number, which is real sales tax, and this is 0.13. And we have third example, which is like decimal. As you can see, is the same result as percentage formatting. But no percentage formatting. If you write, is this, this will be wrong because you will not get a real number. The calculation here will ease wrong. And percentage formatting is the same as written as decimal, but it is easier to read. 16. Understanding number formats - date formats: Hello and welcome to Microsoft Excel 2016. In the second part of a lesson understanding number formats, we will start date formats. Whenever you're working with dates, you will want to use a date format to tell the spreadsheet that you are referring to specific calendar dates, such as, for example, July 2014 or 2016. Now our mind, date formats also allow you to work with a powerful set of data functions that use time and date information to calculate an answer for spreadsheets, don't you understand information the same way a person would? Which means that if you type October into a cell, the spreadsheet one no, you are entering a date, so it will treat it like any other texts. Instead, when you enter a date, you will need to use a specific format, your spreadsheet and the standards, such as the month, day year or day month, year, depending on which country you are in. The example we will see here, I will type, for example. Then, sorry, this is already currency. This is general. So I will type then devolve 2015. And as you can see, my number formatting is automatically changed to the date number format. Now that we have our date correctly formatted, we can do many different things with these data. For example, we could use the fill handle to continue the dates to the column. So a different day of peers in each cell, for example, like this. As you can see after Excel, excel understand my datatype. It will just fill the order of the number. So if the date formatting isn't applied automatically alike this in my case, it means that the spreadsheet didn't understand the data you entered in the example here. In the next example, we'll type, for example, March 16th. If you click on this cell, you could see that Excel didn't recognize, recognize these type of date. So it's still ease general. But the spreadsheet, As you saw, didn't recognize so this Tau yz using the general number format. But on the other hand, if we type, for example, just March 16, as you can see, now, it is recognized. So if I go down, it will make my date. Also, we have other date formatting options. And to access our date formatting options, you will select the number format, drop-down menu, and click on more number formats. These are options to display the data differently, like including the day of the week or omitting the year. The Format Cells dialog box appeared. As you see from here, you can choose the desired formatting option. Let's see. Okay. I have to spread my column. As you can see here. Eight in Formula bar, a custom date format doesn't change the actual date in our sound. It just changes the way it is displayed as you can see here if these 3162017. And for the end of this lesson, we will see some number formatting tips. Those are, for example, first one is that applying number formatting, tweak the entire column. For example, I will type price. And if you want for the entire column to be dollar, currency, currency in your number formatting, you will just click on the name of the column and click on currency, number formatting. So what ever you, right here, any number you write, it will be displayed as currency. So it is just tip. So let's see now second tip. Second tip is double-check your values after applying number formatting. For example, if you apply number formatting to existing data, you may have unexpected results. For example, applying percentage formatting to a salvage value of five will give you 500%, not five per cent. A look at it. For example, I will just do this date. I will just click General six. As you can see if I apply to this column, percentage, sorry. This will give me not what I wanted to unexpected results. In this case, you need to retype the values correctly in each cell. If you reference a cell to number formatting in a formula, the spreadsheet may automatically apply the same number formatting to the new cell. For example, if I now make like this, this cell times this cell, it should automatically give me this sign. Dollar. If you want your data to appear exactly is entered, you will need to use the text number format. This format is especially good for numbers. You don't want to perform calculations sweet. Satisfy numbers, zip codes, or numbers that begin with 0. For best results, you may want to apply the text number format before entering data into these south. And at the end, we will have increase and decrease decimal explanation. This is just simple option. For example, I will enter here this number and by clicking on more decimals, it will give me, But I want, for example, a little bit better view of, so I will just get this number, but here in the formula bar, you have your option. You're real number. So the increase and decrease decimal Commons don't work with some numbers format, so like date and infraction. And this is all for this lesson and an understanding of number formats. So see you later in the next one. 17. Multiple worksheets: Hello and welcome to Microsoft Excel 2016. Today's lesson is working with multiple worksheets. Every workbook contains at least one worksheet by default, when working with a large amount of data, you can create multiple worksheets to help organize your workbook and make it easier to find content. You can also group worksheets to quickly add information to multiple worksheets at the same time. We will see every of these in our lesson. But let's start from inserting a new worksheet. As you can see here in my document, I already made my annually workbook, but I still need December. So I will just click on New Sheet button near the bottom right corner of the Excel window and click, as you can see, I have one of my new sheet, new blank worksheet appeared. By default. Any new workbook you can create an Excel will contain one worksheet called Sheet one. To change the default number of worksheets, navigate to backstage view, click Options, then choose the desired number of worksheets thing called in each in your workbook. Second thing what we'll see is how to copy a worksheet. If you need to duplicate the content of one worksheet to an era, Excel allows you to copy an existing worksheet. For example, for December, I need no Ampere template and I will right-click on new worksheet to you want to copy. Then select Move or Copy to the worksheet Menu. From the worksheet Menu. Then move and copy box appeared. Choose where to shade. We'll appear in the before she'd. In our example, we will choose move to the end, click, Create a Copy, and then click. Okay. As you can see, because we copy November. So our new worksheet is named November to all content from the November worksheet has also been copied to the new worksheet. You can also copy a worksheet to an entirely different workbook. You can just select any word that is currently open from the book drop-down menu. Next thing we will see is how to rename the worksheet because these number two is should be our December. We will just click on November tool and click Rename. So I will click enter December and just click Enter desired named for the worksheet. Click anywhere outside of the worksheets tab or press Enter on your keyboard and the worksheet will be renamed. Now let's see how to move a worksheet. Worksheet, you will just click and drag the worksheet. You want to move until a small back arrow appears from the desired location. I want my December worksheet after November. Release the mouse and the worksheet will be moved. But will now see how to change the worksheet tab color. For example. Click it, click the desired worksheets tab. I'll choose December and hover the mouse over Tab Color. Tab color, menu appeared. You can choose your color. I'll choose this. The worksheet tab color will be changed. The worksheets tab color is considerably less not to symbol one to worksheet is selected, so I will just select on November. And now you can see my color of my December worksheet. And one more thing to see is how to delete a worksheet. We'll just click, right click the worksheet you want to delete and then select delayed from the worksheet Menu. See now I deleted my worksheets from the workbook. If you want to prevent specific worksheets from being the edit, edited, or deleted, you can protect them by right-clicking the desired worksheet and select it protects sheet here from the worksheet Menu. 18. Multiple worksheets - grouping and ungrouping: Hello and welcome to Microsoft Excel 2016. In the second part of working with multiple worksheets lesson, we will see grouping and ungrouping worksheets. You can work with each worksheet individually or you can work with multiple worksheets at the same time. Worksheets can be combined together into a group. Any changes are made to one worksheet in a group will be made to every worksheet in the group. We will now see how to group worksheets. First step in this is to select the first worksheet you want to include in the worksheet Group. For example, I will choose September. Then press and hold the Control key on your keyboard, and select the next worksheet you want in the group. For example, I want October. Continued to select worksheets until all the worksheets you want to group are selected. For example, I want October, November, and December, and just release the Control key. The worksheets are now grouped. Worksheets are groups. You can navigate to any worksheet within the group. Any changes made to one worksheet will appear on every worksheet in the group. However, if you select a worksheet that is not in the group, all of your worksheets will become ungroup. So let's see now that our rent in this month, September, is increased, as we can see here in notes. So now it is 750 and cancel a dream membership. So gym membership now is 0. And let's see now how October, as you can see from all my group, it is same. So grouping document is something that will easy to control more worksheet in one Move. Last thing we will see is how to ungroup worksheets. And we will do it in next way. Right-click on any of worksheet into group and selecting ungroup sheets. As you can see now, the worksheets are ungrouped. Alternatively, you can simply click any worksheet not included in the group to ungroup all worksheets. This was all for me in this lesson and see you later in the next one. 19. Find and replace options: Hello and welcome to Microsoft Excel 2016. Today's lesson is using Find and Replace options in Excel. When working with a lot of data in Excel, it can be difficult and time-consuming to locate specific information. You can easily search your workbook using defined feature, which also allows you to modify content using the Replace feature. First, what we'll see is how to find content. In our example, we will use the find command to locate a specific department in this list from the Home tab. Click Define and select, comment. Then select fine from the drop-down menu. As you can see, our dialog box appeared. Enter the content you want to find. For example, I will choose accounting as it is written here, and click Find Next. As you can see here, excel found me, my department. By clicking fine. All it will give all three accounting forums. By clicking fine. Next, we'll just find any other Excel option we have. When you are finished, click Close to exit the Find and Replace dialog box. You can also access define common by pressing Control plus F on your keyboard. But one more thing before I exit out, we'll just click View options. As you can see, there are more options on finding your results. Then I will just click X. Second thing we'll see is how to replace cell contents eta times. You may discover that you have repeatedly made a mistake. Or Audi or workbooks that is misspelling someone's name or that you need to exchange a particular word or phrase for another. You can use Excel's Find and Replace feature to make quick revisions. In our example, we'll use a find and replace a to correct a list of department names again. And we will click, Find and Select from the Home tab and select Replace from the drop-down menu. As you can see, I want now to replace from accounting with finance. By clicking on Replace. It will just replace one thing. But if I click Replace All, it will change all tree accounting firms into defined as forums. So I will click Replace all just to save time. And as you can see, a dialog box appeared confirming the number of replacement made. Just click Okay and close your dialog box. The selected cell content is replaced, as you can see from accounting, it is now finance. So when you are finished, click Close to exit the Find and Replace box. One. A note here that generally it is best to avoid using replaced all because it doesn't give you the option of skipping anything you don't want to change. You should only use this option if you are absolutely sure it won't replace anything you didn't intend to. It was all for this lesson. See you later in the next one. 20. Checking spelling: Hello and welcome to Microsoft Excel 2016. Today's lesson is Checking spelling. Before sharing a workbook, you will want to make sure it doesn't include any spelling errors. Fortunately, Excel encodes a spellcheck tool you can use to make sure everything in your work book is spelled correctly. If you have Io's, this bow check feature in Microsoft, we're just be aware that spellcheck tool in Excel, while have Paul is not as powerful. For example, it won't check for grammar issues or check spelling as you type. Let's now see how to use spell check. First, you will click the Review tab in the ribbon. And as you can see here, we have ABC spelling. So click on the spelling. And as you can see from my text and why more workbook here, we'll see our mistakes. So as you can see, my first mistake is exercise. Here. Instead of exercise, it is exercised. So I will just click on the exercise and click on change. They want to continue attacking and the beginning of the sheet. Yes. As you can see, the spelling dialog box after I click on my spelling, spelling dialog box appeared for each spelling error in your worksheets spellcheck will try to offer a suggestion for the correct spelling. You will choose suggestion and then click Change the correct word after you are finished and after all your mistakes are checked, you will get this kind of box where spellcheck complete or you are good to go. It will taste like, Okay. If there are no appropriate suggestion, you can also enter the correct spelling manually. There is one more option instead of spell tech, because it isn't always correct. It will sometimes mark certain wars as incorrect, even if they are spelled correctly. So these often happens which name which may not be in the dictionary? You can choose not to change a spelling error using one of the following three options. As you can see here, we have three options. Ignore once we'll skip the word without changing it, ignore all. We'll skip the word without changing it and also skip all other instances of the word in your worksheet and add to dictionary. This adds the word to the dictionary so it will never appear as an error again. But before doing it to make sure the word is spelled correctly. This was all for the spelling election. So see you later in next one. 21. Printing : Hello and welcome to Microsoft Excel 2016 tutorial. Today we will talk about Page Layout and brain. Think. There may be times when you want to print a workbook to view and share your data offline. Once you have chosen your page layout settings, it is easy to preview and print the workbook from Excel using the print pain will now see how to access the brain pain. So just click on File and accessing backstage view, then click Print. And as you can see here, you have all options you need about printing. Let's see now for example, first, we have Printer. Here. If you have multiple printers, you can choose which printer you want for your worksheet to print. Next thing is settings about which pages we want to print. As you can see here, we have four options. Praying the active sheets, print the entire workbook, brain selection, and print selected stable. As you can see here, we have explanations. Brain selected table is only bringing the selected table, only print the current selection, bring the entire workbook and only print active sheets will see this area a little bit later, correlated. As you can see, if you have a lot of papers you can see, you can choose what are they are collated or uncultivated. Landscape or portrait orientation, ladder, and margins and scaling. As you can see here. All of these options are related to the printing. You'll then decide how many copies of this frame version you want and just click on Print. So in order to print document, you will just navigate to the print pain here in backstage view. Select the desired printer, the desired active or other sheets and just click print. Next thing we will see is choosing a print area. Before you print an Excel workbook, it is important to decide exactly what information you want to print. For example, if you have multiple worksheets in your workbook, you will need to decide if you want to print the entire workbook or only active worksheets, there may also be times when you want to print only a selection of content from your workbook. We will now see all these options. Firstly, we will see how to print a active sheets. Worksheets are considered active when they are selected. So if you want more than one worksheets to be printed, you will just click on the first worksheet. Click Control on your keyboard. And just all of the other worksheets you'll want to be printed. Click now the my three worksheets are selected. Selected. After we select sheets that we want, we will just navigate to the print dab and decide. Print active sheets and click print. But in a case and we want to bring the entire workbook will just choose a from the sprint pain print entire workbook. This will mean that our entire, entire book will be printed after we click Print button. Next thing is to print a selection. In our example, we will print the records for the top 40 salesperson on the central worksheet. I will go back. I will select 40 people. As you can see here. Go back to the print tab and click Print selection. As you can see here. After I print selection, I will just click Print and my selected range is printed. If you prefer. You can also set the print area in advance so you will be able to visualize which cells will be branded as you work in Excel, simply select the cells you want to print. So select cell as I now select, and then click Page, Layout, tab and choose Set, Print Area. Set Print Area from the print area drop-down menu and click on it. After you've decided not to not need this, you will just Clear Print Area. 22. Adjusting content: Hello and welcome to Microsoft Excel 2016 tutorial. Second part of Page Layout and printing lesson is adjusting content. On occasion, you may need to make small adjustments from the print pain to fit your workbook content neatly onto a printed page. The print pain includes several tools to help fit and scale your content, such as scaling and page margins, will now see how to change the page orientation. Excel offers a two-page orientation options, landscape and portrait. Landscape oriented horizontally while parted are in the page where the goalie, in our example, we'll say the page orientation to landscape. To do it, what will navigate to the print pain? And choose those two options. So we have four orientations, so we'll select the desired orientation from the page or impatient drop-down menu. In our example, we'll choose a landscape, are impatient. As you can see, my landscape orientation is now shown in the preview pane. Second thing we will see is how to fit fit content before printing. If some of your content is being cut off by the printer, you can use scaling to fit your workbook to the page. Automatically. Navigate again to the print. Print pain if you're not here. In our example, we can see in the preview pane that our content will be cut off when printed. Well, then select the desired option from this scaling drop-down menu. So scaling, in our example, we choose a fit all columns on one page. And as you can see here, the worksheet will be condemned, condensate to fit onto a single page. But keep in mind that worksheets will become more difficult to read as they are down. So you may not want to use this option when printing a worksheet with a lot of information. In our example, we'll change the scaling setting back to no scaling. Next thing we will see is how to include print titles. If your worksheet uses title headings, it is important to include these headings on each page of your printed worksheet. It would be difficult to read a printed workbook if the title headings appeared only on the first page. The print titles comment allows you to select specific rows and columns to appear on each page. You will do it in the next way. Click the Page Layout tab on the ribbon. Then select the Print Titles command. As you can see here, the Page Setup dialog box appeared from here, or you can choose rows and columns to repeat on each page. In our example, we'll repeat a row first. So Rows to repeat at the top, I will then click here. And as you can see, I want first row. I will click Okay. And columns to repeat. At left, I will again click and twos column a. After I finished my selections, I will click. Okay. In our example, row one appears at the top of every page, in column E appears at the left of every page. Next thing we will talk about adjusting paid breaks. First step in adjusting page breaks is to click Page Break Preview. So from normal view, we will go on Page Break Preview. As you can see, word go and horizontal blue dotted lines denoted the page break. The page breaks. Click and drag one of these lines to adjust that they'd break. Sounds. In our example, we select Saturday horizontal page break between rows 2122. So we'll take this and put it here. So in our example, all the pages and now show the same number of rows due to the change in the page break, as you can see here. So the same number of rows. And last thing we will see is how to modify Martins in the preview pane. Margin is the space between your content and the edge of the page. Sometimes you may need to adjust the margins to make your data fit more comfortably. You can modify Paige Martin's from the print pain. Navigate to the print pane. Select the desired margin size from the page margins drop-down menu. In our case, we'll select narrow margins. So the new Paige Martin's are displayed in the preview pane. This was all for this lesson and see you later in the next one. 23. Introduction to formulas : Hello and welcome to Microsoft Excel 2016 tutorial. Today we will talk about introduction to formulas. One of the most powerful features in Excel is the ability to calculate numerical information using formulas. Just like a calculator, excel can add, subtract, multiply, and divide. In this lesson, we'll show you how to use cell references to create a simple formulas. First of all, we'll start with a mathematical operators. As you can see here in my picture, Excel uses standard operators for formulas such as plus sign for addition, minus sign for subtraction, and asterix for multiplication and forward slash for division and a carrot for exponents. All formulas in Excel must begin with an equal sign. This is because the cell contains or 0s equal to the formula and the value it calculates. Second thing we will mention is understanding cell references. While you can create simple formulas in Excel using numbers, for example, is equal to a plus two or easy quo five times five. Most of the time, you will use cell addresses to create a formula. This is known as making a cell reference. Using cell references will ensure that your formulas are always accurate because you can change the value of reference cells without having to rewrite the formula by combining a mathematical operator with cell references, you can create a wide variety of simple formulas in Excel. Formulas can also include a combination of cell references and numbers, as in the examples in my picture. As you can see, E is equal A1 plus A2, which means add cells A1 and A2. C4 minus b, subtract three from cell C4, and so on. Finally, we will see how to create a formula. In example here, we'll use a simple formula in cell references to calculate the budget. So select the cell that will contain the formula. In our example, I will choose D2L. So to add tune budget in July budget, I will type equal sign. Notice how it appears in both the salad in the formula bar. As you can see here in the formula bar. Then type the cell address of the cell you want to reference first in the formula. So I will choose D, then. Then I will add, addition sign and type the 11. As you can see. First one I type D tan, my d ten was blue border. I will just click enter. And as you can see, it will if calculated my value. So if you select a DSL again, notice that the cell formula is displayed in the formula bar and sell displays the result. One note here is that if the result of formula is too large to be displayed in a cell, it may appear as a pound signs instead of a value. This means the column is not wide enough to display the cell content. So you simply increase the column, the weight to show the cell content. One more thing is modifying values with cell references. The true advantage of cell references is that they allow you to update data in your worksheet without having to rewrite formulas. So in my example, I will show you how to modify cell. In the example here, I will show you how changing value from the June budget, for example, I don't want 1200 anymore, I want thousands and 800. I will click enter. And as you can see, the formula in the valve just changed. The result just changed because formula is the same, it will calculate d 1011. So now my result is 3,300. What do we must tell here that Excel will not always tell you if your formula contains an arrow. So it's up to you to check all of your formulas. 24. Introduction to formulas 2nd part: Hello and welcome to Microsoft Excel 2016 tutorial. In the second part of introduction to Formulas lesson, we'll see how to create a formula using the point-and-click method at the beginning. So instead of typing cell addresses manually, you can point and click the cells you want to include in your formula. This method can save a lot of time and effort when creating formulas. So in my example here, we'll create a formula to calculate the cost of ordering several boxes of plastic silverware. It will select the cell that will contain the formula. I will select. Before. Then type the equal sign and select the cell you want to reference first in the formula. First I will click before, then multiplication sign. And second wishes C4. Just click Enter on your keyboard. And as you can see here, we have D formula is calculated and the value is displayed in the cell. Second thing we will see 0s copying formulas. We do feel handle. Formulas can also be copied to adjust and sell with the fill handle, which can save a lot of time and effort if you need to perform the same calculation multiple times in a worksheet. So the fill handle is the small, the small square at the bottom right corner of the selected cells, as you can see here in my example, you will select the cell containing the formula you want to copy. In my case, it is D4. And click and drag the fill handle over the cells you want to fill. I want also these three cells will have the same formula as the first one. And after I selected my cells, I will just release the mount mouse and the formula will be copied to the selected south, as you can see. So we will again check in formula bar as you can see here. We have B5 and C5, we have B6 and C6, and C7, C7. So finally, last thing we will see in this lesson is how to add it to a formula. Sometimes you may need to modify an existing formula. In the example, we have entered anything correct cell address in our formula here. So I will need to correct it. In order to correct it, I will select a cell containing the formula. I want to add it. My case, as I said, it is deed valve, and then click the formula bar to add it. My formula. You can also double-click the cell to view of an additive formula, formula directed awaiting the cell. As you can see, border appeared around any reference cells, which is D9 and the 11. So I need the time instead of D9, and I will delete D9 and just click the ten and click Enter on my keyboard. As you can see, now, my total is changed and the formula is updated and the new value displayed in the cell. One note here that if you change your mind, you can press the Escape key on your keyboard or click the Cancel command in the formula bar to avoid accidentally making changes to your formula. So to show all the formulas in a spreadsheet, one more thing is that you can hold the Control key and press grave accent. The grave accent key is usually located in the top-left corner of the keyboard. You can press control plus grave accent again, to switch back to the normal view. This is all for this lesson and stay tuned for more. 25. Complex formulas : Hello and welcome to Microsoft Excel 2016. Today we will talk about creating more complex formulas. You may have experience working with formulas that contain only one operator, such as seven plus nine. More complex formulas can contain several mathematical operators. Satisfy plus two times eight when there is more than one operation in a formula, the order of operations tells Excel which operation to calculate first. To write formulas that will give you the correct answer, you will need to understand the order of operation, mathematical order of operation in which Excel calculates formulas based on ease. First one, operations enclosed in parenthesis. Second one, exponential calculations. Third one is multiplication and division, whichever comes first, fourth, 1, addition and subtraction, whichever comes first. In my example here, I will try to demonstrate you how Excel uses the order of operations to solve a more complex formula. Here, we want to calculate the cost of sales tax for accreting invoice cell. To do this, we'll write our formula. S is equal to d3 plus d4, d5. Parenthesis closed times 0.075. Excel follows the order of operation in forest as the value inside the parenthesis. So those three V3, V4, V5 together will be 274 times 108. Then multiply that value by the tax rate to 174 times 10.10 times 0.075. The result will show that the sales tax is 20.56. It is especially important to follow the order of operations when creating a formula. Otherwise, Excel one calculate the results accurately. In our example, if the parent is is, are not included, the multiplication is calculated first. D5, it will be multiplicate it with a 0.075, and then D3 and D4 will be added. So parentheses are often the best way to define which calculations will be performed. First in Excel. 26. Creating complex formulas: Hello and welcome to Microsoft Excel 2016 tutorial. In the second part of complex formulas, we will see how to create a complex formula using the order of operations. So in my example here, I will use cell references along with the numerical values to create a complex formula that will calculate a subtotal for a catering invoice. The formula will calculate the cost of each menu item first, then add these values. So I will first select the cell that will contain the formula. I want, C5 and then I will enter. My formula is equal to b3 times c3 plus B4 times C4. This formula will follow the order of operations for as performing the multiplication to 0.79 times 35.292 times 20, and it is equal to 45 times 88. Then we'll add these values to calculate the total, 97 times 65 plus 45 times 80. But what I will suggest you ease to double-check your formula for accuracy and then press Enter on your keyboard. The formula will calculate and display the result. In our example here, the results shows that a subtotal for the order is a 143.451 thing you can do is to add parentheses to any equation to make it easier to read while it won't change the result of the formula. In this example, we could enclose the multiplication operations within paranthesis to clarify that they will be calculated before the addition. I will now add parenthesis just to ensure that my multiplication will be calculated before an election result won't be changed, as I mentioned. But it is more accurate. Again, I'll repeat one node and that is that Excel will not always tell you if your formula contains an error. So it's up to you to check all of your formulas. This was all for this lesson and see you later in the next one. 27. Relative references : Hello and welcome to Microsoft Excel 2016 Tutorial. In this lesson, we will see relative and absolute cell references. There are two types of cell references, relative and absolute. Relative and absolute references behave differently when copied in field to ourselves. Relative references change when a formula is copied to another cell. While absolute references, on the other hand, remain constant no matter where they are copying. In this part of lesson, we will see a relative references. By default, all cell references are relative references. It means when copied across multiple cells, they change based on the relative position of rows and columns. For example, if you copy the formula A1 plus B1 from row one to row two, the formula will become A2 plus B2. Relative references are especially convenient whenever you need to repeat the same calculation across multiple rows or columns. Let us see now how to create and copy a formula using relative references. In the following example, we want to create a formula that will multiply each item's price by the quantity. Instead of creating a new formula for each row, we can create a single formula in cell D2 and then copy it to the other rows, will use relative references. So the formula calculates the total for each item correctly. So first step is to select the cell that will contain the formula, and it is the two in our example. D4, sorry. Then we will enter the formula that calculates the desired value. Example. Go to B4 times C4, and I will press Enter on my keyboard. The formula is calculated and the result is displayed in the cell. As you can see, I will now Lockheed fill handle at the bottom right corner of the desired style. And I will just go till the end of the column. As you can see now, I have my entire column from D5 to d 14. So you can double-check the field cells to check the IRR formulas for accuracy. The relative cell references should be different for each cell depending on the rows. For example, here, as you can see, it is B5 times C5, then B6, C6, etc. Here, b 11 times C 11. So this was all for relative references in the continue will see absolute one. 28. Absolute cell references : Hello and welcome to Microsoft Excel 2016 tutorial. Second part of relative and absolute cell references will cover absolute references. There may be a time when you don't want a cell reference to change one copy to our cells. Unlike relative references, absolute references do not change when copied or field. You can use an absolute reference to keep a row or column constant. And absolute reference is designated in a formula by the addition of a dollar sign. It can proceed at a column references, there are references or bought, as you can see here in my picture. Though, dollar sign before and after. The letter n number mean the column and do not change when copied than a dollar to means that just the row doesn't change when copied. And dollar sign A2 means that column doesn't change when copied. You'll generally use the dollar sign, a dollar sign to format when creating formulas that contain absolute references. The outer two formats are used much less frequently. When writing a formula, you can press the F4 key on your keyboard to switch between relative and absolute cell references. This is an easy way to quickly insert an absolute reference. Now what we'll see how to create and copy a formula using absolute references. In example here, we're going to use cell E2, which contains the tax rate at 7.5% to calculate the sales tax for each item in column D To make sure the reference to the tax rate stays constant even when the formula is copied and failed to ourselves will need to make cell absolute reference. First step in this way is to select the cell that will contain the formula. In our example, we will select cell D4. Then we will enter the formula to calculate the desired value. In our example, we'll type equals sign. Open parties is B4 times C4 close part. This is times dollar sign times e2. Now, we will make this absolute by adding dollar sign. As you can see, now, we have displayed a result. The formula is calculated. Now we will locate the fill handle and a bottom right corner of the desired cell and we'll Lockheed till the end of the column. So now, as you can see after releasing the mouse, the formula is copied into the selected cells and with an absolute reference and the values are calculated in each cell. You can double-click the field cells to check their formulas for accuracy. The absolute reference should be the same for each cell, while the outer references are relative to the cell's row. Note here is that you have to be sure to include the dollar sign whenever you are making an absolute reference across multiple cells. The dollar sign were emitted in the example below. This cost Excel to interpret it as a relative reference producing an incorrect result when copied to other cells. We will see now, for example, I will go undo. And then again, for example, if I just click, as you can see here, I'll click Enter the same result. But if I go down, as you can see here, everything is messed up. So I will just go back to my previous solution. Final thing, we will see easy using cell references with multiple worksheets. Excel allows you to refer to any sound on any worksheet, which can be especially helpful if you want to reference a specific value from one worksheet to an outer. To do this, you will simply need to begin. The cell reference would have worksheet name followed by an exclamation point. For example, if you wanted to reference cell E1 on Sheet one, it's called reference would be Sheet1, exclamation point A1. Know that if a worksheet name contains a space, you will need to include single quotation marks around a name. For example, if you wanted to reference cell A1 on a worksheet named July budded, its cell reference would be a quotation marks July budget exclamation point A1. So let's see now how to reference cells across worksheets. In our example here, we'll refer to a cell with a calculated value between two worksheets. This, this will allow us to use the exact same value on two different worksheets without rewriting the formula or copying data. First, we will locate the cell we want to reference and note is worksheet. In our example, we want to reference cell E 14 here on the Menu Order worksheet. So we'll navigate to the desired worksheet. In our example, we will select the carrying invoice work shaped. So after I select, I will go to gathering invoice and locate and select the cell where we want the value to appear. In our example, we will select C4. Then we will type the equal sign. Then the sheep name. Because, uh, my sheet name is manual order, it is towards, I need quotation marks. So I will Menu Order. Then again quotation mark, followed by an exclamation point. And to sell address in our example, it is E 14. Now I will click Enter on my keyboard and the value of the reference cell will appear. It is if you rename your worksheet at a later point, the cell reference will be Avanade. As you saw note here, is that if you enter a worksheet name incorrectly, the ref sign, as you see earlier, will appear in the cell. So if not, then we have the same number as it is here. And as I said, if you rename a worksheet at a later point, the cell reference will be updated, automatically reflected in your worksheet. Name is any order changes you made in the menu order. It will be reflected in creating invoice. So this was all for this cell references. And types of cell references. Stay tuned for more lessons. 29. Introduction to functions : Hello and welcome to Microsoft Excel 2016 tutorial. Today's lesson is about functions. A function is a predefined formula that performs calculations using specific values in a particular order. Excel includes many common functions that can be used to quickly find the sum, average, count, maximum value, and minimum value for a range of cells. In order to use functions correctly, you will need to understand the different parts of a function and how to create arguments to calculate values in cell references. Now we will see what parts of a function we have. In order to work correctly, a function must be written in a specific way, which is called the syntax. The basic syntax for a function is the equal sign, as you can see here in my picture. The function name, some, for example here. And one or more arguments. Arguments contain the information you want to calculate. The function in the example here would add the values of the salary to A1 to a 20. So as you can see here, we must have equal sign then function name and arguments. So in continual we'll, we'll see how it is working with arguments in Excel. Arguments can refer to vote individual cells in salary ranges and must be enclosed within parenthesis. You can include one argument or multiple arguments depending on the syntax required for the function. For example, the function average, I will do it here Is equal sign. As you can see, I have my possible as far as here, and I will do like this. From C7 to see 14, I will just click Enter. And you can see these function calculates the average of the values in the salary range of C7 to see 14, nice function contains only one argument, but multiple arguments must be separated by a comma. For example, if I do here in the second sum formula, for example, and choose C seven to see 14 than a comma. I will set, I will put now f seven, f eight, again comma, and they sell. I'll put close parentheses in. Click Enter on my keyboard. As you can see here, I got sum of all the values in the cells of three arguments. So what do you need to know is that arguments, one argument, ie disempowering diseases, but more arguments must be separated in common with comma. So this was all for this first part of the lesson, functions. And stay tuned. We'll see soon in the second part. 30. Creating a function : Hello and welcome to Microsoft Excel 2016. In the second part of functions lesson, we will see how to create a function. First, I have to tell you something. There are a variety of functions available in Excel, but there are some of the most common functions you will use. There are five such functions and those are, uh, first of all, some, some function. This function adds all of the values of the cells in the argument than average function. This function determines the average of the values included in the argument. It calculates the sum of the cells and then divide that value by the number of cells in the argument. Next function is count function. It counts the number of cells which numerical data in the argument. And this function is useful for quickly counting the items in a salary range. Max function determines the highest cell value included in the argument. And the mean function determines the lowest cell value included in the argument. Now, we will see how to create a function using the outer sum command. The outer sum command allows you to automatically insert the most common functions into your formula, including sum, average, count, Min, and max. You an example here, we'll use the sum function to calculate the total cost for a list of recently order items. First of all, select the cell that will contain the function. In my example, I will select the 13. Then I will go to my editing part of Home tab and click my drop-down menu of the AutoSum. I will quick sum. And as you can see, function is already voted here. Excel will please the function in the cell and automatically select a salary. And for the argument. In my example, as you can see, those are cells come from the three to the 12, worse. And they were selected automatically. Their values will be added to calculate the total cost. If Excel selects the wrong cell range, you can manually enter the cells into the argument. Or you can use this arrow to make your cells wider or smaller. After you checked your formula, you will just press Enter on your keyboard and the function will be calculated. And the result appeared in the cell. In my example, as you can see, it is 765.29. The outer SAM command can also be accessed from the Formulas tab in the ribbon. As you can see here, autosome and most common formulas and functions. 31. Entering functions manually : Hello and welcome to Microsoft Excel 2016 tutorial. In the third part of functions lesson, we will see how to enter a function manually. If you already know the function name, you can easily type it yourself. In my example here. We'll use the average function to calculate the average number of units sold by each troop. Forest, again, select the cell that will contain the function. In my example, it is C ten. Type the equal sign, and enter the desired function name. In my case it is average function. So I will type Average. Other that we will type parentage cases. As you can see here, we have a list of suggestions function that appears below the cell as you talk. So you can also choose a from this suggestions. After that, we will enter the cell range for the argument inside parents faces. In our example, we'll type s3 to C9. As you can see, my range is bordered with a blue color. This formula, we'd add the values of cells C3 to C9 and then divide that value by the total number of values in the range. I will now press Enter on my keyboard and the function will be calculated. And the result is a beard in appeared in the cell. So in my case, the average number of units sold by each tube is 849. Again, I will repeat. Note that Excel not always tell you if your formula contains an error. So it's up to you to check all of your formulas. 32. Functions library : Hello and welcome to Microsoft Excel 2016 tutorial. In the fourth part of the functions lesson, we will see the function library. While there are hundreds of functions in Excel, the ones you will use, the most will depend on the type of data your workbook contain. There is no need to learn every single function, but exploring some of the different types of functions will help you as you create new products. You can even use the function library on the Formulas tab to browse functions by category, such as financial, logical, taxed, and datatype. In time. To access the function library, we'll just select the Formulas tab on the ribbon, as I already did, and look for the function library group. As you can see, there are several groups and those are different. First, we have insert function. This is very useful if you don't have a specific formulas. So you will just search for a function, will do a search for a function by your keywords. You will hear, enter your keywords and click Go. Then insert function will suggest you possible solution. By clicking on the solution, you can see here explanation. And if this is something that you need, we'll just click Okay. Outer. Some are most frequent, frequently used formulas and functions recently use our dose. You recently used financial. We have a lot of financial functions here. Logical also, DAX to date and time and so on. If you don't have your functions here, you will have more functions and which are divided into several groups. And those are Statistical Engineering, Cube Information, incompatibility, etc. Now, with one example, we will see how to insert a function from the function library. So in my example here, we'll use the COUNTA function to count the total number of items in the items columns. Unlike count counter can be used to tell the cells that contain data of any kind. Now just numerical data. We will do it the next way. First, I will select the cell that will contain the function. In our example it is total items order or B7 will cleave the Formulas tab on the ribbon to access the function library. From the function library group will select the desired function category. In our example, we will choose more functions because we need statistical group. Then we'll select the desired function from the drop-down menu we have here. In our example, as I already told, it is a counter option. As you can see, I have my explanation. Counter counts the number of cells in a range that are not empty. So I will click on a counter. As you can see, my dialog box appeared. I have suggestion, but I will delete my suggestion. Value. One that I want here is a3. A3, a valve. I can add value to. If you want more arguments, you can add value to. But now in my case, I need just one. As you can see here, I have already my result. You can check it here. And after you finish, just click OK. And I have my result. If function is calculated and the result appeared in the cell. So it shows the total of ten items that were ordered. 33. Insert function command: Hello and welcome to Microsoft Excel 2016 tutorial. In the fifth part of the functions lesson, where we'll see the Insert Function commands. While the function library is a great place to browse for functions, sometimes you may prefer to search for one instead. You can do so using the insert function command. It may take some trial and error depending on the type of function you are looking for. However, with practice, the insert function command can be a powerful way to find a function quickly. So let's now see how to use the Insert Function command. In the example here, we want to find a function that will calculate the number of business days each took to receive items after they were ordered. So we'll use the dates in column E and column F to calculate the delivery time in column G. So first, we will select the cell that will contain the function. In our example, we'll select this cell G3. Then we'll click the Formula tab on the ribbon and click Insert function command, formula and insert function. As you can see, the Insert Function dialog box appeared. You will just type a few keywords describing the calculation you wanted the function to perform, and then click ago. So we'll type here, count two days. But also what you can do is from here to select which one category you want. After we search for a function, we have here to select function. So we'll, we'll review the results to find the desired function and then click Okay, Let's see Now which one we have. We are looking for network. This. After we click on a network days, we have here explanation. Network days function returns the number of whole workdays between two days. After we finish our choice, we will click, Okay. As you can see, function arguments, dialog box appeared. From here. We will be able to enter or select the cells that will make up the arguments in the function. In our example, we'll enter as a star d, e three and end date received F3. Holidays before, because it is not bolded, it is not obligate or a field to enter. After I finish, I will click Okay, and the function will be calculated. A result is appeared, appeared in the cell. So the result which shows is here in my example, is four business days to receive order. But one note here that like formulas, functions can be copied to adjust themselves. So simply select the cell that contains the function, then click and drag the fill handle till the end of the column or which you want. And the function will be copied in values for those Tau will be calculative related to their rows or columns. So this was all for the function lesson. And see you later in the next one. 34. Freezing panes : Hello and welcome to Microsoft Excel 2016 tutorial. Today's lesson is freezing panes. Whenever you are working with a lot of data, it can be difficult to compare information in your workbook. Fortunately, Excel includes several tools that make it easier to view content from different parts of your workbook at the same time, including the ability to freeze panes and split your worksheet. So let's see now how to freeze rows. You may want to see certain rows or columns all the time in your worksheet, especially had ourselves. By freezing rows or columns in place, you will be able to scroll through your content while continuing to view the frozen cells. You will do it in next way. First, select the row below the rows you want to phrase. In our example, we want to freeze rows 12, so we'll select row three. On the view of tab, select the Freeze Panes. Then choose Freeze Panes from the drop-down menu. As you can see, the rows are frozen in place, as indicated by the gray line between the second, third row. So you can scroll down the worksheet while continuing to view the frozen rows at the top. So it will be like this. You have to frozen first, second row and you can go them. Second thing we will see is how to freeze columns. First thing in frozen the columns will be to select the column to the right of the columns you want to phrase. In our example, we want to freeze column a, so we will select column B on the View tab, select the Freeze Panes command, then chose Freeze Panes from the drop-down menu again. And the column will be frozen in place as indicated by the gray line. So you can scroll across the worksheet while continuing to use the frozen column in the left. In our example, we will scroll across to column E. So as you can see, this is frozen column. If you only need to freeze the top row or first column in the worksheet. You can simply select Freeze top row or freeze price column from the drop-down menu here, as you can see. And finally, we will see how to unfreeze panes. If you want to select a different view option, you May 1 need to reset this spreadsheet by unfreezing panes to unfreeze rows and columns. This clicks, click on the phrase Pain Command N on Freeze Panes from this drop-down menu. So this was all for this lesson, see later in next one. 35. Other views - split and new window: Hello and welcome to Microsoft Excel 2016 tutorial. Now what we'll see are you options for your workbooks. If your workbook contains a lot of content, it can sometimes be difficult to compare different sections. Excel includes additional options to make your workbook easier to view and compare. For example, you can choose to open a new window for your workbook or split a worksheet into separate pains. So let's now see how to open a new window for the current workbook. Excel allows you to open multiple windows for a single workbook at the same time. In our example, we'll use this feature to compare two different worksheets from the same workbook. To do that, we'll click the View tab in the home, Home tab, as you can see here, it is already in my view. And click on the new Windows command. As you can see, my new window appeared. You can now compare different worksheets from the same workbook across Windows. In our example, we will select 20122013 sales. So we can see two different years in one. And to compare them. If you have several windows open at the same time, you can use the Arrange o option from the View tab and to see and rearrange them quickly. Next thing we will see is how to split a worksheet. Sometimes you may want to compare different sections of the same workbook without creating any window. The split command allows you to divide the worksheet into multiple panes that scroll separately. To do that, you will select the cell where you want to split the worksheet. It is usually somewhere needle. So I will choose, for example, this one. And click the View tab on the ribbon. I'm already here. That's selected is split command. As you can see, the workbook is split into different panes for different paints. You can scroll through each separately using the scroll bars. And it allowing you to compare different sections of the workbook, or for example, to see different person, salesperson and to compare them. After creating a splay two, you can click. You can click and drag the vertical and horizontal dividers to change the size of each section. For example, like this or like this. If you don't want, for example, this word ago, you can just to the left, drag it to the left and it will disappear. And you can go just one story among split. Click the Split comment again and it will be removed. This was all for this lesson and see you later in the next one. 36. Filtering data: Hello and welcome to Microsoft Excel 2016 tutorial. Today we will cover filtering data. If your worksheet contains a lot of content, it can be difficult to find information quickly. Filters can be used to narrow down the data in your worksheet, allowing you to view only the information you need. So now we will see how to filter data. He now example here, we'll apply a filter to an equivalent log worksheets to display only the laptops and projectors that are available for checkout. So in order for filtering to work correctly, your worksheet should include a header row, which is here, hetero, which is used to identify the name of each column. In my example here, as you can see, my worksheet is organized into different columns identified by head ourselves in row one, for example, ID number, type, equipment detail, and so on. So you will select the Data tab and then click Filter command. As you can see, a drop-down arrow appeared in the header cell for each column. Just click the drop-down arrow for the column you want to filter. In our example we will, we will filter column B. So to view only certain types of equipment, the filter menu will appear. Uncheck the box, select all too quickly that these select all the data and then click categories you want in our case, as I told the laptop and projector, and then click AKI. As you can see here. Check. Boxes that are checked are filtered and temporarily my worksheet hiding any content that doesn't match the criteria. So only laptops and tablets are visible. Filtering options can be accessed from the sort and filter command in the Home tab as you can see here. Next thing we'll see is how to apply multiple filters. Filters are cumulative, which means you can apply multiple filters to help narrow down your results. In this example, we have already filtered out worksheet to show laptops and protectors. And we'd like to narrow it down further to only show laptops and projectors that were checked out in August. So again, click the drop-down arrow for the column you want to filter. In this example, we will add a filter to column B to view of information by date. Well then de-select September and October because I need only August and I will click. Okay. As you can see, the new filter is applied in my example, the worksheet is now filter to show on little laptops and tablets that were checked out in August. After applying a filter, you may want to remove or clear it from your worksheet. So you will be able to filter content in different ways. To do it, you will just click the drop-down arrow for the filter you want to clear. In our example, we will clear the filter in column D and choose Clear Filter from checked out. As you can see, the filter, it will be cleared from the column and the previously hidden data is displayed. To remove all filters from your worksheet, just click the filter command on your data tab. As you can see now, all my falters are removed. 37. Advanced filters: Hello and welcome to Microsoft Excel 2016 tutorial. Did they what we'll talk about advanced filtering. If you need a filter for something specific, basic filtering may not give you enough options. Fortunately, Excel includes many advanced filtering tools, including search, text, date, and number filtering. We can narrow your results to help you find exactly what you need. So what will now see how to filter with search? Excel allows you to search for data that contains an exact phrase, number, date, and more. In our example, we'll use this feature to show only salaries brand products in our equipment block. So we will select the Data tab, then click the filter command. You should skip. For our header row, you should skip this step. If you already have your filters on the header. Then we'll, we'll click the drop-down arrow for the column we want to filter. In our example, it is filter, mobile filter column C, which is equipment detail. So we'll click drop-down arrow. And from the text here, we will, we will add our word salaries. As you can see, I will get all my service product and I will just click, Okay. So the worksheet is filtered according to my search term. In our example, the worksheet is now filled or to show only salaries brand equipment. Now what we'll see how to use advanced text filters. Advanced tech filters, text filters can be used to display more specific information, like cells that contain a certain number or characters or data that excludes, that, excludes, sorry, a specific word or number. Now example, we'd like to exclude any item containing the word laptop, and we will do it in the next way. We will click the drop-down arrow for the column we want to filter, which is again equivalent detail. And we will hover the mouse over text filters. As you can see, I have again multiple options. And in my case, I will choose does not contain. As you can see here, I need to enter the word which I don t want my filter to contain. So I will add laptop and click. Okay. As you can see, now, I have all data which doesn't contain a laptop. 38. Advanced numbers and data filters: Hello and welcome to Microsoft Excel 2016 Tutorial. In this lesson, we will cover advanced the number and date filters. Advanced number of filters allow you to manipulate number, number data in different ways. In this example, we'll display only certain types of equipment based on the range of ID numbers. If you hadn't done already filtering for your header, you will do it. So by the Data tab on the ribbon and clicking filter command, then you will click the drop-down arrow for the column you want to filter. In our example, we'll filter column a to view of only a certain range or range of ID numbers. So if I click my drop-down menu, as you can see, Filter menu appeared. I will hover my mouse over a number of filters and see more options because I want to see certain range, I will click between. In my example, I want to see ID numbers which are greater than or equal to 3 thousand and which are less than or equal to 6 thousand. After I enter my values, I will click. Okay. And as you can see, now, I have all filtered the data by the selected numbered number filter in my example, which is three thousand and six thousand. Next thing is using advanced Date filters. Advanced Date filters can be used to view information from a certain period, time period, such as last year or next quarter, between two days, etc. In this example, we'll use advanced Date filters to view only equipment that has been checked out between July and August 15. To do that, we'll select the Data tab and click Filter command if we already done that. So next step is to click the drop-down arrow for the column we want to filter. In our example, we will filter column D. I will click drop-down menu and hover my mouse after the eight filters. So as you can see, here, I have more options and I will choose again between because I need to see equipment that has been checked out between July and August 15. So I will choose my number dates or I could just click here and see July 15. See here August 15. Just I need because this is not 16, it is 15 in my data. So I'll adjust or you can just type manually your dates. I'll click. Okay. As you can see, my filter and now custom filter is selected date filter, which is August 15th and July 15th. So this was all for this lesson about filtering data. I hope you'll learn something and understand and see you later in the next one. 39. Grouping data: Hello and welcome to Microsoft Excel 2016. Today's lesson is about groups and subtotals. Worksheets with a lot of content can sometimes feel overwhelming and even become difficult to read. Fortunately, Excel can organize data into groups, allowing you to easily show and hide the different sections of your worksheet. You can also summarize the different groups using the subtotal command and create an outline for your worksheet. First, we will see how to group rows and columns. First step in this way is to select the rows and columns we want to group. In this example, I will choose b, c, and d columns. As you can see, I select data. Then I will select the Data tab on the ribbon and then click group command. As you can see, the selected rows or columns are grouped. In our example, Column B, C, and D are grouped to ungroup data, select the grouped rows or columns. Then click the ungroup command, as you can see here in Data ribbon. So second thing we will see is how to hide and show groups. First step in debt to hide a group, just click this minus sign, also known as the Hide Details button. As you can see, the group is hidden. To show a hidden group, what we'll just click the plus sign, also known as the Show Details button. As you can see here. 40. Creating subtotals: Hello and welcome to Microsoft Excel 2016 tutorial. Today we will cover creating subtotals. The subtotal command allows you to automatically create groups and use common functions like sum, count, and average to help summarize your data. For example, the subtotal command could help you to calculate the cost of office supply by type from a large inventory order. It will create a hierarchy of groups known as an outline to help organize your worksheet. But one thing to know is that your data must be correctly sorted before using the subtotal command. Let's now see how to create a subtotal. In our example here, we'll use the subtotal command with a T-shirt order form to determine how many T-shirts were ordered in each size. It has small, medium, large, and extra large. This will create an outline for our worksheet with a group for each t-shirt size and then count the total number of shirts in each group. First step in that way is to sort your worksheet by the data you want to subtotal. In this example, we will create a subtotal for each t-shirt size. So our worksheet has been sorted by t-shirt size from smallest to largest. Second step is to select the Data tab from the ribbon and then click Subtotal command. As you can see, subtotal dialog box appeared. Then you'll need to click the drop-down arrow for the change in field to select the column you want to subtotal. In our example, I will choose the shirt size, then click the drop-down arrow for the use function to determine which function you want to use. In our example, we'll select count to count the number of shares ordered in each size. The Add Subtotal to field, you will select the column. Where are you want to calculate a subtotal to appear. In our example, we will again select the shirt size. And once you are satisfied with your selections, just click. Okay. As you can see, the worksheet will be outlined into groups, is outlined into groups, and the subtotal is listed below each group. In our example, the data is now grouped by t-shirt size and the number of shirts ordered in that size appears below each group. 41. Removing subtotals: Hello and welcome to Microsoft Excel 2016 tutorial. Today, we will cover viewing groups by level. When we created sub-totals. Our worksheet is divided into different levels. As you can see here in my example. You can switch them between these levels too quickly control how much information is displayed in the worksheet by clicking their level buttons, which are located to the left of the worksheet here, as you can see in our example, we'll switch between all three levels in our outline. While this example contains only three levels, but Excel can accommodate up to 8. First step is to click the lowest level. The lowest level display the least detail. In our example, we'll select level one, which contains only the grand count, as you can see, or total number of t-shirts ordered. Then we will click the next level, which expand the detail. In our example, this is level two and it contains each subtotal row, but high these heights all our data from the worksheet. The third step is to click the highest level, which in this case is a level three, and it will expand all of your worksheet data. You can also use this show and hide detailed buttons to show and hide the groups within the outline, for example, like this. And as you can see, we have expand and hidden detail in our worksheet. Sometimes you may not want to keep subtotals in your worksheet, especially if you want to reorganize data in different ways. If you no longer want to use subtotal link, you will need to remove it from your worksheet. Then select the Data tab. Then click the subtotal command in order to do it. Select the Data tab and then click Subtotal command. The subtotal dialog box will appear. You will just click Remove all all worksheet data will be ungrouped and the subtotals will be removed. But to remove all groups without deleting the subtotals, you can just click ungroup command drop-down arrow, then choose a clear outline. As you can see here. This was all for this lesson. And see you later in the next one. 42. Tables : Hello and welcome to Microsoft Excel 2016 tutorial. Today, we will cover functions in Excel about tables. Once you have entered information into your worksheet, you may want to format your data is a table. Just like regular formatting tables can improve the look and feel of your workbook. And they will also help you organize your content and make your data easier to use. Excel includes several tools in predefined table styles allowing you to create tables quickly and easily. First thing we'll see is how to format data as a table. First step is to select the cells you want to form. It is a table. I will choose my salary range. And then from the Home tab, click the Format as Table command in the Styles Group. And select a table style from the drop-down menu. For example, I will choose this one. As you can see, my dialog box appeared confirming the selected salary range for the table. And if your table has headers, check the box next to my table has headers and then click, Okay. The cell range will be formatted in the selected table style. Tables include filtering by default, as you can see here, you can filter your data at anytime using the drop-down arrows, using these drop-down arrows, header cells. Next thing is modifying these tables. It is easy to modify the look and feel of any table after adding it to a worksheet, Excel includes many different options for customizing a table, including adding rows or columns and changing the table side. Let us see now how to add rows or columns to a table. If you need to fit more content into your table, excel allows you to modify the table size by including additional rows or columns. There are two simple ways to change the table size. First one is to enter content. For example, here in your content into any adjusts and row or column. In the row or column will be wrapped into the table automatically. For example. As you can see, now, it is automatically changed. Or second way is to click and drag the bottom right corner of the table to create additional rows or columns. Or you can do it like this. If you want to change the table style, you can do it to select any cell in your table. Then click Design tab in the home, in the ribbon. Just locate Table Styles, Group, and click More drop-down menu to see all available table styles. So select the desired table style. For example, I will choose this one. And the table style will be applied. You can also modify this table style options. You can turn various options on or off to change the appearance of any table. There are six options, as you can see here, we've got our header row, total row, bandage row, first column, last column in Banded Columns. You will select any cell in your table. Then click the Design tab. We are already here. You can just check or uncheck the desired options in the tables, table style options. In our example, we'll check total row to automatically include a total row here for our table. The table style will be modified. In our example, a new row has been added to the table with a formula that automatically calculates the total value of the cells in column D. Depending on the type of content you have in the table style you have chosen, these options can affect your tables of periods in the various ways. You may need to experiment with a few different options to find the exact style you want. The last thing is to remove a table. It is possible to remove a table from your worksheet workbook without losing any of your data. However, this can cause issues with certain types of formatting, including colors, fonts, and Banded Rows. Before you use this option, make sure you are prepared to reformat your cells. If necessary. You will select any cell in your table and then click Design tab. Click, then Convert to Range. As you can see, dialog box appeared for confirmation. And you will just click yes. As you can see, the rain will no longer is a table that a cell will retain their data and formatting. To restart your formatting from scratch, just click the Clear command. The clear command from the Home tab here. And clear formats. As you can see, it was just selected, but you will need to select all of your data you want to clear formats. This was all for the tables lesson in Excel. Hope you enjoy it and see you later in the next one. 43. Chart types: Hello and welcome to Microsoft Excel 2016 tutorial. Today we will cover charts. It can be difficult to interpret. Excel workbooks that contain a lot of data chart allows you to illustrate your workbook and data graphically, which makes it easy to visualize comparisons and trends. Excel has several different types of charts allow you to choose the one that best fits your data. In order to use chart effectively, you will need to understand how different charts are used. So as you can see here on the image, excel has a wide variety of chart types and each with its own advantages. So we will now see different types of charts separately. First, we have column charts, which uses a word co bars to represent data. They can work with many different types of data, but they are most frequently used for comparing information. Next type is line. Line charts are ideal for showing friends. The data points are connected with lines, making it easy to see what our values are increasing or decreasing over time. Pie charts make it easy to compare proportions. Each value is shown as a slice of the pie. So it's easy to see which values make up the percentage of a whole. Next one is bar chart. Bar charts work just like column charts with the use horizontal rather than vertical bars. Area is next, and area charts are similar to line charts, except the areas on the radial lines are filled in. And last one is surface. Surface charts allow you to display data across a three-day landscape. They work best with larger datasets, allowing you to see a wide variety of information at the same time. In addition to chart types, you will need to understand how to read a chart. In the continue, what we'll see, how it function. 44. Sorting data: Hello and welcome to Microsoft Excel 2016 tutorial. Today's lesson is about sorting data. As you add more content to a worksheet, organizing, this information becomes especially important. You can quickly reorganize a worksheet by sorting your data. For example, you could organize a list of contact information by lastname. Content can be sorted alphabetically or numerically. And in many other ways, we will now see what types of sorting we have. In Excel, we have one sorting data. It is important to first decide if you want this sort to apply to the entire worksheet or just a salary. And according to that, we have worksheet which organizes all of the data in your worksheet by one column. And the related information across each row is kept together. One disorder is applied. And we have sort Grange, which stores the data in a range of cells, which can be helpful when working with a sheet that contains several tables. Sorting, arrange will not affect the outer content on the worksheet. Now what we'll see how to sort a sheet in our example here, we'll sort a T-shirt order from alphabetically by last name, which is the column C. First, we will select a cell in the column we want to sort by. In our example, we'll select this C2. Then you will select the data, as I already did, tab on the ribbon and click a to Z to sort a to Z or Z to a comment to sort Z to a. In our example, we'll sort a to Z. The worksheet is sorted by the selected column, as you can see in our example, the worksheet is now sorted by the last name. Second thing is to sort arrange. In our example, we'll look, select a separate table in our T-shirt order for the sword, the number of shirts that were ordered on different dates. First step in this way is to select a salary. And we want to sort in our example, I will select salary range G2 to H6, and then select the Data tab on the ribbon. And click sort command. As you can see, the Sort dialog box appeared. Choose the column you want to sort by. In our example, we want to sort the data by the number of T-Shirt orders. So we'll select sort orders sorted by orders. Then we need to decide sorting order, either ascending or descending. In our example, we'll use the smallest, largest to smallest. Once you're satisfied with your selection. Click, Okay. As you can see, the salary range is sorted by the selected column. And in our example, the orders column will be sorted from highest to lowest. Notice that the order content in the worksheet was not affected by the sort. Note here that if your data isn't sorting properly, double-check yourself values to make sure they are entered into the worksheet correctly. Even a small typo could cause problems when sorting a large worksheet. 45. Custom sorting: Hello and welcome to Microsoft Excel 2016's tutorial. In this lesson, we will cover custom sorting. Sometimes you may find that the default sorting options can sort data in the order you need. Fortunately, Excel allows you to create a custom least to define your own sorting order. We will now see how to create a custom sort. In our example here, we want to sort the worksheet by t-shirt size, which is our column D. Regular sort would organize the size is alphabetically, which would be incorrect. Instead, we'll create our custom lists to sort from smallest to largest. Will do it in the next way. First of all, select the column we want to sort by a cell in the column that we want to sort by. And in our example we'll select D2. So we'll select the Data tab. I have already selected it and then click Sort command. As you can see, sorta dialog box appeared. Then select the column you want to sort by. In the column, sort by a list. So you will choose t-shirt size. And going to the order from the order field will choose custom list in the new list, as you can see here, we need to type the items in the desired customer order in the list entries. Here. In our example, we want to sort our data by t-shirt size from smallest to largest. So we'll type small, medium, large, and extra large. I will click Add. As you can see, now, I have my custom list here. When I'm sure that this list is selected, I will click. Okay. So as you can see here, I have my costume list dialog box, closed and sore dialogue appeared. So we have here that it performed the custom sword. After I make sure that everything here is as I wanted, I will click. Okay. As you can see, now, my t-shirt size is made in order from smallest to largest. Next thing we will cover is sorting levels. If you need more control over how your data is sorted, you can add multiple levels to any sword. This allows you to sort your data by more than one column. And we will now see how to add a level. In our example here, we'll sorted worksheet by t-shirt size, which is column D, and then by homerun number, we'll just call them a. So to do that, we'll select this cell in the column we want to sort by. In our example, we'll select A2. Then click the Data tab and sort again command. And we'll go to, as you can see, Sort dialog box and select the first column we want to sort by. In this example, we will sort by t-shirt size with a custom list we previously created for the order field. As you can see, small, medium, large, extra large. And then we will click Add Level to add another column to sort by. Then by home room. As you can see, what will choose homeroom. And we want smallest to largest or from the homeroom. As you can see, the worksheet is sorted according to the selected order. In our example, the orders are sorted by t-shirt size. Within each group of T-shirt sizes, students are sorted by homeroom. Number. One more thing that if you need to change the order of a multi-level sword, it's easy to control which column is sorted first. Simply select the desired column, then click the move up or move down arrow to adjust its priority in the Sort dialog box. This was all for sorting data lesson and see you later in the next one. 46. Filtering data : Hello and welcome to Microsoft Excel 2016 tutorial. Today we will cover filtering data. If your worksheet contains a lot of content, it can be difficult to find information quickly. Filters can be used to narrow down the data in your worksheet, allowing you to view only the information you need. So now we will see how to filter data. He now example here, we'll apply a filter to an equivalent log worksheets to display only the laptops and projectors that are available for checkout. So in order for filtering to work correctly, your worksheet should include a header row, which is here, hetero, which is used to identify the name of each column. In my example here, as you can see, my worksheet is organized into different columns identified by head ourselves in row one, for example, ID number, type, equipment detail, and so on. So you will select the Data tab and then click Filter command. As you can see, a drop-down arrow appeared in the header cell for each column. Just click the drop-down arrow for the column you want to filter. In our example we will, we will filter column B. So to view only certain types of equipment, the filter menu will appear. Uncheck the box, select all too quickly that these select all the data and then click categories you want in our case, as I told the laptop and projector, and then click AKI. As you can see here. Check. Boxes that are checked are filtered and temporarily my worksheet hiding any content that doesn't match the criteria. So only laptops and tablets are visible. Filtering options can be accessed from the sort and filter command in the Home tab as you can see here. Next thing we'll see is how to apply multiple filters. Filters are cumulative, which means you can apply multiple filters to help narrow down your results. In this example, we have already filtered out worksheet to show laptops and protectors. And we'd like to narrow it down further to only show laptops and projectors that were checked out in August. So again, click the drop-down arrow for the column you want to filter. In this example, we will add a filter to column B to view of information by date. Well then de-select September and October because I need only August and I will click. Okay. As you can see, the new filter is applied in my example, the worksheet is now filter to show on little laptops and tablets that were checked out in August. After applying a filter, you may want to remove or clear it from your worksheet. So you will be able to filter content in different ways. To do it, you will just click the drop-down arrow for the filter you want to clear. In our example, we will clear the filter in column D and choose Clear Filter from checked out. As you can see, the filter, it will be cleared from the column and the previously hidden data is displayed. To remove all filters from your worksheet, just click the filter command on your data tab. As you can see now, all my falters are removed. 47. Advanced filters : Hello and welcome to Microsoft Excel 2016 tutorial. Did they what we'll talk about advanced filtering. If you need a filter for something specific, basic filtering may not give you enough options. Fortunately, Excel includes many advanced filtering tools, including search, text, date, and number filtering. We can narrow your results to help you find exactly what you need. So what will now see how to filter with search? Excel allows you to search for data that contains an exact phrase, number, date, and more. In our example, we'll use this feature to show only salaries brand products in our equipment block. So we will select the Data tab, then click the filter command. You should skip. For our header row, you should skip this step. If you already have your filters on the header. Then we'll, we'll click the drop-down arrow for the column we want to filter. In our example, it is filter, mobile filter column C, which is equipment detail. So we'll click drop-down arrow. And from the text here, we will, we will add our word salaries. As you can see, I will get all my service product and I will just click, Okay. So the worksheet is filtered according to my search term. In our example, the worksheet is now filled or to show only salaries brand equipment. Now what we'll see how to use advanced text filters. Advanced tech filters, text filters can be used to display more specific information, like cells that contain a certain number or characters or data that excludes, that, excludes, sorry, a specific word or number. Now example, we'd like to exclude any item containing the word laptop, and we will do it in the next way. We will click the drop-down arrow for the column we want to filter, which is again equivalent detail. And we will hover the mouse over text filters. As you can see, I have again multiple options. And in my case, I will choose does not contain. As you can see here, I need to enter the word which I don t want my filter to contain. So I will add laptop and click. Okay. As you can see, now, I have all data which doesn't contain a laptop. 48. Advanced numbers and data filters: Hello and welcome to Microsoft Excel 2016 Tutorial. In this lesson, we will cover advanced the number and date filters. Advanced number of filters allow you to manipulate number, number data in different ways. In this example, we'll display only certain types of equipment based on the range of ID numbers. If you hadn't done already filtering for your header, you will do it. So by the Data tab on the ribbon and clicking filter command, then you will click the drop-down arrow for the column you want to filter. In our example, we'll filter column a to view of only a certain range or range of ID numbers. So if I click my drop-down menu, as you can see, Filter menu appeared. I will hover my mouse over a number of filters and see more options because I want to see certain range, I will click between. In my example, I want to see ID numbers which are greater than or equal to 3 thousand and which are less than or equal to 6 thousand. After I enter my values, I will click. Okay. And as you can see, now, I have all filtered the data by the selected numbered number filter in my example, which is three thousand and six thousand. Next thing is using advanced Date filters. Advanced Date filters can be used to view information from a certain period, time period, such as last year or next quarter, between two days, etc. In this example, we'll use advanced Date filters to view only equipment that has been checked out between July and August 15. To do that, we'll select the Data tab and click Filter command if we already done that. So next step is to click the drop-down arrow for the column we want to filter. In our example, we will filter column D. I will click drop-down menu and hover my mouse after the eight filters. So as you can see, here, I have more options and I will choose again between because I need to see equipment that has been checked out between July and August 15. So I will choose my number dates or I could just click here and see July 15. See here August 15. Just I need because this is not 16, it is 15 in my data. So I'll adjust or you can just type manually your dates. I'll click. Okay. As you can see, my filter and now custom filter is selected date filter, which is August 15th and July 15th. So this was all for this lesson about filtering data. I hope you'll learn something and understand and see you later in the next one. 49. Grouping data: Hello and welcome to Microsoft Excel 2016. Today's lesson is about groups and subtotals. Worksheets with a lot of content can sometimes feel overwhelming and even become difficult to read. Fortunately, Excel can organize data into groups, allowing you to easily show and hide the different sections of your worksheet. You can also summarize the different groups using the subtotal command and create an outline for your worksheet. First, we will see how to group rows and columns. First step in this way is to select the rows and columns we want to group. In this example, I will choose b, c, and d columns. As you can see, I select data. Then I will select the Data tab on the ribbon and then click group command. As you can see, the selected rows or columns are grouped. In our example, Column B, C, and D are grouped to ungroup data, select the grouped rows or columns. Then click the ungroup command, as you can see here in Data ribbon. So second thing we will see is how to hide and show groups. First step in debt to hide a group, just click this minus sign, also known as the Hide Details button. As you can see, the group is hidden. To show a hidden group, what we'll just click the plus sign, also known as the Show Details button. As you can see here. 50. Creating subtotals: Hello and welcome to Microsoft Excel 2016 tutorial. Today we will cover creating subtotals. The subtotal command allows you to automatically create groups and use common functions like sum, count, and average to help summarize your data. For example, the subtotal command could help you to calculate the cost of office supply by type from a large inventory order. It will create a hierarchy of groups known as an outline to help organize your worksheet. But one thing to know is that your data must be correctly sorted before using the subtotal command. Let's now see how to create a subtotal. In our example here, we'll use the subtotal command with a T-shirt order form to determine how many T-shirts were ordered in each size. It has small, medium, large, and extra large. This will create an outline for our worksheet with a group for each t-shirt size and then count the total number of shirts in each group. First step in that way is to sort your worksheet by the data you want to subtotal. In this example, we will create a subtotal for each t-shirt size. So our worksheet has been sorted by t-shirt size from smallest to largest. Second step is to select the Data tab from the ribbon and then click Subtotal command. As you can see, subtotal dialog box appeared. Then you'll need to click the drop-down arrow for the change in field to select the column you want to subtotal. In our example, I will choose the shirt size, then click the drop-down arrow for the use function to determine which function you want to use. In our example, we'll select count to count the number of shares ordered in each size. The Add Subtotal to field, you will select the column. Where are you want to calculate a subtotal to appear. In our example, we will again select the shirt size. And once you are satisfied with your selections, just click. Okay. As you can see, the worksheet will be outlined into groups, is outlined into groups, and the subtotal is listed below each group. In our example, the data is now grouped by t-shirt size and the number of shirts ordered in that size appears below each group. 51. Removing subtotals: Hello and welcome to Microsoft Excel 2016 tutorial. Today, we will cover viewing groups by level. When we created sub-totals. Our worksheet is divided into different levels. As you can see here in my example. You can switch them between these levels too quickly control how much information is displayed in the worksheet by clicking their level buttons, which are located to the left of the worksheet here, as you can see in our example, we'll switch between all three levels in our outline. While this example contains only three levels, but Excel can accommodate up to 8. First step is to click the lowest level. The lowest level display the least detail. In our example, we'll select level one, which contains only the grand count, as you can see, or total number of t-shirts ordered. Then we will click the next level, which expand the detail. In our example, this is level two and it contains each subtotal row, but high these heights all our data from the worksheet. The third step is to click the highest level, which in this case is a level three, and it will expand all of your worksheet data. You can also use this show and hide detailed buttons to show and hide the groups within the outline, for example, like this. And as you can see, we have expand and hidden detail in our worksheet. Sometimes you may not want to keep subtotals in your worksheet, especially if you want to reorganize data in different ways. If you no longer want to use subtotal link, you will need to remove it from your worksheet. Then select the Data tab. Then click the subtotal command in order to do it. Select the Data tab and then click Subtotal command. The subtotal dialog box will appear. You will just click Remove all all worksheet data will be ungrouped and the subtotals will be removed. But to remove all groups without deleting the subtotals, you can just click ungroup command drop-down arrow, then choose a clear outline. As you can see here. This was all for this lesson. And see you later in the next one. 52. Tables : Hello and welcome to Microsoft Excel 2016 tutorial. Today, we will cover functions in Excel about tables. Once you have entered information into your worksheet, you may want to format your data is a table. Just like regular formatting tables can improve the look and feel of your workbook. And they will also help you organize your content and make your data easier to use. Excel includes several tools in predefined table styles allowing you to create tables quickly and easily. First thing we'll see is how to format data as a table. First step is to select the cells you want to form. It is a table. I will choose my salary range. And then from the Home tab, click the Format as Table command in the Styles Group. And select a table style from the drop-down menu. For example, I will choose this one. As you can see, my dialog box appeared confirming the selected salary range for the table. And if your table has headers, check the box next to my table has headers and then click, Okay. The cell range will be formatted in the selected table style. Tables include filtering by default, as you can see here, you can filter your data at anytime using the drop-down arrows, using these drop-down arrows, header cells. Next thing is modifying these tables. It is easy to modify the look and feel of any table after adding it to a worksheet, Excel includes many different options for customizing a table, including adding rows or columns and changing the table side. Let us see now how to add rows or columns to a table. If you need to fit more content into your table, excel allows you to modify the table size by including additional rows or columns. There are two simple ways to change the table size. First one is to enter content. For example, here in your content into any adjusts and row or column. In the row or column will be wrapped into the table automatically. For example. As you can see, now, it is automatically changed. Or second way is to click and drag the bottom right corner of the table to create additional rows or columns. Or you can do it like this. If you want to change the table style, you can do it to select any cell in your table. Then click Design tab in the home, in the ribbon. Just locate Table Styles, Group, and click More drop-down menu to see all available table styles. So select the desired table style. For example, I will choose this one. And the table style will be applied. You can also modify this table style options. You can turn various options on or off to change the appearance of any table. There are six options, as you can see here, we've got our header row, total row, bandage row, first column, last column in Banded Columns. You will select any cell in your table. Then click the Design tab. We are already here. You can just check or uncheck the desired options in the tables, table style options. In our example, we'll check total row to automatically include a total row here for our table. The table style will be modified. In our example, a new row has been added to the table with a formula that automatically calculates the total value of the cells in column D. Depending on the type of content you have in the table style you have chosen, these options can affect your tables of periods in the various ways. You may need to experiment with a few different options to find the exact style you want. The last thing is to remove a table. It is possible to remove a table from your worksheet workbook without losing any of your data. However, this can cause issues with certain types of formatting, including colors, fonts, and Banded Rows. Before you use this option, make sure you are prepared to reformat your cells. If necessary. You will select any cell in your table and then click Design tab. Click, then Convert to Range. As you can see, dialog box appeared for confirmation. And you will just click yes. As you can see, the rain will no longer is a table that a cell will retain their data and formatting. To restart your formatting from scratch, just click the Clear command. The clear command from the Home tab here. And clear formats. As you can see, it was just selected, but you will need to select all of your data you want to clear formats. This was all for the tables lesson in Excel. Hope you enjoy it and see you later in the next one. 53. Chart types: Hello and welcome to Microsoft Excel 2016 tutorial. Today we will cover charts. It can be difficult to interpret. Excel workbooks that contain a lot of data chart allows you to illustrate your workbook and data graphically, which makes it easy to visualize comparisons and trends. Excel has several different types of charts allow you to choose the one that best fits your data. In order to use chart effectively, you will need to understand how different charts are used. So as you can see here on the image, excel has a wide variety of chart types and each with its own advantages. So we will now see different types of charts separately. First, we have column charts, which uses a word co bars to represent data. They can work with many different types of data, but they are most frequently used for comparing information. Next type is line. Line charts are ideal for showing friends. The data points are connected with lines, making it easy to see what our values are increasing or decreasing over time. Pie charts make it easy to compare proportions. Each value is shown as a slice of the pie. So it's easy to see which values make up the percentage of a whole. Next one is bar chart. Bar charts work just like column charts with the use horizontal rather than vertical bars. Area is next, and area charts are similar to line charts, except the areas on the radial lines are filled in. And last one is surface. Surface charts allow you to display data across a three-day landscape. They work best with larger datasets, allowing you to see a wide variety of information at the same time. In addition to chart types, you will need to understand how to read a chart. In the continue, what we'll see, how it function. 54. Charts: Hello and welcome to Microsoft Excel 2016 tutorial. This is the second part of charts lesson, and we'll, we'll see how to insert a chart. You need to select the cells you want to chart. I will choose this range, including the column titles and row labels. And from the Insert tab in the ribbon, you will click the desired chart command will choose column. I will choose this one. You can choose the desired char type from the drop down menu. And this electric charge will be inserted into the worksheet. As you can see, if you are not sure which type of chart to use, the Recommended Charts command will suggest several different charts based on the source data. After inserting a chart, there are several things you may want to change about the way your data is displayed. It is easy to add it to our charts layout, and style from the Design tab. Excel allows you to add Chart Elements such as charge titles, legends, and data tables, labels to make your chart easier to read. To add a chart element, click the Add Chart Element command on the Design tab, as you can see here, and then choose the desired element from the drop-down menu. As you can see here, we have a legend trend line data table labels, title, axis title. To edit a chart element like a chart title, you will just simple double-click the placeholder and began typing. For example, I will choose book sales. Now, this is my title. If you don't want to add a chart elements individually, you can use one of the predefined layout. Simply click the Quick Layout command, and then choose the desired layout. Here quickly out and choose the desired layout from the drop-down menu. Excel also includes several charts styles which allow you to quickly modify the look and feel of your chart. To change the chart style, you just select the desired style from the Chart Styles group. And you can also click the drop-down arrow on the right to see more styles as you can see here. You can also use the chart formatting shortcut button to quickly add chart elements and change the chart style and filter the char data. As you can see here on the right side of the chart. There are many other ways to customize and organize your charts. For example, Excel allows you to rearrange a char as data changes the chart type and even move the chart to a different location in a workbook. We will now see how we can switch row and column data. Sometimes you may want to change the way you charged, group your data. For example, in the chart here in my book, sales data is grouped by John there we'd column for each month. However, we could speak to the rows and columns. So the chart will group the data by month with columns for each genre. In both cases, the chart contains the same data. It is just organized differently. To do that, we'll select the chart, we want to modify it. Then click the Design tab and select this which row or column. As you can see, they're also in columns are switched. In our example, the data is now grouped by month with columns for each John there. What can also change the chart type? If you find that your data is, isn't file suited to a certain chart, it is easy to switch on your chart type. In our example, we'll change our chart from a column chart to align chart. So I will select and as you can see here, Change Chart Type. When you click on Change Chart Type, change our type dialog box appear, and select a new chart type In layout. So in our example we'll choose line. And I will click, Okay. As you can see, the selected char type appeared. And in our example, the line chart makes it easier to see trends in sales data over time. You can also move our chart. Whenever you insert in a new chart, it will appear as an object on the same worksheet that contains its source data. Alternatively, you can move the chart to a new worksheet to help keep your data organized. You will just select the chart you want to move, then click the Design tab, and then select Move Chart. Command. The Move Chart, the dialog box appeared, and select the desired location. As you can see here, I can, I will make a new sheet, which will create a new worksheet and just click, Okay. As you can see, now, I have new sheet. What is a chart? One and my chart is moved to a new worksheet. This was all for charts lesson. I hope you'll learn something and that you enjoy it. After all. See you later in the next lesson. 55. Conditional formatting: Hello and welcome to Microsoft Excel 2016 tutorial. Today we will cover conditional formatting. Let's say you have a worksheet which thousands of rows and data. It would be extremely difficult to see patterns and trends just from examining the row information similar to charts and sparkling this condition, Conditional Formatting provides another way to visualize data in make worksheets easier to understand. So we will now see what is conditional formatting and how to understand it. Conditional formatting allows you to automatically apply formatting such as colors, icons in data bars to one or more cells based on the cell value. To do this, you will need to create a conditional formatting rule. For example, a conditional formatting rule might be, if the value is less than 2000s, color the cell red. By applying this rule, you will be able to quickly see which cells contains values less than 2 thousand. Let's see now how to create a conditional formatting rule. In our example, we have a worksheet containing sales data and we'd like to see which salespeople are meeting their monthly sales goals. The sales goal is 4 thousand per month. So we'll create a conditional formatting rule for any cells containing a value higher than 4 thousand. To do that, we'll select the desired cell for the conditional formatting rule, which is this range. Then from the Home tab, we'll click Conditional Formatting. A drop-down menu appeared. As you can see, you can now hover the mouse over the desired conditional formatting type and select the desired role from the menu that appears in our example. We will choose to highlight cell rules which are greater than. As you can see my dialog box appeared. We will now enter the desired value, which is $4 thousand. And we will see light red fill with dark red text. You can choose whatever you want. I will keep this one and click. Okay. As you can see, the conditional formatting is applied to the selected cells. And in our example, it is easy to see which salespeople reach the default thousand dollars sales goal for each month. What you can do, you can also apply multiple conditional formatting rules to a salary and or worksheets, allowing you to visualize different trends or patterns in your data. 56. Conditional formatting presets: Hello and welcome to Microsoft Excel 2016's tutorial. In the second part of conditional formatting, we'll, we'll see some conditional formatting presets. Excel has several predefined styles or presets you can use to quickly apply conditional formatting to your data. And they are grouped into three categories. Data bars, color scales, and icon sets. You can find them here. Data bars, color scales, and icon sets. Data bars are horizontal bars added to each cell, much like a bar graph. For example, like this. Higher scale. The higher bars it is, the higher value. Color scales are changed the color of each cell based on its value. Each color scale uses a two or three color gradient, as you can see here, in the green, yellow, red color scale. Or it is one, I choose. Green color is the highest value. The average values are yellow and the lowest values are red. We have icon sets. As you can see here. I can set at a specific icon to eat cell based on its value. And according to that, you can see easily. But now we will see how to use preset Conditional Formatting. Again, we will select that these are cell for the conditional formatting rule. And then click the Conditional Formatting command, a drop-down menu appeared. We will hover the mouse over the desired preset and then choose a preset style from the menu that appears. For example, I will choose, I will choose icon sets. This one. Of course, you can remove conditional formatting as well. And to remove conditional formatting, you will click Conditional Formatting from the Home tab. And as you can see, drop-down menu appeared. You will hover my mouse over clear roles. And as you can see, you have two options. We have clear rules from selected self and clear roles for entire sheet. That also you have one more options which is Manage Roles. If I click on this, as you can see, I have conditional formatting rules Manager, dialog box, and you have all or conditional formatting and roles we made. It is I can set and sell value. It is very useful if you have more rules you applied. So you can just delete. If you want, you can excel. You can cancel and delete. As you can see here, click and delete role AKI. As you can see, I have now just this one, but in my example, I will clear roles from entire sheet. This was all for this lesson. I hope you'll learn conditional formatting and I hope you enjoyed it. See you later in the next lesson. 57. Track cahnges: Hello and welcome to Microsoft Excel 2016's tutorial. Today we will cover Track Changes. Let's see, Someone asked you to proofread or collaborate on a workbook. If you had a printed copy, you might use a red pen to add it. Sell data, mark spelling errors, or add comments in the margins. Excel allows you to do all of these things electronically using the track changes and common features. Let's now see what our track changes. When you turn on track changes feature in Excel, every cell you added will be highlighted with a unique border and indicator. Selecting a marked cell will show the details of the change. This allows you and the other reviewers to see what's been changed before accepting their revisions permanently. There are some changes in Excel that cannot be tracked. And also you cannot use Tractatus if your workbook include tables. To remove a table, just select it, click the Design tab and then click convert to range. To turn on Track Changes command. You will go to Review tab, then click the track changes here and select highlight changes. As you can see here, you have to check this box and verify the box. Each act of highlight changes on screen and then click, Okay. If prompted as it is case here, just click Okay to allow Excel to save your workbook. Okay? Track changes will be turned on in a triangle and border color will appear in any cell you added. If there are multiple, multiple reviewers, each person will be assigned a different color. Then selected the edited cell to see a summary of the track changes. In our example here, we've changed the content of the cell D 14 from these question mark to getting to know your team. Getting to know your theme. As you can see, my cell is highlighted and one I want, I just hover my mouse over it. You can see that it is changed to getting to know your team, time, date and who did it. When you turn on track changes, your workbook will be shared out automatically. Shared work books are designed to be stored where our users can access and add it to the workbook at the same time, such as the network. However, you can also track changes in a local or personal copy as seen throughout this lesson. One more thing to see is how to lead to changes on a separate worksheet. You can also view changes on your worksheets, sometimes called the detract, changes history. The history lists everything in your worksheet that has been changed, including the old value, which is previous cell content, and the new value which is current cell content. To do that, you first have to save your workbook. Then from the Review tab, click the track changes and then select highlight changes. The Highlight Changes dialogue box appeared as you can see, you will just check the box next to list changes on a new sheet and then click. Okay. As you can see, the track changes are listed on my new or new sheet called history. So it is date, time, et cetera. Them. To remove the history worksheets from your workbook. You can either save your workbook again or uncheck the box next to list changes on new shade in the Highlight Changes, dialogue box. 58. Reviewing changes: Hello and welcome to Microsoft Excel 2016 tutorial. Today we will cover reviewing changes. It is related to track changes in the previous lesson. Changes are really just suggested. Changes to become permanent, the changes must be accepted. Or on the other hand, the original order may disagree with some of the track changes and choose to reject to them. We will now see how to review track changes. First step is to click on the review tab. I'm already here, then click track changes. And as you can see, we have option accept or reject changes from the drop-down menu, will click on it. And if prompt, click Okay to save your work book, but I already did it in last time. And threats step is that a dialog box appeared, as you can see. Make sure the box next to the one field is checked and set to not yet revealed. Then click, Okay. A dialog box appeared as you can see, and you can click, accept or reject for each change in the workbook, excel will move through each change automatically until you have reviewed them all. But to save time, I will just click accept all. As you can see, even after accepting or rejecting changes, the track changes will still appear in your workbook. To remove them completely, you will need to turn off track changes from the Review tab. Click track changes, then click highlight changes from the drop-down menu and from this dialog box, if from this dialog box, you will uncheck the box. Track Changes while editing and click, Okay. As you can see, you have to click Yes to confirm that you want to turn arthritic changes and stop sharing your workbook. Just click Yes. And as you can see now, my information and changes are completely accepted. Turning off track changes will remove any track changes in your workbook. As you saw, you will not be able to view of accept or reject changes. Instead, all changes will be accepted automatically. Always review the changes in your worksheet before turning off track changes. 59. Comments: Hello and welcome to Microsoft Excel 2016 tutorial. Today we will talk about comments. Sometimes you may want to add a comment to provide feedback instead of editing the content of a cell. While often used in combination with track changes, you don't necessarily need to have track changes turned on to use KMS. Let's see now how to add a comment. To add a comment, first step is to select the cell where you want the comment to appear. In our example, I will choose the 17. And from the Review tab, click option, new comment. As you can see, a common box appear and you'll just type your comment, then click anywhere outside of the box to close the comment. My comment is, how long is this hike? The comment will be added to the cell, and it is represented by the red triangle in the top right corner. As you can see, just select the cell again to view of the comment. You can also add it to your comment. And to do that, you will just select this cell containing the comment you want to add it. Then from there View tab click Edit Comment. As you can see, the comment box appeared, you can just add it to the comment as desired and then click anywhere outside the box to close the comment. As you can see, after I enter my comment, I added my comment, I will just click anywhere outside of the box. And next thing what we'll see is how to show or hide comments from the Review tab. Click the Show All comments command to view every comment in your worksheet at the same time. As you can see all comments in the worksheet appeared. Click the Show All comments, comment again to hide them. You can also choose to show and hide individual comments by selecting the desired style and clicking the Show Hide command. And finally, to see how to delete a comment, you will select the cell containing the common you'll want to delete. And just from the Review tab, click the Delete comment in the comments group. The comment will be deleted. This was all for this lesson, and I hope you enjoyed and learned something and see you later in the next one. 60. Inspecting workbooks: Hello and welcome to Microsoft Excel 2016 tutorial. Today we will cover inspecting and protecting workbooks. Before sharing a workbook, you will want to make sure it doesn't include any spelling errors or information you want to keep private. Fortunately, Excel includes several tools to help finalize and protect your workbook, including document inspector and the Protect Workbook feature. So let's see first, what is a document inspector? Whenever you create or edit a workbook, certain personal information may be added to the file automatically. You can use document inspector to remove this information before sharing a workbook with others. But because some changes may be permanent, it is a good idea to save an additional copy of your workbook before using the document inspector to remove information. Let's see now how to use document Inspector. Click the File tab on your Excel to accept to access backstage view of Dan from the info pane, click Check for Issues. And in the drop-down menu you can see Inspect Document option. You will click on these back document option. And you before you may be prompted to save your file before running document inspector. So click Yes. And to document, this factor will appear. This box as here in my Excel document. Then check or uncheck boxes depending on the content you want to review. Here, as you can see, in our example, we'll leave everything selected, then click Inspect. As you can see the inspection results appeared in our example. We can see that our workbook contains comments in some personal information. So we'll click Remove all from boat. To remove this information from the workbook. When you are done, just click Close cell. Now, our document is inspected and we can go on. 61. Protecting workbooks: Hello and welcome to Microsoft Excel 2016 tutorial. Today we will cover protecting your workbook. By default, anyone with access your workbook will be able to open, copy and edit its contents unless you protect it. In Excel, there are many ways to protect a workbook depending on your needs. Let's see now how to protect your workbook. Click the File tab to access backstage view. Then from the info pane, click Protect Workbook command. As you can see in the drop-down menu, you have options and you can choose the best option which best suits your needs in our example. Well, select mark as final. Marking your work book is as final is a good way to discover, discourage others from editing the workbook while the other options give you even more control if needed. As you can see, a dialog box appeared and prompting you to save. Just click, Okay. A dialog box appeared again for confirmation and you will just click. Okay. As you can see, the workbook is marked as final. Whatever someone tried to open my account, my document workbook, we'll get to these notification which suggests that it is marked as final. So they can add it anyway, click on this button and edit. So marking a workbook as final will not prevent others from editing it. But if you want to prevent people from editing it, you can use the restrict access option instead. This was all for this lesson and see you later in the next one. 62. Introduction to pivot tables : Hello and welcome to Marks of Pixel 2016 tutorial. Today we will introduce pivot tables when you have a lot of data, it can sometimes be difficult to analyze all of the information in your worksheet. Pivots. Tables can help make your worksheets more manageable by summarizing your later and allowing you to money. People eat it in different ways. We use pivot tables to answer. Question. Consider my data here and let's say we want you to answer the question. What is the amount sold by each sales person? Answering It could be time consuming and difficult. Each sales person appears on multiple rows, and we would need to total all off their different orders individually. We could use the sub total commend to help find the total for each sales person. But we would still have a lot of data to work with. But people table well, save our time. So let's see now how to create a pivot table, First selected table or cells, including column hatters. You want to include in your pivot table, so I will select all my data from the insert tab. Click in the people Stable command as you can see create pivot table dialog box appeared does your settings here I will to stable one and that my pivot table want to be report in new work Shit. And when you're done with your settings will just click. OK, as you can see a blank pivot table and feel the least appeared on a new work Shit. So, as you can see, once you create a pivot table, you will not need to decide which fields to add. Each field is simply ah, column Hatter from the source data in the pivot table field least here, check the box for each field you want to add. In our example, we want to know the total amount sold by each sales person, so we will tax, steals, person box and order amount fields on other way east to drag and drop you're Hatter. The selected fields will be added toe one offer the four areas below. In our example, the sales person field has been added to the rose area, while order amount is added to the or values. Alternatively, you can drag and drop fields directly into the desired area, as you can see, or you can just check the boxes here in the People Table Joe's Fields, the people stable will calculate and summarize the selected fields. As you can see now, example, the food people stable shows the amount sold by each sales person. Just like with normal spreadsheets. You can soar to the data in a pivot table using the sword and filter commend on the home tab. Or you can also apply any type of number formatting you want. For example, you may want to change the number four match to currency. However, be aware that some types of formatting may disappear when you modify the pivot table. Note here that if you change any of the data in your source worksheet, the people table will not update automatically to manually update it. Select the people table and then goto analyzed and refresh. And last thing is pivoting data. One of the best things about people tables is that they can quickly people toe or reorganize your data, allowing you to examine your Roche it in several ways. Pivoting data can help us for different questions and even experiment with your data to discover new trends and patterns. So in that way, you can add columns. So far, our people table has only shown one column at a time. In order to show multiple Collinsville, you'll need to add a field to the columns area. So drag and field month in column. And as you can see, we have now multiple columns to change. AURORE COLUMN You can give a completely different perspective on your later. All you have to do is remove the field in question and replace it with another. So you I can just drag and drop here and I cancel my sales person in rows, and I will just enter, for example, region in that area. So, as you can see now, my data is different, the people table adjusted and showing a new data in our in our example. It now shows the amount sold by each a region. This was all for these introduction off people's tables. I hope you like it and learn something end. See you later in the next lesson. 63. Pivot - Filters: Hello and welcome to Microsoft Excel 2016 tutorial. As you learned in our previous lesson introduction to be with tables people. Tables can be used to summarise and analyze almost any type of data to help human people eat your pivots table and gain even more insight into your data. Exel offers three additional tools. Filters, slicers and be with Char's, So let's start with the filters. Sometimes you may want focus on a certain section of your data. Filters can be used to narrow down the data in your people stable so you can view on Lee the information you need. Let's see now how toe add a filter in the example here will filter our certain sales people to the Thurman how their individual sales are impacting each region. So to do that, drag seals person field into to feel thirst. As you can see, the filter appeared above the fevered table Dan. Click drop down arrow, then check the box next to select multiple items, select multiple items. Now you can uncheck the box that next to any item you don't want to include in the pivot table. In my example here, I will contact the boxes to for a few sales people. I will choose this. This and this author, you're ready. Click OK. As you can see, the beauty table adjusted to reflect the changes. In the next lesson, we'll see something more about slicers. 64. Pivot - slicers : Hello and welcome to Marcus of Excel 2016 tutorial. In the second part, off pivot tables will see more about slicers. Slicers make filtering data in pivot tables even easier. Slicers are basically just feel thirst. Both are easier and faster to use, allowing you to instantly paver to your data. If you're frequently filter your pivot tables, you may want to consider using slicers instead of filth wrists, so we will now see how toe add a slicer. First, select any cell in the pivot table and from the analyze tab you will click. Insert slicer command. As you can see dialog box appeared Jack the box next to the desired field. In my example here, I will choose seals person and then click OK. As you can see, this slicer appeared next to the pivots table. Each selected item will be highlighted in bull as it is here. In the example below, the slicers contains oh, eight sales people, but on Li fi off them are currently selected just like filters on Lee. Selected items are used in the pivot table. When you select or de select them, the pivot table will instantly reflect the change So if I, for example, add Brennan Michael to my least as you can see, you can check more than one by taking control on your keyboard. So, as you can see, my pivot table now is a little bit different. What you can do also is that you can click filter Aiken on the top, right quarter off the slicer to select all items it once. 65. Pivot - Charts : Hello and welcome to Microsoft Excel 2016 tutorial. Today we will cover be with charts. People trust our like regular charts except the display data from a pivot table. Just like a regular cause charts, you will be able to select a chart type layout and style that will best to represent to the data. Let's see now how to create a pivot chart. In example, here, our pivot table is showing a portion off each region sales figure. So will use the people charged so we can see the information more clearly. Select any sow in your pivot table, then from the insert tab here, click the pivot chart. Commend as you can see favor Chart box Insert charged The dialog box appeared. You will now select the desire tar type in layout and click OK, so I will choose this one. And as you see the people charred appeared off course. You can use filters or slicers to narrow down the data in your people charge or to view different subsets of information to change columns or rows in your pivots table. For example, I can who'd sales be people out or I can add month in my access as you can see different options which will change your data in your pivot table as well as in your pure chart. This was all for the people to tables. Lesson an additional people table features. I hope you will like it and that you'll learn something and see you later in the next list . 66. What if Anlaysis - goal seek 1: Hello and welcome to Microsoft Excel 2016. Tutorial Today will cover What if analysis Excel includes many powerful tools to perform complex mathematical calculations, including What if analysis This feature can help you experiment and answer questions with your data, even won. The data is incomplete in this lesson. You will learn how to use a what if analysis tool cold gold seek. So let's see what he's gold seek. Whatever you create a formula or function in Excel, you put various parts together to calculate a result. Go Sig works in the opposite way. It lets you start with a desired result and it calculates the input value that will give you the net result. We will use a few examples to show how to use gold seek. So, first example as you can see here, we'll show you that less. Hey, you are enrolled in a class and your currently have you currently have a great off 65 you need at least a 70 to pass the class. Luckily, you have one final assignment that might be able to raise your average so you can use gold . Seek to find out what a great you need on the final assignment. Pass the class. So as you can see here, in my example, the grates on the 1st 4 assignments are 58 70 72 16. Even though we don't know what if if grade will be weaken right, Ah, formula or function that Khalkhali the final great In this case, each assignment is weighted equally. So all we have to do is average all five grades by typing formula here, equal signed and average, including cells from B two to B six. I'm just click OK. Once we use school sake, Selby six will show us, said the minimum grade will need to make on that assignment to make gold seek what will select the cell with the value we want to change whatever we use school seek. You will need to select the cell that already contains a formula or function. In our example it is cell B seven. So will quick B seven And then from the data tap, we will quick what if analysis and from the drop down my new shoes Gold seek. As you can see, a dialog box appeared with the three fields The first field or SAT cell will contain the desired result. In our example it is B seven. The second field were to value is the desired result. In our example we will enter 70 because we need 72 past exam and the third field by changing south is the cell where goal see quote please. It ends Worm So woke Woolls cell activity sticks after we have finished wall quick. Okay. As you can see, excel need a little bit time to calculate result and if it find result in the dialogue box will tell you that it found a solution. The result will appear in this pacify cell in our example Goal seek calculated that we will need to score a police a nightie on the final assignment toe enter a passing Great. So just click ok and this is my result 67. What if Analysis - goal seek 2 : Hello and welcome to Microsoft Excel 2016 tutorial. This is second part of what if analysis and we will see one more example to use gold seek. Let's they were planning and want and want to and white as many people as you can without exceeding a budget off 500 so we can use gold. Seek to figure out how many people to invite in our example. Here, Selby five contained the formula, which is I will show your now here in my what is be to plaza be three times before to calculate the total cost off a room reservation. Blast the cost per person. So what will select to the cell with the value we want to change? In our example, we will select to be five. And from the data tab, click the what if analysis and select a goal seek from the drop down menu again, we have three feels the first feel the desired result. What is be five to value global and 3500 because we can spend 500 in the turned field by changing cell is the cell where gold sequel place it's answer in our example, will select cell before because we want to know how many guests we can in wide without spending more than 500 after we are done, Double click. OK, as you can see, Gold seeking with Salbi five found a solution. So the result appeared in the specified cell. In our example Gold seek calculated The answer, Toby Approximately 18.62. In this case, our final answer needs to be a whole number. So we'll need to round. The ants were up or down because a rounding up would cause us to exceed our budget. We're round down to 18 guests. As you can see in our example here, some situation will require the answer to be a whole number. If gold sig gives you an decimal, you will meet around, up or down, depending on a distant creation.