Excel Tips and Tricks

0:01(gentle music)

0:06<v Simona>I used to do a lot</v>

0:08of Microsoft Office tips and tricks demos

0:10in my previous job,

0:11and so I thought it would be fun to share with you

0:13some of my favorite Excel ones in this skill.

0:16Note though, that although these tips

0:18aren't particularly super advanced,

0:20I do assume that you have a good working knowledge of Excel.

0:23And I should also point out,

0:25if I show you the list here of what we're gonna cover,

0:27I would estimate that, oh, I don't know,

0:29about 3/4 of what we go through here

0:31is mentioned somewhere or other

0:33in the complete Excel course.

0:35And so if you've stumbled across this skill

0:37as part of that course,

0:38then don't be surprised to see some duplication.

0:41Make sure that you download the supplemental file

0:43so that you can follow along.

0:44And once you've done that, let's get started.

0:06<v Simona>Let's start off this skill then</v>

0:08with a look at some tips and shortcuts

0:09for speeding up data entry in Excel.

0:12And we're gonna see a mixture of keyboard shortcuts,

0:14and also some features or options

0:16that you may or may not have come across before.

0:18And I will show you a summary at the end for your reference.

0:22You can see on this spreadsheet that I'm capturing

0:24some figures, probably, for January, February, and March.

0:27And you will already know that if

0:29I've already got January or Jan entered in there,

0:32then I can use the full handle to continue

0:35the other months of the year, because that's

0:36a list that Excel understands,

0:38January, February, March, April.

0:39It also knows the days of the week,

0:41Monday, Tuesday, Wednesday.

0:42But what about this list here?

0:44So let's imagine that these are

0:45the sales regions in my organization.

0:48You know, this is a custom list of a combination

0:50of counties and areas in the south of England,

0:52but that's how we have divided up our customer base

0:55into different selling regions.

0:56And I often need to input this same list

0:59in the same order for my spreadsheets.

1:01So it will be sensible to save that

1:03as a custom list alongside the months

1:06and the days of the week so that I can

1:08quickly reuse it another time.

1:10So to do that, if you go to File, and then Options,

1:13and choose Advanced on the left hand side here,

1:15and then scroll almost to the end.

1:17Yeah, there we go, Edit Custom Lists.

1:20And you can see that, sure, it already knows

1:21the days of the week and the months of the year,

1:23but I'm going to bring in some information

1:25from some other cells to create a custom list.

1:27And because I already had those cells selected,

1:29it's got the reference there,

1:30but if you hadn't selected it first

1:32then you could just click in there, and then click and drag.

1:34But let me click Import, and that will give me

1:37all of those entries as a new list.

1:39So let's click OK, click OK again,

1:42and let me just delete that so that I can try it out.

1:45So the first one on the list was Greater London. Good.

1:48And if I just fill that all the way down to row 14, perfect.

1:52Similarly, it'll work also going sideways,

1:55but let me just undo that.

1:56So that has saved me a bit of time,

1:58and that'll persist for future spreadsheets as well.

2:01So that's a change that I've made

2:02to the custom list for my Excel environment.

2:06And as an aside, I can also use this custom list to sort.

2:09So if I just go to this Data tab down the bottom here,

2:12you can see I've got a great big block of data here.

2:14You know, the date, customer name,

2:16the region of the sale, or whatever.

2:18And of course, we know that this column here,

2:20well, I can sort it alphabetically, can't I?

2:21So that's how it's currently sorted, isn't it?

2:23A to Z, so Berkshire, and then,

2:25you know, Devon, and then East Sussex.

2:27But what if I want it sorted in

2:28the order of that custom list?

2:30Well, if I click the sorting drop down here

2:33and choose a custom sort, well, one of the options is

2:36not A to Z or Z to A, but a Custom List.

2:39So there's that same little list

2:41of custom lists that we've seen before.

2:43So let's click OK to that. Click OK again. Perfect.

2:46I've got all of the Greater London entries together.

2:49And if I scroll down, yes,

2:50that's the order of that custom list.

2:52But let me go back to the Sales tab.

2:54And let's imagine I've got some figures to fill in

2:57for these first three selling regions here

2:59for the first three months of the year.

3:01And you'll notice that I have selected

3:03the block of cells first.

3:04So this is my next tip for data entry.

3:07Select the area you are gonna enter

3:09the figures into first and then start typing.

3:11So I'm just gonna put in some random numbers here.

3:13And you can see that as I get to the end of the column

3:15and I'm just pressing Enter here,

3:17it takes me to the top of the next column.

3:19Because so often I see people type a number,

3:21press Enter, type a number, press Enter,

3:23and then they have to either use the arrow keys

3:24to go up to the top of the next column

3:26or click with the mouse.

3:27But to select the cells first, when you press Enter,

3:30it'll just take you through the whole selection.

3:31Or if you prefer going sideways, you can press your tab key

3:34and that'll take you along a row at a time.

3:37But that does also remind me of another tip.

3:39So you will know that if you are putting in a number here.

3:42So let me just make an edit to this one and press Enter.

3:45You'll know that when you press Enter,

3:46the selection moves down to the cell underneath.

3:49And a lot of the time I think that's sensible.

3:51But if you find yourself getting annoyed by that,

3:54you may want to consider changing the direction.

3:56Let me show you what I mean.

3:58So if I go to the File menu, and choose Options,

4:00and then into Advanced, you can see here,

4:03after pressing Enter, move the selection down.

4:05Yes, that's the default,

4:07but you could change that if you needed to.

4:09Or if you didn't want it to move at all,

4:10you would uncheck that box.

4:12But I would just choose cancel for now.

4:14Let's imagine for the rest of these cells

4:16we've only got estimates, and in fact,

4:18we're just going to estimate a value of 450

4:21for every cell in that selection.

4:23So press Ctrl + Enter to get

4:26the same value into every cell.

4:28And I have to say, I find this very useful

4:30to be able to just, you know, quickly blitz

4:32some numbers into a spreadsheet here

4:34when I'm testing my formulas.

4:35So let's just for example, press Ctrl + A to select

4:38that block of data, and then Alt + Equals

4:41to put in some totals at the bottom there.

4:44And let's just pop in a total row there, make it bold.

4:46And if you were just quickly populating

4:48your spreadsheet in order to test your formulas,

4:51then there might be an alternative method

4:52of doing that which you might prefer.

4:54And that's to be able to put in a random number.

4:57So again, I've just selected my block of cells,

4:59and I'm going to use a function this time.

5:01So it's going to be =RANDBETWEEN,

5:04and that's got two arguments.

5:07So, between what numbers?

5:08So I want to have a random number between

5:10the value of 10 and 500, close the bracket.

5:14And because I want that same formula

5:15in every cell in the selection,

5:17then I press Ctrl + Enter, bingo,

5:19I've got a whole load of random cells there,

5:21perfect for just populating a spreadsheet

5:23so I can test my formulas.

5:25Let me go back to the Data sheet this time,

5:28and I'm just going to delete one of these values in here.

5:31And let's imagine I want to enter in

5:32the same thing here as the cell above.

5:35Now, for this particular example,

5:36you will already know that I can just start typing

5:38and it will have a look at the entries

5:40in the list above itself and make a suggestion.

5:43But if that doesn't work in the scenario

5:45that you need to repeat the cell above,

5:46then the keyboard shortcuts that you need is Ctrl + D.

5:49That just copies the contents from the cell

5:51directly above the selection.

5:53But actually, Ctrl + D is also useful

5:55when you're working with graphics.

5:56So let me just pop in for example, a quick square.

5:59And in fact, hold down your Shift key

6:00to get a perfect square rather than a rectangle.

6:03And when one of these shapes is selected,

6:05if you press Ctrl + D, then it'll duplicate the shape.

6:08But interestingly, if you then move the shape

6:11into a particular position, so lemme just pop it

6:13next to that original square.

6:16Now when I press Ctrl + D, can you see that

6:18it's also repeating the position and the spacing

6:21for each subsequent shape, you know,

6:23for each subsequent press of Ctrl + D.

6:25So that's quite interesting.

6:26But now let's think about speeding up the way

6:29we can enter in data onto different sheets.

6:32So for example, if you look down the bottom here,

6:33I've got three sheets, Expenses, Targets, and Transactions,

6:36where actually I need the same headings

6:39and the same formatting on all three of those sheets.

6:41So I can input something onto all sheets at the same time

6:44by selecting the relevant sheets first.

6:46So I'm just gonna hold down my Ctrl key in order

6:49to select all three of those sheet tabs.

6:50And then, well, let's pop in some headings.

6:53So let's use our new custom list that we created,

6:55so Greater London, and repeat that

6:58all the way down to there.

7:00Perfect, let's just adjust the column width,

7:03and maybe put in a heading there for the month,

7:05and fill that across, and add some formatting.

7:08You get the idea, you make it look

7:09the way you want it to look.

7:11And then if I click out of this grouping of sheets,

7:14let's just click on, for example, this Timesheets,

7:16and then flick back through Expenses,

7:18Targets, and Transactions, you can see that

7:21the things that I typed while those three sheets

7:23were selected have now appeared on all of those sheets.

7:26So that is a great time saver,

7:27and also really good for consistency.

7:29But actually, thinking about that Timesheet sheet,

7:31I've got a couple of other shortcuts for you here.

7:34So let's imagine this is where I am capturing work

7:36that I've done on a freelance basis,

7:38and 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,

7:44and the client it was for, and what I did,

7:46the start time and stop time.

7:48And then the spreadsheet then works out the total time

7:50I spent on that task and how much I can invoice.

7:52So you already know, I'm sure,

7:54that we can enter in today as a function

7:57to put in today's date, but if you think about that

8:00in the context of this timesheet, well,

8:02I don't want this date to change to tomorrow

8:04tomorrow, you know, the date that I want

8:07to enter in here needs to be a static date.

8:09So the keyboard shortcut that we need for that

8:12is Ctrl + Semicolon, that just puts in a static date.

8:15So lemme just put in the example there of another task.

8:18And it's the same with the time,

8:20I need it to be a static time.

8:22So rather than a function, so equals now would be

8:25the function to put in a dynamic input of the current time.

8:28But if I just want a static time of the time now,

8:31you know, the system time, it's Ctrl + Shift + Semicolon.

8:35So let's say I started the task at 25 minutes past 5,

8:37that's the current time.

8:39And let me just put in that again.

8:40Obviously, it's exactly the same time,

8:42so 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.

8:48I mean, you will know that if I, you know,

8:50click on a cell, and then, you know, click Copy,

8:53that's sent on my Clipboard, and, you know,

8:55I can paste it somewhere else.

8:57And of course, if I copy something else,

8:58that is the most recent thing on my Clipboard,

9:00that I can paste something else,

9:02and maybe I then go ahead and copy this blue shape,

9:04and, you know, I could paste that somewhere else.

9:05Or maybe I go ahead and copy another name.

9:07But you'll be used to using copy and paste

9:10in the context of it's only the very last thing

9:12that you copied this is being available for pasting.

9:15Well, I've got news for you, because copy and paste,

9:18of course, is a great time saver, but we can make that

9:20even more valuable if we pop out the Clipboard.

9:23So on the Home ribbon tab, if you pop out

9:25the Clipboard just here, you can see,

9:27it's showing you the last few things that you've copied.

9:30So those are the different names that I did just now.

9:32There's that blue shape, so if I do want that name

9:34that I copied a few steps ago pasted into that cell,

9:37then I can just click it from the Clipboard just here.

9:39And you'll also see this works

9:41across different applications.

9:42So you can also see other things that I had copied

9:45from OneNote and even a web browser window.

9:47So I think this must have been when I was doing

9:49a peer review for one of my colleagues.

9:51So this clipboard is generic to your whole environment

9:53across different applications, but it can be really useful

9:56to be able to quickly paste in something

9:57that you copied a few steps ago.

10:00But anyway, here's your summary, which I'll leave

10:02showing for a moment or two so that you can freeze frame

10:05to note down anything if you need to.

10:07But for now, I hope this has been informative for you,

10:10and I'd like to thank you for viewing.

0:00(bright music)

0:06<v Simona>I do realize</v>

0:07that you're more than capable of using your mouse

0:10to navigate and to select parts of your Excel spreadsheet,

0:13but this is actually an area

0:14where some keyboard shortcuts can be very helpful.

0:17Let me show you.

0:19You already know

0:19that we can press Control + A to select a block of data,

0:22but within that selection,

0:24if you now press Control + full stop

0:26or the Control + period,

0:28then you'll see that you navigate

0:29to the four corners of the selected block of data.

0:32And I think this is especially useful

0:33when you've brought something in from somewhere else,

0:36and you need to get a handle on the size of a large dataset

0:39or if the data isn't selected,

0:41so lemme just click in the middle of it there,

0:42you can use the arrow keys.

0:44So Control + arrow will take you again

0:46to all four corners of the block of data there.

0:49If you want to move

0:50between different sheets in your workbook,

0:52so look down the bottom here,

0:53you can see I've got different Sheet tabs.

0:55Then try Control + Page Up to go to the previous sheet

0:58or Control + Page Down to go to the next sheet.

1:01Again, useful shortcuts.

1:02or if you've got multiple workbooks open,

1:04so you can see, as I hover over the Excel icon here,

1:07I've got two Excel files open,

1:09then try Control + Tab to switch between them.

1:12And in fact, talking of multiple workbooks,

1:15you'll see on this data sheet here,

1:16I've got this great long list,

1:17but that other file that I had here was the raw data.

1:21Maybe I want to compare these two workbooks side by side.

1:25So on the View ribbon tab, you've got an option there,

1:28View Side by Side, and that'll do exactly that.

1:31And the default is when you choose that option,

1:33View Side by Side,

1:34when you scroll,

1:35can you see that both of those windows scroll together?

1:38So that's exactly what I need for this example

1:40because I'm trying to compare this list

1:41with this list or whatever.

1:42But if you don't want synchronous scrolling,

1:45then look up at the View ribbon tab there.

1:47It's that button just underneath the one we chose

1:49to view the windows side by side.

1:51But if you turn off Synchronous Scrolling,

1:53then only the active window will scroll

1:55when you roll your wheel.

1:57So you'll have to click the window first

1:58and then roll the wheel to scroll that particular window/

2:01To turn off View Side by Side,

2:03then click that same button again

2:05and let me just use Control + Tab

2:07to take myself back to that other workbook.

2:09And while we are talking about navigation,

2:12you might be used to using named ranges in your formulas,

2:16you know, perhaps for naming a LOOKUP table for example.

2:19But it can also be very useful for navigation purposes.

2:22So let's imagine these cells here.

2:24You know, these are our expenses.

2:26I regularly need to go to these particular cells.

2:28So I'm going to name them

2:29so I can quickly get to them

2:30from other parts of my workbook.

2:32So select the data first

2:33and then up here in the name box,

2:35let's just type in Expenses, and then press Enter.

2:39And that means when I'm somewhere else in this file,

2:42I can click that dropdown

2:43to quickly go to that block of sales that I named Expense.

2:47And actually, if you do like the idea of using named ranges,

2:49did you know that we can get Excel

2:52to automatically create them for us from a selection?

2:55So let's just select this block of cells here,

2:58and then go to the Formulas ribbon tab.

3:00And there's this button, Create from Selection.

3:03So I'm going to create some names

3:04from values in the selection using the left column

3:08because I want to be able to quickly go

3:09to these different regions and to their quarterly figures.

3:12So I'm going to uncheck the top row

3:14'cause I'm not that interested in seeing them by month.

3:16It's just the rows.

3:17So when I click OK,

3:19and now I'm somewhere else in my spreadsheets.

3:21Let's just go to this different tab down here.

3:23Let's imagine I now want to see the Wiltshire figures.

3:25I can click the dropdown and go straight to Wiltshire,

3:28and it navigates me to that named range.

3:30And you can see from the dropdown there,

3:32all of these extra entries from those named ranges

3:34that were automatically created.

3:36If you want to tidy up all of the named ranges

3:38that you have created,

3:39then, again on the Formulas ribbon tab,

3:41if you click Name Manager,

3:42that's where you'll see this vast array

3:44of named ranges that have now appeared.

3:45Well, this is where you could go ahead and delete them

3:47or edit them or manually create new ones.

3:50But let me just close that for now.

3:52Let me go back to this data worksheet,

3:54and let's talk a little bit more about selection.

3:56So as we did right at the beginning,

3:58Control + A will select a block of data,

4:00but Control + A again will also select the heading

4:04if you were clicked in a table.

4:05So I don't whether you noticed,

4:06the first time I pressed Control + A,

4:08it didn't include the headings there,

4:09but Control + A again does.

4:10And then Control + A again will select the whole worksheet.

4:14But also if I just click away to deselect,

4:17Control + Space Bar will select the column.

4:20Shift + Space Bar will select the row.

4:22And again, you can see

4:23that the first press of the combination only selects the row

4:26or column within the active block of data.

4:28So you'd have to press it again

4:29to select the whole row across the whole worksheet.

4:33But let me just select the column again,

4:34so Control + Space Bar.

4:35With that column selected,

4:37I can now press Control + Minus to delete it.

4:40It's gone.

4:41Control + Z, of course, is an undo.

4:43So I've got it back again.

4:44This really is keyboard shortcuts galore, isn't it?

4:47But rather than deleting the column,

4:49if I wanted to hide it instead,

4:51then that would be Control + 0

4:53Look up at the column headings.

4:54You can see that column C is missing there.

4:56But let me just do Control + Z to reveal it again.

4:59And don't forget, I will summarize

5:01all of these keyboard shortcuts for you at the end.

5:02So if you think I'm going a bit quickly, don't worry.

5:05But other than deleting or hiding a column,

5:08we might also want to insert a new one,

5:10so that's Control + Shift and then Plus.

5:12To insert a new column,

5:14let's just do Control + Minus to delete it again,

5:16and it is similar for rows.

5:18So let me do a Shift + Space Bar to select the row.

5:21Control + Minus to delete,

5:23Control + Z to undo.

5:24That's the same as we saw before,

5:26but to hide the row,

5:28it's actually Control + 9 rather than Control + 0.

5:31But let me, again, just do Control + Z to undo.

5:34Phew, you'll be pleased to know

5:36the next tip does not involve keyboard shortcuts.

5:38But let me, for example,

5:40just create a couple of blank rows in this data.

5:43And if you have worked with lists and whatnot,

5:45you'll know that blank rows

5:46in your data are a bit of a nuisance.

5:48So it can be useful to have a shortcut

5:50to find all of the blank rows in your data.

5:53So let me select the block first with a Control + A,

5:56and then go to the Home ribbon tab

5:57and click this Find &amp; Select button.

6:00And I do urge you to have a browse

6:02through some of the options,

6:03not only on this menu here,

6:04but also in the Special dialogue box,

6:07because one of the options here

6:08is to find and select blanks.

6:10So if I click OK to that, you can see

6:12that those two blank rows I just created are selected.

6:15So now I could press Control + Minus

6:17to delete those two selected blank rows.

6:20So that was a very quick way

6:21of selecting all of the blank rows and getting rid of them.

6:24But finally, we can also combine

6:27those navigation shortcuts we saw

6:29at the very beginning of this video

6:31with the Shift key to select.

6:34Let me show you what I mean.

6:35So we already know that Control + Down Arrow

6:37takes you to the end of the column that you'll clicked in.

6:40But let's imagine I'm clicked here,

6:42and I want to select all the way from that cell

6:45to the end of the block of data.

6:48So Control + Down Arrow will take us there,

6:50but if we were to hold down the Shift key

6:52while we also did Control + Down Arrow,

6:54it will select all the way to the end.

6:57And again, if I keep the Shift key held down

6:59and use the Right Arrow,

7:00it'll select extra columns to the right

7:02with each extra press of the Right Arrow key

7:04or press the Left Arrow to go back again

7:07or press the Up Arrow

7:08to work my way upwards again from the end of the selection.

7:12And you might think,

7:12"Well, you know, this is a bit academic.

7:15Aren't we just showing off trying to select our spreadsheet

7:18with the keyboard because of course we do have a mouse?"

7:20But sometimes for very large datasets,

7:22if you were trying to select,

7:24in fact, let me just go back to the very top of this.

7:26You know, if you did want to select

7:27just from here all the way down to the end,

7:29you know, your mouse can run away with you,

7:30and it's hard to know when to stop.

7:32So it can be very useful to say,

7:34"Well, let's use the keyboard to select

7:36in a much more controlled fashion."

7:37So Control + Shift + Down Arrow will select

7:39from that cell all the way down to the end of that column.

7:42As before, here's the summary slide for your reference

7:45so that you can grab any shortcuts there

7:47of particular interest to you.

7:48But for now, I hope this has been informative for you,

7:51and I'd like to thank you for viewing.

0:06<v Instructor>The tips that I've grouped together</v>

0:08in this video, all concern the appearance

0:10of your spreadsheet, the formatting.

0:12I've got a nice keyboard shortcut to start us off,

0:15and that is if you select some cells

0:18and then press Control + Shift + 7,

0:20it'll give you a border around the outside of the selection.

0:24And also, before we go any further, do you remember

0:27a couple of videos ago we used that rand between function

0:30to give us random numbers in here?

0:32And you might have noticed

0:33that these keep on recalculating themselves,

0:36which might be a bit distracting.

0:37So you think to yourself, "Oh, well what I'd really like

0:39to do is to cut those, get rid of those,

0:41and then paste them just as values.

0:43And you think to yourself, yeah, I'm sure

0:45there's that option available.

0:46So let's go ahead and choose cut.

0:48So you click the bottom half of the paste button,

0:50all optimistically, and you think, "Well, I'm going mad."

0:52I'm sure there's supposed

0:53to be far more options there under paste special,

0:56but they don't seem to be available.

0:57Well, don't let this catch you out.

0:59That vast array of options that you're thinking of

1:01will only be available if you have copied the text.

1:04So having clicked copy

1:06and now click the bottom half of the paste button,

1:08that's what gives me all of those other options.

1:10So I think, yes, what I really want is

1:12to paste the values rather than those formulas

1:14that keep on giving me new random numbers.

1:16And then I get the choice to either paste the plain values

1:19or to keep the formatting and whatnot.

1:20So yes, I'm going to keep the formatting,

1:22'cause I did apply a border.

1:23And indeed, as I flick through these different options,

1:25can you see those random formulas changing

1:27in front of your very eyes?

1:28So no, let's just paste the values

1:30so they don't change anymore.

1:31And if you just click on one

1:33and look up in the formula bar, you can now see

1:34that's just a static number rather than a function.

1:38But anyway, another option that I'm very fond

1:40of showing people is the transpose option.

1:42So let's imagine you now wish this table was written,

1:45you know, the other way round with the months down the side

1:48and the regions across the top

1:50as column headings when you can transpose the whole thing.

1:52So again, we know we have to copy

1:54to get the full list of options.

1:55So rather than transposing in place,

1:58let's click somewhere else

1:59to give us the transposed version.

2:00So there it is, and it's given me the whole table,

2:03but spun it around.

2:04The figures are all still correct.

2:06Let's get rid of those columns there

2:08so we can see what's going on.

2:10So I've got a keyboard shortcut for that.

2:11So Control + minus will get rid of those selected columns.

2:15And the next thing you'll notice is

2:16that the borders look peculiar,

2:18because of course, when it sort of reorganized

2:20all of those cells into the transposed table,

2:22the borders didn't work anymore.

2:24So to get rid of borders, I've got another keyboard shortcut

2:26for you, Control + Shift + underscore,

2:29that'll remove all borders from the selection.

2:31And of course, I'm sure you know that you can double click

2:33to resize the column widths,

2:35or maybe you want to wrap the text so that, there we go.

2:39The text is broken.

2:41But if you wanted to control very particularly

2:43where the text breaks when it wraps.

2:46So let's imagine for this cell here,

2:47it's currently Suffolk and Norfolk.

2:49And sure, I could turn on wrap text,

2:51but I think, well, I always want it to say Suffolk

2:54and then and Norfolk on the next line.

2:56So I want to control where the line break goes.

2:59Well, if you just click up in the formula bar

3:00and press Alt + Enter,

3:02that's how you get another line within a cell.

3:05Of course, if you were to just press Enter,

3:06it would've taken you down to the next cell.

3:08So to get another line within the cell is Alt + Enter.

3:11Let me just hide these columns here,

3:13because let's imagine I don't need those anymore.

3:15Can you remember the keyboard shortcut?

3:17Control + 0, there we go.

3:19But also in this smaller table,

3:20let's imagine now I actually want a diagonal line

3:24through that cell there.

3:25Well, did you know you can draw on diagonal lines in Excel?

3:28So if you click the drop down next

3:30to the little borders button there

3:31and choose to draw a border

3:33and then very carefully just drag, there we go,

3:36from corner to corner, you've got a diagonal line.

3:39But now let's look at some number formatting.

3:41And for that, I'm just gonna move down

3:43to sheet number six in this workbook.

3:45And you will already know that you can click on a cell

3:48and, you know, choose the appropriate number format

3:50from this area here.

3:51And of course, there's also

3:52custom number formats and whatnot.

3:53But let's talk about a few things

3:55that might have caught you out that I can help you with.

3:58So for example, these reference numbers, well,

4:00ideally they're supposed to be five digits long,

4:03and they're supposed to have,

4:04if they're not five digits leading zeros

4:06to fill the remaining five spaces.

4:08So this number here should be displayed as 00123.

4:12But of course, as soon as I press Enter,

4:14Excel removes those leading zeros,

4:16because they are of no mathematical value

4:19in terms of that being formatted,

4:20you know, with a general number format.

4:22So there are some different approaches I could take here.

4:25I could select the values there

4:27and choose to either format it as text,

4:30or if you're doing it while you're typing it in,

4:32you could just put a little quote at the beginning there,

4:34that'll force it to be formatted as text, which means

4:37that you can then put in the extra zeros

4:39and they will stay there because it knows it's not a number.

4:42But of course, these methods whereby I'm telling it

4:44that it's text, is meaning that I'm gonna have to go through

4:46and manually put in the zeros that have disappeared.

4:49So the better way of doing that would be

4:51to create a custom number format.

4:53And it's a really easy one.

4:55So let's just pop out the number dialogue box,

4:57choose custom, and all we are saying is, well,

5:00we want the number format to be one, two,

5:02three, four, five digits, please.

5:04And if the number itself doesn't fill five digits,

5:05then fill up the spaces with zeros.

5:07And that's all we have to type in.

5:09So when I click, okay, fantastic.

5:12Let's imagine I'm going to fill in some values

5:14into these cells here.

5:15So I've selected all of the cells.

5:16So let's just quickly type in some numbers.

5:18So 100, 200, 300,

5:21400, 500, 600.

5:25And you think, "Oh my goodness, I'm going mad."

5:27So this does happen quite often in Excel.

5:30You type in a number,

5:31and of course the number formatting

5:33gets left behind, doesn't it?

5:34Even when you've deleted the contents of the cell,

5:36it still remembers the number formatting.

5:38Now, that was probably quite obvious to you,

5:41especially I think with maybe the date one

5:43and the percentage one.

5:44And if you look up at the ribbon here, you can see, well,

5:46that rather strange format appears to be a fraction,

5:48and that's obviously a date, and that's scientific.

5:51So of course you can manually reapply

5:54a general number format, but I think you might have guessed

5:57by now I've got a keyboard shortcut for you.

5:59So if the number formatting goes a bit peculiar,

6:01a quick way just to reset it back

6:04to the general number format is Control + Shift,

6:07and then the hash or the pound sign.

6:09And you can see that's just reset them back to general.

6:12Let me just undo that for a second though,

6:14because an alternative that you might be tempted

6:16to use is this button here.

6:18So rather than clearing all of the contents of the cells,

6:22which is gonna, you know, take away the values as well,

6:24you might think, "Well, let's just clear the formats."

6:26But of course, that's clearing all formatting,

6:29not just the number formatting, but also that gray shading.

6:32So I have to say, I am terribly fond

6:34of Control + Shift + pound or hash

6:36to reset the number format back to general.

6:39And interestingly, one of those number formats

6:42that was maverickly applied was a fraction number format.

6:44And I'm not sure if you've come across this before,

6:46but let's say I've got 0.5,

6:50and I want that displayed as a half,

6:52then that's when I would go ahead

6:53and apply the fraction number format.

6:55But actually, let's just put that back to general.

6:59If you were to type in zero, then a space,

7:02and then the fraction, so I want it to show as a half,

7:06it will put the number in there as a fraction number format,

7:10but it is still storing the decimal number

7:13behind the scenes for use in calculations.

7:16Let's imagine now you don't like this gray shading anymore,

7:19so you go and select the cells

7:21and say, you know, let's just put it back to white.

7:23And then you think, "Well, I'm going mad, you know,

7:24where have the gridlines gone?"

7:25Well, don't forget that in order to see those gridlines,

7:28we must have no fill.

7:30So no fill is different to white fill,

7:32so let's just choose no fill.

7:34But I suppose this also leads me onto

7:37what happens if you've got a maverick cell

7:39that isn't behaving for all

7:40of the reasons that we've just looked at.

7:42So maybe somebody has applied white formatting,

7:44and maybe somebody has applied a peculiar number format,

7:47so it's not displaying as you expect.

7:48So if you look at cell B4 here,

7:51you can see it's missing its gridlines,

7:52it's a peculiar number format.

7:54Well, rather than trying to fix what's gone wrong,

7:56I would say every time simply find a cell that's behaving

8:00as you would expect with the right formatting

8:02and it's doing what you want it to do,

8:04and then use the format painter to fix the maverick cell.

8:08Don't even try and work out what's wrong

8:09with the troublesome cell,

8:11just apply good formatting on top of it.

8:13And it feels a bit contrived when we're doing this

8:15in a training environment, but it does happen, you know,

8:18you might accidentally do a keyboard shortcut

8:20that, you know, for some reason makes that cell go peculiar

8:23and you think, "Well, I don't know what I've done."

8:24And because it was done by mistake, you don't know

8:26that you've accidentally applied the time number format

8:29or something, in which case you would've known

8:30to put it back to the

8:31appropriate number format that you want, you know?

8:33So if you don't know what's wrong

8:34with the cell, it's quite hard to fix.

8:36Say just find the cell that's behaving itself

8:38and reset the naughty one.

8:40But lastly, quite an obscure tip

8:42that I have used on a few occasions,

8:45you'll notice that I've got the default gridlines

8:47showing here, and you will know that, you know,

8:49I can turn those off if I want to,

8:51but sometimes if you are projecting, you know,

8:53in a meeting room through a projector that's quite bright,

8:56you know, it's a little bit overexposed,

8:58sometimes these pale grade gridlines don't show.

9:00So for me as a trainer, when I'm trying to, you know,

9:03talk a group of people through how to use Excel,

9:05it's hard for them to see sometimes

9:07because the gridlines aren't showing properly.

9:09Well, look at this, if you go to File Options,

9:12and then Advanced,

9:13and then just scroll down to, there we go,

9:17gridline color, and it's an option just for this sheet,

9:20so let's make that a much darker gray.

9:23In fact, let's make it the darkest gray there

9:24just so it's obvious.

9:25So when I click okay, you can see those gridlines

9:27are now heavier, but it has only applied to that sheet.

9:31As usual, here's your summary for your reference,

9:33but for now, I hope this has been informative for you,

9:36and I'd like to thank you for viewing.

0:00(lively music)

0:06<v Simona>At the time of recording,</v>

0:08Excel does not have the formatting capability for you

0:11to be able to add bullets to text

0:13because, well, it's a spreadsheet program, isn't it?

0:16Not a word processor.

0:17And indeed it does make me twitch slightly when people want

0:20to do this because that's not what Excel is for.

0:23But I do concede that outside of my ideal world

0:26of the training room, that yeah, maybe you do want

0:28to add some notes to your spreadsheet,

0:30formatted nicely in a bulleted list.

0:32So let's look at some options you've got

0:33for doing exactly that.

0:35So let's imagine,

0:36I want some project notes just here in my spreadsheet.

0:39And let's start off with what I suggest you do not do.

0:42So it is very tempting just to type a little asterisk

0:46and then press the space bar a couple of times

0:48and then start typing in your text.

0:50And of course, typically with a bulleted list,

0:52the text is going to wrap,

0:54so we know how to wrap text in Excel.

0:56So let me just click back on that and choose wrap text.

0:59And this is where it all starts going horribly wrong

1:01because you can immediately see it's gonna mess up

1:03the rest of your spreadsheet.

1:04But you know, I try and persevere and I try

1:06and fiddle with this and perhaps put that at the top there

1:09and oh, it's looking pretty horrible, isn't it?

1:11And you can also see

1:13that the text isn't wrapping in the sort of indented way

1:16that we would expect it to wrap for a proper bulleted list.

1:19So that is pretty ghastly.

1:20So I'm going to do a couple of undos

1:22just to step back all of that.

1:24And to be honest, it's not much better if you copy

1:26and paste it from Word.

1:27So in this Word document here,

1:29I do have some text already nicely arranged in bullets

1:32that I would like in Excel.

1:33So let me just copy that

1:35and paste that into that same area just here.

1:38And it kind of looks okay until I start trying

1:41to wrap the text.

1:42And I've got the same problem again.

1:44It's messing up my whole spreadsheet.

1:45I'm gonna have to adjust the position there.

1:48And you can see again, the text hasn't wrapped

1:50under the indent there like it was in Microsoft Word.

1:53Do you see what I mean?

1:54I haven't got that hanging indent there.

1:56So that is pretty horrible.

1:58And again, I'm going to do a few undos to get rid of that.

2:01And I suppose the low tech solution

2:03is what I showed you on the screen snip

2:05in the introduction a second ago.

2:07And that's on this other sheet down here.

2:08So the low tech solution would be to have the bullet symbol

2:12all by itself in one column.

2:14So you can just go and pick up the symbol

2:16from the Insert ribbon tab.

2:17So over here, just go

2:19and find the appropriate symbol you want to use for a bullet

2:21and just click insert and then close.

2:24And then you can format that cell accordingly

2:27with regard to the position of the bullet.

2:28And then in the next column along,

2:31that's where you can type in your text.

2:34And then with text wrapping turned on, let's just go back

2:37and say yes, please wrap the text.

2:39That is looking quite neat and tidy.

2:41You'll also see for this particular example

2:43to make it look nice, I've turned off the grid lines,

2:46but if I go back to the view ribbon tab

2:47and turn back on the grid lines,

2:48you can see how that's constructed.

2:50And that's fair enough.

2:51I think that's an acceptable low tech solution,

2:54particularly if you're putting these notes

2:56on a separate sheet or by themselves.

2:58So you're not gonna mess else up by adjusting the column

3:01and row depths and widths and whatnot.

3:03But if I just go back to that other sheet

3:05because let's imagine I really do want my project notes

3:07on this sheet and we already know

3:09that I'm gonna inadvertently start messing up

3:11the row heights and whatnot over here.

3:13So I have to say, I think my preferred solution is this.

3:18Go to the insert ribbon tab and insert a text box.

3:21So just click and drag to draw a text box.

3:23Now, unfortunately, within the text box,

3:25it's not really a lot better if you type in a bit

3:27of nonsense and have the wrapping text.

3:30It doesn't maintain that indent

3:32that we would hope from a word processor.

3:35But actually, if you copy

3:36and paste from a word bulleted list into a text box,

3:40so it's still on my clipboards, lemme just paste that in

3:42and say please keep the source formatting, then yes,

3:47it has retained that nice alignment of the indents

3:51that we saw in Microsoft Word.

3:52And the beauty of this is this is now an independent piece

3:55of text that you can position in just the right place

3:57without interfering with anything else on your spreadsheet.

4:01So I think that probably is my preferred solution,

4:04but the text has to have been copied from a Word document.

4:07It won't work if you bring it in from a bulleted list

4:10from OneNote, for example.

4:11It has to be Microsoft Word.

4:13Now, the third alternative in my opinion,

4:16is potentially slightly over-engineered,

4:18but there might be some scenarios where it's useful.

4:21And that's to create a custom number format

4:23that includes the bullet.

4:25And that's what's happened with these little entries here.

4:27And this is useful if you've just got short bits of text

4:30that you want to appear with a bullet.

4:32It's not going to capture the indent effect

4:34like we had here.

4:35So as I say, it might be somewhat over-engineered

4:38for what we end up with, but let's give it a go anyway.

4:40And the way we do that is well, first of all, we have to go

4:43and grab the bullet that we want.

4:45So let's create a different bullet style this time

4:47'cause I've already got a number format

4:48for the black circle, so I quite like this lozenge.

4:51So I go to insert that and then close.

4:53And the reason why I'm inserting it is just

4:55so I can pop it on my clipboard.

4:56Let me just press Control + X

4:58to put it on my clipboard there.

5:00Now let's go and create our custom number formats.

5:03Let's go to custom.

5:04And up here where it says general, that's what we're going

5:06to over type with our new number format.

5:08And it's dead easy.

5:09All you have to do is paste in the bullet,

5:12press the space a couple of times

5:13to indicate, you know, the gap you want

5:15between the bullet and the text,

5:16and then put in the little ampersand there

5:18as the placeholder for some text,

5:21but a slight word of warning for you,

5:23not every symbol will work properly

5:25because some of those symbols, you know,

5:27if you wanted the check mark

5:28or something, you know they are a character

5:30from the Wingdings font or the symbol font.

5:33And it won't work properly.

5:34It'll look peculiar when you paste it into here.

5:36So there's only a small number of symbols

5:37that this will work for, but the lozenge works

5:39and obviously, the black circle works.

5:41So if I just click OK to that.

5:43So now let's see, imagine that this is gonna be phase two

5:47and it's gonna be Task 1.

5:49And actually, don't forget, we can use the fill handle

5:51to repeat all sorts of patterns, including our tasks.

5:54You'll see I've already got that indented.

5:56So don't forget, we can indent

5:57or outdent text in our cells here.

5:59But let's apply our new number format with the bullet.

6:02So up we go to our number formatting,

6:04choose more number formats, down to custom

6:06and my one that I created at the end here.

6:09There we go, with the lozenge. Let's click OK.

6:11Ah, very nice.

6:13So to wrap up then, we saw three different workarounds

6:16for creating a bulleted list in Excel.

6:19Firstly, we saw the the low tech solution

6:21where we put the bullet in one column

6:23and the text in the next column.

6:24And I think that's ideal

6:25if you want your notes on a separate sheet

6:27or which was my preferred method, we could copy

6:31and paste a bulleted list from Word into an Excel text box.

6:34And remember, it has to be from Word, not OneNote.

6:37And then finally, we saw

6:38that you could create a custom number format

6:41to capture the bullet.

6:42And that's all you need to put

6:43into the custom number format,

6:44the bullet symbol itself, a couple of spaces.

6:46And then @ is the placeholder for text.

6:49Let me just delete those scribbles

6:50so you can see it for your reference.

6:52But for now, I hope this has been informative for you

6:54and I'd like to thank you for viewing.

0:01(soft music)

0:06<v Simona>Every few months or so,</v>

0:08somebody somewhere asks me

0:09how to insert a checkbox in Excel.

0:12And in my humble opinion, this is one of the few features

0:15that Google Sheets handles a bit better than Excel.

0:18And so in this video,

0:19I'd like to show you a couple of options we've got

0:21for creating this kind of effect in Microsoft Excel.

0:25Now, I suppose the really low tech way,

0:28if we wanted a little checkbox

0:29to indicate which of these tasks was complete,

0:31the really low tech way would be to insert a symbol.

0:33So let's just for example,

0:35pick a little checkbox there for that task

0:36to indicate it's complete.

0:37And maybe we could choose the empty box there

0:40to indicate that's not complete.

0:42But I have to say this really is quite horrible,

0:45and I might just have to say

0:46that you can't be my friend anymore if you do it this way,

0:47because those are just symbols.

0:49They don't do anything.

0:50What we really need is something like this here

0:53where we can actually click the little box

0:55to check it or uncheck it

0:57with or without something else happening as well.

0:59So in this example, you can see when it's checked,

1:01the text appears struck through,

1:03and the strike through disappears when it's unchecked.

1:05So this is probably the sort of thing we're after.

1:08And this is actually done using Excel's form controls.

1:11And back in the main Excel course,

1:14if you have been following that,

1:15we did do an example of creating a little worksheet form

1:18in the macros skill.

1:19So you might have come across these before,

1:21but I do just want to do another example with you.

1:22So let me add task four to the end of the list here.

1:25So task number four.

1:26Let's add the little checkbox,

1:28and we do that from the developer ribbon tab.

1:30So if that's not showing,

1:31right click and choose to customize the ribbon

1:33and make sure it's switched on.

1:35And then you can go ahead

1:36and insert this little checkbox form control.

1:39So I can just click and drag to draw that checkbox.

1:42You'll need to delete the default text label there

1:44because we don't need it showing.

1:46And then I can use the adjustment handles

1:47just to adjust that,

1:49and then move it into position.

1:50There we go.

1:52Something like that.

1:53And then when I click away,

1:55well, that might be all you need.

1:56I can just click that to check it

1:57and click it again to uncheck it.

1:59So if that's the effect you want, then job done.

2:02That's quite nice and easy.

2:03But if you want other things to happen

2:05based on whether that checkbox is checked or not,

2:08then there's something else that we need to be aware of.

2:11Because what you probably are inclined to try and do

2:14is to create some sort of if function maybe

2:16that says if that box there is checked,

2:20then do this, otherwise do something else.

2:22But if we were to do that,

2:23we can't point our if functions or whatever

2:26at the checkbox itself,

2:28we have to do something else.

2:29And what we have to do is

2:30we have to identify another cell somewhere

2:34where the status of that checkbox,

2:36whether it's true or false, is captured.

2:38And you'll see that I do have a hidden column here.

2:41So let me just un hide that.

2:42And that's where I've already got the status

2:44of those three check boxes there.

2:46You can see that it changes

2:47depending on the status of the checkbox.

2:49So what I need to do for the one we added is

2:51right click on it.

2:52And incidentally, that's how you get

2:54those adjustment handles back again,

2:55if you want to move it or adjust it.

2:57But go into format control and say,

2:59"I want you to link the results of that checkbox

3:01to that cell there, please."

3:03And then click okay.

3:04So now when I check it or uncheck it, good.

3:07So that means we can actually use the value

3:09that's in this cell in our functions and our formulas,

3:13because that is representing

3:14whether the checkbox is checked or not.

3:16And then once we've put it in our formulas,

3:17we can hide that column like I did before.

3:20So let's try the example like we saw up here

3:22whereby the text is struck through.

3:24That's actually done with conditional formatting.

3:26So I want the appearance of this cell here to change

3:29depending on the value that's in cell D7.

3:32So I'm gonna click on that cell there,

3:34go to the home ribbon tab, choose conditional formatting,

3:36and let's create a new rule.

3:38And I'm gonna use a formula

3:40to determine which cells to format.

3:42So I'm formatting this based on

3:44whether that cell there is true.

3:48And you'll notice that that's gone in

3:50as an absolute cell reference.

3:52If you are gonna put this in,

3:53and then repeat it all the way down,

3:54then you would need to change that

3:56so that it was a relative reference,

3:57so that it did look at the correct linked cell

4:00as it filled down.

4:01But for us just doing a one-off,

4:02it wouldn't have really mattered.

4:04But anyway, the point is,

4:05if that is checked, if it is true,

4:06then I want the text to show as struck through.

4:09So let's just use the option to say yes,

4:10strike it through, please.

4:11Click okay.

4:12Let's try it out.

4:13Check the box, perfect.

4:14Uncheck it, good.

4:16So now I can hide that column again.

4:19So yeah, I'm pretty happy with that.

4:20That's exactly the effect I was after.

4:23But there's actually an alternative method

4:25I want to show you,

4:26which uses entirely conditional formatting.

4:28What we just done, it did use some conditional formatting

4:31to strike through the text here,

4:32but this was done with the form control.

4:34So for this, I want to actually show the little checks here

4:37using conditional formatting.

4:40So what we have to do, first of all,

4:41is to indicate whether a task is complete or not

4:43by putting in a one to say yes, the task is complete,

4:46or a zero to say no, it's not, okay?

4:50And then I'm going to change the appearance of these cells

4:52using conditional formatting,

4:54and I'm gonna basically be saying,

4:56if a one shows there, then give me a green tick.

4:58But if it's a zero, give me a red cross.

5:00So again, that's conditional formatting.

5:01Off we go, let's create a new rule.

5:04And yes, I'm formatting the cells based on their own values,

5:07but I'm going to be using an icon set.

5:10And let's choose, there we go, ticks and crosses.

5:13That's looking promising.

5:14So I'm gonna change these conditions down here.

5:17So I want it to show as a green check mark there

5:19when the value is greater than or equal to a one.

5:23And you must make sure you change the type here

5:25from a percent to an actual number.

5:27I want it to actually look at the number itself

5:29and decide whether it's a one or not,

5:31and if it is, show a green check mark.

5:33And let's just change this to show the red cross.

5:35Therefore, if it's less than or equal to zero,

5:39but let me just change it to number.

5:41There we go.

5:41So I'm hoping...

5:42Oh, and there's one more thing we need to do.

5:44I need to choose the option show icon only.

5:46Otherwise, it will leave the one and the zero

5:48in the cell itself.

5:49But I only want to see the checks or crosses.

5:52So let's click okay to that.

5:54That's looking promising.

5:54Let's just fill that down

5:56and let's choose the option to only fill the formatting.

5:59Perfect.

6:00So if I want to mark task two as complete,

6:03then I click on that cell and type in a one.

6:05I change the value,

6:06the underlying value there to a one or to a zero.

6:09That's how I change it from ticks and crosses.

6:12And I think that does actually look really quite nice.

6:14I mean, it's an attractive display

6:16because it's got a bit of color.

6:17It's a more visual graphical way of working.

6:20And I think either option,

6:21either the conditional formatting one here

6:23or the little form controls over there,

6:25either option is completely feasible.

6:27The one you choose,

6:28I think is gonna depend on the effect you're after

6:30and which one makes best sense

6:31for your particular scenario.

6:33For example, if somebody else is using your spreadsheet,

6:35then it's pretty intuitive to check a box

6:38to mark something as complete,

6:39whereas they might not know

6:40that they would have to change the value to one or zero.

6:43So that might be a factor.

6:45And you'll also see in this spreadsheet itself,

6:47if you weren't following along,

6:49if you unhide these cells here,

6:52I did do an example using conditional formatting

6:54for your reference,

6:55and you will also see

6:56that I have included another conditional formatting rule,

7:00much like we did with this example,

7:02whereby if the value equals one,

7:05then that task also appears struck through.

7:07So same sort of thing.

7:08But you can have a look

7:08at the underlying conditional formatting

7:10for those cells if you choose.

7:12I hope this has been informative for you,

7:14and I'd like to thank you for viewing.

0:01(bright music)

0:06<v Simona>We've got a whole separate skill</v>

0:08in the main Excel course on using charts

0:10where we see an example of every chart type available.

0:13But in this video, let's run through

0:15a couple of quick tips for your charts.

0:17So let's imagine we want to plot this data on a chart.

0:19And it would be very tempting, wouldn't it,

0:21to just press control A and create a chart from that lot.

0:24But don't forget, you wouldn't normally include

0:26your totals on your chart 'cause those figures

0:28are gonna be much bigger

0:29and therefore it's gonna distort your chart.

0:31So you don't include your totals,

0:32but you do include your titles

0:35so that the chart is correctly labeled.

0:37But actually for this particular example,

0:39I don't want to include the US.

0:41So you can select ranges which aren't next to each other.

0:44So let's say yes, I do want my titles,

0:46and I also want to just plot the UK and Europe.

0:49And notice that I have included that blank cell there

0:52to ensure that the different ranges I've selected

0:54are all the same shape so that they can match up properly

0:57when the chart is created.

0:59But anyway, the keyboard shortcut

1:00to just quickly put in the default chart,

1:02well, you could try F11 to give yourself

1:05a brand new chart on a separate chart sheet,

1:07or if I just go back,

1:09or you could try Alt F1 to give you a new chart

1:12on the same sheet as the selected data.

1:14And sure, you can see that it's used the data

1:17that I've identified there

1:18and everything is appropriately labeled.

1:20But you'll also see that if you are following along with me,

1:23I've got a bar chart here,

1:25rather than the traditional vertical column chart.

1:28And you might be thinking, well, why is that?

1:30Well, that's because I have changed my default chart type.

1:33I mean, let's imagine in our organization

1:35we always use these horizontal bars

1:36rather than the vertical ones,

1:38so you can change your default chart type.

1:40So if you just go to change chart type here,

1:42and then just right click to set as default chart

1:44and you'll see the green check mark there

1:46to indicate that yes, the bar chart is my default,

1:49but actually on this occasion I'm gonna change it

1:52just to the clustered column instead.

1:54So that's probably what you you're used to seeing.

1:56But now let's imagine I want to add

1:58some extra data to this chart.

1:59You know, I belatedly realized

2:00that I did want to include the US figures,

2:02and this is where I'd like you to pay attention

2:05to what you are clicked on in the chart.

2:06So can you see, when I'm clicked on one of the series,

2:09it highlights that for me

2:10and if I click on the other one.

2:12So therefore if I want to include the US,

2:13well just make sure I'm clicked on the chart,

2:15but not on any series in particular,

2:17and then you can actually just extend the selection there

2:20to include the other figures on the chart.

2:22And that's immediately looking a little bit odd

2:24because the US figures are so much bigger

2:26than the other figures so we will address that in a second.

2:29But what I tend to do is

2:31go ahead and create the chart with all of my data,

2:34rather than just a subset like we did,

2:36and then you can use this filter button over here

2:39to turn off any particular series

2:41or categories that you don't need.

2:43So let's imagine I only want to see

2:45the US figures and the UK.

2:47So if I uncheck Europe and then choose apply,

2:49then it's only going to gimme those two series.

2:51But we really do have to deal with

2:53the problem we've got here of the US figures

2:55being so much bigger than the UK figures

2:58that they don't really work very well on the same chart.

3:00So this would be a very good argument

3:02for using a combo chart.

3:04So we'll have one series shown as a line,

3:06you can see in their little preview there

3:08and the other series shown as a bar,

3:10but that's not going to be quite enough

3:11to make this meaningful.

3:13The trick is to make sure that you identify

3:16one of the series to show on a secondary axis.

3:20That means it can use a different scale.

3:22So if I click okay to that,

3:24that is looking much more meaningful.

3:26The UK, the orange line, is using the smaller numbers

3:29on the right hand side here,

3:31whereas the US, the blue blocks,

3:32are using the numbers over on the left hand side here.

3:35But possibly my favorite charting tip

3:37is linking the chart title to the value in a cell.

3:41So you will already know that you can

3:43manually click in there and over type that

3:45with whatever you want as the chart title,

3:47or you could turn off the chart title

3:49altogether if you wanted to.

3:51But let's say I wanted to display

3:53whatever is in this cell here.

3:55So with that chart title selected,

3:57so don't click in it as if you're editing the text,

3:59just with it selected now type your equals

4:02and just click on the cell that you want it to reflect

4:04and then press enter.

4:06Fantastic, that is now linked.

4:08So if I were to change this to say global sales,

4:11then that's going to be reflected in the chart.

4:14So the text is linked but the formatting is not.

4:16So if I were to click back on that title

4:19and change the formatting,

4:20then you can see that is independent

4:23of the formatting of the cell that it's linked to.

4:26As usual, here's your summary of those tips.

4:28But for now, I hope this has been informative for you

4:31and I'd like to thank you for viewing.