Excel Efficiency Class: Keyboard Shortcuts & More in 40 Minutes!

1. Class Intro: In this class we're going to cover tips and best practices for using exile efficiently. Exile is an incredibly powerful piece of software that makes data analysis visualization quick and simple to do. But by learning how to use Excel in a faster, more efficient way, you can further increase your productivity when working with data. The class covers two major areas. The first of these is keyboard shortcuts, which allow you to bypass navigating through different menus and selecting different options with the mouse, meaning you can complete tasks more quickly. The next area the class covers is a selection of productivity tips and guidance for viewing and editing data. For example, using the text to columns feature to split the contents of cells up and customizing exiles ribbon so the menu choices, requirements, the class is designed so the individual lessons can be watched in any order. Meaning you can skip past any lessons that cover topics you're already comfortable with. The keyboard shortcut lessons. I'll be using this workbook that you can download the follow along with, and complete the class project we've taped. Thanks for enrolling in this class, and let's get started. 2. Navigating Cells Keyboard Shortcuts: In this class we're gonna look at keyboard shortcuts to navigate cells. Now, a lot of these are really quite obvious. It's just using the up, down, left, and right arrows, which is obviously the same across Windows and Mac. But it's worth being aware that using these options will be a lot quicker than using the mouse and sort of manually moving around and clicking. So say we've got a smooth date satellite here just as an example, just going up, down left, right, like this. A quicker way to get around than by sort of hovering over and then using the clicking on each, on each individual cell. So worth being aware that it's useful to get into the hang of using these as much as possible. You can also move forward or why across the worksheet with the tab option, which is the same on both Windows and Mac, just like that. And then we've Shift Tab again the same on Windows and Mac, you can go back the other way. So I left and then go to special, which will take you to any cell. You can do this with control j and Windows and Mac. And it will bring up this menu here. And what we can do is we can enter a reference here, any cell that we want to go to. So let's say we want to go to G5. We just answer that and then hit Enter. And then we'll get taken up there. And that's just one example. But obviously if you wanted to navigate to a cell that's in a much further away part of the worksheet. This is particularly useful. So those are keyboard shortcuts to navigate cells mostly are quite obvious, but worth being aware of all of them and using them as much as possible to save time. 3. Navigating Worksheets Keyboard Shortcuts: In this class is going to look at keyboard shortcuts for navigating worksheets. Now these are really useful, especially when you've got a big dataset and you need to move around in a quick and efficient way without having to scroll along the rounds. So cell by cell, the first OK, we've got moving to the bottom of contents. And let's say run this. So here's satellite three. We can jump down to, hey, we've controlled downer or Windows or command our, down our own Mac now. And then you've got the other way. We have control up our command of power. Next up we've got moving to the right of contents and moving to the left of contents. That is the same except you're using the right and left arrows and nice and simple jump over there and then we can jump all the way back. Who control after I left on Mac. And then we're going to sell a one. Now in this particular case is not massively useful because it would only go small set data ever. Again, this is really useful if you've got a huge table that spans many cells and you want to get back and start with a spreadsheet. And you can do that with Control Home and windows or Kunshan control left barrel Mac. And it'll just take you up that just like that. So again, let's say we're over here. You can do that. We backup in cell I want the opposite to this is going to the bottom right cell. And you can do this with control end or function controlled whiteout on Mac and will take us down. And then going one screen down, going one screen up. This is also useful if you want to get through a large amount of data quickly scrolling through. And you can do that with page down and page up on Windows and function down arrow and up arrow on Mac. And what that will do is it will just take you down like this. Big chunks oil cake you like this knowledge will use. And then you can also kind of do this moving right or left. Oh page down and oh, page up on Windows and punctuation options down our lack and function option up arrow MacArthur. So we'll just show you that now. So, oh, page down crosswise. And then we go the other way. Next up is selecting worksheets. This is useful if you wanted to say hi to few worksheets at one time. For example, move you and selecting adjacent worksheets or worksheets that are next to each other is just shifts clip. So just do that now, say we're on this worksheet. And I want to select these three. I can just do shift click, and it will select all of these three here. And then if we want to select non-adjacent worksheets, so worksheets that aren't next to each other, we can do control-click. So I've got this one selected on control-click head, uncontrolled Click here, for example, much selected non-adjacent worksheets. So those are keyboard shortcuts for navigating worksheets. Really useful ones to understand and use as they will save you a lot of time. I've wrote. 4. Selecting Cells Keyboard Shortcuts: In this class we're gonna look at keyboard shortcuts for selecting cells. In my opinion, these are some of the most useful keyboard shortcuts, and you'll find that you'll probably use them a lot. And often in combination with other keyboard shortcuts you'll learn elsewhere in this course. So first of all, we're going to look at the different keyboard shortcuts for highlighting cells. And all these are, is control shift and then use one of the arrows, whether it's our power down our left arrow, right arrow, depending on which tells you wanna highlight. So just an example. Let's say we're in this cell here and we want to highlight up, we can do control shift up, and it will highlight all of those cells there are in this range. And then we can go, if we're in this cell here, we could do control shift down and it will highlight those ones. And then left to go to there. And then if we were to go right, it will actually take us all the way to the end there in this particular case, because our data ends here. So those are the options for highlighting cells. Now, it's worth bearing in mind. And I put an asterix here with the note that if you don't select control, you can move just one cell when you're highlighting cells. So let me give an example of how it's useful. So say we do shift up and we highlight all of the cells. If we release the control button, we can actually come one cell down just like this. And we can come in not one down and up and down on this. So you can actually move one cell at a time. So here we've gone across to the right rather than going all the way to the upper end of the worksheets. Yeah, that's really useful to be aware of. And often you might use these in combination. So you'll go control shift down and then for some reason you might not want the bottom value or the bottom two values. And then you can easily just come up and select what you want. And then as mentioned, these can be combined with other keyboard shortcuts that hey, we could do Control C, and then we can come over here and then just paste this in. So I've just done that, oh, with the keyboard rather than using the mouse and clicking. So on that note, Quality Control Z, just to get rid of that and come back here. So next up, a good one to be aware of is selecting all, which will select all of your data in a date range. So you can do this with control a on Windows and Command a on a Mac. So if we just click here, control a, select all of those cells here. And then another example, if we are up here and it control, it will select everything that's in this range of cells as well. To select an entire column, you can do control space. So there we go, because we were in a cell here, it's selected all those. And then to select an entire row, you can do Shift Space. Now again, that's the same on Windows and Mac control space and shift space. Now, let's say we were in this cell here and we just wanted to select all the way down here. Another option, rather than doing control shift and down, would actually be to use the Shift and click options. So this is combining the keyboard with the mouse. So do Shift and then click, and then that adds the adjacent cells to the selection. So then we could also hold again and select all those and add all those. We could select this blank row here by selecting that one. And then to add non-adjacent self selection, it's control-click on Windows and command click on Mac. And non-adjacent cells simply means cells, the ons connected in the same way. So this one, Hey, we'll do there and then click there. Click up here. And as you see, different cells are being highlighted. But it's ones that aren't necessarily connected to each other. So those are keyboard shortcuts for selecting cells. Really useful to be aware of and to get very comfortable with using. 5. Editing Cells Keyboard Shortcuts: In this class going to look at keyboard shortcuts for editing cells, which are probably some of the keyboard shortcuts that you are going to be using most. So first off, let's look at the keyboard shortcut for actually editing a cell in the first place. So if you wanted to write something in the cell, and we're going to be using this sort of small table here with fictional menu. And for some of the examples in this class, you could click into it like that, double-clicking in. But the other option is just to use a keyboard shortcut which will allow us to editor. And on Windows that's F2. And on Mac, you see we can press F2 on Windows and then start writing some things. Entrepreneur I pizza here. Now, you've then got the option to enter this. So to do this, you can do this with Anton windows, turn on Mac. And what that will do is it will enter the contents of the cell and it will also take you down one cell. Now if you want it to stay in that cell, say you then going to move over to the next cell, for example, you can do control, enter on Windows and control and thrown Mac, it's the same. And then we've entered it, but we've stayed in the same cell to remove the contents of a cell. It's really simple. It's just delete and now get rid of it just like that. And then to cancel what you're doing in a cell. You can press Escape. And that's the same on Windows, unmapped likely as well. So if we start writing like pizza, and then we realize actually we want to write something else in here. We can just press Escape and it will take us out and stop us from editing. So next up we've got Control Z. I'm Command Z on Mac, which is the keyboard shortcut for undo. So really useful one to know. So let's say we start typing. We can actually press Control-C as we're typing like that and it will take it away. Or we could even enter the contents of the cell and then press Control Z and it will also remove it. The opposite of that, again, really useful is reading. So that's control y on Windows and command y on Mac. So you'll see we redo what we did there and add pizza back in. And then the next one to know is copying. And that's Control C, which you've probably used before on our programs. And we could then paste this and that's a keyboard shortcut which we've also got. So control v or command V0 max, or we can paste that into there, for example. And then now we're just going to get rid of that. Next up we've got copy down our nieces, control the on Windows and control your Mac as well. And what you can do is this pizza cell and then you can press Control D and it will copy all of those down. And the other thing you can do, this sort of copying and multiple selves copyright. And to do that, you could highlight these amoeba over here and then do control are on Windows and Mac. So again, another useful one to be aware of. Let's just get rid of these. Next, we've got cut, which is control X on Windows and command x on Mac. So this will remove the contents of the cell and it will have these flashing lines which indicates that we've cut it. You get those also got those lines for when you copy something like that. Cut this out. And then we're actually going to use Control B and we're gonna paste it somewhere else. So we're going to paste it there. So you can see we've taken all of that are new to that. Now I'm just gonna go back with controls. A. Next up we've got inserting a row, column or cell and a Windows. You can do this with Control Shift plus and on Mac is Command Shift plus, and it'll bring up a menu. So if we just do that Now, we then get the option to insert a column or cell. So let's just do an entire array for example, and then select OK. And you'll see we've brought in an entire writer. Now I'm just going to get back. And then opposite to this is the option to delete a row, column or cell. And this is control minus on Windows or command minus on Mac. So we just do that, then brings the same sort of menu except it's for deleting. So again, although entire again, select OK. And we see that that virus disappeared. And I'm just gonna go back with control Xena. Next up we've got fined and you can bring up the find menu with Control F on Windows and Command F on Mac. So if I just do that now, and then this gives you the option to find something in the spreadsheet. So let's just type in Menu for example, and then select Enter. And it will take us to that cell there. That's got menu. And if you've got multiple instances of this in your spreadsheet, you can also define DOT and it will show you what they all are going to exit. Now, next up we've got replace. And this is to bring up the replacement you with Control H on Windows and Control H on Mac. So if we do that, and let's say we want to just replace menu with food. And then I select Replace all food has been replaced there. Or management replacer foods. So all dominate one replacements. And I'm just gonna get back Control Z. And then lastly we've got the spell check option, and that will just bring up the spellcheck set of options, which is similar that you've seen on other programs probably. And on Windows as F7 and its function F7. And then you just get a selection of options for spell checking the contents of your worksheet. So those keyboard shortcuts for editing cells, as you can see, some of them you may be aware of already, some of them you might not be aware of, but who really used for ones that you are no doubt going to use when you're using Excel. 6. Navigating Between Worksheets Keyboard Shortcuts: In this class we're going to look at three keyboard shortcuts for navigating between worksheets. So while she might not always have multiple worksheets in your workbook, often you will. And knowing these keyboard shortcuts is a great way to just save a little bit of extra time. So first off, we've got moving to the previous worksheet or the worksheet to the right. And that's what control page down on Windows and functional control down are on Mac. The opposites that is moving to the next worksheet, which is the worksheets and the left of the one that we're in currently. And that's with control page up on Windows and function Control up arrow on Mac. So if we just run through those examples now, we can move over to the right like this, and we can move back to the left like this. Lastly, we've got creating a new worksheet altogether and a Windows. You can do that with shift f 11. On a Mac, it's function shift f 11. So we'll just do that now. And you'll see we get a new worksheet just pop up here, which is Laughlin quicker than right-clicking and inserting the worksheet manually. So those are three keyboard shortcuts for navigating between worksheets. Again, you may not use more time, but also useful to have in your toolkit. 7. Formula Keyboard Shortcuts: In this class we're going to look at formula keyboard shortcuts. So to get started, a good ones be aware of is the keyboard shortcut for inserting a function, which on Windows is shift their free and on Mac is function shift that free. So if we just do that, shift the free, it just brings up the insert function options and there's various different options here you can select a category or functions that you're interested in, search of hay and, and you can select the function itself down here. Next up we've got the autosome keyboard shortcut. And to illustrate this, use this data here where we've got a few items and their prices. So if I was to go in here and do alt equals on Windows or command shift t on Mac. We get the sum function popping up automatically, which is really useful, is identified that these are the three cells we had. One adding a making, just hit enter and we get our value there. Next up we've got toggling absolute and relative cell references, which is FOR on Windows and Command T or function F4 four on Mac. Now, I will just illustrate what the absolute and relative cell references are briefly, as well as showing this keyboard shortcuts. So let's say we have a discount on a nice store, the moment of 20%. So I'm just going to write down 20%. And then we want to calculate what our discounted prices are for these values here. So here we are going to have discounted price. So that will be as obviously the T-shirt price minus 20% of the price. So take that value there again, times it by the discount, and then close that off, so we get €12. Now if I was to simply drag this formula down, it won't work properly. And the reason is, is because this cell head be 17 should actually be here and it's moving down. And that's because we've not locked ourselves using the F4 option. So what I'm gonna do here on this, on this option here be 16. I'm going to lock that in place. And that brings up the dollar signs here. Now you can you can lock down just the row number. You can lock down just the column matter or you can lock down everything. In this particular case, it would work if we've locked just the row number or everything. So I'm just gonna leave it locked with everything. Hit enter. And then when I drag this down now, you'll see that the discount is applied. We do get 20% off at 25 Euros or 20, 20% off that 10-year-olds, so eight. So that's working correctly because this is locked in place. And that brings me on to the last keyboard shortcut here, which is to evaluate partial formula, which is F9 on Windows and function f nine on Mac. Now, this is useful for identifying what the underlying values are and to see how you formula actually works. So we can just highlight these, this section here of the formula. Press F9. And then we see, and that's because 3€3 is what is calculating. So it gives you that value there. And this is particularly useful if you're working with a very complex formula or if something's gonna be wrong and you need to figure out what's what or if it's a worksheet that you've been passed on from someone else and you're not sure how formulas would build the Evaluate Formula keyboard shortcut is a really useful one to have in your toolkit. So that concludes the formulas keyboard shortcut class, as you can see, not allowed here, but useful to be aware of these. And they may well come in handy. 8. Formatting Keyboard Shortcuts: In this class we're going to look at keyboard shortcuts for formatting. So first off, a keyboard shortcut which you really want to use is control one or command one on a Mac. And what this does is it brings up the full list of Format Cells options. So if we just do control one, you'll see here I've got a whole load of options. I can use Number, Currency, accounting, datetime, et cetera, formatting. And there's also various different options, font, border, fill, et cetera. So just selecting control worn or command one on a Mac will bring up this whole range of options. And that's a really useful one to be aware of. Next up we've got adding or removing bold formatting which is controlled by on Windows or command beyond Mac. So say for example, this text here, it's currently in bold. I can just select control be there and it will remove it. Or I could add it again. We've controlled B and then adding or removing italics formatting. This is just control lie on Windows or command i on Mac. So I'll just do an example of a control I control law. You can see I can switch it on there and off. And then adding or removing underlying formatting. That's just control you so we can do that their control you and you see the little lines appear underneath. And I can take that off as well. Then we've got some that are useful for numbers. So I'm just gonna write 50 here. And if I do control shift and then the percentage symbol, that we'll turn it into pounds because that's how I've currently got my currency set up. And then I could also change this to percentage by doing control shift and the percentage symbol. And so there we go, 5 thousand sent to hide selected rows. It's control nine. So let's just hide this row here. So control nine. And then to hide selected columns so we can just hide all of column a is control 0. And these are the same on Windows and Mac. So just like that, now I'm just gonna go backward Control Z. And there you go. So that's some formatting keyboard shortcuts, ones that you're going to use quite often, and they're really useful to have in your toolkit. 9. Extra Shortcuts and Best Practices: In this lesson, we're going to look at a few extra shortcuts and best practices when using the shortcuts that are also really useful. So first off, to open a completely new workbook in Excel. You can do this with control and in Windows and command and in Mac. So that's a nice, easy one to remember. And for new, and it will just bring up a new workbook just like that. Next UP, saving a workbook Control S and command S in Mac, also fairly easy one to remember and really useful, especially if you're working on a large spreadsheet for several hours and you don't want to lose your work. You can also bring up the save as options and open workbook options. F 12 and Command Shift S on Mac for Save As and control. And command I've opening a workbook on Windows and Mac respectively to for hiding and showing the ribbon. This is useful especially if you're working on a slightly smaller screen and you want to dedicate more space to the actual cells in the spreadsheet. You can do this with control F10 on Windows and command options are on a Mac. So that will just hide and show that we haven't just like that so we can hide it away and then bring it back. And then if you want to enclose XL, That's OK, F4 or windows and command queue on Mac. And then a few extra useful wants to be aware of two is creating a table. And so we've just got this data here. And if you just click into here, you can just do Control T on Windows or command z on Mac. And it will just bring up the table option like this. And you can then just select OK, and then instantly your range of data goes into a table. Just go backward Control Z. You could also just create a filter for your data range. So you can do this with control shift L on Windows or command shift f on Mac. And that will just create a filter just like that. So you can now see we've got these options to filter. And then lastly, another really useful one. Let me quickly want to visualize some data is the Creating a char keyboard shortcut. And you can do that with Alt F1 on Windows and functions option form on Mac. And that will instantly put your data into a char like that. So a really useful one to be aware of. Now it's also worth mentioning that with keyboard shortcuts, you're not going to be off to remember all of them and some of them are going to take a little while longer to remember. So a good thing to do, in my opinion, is to kind of make a list or think about the ones that you want to learn first and that are going to be most useful for you use in Excel. And start off with those. 10. Pasting Formatting to Save Time: In this lesson, we're going to look at how you can use paste formatting to copy the formatting from one or more cells onto ourselves. So we have phoenix store sales data here. However, the Q3 and Q4 sales, sales are currently blank. So I'm going to enter in the values here. What you'll notice is, is that the formatting is different and we don't have the dollar sign or the comma. So to change that, we could navigate to the formatting options and select the exact formatting that we want. So it's the same but quicker and easier option is to simply copy the cells, so highlight them and Control-C and then navigate down to these cells. Right-click, go to paste options, and then go along to formatting and select that. And then you can see the formatting has been copied, but the values haven't. And now all of ourselves are formatted consistently. 11. Filling Formatting to Save Time: In this lesson, we're going to look at how you can use the fill formatting only feature to quickly change the formatting of cells to get them exactly how you want it to be. So we have our sales data over here. These two cells are formatted correctly, however, these ones on, so there's two options that fall outside of filling the formatting. One would be to manually change the formatting and the other would be to copy the formatting of the cells and paste it onto these two are the quickest situation here would just be to highlight this cell, click on it, and then drag this down. And then go to this option here, autofill options and select Fill formatting only. So the numbers our retained as they were. However, the formatting has been copied from this cell. 12. How to Transpose Data: In this video, we're going to look at how you can transpose data in Excel. And what that means is switching rows to columns and columns to rows. So we've got some data here. It's sales data for various stores in Arizona. And at the moment, the data is organized so that the stores are in columns. And then the sales data, wherever it's ki one through to Q4 or the total sales figure is in the virus. However, it's probably better to have this data organized whereby the stores are the rows and the quarters are the columns. So what we're gonna do is we're going to highlight all these cells just like that. And then you can copy this with Control C. And then if we right-click and you have to transpose the data in a different place, otherwise it will overlap and not work. So we're gonna go down here, then we're going to right-click and select this option here. And you can see it says transpose and you get a preview of how it's going to look. We can click that one and then the data has been transposed. So you can now see that we've got the Q1, Q2, Q3, Q4, and total sales as columns. And the store locations on our ads robes. And then we can just remove all this and delete it. And then we had our data swapped over. Just to show you. We can also do it the other way, so S And then we can come down here, right-click and transpose again. And we've got our data, how yeast to be where the store locations are in columns, and the sales data is inverse. 13. Collapsing and Customizing the Ribbon: In this lesson, we're going to look at the collapsing and customizing the Ribbon. Now first off, we can look at collapsing the ribbon. Now, collapsing the ribbon is very simple. You just right-click up on the tabs here and you can select Collapse the ribbon, and then you'll notice the Ribbon disappears, it's collapsed. And what that does is it gives us more space to view data in the actual spreadsheet sells itself. And that's useful particularly if you have a small laptop screen and you want to dedicate as much screen real estate to your spreadsheet data. You can then easily revert this just by right-clicking again and selecting claps the ribbon again and the ribbon will come back just like that. Next up, let's have a look at customizing the Ribbon. Now, customizing the Ribbon is useful if there's some feature you want to use in exile very often and you want quick access to it. And maybe you just don't like the white ribbon is organized so you want to change things about to suit your workflow and what you do in Excel. So again, come up here, right-click and we can select, Customize the Ribbon. And then we have the option to move things around. So we can, for example, move data before formulas. And if we did that and select OK to have now swapped around. Something else you can do is you can actually add features into the ribbon. So let's, for example, at the pivot table feature into the home tab. So what I'm gonna do select pivot table, and then we have to specify a new group is gonna fit within. So we specify a new group and I'm just going to rename this now. Pivot table, select, okay? And I'm just gonna move this up right to the top of the tab and then add the PivotTable feature in. There we go. And then select OK. And now what you'll notice is in the home tab, we've got this option of PivotTable here, so it's more accessible than it would otherwise be. Something else you can do when customizing the Ribbon is actually add a whole new tab. So let's, for example, create a new tab for print preview and just an example. So we're going to select this one and then select New tab. And we're going to rename this new tab. We'll just rename it prints. Okay. And the New Group, again, we'll rename this print k. And then we'll add this feature. And we'll select ok. And then you'll notice, hey, we've had a new tab added in prints. And if we select that, we've got the Print Preview and print feature. So this just shows how you can customize the ribbon in Excel to suit the way you work and to get it in a way that's going to make you more productive and efficient when using Excel. 14. Freezing Panes for Easier Viewing: In this lesson, we're going to look at how you can freeze rows and columns to make it easier to scroll through and view data, especially when you have a large data range. So we've got sales data here, and in column a there are various stores. And then from column B, we've got the quarter one sales 2017, all the way through to column Q, where we've got the quarter for sales for 2020. And as you can see, when we begin to kinda look around this data, it becomes quite unclear which of the cells relate to which store and which quarter as the first row and the first column disappear. But we can change that and do this if we go to the View tab, and then you can go to the Freeze Panes options. And first of all, we're going to have a look at freezing the top row. So we select this one here. And then what you notice is when we scroll down the first row with all the column headings in, stays in the same place, which is really useful because we've still got that information, even if we're kind of right at the bottom of the table, when that otherwise be asked of you. The other option you can do is to freeze the first column. So if we select bat, you'll now notice that although the top row is no longer frozen, if we go to the right, the store location has remained in the same place. So that you can see it's gone from a to F. A is kinda locked in that first column. Remove back over. Another option you have is to freeze both the first row and the first column. So to do that, we're going to unfreeze What we have locked currently. So if we were to freeze panes and then unfreeze panes. And then by selecting cell B2 for with our cursor. And then if we go to freeze panes and then Freeze Panes again, this one here by this column and this row now locked. So when we scroll around it, go down, the top row is locked. And then if we go over to the right, the first column is also locked. And you can see now it's a lot easier to see what these cells relate to in terms of the quarter and store. Now by the first column and the first row a locked. 15. How to Use Text to Columns: In this lesson, we're going to look at the text to columns feature in Excel. Now, text to columns is a feature that allows you to break up the contents of cells into multiple cells. And it's useful in an example like this, where we've got very store locations and we've got the city with a comma and then the state immediately afterwards. So Anchorage, Alaska, Atlanta, Georgia, etcetera. Now, it will be better for us to have this information in two columns where we've got the city in one column and state in another column. And doing this manually by entering into each cell and cutting out the state and moving things around. Not very efficient. And also doing formulas is also more time-consuming than just using the built-in text columns feature. So to use text to columns, first of all, we need to highlight data that you want to spit out. Then if you come up to the Data tab up here, and then just navigate to text to columns. Now, you're presented with two options, Delimited or fixed width. D Limited is what we're going to use. Characters such as commerce or tab separate each field. And fixed width, on the other hand, is when fields are aligned in column two spaces between each field. And that's not going to work here because the contents of these cells are inconsistent in terms of the amount of characters they have. But what is consistent is that they all have this comma separating the city in the state. So we're gonna go with D Limited and you get this preview of the selected data here. Then we're gonna go next. Now, we need to specify the delimiter. Now, in our case, it's a comma. So we're gonna select that and unselect tab. And you'll see that you already get this preview of how it's going to look. So you can see that we're getting the result of having the city in one column and the state in the next column. There's also the option to specify space, or you can even specify other. And if you had something else, maybe it was a forward slash or whatever separating your data, you could use that, that too. Next up, we're gonna go on the next button. And then here we can specify where we want our data to be split into. By default it's gone with a2, but we're gonna change this to this cell here. So we retain this and then it moves into these cells here. And you can also specify the data format. Go to Text general date for example, we'll just leave it as January, which is fine. And then we just specify Finish. And there you go. So what you can see is that data has been splits out, so that now we've got the city in this column here and the state in this column here. And it's a lot more readable and a lot more easier to understand. And yeah, that's just one example of text columns, but as you can see, it's a very powerful and easy-to-use feature. So it's a great ones that have in your toolkit for doing things in a productive and fast way. 16. How to Create Hyperlinks: In this lesson, we're going to look at how you can use the hyperlink function in Excel to make URLs clickable. So we have a sample list of URLs here. It's 13 URLs on the TripAdvisor website. So we've got the hotel's URL, rentals, URL, the attractions, URL, et cetera. And the page name is also in column a. Now, at the moment, when we click on these URLs, the browser doesn't take us to these pages, and that's because they're not clickable, but we can easily change that by using the hyperlink function. So if we open up that there's two parts to this function. The first off is link location. And for this part, you can either or you could type it out. You can open up speech marks like this and type it all out, but we're not gonna do that. So first off, you have to specify during location, so we'll just click on this cell here. So bt. Now, the friendly name option is optional. In this first example, we're not going to use it, but we will show that in the next part of this lesson. So now we're going to close off the function. And if we drag this down, we now have all of our links and you can see the formatting of them is changed. And if we click on one of these, you'll notice it takes us to the page and we get the TripAdvisor Hotels page. And then if we were to click on the rentals page, again, that takes us there as well. Now. So that's pretty simple. Now let's have a look at using the friendly name part of the function. So for the friendly name, again, you could type this out. So we could put, for example, hotels here. But in this example we're just going to click on cell a2 to the hotel's name. Hit enter, and then we're going to fill these down. And then now you can see our links are no longer the URL, but that's just the name of the page. So let's click on another one. Just an example where we gave insurance. Again, the page loads up and we've got our TripAdvisor insurance page. So that's just an example of using the hyperlink function to make URLs clickable in Excel. 17. How to Remove Hyperlinks: Now often when you have links in your spreadsheet, you want them to be clickable. So for example, these links here, if I was to click on the rentals page, the rentals page will appear. But there might be other times where you want URLs in your spreadsheet, but you just want them in for informational purposes and you don't want them to be clickable. And what you can do to move the hyperlink is just to highlight the cells, like click and then select, Remove hyperlinks down here. And then you'll notice all of these cells are now just text and they're not clickable. And this is better if you only want them in there for information and you don't want to be accidentally clicking on them and opening up the browser. 18. Removing Blank Cells: In this lesson, we're going to look at how you can remove blank cells in a range of data. So we have a list of different vegetables down here, and then we have their price per kilogram in column B. And as you can see, there's various blank spaces. And you can end up with blank spaces if you've removed values or if that's just an error in the way that your data is formatted. And particularly if you've got a large data range, this one is quite small just for illustration purposes, but when you go launch date range, it can be quite time consuming to sort of manually identify this type of thing and remove them separately. So what we're gonna do is highlight all of the cells where our data is. Then we can press control G and bring up the go-to money. And then if we select special, and then we're going to select blanks, this option here. And then we're going to select ok. And now you can see all the blank cells have been highlighted in our data range. And then we just have to press control minus. And then we get the option to delete these cells. And the default one is shift cells up. And that's fine. So we're just going to select, okay. And then you can see the blank cells have disappeared and we're left with a nice clean dataset with everything all in order and no blank spaces. 19. Next Steps: Thanks for taking this class and I hope you found that helpful. Let me know if you have any questions and you can find the class project instructions below to test what you've learned. If you want to learn more excel, check out my other classes and follow me. So you're the first to hear about my news classes. Thanks again and see you next time.