I love Excel: 80 easy lessons to build your Excel skills | Jiri Benedikt | Skillshare

I love Excel: 80 easy lessons to build your Excel skills

Jiri Benedikt, Future skills trainer

I love Excel: 80 easy lessons to build your Excel skills

Jiri Benedikt, Future skills trainer

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
81 Lessons (5h 22m)
    • 1. Welcome to the "I love Excel" class!

      0:44
    • 2. Before you start: Language and regional differencess in Excel

      2:45
    • 3. Inserting, moving and deleting sheets

      4:28
    • 4. Inserting and deleting rows and columns

      2:21
    • 5. Resizing and batch-updating rows and columns

      1:58
    • 6. Moving and copying rows and columns

      2:34
    • 7. Hiding rows and columns

      3:38
    • 8. Merging and unmerging cells

      3:14
    • 9. Freeze panes - Keep the top rows or leftmost columns always visible

      3:16
    • 10. Using Find and Replace

      5:11
    • 11. "The Islands Principle" in Microsoft Excel

      4:31
    • 12. Sorting rows alphabetically or by size

      3:11
    • 13. Sorting rows using multiple columns

      3:10
    • 14. Sorting rows by color

      2:26
    • 15. Filtering a spreadsheet in an easy way

      4:28
    • 16. Advanced filtering

      5:14
    • 17. Reversing a list

      1:42
    • 18. The most useful keyboard shortcuts

      3:15
    • 19. Moving fast across the sheet using keyboard

      3:51
    • 20. How to add your favorite icons to a Quick access toolbar

      4:10
    • 21. Formatting basics: Changing text color, borders, making two cells look the same

      5:27
    • 22. Adjusting text horizontally and vertically, changing the text direction

      2:14
    • 23. Adding multiple lines of text in one cell

      3:06
    • 24. Adding cell borders the easy way

      3:34
    • 25. Add cell borders automatically

      3:08
    • 26. Color cells automatically based on their value - Conditional formating

      4:55
    • 27. Creating color scales and other tricks with conditional formatting

      3:23
    • 28. Adding icons, arrow, symbols and mini-charts into cells

      3:55
    • 29. Highlighting an entire row when a value reaches a specific value

      3:37
    • 30. Filling series of numbers

      4:55
    • 31. Creating timelines easily by days, workdays, months or years

      6:01
    • 32. How formulas work in Excel

      8:30
    • 33. How to insert and edit formulas the correct way

      7:19
    • 34. Using absolute and relative referencing - using the dollar signs (=$A$1)

      3:39
    • 35. Using basic functions - SUM, AVERAGE

      7:22
    • 36. Case study: Creating a simple spreadsheet with calculations

      4:41
    • 37. How date works in Excel

      6:43
    • 38. Adding a todays date that updates automatically with TODAY function

      2:30
    • 39. Finding out number of days between two dates, adding and substracting days

      6:17
    • 40. Finding out what day of a week of a certain date

      5:17
    • 41. Splitting dates in to day, month and year

      3:50
    • 42. Magically split and connect columns with the amazing Flash Fill function

      5:06
    • 43. Merging text columns using a formula (&)

      4:25
    • 44. Splitting a column into multiple ones using "Text to columns"

      4:47
    • 45. Splitting text columns using LEFT and RIGHT functions

      5:33
    • 46. VLOOKUP: Connecting data from two spreadsheets using the most important function in Excel

      4:32
    • 47. VLOOKUP: Connecting data across multiple sheets

      4:03
    • 48. VLOOKUP: The traps

      5:52
    • 49. VLOOKUP: Tips and tricks

      3:19
    • 50. XLOOKUP: The new VLOOKUP replacement in Microsoft Excel 365

      1:19
    • 51. Differencess between VLOOKUP and XLOOKUP

      2:13
    • 52. Limiting what can be written in a cell

      4:13
    • 53. Creating a pop-up help message next to a cell

      2:40
    • 54. In-cell dropdowns for each cell

      6:06
    • 55. Making good-looking Excel forms

      1:22
    • 56. Protecting sheets against accidental rewrite

      4:52
    • 57. Letting people edit only certain cells of your sheet

      2:18
    • 58. Protecting a workbook against manipulation with sheets

      2:34
    • 59. Encrypting a workbook with a password

      2:47
    • 60. Introduction to pivot tables

      3:18
    • 61. Preparing data for a pivot table

      10:57
    • 62. Creating a simple pivot table to summarize your data

      12:13
    • 63. Building the perfect pivot table by manipulating rows and columns

      7:28
    • 64. Working with values in pivot tables

      8:34
    • 65. Creating a chart from a pivot table

      8:18
    • 66. Creating a simple chart

      3:10
    • 67. Adding a chart name and category labels

      2:30
    • 68. Making a chart easy to read with data labels

      1:17
    • 69. Pie chart and its special features

      3:29
    • 70. Types of column charts and when to use them

      2:48
    • 71. Switching rows and columns

      1:27
    • 72. Editing horizontal and vertical axes

      2:07
    • 73. Combo charts the easy way - columns and lines in a single chart

      2:17
    • 74. Changing a color of an individual column

      1:05
    • 75. Creating visually appealing charts with chart quick styles

      2:15
    • 76. More tips for professionally looking charts

      2:25
    • 77. Printing in excel the correct way

      6:20
    • 78. Repeating first rows on each printed sheet

      1:36
    • 79. Printing multiple sheets

      1:42
    • 80. Saving sheets as PDF

      1:46
    • 81. Thank you, wrap up, next steps

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

Community Generated

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

12

Students

--

Projects

About This Class

I made this class for all of you that would like to finally learn and understand Microsoft Excel. There are over 80 short, bite-sized videos. They include Excel tips, tricks, functions and tutorials. All of them are narrated with easy language. This class will boost your Excel confidence and your resume. Also, it will save you a lot of time!

Meet Your Teacher

Teacher Profile Image

Jiri Benedikt

Future skills trainer

Teacher

I am Jiri and I love Excel. I believe that learning Excel is one of the easiest way how to boost your resume, improve your skills and have easier and happier work life. 

I have 20+ years of Excel experience and 10+ years training experience. 

I am a freelance trainer of digital skills, lean, design thinking based in Prague. I deliver trainings on Excel, Lean Six Sigma and Design Thinking worldwide for companies like Johnson&Johnson or DHL.

Ex PwC, Lean Six Sigma Black Belt Certified (2016).

I live in Prague with my lovely wife Eva and daughter Anna.

See full profile

Class Ratings

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

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

Your creative journey starts here.

  • Unlimited access to every class
  • Supportive online creative community
  • Learn offline with Skillshare’s app

Why Join Skillshare?

Take award-winning Skillshare Original Classes

Each class has short lessons, hands-on projects

Your membership supports Skillshare teachers

Learn From Anywhere

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

phone

Transcripts

1. Welcome to the "I love Excel" class!: Hi, my name is Jerry, and I am an excellent trainer and this is my XL class. Excel is an amazing tool that can help you save time, understand data, or track things. This training has several videos. Each video is a self-contained lesson with examples. If you'd like to try it out and follow what I'm doing, please download the worksheet with a simple data I am using. I hope you will enjoy this class. And I also believe that the knowledge of Excel will bring you better job or easier and happier days at work. So let's get started. Okay. 2. Before you start: Language and regional differencess in Excel: As you buy online training, participants come from different parts of the world. Let me explain you the differences between different language versions. Before we start, all my videos are made in English versions of Microsoft Excel. However, if you use excel in different language, not just the icons and menus at translated, but also the formulas. For example, here you can see the VLookup function in different languages in the ankle excursion, if I start typing VLookup, as you can see, it is recognized. If I start typing some other versions like German, it's not recognized and it will not work. So if you want to know the name of the formula in the version of we are using for your language. Google it and Google the function and its translation in your language. But of course, if I have an English version and you will have a German version. If I use the VUCA function and send it to you, the function gets automatically translate it to your local version. Another interesting difference is decimal separator. Most of the countries in the world use comma as a decimal separator. So my Excel is set up to accept coma. So for example, if I would like to put 1.2, I put one comma two. And it's accepted as a number. If I will put 1.2, it will not be accepted. And in this case, as you can see, converted to date instead. So if you are living in a country where a comma is used, then your XL is most probably set up to accept coma regardless of the language versions you are using. If you are living in English-speaking countries that you are most probably using the comma. The decimal separator has an impact on formulas. Let's check the VLookup formula here. We can see that there are semi columns dividing the individual inputs. This is what do you use when you have decimal comma set as your separator? If you're living in an English-speaking country and or use decimal point, then most probably you will get coma is individual input separator. So into check if you use your formulas, what kind of what kind of separator does your formula, except if you are using this Insert function way to edit formulas, then there is no difference for you. So as I'm living in a coma country, then you will see, you will see semi-colons here as a divider. But be careful, check your own so that it works for you as well. 3. Inserting, moving and deleting sheets: Hello, welcome. And let's talk about the sheets in excel. File in Excel, it's called a workbook and each individual pages that you can switch here on the bottom left are called sheets or worksheets. If you'd like to create a new sheet, you can easily press that button on the bottom left. New Sheet is created and you can rename it by double-clicking insight that, and let's say I will call it my new sheet. So we can also do is to color code your sheets. You right-click that the top color. And I will put glue. And here I can click on red and green. This will make my tables easily oriental, especially if you have many, many sheets. Next thing you can do is to move the sheets around, but just left-click and halt. You can change the order of the sheets easily. If you'd like to move or copy that cheat, then you can right-click and there is move or copy. Let's say I would like to copy that sheet to a new workbook. So I'll click that. And I can choose here. I can choose actually any other workbook that's currently open. Or as you can see, there are no other open workbooks right now for me. So I will choose new workbook. But it before, because I would like to keep that my new sheet in that workbook. I will select Create a Copy. And right now a new blank workbook was created with a copy of that sheet. I will not need it, so I will close it. Not save it. There are more things you can do with sheets. Let's say delete. I will type something in it. And now I try to delete. So right-click and delete. Let's read carefully what they write here. Microsoft Excel will permanently delete this sheet. Do you want to continue? You click Yes. And the thing is, it's really permanently deleted. So you can't go back by clicking here on the top left. It's impossible. So once you delete a sheet in exile, there is no way how to get it back. You can revert to an older version of the document or previous S8 version, but you cannot simply endure. So be careful about that. If you'd like to duplicate a sheet insights that workbook, there is an easy trick. So let's say I would like to have a second copy of that employee sheet. So I hold Control, left-click and drag and drop it. So holding the Control key, we'll make a copy of that sheet. So I have two of them. So now I will delete them or what I can also do if I have something that I don't want to be visible, but I don't want to delete it either. I can hide that sheet. So I click Hide. It disappeared, but it's still there. I will hide that again. So now I have two hidden sheets in that workbook. These are not accessible easily, but you can revert them by just clicking to any other sheet and click unhide. And you will see a list of hidden sheets. I have two of them. Click OK and click unhide again and I, and the second one. So hiding sheets can make it maybe a little bit more organized. Any of the operations that you can do, you can do with multiple sheets, let's say copying, hiding, moving, or any other operations. If you'd like to select multiple sheets, you can either click on the first one, hold the control key, and then click on the second or third or any other ones that you would like to select. And now you can hide, delete whoever. Or if you'd like to say multiple and let's say there is many of them. You can just click on the first one, hold the Shift key, click on the last one. And now all the sheets are selected. So that's all I know about Sheets. 4. Inserting and deleting rows and columns: In Excel, there are always several ways how to do the same thing. I will show an example, and this is creating new rows. So I have a table here, it's list of business clients. I have here my sales representatives, and here I have different countries. And let's say I would like to create a new row for a new sales rep in the middle. So I will select this row, right-click it and click insert. If you do that, euro will be created above the rotor was just selected. I can do the same thing by just going home and clicking insert and insert cells. Easy. If you would like to use keyboard, you can just use the keyboard shortcut, which is control and plus. So I hold Control and plus and wallah. Neuro is created if you'd like to delete it again, again, for example, right-click and click delete. Or I can use the menu agent them or I can use the keyboard shortcut, Control minus. So if I click contra minus, currently selected, draw will be deleted. If you'd like to create multiple rows, you can just select the same number of rows. Let's say I would like to create three new rows here. So I will left-click on the number 1112 and drag it down to select €3. Now I will click Control plus and three rows are created. Now I will click contra minus because I don't actually need them. So contra minus two again, delete that. The same principle works for columns. So let's say I'll select the column D. And now if I click Insert and you column will be created on the left from the selected column, contra minus, again to the reader that you would like to create multiple columns. I click on the design. Halt. And let's say this time I will select five columns. Click, Insert, and five consecrated. So this is how you work with rows and columns in Excel. 5. Resizing and batch-updating rows and columns: The rows and columns can be easily resized. Let me show you how. Let's say I would like to resize the column C. Click here, select it, then I can easily drag here to make it as wide as I need. If I would like to resize multiple columns at once, I can click on the sea, drag all the way to do column j, n. Now, if I have multiple columns selected, and if I change the width of any of those columns, all the columns will be resized to the same width. That's quite practical, especially if your table is messy. Who like to make it neat? The same goes for rows. So we like to resize arrow. It goes like that. If I looked at precise multiple rows, goes like that. If I would like to resize the columns, let's say to the perfect size, that means everything fits. I can simply double-click. I will show it on the employees sheet. So I have a name of employees in the column a, but as you can see, it's cut, it doesn't fit. If I select the column a, I can, of course, I can manually find out the right width, but I can also double-click. If I double-click, it will automatically select the perfect width where everything fits. So again, if I select all the columns in this table and resize one of them, they will be resized to the same width. There can be practical in some situations. But if I would like to little bit compress the size, I can now just double-click on any of the columns. And all the columns will have the perfect size. 6. Moving and copying rows and columns: There are two ways how to move rows or columns in a sheet. I will show that on the employees sheet. Let's say I have a column called Office, and I would like to move it here between the columns F with great and G with salary. So how most people do it, they will create an empty column here by just pressing control paths or any other way. Copy that, or let's say cut it, control X or you can just do that and pasted here and then delete the empty coal. That's possible. But I will show you a faster way how to do that. Let's go back a little bit. You can actually move the columns, but it needs some precision. First thing, you have to see the lime number one, which we have already selected columns you would like to move or column. And now move your, move your mouse exactly to the point between number one and the letter. So right here. Now if you click and hold with your left mouse, mouse key, you can see that you can easily move it around. So if I drop it here, it will be moved here. But it's not perfect because there is an empty row left. Or also if there would be something in the i row, it will be deleted. So who'd like to, let's say, squeezed its column between the Column F and G. We just like click exactly here. And now hold the Shift key. If you hold the Shift key and hold the mouse, you will see that there is just one line. So if I just like plays the line right here between f and g and drop the mouse and then release the shift key. You will see I moved the column very easily. I will show it once again quickly. In order to move a column, I need to select it. Hold the Shift key, left-click and on here on the edge of the column name, and drag it where I need it. And that's the trick, is always the same works for rows. So let's say we would like to move around. I click it, move our mouse exactly in the point between the row number and the first cell. Click it, hold shift, drag it where I need it and drop it. So this is how we can move rows as well. 7. Hiding rows and columns: Now I will show you how to hide rows and columns, and you can try that with me on the sheet called client's. Let's say I would like to hide the Column F, South Africa. I click it, right-click it and select that. And now it cannot see the column anymore, but you can spot that there is a hidden column. You can see here there is a, E and F is missing. So you can also see there are like two, lets say double lines here. If you would like to show it again and hide it, you can either double-click like towards the, towards the one of the two lines on, towards the line on the right. If you double-click exactly to that spot, it will appear. If you struggled with that. You can also do that. You can select these two things, right-click and unhide. Oh, sorry, now I press height. Lets go back. What I meant is to click and hide. You can easily select multiple columns like that. And the same goes for rose. Again, right-click height. Say same principles. Be careful though. Heading columns is not advisable. If you don't need to hide it, try to avoid it as much as possible because mistakes happen. Look at that, look at John. He is a total number of 380 clients, which is calculated by a sum formula. But if I hide some numbers, let's say South Africa, the number stays the same. So Excel formula also counts with things that you cannot see that are hidden. That can be confusing, especially let's say if you'd print the table, the sand would not add up. So that may confuse people. You can make mistakes in your table, so I suggest not doing it. I will show one more trick. Let's say if you have more columns hidden in your sheet and may be or she this large and you'd like to simply unhide all the columns, then you can do this. Click here on the top left to this triangle. And now all the cones gets selected. Now can Right-click any of the columns and click unhide. Anything you do to multiple columns. If multiple columns are selected, are down to all of them. So if you click unhide, all of those will be unhidden. The same goes for rows, so you cannot say, I will highlight three of them. I was holding control key to select multiple rows. Then right click and hide. The same thing. I can select everything. Then click any of the rows and click and hide, and that's it. So if you, if I would have hidden columns and rows like that, I will hide view. And let's say these three again. And if we would like to ignite everything, I'll have to do it in two steps. So I have to first select everything, click on the first column or any other column, click unhide, and then click on any of the rows and click unhide again. So this is how you show everything in your table. 8. Merging and unmerging cells: Let's talk about merging cells. I will demonstrate it on the sheet called client. Let's say there's a title. And I would like this title to be all over that table in the middle. So what I can do is to select the cells that are looking to merge. And on the Home tab here, click the button, merge and center like that. And now I have only one cell that goes over all these columns and it's centered. So I can see it's like a nice effect. Let's say I can make it, I can put a border around it. And I have a nice marriage. There is another trick. It's called merge across. Not many people know about it. So let's say, let's look at USA and UK, I'll say would merge that sales region and you want to have one cell for both columns in each line. I will delete these numbers. Let's pretend this is the sum of both. And you can manually merge and center one by one, but there'll be time consuming. You can use this little trick. It will select everything. If I would press merge and center, it will be a merge into one big cell, so you don't want to do it, but if you open that menu, there is merge across. So if you click Merge across, you see only columns will be merged, but the rows will stay separated by that. Sort of put it in the center. So this is exactly what you want it. Let's go back few steps to think about merging. Merging can look nice, but I don't recommend it. Try to avoid merging as much as possible. Because if you merge something, it will be much harder for you to let say, filter. Move things around, sort things are great pivot tables. So if you're building an Excel habits, tried to avoid merging. So how to work with titles? Let's say I have this title and I would like to put it in the center. So you can do is let's say I got it and place it in the middle and pasted here and then arranged to the middle slope. It looks more or less the same. Or what I can do is I can just push it little bit. So I have these patents here to push it right here. So it's, if you click it several times, you can move it to everywhere you need. Now if you would like to make the effect Perfect, you can just fill in wide. So it looks like a really one cell, okay, that really maybe to that. So you have the same effect without actually merging cells, which will make your life easier as soon as you learn more advanced stuff in Excel. 9. Freeze panes - Keep the top rows or leftmost columns always visible: Let's move together to the sheet. Got employees have a list of employees here. And the first row I have a name of the columns. But if I go down by scrolling the mouse, you see, I don't see this first-line anymore. The title. It'll be practical to see like what it is, especially with numbers, like what this number means. So it'll be great if you can always see that. Fortunately, that's possible in Excel and it's called freeze panes. You can find this on the View tab here. Click on the View tab. And right here, Freeze Panes. If you're an older version of Excel, you're icon may look a little bit different, but the function that is the same. So it's open it, and you have several options. Let's start with the second one, freestall pro. This is what we want. You click it, and that's it. So if you go down, you will see that the first top row is frozen. But imagine you have a table that has many, many columns where they can have tens of columns and you often scroll to the right. And you would like to see the names always be there. So for that is the third option, Freeze First column. If you now go to the right, you will see that the names are always there, which is quite practical if you have many crops. And what if you would like to, let's say, put the boat first row and the first column in the same time. That gets a little bit more complicated. So in order to show that we need to unfreeze space, which is now the first option and threes. And we have to freeze it manually. So the point of that is you need to stand in a specific location because it depends where you stand. So if you if I stand here, anything that's above this cell will be frozen. Air. Anything which is on the left of that cell will be frozen. I will click that click Freeze Panes. And now we will see first-line stays. And if I go to the right, the first column stays visible. Unfreeze again. Let's try something more. I will create a few more columns, rows here, let's say output employees. And now freezing the top row would not work because on the disk would be frozen. This is what we don't want, so unfreezes it again. And in that situation, same rules applies in need to stand so that everything I would like to be frozen is above or on the left. So let's say I don't want to have these names frozen, so I will stand into a column, but all the rows above will be frozen. So Freeze Panes go down. And that's it. If you would like to cancel it again, I can click and freeze. If I would stand here and free Spain's first line and the first column will be frozen. 10. Using Find and Replace: I have a list of employees here on that sheet and let's say I would like to find my colleague David. I'm not sure about this surname, but let's find him. To open the find function. You can either press Control F, like that or here on the home tab. On the far right. You can click here, find and select and find. Same thing. So I would like to find David. And let's click Find Next, and that's David. Let's see, there's one more. Nothing happens, so there's no more liquid in that field. If you start searching with no, you just one cell selected like that. Let's say I click Find and put. David will find me. That if I keep clicking Find Next I will see more. David's, let's put Jack for example, Jack Glick, 5X. Jack and I will see it will alternate between those two jacks. Faculty find, oh, it will show me a list of all of those. So There's more options. For example, if I would have to column D selected and do the same thing, finding jack, nothing will be found. Because if you have more than one self-selected, let's say a full column or several columns or few lines. The search will only be performed in that selection, so it doesn't work in this time. So also there are other options if you do that. Match case, that means, let's say if I would put lowercase Jack and click this match case, nothing will be found again because there is no Jake with lowercase. If I uncheck it, he doesn't occur. And even though I spelled lowercase, it will be found. One more thing that is called match entire cell contents effect like that. That means find a cell that contains Jack and only jack. Nothing happens because we have the names with the surnames here. But if I would put, let's say IT and find next, will find IT. So match case and much entire contents are quite important. You can also replace, let's say, one of the name of the departments changed from sales to sales and operations. If I click find, I can find the next and then choose to replace like that. Then it will jump to the next one. I can choose where we like to replies or go to find next. If I would like to replace all of them, I will just click Replace all. And that's it through two replacements. Easy. One more trick that you can apply. Let's move to the sales sheet. Where I got here are numbers copied from some other system, let's say Google sheets or from your reporting system or from an ERP system. And we have this strange situation. Sometimes Excel has trouble to identify numbers that has, let's say space as a separator, you know, 100, you know, and it doesn't consider it as a number. It takes it thinks it's a text and keeps it on the left. That's why. So it will be very hard for you to summits or let's say if I would put to some of those. You want to show 500 because 500 does not have any space, let's buy it's recognized as a number. So these are not recognized as a number. So how to fix it? Very simple. I will select that and I will use replace or Control H. I will choose find what space replaced with nothing, and click Replace all. Doesn't work. Why not? I know why. Because sometimes with these numbers, this is not a real space. It's called a non-breaking space or heart space. And even though it looks like as a space, you need to double-click and select and copy. It's very weird in IT staff, but it works like that. So now if I click replace or control age, I will, instead of just typing space, I will just paste this technically different space into that. And click Replace all. While. Ah, maybe you haven't found the situation yet, but the next time that thing happens, you will know how to solve it. Just copy this weird space that cannot be replaced and then just paste it into the replace field. 11. "The Islands Principle" in Microsoft Excel: I would like you to understand an important principle in Excel, which I call the principle of islands. Look at that table. It's a Christmas party budget and I will my sorted by category, by clicking data and AZ. And it's sorted, right? And let's sort it back by price from largest to smallest. Works like a charm. But images there will be an empty line here like that. And now I would like to sorted again by category. Click here. And as you can see, only the top part got sorted. The second part of the table below, the empty row stays the same. Because for Excel, if there is an empty line, it considers these as two separate tables and does not do anything in the other part. Same goes for filtering. Let's say I would like, I will open a filter here, and I would like to only filter the entertainment items. And as you can see, the filter only gets applied to the top part, to the top, Ireland. So in Excel, it's important to keep the table neat and tidy without any empty lines and your life will be much easier. The same goes for columns. So you, there would be an empty column. And now I would like to, let's say, sorted by category again. You see only death part could sorted and that part stay the same. So now the table is messy because it doesn't, doesn't fit. Let's go back. Because again, it considers that as a separate table that has nothing to do with that table. How to fix that? If you need to have some empty lines. You can override this island principle by just selecting the full table. Like that. If I select the full table and then click sort, you see everything is sorted. So the principle of islands is overruled by selection. So if you only stent in one cell excitable sort filter or do any operations with the island, use tending. But if you select everything, you could overrule it. So for example, if I only select that part and sort by price, Rosie, Sorry, by category to make it a little bit easier. Or like that, you will see only the selected part gets sorted. There's a very common mistake that people do in excel while sorting. You realize that when I sorted by a column, I would just like simplest stent into column and press a and Z and it will be sorted by that Column. Or let's click here, sorted by category. But if people select the full column like that price, and now I would like to sort. Now because the island principle is overridden by what's selected here. This will be understood by Excel that you only would like to sort the column c without sorting the rest. You will see what happens. And fortunately or unfortunately, this is so common that Excel will warn you. So if I click that, it doesn't even warn me actually. It just like a sorted by price. And now I have my table messed up again. Let's try with this first column. Do that. Again. You see it's messed up. Sometimes. Xml war new, but be careful about that. So if you want to do work on your island, just quicksort, and that's it. You will have to select the full coal as the principle of islands. So try to avoid any empty rows and columns and your life will be so much easier. That applies to everything in Excel. If your table is organized in a neater way, you will learn that later, then your life in Excel, it'll be so much easier. 12. Sorting rows alphabetically or by size: On the sheet budget, there is a simple budget of a Christmas party, each line representing one item in the budget. And let's talk about sorting. There are several ways how to sort it. Let's say I would like to sort it by category. The easiest way is to click home and find and select or actually sorry, certain filter and Sort a to Z alphabetically like that. And you will see it's sorted. If I would like to sorted, let say by price, I'll simply jump into the other column, click Sort and Filter. And you will see this time it will offer sort smallest to largest or the other way to largest, smallest. You can do the same on the Data tab. It's just like a duplicate of these buttons. So they do the same. If we'd like to sort it by category, I can just simply click that button. That's the easiest sorting. Also, if you have a filter applied, you can sort it by clicking the Filter button. And there's the sort. So any method you use or prefer, it's up to you. That's easy. Now let's move to the sheet called guests lists, and I will show you a little bit harder situation this time. This is also a list of names, but it's hard for me to sorted by surname because in this situation we have always firstname and surname in one cell. That should not be done. But if you receive a list like that, you need to fix it somehow. Luckily in the new versions in Excel starting for 2013, there is a function called flash fill that will help you easily split name and surname into two columns. It uses artificial intelligence. We will show them on the first line how you like to do it and it will follow suit. So let's say I would like to split it here to firstname and last name. So I will simply rewrite how we wanted on the first line. So firstName is Barry and SUR name or last name is or Nelis. Okay. Now I just click on the FirstName. And on the Data tab there is a function on the right called flash fill. Maybe your icon is little bit bigger because Microsoft Office source the icons depending on your screen resolution, but it looks like that there's little flesh. So if you click it, it will automatically fill in all the firstname. And if you click it here, it will automatically fill in the last names. And now standing in the Last Name column, I can easily click a to Z, two sorted by surname. That's easy in that situation. Of course, if there are people with multiple names, you have to fix it. Fix it manually. But this is a way how to do it in that special situation. 13. Sorting rows using multiple columns: I have a list of employees on that sheet called employees. And I would like to sort it by office and then by department. If I click in the column D and press, let's say on the Home tab, press sort alphabetically, sorted by office. But if I click department and sorted by department, it gets re-sorted. So using this simple and z is z to a buttons. It's good if you would like to sort by one column. But if we would like to sort it by office and then by department, I have to use the custom sort of which is that icon. Also on the datatype. There's the same icon right here as the same thing. So it doesn't matter which one you use. So let's click Custom Sort. And so I will sorted by or phase by values a2 x0. That's what we did. And then there is a button called ADH level. Press that. And it will tell us then by so I would say then by department. And let's put one more layer. Let's put then by grade. Okay, that should be fine. What's personal? Ok. And see what happened with Berlin first, obviously. And it's finance IT marketing sales. Then we have Bratislava and again, finance IT, marketing, sales and so on. So even we have 80, you see, we have three IT people. So admin, associate, senior manager. Here again, we have associate and manager here. So we have three layers of sorting. This Custom Sort has one more function. You see that Excel usually recognizes that this first line should stay on top, but sometimes it doesn't and it would sort it and it will stay somewhere in the bottom. I'll try to stimuli that situation. Is that button, my data has headers. So you see it's checked because Excel found out that there is a header, but sometimes it would not. Let's, let's uncheck it to see what happens. First thing you will see, you will not see the names here. This is the first warning sign. And if I sorted like that, what happens is I lost that. It was that first header. It's actually down there, just below m because we sorted by, by the office. So just after an M, New York there is o like office. You see, you don't wanna do that. So in case that happens with a simple sorting, you know that you have to click the Custom Sort and be sure that this button my data has headers is checked so that customers sort, you can sort anything with ease. 14. Sorting rows by color: Sorting in exile is also possible by colors. Let's say I will select several employees and mark them yellow. Maybe this would be the candidates for the employee of the year. And now I would like to sort all the yellow to the top. So how I do it with a custom sort. So I click Home sort and filter Custom Sort. And I have some previous sorting, so I will delete that and I will click, let's sort it by. And I can choose name. That's fine. Instead of this sort on cell values, that means on the name itself, I will choose on cell color on this time. And I would say I would choose yellow on top. So all the people marked yellow will be on top. Okay, that if I had more colors, I will have to put multiple levels of sorting. Let's say I would put some people on orange. That would mean, let's say something else. And I will also make some people marked blue. Let's say these two people. And I would like to sort it so yellow aren't the top than the orange and the blue lines. So let's go to custom cert again. So this will stay and then I will put one more layer. So after this is sorted, so after yellow is on top, I will suggest again by name, and this time the so-called orange is on top. And after that, I certainly by name. Actually it doesn't matter by which column I will sort. In. This case, we have marked the whole line, so all columns have their colors. I will choose name again, and this time I will choose again, so color and I will choose blue. You can see I can also sorted by font color, or an icon. So click OK. And today I have a color sorting Dan. 15. Filtering a spreadsheet in an easy way: This video will show you the basics of filtering. I have a list of people here on the sheet called employees. And I would like to see on this some of them by some criteria. To turn on the filter. Go to the Data tab here and press this icon filter. You will see these filter dropdowns will appear on top of every column. And now, if we would like to filter on the people in the Berlin Office, click here and you see everything is selected. So I can, let's say Deselect All and click Berlin. And that's it. And I want to see people from the Berlin office. You can see here on the left that some of the letters, I mean, numbers are blue. This indicates that not, not all of the lines are visible, that some of them are hidden by filtering because these are not people from Berlin. So this is an indication of filter. If you looked at turned it off, you will just click Clear, Clear filter or just click Select All. Once again, if you have a many offices, let's say, or many items, you can just type in. So we just type in Madrid, just a May and Madrid is filtered. I will turn it off again. Or if I'm searching for some person, let's say I would like to see Billy. And we'll just type the array. And you'll see I have two guys called Billy, and I can see them easily. I can filter a multiple lines as well. So if I would like to see only the managers from Bratislava, I would select Bratislava. And I'll select managers. I can select multiple server, say outlet to see directors as well. So I click and I see the director and managers from Bratislava. You'll see this icon as soon as it has this little cone here, that means that the filter is applied. So you will see it here and here. The rest is unfiltered. Again, you can easily turn it off, or if you press this button, clear filter, filter will disappear. There is one more important thing that you should be aware of. And this is the principle of Ireland because if there'll be an empty line and they were in the table like that. And you just simply click on the top and turn on the filter. It looks the same like before. But there is a difference because this is conserved by excellence at different table. It will not be filtered, will get that. I would like to only see marketing people. But this filter will be applied on the, on the top part of the table. You don't wanna do that. So let's turn off the filter. So there are two ways to solve it. Either remove all the blank rows from your selection. If you cannot, or if it's just a tool, long list or you just don't want to bother. There's a safe way how to do it. Select the full columns like that. Left-click and drag across all the columns in your table click Filter. And this will override the island principles and it will consider it as a whole table regardless or how many empty spaces you have. So now let's try again marketing. And okay, that's it. By the way, if you have empty lines and you would like to get rid of them, filtering is a good way how to do it. Lets say i will create few other empty lines. And there are many empty lines in my document or some other things that they are due on there. And then I can simply filter in any field, just the blanks. That means it is blank. Double-click that, click links. And I will see you see I have four lines, filter decided blank line, so I can select those. Right-click and delete. And you don't have to be afraid that nothing else will got deleted. You just simply click Clear and you have everything there and only the empty rows got deleted. And this is how we work with filters. 16. Advanced filtering: Filtering XL is easy, but you can also do some advanced With that. Let's filter these employees tables. I will turn on the filter. I prefer selecting the full columns to avoid any filter programs is a safer version. You click filter and filter is applied. Mao would say I would like to only see the marketing people. So I would just like select marketing or search the people. That's easy. But what I would like to search something more advanced. And let's say I would like to search only people with a h under 30. So click that I click number, number filters. It will recognize that there are numbers in this line h. I will click that with number filters and you see you have several options here. So let's say greater than, I would say H is greater than 30. And click OK. And I love people over 30. I can also search by, let's say, salary. Or I can say as I would like to see, the top ten highest paid people in the company. So i would click salary number filters, and I would say top ten. And now we can choose either top ten people or maybe top 15 people if I want an either items, that means the number of people or a percent. So I will choose items, I would like to say Top 15, highest earning people. Click okay, and that's it. It's not sorted, so I can now sorted largest, smallest, so okay, these are, has earnings people in the company. You can also work with text filters. For example, I would like to search for people with a surname starting with C. I don't know how useful would that be, but just to demonstrate it. So if I open the filter menu and you see this time it will offer me a text filter because Excel recognizes there is a text in this column. So texts and output either say ICO or I said begins with the. In this case, I would like to find people beginning with c. And this is my list. If I'd like to filter on the people called Jack, I would click Contains, contains, check. Ok. Actually, if I'm only looking for the firstName, it will be better in this case to choose a ends with just to avoid people with a surname like jackley or something like that. So contains. So I'll clear the filter. Last thing I would like to show you, or maybe not last I will see is filtering by date. Here we have people by the date they joined the company. Let's open that. And I can filter by month or date. Let's say I will filter all the people that joined in 2018. That's it. Or I can do even cooler stuff. Look at that with the Date filters. I can all the filter that joined this year, no one. But it is data, it will not be visible, but I can see people the joint last year, which is 2018, while I'm shooting this video. Or you can filter people that joined today or tomorrow that by not be applicable for filter. But let's say you have some transactions or invoices to be paid and you can filter it by invoices that are due, let's say this month. Or you can make it between you would like to see people that are between any we'll put a two dates, people that will join between two different dates. So you can do a lot with filtering. Of course, you can use multiple of these given on the same stem stuff. Let's say I would like to filter out only the people over 50 or under 30. In that case, I would say number filters customFilter. And I would say is greater than or maybe greater or equal to, to make it more fun. 50. And here I choose and, or if they should meet all the criteria or one of those. If I'm looking for people over 50 or under 30, obviously they cannot meet all the conditions. So that means either one of these will be met so are less than or equal to 30. Okay? And that's it. So that's about filtering and have fun working with the tables. 17. Reversing a list: This little trick that I'm about to show you may be useful one day. On the sheet guest list, there is a list of people and I would like to flip that lists to not to start with Mr. Jack and shown but ended so flip the order of maybe is a seating plan or any other reason. There is no function for that in Excel to flip it. So what you can do is write numbers next to it and then sorted by number. So right number one. Double-click, it will fill with ones. But if I click uterine open that instead of copying the number one, I can click film series. And I will have a list of numbers. And then I can simply go home, Sort and Filter and sort largest to smallest, and the list is flipped. Second trick that you might need is flipping rows and columns. Let's select this list once again, you can do it easily by pressing control and a, which selects the current table. Click Copy or control C. I will click here. And then instead of paste, I'll open that. And there is this icon right here. It's called the transpose. So if you click that, you will see that the original table is flipped. The columns and rows are flipped. 18. The most useful keyboard shortcuts: Learning the work faster is quite important, especially if you spend a lot of time in Excel. I like this fun fact that if you save ten minutes every day in a one-year, it will add up to one full working week. So let's get started. I will show you the basic shortcuts that I use every day. Foreshore gut is control a. Control a will select the full table that you are in. If you press Control N, Once again, it will select the full sheet. If I show it on the travel agency where I have two tables next to each other. I press Control a to select that one or Control a to select that one. Again, if I would spend safe control a, once again, it would select the entire sheet. One more thing is control bus that will create empty row or empty column in Excel if you want control and plus. You can do multiple by selecting more and control bus. If you press contra minus, it will delete rows and columns, all the selected rows and columns. That's quite important for working with rows and columns. You can also do this if you press shift and spacebar. The current column, sorry, row gets selected, which will make it the creation of new one. Quite fast. So standard, you don't have to select the column to create a new one. You press shift and Spacebar and then control and plus. And you can easily create new row if you press Control and Spacebar and say I'm studying here, press Control and spacebar. The current column will get selected and then once again, control plus and new column gets created. What I also like is control z. This is a general shortcut for any excel or a Windows application. Let's say I delete few lines by accident and say, oh, I would like to revert it. So I press Control Z and I'm back where I was. That's quite important. What I also do is contrast for saving, you probably know, but I often use F 12, which is on the top right of your keyboard and individual safe as so you can choose a different name, different location for your file. Control N will create a new file and control w will close the current workbook in Excel. And of course, it's important to know the basic shortcuts for working with a clipboard. So Control C for copying and controlled paste for pasting or control X4 cutting. You don't have to use a lot of keyboard shortcuts. But if you know these, I'm sure you will save a couple of minutes. If you would like to learn shortcuts. Don't try to learn everything at once. Make a shortcut of the week and every week focus on mastering and really learning to use one shortcut, making it a habit. 19. Moving fast across the sheet using keyboard: This trick for faster work in Excel is the most important I know. And there's one keyboard shortcut that you learn. You should start with this one. Here for long lists like we have here in the daily sales, it might be a hustle, you know, to always scroll up and down in that list. And so a guided tried, how long would it take if we would press the down key, you know, to reach the very end of Excel. I can tell you it took him 7.5 hours to do that. I will show you a much better way to do that. If you'd like to go to the last row in that table, press Control and the down arrow key on your keyboard. So I hold Control, press down and I jump on the line, in this case 2194, which is the last line in my list. If I press Control and down once again, I will reach the very last row in Excel, which is just over 1 million. I press control up again and up again, and I'm on the top. So this is how we can jump. The same works to the sides. If I press Control and write, xy will move me to the very right of the current table. If I press controller right, once again, at the very end, control left to be back. If i would add more tables on my sheet like we have here in the travel agency. If I press control right. And control right again, it would just jump to the next table. Be careful though, if there will be an empty line for some reason right here. And I will be on the top and press Control down to find out how many rows are there in this list. I would say all 43. Nice. But really there is more. So be careful, empty line for Israel for exile. Sign that there is the other table. It's something else. So if you really want to find out how many, how many lines are there in your table. Just keep pressing the control and down key until you reach the very last row and then press the Control up again. And this is your last row of our table. So I will delete this empty row just after I find it and show you the next key that's important for you. Oh, I meant to delete. It. Assures the next key that's important for you, and it's the Shift key. If you just move your key, heirarchy arouse, you can easily go around your table, but if you also hold the Shift key, the selection will expand like that. So same goes down or we can combine it, you know, to select things that you can maybe delete or do whatever you like with it. That's quite important. You can also combine the control and the shift key. For example, I would like to copy only the dates. So I will stand here, press control shift down. And it would combine the jumping to jump down, and also it would combine the selecting. So I could then just copy and paste it somewhere else. If you would like to move between sheets. For that. There is a shortcut called control and page up and page down. And control page up will go left and control page down, we'll go right. In your list of sheets, this is quite important to move around. A lot of sheets. 20. How to add your favorite icons to a Quick access toolbar: You can see there's currently a large portion of my screen is used by the Excel icons and other elements. But sometimes you need to see as many rows as possible may be when you are presenting the numbers or when you are working with really large tables. So if you would like to save some screen real estate, you can hide these tabs. You can simply do it by double-clicking the tab name. I double-click it here, it will disappear. You can still access those functions. Let's say I would like to align this to the center. So I click Home aligned to the center and it is abused again. So it's kind of this disappearing mode. So if I double-click it again, I can see it again. Sometimes you can double-click, you know, to make it disappear, but just keep the most used icons here. On the top you can see we have here save button. You can see going back and going forward. I also have here this auto save function which is connected to office 365 and allows me to automatically update everything and not pressing the save button. Maybe you don't have it here, maybe yes, but you can also add more icons here. You can do it by just opening that pattern. And that is Customize Quick Access Toolbar. So let's say I can put more icons here. I put echo new, you know, to create a new sheet. I mean, you document. I can put the email button, which if I press, it will automatically open your email client, you know, and send this file to anybody that you choose. You can click the Print button if you like, or any other things. I especially recommend putting the sorting sorting buttons because you use them quite often, or at least I do. If you want to see more, you can click More Commands. And then you can choose out of many other popular commands like let's say adding or removing the filter. Or you can choose anything you want. Let's say I would like to see something from the Data tab, or let's say PivotTable. So go to Insert tab and I will see there is an Insert PivotTable somewhere here. So I can add any other icons that I would like to do here. Is it PivotTable? I put Bill table, and that's it. You can also at macros or any other things that you would like to do. You can also make it unique for every recommend, maybe for some document you print every day. And they would like to have this button, sprinting button here visible all the time and for the other documents, not so often. So you can do, if you open that, click More Commands, you will see that this is the list of your Quick Access Toolbar. And you see it's for all the comments. But if you want, you can, You can choose it for that particular recommend you not to put some documents that say if I put the filter button here, it will be only visible when I open this document. So this is how we can customize quick access for this, for this for this line of commands. I usually like to keep this one open as well as this one on top. By the way. One more last trick in this video. If you would like to quickly flip through these tabs, you can simply roll your mouse wheel. If I'm standing in the home and I'm not the clicking anywhere, I'm just standing here and if I roll my mouse, my mouse wheel, I can simply move around between these tabs. You know, maybe I use it when I looking for some icon that I forget where it is. You know, I'm still learning with Excel even after all these years. So quick tips to make her life maybe a little bit easier. 21. Formatting basics: Changing text color, borders, making two cells look the same: When we speak about formatting, we mean how the table looks. In this video, I will show you some basic tricks. I'm sure you know many of them, but I'm also sure we'll learn something new. First, let's look at this table. Sales report. This is easy table. Each column is one month, and each row here is one branch of our French operations and we have a sales. How many books we sold in each month, in each branch, there is some total column and there is some Total Row, vice versa, and there is some average column. So first thing I'm gonna do is to remove all the formatting and we will start with Scratch. To do that, I will click the triangle here on the top to select everything. And here on the home tab, there is this clear function. If you open it, there's clear all though, delete all the data as well. We will also do this clear formats. It will queue data, but make it plain simple, right? The first thing, I will have a color that I can click this button. You see excises, TPP colors, some basic colors that match each other. They're shades to make it easier for you to make it beautiful. Also, these theme colors can be changed maybe to match your company profile or change it later easily. I will use a standard core. I will click this one and make the font, right? And bought like that. That's the formatting. Here. I will make this bald, it's easy. You can also use Control B. I like to use that to make the font bold. Now let's look at the numbers. They are not very legible because there is no space separator at the moment. Let's talk about numbers. Excel stores numbers in a very plain format, like a computer number, which you can see here in the formula bar. But it can make it nicer for you if you want. First thing you would like to add would be, let's say Decimal points. For that we have a buttons here on the home, home button. Like that. You know, if you would like to have it with decimal points with two or less as you want. In that case, you would not make much sense. Here we can add the currency. If you click that button, it will put the default currency. You know what now happens? You see this crosses DID happens because the amount cannot fit into the cell. It's just too large with the currency symbol. So what we can do is to make it larger by just double-clicking here. And now it's better. You see it put me chick grounds because this is my default currency setup for this computer. For you, it might be a different thing when you click here. But if you open that, you can see you have some other currencies, some Euro dollars, pounds. And if you want, you can click more for more or currencies. I will go back and I will not put these similar things. Actually, currency doesn't make sense for us because these are pieces. These are not money, but pieces. So I will, I can, I can open that and we have, again put like a general number like that. But the disadvantage, because there is no space separator. So if you'd like, you can go to more number of formats to open that table. And you see this is, again, you can choose decimal places. You can choose the 1000 separator, which is a space for me. And you can also see how the negative numbers would look like. If you would like to have them read, You can choose it here. Again as these items salt. I don't think I'll have any red numbers here, but I will do it like that. So that will be what I needed. So the next thing I would like to show, as well as this copy format for the Format Painter. Let's say I would have like a new table somewhere here. Let's say, let's say January, february. Some simple table here. And I would like to have it the same look and feel like that cell I can go again and bated again, but it's quite painful. Or I can just use this, you know, to apply the same format. First you click to the cell, which you would like to copy the format than you click this thing. And selectable would like to put it like here. And you can see this will look the same, say works for numbers. Let's say I will have some numbers here. And again, I would like to have the decimal separator. I click here, click there. And it's easily formatted exactly like we have. So there was like a click quick intro to formatting. I will just clear all here. With this eraser button. I will quickly it clear all. And yeah, that's the very basic you need to know. 22. Adjusting text horizontally and vertically, changing the text direction: This video will be about alignment, or in other words, how is the text or number of placed inside a cell? Exile has this logic that if you input a text, hello, it will stay on the left of the cell. But if you input a number, it will stay on the right of the cell. It makes sense, but you can adjust it and change it how you want. Let's say I would like to fix something. In this header. I can align it to the center. So this is, this is this alignment. And also if this cell is, let's say this rho is wider. You can, I have it on the bottom here, but I can also press this button, you know, to keep it on the middle or on the top. It depends. What do you need? What you also can do with formatting is changed the direction with the text. I will make this smaller by double-clicking. But this table is quite a wide, let's say I would like to compress it a little bit. And for that, I can make change in the x direction of the header. If you click that button, you can make it angle like that. If you click that, you will see I have these numbers angle lay that especially practical, if the numbers would be a little bit smaller. You can see now, I can compress the table a little bit to fit more columns. What I can also do is make the text, the text up. That this would be like very, very compressed if that's needed, maybe even number will be short too, they would make more sense. Or there are some other other ways how to do it takes down. I don't know if it makes much sense. Are like that. Maybe there'll be nice as well. If you are not turning it off, just click it again. And that's it for formatting. So horizontal formatting, vertical formatting, or you can make the text and the other direction you want. 23. Adding multiple lines of text in one cell: Here are the shield called task list. I have a template for trekking tasks, maybe on a project or in your team. Feel free to use this template for your own use. But before that, I would like to show you how to work better with that. What we see as a simple task list with the name of the task owner who should do it was the deadline. And what are the, what are the nodes and what is the status if it's a 100% down or 50% down or no, Danielle, I had these three tasks here and we have to fix some problems. You can see that suggests three possible locations for the Christmas party. That's OK. But if you look at the second task, fifth, the, you will see that it doesn't fit. What exile does is if it doesn't fit, it will automatically cut it. If it's not a number. If it's a number, if we just put all the crosses, if it's not, it will just like cut it but we want don't want to do it. If you would like to revert this. If you would like a cell, you know, to split the text to multiple lines automatically, you can turn it on by this button Wrap Text. It'll make sense for us to enable it in the entire B column. So I will say the B column and click wrap text. And now I see I have a text in multiple lines, but in that case, and we need to fix it to make it loop either I can double-click on the line to make it fit the entire thing. And here, once again. So wrapping the text, it's easier. Again, you can fix the alignment in that you see, you can choose which looks better. I would personally maybe choose to put the name here on the top. If it's a multi-line, it looks a little bit better. What you can see here as well is that this one is a multiline item. You can do it by pressing LEFT_OUT and enter. So if you'd like to in one cell, put more than one thing, I would say like book. And I oppress left out and enter because f outrageous enter, it will just close the cell. So if I say like book, I don't remember what does their catering DJ and drinks. It looks now that I'm using several lines, but actually if I press enter, it will automatically extend to that foo, that force. Ok. So that's for how to work with this table. You can, if you need more than these lines, you can simply just like copy the last line on or several lines and pasted. Also the format will be pasted in those. So this is just like to see how it works. And in a very practical situation that you can use yourself. 24. Adding cell borders the easy way: In this video, you will learn about borders. You can see that the cells in Excel R divided by this thin grey lines. They are called the grid, but those are not visible when you print the page or converted to PDF. So let's make nice borders around this table. You can click inside it, press Control a to select the full table. And then here on the home, you can go to here and open that and click all borders. And wallah, we have borders. That's quite easy. You can also do other formatting tricks. You can make a thick line, maybe just above the total line. You can do it by selecting this row or this line of the table just above the line, and open it again and you have this thick bottom border choice. I do it quite often. Or if I would like to select the total, I can choose this thick outside borders. That it looks nice and neat. You can also choose some other types of borders, let's say dotted lines or something like that. I will show you how to do it. You go to line style. Now we can choose maybe a dotted line or a dashed line like that. So I will choose this fun. Now Excel is in the drawing mode that will allow me to draw it manually, like that. You know, I don't want to do it. So if you press escape, you go out of the drawing mode, but the style is still remembered. So if you now do the same, so select everything, go to all borders. This time you see the borders will be dotted. You can combine these task when you now just change, change style two, let's say regular line, and then escape again and select the table again. And you can make, for example, outside border. So now the outside of the table is single-line. Insight is the dashed line. And now we can make, let's say the tick outside borders. So you can play around quite a while to make a nice table like that. To make it look even more professional. You can disable the grid, these thin grey lines. You do it by going to View here on the top. And here's what it should be shown. So you can uncheck the grid lines so you can check it. You will see that data's appear and it looks like a PDF or rocket printout, which looks a little bit more professional. If you sent table to somebody. Here, you can hide some other stuff if you uncheck this, also the headings will appear. That makes using Excel harder because we have the heading. You cannot select rows or columns, but when is the finished report to send that makes it neater? Or you can uncheck the formula bar here if you don't like it as well. And the new double-click this, and you are in like a full presentation mode of the table. So let's, let's take it back. So double-click. I like to keep the formula bar and headings. And if you would like to go back with the grid lines, you can do. But if you're finished, you can keep it like that. 25. Add cell borders automatically: There is a simple trick how to make borders around table in a simple way. Let's say if you have some idea how to make some sophisticated borders and you don't like to play around with these left, right, top. There is no other choice. If you go down here, has a choice called draw borders. So if you select that, now, your cursor will change to a pencil and then you can draw the border around the table and thereby you want, you can select stuff like that or you can select the full table. And aware, you would let you can customize your design. I just made a mistake. So let's go back. I can maybe with this as well. So I can draw borders easily like that. It's usually easier than just doing manually. If you would like to. Also, there is a second tool called draw border grid like that. And you can, oops. You can draw borders like that and they can draw empty borders if you like. It's quite fun. The third tool here is the eraser. If you click that you can erase any borders that you do, does my mistake or we don't want anymore. So the draw our abilities. Do it like that. Again, for the draw, we can choose a different line core or say I try to draw a red line and line style. Let's say as switch EBIT to draw border, I choose read line and I'll make, let's say thick red line to, let's say select this total. So I would just like to that, oh, sometimes I make mistakes. You know, it needs some precision. So that's a little like that or like just like make this number, right? So you can customize it, customize it quite easily. As you want with these things. Again, you can switch off the grid lines by going to view grid lines. And again, you have a beautiful table. Or you can also do with drawing the border is you can make a diagonal things let's say. In Nice, in February the store was closed. And I would like to cross it out. You can do it by drawing borders. If you could draw border, let's say I go back to red line and lines tell beam back to normal. And I would choose, if you carefully click from draw it, draw the line from this corners, you can make it maybe cross it out like that. So I click escape, you know, to go back to normal. And now also if you have these borders, also if you select this alignment angled like that, you see also the borders will be aligned like that in the table. 26. Color cells automatically based on their value - Conditional formating: Now we will talk about conditional formatting. This is a tool for easy data visualization. Let's look at this sales report that shows sales data by month and by branch. And I would like to analyse it. I would like to see where we are getting above some goal or where we are getting very low. If you look at these numbers, sometimes is hard to see some trends and important numbers, or high or low outliers, but you can just select it. Let's say I'm interested in all the months where we sold more than 70 thousand books. I can go one by one, you know, and lets say selected manually like that. But it's quite time consuming and inefficient and we're not here to do that. So I'll show a better way. And it's called Conditional Formatting. And maybe name is scary if you haven't used it, but you will soon realize that is super easy. So if I would like to see all the months that the is over 70 thousand, I will select all this data by hand. I need to avoid the total rows and a total column because actually introduced like that, because this will always be over 70 thousand. So I don't wanna do it. Let's say over 70 thousand, that would be considered a great result in our company. So let's make it green. So I go to home and conditional formatting. And I will click highlight cells that are greater than. And it says Format Cells that are greater than 70 thousands, I put 70 thousand and I will mark them green. And okay. So you see it was automatically mark and I can easily see where we reached the goal at this conditional formatting is dynamic. So if a number change, it will also change. Let's say I found all that mess. We made a mistake. So it was actually only 60 thousand eye that you see when I change the number. It's not green anymore. So it remembers this rule. Let's go back in the other way around. If I put 100 thousand year, it gets green. This is quite useful because when you have that kind of tables and reports that you fill with new numbers, you can easily delete all those, fill it with numbers. And again, the same rule will be applied. Let say I also want to select the month with very low sale, let's say under 20 thousand. So I will do it once again. I will put one more rule. I will put this time less than, and I would say Format Cells that are less than 20 thousand. And this time I'll keep it lighter, right? Racket, like it is here. You can select a green, yellow, and red, which is the most common. I can also select, let's say read texts to make it red or red border. No two square it like that. Or custom formats, which will allow me to choose NFL kernel. I want, if I would like to have done this color, that's possible as well, but I will stick with red, which are hereby understand what it is. And if you'd like to remove it, you can just, you can just go to conditional formatting, clear rules. And you can clear rules from the entire sheet or from the selected cells. Also, there is one more thing that I would like to show and it's called curl scales. Sometimes I do it because this is the easiest way to do it. We would like to see the long numbers in high numbers in general, I will select everything here. Go to conditional formatting and color scales. And select this one. Because, because as you can see here, greenness on the top, because it's sales that's good. About average is wide and the lower number is, the redder is as the icon shows. So I will select that and you will see that I have an immediately some feeling about the numbers and I can see the trends. I see the January was not very strong. August was not very strong, but July was a little bit above average. What I can do as well is, again, an KQ clear dose from attire sheets. I also like to choose this one where we're like the value is just like the thickness. The thicker the color is, the more green, the higher the value is. It's like a temperature map, and that's what you can do. 27. Creating color scales and other tricks with conditional formatting: Conditional formatting is a great tool to visualize your data. It allows you to highlight some numbers based on some rules. I will show you some more advanced stuff. Let's say I have this sales numbers and I would like to sell select top ten months with the highest sales. I will select. The cells, go to Home, Conditional Formatting and I will highlight, let's say, top ten best months, and I will mark them green. I would like to also select the bottom ten items. You see you can also select, let's say, the top 10% or top 20% or anything you choose. I will select, let's say, bottom five months with the lowest thing. And I will choose to keep them light red how it's offered here. So this is great about conditional formatting. Let me show you Conditional Formatting and other situations. Let's switch to the employees tab. And I have, let's say the salary here. So I can easily select the full column and I will use the color scale. On salary mats. Maybe I will choose this scale. The greener, the higher or I can choose this one, you know, the green, yellow, and red. So I have the salary that the greener, the higher. So and then I can maybe sorted from the highest to lowest. And you see, I will get a nice rainbow of colors by the salary. What I can also do is to apply conditional formatting on dates. You see I have a date when this, when they do those people are joined, I can apply conditional formatting on, let's say a date occurring like a like a day occurring, let's say yesterday or today. I don't think we have any new joiners yesterday or today or tomorrow. So we can choose this week, last week, last month, etc. So, so this is not my particular useful maybe with these employees, but maybe if you have a table with inverses do or some transactions and you would like to see which invoices are due in, let's say this week, you can do that and it will automatically update. So, so let's say if you open the next week, it will be always up to date. And you can also filter or sort by colors. Then if you would like to manage dose, that's a change it later you go to conditional formatting and manage rules. And you will see all the rules that are in applied for the current selection. Or you can see this worksheet and you can change it. So for example, if they decided to change it to bottom ten, I can edit the rule and I can see I would like to apply to bottom ten. Or I can change the rule to apply to bottom 10%. Let's see how it changes. And put the top 10%. Ok. Press Apply. It didn't change much, but this is how you can adjust and change these rules. 28. Adding icons, arrow, symbols and mini-charts into cells: You can use conditional formatting also to put icons in your tables. Let's see these sales. And I would like to make a, like a traffic light symbol to see if the target was met or not. So I will select these and click on here a conditional formatting on the Home tab and go to Icon Sets. And I can select these shapes. As you can see, I have this icon that shows me if I met the goal or not. Of course if I create it now like that, it will just simply split all these cells into three groups, like the top, 33% will get green, the middle will get yellow or amber, and the lowest gets read. You can change that if you click Conditional Formatting Manage Rules, you can, you can then edit the rule and you can see, you can see like where it will be green. So now it's 67%, top 67%. So I will choose it to, change it to number. And I will say, green will be when we meet the target of 70 thousand books halt. And this one will be when it's over 20 thousand books sold. I can choose also different kinds of icons that will match the situation. But for me, the strophic lights will be fine. I think I can choose even different for each individual set. So I click OK, Apply. And now I have a nice showing of meeting the target or not. I can also choose other icons like these. Growth if it's growing or steady, steady. This takes a little bit time to set up in the Manage Rules to work properly. You have some other indicators, ratings, Harvey Balls, people told me, only thing they miss are the smiley faces. One more thing that you can do. We can try onto total our data bars. These are something like a little charts that are placed directly in the cell like that. Or maybe we can do it like that. So these data bars show that I will, I will just delete few numbers to show you how this average is. Bill Move. It actually doesn't because the average function doesn't count empty cells. I forget about it. So let's put few number of zeros here. And you can see that this chart as a big round of these cells, is reflecting the change. There is one special function that you can use in this type of table, and this is called the sparklines. These are special tiny charts that can be placed directly in a cell. It works like that. I will just click here, go to insert. And it's called sparklines here I will click, let's say coal. I will select the data range from January to December. I will not use total or average, but like those. And I will see you like a little tiny charge is placed directly in the cell. I can drag it down. So this is like a little charts. I can also choose to have different like Lucky line chart or the span. There will be actually not used for that situation. But I can have a line chart or like a column. It's like a tiny trick to visualize your data. 29. Highlighting an entire row when a value reaches a specific value: A lot of people asked me how to highlight a line in a table like that. Let's say I would like to highlight all interns. That means like that. I would like to have interests in a different color, yellow, so that it's clearly visible that these are not full-time employees. You can do it manually, of course, or you can use conditional formatting, which as you know, changes the color a cell based on what's in there. There is a simple version to select the Column F, go to Conditional Formatting, Highlight Cells that are equal to intern. Okay? And make it yellow. You see that this is very easy to do. You see all the interests are highlighted, but people are not satisfied with this solution. They would like to see the full row to be, to be highlighted. So let's do that. I will clear that rule. Let's clear rules from the entire sheet and let's start again. This is little bit more complicated to do as the rule will actually, let's say it will cover dead cell based on what's placed in death cell. So for that, we need to use a formula. I will select the entire table or I can actually select the full columns, so it will be applied also on all future employees. Go to conditional formatting new rule. And I will click use formula to determine which cells to format. First, I will check how it should look like. I said, like yellow background should be fine. And now there's a special formula. Let's say, let's start because we start on the line number one, we need to write a formula for line number one. It will then fill to the other ones. So for the line number one, even though it's a, it's just a header. And there'll never be an interim, but we need to still count with it. It goes like that equals sign for counting F1, which is that cell equals intern in parentheses like that. It looks quite weird, but it's correct. The problem is F1. It would work the same as I should be forced it which only select the F. Think because for other columns, it would, the formula would move and it will change to ED or C or B. So we have to prevent Excel to change. The columns are always look in the column F regardless of the number. This is what we do by adding a dollar sign. If you don't know what that means, that will be explained in some of the other videos, but for now, let's take it as granted. So this is the secret trick, how to make it work. That are, and you will see that the introns are highlighted. So if I have just like make somebody and in turn, you will see he's now highlighted. You can do many rules like that. You can make it maybe to highlight somebody with some salary over some limit or sum over sum h, that's up to you. But it, you need to use a formula to do that. 30. Filling series of numbers: Filling is a tool that makes accelerate. I will show you that on the empty sheet called fail, you can practice with me. If you see that I have selected the cell B2. And if you look closely, you will see that there is always like a, like a blue dot on the bottom right of every cell. This is the filling button. I will show you how it works. Let's say o right number one here. Go back, click into that, and then drag down by clicking left on the filling button and holding and dragging down like that and then dropping. What I got is a line of one's. That's not very impressive because this is actually copying what Excel did is he copied the first cell over and over. Filling, although means that he will continue like a, like a list of numbers. So if you see just after you do this filling, you will see this little thing here on the bottom, right. If you open it, you have several options. Copy cells, that's what happened. Or fill series. If I click fill series, you will see that now I got like a list, like a series of members. I would achieve the same by just writing 12. Selecting both of these. And now Excel will automatically choose filling. If I will still need to copy, I can still select copy. These managed to choose between copy and Phil is only available just after you do that. If you click somewhere else, do something else, you cannot choose any more if you'd like to fill or copy. Filling works also for different sets of numbers. Let's say I would like to have odd or an even numbers. I will click that to four and it would continue the line. Or if I put 101220, Select and fail, I get the line. I think you get the point. The same way you can do with filling. It goes to the right. So if I put number one, go there. And if I drag to the right and select Fill, this is okay, I can do it in the same fashion. Also. Let's say I have something here already. And I would like to, I would like to fill some other numbers, let's say 1020. So like that, if there is something right next to it, like if, if the column right on the left is felt, I don't have to drag it all the way down, but I can only double-click it. And it would automatically fill it until the last filled row in the previous call. Or you can also do is to press Control D. So I select that. If I would select these and press Control D like down, it would copy distinct down. If there would be 20, again, those And I would select those like that. Control D, it actually only copies in this space, but for formulas it would feel to fill to the right, it goes control are it can do not just this, but also dates which we'll cover in another video, but also stuff like Task one or project one. And if I drag it down, I'll get lists like that. This filling gets especially useful when working with functions. Let's go to the sales report here. And I have cells per branch here. I have columns are months, and this, these rows are branches and the total row here is empty. I can easily put an, a sum function. I will explain that again in a different video in more detail. Click ethically the Sam. And now I can fill the same formula to the right, like that easily. Or I will just click the sum here. Click Enter, and then I can fill it down. Or akinesia selected and press Control D, As I said. So filling is a great way how to, how to work in Excel. It works with number lines, formulas, and also calendar dates. 31. Creating timelines easily by days, workdays, months or years: Here on the calendar sheet, I will show you how to use filling to build calendar dates. Let's say I would like to make a list of days beginning of today. There is a great shortcut how to enter today's date and excel, and that's control and semicolon. Press control, semicolon today. And I have today's date. Okay. And now if we would like a list of days, we just click and drag the filling button like here. And you can see I have a list of dates by day. I can do the same to the right. To get a list of days that can be useful if you do some planning calendar or some similar application. There are some advanced versions of that as well. If you drag down there is this little menu that if you open, you can choose different things. First thing, you can fill weekdays, if you click felt weekdays, ten, Saturdays and Sundays are skipped. You can see the line 171820, etcetera, because today is a Saturday. It doesn't really work that well, but then later on it will skip Saturdays and Sundays. So if you click fill months, it will only fill months or the same day, but a different month over and over. Or you can say Phil, years as well. So you can make it easier lines like that. If you would like to make a line of weeks, then you need to find out. So let's find out what is the next what's the next so 24 hours. So so so thoroughly 24th, it would be the next Saturday. And then I select both and I drag it down and I have a list of all Saturdays. As long as I want. There is a second thing to do, but you're going to be date and these are like months and days. For example, I will write January. And I can fill a list of months in a repeating over and over. I don't even have to start with general against terror. It may and go down. And there is a little trick with filling months, because it depends on the original settings of your computer, which language will be supported. And surprisingly, this has nothing to do with the language of your excel or of your windows, but more to the original settings where your computer is placed. Because my computer is placed in Prague, Czech Republic. I didn't have these. Normally there. I had checked pans. So you probably also have months in your local language. The same goes for weekdays. I will do it in my language. And I can fill any line. I don't even have to start with the first day or the first month. I can also do shortcuts of weekdays, like Paul goes for Wednesday, I think in English is MON man for Monday. And also you can have a shortcuts of months. In English it would be like Jen, FAP, et cetera, like the three-letter abbreviation. In my language, it doesn't exist. So instead, I will have a local roman numerals for four months. So if I put like, I like number one, January, I can fill it in. I will get the roman numerals, but on their own until 12 and it starts with one again. So this is what you can easily do with days. With days, you can also choose on the workdays, but not anymore. You have to do it just after you filled in. So if I fill it in like that, I can choose Fill weekdays and Saturdays and Sundays are omitted. You can try with your own language. If you would like to add some other language like me, like I put two English names to be able to fill it in because I worked a lot with English documents. Oops. I will show you where to do it. You go to File Options. Here on the bottom. Advanced. You go all the way down. And here is edit, custom lists. Click that. And you will see the listed are currently available for you. You see these are these that I mentioned in my language, and this is I added myself. If I click Add, I can actually create new list and I can make a list of, let's say, my colleague, John Paul, Ringo, and George. These are my colleagues that I fill in all the time. I click OK. And OK again. And next time I right John, I can easily fill in the line over and over or I can fill it to the bottom. So as I said, filling is quite useful for calendars or any kind of building these blocks. Or maybe you can make her customer lists for your products, your colleagues, or calendar dates in different languages anytime you wish. 32. How formulas work in Excel: Now I will help you to understand these formulas really well. And I will start with the very basics. I will demonstrate it on the sheet Formulas, which is empty. Now, I'll put two numbers in 12. And now I would like to make a, some of these are, I would like to add those. So the way how formulas work in Excel is you equal sign. This is for Excel, like a sign that there will be a calculation that you are not going to input a number, but you would like to input some formal. And I can sum up those, I can click to disband. And you will see it will automatically fill in the name of the cell, which is b two in this case. Then I will press plus and B3. You see again, automatically filled. I press enter. And of course the result is three. How for most work is if you change the numbers, anytime the formula gets automatically recalculated. On the first side, you don't see if there is a formula or a video just like type and number of seven. But you can see here in the formula bar. And so this is what's actually hiding behind this number. If you would like to edit or change the formula, can double-click it and your back and you can maybe adjust something. You can also type the formula directly into this formula bars, a lot of people do that. Click this plus click. Same, same principle. Or what you can also do is you can type in the cell names. So if I press b2 plus b3, works exactly the same. So no change here. If I would like to do some other operations. Minus, that will be easily. If you'd like to do multiplication. That for that you need a star sign. This is the multiplication sign, excel and division. That's quite easy. That's this sign. Okay? And this is how you divide numbers in Excel. Now let's talk about functions. Functions are also formulas, but special ones. Imagine a function like a little box where you put something in and something else comes out of it like a machine. I will show it with a sum formula. To sum up these numbers, I can easily create some formula by going home. And all the way to the right, there is this some button that will automatically add some function. Let's click that. And exile. Understood that I'm about to sum these two functions. Currently. This is the same result because it's just two numbers. Like if I would just add them up. With more numbers, it's more efficient to use the sum functions. As you can see, every function in Excel has a name. There is more than 400 functions in Excel, but to be really good in Excel, you need maybe 20 of them. Not even that. Maybe if you know ten functions that all unit. So there's always a name of the function, and then there are the brackets, parenthesis, and there is the inputs, every function, or most of the functions, they have inputs. So in that case, the input of the function sum is B2 dot B3. The column that it means that is arranged summits make mega some from B2 until up to B3. Press enter. I get the same result. If we would like to change the exchange it, I double-click it and I can changed. Of course I can combine sums and calculations or functions and calculations. I can press plus one or like that, and I get a little bit of higher. So every function in Excel works like that. Some function though, do not have any inputs. I will show an example of that function. This is a function called today and the only output of the function is today's date. So I will type it like that equals sign to day. Today's sorry. Even though this function does not have any inputs, I still need to put these brackets here like that. And tr. And by the way, this is a nice function to put today's date, and the date will automatically change every time you open Excel. Again, you will see a date, but if you check the formula bar, you can see where the formula comes from. There are formulas that have more than one inputs. Let's try something. I put hello, and there is a formula that can take one sign from the left. The formula is called lefts or Eco, left. Like that would take me just one letter from the left and remove everything else that can be useful in various business situations. But I can also do right, left. And then I make a semicolon, and then I will put number of characters. So in this tape is, in this time, it will give me two letters from the left. Let's see some other things. So if I right ICA, I get functions offered. So as soon as I write L, L E, I will see the left function here. So you can double-click that to autofill, or you can press the top key on your keyboard just above the Caps Lock to auto-fill that. You get like a little help here with the function. You will see how many inputs needs to be inputted. Lucy, left function needs a text and the number of characters. You see, it's in these special brackets that show that this is not necessarily so good function can have only one input, just text, or it can have two inputs, the text and the number of characters. If you don't put anything here, it will take just one. So I put three this time. And that's it. You see, it will show you what are you supposed to input right now. You can also click on that and a help file will open so you can read more about it if you double-click on this sign of the function. There are more ways how to input functions, not just like that, but you can also click that button, Insert function, which is a little bit easier sometimes for the beggining to enter functions, I click that. And you have a list of functions, all this 400 plus functions in Excel, and you can use them. So for the left functions, I would just type left to search for it. Click Go. And I see left. I see description where the function does. This is quite useful, especially if you're not sure if it's the right function, I click OK and I get something like a wizard that will help me with building the function. So the text is this, and number of characters specifies how many characters you want to left to extract. So put to. And you see, it's building the function for me, it's right here. Okay? And I have the function, once again, I can double-click, can change the function or do anything I need with them. So these are basic terms in Excel. Formulas are anything that starts with an E cosign and the calculations, it can be simple multiplication, division, et cetera. And functions are these little boxes that have visual inputs. You put something in then and it will give you something else. 33. How to insert and edit formulas the correct way: This table called sales report is a great opportunity to learn some basics of formulas. I have months in every column and branches in every row. And I would like to put a total row here To sum up every month. And then later on, a total column to some of the full year. And later on we'll put average. There are more ways how to input the formula or the function here in the total row. So every time impulse function in situation like that, I always do it for the first column and row and then use the fill function to fill it to the other rows. The easiest way to input a formula here, to go home. And on the very right there is the automatic sum. You just click the button and form wise there is like that. This works though in a very easy situations like the divinities need to put some under a list of numbers. In some other more complicated situations, you need to enter it manually. So it's the same like if I would put equal sign some and select those S0, the output is exactly the same. Now if I would like to fill it into other months, I simply click and drag this Fill button. And today I have a sum in every single column. The sum will automatically move its inputs. When you move it one, right? It will take the February data. You can simply see it if you double-click this function, you will see now the range where it takes the data from. Let's do the same in the total column and just click some. And that's it. And I can fill it in, or in this case, I can actually double-click and it gets filled automatically. Not really. So any two Purdue total of everything right here. So this is the total number of books that could be sold. You see this is a sum function. Also under this button you'll find also average button count, maximum and the minimum. So like the basic, very basic functions, but in this time, average will fail. It will show you how. Click that. And the average function is called average. And it will give you an average. But the automatic function was not quite correct. Can you see it? Yes, because the total column is also included. So the average would make no sense at all. So in that case, what you can do is either you can type it manually, an average, select that, and then select the numbers. And press close it and press Enter or move it a little bit. Or you can still use the autosome click average and then adjusted the input either in the mouse by just like dragging it and you know, resizing it like that. Or you can just click inside and retype it. This. Column o to the column N. And you will see, now it's correct. Double-click again and once again. And I have the average, this is the monthly average for each individual brand branch and this is the monthly average for all sales. So this is how to input functions. I would like to put one more thing, and this is how to work with percentages. In Excel. Percentages are done in a very special way. So if you put, you can input a percentage, say 15%, you can put it like that, 50%. But what actually excel remembers in that case is not 50%. I will change it back to number, but 0.5. so all the percentages in Excel are stored like a decimal numbers where one is 100%. So you can either put 0.05. and then change the format. So it's shown like a percentage. Or you can input 50% as we did before. What you cannot do is put 50 and then try to convert it to percentages like that because it will be 5% thousand. So remember every time you count percentages, you need to, you need to do that. So, so decimal points, the same goes for a year for the total. So I would like to see what is the split of the total sales per different branch. By the way, it'll be a good idea to freeze panes. So I will click here to freeze everything on the top than everything on the left view space so that I can always see the name of the branch. And then I will see like percentages. And I will use the fill format filled to make it nicer Cu and now I would like to know and double-click to open that. And i would likely what percentage of total sales was done in Paris actually here. So I will put total divided by, sorry, Paris divided by total. There'll be like a fraction, like a decimal point, like that, 0.17. So I can easily change it to percentage, or if I would like to 17.2, if you'd like to put more decimal points. Actually, if you need to, if you would like to fill this formula, in this case, you need to press F4 to help to give, give it an absolute reference. This dollar would make sure that it will always take the total, even though we will go line by line. You can see I can fill it in. And I have percentages. If I would wouldn't do that. The formula would still work on the first line, but would not work on the other lights. Why not double-click it? You see that the total input, the cell would move with each row once again. So we don't want to do that. That's why you put the dollar sign by pressing the F4 button here. And double-click and one more. And we used very simple formulas like some to make this very neat table. 34. Using absolute and relative referencing - using the dollar signs (=$A$1): The concept of absolute and relative reference sounds horrible when I say it like that. But it's quite important to know when you work with any formulas or functions ghost-like that. Anytime you link something in Excel or may be here. This is the simplest formula ever. It will just read whatever is in the air in here and write it down there. If that would change to something, that would change as well. And if I drag it, if I fill it down, I will get a list. So every time the formula is felt, one row below, the input will also change. So XOR remembers every input relatively. So. It doesn't actually remember take what's a, whatever is in B6, but it remembers take whatever is like 1-2-3, 4-5-6, seven rows above me. So that's why it's easy to fill. If you fill it to the right, you see it would take these numbers. Although the formatting is not converted. That's quite easy. If you would like to turn off the default behavior. And you only want to have Paris filled. You can just like put these dollar signs. If we press a four, you will see this dollar signs appear here. They actually mean that they will say that two of them, one of them is blocking the column to be changed, and one of them is blocking the row to be changed. So anytime I feel either down or to the right, it's always Paris. So as I said, there are two ways how to do that. You can either press for button or we can just like manually type them in. There'll be a little bit time-consuming part touristy. You can click in, make it a dollar sign and make the dawson again. And that's the same result. But there is also advanced way, which is used in advanced formulas when you only have one door sign, I will show you that, say I will only block the line like that. So I will, I won't, I will tell them like take anything that's in line six in the same column like I am, right? So B, so the column can change but the room never. So if I drag it down, It's always Paris. But if I drag it to the right, it will take the same stuff from the same row, but from a different column. You can actually change those mixed references. It's called Mixed reference by just keep pressing the afore. If I press the effort for the first time, I get the absolute reference. If I press it for the second time, I get only the row fixed. And if I press for the third time, I get on the, the column fixed. And in this case, you can try to guess what will happen. If I fill it down. I'll get results from like the following rows. But if I fill it to the right, I always get stuff from the Roby because Ruby is fixed here by the, that concept is extremely important to know. And if you know that you can call yourself like an intermediate extra user, I really think it's quite useful and everybody should know about it. 35. Using basic functions - SUM, AVERAGE: This sheet budget has a table with a great opportunity to learn some formulas, is a Christmas party budget and each line is one item and there is a price. And we would like to know something about it. So let's start the easiest way how to put formula or actually, let's freeze the paints. Let's go to View. Click states than here to fix the first three rows. Freeze Panes so we can scroll down and still see the name of the categories. So if I would like to know what's the total, I can just click home and put the automatic sum here. See it's automatic, it's fine. And we have the total. You see this time it shows me its chick grounds, even though it's euros. You know, exile is not very good with working with currencies for them. It's just a number, no, and the currency is just like a format. So you can just simply change it. Here. It depends. Maybe you're as your default currency. If not, you can open it and newer certainly find it here between the most common currencies, okay? Now we have two decimal points. I will hide them like that. But still, this is not very practical because if I, if I'm about to add a new line with the new item, you see the formula is not, is not counting with those. So that can be a source of potential problems. So let's delete that. I will show you a better way where to put the formula. You can theoretically put the formula on the top here. So let's see if there's some function will work automatically. It doesn't, so you have to show them where the number is, so we'll select that. This is also impractical because if we add new lines, that will be No, we still need to change the sum. What I often do in these situations is I would just like to make this number higher. So, so instead of C2H4, I'll put C 2240 or pull one more 0, so I have some enough slack to add new items. And again, interchange to Europe. So this is one way how to do it. The most elegant way how to do it, in my opinion, is just to put it here, like here, some. And just put it here, Sam. And select the full column. You see in this time, the exogenous enters c dot c. That's like assigned the full column C. It doesn't matter that there are empty lines or that there is a price. As soon as there is a no table under it, is there would not be any other budget under it which I would not recommend. Anyway, then that's fine. So I could press that. Changed the format again. You can use the Format Painter. And I have an elegant formula which will also include anything that I will add later on. As you can see, it's real quiet easily. I will do the same for average. This time I can, again open that. Or I can put a marriage like that. Again, the Euro program. So maybe I should select the full column and select euros. But before I do, I would like to show something about two decimal places. Normally would use rounding to round this number to the full euros. But in Excel actually you don't have to round. You can just highlight these by pressing this Decrease Decimal button here. Because if you do once and twice, it will actually not just eat the number, it will actually run the numbers. So you can see it's 67.7 and if I hide it, it's 68, you know, so did the correct rounding. But there is a no running error because the number which is inside keeps the same. So if you work with it later, you will not get any running error. Another function that you can use is for example, count, count. And I would actually count these things. It function count only comes numbers. So I will know there are 21 items in the list. One more I can use minimum, maximum, but I would not recommend actually to put it here because it will be on the same line with a stylist here. So if I would put function here and then decide remove the stylist, I would still have a problem. So the most elegant way here would be to create one more column or two of them. I will cut and pasted here, I will put the maximum, the most expensive item, cheapest item. I will already pre format everything, the whole column, €2, the decimals. And again, I can use the max function. That's the most expensive. And I can use the min function to find the cheapest. I would like to also use which percentage of budgets are all these things? For that, for percentages, I would make this thing divided by the sum. But in order to be able to fill it down, I need to use the absolute reference to, to press for. So it will always take the sum from the one cell, even though we will move with this motto other lines. So it shows 0.13. This is how Excel Sean's percentages like decimals. So I press stat to have 14% and double-click to fill it in. I will see which part of portion of budget is that maybe I can sort it by the tallest. Ok. And now I messed my full table. So let's go back. I would like to remind you of principle of islands in Excel. Excel things, whatever isn't connected. It is a one table, so it considers this like a one weird table. So to be even more elegant in Excel, what we need to do is to do that. And then we put the percentage based format. And we have a beautiful table. And now we can sort it and XML automatically know what to do, right? I can make it small and maybe to see it better. And this was another example how to work with formulas in Excel. 36. Case study: Creating a simple spreadsheet with calculations: The book sales sheet contains a great Stable to practice formulas in Excel. So let's do that. What we got here is Harry Potter book sales. By month. They'll see DSR month, let's say last year. And this is the volume. So how many books are sold? And we need to fill in the rest of the table. And I will show you how total sales that easy, that a sum. Let's go home and click the autosome. Average monthly sales. I would use the average function. But this time, again, this function doesn't work properly because it tries to make an average. I'll also out of the total sales. So I need to adjust it a little bit like that, so it does not include the total sales. That's the average I will formatted later. And average weaker sales. How would we talk with average weekly sales? There is no functions for that, but we can use a simple formula, simple calculation. Let's simplify and assume that every year has 52 weeks. So we just take total sales and divide it by 52. These are total sales. And because our numbers are not very neat, we can just select those and choose a number. But because I also want to have a decimal separator, I click More Number Formats. Number. I want to have 0 decimal places and a thousand separator like that to have a space between each thousands. Okay, that's much better. Now let's calculate the side sales in air. We have a volume, we have a book price in Europe, it's €19.9. So we can do it by multiplying these two things. But again, we must not forget that. We need to fix that with a four. We need to press F4 in order to fill in these dollar signs that will keep that in place. It will always look into that cell. Presenter. Double-click and lets it. Format is easy. I open that, choose euro and Dewey decimal points. Do the sales in USD, in US dollars. I need to multiply it again by a conversion rates. It's very similar case here. So take the sales in Europe divided by the conversion rate. And shows me the result is again in Euro, but it's not. We know that the signs of currencies are just the format is for XOR is the same information like if the cell is green or red. It doesn't count with its XOR actually only counts with numbers. So it's like an olive visual stuff, so we can change it to dollars like that. Id numbers again, double-click. And it doesn't work. Why not? Think for yourself? You got that? I will show again. We forgot to fix that. And instead of always looking for the conversion rates with every line, this red input goes always lower and lower. So we need to go back here. Press F4. That's fixed on the first line. And then we have to double-click to fill the correct formula down to the other line. And to celebrate our success with building this table that contains basically all the essential stuff. We will make a chart. You just select that, not the total sales, but just like month and volume. Go to insert and select charts. And we have a nice chart, more org charts later. This is just like a sample at. This is a short case. If you know how to build that. This is a basic, basic building block of all Excel formulas. If you know how to do that, you can calculate anything in Excel. So congratulations for advancing your skills in Excel. 37. How date works in Excel: Very often you have to work with dates in Excel. If you now to do it, your life will be so much easier. I'll show you how to use them right now. Let's start on the sheeted date, which is empty right now, but I will put a date into this cell, let's say first of January 2013. Press Enter. And as you can see, the date got aligned to the right side of the cell. And if I click in it, I will see that here in the number format, it says date. That means that Excel automatically recognize this as a data entry. I can also input the date as that. And it will convert it automatically to my default format of my computer. Not all devs are accepted in your computer. It very much depends on the original settings, how would a computer is set? So maybe inputting a date like I do with the dots doesn't work for you. Maybe you have to use to use slashes like that. You need to find out how to do it. So for example, if I put the date like just that, the data will stay in the left side and it doesn't say date if saves General, that means that exile didn't recognize it as a date and tree and will not be able to work with it properly. So I'll delete that and let's stay with the states. I'll put, let's say second January here to show you something. Because if I change the format from date to something else to a number, I will see a weird looking number, 40 thousand something. If I change the next state as well. The number, I will see another we're looking number, but this time 1 larger. So what is it? This is actually a format how actually XL stores to date? Because for computers, it's really hard to work with the days and months with different numbers of days and leap years and that kind of stuff. So actually XL remembers dates as a serial number. And I will show you where it starts. What is the day number one. Let's do the other way around. Let's switch it back to the date format. And you will see that is the first of January 1900. So this is the day number one for Excel. That's quite important to know when you are, when you will be working with dates, let's say adding, subtracting, and calculating with dates. You can change the format of your date. As I told you, I input a date like death and it's automatically, oh, now it stays as a number because I switch it to number. So let me put it here. You will see you have a short date and long date option of your format here. It can go like that. Or with a long date. It will also show me. And now it's too large for the cell to impulse. I double-click. Now you'll see it shows me in my language the name, like it's Tuesday, first of January 2019. You see even though my Excel is in English, the date states in my local language based on the setting of my computer and it will be the same for you. But if I if let's say your computer is set to English dates and I will send you this XML file. You will see that date in your local language. So Excel is smart to do that. So you have a long date and short dates, but if you need something special, you can go to more number formats. And now you have more formats to choose. Maybe you need to make a short format of the year, like very like that. Like a sample or with Roman numerals, or like a very long date. Or you can see, you can change the language as well. You can see my language is in check. So if I switch it to English, if I find English year, I can choose to Wednesday 14 of March, 2 thousand civil see the date is in English. So I can choose my own or I can choose Custom. And oh, that looks scary. But this is actually a template. I will show you how to use it. If I delete that. And let's say I would like to see have a date that dot m, m dot IRR, IRR. This is like a template for how the date should look like. If I put just two hours, you know, I will have a short date. I want to keep the long date. I 1D but if I changed it to Sir, I have two d's. If I change it to 1D, the 0 on the beginning of the data will disappear. Same goes for a month. If I just keep one m, I will only have a single digit month for the first nine months. If I put three M's, I will see the Roman numeral of the date. Your language is set, let's say to English, you will see like Jen, like the three-letter abbreviation of that month. Depends on the language. In the Jack language, there is no three-letter abbreviation. That's why we have removed numerals. Also, what's I have Rs here, but maybe in your English setting you need to use wise, like a year doesn't work for me right now. But if ours don't work for you, it might be years or anything other in your language. So this is how you can set up your default to take. If you put more than one D, let's say 123, I'll get a shortcut of the day of a week, which is Tuesday. The forties I'll get the full length of the weekday. The same goes for month. If I put three M's, as I told you, is the short version or Roman. If I put four m, I get the full name of the month. So this is how we can play with the custom dates. But I will keep it with the original date like that, which is where i like. So that's about date. You need to remember that the how data is shown. You can influence easily into number format and need to remember that actual Excel works with a number which will be important with working with calendars. 38. Adding a todays date that updates automatically with TODAY function: If you would like to show today's date in Excel, there are two ways how to do it easily. There are some differences. Let me show you. First the shortest way how to put today's date is to use a keyboard shortcut, Control and semicolon. This is this sign. So if I stay in that cell and press Control and semicolon, Excel automatically inputs today's date. That's quite useful. You can work with easily for maybe you can fill the dates. If I drag it. I get a list of days after today. If I click here, I can check, change it to fill weekdays. So then Excel will skip weekends. Or I can change it to fill months or years, or I can just fill it horizontally. Like that. This is one way how to work with today's date. In other way, how to work with today's date is with a function, and the function is called. Today. You will input as any other function, so e co-sign and you type today like that. And then I need to put these signs, the brackets, because today is a function and each function starts with the Eco Sign for calculation, then there's a function name, and then there are the brackets for inputs. But because today function does not have any inputs, the bracket stays empty, but still they need to be there. If I press Enter, you will see today's day the difference between putting them with a shortcut like that and with a formula is that if I open the file tomorrow, there will be a tomorrows date. So the function to date will automatically update today. This is quite useful if you need to, let say a calculates how many days are remaining to some date or any other thing. So this is quite useful when working with formulas. If you would like to fill, that would not work here. See it first only the same day because of what it does. It fills the today function. If you need to do like tomorrow's day, then he would put today's plus one for example, like that. So these are two ways how to input to today's function in Excel. 39. Finding out number of days between two dates, adding and substracting days: If you put a date in Excel in the correct format, that means it aligns to derive and recognize it as a date. You can calculate it very easily. So for example, what will be the day in 30 days? It's quite easy to find out. You just put equal sign, click the date and put plus 30 or any other number. So what was the day? 100 days ago? Let's find out. Same thing. Put today's date minus 100. It works properly, does account for leap years and for everything. So you can count on that. This especially useful if you are calculating some due dates or payment dates. You can also find out the difference how many days are there between two dates. So let me show an example of that. So today, I could fill it in automatically would control and semicolon and let's say the Christmas Day, which is on 25th of December 2019. And now I would like to find out how many days are remaining. So what I do now is subtract these two numbers. So I would put Eco's Christmas Day minus today. And what I get is 120, which is the days remaining. If I would like to have these automatically updated, I would not input to date. Now its input manually, but if I put the today's function, it stays the same now. But you will see that every day it will update. It can go also to negative numbers. So let's say if I would have last year's Christmas date. So if I put 2018, I will get minus 245, which is the number they passed. This is again useful if you want to know how many days are there until some payment is due. Let say I will show you the payment. Due will be the first of October of this month. And I would like to calculate how many days are remaining until it's too. I can use the same formula using today and then destructed or I can do this thing, I can put this date and minus today. This is just like including the formula in the calculation. So the future date minus today, which gives me the number of days remaining. But weird thing happens here. You see, instead of the days remaining, I get here, a fourth of February, 1900. Is that you will face that situation quite often in Excel. And this is wrongly formatted number because Excel assumes that if you do some calculation with date, the result will be another date. But this is not the case. So it formatted it wrongly as a date. You see. And only thing you need to do is put it back to numbers. So we see that our 3-5 days remaining until the payment is two. Why we had this weird date, because the number 35 for Excel is like a 35th day from the beginning of the year 1900, which is fourth of February. This is weird, but you know what to do if you see these weird dates. Let's try something more practical with the employees. We have employees list here and we have a different information about and we also have the date they joined the company. And we will be interested. How many days or years are they with us? So let's put a new column and like days at the company. And I will put equal sign and I'll put the other way around. So today minus today in the past. And press enter. Again. This time it got to wrongly formatted as a date with 1900 something. So I know already that I need to just click here, change it to number. So this guy, Barry, is 1940 days with the company. I double-click file_id for everybody. Maybe there'd be more practical to see that in years. So we can simply divide it by three and it's 65. It does not exactly correct because it doesn't count with leap years, but it's 99% correct. So 365€5, Wi-Fi, if you're maybe I had some euro numbers in that cell before. So again, I will change it back to a number. So I will see that barrier is 5.3 years with the company, years at the company. And I double-click here. And now, if I sorted them from largest to smallest, I will see that there are several people that are more than ten years with the company. If I do the other way around, the freshest person here is about one year with us. So this is how we can work with the dates in excel calculation. It's quite easy. And if you know how to do it, you can do various calculations with due dates and dates and know when it's done. 40. Finding out what day of a week of a certain date: Let's learn one basic but very important formula when working with dates, and it's called weekday. Let's move to the sheet called birth dates. And you can see here is a list of several well-known people. And today's date and the date of birth of those people and today's date. You will see the when I'm shooting this video, but there is the today function that will change for you in your worksheet to the date when you are working on that. So weekday, sometimes we need to find out which day of week is a date. Here, maybe just for fun. But later on I will show you a more serious example where to apply. So how to find out when Leo DiCaprio was born. So the function is called weekday, like that. And it has two inputs, serial number and a return type. These inputs sound scary and IT ish. So serial number is actually a date. Date is for XOR is just a 0 number of days starting from first of January 1900. So click that. So this is a date. And the second input is called return type. If I've only put the second input, it's not mandatory. It will give me a number from one to seven base on the weekday, starting with one with S Sunday. Why? Because in the US, the day number one of a week is a Sunday. That's not usual in most other countries. So let's change the type of the function. So number one would be a Monday. So that's why we have the return type. And you will see type number one. This is the basic type. One is Sunday until seven Saturday, and the type number two starts with number one for Monday until 74 Saturday. So let's put number two. And low DiCaprio was born on Monday. Let's double-click. And we have a weekdays like that. So you can check if with your today's date, if it matches with what you know it is. So we have the weekdays. How to use that? Let's move to the daily sales sheet. And what do we got here is a dealer venue per country. Let's see, we are like a retail chain and we have some retail stores in these four countries and we have a daily revenue. And this is actually time series starting from first of September 2013 until 2019. So how to find out which day of week is each day. It's quite important for us because we would like to analyze if the cells are different on weekends than on the weekdays. So let's start with calculating the weekday. So let's do it for the first line and then fill it. So weekday. So again, this'll be the date. And once again, when the return type number two, so it starts on Monday. So we get a, so we will see this is a Sunday. So let's double-click and we have it filled for all the days. Now it'll be nice to see which are weekdays and which are weekends. Or we can use the IF function to split it. So I would say like datatype and use the IF function. So if if has three inputs, first is a condition. So if this number is higher than five, larger than five, that means it's six or seven, so it's a weekend. Then the first input is it should put weekend. We can. And the last input is value if false. So we'll, if it's not larger than five, then it's obviously five or smaller. And that means it's a it's a weekday. Okay. Meaning Monday to Friday. So this is a weekend. The idle click to fill it in. And yes, it works. Where we can do now is to use conditional formatting to highlight weekends. So too. So I would say Format Cells that are equal to, we can to write, read. You see, they're highlighted already. And one more rule. So if the cells are equal, Sue weekday, let's make them yellow, for example. And now we have a nice calendar. We can filter weekends or weekdays. We can use it for a period table or any other function and compare these things. 41. Splitting dates in to day, month and year: Sometimes you need to split the date. Right here. We have we have date and we'd like to find out days, months, and year separately. I will show you how to do that. You can not use flash fill. For example, if I put number 11 and I would guess that the flush Hill function would somehow affiliated. And you see these are kind of nonsense numbers. So this is not the right way. Also, text to columns would not walk work either. We need to use these three functions, day, month, and year. It works like that. They will just keep a day or a month from a date. Month function would use just the month. And the year function would split the year. Okay? Sometimes you might have some other problems. Sometimes you need to split it back to merge it back to a date for the VAR function called date. And again, year, month, and day. And it will put it back to like a proper XML formatted date. You see we have one more column here called h to calculate the age of these people. So let me show you how to do that. I'll put, I will use the function today so that it updates automatically. So I'll put today menos, the birth date, which will give me number of days between today and the birthday. So we will know that low DiCaprio is more than 16 thousand days alive. If I would actually put it in years, I will just divide it by 365, which is kind of ninety-nine percent. Correct. We're just disregarding the leap years. And to put the brackets here on the beginning and here on the end and divide it by 365, oh, 365. And we will know that low is 44.8 years of sort of I double-click. We'll see that H of everybody here in the table. You will see today it shows 0 because obviously anybody who would be born today will be 0 years old. How to use this function in their real life? Let's move to the daily sales. And if I have some, let's say list of dates that say some transaction dates or in this time is a time series. And sometimes I need to know which month it is in order to filter or to use a pivot table on death. I know you can filter by months or years by just opening the dates, you know, and then you can filter on the years or months. Or if you needed to affirm life, we need to put the month here. Maybe to later on, use it in a pivot table. I will produce the month formula and the year formula, and then simply fill it in. And that's it. 42. Magically split and connect columns with the amazing Flash Fill function: The thing I'm going to show you right now is by far my favorite feature in Excel. It is called flash fill. Let me show you how it works here on the responses sheet. What we get is a list of people that responded to our survey, let's say and we have a name and we have an e-mail. Oh, let's put name, name, and email. And unfortunately, as the people would fill in the survey, they will put their first name and last name, which makes it very difficult to serve the people alphabetically, if I would like to let say the guest list or something. So we need to split it to firstName, lastName. Luckily, we just need to one-click to do that. So I will put Barry here and organelles here. And all I need to do is go to Data and fight this econ called Flesh fail. Right here. It might be bigger on your screen because it differs on, by the versions and buyer resolution. But it looks like that is like a like a this table with this yellow flesh. So you just type how you'd like to split the name on the first line of the table. And to warn you, there must not be an empty column. Otherwise it would not understand this is the same table. So if you have it like that, so I have it prepared on the first time. I can just press the button once and I get the first names for the entire table. Then I go to the lastname, press once, once again. And I have the last names. Miracle, right? I can now sorted alphabetically by lastname. I can do more with flushed Phil, let's say any two have initials of these people, be like J here. So I put flush fail and the flesh will will understand that I need initials or if I would like to put Mr. J. Or J J Edom, that would work as well. Or what I can do as well is to extract the name of the company of that person from their e-mail address. This guy works for 3M. So just like 3M is the name of the company here and phosphate. And that's it. You see, it starts with a lowercase letter. So again, corrected that c on the second line is put HP will enlarge letter to autocorrect everything to start with a large letter. So it's quite smart. As you can see. It says you ought to work by splitting and combining cells as well. So if we would like to see, for example, this guy is JC from 3M. So rapid j's that work. So let's put JC from 3M and flash fill it. You can also use the control E as a shortcut. And I got it. I got it right? I think so. Yeah. It seems right. In case it wouldn't work, you can show them more than one line may be to shore, to shore them one or two lines, or maybe do some corrections. And Excel will keep learning from that. So fleshly IS truly amazing. It does have some limitations, though. It only works in Excel 2013 and above. This version is 365, so it is there. So if you have an older version like 2010 or something, you might not find that number. It cannot split dates, you know, so let's say I have employees here and I can know just spit and I will put like five like the date joined. It wouldn't work. Just put random stuff here. So for that I need to use a function month. If I would like to split the month, I would need to do the month function together twice. So, so here I couldn't use the formula. The second thing, it's not dynamic. So for example, if I am here in the invitees, let's go to responses. So for example, if I say, oh, I got this name wrong, it's called missed is actually Mr. Adams with an S. It does not update if you use formula for more updates, but Nashville is like a onetime like a black box, so you wouldn't know how it really works and it does not update. So it's like a one time thing. So that's what you need to know about. Flush fell. 43. Merging text columns using a formula (&): On the worksheet invite t's. I have people that we invited for our product launch conference. I have a name, surname and email. And I would like to put name and surname into one column, maybe to, let's say, print some annotations or do some other stuff. There is a easy way how to do it. Flash fill, I would just type berry or an ls like that, presses flash fill on the data. And that's it, that's done. But I would like to show you how to do it with a formula because that's practical. Maybe if you have an empty list and if you'd let say, add new people later or change their names, you will still want to work it. And as you know, flush will only works once and it doesn't update. So how to make it so it does update. First thing you need to know is how an ampersand work in Excel. So it goes like that. Let me show you an example. Let's say I have a country code and then I have some number. And together it will be a VAT number. So how to join these together? Again, I can use flesh fail, or I can use a formula for that. And in Excel you know that I can use for adding minus four, subtracting this for division and multiplication. And I can also use this asterix sign, so it is not, the extract is actually ampersand, ampersand sign. And what it does, it would glue these two cells together into one like that. So that's the ampersand. Okay, so and I will show you how to use it in that case. So berry or no, so let's glue these two together. So Barry and I put the ampersand and the surname glued together. Double-click and OK, it kind of works best as you can see, we're missing the space between the name and surname, which is quite crucial. So let me show you how to do it once again, the right time. So Barry, I'll put the ETH as a glue. And now I need to glue a space between the name and surname. But I cannot just like type space like that because in Excel formulas and it text input needs to be in parenthesis. So into put like parenthesis space and another parenthesis. So this is a space in parenthesis. Then I put glue again and click the. Certainly. It might look quite scary, but don't worry about it. It's like just the hamburger. The name is a meat. Then we have some source of sarin. The name is the band. Then we have some source than the space in the brackets is the meat. Then we have some source again, and then the lower part of the band. So this is our hamburger that we just did. And that's it. Barry formulas. Now we can just double-click and it's done. And this time is dynamics. If I change the name, let's say, Oh my God, this guy actually is called adams. You see it updated automatically because this formula, and as every formula, it does update easily. So if there'll be like, completely different name, like Jones does have dates as well. There is another function that you might know that will work for that it's called concatenate. Actually in a new versions, it's shortened to just concat. They are both the same. So should the concatenate, I would press name semicolon, then the space semicolon, and then the surname. Barry Cornell has exactly the same effect. Some people like to use it because they're used to that for some reason. It is a long name, so you don't need to know dysfunction because it's exactly the same result like using this glue method. So this is how you connect stuff with a formula in Excel. 44. Splitting a column into multiple ones using "Text to columns": I have a list of emails here on the sheet, cold emails. And they all have the same format, like name, surname, app, company.com. I would like to split this information to column B, C, and D. We obviously I could use flash fill to do that with control, ie. There'll be super easy. But maybe your version of Excel is little bit older and you don't have the Flash Fill function here to do it. Or Control E. Or maybe you have some bad experience with flesh field doesn't work for some reason. So you can use the old-fashioned text to columns and pet the works as following. You will select the entire column and then go to data and data tools. And here it's text to columns. You're icon can be a little bit different if you have older version, but it's somewhere here in the right side of the Data tab. So click that. There will be a wizard. So first, choose the file type that prescribes your data. It's delimited because it's split by some characters by dot. And at here, I'll explain that later. With a fixed width, I will show you later on how to use that. So this is delimited. I go next and now I need to split it by some delimiters. So in our case I need to split it where the dot is, this is where name and surname will be split. So let's put assigned, which is actually a dot. And you will see the dot will specify that or will I can do. Maybe the better idea would be filled first use at to spit off left and right part of the internet address. And let's press finish year. So I press finish. And then actually to put one more empty space because I will be splitting it into two columns and make sure everybody has only one name and surname. So I will do the text column once again so that you have to do it multiple times to get results. I will click that there. And this time I will split it by dots like Beth and press Finish. There's already data. That's fine. I think it's concerning justice. Red cell. So press OK. And now I have name and surname split. Now I need to split the dot-com so it's not there, so I can just separate the name of the company. So again, TextCircle looms, delimited and split by the dot. That's okay. It will cut caffeine by the dots into two columns. This.com I will not need anymore. So I've berry or nervous Intel, which is great. But it's somewhat weird that the, you know, the first letter is always lowercase. I will show a secret function which is maybe not that common, but exactly for this case, it's called proper. And what it does, it will take a text will make the first letter of each word uppercase. So this is Barry. And if I fill it to the right, I get it for the entire sheet. But there is a function. I don't need to have these things twice. So in order to, let's say you remove the function so I can delete those because now if I will delete these three columns, let me show you. The function stops working because it's lacking the data, the input data. So this is not the way we need to do it. So in this situation, you need to select these columns. Press control C for copy, or go here and copy, and then paste it as values. That means the formulas will disappear and only the results, the output of the formulas will stay. I'll put these and the result seems the same, but if you double-click the formula, proper is not here anymore. So I will delete those contra minus to delete two columns. And I'm done. 45. Splitting text columns using LEFT and RIGHT functions: Let me introduce you to two new functions, left and right. We'll practice them here on the employees list until employees sheet. And let's work on some examples. For example, and the new column with the gender. But this time I don't need a female or male, I just need F or M. So it's like a shortcut for gender or gender. Short. And I need just like a first letter from the left of that text. And for that, the left function is perfect. So I put left. And this text for the first line. And I got the letter F. Amazing, right? So if I double-click, I get male and female here in that column. So this is exactly where I want it. Next, I would like to have a shortcut of an office, which in our company is the first three letters of the city where the office is placed. So I need this time from this column, I need not just one, but first three letters, we use the function left again, but this time we put the second input. So it starts the same left. But this time we put one more input which will tell the function how many letters or characters from the left it should keep. If I skip this, it will take one as you just saw. But if we would like to have more, I put number three and it gives me a loan for London and more of the others. So, but again, I would like to have these all uppercase. And for that we have a function, it's called upper. And I have the upper function gives me uppercase letter I, that, or uppercase letter for these others as well, right here for London. And that's it. I can make it in a more elegant way than a withing the formula into two parts to have like a left formula here and the proper for more, sorry, upper formula here. I can do it all in ones using nested formulas. Instead of instead of, you know, doing the upper in the other side, I would just put the upper in the same formula like like encapsulated. So like that upper and left. So it's like an onion. We have, we have the left column which will be calculated first, and then the upper function will be calculated later based on that. So we have an uppercase letter, what's left from the column b here. Same goes for the office shortcut. So I would say upper and left. And I have the office shortcut and I don't need those anymore. So nested formulas may look really scary too, but it's actually quite easy to use. So we just practiced the left formula. Now we need to use the right formula. Unfortunately, I don't have a like a proper example here. I can show you just an example that it works. Right? So i would, if the name, I will get the last letter of the name, which is an E in that case. Or I could put right and put the second input. But like last five letters of the name doesn't make any sense at all with that example, but it does make sense for some product codes, which for example, you have a credit card number and you need to see last four digits of the number. For that, the right function would be perfect. I would like to also note where the right function doesn't work. It does not work with dates because it's a text function and it works with texts primarily. And a date is not a text, it is a different format in Excel, if I will put right here. And for hoping it will give me the year. That does not work, gives me absolutely nonsense. In death case, once again, I need to use the year function. So these are functions how to work with text left and right. But the good news is they are dynamic once again. So if for example, Mrs. holiday changes office from London to brac, which will be IRI choice by the way. Automatically, the office shortcut would change as well. 46. VLOOKUP: Connecting data from two spreadsheets using the most important function in Excel: Vlookup is by far the best and the most useful function in Excel. Let me show you how to use it on this simple travel agency example. What I got here is an ordered list. Each line represents an order, and I know the name of the customer and the trip they choose. On the right side, I have a price list, which is a list of trips that these travel agencies offering and its name and price. And my task right now is to fill in the trip name and price for each order. I can do it by hand, but that would take a long time. So let's try to use VLookup instead. Let me do it for Bradley here first, and then we will fill it in for the other rows as well. So we'll cap and VLookup has four inputs, four things to know to get the correct trip name. First thing, lookup value, this is okay, so what we are looking at, so we would like to know the name for trip I 20 for the next thing would be the table where to look up. I will select it right here. So we'll, we'll cup will do is go through the first column of the selected table from the top until it finds right line with the right item, which is here, I 24. And then the next question will be, from which column of this table should I give you the output? And the answer will be the column age. But in this function we can just simply say column H. We need to tell them the number of the column in side this selected table. So in our case right here, trip name is column number two. The last thing the function needs to know is what should we do in case the trip ID will not be found. Approximate match means XOR trying to find the closest value, the nearest value. We don't want that. We don't want to know the approximate strip name. That's not the case right now. We would like to know the exact trip name or if it's not there, we would like to get notified of that. So we'll put an exact match. Instead of force, I can just simply type 0, which is the same as false, but shorter. So that's it. So there's the function to look up the trip name Bradley chose. So let me see, is the Iceland and it's correct. So now I will double-click it to fill it for all the orders as well. So if you double-click this dot here on the bottom, you will get function for each of the lines. Isn't that great? I will show you once again how to use it for price. It will be very similar formula, but this time I will use the insert function. This is a different way how to enter formulas in Excel. Some people prefer to type it right into the cell, right? Like I just did. Some people like this. So I would like to show you how to use it. As you can see, I have VUCA beer in my most recently used functions because I use it quite often. But you might not have it here. In case you don't see it here, you can just type view gap right here. And press go. And it's right here. And okay. So you will get like this window with four inputs, which are the same, like the impulse that we put directly into the cell. So once again, we are looking for trip that Bradley takes. And we are once again searching in the same table right here. But this time we would like to get the output from column number three. And once again, we would like to get an exact match. Okay? And that's it. And what we got is the correct price. I double-click this dot again to get it filled in all the other orders. As you can see, VLookup is a very powerful function that can help you a lot. The good news, it's easy to learn, but it takes time to master. 47. VLOOKUP: Connecting data across multiple sheets: Vlookup is a function that can look up a value from a source table and inserted into a destination table. There may be both on the same excel sheet, but in most situations, you will find them on separate sheets. You can do that easily. Let me show you how. This is a travel agency example. And I have my order list here on the sheet orders and I have my price list here on the sheet prices. And what I need to do is to fill in trip name and trip price for each of the orders. Let me show you how to do it for using VLookup across multiple sheets. I highly recommend using the Insert function right here. Click here. And as you can see, I have a VLookup in my most recently used functions. So I can just click OK and go if you don't see it here, just type yield gap here and click Go here, cyclic. Okay? And so I will look it up for Bradley. So readily go on this trip. And I will look it up in the price list right here. It's okay and see what happened. I see here prices, exclamation mark a to C. This is something like a calling code of the other sheet. This tells us, okay, take this information from the prices sheet. Column index number is two and range_lookup is 0 for exact match. Click OK. And have the results for Bradley in if I double-click the dot here, either results for everyone. Let's try the price now and this time I will show you what can go wrong. So we look up once again, I will look up this value and be careful if you switch sheets now, see what happens. If I swish switch sheets now you see anything I put here was replaced by the calling code. Every time you switch sheets, Excel will fill in the calling code of the sheet and deletes everything you put in there. So what we need to do is click here and put that information in. See what just happened. X, I'll put a colon code, even though you are referencing the same sheet, that's actually not working in Excel, you might not called the calling code because there can be a mistake. So I would recommend not to use the calling code on the same sheet. So it S5. And now let's switch for the prices. It's like the prices. And now see what happens if you just stay in the input field of table and switch sheets to continue. See the calling code switched from prices to orders. And now you are selecting completely wrong array. You don't want that to be careful. So after I finished with that, just click to the next input. Column, input number, maybe you forgot which column number it was. So would like to look to switch again. And as you can see, calling code is right here once again. And if you've now press number three, it will not work like that. The column index number must not have this calling code. So delete that and put three symbols for range lookup. It's going to be just plain 0 for exact match. So as you can see, using VLookup across multiple sheets may not be very user-friendly, but after some time playing around with it, I think it's easy to learn. That's it. That's how Bill cup works using multiple sheets. 48. VLOOKUP: The traps: Vlookup is an amazing function, but it does have some trips. I will show you how to avoid two most common ones. This is a travel agency sheet and I have two tables here, the order list and the price lists. And I would like to fill in the trip name and pre-trip price from the price list. That will be fairly easy if it's like that, by which the trip ID would be in the second column right here. And we still do that. Let's try. So we have you look up here and I'm looking for this trip id. And I would like to select the table. But now what next? How will it work? The program is that you cannot choose which column will be the one that will be searched. Unfortunately, we'll cup can only go through the first column out of the table you just selected. And because the trip ID is in the second column, there is no way for you to search for the trip name. So it's impossible to use VLookup to search for a trip name. Right now, you would need to flip back the columns so that the trip id is on the left side of the trip name. However, we can still search for price. Let me show you how. Because this time, if I'm searching for that and selecting the table and you have to select the full actual table. You can select only these two columns. So in that case, I do have the trip id in the first column, and then the price will be the second column of the table. So in this time, I can search for it easily. I put 0 for exact match and I have the price. So this is how I do that. So that was one of the traps. So there is no way how to search left out of the id. So on the right is possible. So let me take it back and I will show you second way how to do it. So I will put View CAP and I will use a slightly different approach. So I will select that and I will select a table like that. And column number two and exact match. And you're like, hmm, maybe that works as well. Why not? But never be happy about your result when it works on the first line. Always check when it works for all the lines. So if you double-click that, you will see that your function works for first couple of lines, but then it stops. And a, in VLookup it means the value was not found. So f, z six was not found. You're like, How come it's under second line, how it will be not found. And the same goes for the others like E 12, like how it can not be found. How come the formula works in the beginning and it stops working later on. It's because of the absolute and relative way of referencing and excel and works now. But if you double-click the formulas on each line, you will see that the array, like back the table array for the source, moves from which each line, it moves one line down. So each row takes it a little bit down. So it moves relatively with the formula, which is great. This is exactly what we need to do here on the trip id, but we don't want to do that right here. There is a way how to avoid that. So let me delete those. And there is a way how to avoid that. So let's try once again. So VLookup, looking at for this in that array. And after I select that, I need to press F4. So if you press F4, you will see these dollar signs right here. What they do is they switch from relative reference to absolute reference. So now we are referencing that table and it will not move. So the dollar signs here mean do not move the table if you fill in the formula so that we can continue like normal. And now if I double-click to fill in, you see it works. But if a US view cup, I personally prefer to select full columns for several reasons like that. So I click that and we'll select the full columns here. Why? Because first you don't get this problem of moving space. So you will avoid this proverb, complete liquid selecting the full columns, and also the formula looks a little bit nicer. You don't have to worry about losing a time or you don't have to worry that the table does not start in the first line. Was your, let's say maybe lose several microseconds of computing time for searching these four lines in an unnecessary way. But in 99% of situation, that's perfectly okay. So I encourage you to use full columns that will make your life in Excel easier. So this is the way how I use view gap and this is the way how I avoid the two most common traps. 49. VLOOKUP: Tips and tricks: There are many things that can go wrong when you use VLookup. Look at this travel agency example. I populated the VUCA function here to search for the trip name in the price list right here. And I filled it in. Press OK. And right now what I see is the, the formula is not working on the first throw, even though it's working really great on the other rows. And you are like, how is that possible? This is the same code, I 24 by 24, same formula, and it's not found on the first line, and it is found on the second. How is that possible? The reason for that very often is a very tricky and it's right here. If you double-click that and check, you will see there is a space. There is a space that is invisible. So visually, these two cells are the same. But because there is one space at the end, we will me by accident or for some other reasons too, and we'll be fine. So if I delete that space, suddenly it works. So be careful with VLookup. Vlookup is not Google. It needs to have an exact match letter by letter, character by character to work well. So even the empty space can ruin your results. Now, let me show you another way how not to do it. So I will delete that and try once again. So view gap. And I'm looking for that. Once again in that price list. Once again, I would like to have results from sheet number called number two. And that's it. Press enter. And it seems to work. So perfect. I double-click it. And once again, it doesn't work on some of the lines and you're like, okay, what's wrong in that? What's, what's wrong? How is it possible that it doesn't work for some lines in a box, for some others. There is no problem. I don't see any problem. The reason is that in this formula, we miss the last input, which asks if you'd like to do the approximate match or an exact match. We didn't put it in. If you because we'll cap if you don't put in if you would like to put an approximate and exact. That's okay because this input is not needed. So that means if you see this brackets, square brackets, that means this input is not needed. And if you don't put it there, Excel will automatically choose to approximate match. And because the approximate matching needs to be alphabetically ordered, it doesn't work and it gives you like completely nonsense results. The problem is, it doesn't worry you, it doesn't tell you that you made something wrong. We just need to know this is a very sneaky thing. So don't forget about the 0 at the end. And if I put 0, it works. 50. XLOOKUP: The new VLOOKUP replacement in Microsoft Excel 365: X lookup is a new amazing function that Microsoft introduced in Excel 365. It is there to replace VLookup. Not only it is easier to use, but it is much more features as well. Let me show you really quick how to use it on this travel agency example that we used on this training. So x lookup has only three inputs on minimum. So first, I'm looking for this, so I'm looking for this trip id for Bradley. And this time I will just select the column to search. And I will also select the column where to get the results. And that's it. It automatically has an exact search click and that's it. And we are done. Wow, isn't that amazing? If you'd like to learn more about x lookup. Check my other class here and skill share, which is called x lookup masterclass. We'll go, we'll be there with us for many more years to come because of the older versions. But now is the time to learn the new great way how to look up with excellent cap. So check skill sharing for x lookup, masterclass. 51. Differencess between VLOOKUP and XLOOKUP: This video is for those of you that use VLookup and are interested, what are the differences between VLookup and x? Luca have this example here where I found the trip name from that table using the XOR function. As you can see, the inputs are different. So this first input is the same looking lookup value, and then I select the search column and the third input is the result. And because I have a search and the results separated, it is very easy for me to move around the columns. As you know, that then VLookup, the search column, must be first in the table and you can only look up from the columns that are on the right from the search column here. It doesn't really matter if you just switch it in the middle or at the very end. Like here, the function still works. There is no problem. So you can search left with X lookup very easily. Also, adding new columns, it's a super easy and it's risky because you are counting columns in view cap, then adding new columns in your table might result in breaking the functions or checking the, checking the wrong column. So it does not happen anymore. Also, if your source tables with a large and has many columns, sometimes with VLookup counting the combs can be really time-consuming. With x look-up. You didn't have to do that. The second thing I would like to mention this video is the exact and approximate match. In VLookup, you most probably used exact match all of the time or nearly all of the times. But yet you still need to set it up as an exact match to put a 0 or a force in the very end x lookup. You can set the match is the actually is the fifth input, the match mode. But if you don't sell it up, it's automatically set up as exact match, which is 99% correct. So as you can see, I didn't specify my match. So it will search exactly because this is what I want. 52. Limiting what can be written in a cell: Very often you have to share our tables with your teammates or clients. And what multiple people input data into one table. There can be misunderstandings or mistakes. The way how to prevent that is to restrict what people can put into each cell. Let's look at the sheet called conference. And we invited several companies to attend our product launch Conference. And this is a table shared among the team where they should fill in how many people would attend, if they would attend and if they paid to phi. I will show now how to restrict the debt that area. That means for each of these companies that we invited ten people is maximum. So two, like two people can only write numbers in that. So they couldn't just write to people or something like that because they would mess up the formulas. And we also can ensure that people don't write 12 or more people how to do it. Do it. It's called dysfunction is under data sheet and it's called the actually data validation is right here. It's like a, you wouldn't find it under that name if you would now, so we select the cells which you would like to restrict. These, these people then click data validation and data validation. And now validation criteria says allow any value. That's the default. You can put anything you want in any cell of the Excel unless you make it otherwise here. So let's decide we put like a whole number between and the minimum would be 0 people. Maximum would be ten people. Okay. And that's it. You don't see any difference when you see that. But if you click on that, right, one or five people, nothing happens. But if you attempt to write, let's say 12 people, Excel will tell you, okay, that's not possible. Or we can write that say to people as I did previously. Again, that's not allowed. So you can only put numbers. You can also put a if a fee was paid or no. So it can be yes or no or there is a status yes, no, maybe or waiting. So let's try to feed bait. You can do it by selecting that. Again. Go to text, to columns and solid data validation and click a list I would like to allow on the list of items and source, I can either click somewhere or I can just put yes, semicolon. No, there'll be the only choice. They can input. So if you click here, you can write yes, no, no. You can just type maybe or something like that. That doesn't work or just one or 0. So only yes or no. If you see that the data validation, again, if I would like to change it, I select that, go to Data Validation. Once again, I also see i can also allow a decimal number. If that's the case with the minimum or maximum. You can also choose the number that are not between some summer range or larger than some number or less than some number. You can also restrict the cell inputs to date. It can only be a date, just the date, or between these two dates. Or you can also restrict to time or text line. That's quite important as well. So minimum if you're, let's say in the postcode than the minimum and maximum would be five. So exactly five characters. So we will not use that in this case. This was just to demonstrate to you how to restrict inputs into cells. 53. Creating a pop-up help message next to a cell: Here in the ShareTable conference, I would like to restrict the cells so that people can only input number from 0 to ten. I do it with data and data validation here under the Data tab. And I can allow hole number from 0 to ten. But, you know, if the people put something wrong, they might not know why is it wrong, why they are not allowed to put it there. So I have a two tools here. One is the input message and I put maximum ten. And the input message would be boot. No more than ten people. Okay? In that case, every time a person clicks into the cell, they will see this little hint that it'll tell them what is allowed or what is expected from them to put. That will make life of users a little bit easier. Also, in the data validation, you can choose a custom error alert. So instead of just like saying, okay, this cannot be input. You can put your own, let's say stop and the title will be no more than ten. These boots, a number between 010. Ok. So in case of people miss this hint to know or try to put the number anyway. There'll be a custom message that says whatever you write in it. What you can also do is put a different kind of message, let's say a warning or information that works both quite similar array. So in this case, it would warn the user if they put, let's say more than ten, let's say 12. There will say OK, that's more. But if they click OK, it's accepted. So maybe if we assign like a soft limit warning, okay, maybe that's more than agreed. Please see the manager or something like that. So this is how you work with a warning. So you can help people by giving them an input message. Or let's say warn them or stop them if they put something that they shouldn't. So this is how data validation works. 54. In-cell dropdowns for each cell: This small table shows the companies that we invited for a conference. And we would like to know the status of their invite if they accepted, if they didn't accept, maybe or if we are waiting for their answer. I listed these four possible statuses here on the right side. So I would like to restrict people to only put one of these four statuses, but also helped them a little bit by showing this little drop-down menus. So I will select the cells that I would like to put the restriction on that. And I would put allow a list and the source of the list. And we'll move the spinning a little bit so I can see it as a source of the list will be these four items. And you see here it says in cell drop-down, and this is the functionality I would like to teach you now. So here, if you press this, you will see this little drop-down. If you open that, you can choose one of the possibilities, yes, no, maybe or waiting. You can also input it manually by keyboard. That's fine as well, but it's much easier and more convenient with that little dropdown. But still if you put something that it should not be here, like a they will not be accepted. I will show you how you can use this dropdown in a more advanced version on these employees list where Alice of employees or a company and different information about them. One of them is being a department where they are. And I would like you so that you can choose which department is with these drop-down menus. How to do it? Let's combine few little tricks. So first, what is the complete list of all departments? Maybe I know by heart or if not, I can do this. I will copy this entire column somewhere on the site, let's say to the k. And then I will use the function, remove duplicates. Here on the Data tab. It's called remove duplicates. And I click OK. And of course there are many, many duplicates. And what I got is the unique values, so marketing, IT, finance, and sales. So this will be my list for the insula top-down. However, is it not good idea to keep it here? Because in case I would like to say delete that person, I might accidentally, accidentally delete also the IT Department of these choices. So I will create one more sheet, but say I will put it like an x or like a helper sheet and cut and paste these things like just this. I will cut it and paste it into that cheat. And then I will put the data validation restriction here and here again on the data sheet, and we'll click Data Validation. And I will allow a list. And the source of the list will be here on the sheet x. You can select just these four, or which is even more practical, you can select the full column so that you can later at more of these departments without changing This Data Validation functionality. So I click OK. And now, anytime I need to choose a department or something, I can choose Finance or any other of those. It all it would also work for a new employees that I would put because it works for the full column. So if there'll be new employee can easily choose the department. If I would like to put a new department. Okay. Let's say let's say headquarters. Then automatically, I can use it to select the department for a new or existing employees. What is also practical is to use the conditional formatting so that each department will get their own colors. So I can again, I will show you how to do it for maybe marketing will have one color and then I need to apply the rule for each individual names. So I would say equal to finance, for example, will be yellow. Did I do something wrong? Maybe as if I did something wrong. Because as you can see, finance is not highlighted. I can go to Manage Rules and Edit Rule and I did a typo finance, I have N missing. So that's much better. Am I can put morals. If I run out of colors here, let's say sales. I can choose from these preset, red, yellow, green, or other, or I can put custom formats and choose some other colors here and fill section I can choose to sales to build, say orange or maybe blue. Blue would be great color. So this is how we can easily color those. So insular drop-down is very practical. People like it and it will make less mistakes in your data and people will like or tables a little bit more. 55. Making good-looking Excel forms: When building forums or shared tables in Excel, it's quite important to make them clear and obvious for the user which fields they are supposed to fill and, uh, which not. I use formatting to make it very obvious. Let's look at this table conference where my team needs to fill in for each component status of the signing number of people and it will feeble spade. How to make it a little bit clearer, what needs to be filled in. I do it this way. First I select everything by just clicking this triangle on the upper left corner. And then I will choose some cholera-like fill colour like this one. And then I will make these, for example, direct blue. And then the fields they are supposed to fill in. I will keep them white and maybe put some borders around them. It can be as simple complement of the data validation tool which actually physically restricts what to put in it, but make it visually clear. What needs to be filled in makes your life easier. 56. Protecting sheets against accidental rewrite: Sometimes you already tables that you don't want the users to change at all. Let's see this employee list on the sheet. Employees. Let's say this is a final list. We would like to people to access it, but you don't want anyone to change any of these information. You can do it by going to the review tab and clicking on Protect sheet, can just click protect cheat. And okay. And that's it. It looks the same. You can still copy things out of it. You can read the information, but you are not allowed to change anything. So if I would like to type in it, it will tell me that the sheet is protected. I cannot add any new columns. I cannot change anything, not resize, put anything. That's quite important if the table is final. I will protect now to show you more options that you have. First obvious option is to put a passport to it because if I just protected like that, anyone can simply unprotected and change it. Somebody's protection is enough without the password though, because, because the primary function is to prevent some accidental changes are mistakes. But if you'd like to, you can put a password. But remember, first thing, if you put a password, let's say ABC. I click OK, ABC. Once again. Two things about it. First thing, it is quite easy to crack the password, so it's not very good for protecting very sensitive data. The second thing, there is no way how to retrieve the password. If you forget it. So be very careful. If you forget the password. You can still had good, but it's not very easy to retrieve it. So I recommend not using the passwords. In another way, who can use it is to maybe hide some columns. Let's say I would not want people to see the salary. Our headed column and protect the sheep. And while it's protected, nobody can unhide salary column. Although everybody sees there is a hidden comb. So it may be tempting for people to try to guess the buzzword or tried to heck it with some macro. If you google how to unprotected password protected sheet, you will find some answers. So you should not rely on protecting the sheet, but it can be a nice tool. So let's unhide salary and show you more things. If you protect the sheet, you can still allow users to do something, let's say Format Cells. So if I click Format cells, then maybe they can highlight something, but they will not be able to change the number, but they can maybe highlight some people with some special color. Let me show if it works. So now it's protected. But if I decide to, let's say, highlight that person with a color. That works easily. You can allow some other choices. For example, you can, you can allow the user insert column, rows, but not delete anything. So if there will be a new employee, I can still control pass to insert a row. And they said like her, john, something. But I can insert row but I cannot change it. I cannot put anything in that anymore. So, and I couldn't even delete that row. So it's quite tricky. So if I unprompted theta, then I can delete something. So you can allow different things, but you can see into the hyperlinks, delete coulombs, delete rows, use filter. That's quite useful actually. I would recommend that. So if you click Use Auto Filter, and then even if it's protected, people can still filter. If it's if it's applied, they can not turn it on. So I have to protect again, go to Data, turn on the filter. And then after I protected the sheet with using the Auto Filter on, people can still filter in that table without any restriction. Also, if there will be a Pivot Table on that sheet, you can allow people to use a PivotTables. So you can allow people to sort there. You can play around with it so that it works perfectly. People can access the information they need and there is no accidental damage to your data. 57. Letting people edit only certain cells of your sheet: Sometimes you need to protect a sheet to prevent rewriting something, but you still want to allow the users to change some of the cells. Like in this example, I would like people to fill in the status number of people and feed paid, but I don't like people to change anything else on that table. And I will show you how to do that. It's again on the review tab with protecting the sheet. But before you protect the sheet, you need to switch off the protection for some cells. So I will select these cells and right-click them. Click Format Cells. And there is a protection. You see there is a checklist called locked. It's actually a little bit confusing because the cells are not locked now. So it should say locked when protected. So when the x0 would be protected and it says locked, the user cannot write into these cells. But if you uncheck that, that means nothing happens at the moment. But as soon as you protect the sheet, like that, users can still input anything into the cells. Of course, I also have the data validation so they can put anything, but based on the data validation, they can put stuff in it, but they cannot change, let's say the header here because it's protected. So how to make it easier visible for the user who they can also do when I'm protecting the sheets. I can see there is select Lock cells and select unlocked south. So if I uncheck the select Lock cells, I will I will show what it does. Okay? So in that case, I cannot even click anywhere outside of the unlocked cells. So I can move my cursor to move around this era where I can write. By the way, it will be good idea to highlight visually which are unlocked and which are locked, which occur in different video. But this is easier because the users understand that they cannot simply click in the other area, but they can edit what he here. 58. Protecting a workbook against manipulation with sheets: In Excel, you can protect individual sheets, or it can also protect the entire workbook. Right now I have three sheets in my workbook and I didn't protect any of those. They're all are protected and you can type anything we want and never in these sheets. Let's see what happens if I protect the entire workbook. I can put password, but I do have to conscious click OK. What happens is all of the sheets stay and protected. But what you cannot do anymore is to manipulate with the sheets. So you cannot rename the sheet. You know, because the workbook is protected. You cannot move around the sheets. You cannot change the color. You cannot even create new sheets. You cannot even copy the sheets to another XML file. So this is a very practical if you have a shared world book, let's say with many sheets, and you don't want people to accidentally manipulate with the sheets. It's especially important because as you may know, if you delete a sheet in Excel, there is no way how to get it back. So this protecting workbook is just to keep a structure intact. Once again, same as product taking sheets. You can put a password on it so that people need to know the password to unprotected. Once again, be careful. There is no way how to retrieve the password, although there is a way how to hack the passport, but that's quite a little bit more difficult. This one important thing, if you hide a sheet for say I have the employee sheet and I will hide it. Then it's very easy for any user of this sheet to unhide it. You just right-click and unhide and choose employees. And the hidden sheet is back. But let me hide it again. If I click protect workbook and press OK. There's no way for the users to unhide the sheet. If I right-click, you see, you see both hiding and an hiding is impossible. The user also does not have an idea if there are any hidden sheets. So this is the way how to actually hide something and prevent people from easily and hiding it. So this is the protective or book function. 59. Encrypting a workbook with a password: Sometimes your data can be sensitive and Excel has couple of functions to prevent. Some people see that data. Let's look here. We have some salary data in the employees sheet and maybe we would like to protect it. Little bit, few click file on the bottom left. This will, this table will open and there is a sign called protect workbook. So if you open that, he has several options. What to do. First is always open as read only. This is again to prevent accidental changes. It's just like a warning anytime the user opens that it will tell them like this is the read-only version. Are you sure you want to rewrite it? This especially useful if you have like a multiple versions of the same document and the one that is final, you can ask people to always open, open, read only encrypt with password. So as it says, a, you can require a password to open this book and it actually is encrypted, so it's quite safe. Yet, remember that there is no way how to retrieve the password if you forget it. Protect current sheet would look the current sheet protect the workbook structure, would prevent people from creating, hiding or deleting sheets. Digital signature, if that's implemented in your organization, you can use that as well. Mark is final, would not do anything or prevent the user to anything. It just like tells them that this is a final version and they are not supposed to change that. This is like a like a marking. It doesn't prevent anything. It just helps people understand. Maybe her older version of Microsoft Excel and you don't see this protect workbook sheet. I will show how to do it the other way. So if you click save a copy or save as and more options or just control S, You will see this traditional form where you are looking for saving. So what you can do is clicking Tools, general options. And you see again you can choose a password to open the file. That means to read it And there's one more password to modify, to save it. With changes. Read-only recommended. Once again, this is the same like Mark as final, so it's discourages people from making any changes in the document. So this is how you protect your data from people that you would not see them. 60. Introduction to pivot tables: In this short video, I will explain you the principles of PivotTables. Let's move to the sheet. Employees. What I've got here is a list of employees of our organization with some information about them. So each row is one employee and each column is one type of information like gender, age, and office they are located in. And I also have a salary and day. They joined. And look at the employee report. Here. We're very impressive reports on our employees. I have a let's say number of employees per grade. That means we have five directors, 11 senior managers, 28 managers, 38 associate, et cetera, grand total. We have 100 employees. I can also look in tons salary costs per office, like these are our offices and these are different departments. And this is like the sum of salaries of all the people. I mean, okay, so a number of employees per department. Again, I compare our departments and also split by male and female. I also have a little chart that shows the same information. It will be quite time-consuming to build these report manually or with functions in Excel. These are PivotTable, and luckily for us, it just takes minutes or even seconds to build these nice dashboards. So what Bill table do is they will take a list, data like that, like a database. And then with a few clicks, they didn't use any formulas, but with a few clicks they can build reports take will summarize categories, built these tables, or even charts. And it's very easy if I change something, let's say I promote any of the employees to a director. We had five directors, so let's promote death manager to director. And I can then simply refresh the bill tables. And immediately I have six directors and also the chart would change a little bit. So this is how it works very easily. Also, if I have a new employees that will get automatically edit. Or maybe if you work with some monthly data, let's say, or weekly data you export from your system. Let's say sales, like that, like daily sales. Or if you have some, let's say travel agency and you have some trip salt that you can very easily with a few clicks, you can build a quick reports and every week or month, you can just replace the base data and give the pill tables and just refresh it and then use it in a presentation or just keep it like that. So build tables are an amazing tool how to summarize data. There is no problem with size. If we had 1 million employees, there will be no problem with the, with the pivot tables. So the PivotTables are quite easy to learn luckily. So let me show you a few more in the following videos. 61. Preparing data for a pivot table: In this video, I will show you how to make a simple pivot table out of the employee list on the sheet. Employees, we will build a salary costs per office. But before we start and to tell you something about the data that needs to be ready for the pivot table. So what we see here is data that have a header in the first row. That means it says what each column contains. And then we have another row, Vf to employees. The general rule for the PivotTable is every row needs to be equal. So one row, one employee. In other cases, like we have here, daily sales, one row is one day of sales, or it can be one invoice or one transaction or one trip salt here in the travel agency. So they they need to be the same. There must not be any subtotals. Let me show you what I mean. Let's say I sorted by office and say some data contain stuff like, let's say Berlin total. And I would put like manual or automatic subtotals like that showing Berlin total. That's actually not acceptable for PivotTable. Pivottable needs to have a like a raw data without any additional calculation or extra, so I will delete that row. Also. Pivot table does not like merged cells. So sometimes people like to merge sauce like that. So there'll be like one big saw that says like office Berlin or maybe like that. So merging cells doesn't work for pivot tables. You wouldn't recognize it as a separate rows. So let's not do that. So let's go back. Also. Pivottable needs to have all rows filled. So if there'll be like a taper that would always only show the name of the office of the first employee. And we somehow assume that the other employees are from the same office. It would not be clear for the PivotTable sort of beautiful, nice to have all the information for all the people. It's okay though, if there'll be like a missing information, if, for example, that employee would not have an office, it would not be treated as a Berlin office employee, but it will be treated as a unknown office employee. So this is how data should look like. There can be up to 1 million rows, which is the maximum in Excel. So I will click and insert a pivot table and the data gets automatically selected. You see we have 100 employees, so it wants to select the list sheet of employees from A1 to age 101, which is correct. But there might be empty rows in a data set like that. There'll be like empty row or maybe like empty row after each city. We're like a possible situations sometimes appearing in lists like that. That's generally not a problem for a pivot table, but used to be very careful with money. We're selecting the data. Because if you click here and click create PivotTables, as you can see here, it only tries to create a pivot table. From the top part. You would have the same problem with creating a filter or something like that. If you stand in a table, if there's an empty row, Excel considers that as a separate table. I will show you how to remove empty rows in one go. So if you have a long table, we would like to make sure there are no empty rows. You will just like select all the columns, go to Data, turn on the filter, and then use any of the columns to filter the blanks. Let's say on the gender. And I see these are all empty, 112141. We'll select those. And right-click and delete throw, and then clearly filter. And that's it. So only the clears, clear roles where selected. Bureau careful to check the rows. If there would be empty rows that you would insist on keeping empty rows, then when creating a pivot table, you need to select the full table, including the empty row. May be like that, or maybe even the full columns, but that create some additional province we will talk about later. So if you select those and then you can go insert pivot table. And then you would understand why we would like to take it and the empty row would make no problem in general. What is especially dangerous is when the Emperor Nero is somewhere below what you see when you, when you open the table, let say somewhere here, I go up and I click OK. And is it nice dataset, I click Create PivotTable. I didn't check it anymore. And to that I will be surprised about the data because actually, as you can see, there is empty row and not all the data gets automatically imported into the PivotTable. So first thing, remember picker for about empty rows or subtotal. So there must be header and the row number one, that it must be in every column needs to have one information. There must not be any merged cells. There must not be any empty cells unless it means like really empty if the information is missing and if there are empty rows, be careful to import all that data. So let's do that. Let's create a new pivot table. So I create a pivot table. And let me explain you the user interface of the pivot table. Right now, I don't see any data. I still need to do some adjustments. I need to tell Excel which data we would like to see. What I see here on the left side is where the PivotTable will be. You see the Pivot Tables are usually created on a new sheet. She'd won in our case, unless you tell Excel otherwise when creating it. So this is where the PivotTable Welby. Well, you see here on the right side is PivotTable fields. This is like the Mission Control of the pivot table. You see here you see all the columns, we call them fields, name, gender, age, etc. And then there are like four brackets that you use for controlling the PivotTable. When you are in a Pivot Table, you see also two additional tabs here. Pivottable, analyze, and design. So, but if you click anywhere outside of this range of the PivotTable, let's say here, everything will disappear, but don't worry, you can just click back and you will see the information. Once again, if you by accident close this pivot table fields while being in the pivot table. Don't worry, you can go to the Analyze tab and field list here on the right. If you click that, you will get the field list replaced. So as I promised, we have an office. So what I got is if I put the office into rows, I will get a list of all unique offices in my dataset. That's quite practical. And now, when I need to find some values for the office, what is the information that we would like to know? And we would like to know the sum of salaries of all the people working in these offices. So this is the values field. So if I put some, if I put salary and drag it to the values, you see automatically it will turn into sum of, sorry, you see the format is not very neat, so you can just select it. And on the Home tab, change it back to the currency that you need to produce, euro in our case. So you can format it as a regular table. So what I've got here is a sum of, sorry, I can easily sort the table going to data in sore sort if I click here or Z to a to be sorted with the city with the highest sum of salaries, which is brac, and a city with a lower Samsara, which is Sydney, in our case. We can also filter in those, but the filter works a little bit different than in a, in a table. You can filter by column. Both columns are filtered using that field. So the new choose label filters, this is, this is the offices. These are labels and value filters. This is for filtering these things. So you can, for example, do that. You can filter on this some particular cities that you choose, let's say Berlin, Bratislava, and Cape Town. And you will see on the DCC the filter is active. So I will go back. Or you can use the value filters. You can see, for example, let's filter only only the offices. Ok, I will clear the filter from office right here to see all the cities once again. And now in the values, I would only want to see offices where the sum of salary is greater than, let's say 200 thousand Euro. And you see it's filtered. Or I can filter, let's say on the offices that are top ten, that doesn't have to be top ten, but can be, let's say, top three items by sum of salary. So top three offices with the highest, sorry, coasts. And these are prac, Tokyo and San Francisco. And I will clear the filter once again. So this is how you filter and sort in Bill table. There is a lot more that you can do with a pivot table. That it was just like a quick introduction. 62. Creating a simple pivot table to summarize your data: In following couple of minutes, I will build three sample PivotTables and then I will show you how to update the data. So let's move to the travel agency sheet here. And what we have here is the ordered list. That means every line is one customer order and there is a trip id. Actually use VUCA function to find the trip name based on that id. So there is a BellKor function, very important function and price to find the price of that. So I have a like a complete, let's say data that are perfect for a pivot table. So let's build a report that will show me what is our revenue per each trip and per each trip, that will be fine. So we assume that everybody paid the full price. So a stand in death, I go to Insert PivotTable and click here. And I would put the trip id or sorry, trip name would be a little bit better maybe to the rows. And then price to values. Automatically, the PivotTable will show me the sum of price, and we'll format this into Euros. Okay? Another interesting information would be now the number of customers per each, per each trip. So I would put the customer into values. And this time because the customer is not a number, it's a, it's a name. So it will offer me to count the customer for me. Unfortunately, formatting goes wrong. So I need to select though and remove these Europe because this is an actually number of customers. So I will choose number here with zero-sum points. So this is the number of customers we have per each trip. So this is a second example, or still was the first example. Let's go to the second example now. And we have daily sales. Here is a retail store with foreign countries and we have a like a daily sales for each days from 2013 until 2019, we have 2 thousand rows, so it's little bit larger, but it's not a problem for pivot table. So let's go Insert PivotTable. Click Enter. And this time I'll be interested maybe in the sales in France. So output, I will start with the value. I will put France in values. So this is a sum of sales in France. Again, I will format it to Euro Pivot Table. As you can see, it's not very good in understanding the format because you see even though the data is formatted with Euro, the PivotTable cannot read this formatting, so you need to formatted manually here. And now I would like to split it by date. And you will see that it automatically got split by dates. If I open it, I have quarters and if I opened again, I have months. That's quite practical. So theoretically, even if that would not be aggregated, even though I would have like a individual transactions, not like a daily summaries, but individually and transactions. And we very easily be able to have a like a monthly, quarterly, and yearly sales numbers. Maybe if you'd like to adjust that, or maybe if you're an older version of Microsoft Excel, you don't see that aggregated by years, quarters, or months. Have you actually turn on and off this aggregation or grouping is, if you right-click anywhere into these numbers, and I will click Ungroup. And this is how you maybe see the stable if you follow the steps that I just do. Because not, not all versions of Excel can do it automatically, but all versions of Excel can do it after the right-click. So if you have it in that states, you can right-click, click group, and then you can select the months. And also heres, I click months and years and you have a nice sales table of months and years. Here in the rows, columns. You have a date and you have a years as a special new field. So you can drag this field into columns. And you will see that now it's neatly formatted by month in rows and years in Column Labels. That's great, isn't it? Okay, so now let's go back to employees. We have employee list and we would like to know how many employees there are in each office. So let's again go to Insert PivotTable and click name as values. That would be a count because you cannot sum the names and office here in rows. That's quite easy. But I wanted to build this table to show you how it works with updating the table. So if you go to employees and that's the I will relocate some of the employees to different office, let's say end Waldman will be relocated to Frankfurt, our new office. So let's go back. And there is no change with a pivot table. The reason for that is that PivotTable doesn't act as a typical Excel formula. It acts more like a webpage. So even though changes were made in the underlying data, you need to refresh the pivot table. The easiest way how to do it is to right-click and refresh. And wallah, We have a Frankfurt and the one person in Frankfurt. So also if you go to Analyze tab of the pivot table, there is a refresh. And if you open it there is a refresh or which is practical if you have multiple who build tables and you update your data. So refresh all, refreshes all the Pivot Tables. If you go to employees. Another typical situation might be if you go down and let's put a new employee. Jones, Adam, male, 30. And let's build one more office in Japan, let's say Osaka finance manager. And sorry, we can keep that empty. Actually, this is not a problem and the starting date would be today. Ok, let's go back to our Pivot Table. Click refresh. And nothing happened. Why not? Because if you remember when we were creating the Pivot Table, I will do it again. It will, it will only a select these cells until 102. So pivot table does not automatically understand that it should include any additional rows below. We need to teach it. So if I just do some additions, like time to time, I can just be invertable, go to PivotTable, analyze, and choose, change data source. And then this will give me step back to where I started the pivot table. And if I go down, I see this Adam Jones is under that line. I can just click in Greek Control a to select everything and click OK. Problem solved. But you know, you don't want to do it every time you update your data. So there is a clever how to do it, how to work with data, you can use the Excel data table. It is a lot of interesting functions, so I will make a copy of this table and show you how to use it. So if you have a table like that that gets updated or appended, it's good to use the Excel special format for four data. I will make a copy of this table by just holding control and dragging to the side. I hope you remember this trick. And if I stand in this table, I go home. Format is a table. And I can choose any of the coloring. That doesn't really matter. And then create a new pivot table out of this table. It is a special function. It's not just typical Excel worksheet. It has some data properties is more like a database style table. So if I go to insert and pivot table, you will see that this time it will tell me it's not like a range, it's just done me. Table one will be my source data. I can even rename the table. If I go to table design, I can put the favorable name to employees. So it's neat and tidy. Then I go to Insert bill table and I have the new sheets. And I will recreate the same table. I will go to office into rows and name two values. And again, I will show you how to create new employees in that case. So if I go down and create more data that's able to put new employee, lets say relier. Anna, female thoroughly. And let's build one more office. Let's say Melbourne. I'm not sure how to spell it. That might be right. Sales director. And starting today, sorry. There'll be filled later. So if I go back to this table, to the pivot table that's built on these blue database table. And now I right-click and refresh. You'll see the Melbourne office appeared. Because this table automatically adds any new rows, which is very practical if you need to keep some database or making new data. So anytime your data gets updated, if you work with some exports, let's say you can download the new people and just like pasted below and then just click Refresh. Or if you would like to have a completely refresh, what you can do is just like a deleted zeta. Completely keep like the header so the, so the table will not be deleted and later on export the data, let say from your HR system and paste it, paste it here, and it, the PivotTable will work with that updated data. So these are few examples of pivot table data and very important information how to update those. 63. Building the perfect pivot table by manipulating rows and columns: Let me walk you through all the building blocks of a PivotTable. Let's move to the employees list on the sheets employees and build a pivot table out of this data. So click, Insert Pivot Tables, and click OK. So let's start once again to have a pivot table which shows office like all the offices that we have. And sorry to have like a sum of smaller. Great. So formatted here €2. You will see that the Osaka salary is empty because in the original table, there is a edit a new employee in Osaka office, but I didn't assign him a salary. So let's just choose a number from here. Go to the pivot table, and right-click to update or refresh. And now we have this number. Great. So let's do something more. So we have rows, columns, filters, and values. So working with rows is easy editing you put in there, we'll split the data into that category. So if instead of rows, I will just carry it out. I would put department it we'll split it by these four departments. We have. We can put grade and we have grades. Let's look at columns. Columns here works exactly the same like Rose, but in columns. So it can split the data by columns, let's say, great. At main Associate Director, et cetera, you see it's split into columns. This is especially useful if you would like to split the data by two categories. Soil put departments in rows and great in columns. So what you can see here is the here is the totals, total sum for all the salaries. And here we have like Admin, all the admins or the associates or directors, etcetera. Here we have all the finance people, although IT people all the marketing. So, and here's the hourly combination. So all the associates in IT, etcetera, etcetera. So this is how rows and columns works. They are interchangeable. So if I, if I would put, let's say department into columns and great into rows. That might even look little bit bearer. So great. And then we have filters. Filters can neglect brief filter the data. So if I put, let's say office, I can choose to display this you, but only for one of the offices. So if I choose Berlin, for example. The table gets a little bit smaller because not all of the departments are in Berlin and not all of the roles. So you see we have no director in Berlin, for example. And there are much more empty cells because there is no such combination. There is no finance manager in Berlin. It's via this empty. I can even select multiple items, let's say Berlin and Bratislava. And this is a sum of bold Berlin and pretty slow. I can put multiple filters. For example, daily joint, I can only filter people that joint. Actually, this is quite stupid because here PivotTable doesn't allow me to filter by years or months. So let's do something else that's filter them by gender. Maybe. So for some reason, one lossy all the female or male, let's say employees from Berlin and Bratislava. So I can have multiple filters. Ok, so I switched them off. To show you a little bit more what we can do, we'll take those away. So what we can also do is to put more fields into row. Let's say if I put it under each other, well, triggered here a little bit to make it bigger. So we will see we have admin and the lid split into departments. So we have a great and when it's split by department, if I choose to, I can switch the order. So it's first sorted by department finance and then spit by. Great. You can choose anywhere you like. And if you wish, you can put, you can put, put as many rows as you want. Let's say office here. I will put just to. This is practical, especially if you have some three logic in a structuralist say if you have some stock levels and have some family of products and you have like sub codes and the Nerf like different items like or SKUs. So for that, this three kind of sorting is quite useful. You can close the individual labels here or you can close the entire fueled by just right-clicking. Expand, collapse, and collapse an entire field. So that's what you can do, quite practical. So again, I click expand, expand the entire field, and I'll see everything. So if I put department here in grid here, for example, if I am not interested in interests, I can open the filter here and I can uncheck the interns to height something in, in the data I have. So there are multiple ways how to work with the pivot tables. You can also change the structure if you go to design, there are little bit more layouts or the pivot table. There is this compact form. Now actually there is no change, but if I have multiple labels in rows, so there's the layout, you can change it to compact form. You can go to outline form, which is little bit more spread out or like that will form if you want. So you can change the layout and of course you can change the color of the pivot table if you want. There are multiple versions of that, even some more color to one. So great this variable, the basic manipulations. So we have filters, columns, rows, and values. And by just clicking and dragging, you can build your own Pivot Table Report, which is very easy as you believe by watching this video, I hope. 64. Working with values in pivot tables: In this video, I'll walk you through what you can do with values in pivot tables. So let's create a pivot table and then play around with the numbers. We have an employee's list here on the sheet, employees. And let's create the PivotTable. Let's go to the insert tab and click pivot table on the left. Everything gets selected, that's fine. And let's create a simple PivotTable. It doesn't actually matter if you start with rows, columns, values, or filters. You can choose anything. So we would like to list of some of salaries by office. So I'll put the office here to get a list of our offices. And then I will choose the salary and drag it to the values and formatted as euros. I'll go home and choose a euro as a format and remove two decimal places rate. So this is the basic of values. You just direct value with numbers, which is a salary for example. And what you get is a sum of sorry, because typically in finance we work with sums. But sometimes one, something else, let's say our average, I'll be interested with the average salary in each city. Don't take it as granted, these numbers are basically made up for the purpose of this training, so they make no sense in real life. But let's, let me show you how to change it to average. You can stand anywhere in the salary field here. Right-click any of the number and click Summarize Values As. And instead of sum, you can click average. And this will be the average Cyrus PR office. So let me, let me sorted from largest to smallest. So in Frankfurt and Tokyo be the largest, Kuala Lumpur and Berlin, the lowest. As I said, I'm not sure if it's a real life situation. Okay. So we have this data, we have average where you'd be interested in, let say maximum and minimum, sorry, will be the lowest paid person, who will be the highest paid person. We don't actually have to always switch that. We can put the salary into values multiple times. So if I take the salary, once again, drag it to the values, I get some and I can right-click it. Summarize values as and this time I will put the put is the minimum salary, but is the lowest salary in each of this. Ice. And drag the sorry, once again. And I would like to click it this time. Radically kid, once again summarize values by maximum. So there'll be the maximum salary. Nice else to stupid once again, for the sum of salary. And this time I would like to chew something special. I would like to show you the share of salaries of the total service per individual offices. I will do it by right-clicking that. And this time I will show, Show Values As, which will be the second menu. Show Values As, and I would say. Percentage of grant total. In the older version of Excel, it is little bit different wording, but you can click that and you get percentages. So there'll be like the percentage of the total, 100% of the salary. So you will see 11% of the salaries of the city IR in Prague. So we have the sum of salary in percentages. We can choose to put the salary once again here to see it not just in a percentage here, but also in a full number. Four, the percentages you can theoretically choose to display the salary as well. If I click the average. So if I show values as percentage of grand total, you will see that the 100% would be the average of the whole company. And so you see in Frankfurt there is one in 53% of the company average, whereas in Berlin it's 79% of the company average. So let's go back. We can do the same with another number and 3's, let's say age. So I can put one more value here. I will put some of age. And this time we'll go to wrongly calculated as Euros. And I will choose again. It'll be a number, a Judean number. And actually some of H makes no sense at all. So let's choose to summarize values by average. So I'll be interested in will be the average age, PR office, Rice, we are quietly yank office in Frankfurt with the highest average salary. That's quite interesting. Okay. I think we only have one person here because the average, minimum and maximum is the same. So right click here. Click. I have a problem with this thing because it's, it's good to wrongly formatted as Euros once again, even though those are percentages. Nice. So as you can see, you can play around with the values. There's one more trick I would like to show with values and for that I will create one more thing. Actually tumor takes one of them is count. No. So, so you can use, for count, you can use any of these fields because counting was the same. So I'm running out of space. So if I put name here, right here, it will be count because you cannot average it. There'll be a count of name, but it's not a unique count. So it's really count of lines. So if I would put gender here, the result will be same. He just uses a column to count how many people are there in each individual office. I see my table is quite large, so I can maybe go to View and Freeze Panes and freeze first column. So if I'm scrolling, I can still see which office it is. Two, you can use some other excellent features with pivot tables as well. One more thing, if you have any build tables and any numbers that you are interested in, the maximum salary and you are thinking, oh, how is it possible that the maximum salary in Tokyo is that higher? Who's that person? So you could double-click that. And a new sheet will be created with all the people in Tokyo like and you will see where the salary comes from. A will, you instantly see who's the maximum salary guy? Or let's go back. Like I'm interested in Berlin so I can double-click it. So even bearer, I will build one more table to demonstrate that the employees table. So I will put salary here, grade here, and department here. And then I can really see OK. So why we are so much costs here, the managers in sales like who are dose. So again, if you double-click any number in a pivot table, you will see the list of rows that are making these numbers. If I double-click it, I get all the sales managers is a new sheet, is not a filter in the original table, is a new sheet. So after you saw that a token, delete it. So that's what you can do with values. I hope you're excited. 65. Creating a chart from a pivot table: Creating charts out of pivot tables is quite easy. They are called pivot charts. Let me show you how to do them. Let's build a billiard table on the employees sheet and stand anywhere in the sheet, go to Insert Pivot Table. And let's make something simple. Let's make a department into rows and sum of salaries into values. Okay? I will choose to format it is euro, and then I will build the pivot chart. Okay? All the things you need to do is to stand anywhere in the pivot table and then go to Pivot Table, analyze, and click pivot chart. This button is not there in your version of Excel. You can just stand anywhere in the pivot table, go to insert and just select any chart you want. So and also you can see the Pivot Chart button is also on the Insert tab. So I can click it here. And now I get a choice of the style of the chart. If I would like to see line by column, etcetera. I like column charts. They are usually okay for most of the situations. So create that and you will see I get like the salary Sam, based on, based on department. I can go through design, you know, to format the chart a little bit. Nicer. I have these pre-formatted styles here. It works basically as any other chart, you know, but I will add the labels, data labels on top. It works as any other chart, but it's collected. Loop your tables. If I change something in the PivotTable, that charge will change as well. So for example, if I sort the data in a pivot table, the charge will be sorted as well. If I choose to insert department, I will choose to put a grade here. You will see charged that gets updated as well. And once again, I can sorted from smallest to largest and easily the chart will be done as well. So I can also use, for example, rows and columns. Now it's little bit messy, so I would need to sorted out or maybe choose a column with less, less items like gender. So I get male and female. So you can see the summary of that. So as you can see, anything I put in the rows and pivot tables will be, will be individual categories here. And anything you put into columns will be the individual colors in the chart. Also, if you click in the chart, you will see the same menu, but it says axis categories. And legend series, we shall make understanding the chart a little bit easier. So I'll simplify the chart. The thing I would like need to notice is using this chart in PowerPoint. So let me copy this chart and paste it into PowerPoint. That's quite important. Control c. And I will go to an empty presentation that I prepared. I click inside this field, click paste. Wait a little bit, and the chart is here. Maybe I can make this, make it a bit bigger. So and the charges there, but there is a problem. So let me switch and let me change a table. Maybe after some time I decided to rebuild the table to put to put something else here, like an office. The Chargaff's rebuilt. But if you go back to PowerPoint, you will see that the chart changed as well. So if you copy and paste pivot chart into PowerPoint, then it stays connected with the original pivot table. So you have choices out to how to prevent that. When you are pasting the chart. You have a like on the bottom right, we have like a little type. Base options is not quite visible. What we maybe make it smaller. And now the top is gone. So let me try once again. The choices only after you put the tap in. So let me try it like that and I'll paste it. And now I have my paste options here. So if I open it here, you will see I have a base options based on color as well. So use this nation theme. That means that theme of the presentation, if they would have different color scheme, which do you have an OT and embed workbook, you know? So the default version here is link data. Use the destination theme and link data. But if you choose this span m-bit workbook, that means like a every, all the world's books will be copied inside of that presentation. And it will make it invisible, invisible workbook inside the presentation. So discharge is no longer linked to the original one. This is like embedded chart with like embedded Borg book. And if I go back to excel and change the chart to, let's say something that we didn't see yet. Let's say the joint. Then go back. You will see discharged did not change at all. But the previous chart that's still linked was changed by the joint. There's a third way how to enter charged into PowerPoint and show you in a second, pasted here. The next one. The last possibility is to paste as picture. This basically means that you will take a picture of that like a photograph of the chart. And so now it lacks any information. So it's just like a simple picture. So the advantage is you are absolutely sure that it will never change. The disadvantages is that it's very hard for you to change the formatting or do anything with it. Unlike that chart, boat embedded chart and the link chart, you can choose to let say, change the chart design as you go, or change the colors, anything you wish. The only disadvantage or the embedded chart might be that if you, if you have a large datasets and a lot of embedded charts, the final presentation can be the size. It can take several megabytes or tens of megabytes, but in normal cases it doesn't happen that much. So depends on your case. Some people like to use linked chart. They have like a pre-made report. You know, they replaced the data in the pivot table and they have a presentation ready every month. But it's quite tricky. I recommended if you know what you're doing. Otherwise, I'll be very careful and use the embedded chart. On the other hand, I discourage you from using the picture over chart like here because it does not look very professional when you work with data. Okay, so there was PivotCharts. One more thing, this linking and embedding works not only for pivot charts but for any charts in Excel. If you paste charts in PowerPoint, you have three ways to paste it. Not just pivot charts. 66. Creating a simple chart: Making charts in Microsoft Excel is fairly easy. Let me show you how to do it on this sales per country sheet. We have some data cells per country and I would like to do a chart for Germany, South selected data like the Month column and the German icon. And then I go to insert and click the Recommended Charts. Pattern. Excel offers me few charts. I would like to choose the column chart. I recommend this. You will never disappoint and a man with a nice column chart. So there will be the sales. In Germany. As you can see, chart is a floating object so you can drag it anywhere you want in your real combined or resize it to any size that you need. You can also see that the data where the chart is built are visible when you select the chart. So this is the data, these are the category labels and this is the series labels. So then you can adjust it with your mouse where the data come from. You see that I, on purpose didn't select the total row because if I did, the chair would make no sense because the total row would overrule the whole axis and you would not see what you wanted to see. So I didn't choose it. But what I can do is to choose more countries. You can see now we have Germany and France, Italy. And yeah, this is how you build a chart. I will delete this chart is time. And now, if I would like to build a chart, let's say just for France, it can either move the column so the, so the category labels and the data are next to each other. Or I can hold the control key and simply select, Let me see any distinct here. Select the control key like this. Then hold the control key. Select France. It takes some practice. Then go to insert and click recommended charts. If your version of Excel is missing the Recommended Charts button, then you have to choose the chart style directly. I will click on Recommended Charts and click homes again. And that's it. So this is a chart for France. I would like to show you one more thing which is a map chart that comes with new versions of Excel. I will copy like a little part of it. I don't have to. I just wanted to show you what I want. Let say I would like to demonstrate January sales in a map. So I will go to Insert maps, filled map. And as you can see, I have a Germany, France, and Italy selected here in this small map showing me the number of sales for that you need to the new version of Office or an internet connection. So as you can see, there are two steps in building charts. First you select the data and then you select the type of chart that you want. And that's it. 67. Adding a chart name and category labels: Let me make a quick chart and show you how you can customize it. So I'll select the Germany data here on the sales per country. She'd go to insert recommended charts, and I select a bar chart. Now we can adjust the chart with going to this chart design tab that appears when you are standing on the chart. And then here on the left, there is Add Chart Element. And you know a lot of tools that you can add and modify on the chart. If you have an older version of Microsoft Office, instead of this button, you will see a whole top that's called layout. And you will see exactly these functions there. So for example, you can modify the name of the chart or at the legend. Legend is not necessary right now, but I will put it for Germany. But if I had more than one series, that will make much more sense of this is what is called a legend. Chart. Title is the name of the chart. So we have None at the moment. If I put one above the chart, then I can double-click and modify it. So like sales 2018 for example. Or I can move it around inside the chart area as I want. There are more things that you can check and adjust their the axis titles. So it's very good to put for title, for readability month. That's quite clear for the user of the chart, but maybe for the horizontal, for the vertical. I double-click inside. It will be good to mention that these are number of cycles. Where you can also use is data table. Data table just gives it puts the data just below the charter with an exact numbers. It's very practical in some situations. You can adjust the grid lines here as you want. Error bars. This is more for like a financial charts. And yeah, that's about it. These are the most important features of a chart. 68. Making a chart easy to read with data labels: Data labels are important feature of any chart. I just made a simple chart from this data cells per country and I chose Germany here. You can see that even though there is an axis, you don't exactly know what are the individual value we will use in each month. You can see if it's growing or going down, but you don't see this values. So I will show you how to put the value just above that. You go to chart design. You go to very life to Add Chart Element and then choose Data Labels. And then you can choose where they are placed. Center doesn't make that much sense. Very much indoor can change their color to white if you want. Insight and potentially interesting. Again, I would need to record them by it, but I mostly choose this outside and thing which makes the most sense for me. When the labels are here, you don't have to use the x's anymore. So any feature of a chart if you just click on it like that. And to be very careful, and yes, now I clicked on the grid. So if I now press Delete, data's appear same goes for the axis, and I now can delete and just keep a very nice chart like that with using the data labels. 69. Pie chart and its special features: Let me show you how to use pie charts. Here in the sales per country sheet. I will select all the months and Germany here, and then go insert and choose pie chart here. But as you can see, discharge fails in visualizing the data in a nice way. We cannot see anything from the char at all. It looks like a ball. So this data are not quite suitable for pie charts, so I will delete it. I have another example where a pie charts would be more useful. And let's move to the market Sherry worksheet. So we have countries and market share of our company in each country. So if I select that, go to insert, create a pie chart and see what happens. Doesn't really work. I will tell you why. Because to see the real pie, we also need the other part of the pie. So which is not our market share, which uses share or competitors. So let me add that column. So to count the remaining to 100%, I put one which represents 100% in Excel, minus the cell. Click Enter. So 87% and double-click the filling, got to fill it in. And then I put, like competitors, competitors is not visible because there was a white pain before a click to copy the format. And dense like that. Go here. And you see, even if I click the recommended charts, the pie chart is now the number one recommended. And we have a nice pie chart. You can, of course, change colors if you want. What is practical in that situation is to add data labels. So if you click here chad chart elements here on the chart design tab, choose Data Labels and you can put them outside or maybe inside it will be possible or center. As you want. With the chart. You can also adjust the rotation and something which is called explosion of the chart. I will show you if you right-click the chart and click Format Data Series. We will get a panel here and there are two things versus angle of first slice. This will allow you to rotate. The pie chart is you need. And the second part is quote, explosion, that shows, you know, how much apart are the individual pieces of the pie. So this is how you can individualize your pie chart and make her data nicely visually represented. But remember, by Charles are the most overused charged, so they are suitable for only some situations. If I would like to do one more, I select this and hold the Control key. Select that. And I'll get the Chairman he selected. Go Insert Pie charts and continue with the same manner. Ok, so those pie charts, and I hope you liked it. 70. Types of column charts and when to use them: There are three types of column chart in Excel and our show the differences. So let me select this sales per country sheets and these data for Germany, France, and Italy from January to December. And I go to insert and choose the pie chart here. And you see this 2D column chart, or you can just 3D If you've learnt that there are three different charts, I click on that and we have this kind of chart. This is very good because you can see, you can see this data series lived next to each other. So that if you want to see the progress of Germany or France or Italy, thanks to color-coding, you can visually follow each data series individually. On the other hand, this chart is not very good at showing the total, doesn't tell you what's the total result. You will have no impression if as a whole the company is going up or down. So for that we have the second type of chart, and this is stacked column chart. This chart is very good if the most important information for you is the total how much the company made and the information how much was made in individual countries would be secondary. So this is when this chart is good. If you would like to see what are the individual states in industrial cart tree is kind of hard to compare. Is this part bigger than that part? You don't really know. That's quite hard to guess from this chart. What you can do is put both of these charts are next to each other. Or you can maybe click Add Chart Element in the chart design tab and click data table. And you can put the numbers just below so that people can at least read those. There is a third type of column chart and that's 100% stacked column chart. So I go Insert Column Chart and the last part. Ok. So what you can see now, all the bars are the same size? Yes, because they are shown on the in percentages. So this shard will charge, will not show you the absolute values of any of these numbers, but it will only show you proportions between different countries. That can be interesting, especially if the total doesn't change that much. And to see how the proportion changes, I use this chart very often as a complimentary chart to the stacked column chart to see the proportions. So these were the three types of column charts. All of them are useful and use them wisely. 71. Switching rows and columns: Now I will create a chart of a sales per country here by selecting these data, going to Insert. And I can use recommended charts, but I already know I want to do a column chart, so I will choose this one. So I have a nice chart like that. But there is a very interesting function called Switch rows and columns in the chart design tab. If you click that, the charge will change in a different format. So now the categories are Germany, France, and Italy. And these are the individual lines. So you can see next to each other, if I click that again, is a different chart which shows a different situation. So switching rows and columns, you can play around with it if you feel that the, the other version would show a clear picture of what you would like to show. Also, if you have a table like that and you would like to switch rows and columns in the table itself. You can copy it. So I select everything. I click, Copy and click next to it, and then paste. I open this paste section. And here I have transpose icon, which is here. It's got transpose. And if I click it like that, as you can see now the rows and columns in the original tables are switched. That can be useful not just for charts, but generally working with tables. 72. Editing horizontal and vertical axes: I have number of sales per country on this sheet, but also in this column F, I have websites visits, and now I would like to make a chart of that and show you how to work with the axis. So I will select that, hold the control key, select the next column like that, and go to insert and recommended charts. And I will choose the line chart this time. Okay? And now, as you can see, there is this left axis showing me the values that it automatically is set as a maximum of 600 thousand. That might be kind of unnecessary as we never reach even 500 thousand. So we can change the resolution of this axis by right-clicking it format axis, and we will get this axis options. You see the minimum is 0 on automatic and maximum is 600 thousand. So it can retype it, rewrite it to 500 thousand. And you will see that the axis would change. I recommend keeping the minimum at 0. Theoretically I could make it 100 thousand, but that can be a little bit misleading to the viewer of that chart. You can also change the units. Where are the meaner and minor units, which reflects how many Hominidae these bars will be there to show there. So there's a little bit more options you can play around with that. This is for more advanced charts, but this is the basic format axis stuff. If you by accident, delete the axis. If you don't want it there, then you can click delete and it's done. And then if you now need to change the maximum of the axes, you can click to chart design tab here. Go to Chart, Element and access. And you can see the, you can turn on the vertical axis once again, right-click Format Axis, and then change it once again. 73. Combo charts the easy way - columns and lines in a single chart: On the cells per country sheet, I have this table which shows number of cells per country in Germany, France, and Italy, but also of the global website visits. It'll be great to compare the sales and the website visits. I will select everything. Go to insert. And then if I choose a column chart, it seems that only the websites visits are visible here. How is that possible? The thing is actually all of the columns are visible here. But if you compare the sales which are just like I tense, and the website visits, which are in a 100 thousands, the arches here, but they are so small that they are invisible. So this type of chart is not very practical for that situation. Let me show you how to do it easily in some other way. So we click insert recommended chart. And you can see still no difference. So let's go to All Charts. And the trick is to use the combo chart here, as you can see. And now you can choose which type of Charles will be. So I will choose the first three columns to be a column chart for Italy as well. I will choose the clustered column, which is this one. And website visit is aligned, but this is still not making a difference. The trick is here with a secondary axis. So I will go to website visits and check secondary axis. And voila, we have this chart. What happened is that the these websites visit are not in the same axis, are not compared to the sales, but they have a special axis here on the right side will click to see how it works. So you can see that these relate to the website visit and this axis relates to relate to the individual sales. So this Combo Chart is quite nice and visual to show and compare things that are not in scale to each other, but make sense to be compared. 74. Changing a color of an individual column: Now I will show how to change a color of an individual bar in a column chart. So I will create this chart for Germany sales, total, insert, recommended charts, and I select the column charts. Okay? And if you analyze the data, we see that in March, the cells where particular law when preparing this child representation, it will be good to mark this to change the color of that individual bar. So how you do it? So you click to any of the bars, let say the march bar. And as you can see, all of the bars gets selected. But if you click once again to an individual bar, like March, only this one gets selected. And now you can go home and change the color of the individual one to red. So this is how you can change an individual color over individual column. 75. Creating visually appealing charts with chart quick styles: I created a simple chart here in cells per country. And this chart is showing the results for Germany. I will show you how to modify and adjust the style and Luke of this chart. If you stand in that chart, you side of that chart, you will see two special tabs here to chart design and format. So here you have Chart Styles. These are predefined styles that you can choose, whichever suits your style. These are quite nice and have a different the designs. If you open that there is plenty of those even like a contrasting colors that can be useful for some presentations. You can change the colors of the charts to suit different color scheme or make it monochromatic. In this case, there is no difference in monochromatic if there's just one column. But if I would select more columns for the charts like that, you will see the difference. Although I'll be careful with the monochromatic charts, because if you have many columns on the screen projector, it might be hard for the user to distinguish between them. So I would prefer these colourful versions. Okay, let's use this one. Or you can also do is change the quick layout. This is another option. This is not about the visual, this is about the layout. And it plays around with the data tables, data bars, and different styles of the x's. So you can choose your own quickly without spending a lot of time adjusting it. So I will choose maybe the span, this is the basic one. So I will choose something more advanced like this one, which also includes the data table. In the format up. You can select some other things like a shape fill, CNG colors of the chart. Like a big round or outline or some other effects or anything that can make your chart more beautiful. 76. More tips for professionally looking charts: Let's talk about how to make charts in Excel visually appealing, and also easy to understand for the reader. So here under sales per country, I create one chart for Germany. I go to insert recommended charts. I highly suggest column charts because they are the easiest to read. Then other types like this one. So let's use the column chart and adjusted a little bit. Okay, so first thing, I'm a big fan of this style where there is the axis and the lines and you need to somehow guess, will be devoted to know. Okay, so what is the value here? You need to read this number, go along the line and trying to guess what it is. So much better style is to add the data labels. So I click on this column, click Add Chart, Element, Data Labels and outside. And that's my favorite style. And now I can delete the axis because there is no need for that. Ok. Also, what's important here is to make it clear what the worth it says, such as Germany. So what is it in German? So we say number of sales in Germany. And then I would like to also, at the axis title, horizontal, it will say months. It's good to say which year it is. It would not be clear on the first time. So we'll say 2018. You can put it here or you can just say months and Purdue 2018 intertidal, it doesn't really matter. And I would put Axis Titles, I would also put the vertical axis and click and right. Number of sales is quite important to label both of the axis as my experiences as a thing that people forget the most. If you build a chart, you know where the data comes from and you can read it easily. But for people that see the chart for the first time, it might be harder for them to read. So these are the quick and easy tips how to make beautiful and visually appealing charts. 77. Printing in excel the correct way: Printing an Excel was never easy. But if you know this few tricks I'm about to show you, your life will be so much easier. So let's look at this table employees and let's try to print it. You can go to File and Print or press control PPE. Before you press the Print button, you can check to check the preview how it will look like. And what we see here that the, IT looks great but we are missing to date joined column and it shows there is eight pages. So let's move forward to see what's there. And you will see that the data joint is on a separate pages. So because it didn't fit the page horizontally, it will split with different page. So unless you want to do a lot of cut and glue work with a paper, you need to add, adjusted. And all the important thing with adjusting printing you can find in the page layout. So if I want to fit all the columns to the page, check the width here. And instead of automatic, I will change it to one page. And now I will make sure that everything will fit into one page horizontally. You will see the scale. It's 98%, so it went a little bit smaller. So the letters will be little smaller, but it will fit the page. We can see if it's the case and you will quickly see that we have the date, join now and now on the four pages will get printed. So let's go back. So this is the easy trick. You can also try to fit it on one page two. But this time the scale will be much smaller. Scale will be on the 30%. What would help is to change the orientation to portrait. And then the scale gets little bit bigger, but 46%, but still the letters will be quite small. So if we see the print preview, you will see that the, this is quite small writing, but we fit it on one page. So I put it back to automatic. But I will keep the width on one page. So this is the first important thing. Let's look at travel agency. If you would like to print this fund. You will see that the there are two tables next to each others, the orderlies and the price list, but not B.C.. Only the ordered list. If you go down, you will see that the price list is here on the separate table. But what, we don't want to print the price as we would like to split the orders differently. In another useful tool is hidden here on the bottom right corner, right here. It's called Page Break Preview. If you click that, your page will look a little bit different and you see this thick blue lines showing where the page breaks will be, so which will be different pages of the document, it's even marked. So what you can do here is to move around these lines. If I don't want to include the price, I would put it there. Now the cut will be here. Or I can adjust like where would be the line breaking may be if I if some limit of customers, I can manually set the line here. Also, if I don't want to print a priceless at all, I can drag this line here, and now it's gray. That means the prices will not get printed at all. And now I go back to the normal view. So that's for printing. You also noticed that when I printed the employees, we had there some header and footer. Let's check that. So now it's horizontal. So let's make it the landscape again, by the way, very similar setting you can set up right here. So let's go back to landscape. You can set up, we're also page size from a four to 83 or whatever your printer needs. You can also make the margins smaller. That means the empty space. If I click narrow, then I'll have more space for my table. But these margins get little bit narrower as you can see right here. So header and footer, where you set it up, you set it up in the Insert tab. You go to the right and mine is hidden here. Beyond this text button, you might see this icon directly if your screen resolution is little bit higher. So let's click on that. And you can set up a header and footer for this page. So you can see what is here. You can see what is on the bottom. So you see that there is this page. So if I would like to see just the page number. So Otzi output here just employee list. And let's look at what's on the bottom of the page. So let's scroll down to the bottom. And we have three spaces which we can adjust. Here it says page. So if one looks at page one out of four, I would put page and then I would put here on the header and footer page number, which is a shortcut that will replace the page numbers are in. Each page will be 123, et cetera. Out of in this list is practical to put lie the total number of pages in case you lose some of the sheets that you will know about it. Here is the name of the file. So you can just click here. You can put the file name. You can also put the file AP, which can be practical if if this is stored somewhere in your shared drive and you want people know where to find the original of the printed document. I will just put the file name just for my reference. And here I can put like due date and time when it gets printed by just clicking current date and current time. So this is how I adjust header and footer. So yeah, so this is important things about printing. I hope it will really make your life a little bit easier. You can go back to normal by clicking here to normal. 78. Repeating first rows on each printed sheet: I would like to print this list of employees here on the sheet employees. But there'll be one problem. Let me show you. Let's go to File and Print and check to preview. It works quite well. We see everything. And when I go down to the next pages, agency the header anymore, here, it's missing. And it would be hard for me to judge what will be what will be here. So we need to fix that. Let's go back to Excel. I don't want to copy and paste this heather. Several times a year they would take a lot of time, it will make also filtering a little bit harder. There is a special function for that in Excel to repeat this header on top of every page to go to Page Layout, print titles. And here is repeat rows at the top. So which rows should be repeated on top? So click into this field. And then in our case, we saw just the room number one like that. And then you can click OK and this thing will be printed. So let me see how it looks like. Print. And you will see that on every single page, we have now, the header, which shows me the name of the fields. 79. Printing multiple sheets: If you press print in Excel, on the active sheet gets printed. So let's try it. Let's go to File and Print. Or you can press control P. And on the dispatch will be get printed. But here in the settings you can change it. You can see print active sheets. If you open that, you can select Print Entire Workbook. You can also select Print selection. But we didn't select anything, so that wouldn't work. I will show how the print selection works. Let's go to travel agency and I would like to only print is stable. So I selected thing go file, print and print selection. And without setting anything, only this table will get printed. There is alternative way how to do it, which is practical if you have many sheets and you would like to print only some of them, I will create, let's say, two more empathy sheets to demonstrate that. So the first thing how to do it is to hop, click on the first sheet, then hold shift, click on the last sheet. And all these sheets will get printed, you know, or you can say just cheats 1-2-3. So click and shift the hold Shift. And then if you press control P and go print on the DC-3 gets printed. Also, if you have two prints on only some sheets, you can hold the control key. So I click here, hold the Control key, click here, and click here. So now these three sheets gets printed and not the sheet number one. So this is how you print individual sheets or multiple sheets in Excel. 80. Saving sheets as PDF: Exporting an Excel table to PDF is very similar to printing. I will show you how to do that. You go to File, Export and create PDF document and create PDF. There are alternate versions of that. If you have an older version of Excel, you need to go to File, Save As or save a copy. More options. You need to go to this thing and then you change it here to PDF, which is right here. So this is the alternative version of that. How it will be looked for the actual export. It, it's the same like what will be printed when you press print at the same moment. So if I go File and Print, and I'll get the Print Preview here, and exactly this will gets printed here. I just said print selection, so I changed print active sheets. So everything was set here, will get printed based on the print setting you have here. So if you were to export both sheets to PDF, you need to click the first one, hold shift, and click the second one. And then if you go to export, all of them will get exported. So these settings are important. If you need to save PDF one-by-one, then you need to export them one by one. So PDF is nice, you might need it. So anything you set for your page in this page layout, think about margins, orientation, if it should be landscape or portrait size. Print styles, or any other setting will also affect how your PDF will look like in the end. 81. Thank you, wrap up, next steps: If you like Excel, good news. You just learned something new. If you don't like Excel. Well, Windows, you just learned how to spend less time with. Excel is easy to learn, but it takes time to master. If you practice often, eventually you will master it, save time, maybe get a promotion or better job. And that's for the effort. Before you go. Also, check in my other courses here on the platform. Thanks for watching and bye.