Excel Tips and Tricks

0:01(gentle music) I used to do a lot of Microsoft Office tips and tricks demos in my previous job, and so I thought it would be fun to share with you some of my favorite Excel ones in this skill. Note though, that although these tips aren't particularly super advanced, I do assume that you have a good working knowledge of Excel. And I should also point out, if I show you the list here of what we're gonna cover, I would estimate that, oh, I don't know, about 3/4 of what we go through here is mentioned somewhere or other in the complete Excel course.

0:35And so if you've stumbled across this skill as part of that course, then don't be surprised to see some duplication. Make sure that you download the supplemental file so that you can follow along. And once you've done that, let's get started.

0:06Let's start off this skill then with a look at some tips and shortcuts for speeding up data entry in Excel. And we're gonna see a mixture of keyboard shortcuts, and also some features or options that you may or may not have come across before. And I will show you a summary at the end for your reference. You can see on this spreadsheet that I'm capturing some figures, probably, for January, February, and March. And you will already know that if I've already got January or Jan entered in there, then I can use the full handle to continue the other months of the year, because that's a list that Excel understands, January, February, March, April.

0:39It also knows the days of the week, Monday, Tuesday, Wednesday. But what about this list here? So let's imagine that these are the sales regions in my organization. You know, this is a custom list of a combination of counties and areas in the south of England, but that's how we have divided up our customer base into different selling regions. And I often need to input this same list in the same order for my spreadsheets. So it will be sensible to save that as a custom list alongside the months and the days of the week so that I can quickly reuse it another time.

1:10So to do that, if you go to File, and then Options, and choose Advanced on the left hand side here, and then scroll almost to the end. Yeah, there we go, Edit Custom Lists. And you can see that, sure, it already knows the days of the week and the months of the year, but I'm going to bring in some information from some other cells to create a custom list. And because I already had those cells selected, it's got the reference there, but if you hadn't selected it first then you could just click in there, and then click and drag. But let me click Import, and that will give me all of those entries as a new list. So let's click OK, click OK again, and let me just delete that so that I can try it out.

1:45So the first one on the list was Greater London. Good. And if I just fill that all the way down to row 14, perfect. Similarly, it'll work also going sideways, but let me just undo that. So that has saved me a bit of time, and that'll persist for future spreadsheets as well. So that's a change that I've made to the custom list for my Excel environment. And as an aside, I can also use this custom list to sort. So if I just go to this Data tab down the bottom here, you can see I've got a great big block of data here. You know, the date, customer name, the region of the sale, or whatever.

2:18And of course, we know that this column here, well, I can sort it alphabetically, can't I? So that's how it's currently sorted, isn't it? A to Z, so Berkshire, and then, you know, Devon, and then East Sussex. But what if I want it sorted in the order of that custom list? Well, if I click the sorting drop down here and choose a custom sort, well, one of the options is not A to Z or Z to A, but a Custom List. So there's that same little list of custom lists that we've seen before. So let's click OK to that. Click OK again. Perfect. I've got all of the Greater London entries together.

2:49And if I scroll down, yes, that's the order of that custom list. But let me go back to the Sales tab. And let's imagine I've got some figures to fill in for these first three selling regions here for the first three months of the year. And you'll notice that I have selected the block of cells first. So this is my next tip for data entry. Select the area you are gonna enter the figures into first and then start typing. So I'm just gonna put in some random numbers here. And you can see that as I get to the end of the column and I'm just pressing Enter here, it takes me to the top of the next column.

3:19Because so often I see people type a number, press Enter, type a number, press Enter, and then they have to either use the arrow keys to go up to the top of the next column or click with the mouse. But to select the cells first, when you press Enter, it'll just take you through the whole selection. Or if you prefer going sideways, you can press your tab key and that'll take you along a row at a time. But that does also remind me of another tip. So you will know that if you are putting in a number here. So let me just make an edit to this one and press Enter. You'll know that when you press Enter, the selection moves down to the cell underneath. And a lot of the time I think that's sensible.

3:51But if you find yourself getting annoyed by that, you may want to consider changing the direction. Let me show you what I mean. So if I go to the File menu, and choose Options, and then into Advanced, you can see here, after pressing Enter, move the selection down. Yes, that's the default, but you could change that if you needed to. Or if you didn't want it to move at all, you would uncheck that box. But I would just choose cancel for now. Let's imagine for the rest of these cells we've only got estimates, and in fact, we're just going to estimate a value of 450 for every cell in that selection.

4:23So press Ctrl + Enter to get the same value into every cell. And I have to say, I find this very useful to be able to just, you know, quickly blitz some numbers into a spreadsheet here when I'm testing my formulas. So let's just for example, press Ctrl + A to select that block of data, and then Alt + Equals to put in some totals at the bottom there. And let's just pop in a total row there, make it bold. And if you were just quickly populating your spreadsheet in order to test your formulas, then there might be an alternative method of doing that which you might prefer.

4:54And that's to be able to put in a random number. So again, I've just selected my block of cells, and I'm going to use a function this time. So it's going to be =RANDBETWEEN, and that's got two arguments. So, between what numbers? So I want to have a random number between the value of 10 and 500, close the bracket. And because I want that same formula in every cell in the selection, then I press Ctrl + Enter, bingo, I've got a whole load of random cells there, perfect for just populating a spreadsheet so I can test my formulas.

5:25Let me go back to the Data sheet this time, and I'm just going to delete one of these values in here. And let's imagine I want to enter in the same thing here as the cell above. Now, for this particular example, you will already know that I can just start typing and it will have a look at the entries in the list above itself and make a suggestion. But if that doesn't work in the scenario that you need to repeat the cell above, then the keyboard shortcuts that you need is Ctrl + D. That just copies the contents from the cell directly above the selection. But actually, Ctrl + D is also useful when you're working with graphics.

5:56So let me just pop in for example, a quick square. And in fact, hold down your Shift key to get a perfect square rather than a rectangle. And when one of these shapes is selected, if you press Ctrl + D, then it'll duplicate the shape. But interestingly, if you then move the shape into a particular position, so lemme just pop it next to that original square. Now when I press Ctrl + D, can you see that it's also repeating the position and the spacing for each subsequent shape, you know, for each subsequent press of Ctrl + D. So that's quite interesting. But now let's think about speeding up the way we can enter in data onto different sheets.

6:32So for example, if you look down the bottom here, I've got three sheets, Expenses, Targets, and Transactions, where actually I need the same headings and the same formatting on all three of those sheets. So I can input something onto all sheets at the same time by selecting the relevant sheets first. So I'm just gonna hold down my Ctrl key in order to select all three of those sheet tabs. And then, well, let's pop in some headings. So let's use our new custom list that we created, so Greater London, and repeat that all the way down to there. Perfect, let's just adjust the column width, and maybe put in a heading there for the month, and fill that across, and add some formatting.

7:08You get the idea, you make it look the way you want it to look. And then if I click out of this grouping of sheets, let's just click on, for example, this Timesheets, and then flick back through Expenses, Targets, and Transactions, you can see that the things that I typed while those three sheets were selected have now appeared on all of those sheets. So that is a great time saver, and also really good for consistency. But actually, thinking about that Timesheet sheet, I've got a couple of other shortcuts for you here. So let's imagine this is where I am capturing work that I've done on a freelance basis, and I bill by the minute at a rate of 100 pounds per hour.

7:41So what I do is I fit in the dates that I did the work, and the client it was for, and what I did, the start time and stop time. And then the spreadsheet then works out the total time I spent on that task and how much I can invoice. So you already know, I'm sure, that we can enter in today as a function to put in today's date, but if you think about that in the context of this timesheet, well, I don't want this date to change to tomorrow tomorrow, you know, the date that I want to enter in here needs to be a static date. So the keyboard shortcut that we need for that is Ctrl + Semicolon, that just puts in a static date.

8:15So lemme just put in the example there of another task. And it's the same with the time, I need it to be a static time. So rather than a function, so equals now would be the function to put in a dynamic input of the current time. But if I just want a static time of the time now, you know, the system time, it's Ctrl + Shift + Semicolon. So let's say I started the task at 25 minutes past 5, that's the current time. And let me just put in that again. Obviously, it's exactly the same time, so it's gonna give me a total of 0, but you get the idea.

8:46And finally, let me just go back to that Data sheet. I mean, you will know that if I, you know, click on a cell, and then, you know, click Copy, that's sent on my Clipboard, and, you know, I can paste it somewhere else. And of course, if I copy something else, that is the most recent thing on my Clipboard, that I can paste something else, and maybe I then go ahead and copy this blue shape, and, you know, I could paste that somewhere else. Or maybe I go ahead and copy another name. But you'll be used to using copy and paste in the context of it's only the very last thing that you copied this is being available for pasting. Well, I've got news for you, because copy and paste, of course, is a great time saver, but we can make that even more valuable if we pop out the Clipboard.

9:23So on the Home ribbon tab, if you pop out the Clipboard just here, you can see, it's showing you the last few things that you've copied. So those are the different names that I did just now. There's that blue shape, so if I do want that name that I copied a few steps ago pasted into that cell, then I can just click it from the Clipboard just here. And you'll also see this works across different applications. So you can also see other things that I had copied from OneNote and even a web browser window. So I think this must have been when I was doing a peer review for one of my colleagues. So this clipboard is generic to your whole environment across different applications, but it can be really useful to be able to quickly paste in something that you copied a few steps ago.

10:00But anyway, here's your summary, which I'll leave showing for a moment or two so that you can freeze frame to note down anything if you need to. But for now, I hope this has been informative for you, and I'd like to thank you for viewing.

0:00(bright music) I do realize that you're more than capable of using your mouse to navigate and to select parts of your Excel spreadsheet, but this is actually an area where some keyboard shortcuts can be very helpful. Let me show you. You already know that we can press Control + A to select a block of data, but within that selection, if you now press Control + full stop or the Control + period, then you'll see that you navigate to the four corners of the selected block of data.

0:32And I think this is especially useful when you've brought something in from somewhere else, and you need to get a handle on the size of a large dataset or if the data isn't selected, so lemme just click in the middle of it there, you can use the arrow keys. So Control + arrow will take you again to all four corners of the block of data there. If you want to move between different sheets in your workbook, so look down the bottom here, you can see I've got different Sheet tabs. Then try Control + Page Up to go to the previous sheet or Control + Page Down to go to the next sheet. Again, useful shortcuts.

1:02or if you've got multiple workbooks open, so you can see, as I hover over the Excel icon here, I've got two Excel files open, then try Control + Tab to switch between them. And in fact, talking of multiple workbooks, you'll see on this data sheet here, I've got this great long list, but that other file that I had here was the raw data. Maybe I want to compare these two workbooks side by side. So on the View ribbon tab, you've got an option there, View Side by Side, and that'll do exactly that. And the default is when you choose that option, View Side by Side, when you scroll, can you see that both of those windows scroll together?

1:38So that's exactly what I need for this example because I'm trying to compare this list with this list or whatever. But if you don't want synchronous scrolling, then look up at the View ribbon tab there. It's that button just underneath the one we chose to view the windows side by side. But if you turn off Synchronous Scrolling, then only the active window will scroll when you roll your wheel. So you'll have to click the window first and then roll the wheel to scroll that particular window/ To turn off View Side by Side, then click that same button again and let me just use Control + Tab to take myself back to that other workbook.

2:09And while we are talking about navigation, you might be used to using named ranges in your formulas, you know, perhaps for naming a LOOKUP table for example. But it can also be very useful for navigation purposes. So let's imagine these cells here. You know, these are our expenses. I regularly need to go to these particular cells. So I'm going to name them so I can quickly get to them from other parts of my workbook. So select the data first and then up here in the name box, let's just type in Expenses, and then press Enter. And that means when I'm somewhere else in this file, I can click that dropdown to quickly go to that block of sales that I named Expense.

2:47And actually, if you do like the idea of using named ranges, did you know that we can get Excel to automatically create them for us from a selection? So let's just select this block of cells here, and then go to the Formulas ribbon tab. And there's this button, Create from Selection. So I'm going to create some names from values in the selection using the left column because I want to be able to quickly go to these different regions and to their quarterly figures. So I'm going to uncheck the top row 'cause I'm not that interested in seeing them by month. It's just the rows.

3:17So when I click OK, and now I'm somewhere else in my spreadsheets. Let's just go to this different tab down here. Let's imagine I now want to see the Wiltshire figures. I can click the dropdown and go straight to Wiltshire, and it navigates me to that named range. And you can see from the dropdown there, all of these extra entries from those named ranges that were automatically created. If you want to tidy up all of the named ranges that you have created, then, again on the Formulas ribbon tab, if you click Name Manager, that's where you'll see this vast array of named ranges that have now appeared. Well, this is where you could go ahead and delete them or edit them or manually create new ones.

3:50But let me just close that for now. Let me go back to this data worksheet, and let's talk a little bit more about selection. So as we did right at the beginning, Control + A will select a block of data, but Control + A again will also select the heading if you were clicked in a table. So I don't whether you noticed, the first time I pressed Control + A, it didn't include the headings there, but Control + A again does. And then Control + A again will select the whole worksheet. But also if I just click away to deselect, Control + Space Bar will select the column.

4:20Shift + Space Bar will select the row. And again, you can see that the first press of the combination only selects the row or column within the active block of data. So you'd have to press it again to select the whole row across the whole worksheet. But let me just select the column again, so Control + Space Bar. With that column selected, I can now press Control + Minus to delete it. It's gone. Control + Z, of course, is an undo. So I've got it back again. This really is keyboard shortcuts galore, isn't it? But rather than deleting the column, if I wanted to hide it instead, then that would be Control + 0 Look up at the column headings.

4:54You can see that column C is missing there. But let me just do Control + Z to reveal it again. And don't forget, I will summarize all of these keyboard shortcuts for you at the end. So if you think I'm going a bit quickly, don't worry. But other than deleting or hiding a column, we might also want to insert a new one, so that's Control + Shift and then Plus. To insert a new column, let's just do Control + Minus to delete it again, and it is similar for rows. So let me do a Shift + Space Bar to select the row. Control + Minus to delete, Control + Z to undo. That's the same as we saw before, but to hide the row, it's actually Control + 9 rather than Control + 0.

5:31But let me, again, just do Control + Z to undo. Phew, you'll be pleased to know the next tip does not involve keyboard shortcuts. But let me, for example, just create a couple of blank rows in this data. And if you have worked with lists and whatnot, you'll know that blank rows in your data are a bit of a nuisance. So it can be useful to have a shortcut to find all of the blank rows in your data. So let me select the block first with a Control + A, and then go to the Home ribbon tab and click this Find & Select button. And I do urge you to have a browse through some of the options, not only on this menu here, but also in the Special dialogue box, because one of the options here is to find and select blanks.

6:10So if I click OK to that, you can see that those two blank rows I just created are selected. So now I could press Control + Minus to delete those two selected blank rows. So that was a very quick way of selecting all of the blank rows and getting rid of them. But finally, we can also combine those navigation shortcuts we saw at the very beginning of this video with the Shift key to select. Let me show you what I mean. So we already know that Control + Down Arrow takes you to the end of the column that you'll clicked in.

6:40But let's imagine I'm clicked here, and I want to select all the way from that cell to the end of the block of data. So Control + Down Arrow will take us there, but if we were to hold down the Shift key while we also did Control + Down Arrow, it will select all the way to the end. And again, if I keep the Shift key held down and use the Right Arrow, it'll select extra columns to the right with each extra press of the Right Arrow key or press the Left Arrow to go back again or press the Up Arrow to work my way upwards again from the end of the selection.

7:12And you might think, "Well, you know, this is a bit academic. Aren't we just showing off trying to select our spreadsheet with the keyboard because of course we do have a mouse?" But sometimes for very large datasets, if you were trying to select, in fact, let me just go back to the very top of this. You know, if you did want to select just from here all the way down to the end, you know, your mouse can run away with you, and it's hard to know when to stop. So it can be very useful to say, "Well, let's use the keyboard to select in a much more controlled fashion." So Control + Shift + Down Arrow will select from that cell all the way down to the end of that column.

7:42As before, here's the summary slide for your reference so that you can grab any shortcuts there of particular interest to you. But for now, I hope this has been informative for you, and I'd like to thank you for viewing.

0:06The tips that I've grouped together in this video, all concern the appearance of your spreadsheet, the formatting. I've got a nice keyboard shortcut to start us off, and that is if you select some cells and then press Control + Shift + 7, it'll give you a border around the outside of the selection. And also, before we go any further, do you remember a couple of videos ago we used that rand between function to give us random numbers in here? And you might have noticed that these keep on recalculating themselves, which might be a bit distracting.

0:37So you think to yourself, "Oh, well what I'd really like to do is to cut those, get rid of those, and then paste them just as values. And you think to yourself, yeah, I'm sure there's that option available. So let's go ahead and choose cut. So you click the bottom half of the paste button, all optimistically, and you think, "Well, I'm going mad." I'm sure there's supposed to be far more options there under paste special, but they don't seem to be available. Well, don't let this catch you out. That vast array of options that you're thinking of will only be available if you have copied the text. So having clicked copy and now click the bottom half of the paste button, that's what gives me all of those other options.

1:10So I think, yes, what I really want is to paste the values rather than those formulas that keep on giving me new random numbers. And then I get the choice to either paste the plain values or to keep the formatting and whatnot. So yes, I'm going to keep the formatting, 'cause I did apply a border. And indeed, as I flick through these different options, can you see those random formulas changing in front of your very eyes? So no, let's just paste the values so they don't change anymore. And if you just click on one and look up in the formula bar, you can now see that's just a static number rather than a function. But anyway, another option that I'm very fond of showing people is the transpose option.

1:42So let's imagine you now wish this table was written, you know, the other way round with the months down the side and the regions across the top as column headings when you can transpose the whole thing. So again, we know we have to copy to get the full list of options. So rather than transposing in place, let's click somewhere else to give us the transposed version. So there it is, and it's given me the whole table, but spun it around. The figures are all still correct. Let's get rid of those columns there so we can see what's going on. So I've got a keyboard shortcut for that. So Control + minus will get rid of those selected columns.

2:15And the next thing you'll notice is that the borders look peculiar, because of course, when it sort of reorganized all of those cells into the transposed table, the borders didn't work anymore. So to get rid of borders, I've got another keyboard shortcut for you, Control + Shift + underscore, that'll remove all borders from the selection. And of course, I'm sure you know that you can double click to resize the column widths, or maybe you want to wrap the text so that, there we go. The text is broken. But if you wanted to control very particularly where the text breaks when it wraps.

2:46So let's imagine for this cell here, it's currently Suffolk and Norfolk. And sure, I could turn on wrap text, but I think, well, I always want it to say Suffolk and then and Norfolk on the next line. So I want to control where the line break goes. Well, if you just click up in the formula bar and press Alt + Enter, that's how you get another line within a cell. Of course, if you were to just press Enter, it would've taken you down to the next cell. So to get another line within the cell is Alt + Enter. Let me just hide these columns here, because let's imagine I don't need those anymore. Can you remember the keyboard shortcut?

3:17Control + 0, there we go. But also in this smaller table, let's imagine now I actually want a diagonal line through that cell there. Well, did you know you can draw on diagonal lines in Excel? So if you click the drop down next to the little borders button there and choose to draw a border and then very carefully just drag, there we go, from corner to corner, you've got a diagonal line. But now let's look at some number formatting. And for that, I'm just gonna move down to sheet number six in this workbook. And you will already know that you can click on a cell and, you know, choose the appropriate number format from this area here.

3:51And of course, there's also custom number formats and whatnot. But let's talk about a few things that might have caught you out that I can help you with. So for example, these reference numbers, well, ideally they're supposed to be five digits long, and they're supposed to have, if they're not five digits leading zeros to fill the remaining five spaces. So this number here should be displayed as 00123. But of course, as soon as I press Enter, Excel removes those leading zeros, because they are of no mathematical value in terms of that being formatted, you know, with a general number format.

4:22So there are some different approaches I could take here. I could select the values there and choose to either format it as text, or if you're doing it while you're typing it in, you could just put a little quote at the beginning there, that'll force it to be formatted as text, which means that you can then put in the extra zeros and they will stay there because it knows it's not a number. But of course, these methods whereby I'm telling it that it's text, is meaning that I'm gonna have to go through and manually put in the zeros that have disappeared. So the better way of doing that would be to create a custom number format.

4:53And it's a really easy one. So let's just pop out the number dialogue box, choose custom, and all we are saying is, well, we want the number format to be one, two, three, four, five digits, please. And if the number itself doesn't fill five digits, then fill up the spaces with zeros. And that's all we have to type in. So when I click, okay, fantastic. Let's imagine I'm going to fill in some values into these cells here. So I've selected all of the cells. So let's just quickly type in some numbers. So 100, 200, 300, 400, 500, 600.

5:25And you think, "Oh my goodness, I'm going mad." So this does happen quite often in Excel. You type in a number, and of course the number formatting gets left behind, doesn't it? Even when you've deleted the contents of the cell, it still remembers the number formatting. Now, that was probably quite obvious to you, especially I think with maybe the date one and the percentage one. And if you look up at the ribbon here, you can see, well, that rather strange format appears to be a fraction, and that's obviously a date, and that's scientific. So of course you can manually reapply a general number format, but I think you might have guessed by now I've got a keyboard shortcut for you.

5:59So if the number formatting goes a bit peculiar, a quick way just to reset it back to the general number format is Control + Shift, and then the hash or the pound sign. And you can see that's just reset them back to general. Let me just undo that for a second though, because an alternative that you might be tempted to use is this button here. So rather than clearing all of the contents of the cells, which is gonna, you know, take away the values as well, you might think, "Well, let's just clear the formats." But of course, that's clearing all formatting, not just the number formatting, but also that gray shading.

6:32So I have to say, I am terribly fond of Control + Shift + pound or hash to reset the number format back to general. And interestingly, one of those number formats that was maverickly applied was a fraction number format. And I'm not sure if you've come across this before, but let's say I've got 0.5, and I want that displayed as a half, then that's when I would go ahead and apply the fraction number format. But actually, let's just put that back to general. If you were to type in zero, then a space, and then the fraction, so I want it to show as a half, it will put the number in there as a fraction number format, but it is still storing the decimal number behind the scenes for use in calculations.

7:16Let's imagine now you don't like this gray shading anymore, so you go and select the cells and say, you know, let's just put it back to white. And then you think, "Well, I'm going mad, you know, where have the gridlines gone?" Well, don't forget that in order to see those gridlines, we must have no fill. So no fill is different to white fill, so let's just choose no fill. But I suppose this also leads me onto what happens if you've got a maverick cell that isn't behaving for all of the reasons that we've just looked at. So maybe somebody has applied white formatting, and maybe somebody has applied a peculiar number format, so it's not displaying as you expect.

7:48So if you look at cell B4 here, you can see it's missing its gridlines, it's a peculiar number format. Well, rather than trying to fix what's gone wrong, I would say every time simply find a cell that's behaving as you would expect with the right formatting and it's doing what you want it to do, and then use the format painter to fix the maverick cell. Don't even try and work out what's wrong with the troublesome cell, just apply good formatting on top of it. And it feels a bit contrived when we're doing this in a training environment, but it does happen, you know, you might accidentally do a keyboard shortcut that, you know, for some reason makes that cell go peculiar and you think, "Well, I don't know what I've done."

8:24And because it was done by mistake, you don't know that you've accidentally applied the time number format or something, in which case you would've known to put it back to the appropriate number format that you want, you know? So if you don't know what's wrong with the cell, it's quite hard to fix. Say just find the cell that's behaving itself and reset the naughty one. But lastly, quite an obscure tip that I have used on a few occasions, you'll notice that I've got the default gridlines showing here, and you will know that, you know, I can turn those off if I want to, but sometimes if you are projecting, you know, in a meeting room through a projector that's quite bright, you know, it's a little bit overexposed, sometimes these pale grade gridlines don't show.

9:00So for me as a trainer, when I'm trying to, you know, talk a group of people through how to use Excel, it's hard for them to see sometimes because the gridlines aren't showing properly. Well, look at this, if you go to File Options, and then Advanced, and then just scroll down to, there we go, gridline color, and it's an option just for this sheet, so let's make that a much darker gray. In fact, let's make it the darkest gray there just so it's obvious. So when I click okay, you can see those gridlines are now heavier, but it has only applied to that sheet.

9:31As usual, here's your summary for your reference, but for now, I hope this has been informative for you, and I'd like to thank you for viewing.

0:00(lively music) At the time of recording, Excel does not have the formatting capability for you to be able to add bullets to text because, well, it's a spreadsheet program, isn't it? Not a word processor. And indeed it does make me twitch slightly when people want to do this because that's not what Excel is for. But I do concede that outside of my ideal world of the training room, that yeah, maybe you do want to add some notes to your spreadsheet, formatted nicely in a bulleted list.

0:32So let's look at some options you've got for doing exactly that. So let's imagine, I want some project notes just here in my spreadsheet. And let's start off with what I suggest you do not do. So it is very tempting just to type a little asterisk and then press the space bar a couple of times and then start typing in your text. And of course, typically with a bulleted list, the text is going to wrap, so we know how to wrap text in Excel. So let me just click back on that and choose wrap text. And this is where it all starts going horribly wrong because you can immediately see it's gonna mess up the rest of your spreadsheet.

1:04But you know, I try and persevere and I try and fiddle with this and perhaps put that at the top there and oh, it's looking pretty horrible, isn't it? And you can also see that the text isn't wrapping in the sort of indented way that we would expect it to wrap for a proper bulleted list. So that is pretty ghastly. So I'm going to do a couple of undos just to step back all of that. And to be honest, it's not much better if you copy and paste it from Word. So in this Word document here, I do have some text already nicely arranged in bullets that I would like in Excel. So let me just copy that and paste that into that same area just here.

1:38And it kind of looks okay until I start trying to wrap the text. And I've got the same problem again. It's messing up my whole spreadsheet. I'm gonna have to adjust the position there. And you can see again, the text hasn't wrapped under the indent there like it was in Microsoft Word. Do you see what I mean? I haven't got that hanging indent there. So that is pretty horrible. And again, I'm going to do a few undos to get rid of that. And I suppose the low tech solution is what I showed you on the screen snip in the introduction a second ago. And that's on this other sheet down here.

2:08So the low tech solution would be to have the bullet symbol all by itself in one column. So you can just go and pick up the symbol from the Insert ribbon tab. So over here, just go and find the appropriate symbol you want to use for a bullet and just click insert and then close. And then you can format that cell accordingly with regard to the position of the bullet. And then in the next column along, that's where you can type in your text. And then with text wrapping turned on, let's just go back and say yes, please wrap the text.

2:39That is looking quite neat and tidy. You'll also see for this particular example to make it look nice, I've turned off the grid lines, but if I go back to the view ribbon tab and turn back on the grid lines, you can see how that's constructed. And that's fair enough. I think that's an acceptable low tech solution, particularly if you're putting these notes on a separate sheet or by themselves. So you're not gonna mess else up by adjusting the column and row depths and widths and whatnot. But if I just go back to that other sheet because let's imagine I really do want my project notes on this sheet and we already know that I'm gonna inadvertently start messing up the row heights and whatnot over here.

3:13So I have to say, I think my preferred solution is this. Go to the insert ribbon tab and insert a text box. So just click and drag to draw a text box. Now, unfortunately, within the text box, it's not really a lot better if you type in a bit of nonsense and have the wrapping text. It doesn't maintain that indent that we would hope from a word processor. But actually, if you copy and paste from a word bulleted list into a text box, so it's still on my clipboards, lemme just paste that in and say please keep the source formatting, then yes, it has retained that nice alignment of the indents that we saw in Microsoft Word.

3:52And the beauty of this is this is now an independent piece of text that you can position in just the right place without interfering with anything else on your spreadsheet. So I think that probably is my preferred solution, but the text has to have been copied from a Word document. It won't work if you bring it in from a bulleted list from OneNote, for example. It has to be Microsoft Word. Now, the third alternative in my opinion, is potentially slightly over-engineered, but there might be some scenarios where it's useful. And that's to create a custom number format that includes the bullet.

4:25And that's what's happened with these little entries here. And this is useful if you've just got short bits of text that you want to appear with a bullet. It's not going to capture the indent effect like we had here. So as I say, it might be somewhat over-engineered for what we end up with, but let's give it a go anyway. And the way we do that is well, first of all, we have to go and grab the bullet that we want. So let's create a different bullet style this time 'cause I've already got a number format for the black circle, so I quite like this lozenge. So I go to insert that and then close. And the reason why I'm inserting it is just so I can pop it on my clipboard.

4:56Let me just press Control + X to put it on my clipboard there. Now let's go and create our custom number formats. Let's go to custom. And up here where it says general, that's what we're going to over type with our new number format. And it's dead easy. All you have to do is paste in the bullet, press the space a couple of times to indicate, you know, the gap you want between the bullet and the text, and then put in the little ampersand there as the placeholder for some text, but a slight word of warning for you, not every symbol will work properly because some of those symbols, you know, if you wanted the check mark or something, you know they are a character from the Wingdings font or the symbol font.

5:33And it won't work properly. It'll look peculiar when you paste it into here. So there's only a small number of symbols that this will work for, but the lozenge works and obviously, the black circle works. So if I just click OK to that. So now let's see, imagine that this is gonna be phase two and it's gonna be Task 1. And actually, don't forget, we can use the fill handle to repeat all sorts of patterns, including our tasks. You'll see I've already got that indented. So don't forget, we can indent or outdent text in our cells here. But let's apply our new number format with the bullet. So up we go to our number formatting, choose more number formats, down to custom and my one that I created at the end here.

6:09There we go, with the lozenge. Let's click OK. Ah, very nice. So to wrap up then, we saw three different workarounds for creating a bulleted list in Excel. Firstly, we saw the the low tech solution where we put the bullet in one column and the text in the next column. And I think that's ideal if you want your notes on a separate sheet or which was my preferred method, we could copy and paste a bulleted list from Word into an Excel text box. And remember, it has to be from Word, not OneNote. And then finally, we saw that you could create a custom number format to capture the bullet.

6:42And that's all you need to put into the custom number format, the bullet symbol itself, a couple of spaces. And then @ is the placeholder for text. Let me just delete those scribbles so you can see it for your reference. But for now, I hope this has been informative for you and I'd like to thank you for viewing.

0:01(soft music) Every few months or so, somebody somewhere asks me how to insert a checkbox in Excel. And in my humble opinion, this is one of the few features that Google Sheets handles a bit better than Excel. And so in this video, I'd like to show you a couple of options we've got for creating this kind of effect in Microsoft Excel. Now, I suppose the really low tech way, if we wanted a little checkbox to indicate which of these tasks was complete, the really low tech way would be to insert a symbol.

0:33So let's just for example, pick a little checkbox there for that task to indicate it's complete. And maybe we could choose the empty box there to indicate that's not complete. But I have to say this really is quite horrible, and I might just have to say that you can't be my friend anymore if you do it this way, because those are just symbols. They don't do anything. What we really need is something like this here where we can actually click the little box to check it or uncheck it with or without something else happening as well. So in this example, you can see when it's checked, the text appears struck through, and the strike through disappears when it's unchecked.

1:05So this is probably the sort of thing we're after. And this is actually done using Excel's form controls. And back in the main Excel course, if you have been following that, we did do an example of creating a little worksheet form in the macros skill. So you might have come across these before, but I do just want to do another example with you. So let me add task four to the end of the list here. So task number four. Let's add the little checkbox, and we do that from the developer ribbon tab. So if that's not showing, right click and choose to customize the ribbon and make sure it's switched on. And then you can go ahead and insert this little checkbox form control.

1:39So I can just click and drag to draw that checkbox. You'll need to delete the default text label there because we don't need it showing. And then I can use the adjustment handles just to adjust that, and then move it into position. There we go. Something like that. And then when I click away, well, that might be all you need. I can just click that to check it and click it again to uncheck it. So if that's the effect you want, then job done. That's quite nice and easy. But if you want other things to happen based on whether that checkbox is checked or not, then there's something else that we need to be aware of.

2:11Because what you probably are inclined to try and do is to create some sort of if function maybe that says if that box there is checked, then do this, otherwise do something else. But if we were to do that, we can't point our if functions or whatever at the checkbox itself, we have to do something else. And what we have to do is we have to identify another cell somewhere where the status of that checkbox, whether it's true or false, is captured. And you'll see that I do have a hidden column here.

2:41So let me just un hide that. And that's where I've already got the status of those three check boxes there. You can see that it changes depending on the status of the checkbox. So what I need to do for the one we added is right click on it. And incidentally, that's how you get those adjustment handles back again, if you want to move it or adjust it. But go into format control and say, "I want you to link the results of that checkbox to that cell there, please." And then click okay. So now when I check it or uncheck it, good. So that means we can actually use the value that's in this cell in our functions and our formulas, because that is representing whether the checkbox is checked or not.

3:16And then once we've put it in our formulas, we can hide that column like I did before. So let's try the example like we saw up here whereby the text is struck through. That's actually done with conditional formatting. So I want the appearance of this cell here to change depending on the value that's in cell D7. So I'm gonna click on that cell there, go to the home ribbon tab, choose conditional formatting, and let's create a new rule. And I'm gonna use a formula to determine which cells to format. So I'm formatting this based on whether that cell there is true.

3:48And you'll notice that that's gone in as an absolute cell reference. If you are gonna put this in, and then repeat it all the way down, then you would need to change that so that it was a relative reference, so that it did look at the correct linked cell as it filled down. But for us just doing a one-off, it wouldn't have really mattered. But anyway, the point is, if that is checked, if it is true, then I want the text to show as struck through. So let's just use the option to say yes, strike it through, please. Click okay. Let's try it out. Check the box, perfect. Uncheck it, good. So now I can hide that column again.

4:19So yeah, I'm pretty happy with that. That's exactly the effect I was after. But there's actually an alternative method I want to show you, which uses entirely conditional formatting. What we just done, it did use some conditional formatting to strike through the text here, but this was done with the form control. So for this, I want to actually show the little checks here using conditional formatting. So what we have to do, first of all, is to indicate whether a task is complete or not by putting in a one to say yes, the task is complete, or a zero to say no, it's not, okay?

4:50And then I'm going to change the appearance of these cells using conditional formatting, and I'm gonna basically be saying, if a one shows there, then give me a green tick. But if it's a zero, give me a red cross. So again, that's conditional formatting. Off we go, let's create a new rule. And yes, I'm formatting the cells based on their own values, but I'm going to be using an icon set. And let's choose, there we go, ticks and crosses. That's looking promising. So I'm gonna change these conditions down here. So I want it to show as a green check mark there when the value is greater than or equal to a one.

5:23And you must make sure you change the type here from a percent to an actual number. I want it to actually look at the number itself and decide whether it's a one or not, and if it is, show a green check mark. And let's just change this to show the red cross. Therefore, if it's less than or equal to zero, but let me just change it to number. There we go. So I'm hoping... Oh, and there's one more thing we need to do. I need to choose the option show icon only. Otherwise, it will leave the one and the zero in the cell itself. But I only want to see the checks or crosses. So let's click okay to that.

5:54That's looking promising. Let's just fill that down and let's choose the option to only fill the formatting. Perfect. So if I want to mark task two as complete, then I click on that cell and type in a one. I change the value, the underlying value there to a one or to a zero. That's how I change it from ticks and crosses. And I think that does actually look really quite nice. I mean, it's an attractive display because it's got a bit of color. It's a more visual graphical way of working. And I think either option, either the conditional formatting one here or the little form controls over there, either option is completely feasible.

6:27The one you choose, I think is gonna depend on the effect you're after and which one makes best sense for your particular scenario. For example, if somebody else is using your spreadsheet, then it's pretty intuitive to check a box to mark something as complete, whereas they might not know that they would have to change the value to one or zero. So that might be a factor. And you'll also see in this spreadsheet itself, if you weren't following along, if you unhide these cells here, I did do an example using conditional formatting for your reference, and you will also see that I have included another conditional formatting rule, much like we did with this example, whereby if the value equals one, then that task also appears struck through.

7:07So same sort of thing. But you can have a look at the underlying conditional formatting for those cells if you choose. I hope this has been informative for you, and I'd like to thank you for viewing.

0:01(bright music) We've got a whole separate skill in the main Excel course on using charts where we see an example of every chart type available. But in this video, let's run through a couple of quick tips for your charts. So let's imagine we want to plot this data on a chart. And it would be very tempting, wouldn't it, to just press control A and create a chart from that lot. But don't forget, you wouldn't normally include your totals on your chart 'cause those figures are gonna be much bigger and therefore it's gonna distort your chart. So you don't include your totals, but you do include your titles so that the chart is correctly labeled.

0:37But actually for this particular example, I don't want to include the US. So you can select ranges which aren't next to each other. So let's say yes, I do want my titles, and I also want to just plot the UK and Europe. And notice that I have included that blank cell there to ensure that the different ranges I've selected are all the same shape so that they can match up properly when the chart is created. But anyway, the keyboard shortcut to just quickly put in the default chart, well, you could try F11 to give yourself a brand new chart on a separate chart sheet, or if I just go back, or you could try Alt F1 to give you a new chart on the same sheet as the selected data.

1:14And sure, you can see that it's used the data that I've identified there and everything is appropriately labeled. But you'll also see that if you are following along with me, I've got a bar chart here, rather than the traditional vertical column chart. And you might be thinking, well, why is that? Well, that's because I have changed my default chart type. I mean, let's imagine in our organization we always use these horizontal bars rather than the vertical ones, so you can change your default chart type. So if you just go to change chart type here, and then just right click to set as default chart and you'll see the green check mark there to indicate that yes, the bar chart is my default, but actually on this occasion I'm gonna change it just to the clustered column instead.

1:54So that's probably what you you're used to seeing. But now let's imagine I want to add some extra data to this chart. You know, I belatedly realized that I did want to include the US figures, and this is where I'd like you to pay attention to what you are clicked on in the chart. So can you see, when I'm clicked on one of the series, it highlights that for me and if I click on the other one. So therefore if I want to include the US, well just make sure I'm clicked on the chart, but not on any series in particular, and then you can actually just extend the selection there to include the other figures on the chart. And that's immediately looking a little bit odd because the US figures are so much bigger than the other figures so we will address that in a second.

2:29But what I tend to do is go ahead and create the chart with all of my data, rather than just a subset like we did, and then you can use this filter button over here to turn off any particular series or categories that you don't need. So let's imagine I only want to see the US figures and the UK. So if I uncheck Europe and then choose apply, then it's only going to gimme those two series. But we really do have to deal with the problem we've got here of the US figures being so much bigger than the UK figures that they don't really work very well on the same chart.

3:00So this would be a very good argument for using a combo chart. So we'll have one series shown as a line, you can see in their little preview there and the other series shown as a bar, but that's not going to be quite enough to make this meaningful. The trick is to make sure that you identify one of the series to show on a secondary axis. That means it can use a different scale. So if I click okay to that, that is looking much more meaningful. The UK, the orange line, is using the smaller numbers on the right hand side here, whereas the US, the blue blocks, are using the numbers over on the left hand side here.

3:35But possibly my favorite charting tip is linking the chart title to the value in a cell. So you will already know that you can manually click in there and over type that with whatever you want as the chart title, or you could turn off the chart title altogether if you wanted to. But let's say I wanted to display whatever is in this cell here. So with that chart title selected, so don't click in it as if you're editing the text, just with it selected now type your equals and just click on the cell that you want it to reflect and then press enter.

4:06Fantastic, that is now linked. So if I were to change this to say global sales, then that's going to be reflected in the chart. So the text is linked but the formatting is not. So if I were to click back on that title and change the formatting, then you can see that is independent of the formatting of the cell that it's linked to. As usual, here's your summary of those tips. But for now, I hope this has been informative for you and I'd like to thank you for viewing.