Microsoft Excel 2016 - Learn Excel 2016 Beginners Tutorial Video

Aug 13, 2023

Microsoft Excel 2016 - Learn Excel 2016 Beginners Tutorial Video

Exercise Files: bit.ly/43QsTCO

Learn Microsoft 2016 from the pro’s. In this FREE beginners tutorial for Microsoft 2016 you will learn the basics of Excel 2016, and Tips and Tricks so that you can get the most out of your Excel experience. Our beginner videos start with simple tasks like selecting cells and entering data, then quickly move to calculating data with basic formulas and functions. The intermediate portion shows you how simple it is to sort and filter and then moves to one of Excel’s most popular features, Pivot Tables. Our advanced videos start with functions like IF and VLOOKUP and finish up with Macros.

Some of the other topics in the Microsoft Excel 2016 tutorial include:
Start Screen \u0026 Templates, Ribbon \u0026 QA Toolbar - Part 1, Ribbon \u0026 QA Toolbar - Part 2, Backstage View, Interface, Share Pane, Tell Me, Smart Lookup, Navigating, Keyboard Shortcuts, Entering Text
Formatting Numbers, Deleting \u0026 Formatting, AutoSum, Simple Formulas, Simple Functions, Absolute Referencing, Inserting Rows
Inserting Columns, Moving \u0026 Copying Data, Autofill, Cell Styles, Worksheet Themes, Excel Templates, Grouping Worksheets, Freeze Panes, Custom Views, Spell Check, Printing, Flash Fill, List Design \u0026 Single Level Sorting, Multi-Level Sorting, Custom Sorting, Filtering, Multi-Level Filtering, Search Filtering, Format as Table, Table Style Options, Remove Duplicates, Convert to Range, Subtotal, Multi-Level, Subtotaling, Remove Subtotals, Quick Analysis Charts, Inserting Data Charts, Formatting Data Charts, Chart, Templates, Spark lines, Printing Charts, Importing From Web, MS Query, Exporting Overview, Pivot Tables, Multiple-Field Pivot Tables, Drill Down Reports, Pivot Charts \u0026 Grouping Fields, Slicer Tool, Data Validation - Part 1, Data Validation - Part 2, Cell \u0026 Sheet Protection, File Encryption, Conditional Formatting, Linking Data, Inserting Comments.. and more

Please subscribe to Learn iT! and check out our website (http://learnit.com) to browse other Microsoft Courses, Project Management, and IT Training courses.

For ad free training and certificates please join Learnit Anytime:
https://www.learnitanytime.com

For Private Group Trainings please visit:
https://www.learnit.com/private-group…

#Excel

(C) 2021 Learnit, Inc.
Any illegal reproduction of this content will result in immediate legal action.

Content

6.08 -> - [Voiceover] Hello, and welcome to Excel 2016.

9.969 -> My name is Sean Bugler and I'll be your host today

12.293 -> as we go through all of Microsoft Excel 2016.

17.936 -> Whether you're a beginner, advanced,

20.142 -> or you've been using this since the day you were born.

22.51 -> Microsoft Excel has something for everybody,

25.169 -> and as we go through these lessons I'm sure

27.131 -> you're gonna find that there are features

28.605 -> even you didn't know were available.

31.914 -> Let's go ahead and get started.

34.863 -> When you first open up Excel for the first time

36.872 -> this is the view you're presented with.

39.774 -> This is called the start screen and this is where

41.98 -> we have access to any recent documents

44.012 -> that we may have worked on in the past.

46.203 -> Additionally we also have access to the ability

48.362 -> to open workbooks from other sources.

52.774 -> Beyond that we also have access to a template view.

56.942 -> This template view is where we have access to

59.52 -> blank workbooks, additionally in newer versions of Excel,

62.979 -> we're also provided with a tour field.

65.623 -> So if this is the first time that you've ever opened

67.562 -> this program and you'd like to see what new features

69.431 -> are available this is generally the best place to find it.

73.576 -> Additionally, we have access to a number of templates

75.573 -> provided by Microsoft that allow us to

79.563 -> work with files that have been created by others.

82.593 -> This is a really quick and easy way to start very quickly.

86.889 -> Additionally, at the top we can search through

88.387 -> online templates, for instance, maybe I'd like to

92.067 -> see a grocery list.

99.428 -> And you'll see that not only is there more than one

102.365 -> there are a couple and they look

104.037 -> very different from each other.

107.396 -> A monthly meal planner, that might be something I need.

111.877 -> To get back to the templates view if you do decide

113.955 -> to use the search feature, we're gonna use the Home button.

118.051 -> But before I do, I'd like to point out that

120.175 -> there are a number of categories,

122.219 -> so if you're not entirely sure what it is

123.565 -> that you're looking for but you'd like to

125.04 -> narrow it down just a little bit,

126.305 -> you can always use the category selection field as well.

130.276 -> So let's go ahead and dive in.

132.238 -> I'm gonna go ahead and start with a blank workbook.

134.769 -> To select it I'm simply going to click

137.497 -> and it's going to dive me right in.

139.506 -> I know the inclination is to do a double-click

141.468 -> but in 2016 that's not necessary.

144.742 -> And with that we are plopped straight

146.576 -> into an Excel workbook.

149.734 -> Go ahead and pause the video for just a moment here

151.696 -> and join me in the blank workbook view.

155.783 -> So let's talk about the structure of Excel.

158.024 -> If you've used Excel in the past, not a lot's changed,

160.857 -> we still have access to the ribbon.

162.854 -> For those of you who aren't familiar with the ribbon

164.897 -> it's this giant part of the screen right here.

168.206 -> Often considered to be our navigation pane,

170.214 -> this is where we have access to all of our

171.84 -> tools and feature sets built-in to Excel.

175.079 -> So within that we have Home, Insert,

178.051 -> Page Layout, Formulas, and so on.

182.916 -> Inside of each tab we have access to something

184.831 -> called command groups, these are small groupings

187.409 -> of tools based on one of two sets of criteria.

191.124 -> Either they manipulate similar content,

194.224 -> or they do similar things.

197.463 -> So either they work with some of the same subjects,

200.772 -> or their actions are all very similar.

203.57 -> For example, we have the Clipboard,

206.019 -> which is exclusively focused on cut, copy and paste.

210.872 -> Beyond that we have access to the Font command group,

213.903 -> whose explicit purpose is to manipulate text.

217.78 -> As we go on you'll see me reference the command groups

220.114 -> quite a bit, and as far as learning Excel goes

223.701 -> it does us well to remember these command groups

226.72 -> are often named very intuitively.

228.972 -> So for instance, focusing on the Alignment command group

232.328 -> I could reasonably ascertain that we're

233.883 -> probably going to be focusing on

235.602 -> left-align, right-align, and so on.

238.783 -> This is what's going to allow us to work

240.199 -> with the alignment of the text.

242.034 -> Hence, the Alignment command group name.

244.901 -> Each command tab has their own series of command groups.

248.373 -> For my 2013 users, not a lot's changed,

252.018 -> frankly there's not much at all that's changed.

254.48 -> As we go through you might notice a few things

256.07 -> here and there, but don't worry if you've invested

259.333 -> a bunch of time in learning older versions

261.62 -> of Excel you're gonna be just fine.

264.73 -> The next thing I'd like to talk about is something

266.936 -> that we've seen pretty much the entire time

269.316 -> we've worked inside of Excel but haven't

270.988 -> given a lot of thought to.

273.078 -> This can be found up in the top left corner,

275.4 -> we usually know it as just the Save icon,

278.128 -> or the Undo icon, because that's generally

280.265 -> all we've used it for but it actually has a name.

283.283 -> This is called the Quick Access Toolbar.

285.919 -> The Quick Access Toolbar provides us access to

287.834 -> exactly that, a series of tools based on our needs.

291.677 -> If you click on the drop down arrow

293.07 -> on the right-hand side of this,

296.252 -> you'll see a list of provided tools;

298.144 -> New, Open, Save, Email and so on.

302.184 -> If you're really observant you'll notice

303.357 -> that these checkboxes that already exist

305.156 -> directly correspond with the tools that are

307.502 -> already available in the Quick Access Toolbar.

310.392 -> Save to save, Undo and Redo, to undo and redo.

314.829 -> So logic dictates that if I add a checkbox

316.783 -> to a tool that doesn't have a checkbox what will happen?

321.195 -> If you said it will add it to the

322.542 -> Quick Access Toolbar, you'd be right.

324.91 -> If a click New, voila.

328.335 -> A tool to create new workbooks has just been added

330.75 -> to my Quick Access Toolbar.

333.362 -> Now I hear what you're saying,

334.755 -> Sean I already know the keyboard shortcut for new,

337.414 -> I don't need that.

338.714 -> In fact, I don't need any of these tools.

340.282 -> None of them really strike me as something that

343.289 -> is imminently necessary to the point where

345.123 -> I would need to put it up here.

347.267 -> Well I hear what you're saying, for my users out there

349.639 -> who are curious to know what the keyboard shortcut

351.916 -> for a new workbook is by the way, it's Control + N.

355.933 -> But again, I hear you, these might not necessarily be

358.453 -> the tools that you need quick access to.

361.018 -> But there might be a tool that you do.

363.712 -> For example, you might notice in the

365.79 -> Font command group here, there's a blatant omission here,

369.749 -> at least according to me.

371.641 -> Of course I'm talking about the strikethrough tool.

374.172 -> I do a lot of collaboration

375.566 -> in Microsoft Excel and you might too.

377.934 -> The strikethrough feature is a really useful tool.

381.069 -> Or maybe there's another tool that you use

382.416 -> all the time that you'd like access to.

385.771 -> But if I click on this drop down here,

387.489 -> I don't see access to strikethrough,

391.123 -> but I do see More Commands.

395.488 -> If you notice on the right-hand side of More Commands

397.601 -> you'll see that there's an ellipses, that dot, dot, dot.

401.038 -> That dot, dot, dot, simply means that there is more

403.372 -> behind the scenes, there's a dialogue window.

406.692 -> So I'm gonna go ahead and click on More Commands here

409.502 -> and I gain access to a series of tools

412.601 -> and on the right-hand side I have my tools

414.773 -> that are inside the Quick Access Toolbar,

416.816 -> my Save, my Undo, my Redo, and so on.

421.506 -> Now I think we can all agree that we don't need New File.

424.455 -> So to get rid of that I'm gonna highlight it

426.812 -> and either use the Remove tool, or double-click.

430.574 -> Either way will remove it from your Quick Access Toolbar.

433.871 -> Go ahead and pause the video

435.311 -> and let's get to this point here.

437.667 -> Once again to remind you how we got here,

441.673 -> we were inside the Excel blank workbook

444.401 -> and we navigated up to the drop down arrow

446.584 -> on the right-hand side of the Quick Access Toolbar.

450.705 -> Having clicked on that we navigated down to More Commands.

456.592 -> Go ahead and do that now and join me after the break.

462.106 -> So now what I'd like to do is I'd like to find

464.068 -> a tool to add to my Quick Access Toolbar.

466.959 -> So we'll go ahead and stick with the example

468.956 -> I gave previously, I'd like to find Strikethrough

471.162 -> and add it to my Quick Access Toolbar.

473.891 -> So I'm gonna go ahead and scroll down here,

475.702 -> it seems like it's alphabetized

477.083 -> so this should be pretty easy.

479.243 -> And I get down to the bottom here,

480.473 -> I've got my Save, Save As, Set Print Shapes,

485.117 -> but no Strikethrough, well what gives?

488.426 -> I thought this instructor knew what he was talking about.

491.723 -> Did you figure it out?

494.626 -> If you said it's because we are inside

496.17 -> the Popular Commands drop down here

498.213 -> you'd be absolutely right.

500.292 -> Despite my objections, Microsoft doesn't agree

502.486 -> that Strikethrough is considered a popular command.

504.901 -> And in fact there are a lot of tools that don't meet

506.944 -> the criteria for being considered a popular command.

509.591 -> One of them might also be the tool

511.391 -> that you're thinking of that you'd like to use.

514.293 -> So to fix that we're going to click on

515.385 -> this drop down arrow here,

519.436 -> and we're gonna go to All Commands.

523.105 -> This is a list of every single tool

525.961 -> inside of Microsoft Excel and there are a lot.

529.56 -> How many you ask?

530.745 -> Not a clue, a lot.

533.322 -> In fact most of them you'll never use and that's okay,

536.596 -> because this is about finding the tools we do use.

539.243 -> In fact we're finding the tools that we use a lot.

542.761 -> So let's go ahead and find that Strikethrough tool.

548.148 -> Now this scrolling is taking a long time here,

549.82 -> so I'm actually just going to click inside this list

552.026 -> and press the S key, now at this stage right here

555.451 -> you can't type out the full name of the tool.

557.54 -> I know that would be nice, unfortunately that functionality

560.431 -> has just not been added yet.

562.881 -> But it does take me at least to the S section.

566.48 -> And I'm looking, and I'm looking, and Strikethrough.

571.426 -> Go ahead and find Strikethrough from the All Commands list,

575.327 -> and I'd like to add it to my Quick Access Toolbar now.

578.682 -> So either by highlighting it and clicking the Add button

582.061 -> on the right-hand side here, or by double-clicking on it

585.66 -> let's go ahead and add it to the Quick Access Toolbar.

590.397 -> A common question that I often get asked is,

593.578 -> let's say I do set all this up, is it going to be available

595.9 -> in all of my workbooks or just this one?

599.789 -> And the answer is it's gonna work in all of them.

602.436 -> For as long as you have this version of Excel

604.607 -> and you're on your computer these tools

606.848 -> will be available to you.

608.566 -> If you'd like to reset it at any point,

610.319 -> let's say you add 40 tools, you can always click

613.628 -> on the Reset button on the bottom right here

616.972 -> and select either Reset Only Quick Access Toolbar

620.559 -> or for those of you who go crazy with it and decide

622.487 -> to customize the ribbon as well,

625.552 -> we can Reset all Customizations.

630.254 -> Now I'm not gonna do that because I want Strikethrough.

632.32 -> But know that that's available for you.

634.619 -> Once you've found all the tools you'd like to add

636.094 -> to your Quick Access Toolbar go ahead and click OK.

640.598 -> And just like that I have access to Strikethrough.

644.708 -> How easy was that?

646.589 -> Go ahead and pause the video and get to where I am.

649.817 -> Once again to reiterate, in order to customize the

652.313 -> Quick Access Toolbar we clicked on

653.95 -> the drop down arrow right here,

657.537 -> we selected More Commands,

660.591 -> we found the tool and if it's not inside

662.425 -> the Popular Commands drop down we now know

664.619 -> we can change that to All Commands.

668.915 -> From there we find the tool in this giant list of ours,

672.015 -> and either highlight it and select the Add button

675.161 -> or double-click to see it added to our Quick Access Toolbar.

679.573 -> And lastly we click OK to lock it in.

683.613 -> Go ahead and pause the video and do that now.

686.307 -> All right, so we've customized the Quick Access Toolbar

690.266 -> it feels good to make the program

691.45 -> a little bit more ours, doesn't it?

694.097 -> In fact Microsoft provides a lot of tools

696.013 -> that allow us to customize different parts

698.509 -> of the Microsoft Excel interface.

701.121 -> And we'll talk a little bit more so

702.27 -> about those in just a little bit.

703.989 -> But before I do, I'd like to dive into

706.311 -> how we can make changes to the file.

709.167 -> Up until now we've been referencing tabs that are

712.406 -> to the right of the Home tab.

714.937 -> These are tabs where we can make changes within the file.

718.13 -> However, there's one tab out of all of these

720.661 -> that is slightly unlike the other.

723.981 -> In this case I'm talking about, File.

726.756 -> Go ahead and find the File tab and let's click inside of it.

732.387 -> And with a swoopy animation we dive into

734.871 -> what we like to call the backstage view.

737.565 -> This is where we can make changes about the file.

741.199 -> All other tabs inside of Excel make changes inside the file,

745.271 -> but this is where we can see

747.024 -> the grand scheme of things if you will.

749.474 -> The very top we have access to Info,

751.336 -> where we can protect our workbook, inspect our workbook,

754.958 -> manage our workbook, and even view options.

759.916 -> As we go throughout the lessons inside of Excel

761.796 -> we'll talk a little bit about more of these.

766.858 -> Inside the New view here, we see exactly the same view

770.399 -> we saw inside of our start screen, our templates.

774.765 -> Of course we can always select our Blank Workbook

776.84 -> and yet again we have access to our Search Bar

779.208 -> so we can search for online templates.

782.831 -> The Open tab does exactly that, it opens any workbooks

785.605 -> that we may have available to us.

788.334 -> And in 2016 it's easier than ever to open

790.946 -> from online storage solutions.

793.93 -> In this case I have several OneDrive accounts connected

797.076 -> as well as a share point site for those of you

799.07 -> who operate inside share point environments.

801.682 -> Additionally I also have access to files

803.807 -> on my PC and if I'm not sure and I would just like

807.441 -> to get to a more familiar view, I can always click Browse.

811.272 -> And that will bring up my open view.

815.44 -> Additionally I have access to Save and Save As,

818.331 -> however because I have not saved this file yet at all,

822.221 -> I can't use the Save button.

824.341 -> For those of you who aren't sure what the difference is

826.582 -> Save As creates a new copy of a file

829.124 -> that you may have already saved before.

833.165 -> Save on the other hand only works if

835.359 -> a file has already been saved and already has

838.168 -> a place that you can save it to.

840.885 -> If you've never defined a save point,

842.754 -> Save won't work and it will force you into

845.042 -> the Save As view as you can see by my clicks here.

848.223 -> No matter how many times I click on Save

850.382 -> it forces me into the Save As view.

855.212 -> Print brings us down into our print preview field,

857.337 -> where we can make a number of changes

858.73 -> before we select the final Print.

862.399 -> Inside of Share we have the ability to work with

864.802 -> a number of different features here,

866.845 -> and this is kind of cool we've actually seen

868.598 -> some expanded options.

872.441 -> We now have access to something called the Share with People

875.866 -> now there's another place that we're gonna have access

877.619 -> to this but I'd like to point out here

879.79 -> that before we can do anything we have to save our document

883.215 -> to a OneDrive location, once we've done that,

886.466 -> we'll be able to do just that, share with people.

889.647 -> And we'll talk about that in just a little bit.

892.213 -> Within Email we can send as an attachment,

894.291 -> for those of you who have Outlook set up

896.378 -> or another default file email solution,

899.814 -> clicking this button will open up Outlook,

902.02 -> open up a compose email and attach that file

905.039 -> all in one click, and that feels pretty good.

908.998 -> We additionally have the ability

910.054 -> to send as a PDF or XPS file.

917.74 -> Additionally, we're seeing a resurgence

918.924 -> in the Present Online feature.

921.339 -> This allows us to present this program

922.767 -> in a Skype meeting or conversation,

925.38 -> for those of you who are working in environments

927.179 -> that are going to be engaging in Skype for Business

929.594 -> this is going to be a really powerful tool.

933.309 -> And lastly, we have Send by Instant Message.

936.734 -> So for all of you who work in corporate environments

939.544 -> if you've got Skype for Business already interacted

942.121 -> you can actually save this via Skype for Business

944.408 -> or for those of you who are more familiar

946.359 -> with the older version of this, Lync,

948.321 -> to anyone on our network.

950.318 -> Everyone gets a copy and everyone gets an instant message.

956.018 -> Underneath Share we have Export.

958.143 -> This allows us to either create it as a PDF or XPS file

963.298 -> or change the file type to a number of

965.782 -> different options found here on the right.

968.569 -> Where else do we have access to this?

970.728 -> Well funny you should ask, there it is right there.

974.083 -> These are all things that we can do

975.628 -> inside the Save As dialogue box as well.

980.559 -> Underneath export we have Publish,

982.44 -> which is exclusively focused on Power Bi,

985.168 -> something that has newly been implemented

986.828 -> out of the box inside of Excel 2016.

991.611 -> Underneath Publish we have Close,

993.202 -> which will do exactly that, don't click on this just yet.

998.461 -> Underneath that we have Account where you can see

1001.039 -> information about the account here and interestingly enough

1004.882 -> we can also change the theme here.

1008.341 -> Now what does that mean to change the theme?

1010.803 -> Well, I'll show you, this isn't the best view to do it

1013.659 -> but I will show you shortly.

1016.805 -> You can also see any Connected Services

1018.927 -> that might have been placed here.

1021.667 -> Lastly let's talk Options, go ahead and find Options

1025.579 -> on the very bottom here and click on it.

1030.316 -> This is where we can change a lot of deep down

1032.928 -> features inside of Excel.

1034.728 -> Things from the default file font,

1036.481 -> to where these files get saved,

1039.175 -> but this is also where we can change

1042.565 -> the Office theme, and this is where we come back

1043.505 -> to the ability to customize this program to our liking.

1047.127 -> In Excel 2016, and in fact, all of Office 2016

1051.667 -> we see that these programs have gotten

1053.385 -> a new colorful option.

1056.299 -> Now this theme is being provided across

1058.099 -> all of those programs but we do have

1059.736 -> the ability to change that.

1061.779 -> Currently we find here in the Office Theme

1063.37 -> that we can change it form Colorful to

1066.226 -> maybe a more familiar view.

1069.453 -> If I select the White office theme and click OK

1072.457 -> all of my Excel 2013 users are going

1074.872 -> to breathe a sigh of relief, because this is

1076.985 -> a much more familiar view.

1078.866 -> The File tab now has its distinguished coloration

1082.175 -> the remaining tabs do not.

1085.855 -> Additionally we've been provided one more theme

1088.305 -> because two wasn't enough, we have the Dark Gray theme

1092.508 -> and this one is brand new to Microsoft Excel

1095.201 -> and all of the Office productivity programs.

1099.613 -> This provides a dark user interface for the very first time,

1103.05 -> and it's getting rave reviews from users

1105.627 -> who prefer a dark user interface whether because

1107.752 -> the white is too bright or because they need something

1110.24 -> that's a little bit more calming to the eyes.

1112.771 -> The dark user interface is a really cool feature

1115.139 -> that's been built-in new to Office 2016.

1119.47 -> Personally, I prefer the Colorful theme,

1121.618 -> so I'm going to stick with that for the duration

1122.999 -> of this presentation but do know

1124.961 -> that you have these options available.

1128.526 -> Go ahead and play around with those options here

1130.488 -> for just a moment and let's join us back after the break.

1133.933 -> Once again to get into that view,

1136.104 -> we jumped into the File, Options,

1141.838 -> and within Options we had access to the Office Theme

1145.832 -> we also had access to the Office Theme

1147.947 -> inside the File, Account,

1153.137 -> where we can change the Office Theme here, as well.

1156.631 -> So however you'd like to do it, go ahead and try that

1159.081 -> and come back after that.

1162.668 -> So now that we've seen a little bit about

1163.702 -> what's new inside of Excel,

1165.652 -> let's talk about some things that have stayed the same.

1169.077 -> On the top left-hand side we have access to

1170.842 -> our Cell Map field here.

1173.93 -> This tells us where our active cell is,

1176.438 -> so for instance currently it's in cell A1,

1179.085 -> and amalgamation of the column value A

1182.034 -> and the row value 1, A1.

1185.54 -> However, if I place my cursor anywhere else

1187.502 -> I can see exactly where that is without

1189.383 -> having to make that juxtaposition of

1191.798 -> D and 9 in this case.

1194.282 -> I can simply look up here in the top left corner

1196.848 -> and see that I am currently inside cell D9.

1201.248 -> Beyond that in the right-hand side I have the Formula Bar.

1204.348 -> The Formula Bar is where I can manipulate

1206.217 -> things going on inside of that cell

1209.201 -> additionally I also have access to my Insert Functions tool

1212.673 -> which we'll talk about in a little bit.

1216.748 -> On the right-hand side we have access to our trusty

1219.972 -> Scroll Bar this is where we can scroll up and down

1224.533 -> and if you'd like to, additionally right and left using

1228.715 -> these scroll bars found to the right and on the bottom.

1231.989 -> Simply by clicking and holding the Scroll Bar

1233.846 -> we can do that, sliding to the left and right,

1237.039 -> using the bottom Scroll Bar or up and down

1240.487 -> using the right Scroll Bar.

1242.902 -> Additionally I can use these arrows to expand or contract

1246.966 -> my view in the upward or downward direction,

1250.251 -> or the right and left direction as necessary.

1255.093 -> As with all Microsoft programs in the top

1256.822 -> right-hand corner I have access to my trusty three here;

1260.329 -> Minimize, Maximize or Restore,

1264.055 -> depending on the view you're in, and Quit.

1269.069 -> Additionally to the left we have a new button here

1271.078 -> the ability to collapse our ribbon view.

1273.864 -> So for instance if you're working on a screen

1275.745 -> that is less than the size you'd like it to be

1278.845 -> and you need more working space,

1280.679 -> you can select the Display Ribbon Options

1283.791 -> and auto hide the Ribbon.

1286.774 -> This completely removes the Ribbon from view

1289.352 -> and when we need it we click on that Ellipses

1291.604 -> that pops up at the top and we gain access to it again.

1296.062 -> Once again, if you're in this view, simply find the Ellipses

1300.033 -> and give it a click and it will reveal itself to you

1303.388 -> however to lock it in you're gonna need to select

1306.302 -> that same tool again and deselect,

1309.321 -> by selecting the Show Tabs and Commands.

1313.297 -> Go ahead and take an opportunity to play with those features

1315.329 -> for just a moment and come back to me.

1318.359 -> Now no matter what version of Excel you're coming from,

1321.227 -> there are two things for sure that you had to have noticed

1323.92 -> that are very different from

1325.522 -> any other version of Excel, ever.

1328.785 -> And we'll talk about both of them but let's go ahead

1330.863 -> and start with this one right here.

1332.546 -> Because we've already seen an example of where

1334.299 -> this tool also exists in other parts of this program.

1338.27 -> This is called the Share button.

1340.313 -> The Share button allows us to do just that,

1342.554 -> share the workbook we're in to encourage

1344.481 -> collaboration or to submit a final product.

1347.303 -> Go ahead and click on the Share tool,

1351.32 -> and you'll see that the first thing it asks us to do is

1354.013 -> save a copy of our file to an online location,

1357.485 -> and it provides us with a button to do just that,

1359.447 -> Save to the Cloud.

1361.327 -> In order to use this tool you do need to save to the cloud.

1365.217 -> So I'll go ahead and do just that.

1367.504 -> I'm gonna click on Save to the Cloud here

1369.617 -> and it's gonna ask me where I'd like to save it.

1372.16 -> And of course it provides my Save As menu,

1375.109 -> which we've seen already.

1377.105 -> I'm gonna go ahead and save it to my OneDrive account.

1382.597 -> And maybe I'll go ahead and call this Excel 2016 Training,

1389.586 -> and I'll go ahead and click Save.

1393.998 -> And there it is, and just like that

1395.705 -> I've saved it to the cloud.

1399.884 -> Once I've saved it in the cloud, I'm provided with

1401.591 -> new share options, I can invite people,

1405.225 -> and depending on what it is that I'm asking for

1407.024 -> I can control how they have access to this file.

1411.123 -> So depending on who it is I can ask them

1412.795 -> to view the file and edit it or maybe

1415.813 -> I'd only like them to view it and give me

1417.729 -> their thoughts in a separate medium.

1419.702 -> I don't necessarily want them to have access

1421.003 -> to my functions and formulas, or any of my raw data

1424.637 -> to mess up or accidentally delete.

1428.189 -> Lastly, I can create a message asking them

1433.135 -> for either general assistance or maybe more specific things.

1442.179 -> At the very bottom here we have access to

1444.432 -> anybody who has already been shared this file.

1447.218 -> Currently, I'm the only one and you can see

1450.121 -> as denoted by the Owner tag that I'm the only person

1453.638 -> that has access to this because I'm the one that created it.

1458.666 -> Once you've invited somebody you simply click

1460.593 -> the Share button and once they've accepted it

1463.53 -> you'll be able to see their name populate

1466.061 -> on the list down here below.

1473.166 -> Go ahead and close that.

1476.278 -> Now we've almost come to a close on our tour

1477.903 -> of the interface of Microsoft Excel 2016,

1481.305 -> but there's one part of the Ribbon left

1483.186 -> that we haven't talked about yet, and it's the biggest one.

1487.737 -> In fact, it's the biggest change to ever come

1489.653 -> to the Microsoft Office Ribbon

1491.371 -> since the introduction of the Ribbon.

1493.554 -> It's called the Tell Me bar and it's indicated

1496.201 -> by a light bulb followed by the phrasing,

1498.569 -> tell me what you want to do.

1501.054 -> Now I know that sounds a little vague

1503.259 -> and a little too open, but that's because

1505.5 -> this tool is so powerful that that's all it needs

1508.287 -> is for you to talk to it.

1510.539 -> For instance, let's say I don't remember

1512.42 -> how to insert a column.

1514.66 -> So in this field right here, I'm just gonna go ahead

1516.576 -> and click inside and say, "How do I insert a column?"

1524.181 -> And check it out, the tool that pops to the top

1526.468 -> of the list is the Insert tool and it's not just

1530.195 -> what that tool is, oh lost it there,

1537.114 -> but if I hover over it, check it out.

1540.307 -> I can either insert individual cells, I can insert rows,

1544.103 -> or there it is insert entire columns or sheets.

1549.339 -> If I give it a click it doesn't just show me where it is

1551.917 -> it actually inserted an entirely new column for me.

1557.362 -> This is a game changing feature because there are

1559.765 -> so many tools inside of Microsoft Excel there's no way

1562.935 -> you're going to remember where all of them are.

1564.943 -> And for the times where you don't remember

1567.219 -> you don't have to Google it anymore.

1568.612 -> You don't have to Bing it if that's your thing too.

1571.387 -> All you have to do is go to the Tell Me bar and

1574.301 -> say, "Hey, how do I add comments?"

1579.363 -> And there it is the Insert Comment feature.

1583.542 -> The Tell Me feature is a huge introduction

1586.607 -> into navigating this increasingly complex program.

1590.532 -> And while Microsoft just made huge strides

1592.378 -> in terms of making this a more usable program,

1595.106 -> not everybody is going to remember where everything is.

1597.974 -> And that's okay and in fact it's easier than ever

1600.47 -> to not remember with a tool like this.

1603.744 -> Go ahead and take a moment to try it out for yourself.

1605.996 -> The Tell Me bar it's a huge introduction into

1608.933 -> Microsoft Excel navigation and it could potentially change

1612.115 -> the way you interact with this program entirely.

1615.389 -> All right, so while we're on the topic

1617.885 -> of new features being introduced

1619.232 -> into Excel 2016 let's not stop there.

1622.54 -> I've got one more amazing feature I'd like to show you.

1626.465 -> Even cooler than potentially the Tell Me bar.

1629.866 -> Inside this worksheet right here I've got access to

1632.432 -> three cells with some content in it I might not understand.

1636.821 -> For instance, YoY, now why oh why would I put

1640.327 -> that inside of a worksheet?

1642.289 -> Well why oh why would anybody?

1644.948 -> Well it's because it means something to some people.

1647.188 -> And you might not be entirely sure what it means either.

1650.915 -> Now where do you turn if you don't understand something?

1653.934 -> Well in 2015 most of us Google it.

1656.732 -> However, with the introduction of this new feature

1659.112 -> that I'm about to tell you about Excel doesn't need you

1662.537 -> to jump out of the program in order to look it up.

1664.952 -> Go ahead and find a cell, or in this case,

1667.761 -> in your worksheet type the words YoY, or the letters.

1672.684 -> Once you've done that go ahead and give it a right-click

1675.54 -> and there's a new tool that's been introduced

1677.665 -> into the right-click sub menu.

1680.242 -> It's called Smart Lookup, so having highlighted that cell

1684.898 -> find the Smart Lookup tool and click on it.

1689.077 -> And we get a new pane called the Insights pane

1691.364 -> to pop up here.

1693.953 -> And just like that it's identified

1697.866 -> what this potentially could mean.

1700.072 -> In this case here, YoY may refer to the Yoy language

1703.891 -> however more than likely inside

1705.319 -> of a spreadsheet that's not the case.

1707.572 -> As we go a little bit further we see that

1709.487 -> it's potentially an abbreviation for year-over-year.

1713.458 -> Which is an easier way of identifying how we compare

1716.279 -> last year's performance to this year's performance.

1721.109 -> The Insights pane provides us with not only definitions

1725.207 -> but it'll do a web search to provide top links.

1729.793 -> Here we go here, Investopedia.

1732.046 -> So now we get a full break down of what exactly

1734.739 -> YoY is if this is exactly what we're looking for and it is.

1739.929 -> Let's try another tool,

1742.344 -> I'm gonna go ahead and right-click on FY here

1745.502 -> and I'm gonna try a Smart Lookup again.

1750.238 -> Now in this case here, my first two options,

1752.282 -> the FY postcode area and Fengyun aren't necessarily

1756.694 -> exactly what I'm looking for,

1757.959 -> so let's scroll down a little bit further

1759.271 -> and see if we can find some relevant context,

1762.708 -> and there it is right there.

1764.461 -> The very top web search a fiscal year,

1768.292 -> so now I can see that some of these are finance terms.

1772.472 -> Let's try one more, Satya Nadella,

1776.431 -> now a lot of people have heard this name

1777.615 -> but may not necessarily know where they've heard it from.

1781.144 -> Once again, this time actually instead of doing

1783.919 -> my right-click and Smart Lookup, I'm gonna go ahead

1786.705 -> and navigate up to the Tell Me bar.

1789.933 -> Now I know that the Tell Me bar can tell me where

1791.651 -> these tools are that are available to me,

1793.904 -> but what happens if I type something that isn't a tool?

1796.562 -> Maybe something that I'd like to search.

1800.603 -> Once it's identified that I'm typing something

1801.961 -> that isn't a tool it's either going to provide me

1804.445 -> help or offer to do a Smart Lookup on it.

1808.555 -> So I'm gonna do that.

1811.899 -> And there it is, Satya Nadella CEO of Microsoft.

1816.485 -> It's provided his Wikipedia, it's also provided

1820.038 -> a Bing image search, if there was any doubt

1823.184 -> about what the web engine backing all of this was

1827.259 -> search no further, of course it's provided by Bing.

1831.817 -> This is Microsoft finally stretching its muscles

1834.149 -> a little bit in terms of the experience and data

1836.727 -> that it's collected with its

1837.992 -> Bing search engine over the years.

1840.732 -> So there you have it, once again, to use the Smart Lookup

1844.331 -> feature simply find a cell, right-click on it,

1848.093 -> and select Smart Lookup, or from the Tell Me bar

1852.272 -> simply type what it is you're not sure of

1854.803 -> in our case, Satya Nadella, and select Smart Lookup

1859.155 -> from the drop down menu.

1862.038 -> Go ahead and pause the video to check that new feature out.

1866.403 -> There's a couple of other fundamentals within the Excel

1869.257 -> interface that we should talk about

1870.244 -> before we move any further.

1871.997 -> The first one is really important,

1874.086 -> in the top left-hand side you'll see that we have

1876.049 -> A1 listed in this little box here.

1879.787 -> Now this isn't a cell this is actually telling us

1882.597 -> what cell our current active selection is.

1886.434 -> You'll notice that I currently have A1 selected.

1890.044 -> This is the Name Box, the Name Box tells us

1892.459 -> exactly where our current active selection cell is.

1896.43 -> So let's say I click into cell C3

1899.367 -> you'll notice that now it's changed to C3.

1902.409 -> This is to allow us to know exactly

1904.092 -> what cell we have selected no matter

1905.927 -> where we are in the spreadsheet.

1907.692 -> While it's relatively easy when we're talking

1909.247 -> about A1 or C3 when we get a little bit farther out

1912.904 -> like for instance here in say P14 it's

1916.144 -> a little bit more tedious to have to go

1918.333 -> all the way to the left to see that we're in row 14

1921.162 -> and then all the way up to the top

1923.171 -> to see that we're in the column P.

1925.969 -> So using the Name Box makes a big difference.

1928.43 -> As we get a little bit further, you'll also see

1930.52 -> when we have certain fields selected

1932.401 -> if they're named ranges or tables the Name Box

1935.35 -> will tell us the names of those fields as well.

1938.264 -> To the right of that we have access to the Formula Bar.

1941.816 -> The Formula Bar not only tells us what formulas

1943.964 -> are happening behind the scenes within a cell,

1945.926 -> but it'll also just tell us what's inside the cell.

1949.084 -> So for example here if in cell A2 I just type

1952.776 -> the number two, the Formula Bar tells me that

1956.84 -> that's exactly what's going on inside the cell.

1959.673 -> However, if inside the cell I have one plus one

1964.061 -> you'll see that while the cell itself does say two,

1967.8 -> when I select it the Formula Bar actually tells me

1970.632 -> what's really happening behind the scenes.

1972.722 -> It's calculating one plus one, so the Formula Bar

1975.718 -> allows us to see behind the looking glass if you will,

1978.748 -> to allow us to understand better

1980.257 -> what's going on inside of our spreadsheets.

1982.974 -> This is also where we can make changes to the cell,

1985.342 -> so instead of double-clicking on the cell to make changes

1987.885 -> we can click inside the Formula Bar and let's say that

1991.867 -> I'd like this formula to be one plus two,

1995.849 -> once I hit Enter there, you'll see that

1998.763 -> the cell itself changes as well.

2000.772 -> The Formula Bar allows us to make widespread changes

2003.315 -> without having to bury ourselves

2005.706 -> within the actual cell spreadsheet.

2008.063 -> This kind of keeps us a little bit more focused on

2009.909 -> the formula and not the content happening around the cell.

2014.007 -> On the very bottom of our sheet we have our Status Bar

2016.747 -> our Status Bar of course has existed since 2007.

2019.754 -> This tells us whether or not we are in the middle

2022.552 -> of using a tool or whether or not there are

2025.745 -> instructions that we need to be made aware of.

2028.137 -> Currently Excel is ready to go.

2030.958 -> If you right-click on the Status Bar

2032.804 -> you can add additional features here.

2034.731 -> For example, maybe I'd like to see

2037.111 -> whether or not I can Flash Fill,

2039.689 -> or maybe I'd like to see whether or not

2041.453 -> signatures are available in a particular field.

2044.24 -> This allows me to make a number of different changes

2046.051 -> without having to think too hard about where to go.

2049.499 -> On the bottom right-hand side we have access to

2051.217 -> our View Fields so I can change between

2053.597 -> my standard view, I can change to my print preview view,

2058.148 -> I can change to a number of different setups within that.

2062.026 -> To the right of that I have my Zoom feature here,

2065.01 -> where I can use my minus or my plus sign,

2067.936 -> to increase or decrease my zoom level of the spreadsheet.

2071.86 -> Now here's a fun little trick within that,

2073.787 -> if you press and hold the Control key and use

2076.039 -> the scroll wheel you can zoom in and out

2079.197 -> using that feature set as well.

2081.287 -> In fact that's generally my preferred method

2083.412 -> of zooming in and out.

2085.095 -> And lastly, on the right-hand side we have our scroll bars

2089.704 -> now of course our scroll bars allow us to do exactly that

2092.003 -> scroll throughout the spreadsheet,

2093.756 -> either by clicking and holding the scroll bar

2096.392 -> and dragging up and down, however you'll notice

2098.04 -> that it only let's me go down to row 21,

2101.059 -> however if I use that downward arrow

2102.499 -> to keep clicking it will take me ever further.

2105.656 -> Additionally, the same methodology applies to my

2107.909 -> down scroll bar here allowing me to scroll

2110.544 -> from left to right, however once again,

2112.924 -> you'll see that it only lets me go as far as

2115.223 -> column R unless I use my arrows,

2117.963 -> in which case it allows me to go even further.

2121.005 -> So there you have it.

2122.108 -> Go ahead and take this opportunity

2123.42 -> to try that out and explore the interface

2125.219 -> a little bit more so before we carry on.

2129.863 -> There's a lot of different ways

2130.696 -> to navigate within Microsoft Excel.

2132.777 -> Of course we've always been able to point and click

2134.67 -> to get to the cell we'd like to go to,

2136.876 -> but it's not always the most precise methodology

2138.931 -> to do just that.

2141.148 -> Alternatively, we've always also been able to use

2143.203 -> the arrow keys, so let's say I'd like to go down a cell

2147.754 -> simply tapping on the down arrow key

2149.438 -> allows me to do just that.

2151.365 -> Tapping on the right arrow key allows me

2153.176 -> to go to one cell to the right,

2155.428 -> tapping on the up arrow key allows me to go one cell up,

2158.749 -> and the left arrow key allows me

2160.142 -> to jump to the cell to my left.

2163.428 -> Now one cell at a time may not

2164.821 -> necessarily be your cup of tea.

2167.015 -> For example, maybe you'd like something

2168.281 -> a bit more extreme that allows you to jump

2169.918 -> to the furthest reaches of the data set.

2173.273 -> Do you happen to know how many rows are

2174.956 -> inside of Microsoft Excel?

2177.383 -> Well you can find out, by pressing and holding

2179.473 -> the Control key and then tapping the down arrow,

2183.327 -> you'll see it flings us all the way

2185.87 -> to the furthest reaches of Microsoft Excel.

2188.61 -> In this case we see that the furthest row possible

2191.675 -> inside of this program is row 1,048,576.

2197.596 -> Tapping on the Home key or in this case

2200.138 -> Control + Home key, allows me to go back

2203.575 -> to the original cell that I was in.

2206.675 -> Were you curious to see how many columns

2208.486 -> are inside of Microsoft Excel?

2210.866 -> Well we saw that the right arrow key by itself

2213.618 -> only takes us one cell to one cell,

2217.019 -> but pressing and holding the modifier key

2219.156 -> the Control key and then tapping the right arrow key

2222.406 -> flings us all the way to column value XFD,

2227.306 -> that's pretty far out.

2229.384 -> Hopefully that's enough cells for you.

2231.799 -> Once again I'm gonna press and hold

2233.204 -> the Control key and tap the Home key,

2235.944 -> and this is going to bring me back to cell A1.

2239.148 -> So a couple of different ways to navigate

2240.46 -> within the spreadsheet, additionally we're able

2242.759 -> to select cells in a number of different ways

2245.394 -> either using the mouse by clicking and dragging,

2250.561 -> but again this might not necessarily

2251.547 -> be the most precise methodology.

2256.052 -> We saw that pressing and holding the Control key

2258.966 -> enhanced our arrow keys, what about the Shift key?

2263.9 -> Well if you press and hold the Shift key

2265.549 -> and use the arrow keys, you'll see that I'm able

2268.301 -> to select multiple fields of cells.

2271.842 -> Now I'm tapping the down arrow key in this instance,

2275.754 -> and once I've done that let's say

2277.113 -> all the way down to row 10,

2278.703 -> let's say I'd like to expand outward to column F.

2282.14 -> So I'll tap my right arrow key twice,

2285.054 -> and you'll see that it selects the values as necessary.

2288.456 -> All really quick and easy ways to navigate

2290.255 -> throughout the Microsoft Excel spreadsheet.

2293.239 -> Go ahead and pause the video and try that for yourself.

2296.072 -> We're starting to see that pressing and combining keys

2299.183 -> allows us to enhance the feature sets of our keyboard.

2302.062 -> These are called keyboard shortcuts,

2303.943 -> and they're really popular among power users

2305.836 -> and the average everyday users alike.

2308.796 -> Let's talk about a couple of other ones

2309.946 -> that we have available to us.

2311.838 -> For example, let's say I'd like a new workbook.

2315.785 -> Well I could go to the File tab and select New,

2319.303 -> and then pick the template from there,

2321.114 -> however that's a lot of steps,

2323.286 -> when all I really need is a blank workbook.

2326.664 -> By pressing and holding the Control key

2328.556 -> and tapping the N key, you'll see that it's jumped

2333.444 -> me into a brand new Excel workbook.

2336.486 -> I'm now in the generic workbook, book two.

2341.002 -> Let's say I'd like to open up a file

2342.349 -> that I already have saved somewhere.

2345.182 -> Well if Control + N for new creates a new workbook,

2349.396 -> any guesses what they keyboard shortcut for open would be?

2352.926 -> If you're saying to yourself Control + O

2355.213 -> you'd be absolutely right, once again by pressing

2357.593 -> the modifier key Control and tapping the O key

2361.703 -> you'll see it flings us into the Open dialogue.

2365.407 -> This is where I have access to any recent files

2367.461 -> I might have been working in, any online cloud drives

2370.527 -> I might have access to, or any files I have on my PC.

2377.191 -> So if Control + N for new creates a new workbook

2379.896 -> and Control + O opens up a new workbook,

2383.216 -> any guesses what save is?

2386.966 -> If you said Control + S you'd be absolutely right,

2389.497 -> once again good guesses.

2392.202 -> By pressing and holding the Control key

2393.479 -> and tapping the S key you'll see it flings me into,

2398.194 -> well this is interesting, the Save As dialogue box.

2402.107 -> In case you missed out on when I mentioned it earlier

2405.811 -> when you haven't saved a file before it forces you

2408.667 -> into the Save As dialogue box.

2411.697 -> So let's say that I'd like to Save As now,

2414.564 -> I'm gonna go ahead and create a location

2416.294 -> because of course Save can't create a file only Save As can.

2421.925 -> So I'm gonna go ahead and save mine to the desktop

2423.527 -> and call this Practice Save.

2429.843 -> So now that I've saved my file I'll try my keyboard shortcut

2432.455 -> Control + S again and nothing happens,

2437.483 -> at least not as far as I can tell.

2439.282 -> But it is actually saving my file,

2441.79 -> we just can't see it now because save is doing its job.

2444.53 -> It's just overwriting the existing file.

2448.257 -> Now with that being said,

2449.532 -> what's the keyboard shortcut for Save As?

2451.914 -> What if I don't want to overwrite my existing file

2454.336 -> I'd like to save it as a new copy.

2457.575 -> Well in this case here, up until now we've been

2460.001 -> served some softballs with these keyboard shortcuts,

2462.823 -> Control + N is New, Control + O is Open,

2466.271 -> Control + S is Save, so what's Save As?

2470.532 -> It's not Control + SA, it's actually just the F12 key.

2475.988 -> So if you tap the F12 key, it'll jump

2479.065 -> you straight into the Save As dialogue box.

2482.223 -> This is also where we can save this document

2485.009 -> as any type of file that Excel is capable of exporting into.

2491.371 -> Here in the Save as Type you'll see that it's

2493.093 -> currently saving it as an Excel workbook.

2496.75 -> If I click on that you can see

2498.027 -> all the different types of documents

2499.908 -> that I'm able to save this Excel spreadsheet as.

2502.938 -> I can save it as a workbook.

2505.04 -> For my power users I can save it as

2506.561 -> a Macro Enabled Workbook.

2509.753 -> For those of you who work with organizations

2511.483 -> or divisions that might have older versions

2513.527 -> of Microsoft Excel they can't read the newer versions

2516.812 -> of Microsoft Excel workbooks, anything from 2007 on

2520.876 -> can't be read by a Excel 2003 or earlier version of Excel.

2526.785 -> So in those instances we might actually need

2528.55 -> to save it as Excel '97 to 2003

2531.348 -> or the dot xls document workbook.

2535.109 -> We can save it as a web page,

2538.14 -> when we get a little bit farther into these lessons

2539.742 -> you'll see that we have the ability

2540.973 -> to save these documents as templates.

2544.177 -> Now there's a lot of other options

2545.245 -> within that here and of course

2546.685 -> I encourage you to explore the ones

2547.718 -> that make the most sense to you.

2549.659 -> One that of course I'd be remiss to not point out

2552.167 -> would be the PDF and XPS file formats

2556.393 -> allowing us to save these documents in the PDF file styling.

2562.256 -> So we see that we have a lot of different ways

2563.614 -> that we can export this workbook.

2565.867 -> In this case here I'm gonna go ahead and leave it alone.

2570.998 -> Go ahead and take this opportunity

2572.296 -> to practice a couple of those keyboard shortcuts.

2575.013 -> We'll be introducing them as we go along

2576.893 -> throughout this lesson, but it's important to note

2578.832 -> that quite a few of these file tools

2580.887 -> do have keyboard shortcuts.

2583.964 -> In case you're ever not sure if a tool

2585.392 -> does or does not have a keyboard shortcut

2587.853 -> if it's inside the Ribbon here for instance Bold,

2591.545 -> just go ahead and hover over it and you'll see that

2594.006 -> Bold immediately to the right of that

2596.166 -> has Control + B in parentheses.

2599.661 -> This allows us to see that it has a keyboard shortcut.

2603.236 -> So if I try the Control + B keyboard shortcut

2605.942 -> it would bold the text that I have selected.

2609.262 -> Many tools have keyboard shortcuts,

2610.899 -> so I do encourage you to just highlight

2612.466 -> some of these for instance Fill Color does not,

2615.996 -> however making text bigger, in this case does not as well.

2622.103 -> There's one Control + U for Underline.

2625.678 -> Go ahead and pause the video for just a moment

2627.527 -> and try some of these keyboard shortcuts out

2630.348 -> and maybe try to see if your favorite tool

2631.66 -> has a keyboard shortcut all on its own.

2635.12 -> All right, welcome back, so we've spent a lot of time

2638.405 -> talking about the basic user interface of Microsoft Excel.

2641.958 -> And you might have seen that

2643.316 -> up until this point we haven't done a lot

2644.535 -> of typing inside of the actual spreadsheet.

2646.927 -> Well that stops now, what I'd like to do

2649.342 -> is focus a little bit more so on actually

2651.153 -> putting this content inside the spreadsheet.

2653.742 -> Let's say that we've decided to strike it out on our own,

2656.285 -> we're gonna be opening up an artisanal coffee shop.

2659.129 -> I live here in San Francisco and if there's one thing

2660.685 -> this city totally needs another one of

2662.821 -> it's an artisanal coffee shop.

2664.996 -> We'll go ahead and start with

2665.972 -> the actual name of our business.

2667.62 -> I'm here in cell A1 and I'd like to just

2669.445 -> put the name of the business in here.

2671.779 -> Now I can't think of a better

2673.233 -> artisanal coffee shop name than the one

2675.41 -> on the tip of my tongue here, Birch & Sons,

2678.997 -> actually it's 2015 Birch & Daughters, there we go.

2684.092 -> So I've got my Birch & Daughters coffee shop.

2688.027 -> Now when I finished typing I tapped the Enter key

2690.814 -> and you might have seen that my cell jumped down to A2.

2694.68 -> The reason being is that the Enter key is actually

2697.35 -> the equivalent of a down arrow key,

2699.637 -> especially when you're inside of a cell.

2702.54 -> Now with that being said, you can also go

2704.339 -> to the right instead by tapping the Tab key.

2708.763 -> So by tapping the Tab key you can finish

2710.609 -> typing content inside of a cell

2712.826 -> and go one cell to the right,

2714.846 -> or again the Enter key to go down.

2717.969 -> Now within that you can also go

2720.628 -> one cell up by pressing and holding the Shift

2724.065 -> and then tapping the Enter key.

2726.236 -> So if the Tab key pushes us off to the right

2729.73 -> and the Enter key pushes us down,

2732.11 -> but the Shift + Enter key brings us back up

2735.802 -> what do you think Shift + Tab will do?

2738.426 -> Well if you said it's going to bring us to the left,

2740.806 -> you'd be absolutely right.

2743.465 -> So once again, the Enter key brings us down a cell

2747.575 -> the Tab key brings us to the right,

2750.361 -> the Shift + Tab key brings us back to the left,

2753.426 -> and the Shift + Enter key brings us one cell up.

2758.86 -> Now let's say that I'm not done with this name here,

2761.518 -> let's say that Birch & Daughters isn't the full name

2763.98 -> maybe I'd like to add the word coffee shop to that.

2766.36 -> If you say the name Birch & Daughters you might be curious

2768.415 -> as to what kind of business that would be too.

2771.201 -> So if I'd like to add new content into the cell,

2774.603 -> without double-clicking on it

2775.996 -> or going up to the Formula Bar,

2777.9 -> both of which are things I could do,

2780.007 -> I'm gonna simply tap the F2 key,

2782.759 -> and the F2 key is going to bring me back

2785.266 -> into that cell at the very end.

2788.424 -> You'll see that my cursor is now active

2790.56 -> and I can go ahead and add the rest of my content.

2792.906 -> And I'll go ahead and type space, Coffee Shop.

2797.492 -> Now if I'm done typing inside of a cell,

2799.547 -> but I don't want it to drop down or go to the right

2802.136 -> or go up or to the left, I can press and hold

2804.957 -> the Control key and tap the Enter key

2808.451 -> and it will keep my active cell inside the content

2811.772 -> without having to jump down or go to the right.

2815.092 -> So again if you don't want your cursor to move,

2816.776 -> but you're done editing the cell press and hold

2818.947 -> the Control key and tap the Enter key.

2821.989 -> Go ahead and pause the video and get to this point with me.

2825.147 -> All right, so let's go ahead and expand

2826.586 -> on this menu a little bit more so.

2829.175 -> We need to know what kind of items

2830.278 -> we're going to be carrying here.

2832.205 -> So I'm gonna go ahead and type Items in cell A3,

2834.295 -> I'd like to drop down so I'm gonna tap my Enter key.

2837.836 -> And I definitely know that a coffee shop probably

2839.81 -> is going to carry coffee, probably going to carry tea,

2844.477 -> and then maybe a couple of food items.

2845.917 -> We'll say bagels, crumpets,

2850.421 -> for all of my tea drinkers out there

2851.85 -> I know you're about your crumpets.

2854.891 -> And additionally I'd like to go ahead

2856.285 -> and enumerate the price here.

2860.058 -> So how much is our coffee going to cost?

2861.451 -> Well this is an artisanal coffee shop,

2863.297 -> so we're probably talking let's say $4.00.

2867.953 -> So I'm gonna go ahead and type 4.00

2870.855 -> and I'm gonna hit Enter.

2872.782 -> Huh, where did my .00 go?

2875.72 -> Well we'll talk about that in just a little bit

2877.972 -> but I do want to point out that despite the fact

2879.528 -> that I did type 4.00 that went away.

2884.16 -> Let's talk about tea, how much is our tea gonna cost?

2887.608 -> Let's go ahead and say $3.50.

2890.778 -> Once again I'm gonna go ahead and type 3.50

2893.82 -> and I'm gonna hit Enter and huh, now wait a minute,

2897.918 -> I definitely typed a zero right after that five,

2900.495 -> and I know that I typed two zeros after that four.

2904.64 -> So what's going on here?

2906.858 -> Well the problem is Excel is trying to keep

2909.865 -> these cells as simple as possible.

2912.129 -> And one of the ways it does that is by removing

2914.218 -> zeros that might go on forever.

2917.376 -> For those of us that remember algebra,

2920.534 -> you might have been taught that every number

2922.937 -> has zeros after it, 4.0000000

2927.256 -> is still just the number four.

2929.218 -> And in fact all numbers have that

2931.157 -> decimal based valuing after it.

2933.786 -> In order to make sure that this is the simplest

2935.632 -> presentation of these numbers Excel

2937.826 -> actually removes those zeros.

2941.019 -> To fix that we have to change the number formatting

2943.678 -> we actually have to tell Excel what kind of number this is.

2946.952 -> So there's a couple of different ways we can do that here.

2950.028 -> Now, I'm gonna go ahead and select that four

2954.243 -> and I'm gonna right-click on it.

2956.867 -> Now where can I format the settings for this?

2961.034 -> There, Format Cells, I'm gonna go ahead

2964.287 -> and click on Format Cells and it jumps me into

2966.725 -> the number formatting settings.

2969.976 -> So what kind of number is this going to be?

2972.274 -> Is this going to be a regular number?

2974.48 -> Maybe, or is it going to be currency?

2978.01 -> Notice that it actually affix the dollar signs to it.

2981.702 -> This is exactly what I want so I'm gonna go ahead

2983.548 -> and apply my currency and click OK.

2987.344 -> Much better, now I've got my .00 afterwards.

2991.489 -> But, I still don't have it here, the reason being is

2994.925 -> that I only made that change to that one cell.

2997.932 -> We have a saying at Learn iT and it's that

2999.151 -> you've got to select to effect.

3001.334 -> If I want to make changes to a certain call

3003.958 -> I need to select it first.

3007.111 -> So I'm gonna go ahead and select the three additional

3008.911 -> cells that I know I'm going to be typing dollar values into.

3012.464 -> And this time I'm gonna show you even faster way.

3015.494 -> I'm gonna select those three cells and inside the

3017.375 -> Home tab I'd like to draw your attention to

3019.906 -> the Number Formatting command group here.

3023.621 -> Currently the formatting is set to General,

3027.835 -> so I'm gonna go ahead and change that

3028.963 -> by clicking on the drop down arrow and selecting

3031.376 -> the Currency number formatting and there we go.

3037.414 -> So I've now changed the way Excel presents this information,

3041.059 -> so I'll go ahead and fill out the rest of this here.

3043.263 -> We'll say the bagels are gonna be $2.50

3046.491 -> and the crumpets will be $2.75.

3050.345 -> Go ahead and take this opportunity to pause the video

3052.272 -> and catch up with me here.

3053.944 -> Once again to change the number formatting

3056.44 -> simply select a cell and right-click on it,

3059.25 -> and go to Format Cells and choose

3062.315 -> the formatting category from there,

3064.277 -> or alternatively select the cell and then using

3067.528 -> the Number Formatting command group click

3070.396 -> from the drop down and select from there as well.

3073.948 -> Go ahead and try that for yourselves.

3075.841 -> All right, welcome back, so hopefully now

3077.675 -> you're all caught up with me here.

3078.987 -> We have our Birch & Daughters Coffee Shop

3080.786 -> and we're building a menu.

3082.749 -> Now over the break I ran this menu by a focus group

3087.16 -> it was expensive but it was totally worth it

3088.786 -> because we've seen that the focus group told us

3091.328 -> crumpets are definitely out.

3093.465 -> The average person doesn't go to a coffee shop

3095.09 -> to order crumpets, so I'd like to remove that item

3097.958 -> from the menu and replace it with something

3099.629 -> a little bit more modern.

3102.01 -> So in this instance here, I'm gonna go ahead

3103.681 -> and remove the word crumpets from cell A7.

3107.408 -> There's a couple of different ways I can do that for you,

3109.208 -> and I'm gonna run that back.

3110.81 -> The first way I can do it is simply by pressing

3112.691 -> the Delete key, so I'm gonna highlight A7

3115.605 -> and press the Delete key, and you'll see that

3117.938 -> it's deleted the entire value instantaneously.

3121.688 -> So that's one way to do it.

3122.803 -> I'm gonna go ahead and undo that

3124.359 -> so I can show you another way.

3126.042 -> To undo you can either use the Undo button

3128.945 -> on the top left corner, or alternatively you can

3131.685 -> press and hold the Control key and tap the letter Z,

3135.853 -> either way will work.

3137.768 -> So once again you can either press the Backspace key

3140.52 -> or the Delete key to delete a value,

3142.319 -> or let's say that I'd just like to replace the value.

3146 -> Maybe, crumpets aren't going to do so well,

3148.548 -> but biscuits a more common iteration of crumpets

3154.273 -> might be, or English muffins, let's do English muffins.

3158.313 -> So to replace crumpets I'm simply going

3159.823 -> to start typing, I've got A7 selected

3163.387 -> and I'm just gonna start typing there, English muffins.

3168.577 -> And once I'm done I'd like to get out

3170.701 -> of the editing field here but I don't wanna leave the cell.

3174.289 -> To do that I'm gonna press and hold the Control key

3176.541 -> and tap the Enter key once again,

3179.107 -> and there you go English muffins is now in the cell.

3183.461 -> Now, wait a minute here, where's the word muffin?

3187.989 -> What's an English M?

3189.869 -> I don't know, what's happened here?

3192.238 -> Did I just lose all of my content?

3195.64 -> Well the answer is complicated but we'll break it down here.

3199.482 -> When we start to type anything inside of a cell

3201.119 -> we're allotted a certain amount of space here.

3203.569 -> In this case here you can see that the space

3205.09 -> between A and B is not that large.

3208.817 -> So you can see that English muffin

3209.839 -> didn't fit in all the way.

3212.161 -> But you might be saying to yourself,

3213.194 -> Sean you typed Birch & Daughters in here

3215.387 -> and it seems to fit fine.

3217.733 -> Well it kind of does, the reason being is that

3220.6 -> English M, or in this case English muffins,

3223.05 -> is being cut-off because cell B7 does have a value in it.

3227.88 -> Meanwhile, A1 is allowed to run on, and on, and on

3230.98 -> because there's no values in B1 or C1.

3234.613 -> If I typed anything inside of these cells right here

3236.912 -> like the word hello, you'll see that

3239.501 -> Birch & Daughters Coffee Shop immediately got hidden

3242.902 -> because B1 takes precedence.

3245.84 -> However, if I delete the value A1 is allowed

3249.392 -> to run itself over simply because there is no content

3252.55 -> it could possibly be impeding on.

3254.954 -> So how do we fix this?

3256.788 -> Well we need to re-size the column.

3258.913 -> The easiest way to do that is to find the border

3261.153 -> between A and B, or in-between any column that has

3264.868 -> a value you'd like to adjust.

3267.237 -> And clicking and holing I'm going to expand the value

3270.801 -> until muffins fully fits in.

3273.669 -> Additionally, what I could have also done

3276.049 -> is find the border and double-click,

3279.033 -> now you'll notice it went a lot farther, and this is

3281.076 -> because of Birch & Daughters Coffee Shop field here.

3284.803 -> It auto expanded to the widest value inside of that column.

3288.692 -> So depending on the way that you're trying to expand it

3291.386 -> this might not necessarily be the way that you want.

3293.313 -> I think this is a little far personally.

3297.307 -> So I'm going to readjust, so that it allows

3299.315 -> English muffins to show without being too big.

3304.168 -> Of course we can also do the same thing

3306.467 -> with row based values.

3308.232 -> We can make row based values wider as well,

3312.202 -> or more narrow, once again by finding the border

3314.884 -> between any two rows you can double-click

3317.162 -> and it will AutoFit based on the content

3319.749 -> of the cells in the rows.

3322.185 -> Go ahead and pause the video and try that for yourself.

3324.681 -> All right, so we've had some opportunities to enter

3327.699 -> content and edit content if we need to.

3331.74 -> Now, let's go ahead and talk a little bit more

3333.829 -> about what we can do with this data once it's input.

3336.964 -> Now, I've gone ahead and I've added data

3339.251 -> for weeks one through four, in terms of total unit sales

3342.154 -> for coffee, tea, bagels and English muffins.

3345.753 -> So this is great, our business is successful,

3348.237 -> we're making it but we don't know exactly

3350.443 -> what's going on with all of this data.

3353.021 -> At this stage, all we know is how much we've sold

3354.994 -> in any one week for any one of these particular items.

3358.791 -> There's not a lot of questions we can answer

3360.103 -> with this data just yet.

3361.821 -> For example, maybe I'd like to know the total amount

3363.981 -> of units I sold in week one completely.

3368.439 -> This might give me a better idea of

3369.542 -> how many customers I might have had.

3371.91 -> Or how many individual transactions I might have had.

3375.347 -> So let's say I would like to calculate

3377.402 -> the total unit sales for week one.

3380.339 -> Placing my cursor in cell C8 here,

3383.45 -> what I'd like to do is calculate

3384.635 -> all of the values directly above.

3387.05 -> Now for those of you who have used

3388.28 -> Excel in the past I'm sure you know the ability

3390.405 -> to type equals and add up all of these different values.

3393.923 -> But before we get to all of the hand typing,

3396.326 -> I'd like to show you some automated options

3398.044 -> that we have available to us.

3399.762 -> In particular, I'd like to introduce

3401.191 -> to you the AutoSum tool.

3403.884 -> So go ahead and let's place our cursor in cell C8

3406.67 -> and I'm gonna go ahead and just click the AutoSum button.

3411.767 -> When I do it actually does exactly that,

3414.832 -> it auto sums up the values directly above it.

3418.013 -> It's made a guess as far as what the values

3420.103 -> I'd like to add together are,

3421.984 -> in this case it's identified cell C4, five, six, and seven

3426.024 -> as those values, and it's saying,

3427.87 -> "Hey do you want to sum those up?"

3430.726 -> It's guessed right so I'm just gonna go ahead

3432.607 -> and tap the Enter key.

3435.997 -> And you can see that I sold in week one 1,625 units.

3442.301 -> Let's go ahead and do the same thing

3443.393 -> for weeks two through four.

3446.307 -> Once again, I'm gonna place my cursor in cell D8

3449.175 -> and click the AutoSum tool.

3451.88 -> It's gonna select all the values that I think

3454.654 -> it's going to be adding together,

3456.257 -> but if I'd like to edit it I can always

3457.603 -> just highlight the content myself.

3460.181 -> Now in this case it's right, but let's say it's wrong.

3461.969 -> Let's say it only guessed that one cell,

3464.221 -> I can click and drag and fix it.

3467.948 -> Now once I'm done here, I could tap the Enter key again

3470.688 -> but I'd like to get just to the next cell to my right.

3473.393 -> Who remembers the key that I used

3474.612 -> to go one cell to the right?

3477.352 -> If you guessed the Tab key you'd be absolutely right.

3479.929 -> I'm gonna go ahead and tap the Tab key

3482.472 -> and it's gonna take me right over.

3483.691 -> You can see week two was definitely better than week one.

3487.987 -> Go ahead and take this opportunity to apply AutoSum

3491.342 -> to weeks three and four.

3494.813 -> All right, so far we've calculated the total unit sales

3498.412 -> for weeks one, two, three and four

3500.502 -> using the AutoSum feature.

3502.789 -> What I'd like to do now is find out the total amount

3504.995 -> of coffee that I sold in this particular month.

3508.92 -> To do that I'm gonna place my cursor in cell G4

3512.194 -> and in the same process here what I could do

3514.933 -> is use the AutoSum feature and the AutoSum feature

3517.383 -> prevents me from having to go through the tedious process

3519.995 -> of typing an equal sign, typing the word SUM

3523.838 -> with an open parenthesis and then saying,

3527.147 -> C4 plus,

3529.645 -> D4 plus,

3532.095 -> E4 plus.

3534.266 -> You can see how this is really tedious

3535.632 -> even just to watch me do that.

3537.629 -> So again I'm gonna go ahead and use the AutoSum tool

3541.472 -> but this time before I click Enter,

3543.318 -> I'd like to point out that it's not perfect.

3547.474 -> Notice that it actually includes the pricing value

3550.005 -> in column B, which is not exactly what I wanted.

3553.198 -> In this case, I only want weeks one through four.

3556.344 -> Or in this case C through F, so I'm gonna re-select

3561.569 -> from C4 to F4 and I'm gonna be dropping down

3565.702 -> to the cell below so I'm gonna

3566.896 -> tap the Enter key and there you go.

3570.855 -> So go ahead and do that.

3574.663 -> AutoSum for all of these individual values here,

3578.633 -> once again simply click the AutoSum tool,

3582.801 -> and highlight the values that you

3584.278 -> would like summed together.

3589.224 -> Now lastly, what I'd like to do is get

3591.233 -> a total units sold overall.

3595.818 -> Now which one do I calculate?

3596.956 -> Do I calculate the values above,

3598.756 -> or the values to my left?

3600.846 -> Well the answer is the same, no matter which one

3603.377 -> you use because this one is simply calculating

3605.71 -> individual weeks whereas these values are calculating

3609.588 -> individual items however the total units sold

3612.328 -> will be the same regardless.

3615.544 -> So overall we sold 6,879 units in a single month,

3620.327 -> that's not bad, go ahead and pause the video

3622.587 -> and take this opportunity to catch up with me.

3625.951 -> All right, welcome back, so we've calculated

3628.138 -> the total units sold per week and per item,

3631.734 -> and we were able to do that using

3633.58 -> automated features like the AutoSum tool.

3637.2 -> What we'd like to do now is focus a little bit more

3639.208 -> on the real world matters, we'd like to figure out

3641.461 -> now that we've got our total units sold,

3643.307 -> how much did we make?

3645.199 -> Let's talk dollars for a moment here ladies and gentlemen.

3648.218 -> Here in cell H4 I've got a column ready

3651.097 -> to calculate my subtotals.

3654.417 -> Now, how do I calculate how much money I actually made?

3657.865 -> Well with this data here I can simply take

3659.723 -> the total units sold and multiply it

3664.599 -> by how much each individual item costs.

3667.966 -> Now of course this is glazing over business costs

3670.753 -> like how much it costs to get the raw materials,

3672.645 -> and blah, blah, blah, but let's keep it simple.

3675.211 -> I'd like to just calculate how much income

3676.767 -> I made selling these items.

3679.518 -> Placing my cell in H4 what I'd like to do

3682.931 -> is calculate those values; units sold times price.

3687.808 -> So I'm gonna actually need to write a formula this time.

3689.979 -> There's no automated tool for this.

3692.812 -> Now, in Excel we need to wake it up.

3695.273 -> We need to tell it that it's gonna be doing some math.

3698.976 -> The way we do that is with the universal sign equals.

3704.108 -> By typing the equal sign we've indicated to Excel

3706.291 -> that it's about to do some math.

3709.251 -> And that it needs to wake up and start paying attention

3711.213 -> to the content coming afterwards.

3714.371 -> So now that I've typed H4 I'd like to go ahead

3717.738 -> and calculate total units sold times price.

3720.722 -> Now I could type

3723.405 -> three two six three and then the multiplication sign

3728.846 -> using my Shift + 8 key, and then typing four

3735.629 -> and it calculates it, in this case here, I made $13,052.

3741.508 -> But here's the problem, let's say I raise the price,

3743.977 -> let's say that this was incorrectly added

3747.127 -> and the actual price of a cup of coffee is $3.95.

3753.59 -> Did my subtotal change?

3756.377 -> No, it's still 13,052.

3759.5 -> The reason being that we told Excel

3761.706 -> what to do not where to look.

3765.235 -> Remember when I showed you the Formula Bar,

3767.441 -> if I click inside this cell right here,

3768.881 -> I can see that the formula isn't looking at

3771.922 -> this cell right here to see if it's changed

3774.059 -> it still says four.

3777.658 -> So how do we fix that?

3778.807 -> How do we future proof a formula

3780.769 -> to account for data changing?

3783.927 -> Well first things first, let's go ahead

3785.042 -> and delete that value in cell H4.

3788.873 -> And I'm gonna start over with my equals sign

3791.497 -> and this time instead of telling Excel

3793.494 -> that it's gonna be multiplying 3,263 by $3.95,

3799.206 -> I'm simply going to tell Excel

3800.564 -> where to look to find those numbers.

3803.687 -> So in this case here it's going

3804.795 -> to be G4 selected by clicking on it.

3808.32 -> You'll notice that it's got the dancing ants all around it,

3812.169 -> multiplied by B4.

3817.509 -> Once again notice that it's got

3818.94 -> the little dancing ants around it

3820.821 -> G4 times B4 inside of the cell where

3823.282 -> I'd like this answer to come out.

3825.43 -> And I'm gonna lock it in by pressing and holding

3828.042 -> the Control key and tapping Enter.

3830.016 -> And there it is, so I made $12,888.85.

3836.262 -> And now if I change my coffee price back,

3839.467 -> to four dollars, voila,

3843.381 -> my sub total also changes.

3846.295 -> So now I can update this data with the latest information

3849.21 -> and I'm able to see those numbers reflected

3852.17 -> in any formulas that are referencing those cells.

3856.199 -> Go ahead and pause the video and try this out for yourself.

3858.614 -> Once again, in cell H4 what we simply did

3861.888 -> was multiply the total units sold by clicking on that cell

3866.509 -> multiplying it by the price, once again,

3869.434 -> by clicking on that cell and pressing Enter to lock it in.

3873.95 -> Go ahead and try that.

3875.633 -> All right, so now we've got the subtotals

3878.106 -> for all four items, and if I'd like to

3881.23 -> I can actually even calculate the total dollar value

3884.724 -> of all the items together.

3886.895 -> Now we saw that we were able to do it

3888.381 -> with the AutoSum feature, let's talk a little bit about

3891.075 -> how they got that value and how we

3893.002 -> can build that function ourselves.

3896.334 -> To do that what I'd like to do is build a SUM function.

3900.525 -> Now just like with a formula I'm gonna start

3901.977 -> with my equals sign but this time instead of just

3904.844 -> jumping right into the math,

3906.69 -> I'm gonna tell Excel exactly what I'd like it to do.

3909.976 -> And in this case I'd like it to sum up the values.

3913.505 -> So I'm gonna type the word SUM and a couple of things

3916.705 -> pop up here, we see a list of functions,

3920.422 -> like SUM, SUMIF, and for every cell that's selected here

3924.764 -> or every function rather, you'll see off to the right

3926.854 -> a description of what this function actually does.

3929.919 -> And in this case the SUM function adds

3931.892 -> all the numbers in a range of cells.

3935.259 -> If that's the function you'd like to use,

3937.024 -> and it is, we'd like to tell Excel this is the one.

3941.703 -> We do that by inserting an open parenthesis

3945.511 -> after the function's name.

3947.438 -> So I've got equals SUM open parenthesis,

3952.581 -> I can now select all of the numbers I'd like

3954.543 -> added into my SUM function.

3957.202 -> In this case here, I've got 13,000

3963.042 -> through 1,927.

3965.259 -> I selected all four values by clicking and dragging.

3968.255 -> However, you can also click and hold the Shift key and

3971.784 -> tap the down arrow key until all the values are selected.

3976.579 -> Once that's in place we'd like to tell Excel

3978.541 -> that this function is now complete,

3980.642 -> and the way we do that is by pressing and holding

3982.825 -> the Shift key and closing off these parentheses.

3986.68 -> Remember every open parenthesis requires

3988.978 -> a closed parenthesis as well.

3992.612 -> Once this function is done I'm going to tap

3994.586 -> the Enter key and there you have it.

3997.906 -> Total, we made $24,038 in this

4001.854 -> particular month's worth of sales, not bad.

4006.254 -> Maybe we'd also like to calculate the average

4008.599 -> number of units sold, so in this particular instance

4012.047 -> maybe I'd like to find out the

4014.056 -> average number of coffee items sold.

4017.945 -> So let's go ahead and in cell C10 here,

4022.206 -> average units, I'd like to find out how much I sold

4025.979 -> in coffee,

4027.82 -> tea,

4030.037 -> bagels,

4032.289 -> and English muffins.

4036.434 -> Go ahead and re-size here a little bit.

4039.639 -> So how do you calculate the average units sold for coffee?

4044.817 -> Well just like we did with the SUM function,

4046.593 -> we'd like to tell Excel to do a certain set

4048.59 -> of math and the way we're going to do that

4051.91 -> is starting with the equals sign.

4056.264 -> So I've typed my equals sign and this time

4058.807 -> instead of typing SUM, I'm gonna go ahead and type AVERAGE.

4065.297 -> Once I've done that I'd like to tell Excel

4066.655 -> yeah this is the function I want to use,

4069.163 -> and I'm going to open up these parentheses.

4072.669 -> Now all you need to do is select the cells

4074.225 -> that I would like averaged together.

4076.767 -> In this case I'm averaging coffee,

4077.998 -> so I'm gonna select weeks one through four of coffee

4082.335 -> and close the parentheses, tap Enter to lock it in.

4086.429 -> And I can see that the average number of units

4088.31 -> of coffee sold in any one particular week is 815 units.

4093.813 -> And if I highlight this cell I can see in the Formula Bar

4096.634 -> there's that function that I built.

4099.421 -> Go ahead and pause the video and catch up to me here.

4102.428 -> Let's calculate the average units for all four items.

4107.478 -> All right, welcome back, so at this stage

4109.73 -> we've calculated the average unit sales

4112.598 -> of each individual item.

4114.281 -> We've calculated the total units sold

4116.836 -> of each individual item, and using that information

4119.343 -> we were able to calculate the subtotal dollar value.

4123.233 -> The actual amount of money we earned selling these items.

4127.482 -> So let's keep on keeping on, with some of this math.

4130.559 -> What we'd like to do now is maybe calculate

4131.963 -> the total amount of taxes that we're gonna need

4134.436 -> to set aside for federal and state and local taxes.

4139.405 -> To do that we're gonna need the help

4140.636 -> of our tax rate field up here at the top.

4143.585 -> Now here in San Francisco, our tax rate is about 8.75%

4147.927 -> so we'll bus using that value.

4149.46 -> If you'd like to substitute it for your local

4150.969 -> sales tax rate you're more than welcome to.

4156.681 -> Here in column I I've got a taxes field.

4159.386 -> Now to calculate taxes I'm simply going to

4161.383 -> multiply the total items, total dollar value made,

4166.747 -> by the tax rate, so in this case of coffee

4170.276 -> $13,052 multiplied by 8.75%

4175.465 -> to get the total amount of taxes I need to pull out.

4178.46 -> So let's go ahead and do that,

4180.19 -> once again to start any function or formula

4182.327 -> I'm gonna need to kick it off with an equals sign.

4185.275 -> And I'm gonna go ahead and select cell H4

4188.433 -> and multiply it by 8.75%

4192.882 -> enter to lock it in and I can see that the total amount

4195.506 -> of taxes I need to set aside is $1,142.

4201.241 -> Now once I've built a formula I really don't necessarily

4203.726 -> want to have to build it all over again,

4205.688 -> that seems tedious and it feels like a lot of work

4208.103 -> for a software program that's touted as being

4209.937 -> the end all be all of doing math for me.

4214.419 -> So we don't actually have to retype this formula

4216.23 -> all over again, we do have access to something

4218.436 -> called the AutoFill handle.

4221.849 -> The AutoFill handle allows us to copy

4224.02 -> the same formulas over and over and over again,

4227.178 -> adjusting for relative cell references.

4230.752 -> So for example here, let's say that I'd like to

4234.073 -> replicate this formula of G4 times B4 here in cell H4.

4240.969 -> I'm gonna go ahead and clear these values out right here,

4244.185 -> and while in this cell right here it does say G4 times B4

4248.248 -> what I need in each individual cell is

4250.756 -> the respective row value.

4253.926 -> So instead of G4 times B4 here, in the cell below,

4257.246 -> I would need G5 times B5, and G6 times B6, and so on.

4263.481 -> So let's see what happens.

4265.118 -> To engage the AutoFill handle I'm gonna need to

4267.498 -> find the little green box in the

4269.309 -> bottom right corner of my active cell.

4273.744 -> Hovering over it you'll see that my fat white cross

4277.192 -> turns into a thin black cross.

4280.803 -> Hovering over it you'll see that once I've got

4282.893 -> that thin black cross I'm going to click and hold

4286.271 -> and drag down to fill the respective cells

4289.742 -> that I'd like to copy, notice that the green box

4292.285 -> is expanding although no values have appeared yet.

4295.896 -> Once I release, notice all of the cells values

4300.11 -> auto populate, so it surely did,

4303.518 -> G5 times B5,

4305.532 -> G6 times B6,

4307.705 -> G7 times B7,

4310.863 -> great.

4312.024 -> So let's go ahead and do the same thing with taxes.

4315.38 -> Once again I'm going to hover over that little green box

4317.841 -> in the bottom right corner,

4319.95 -> and click and drag, and release,

4324.617 -> whoa that is not what you expected is it?

4329.795 -> What went wrong?

4331.804 -> (laughs)

4332.907 -> Well a lot went wrong here.

4335.31 -> Let's go ahead and talk about it here.

4337.33 -> Now, here in cell I4 I see the formula is exactly

4342.044 -> what I built it out to be, H4 times H1,

4345.121 -> or the subtotal times the tax rate.

4349.01 -> Let's see what the cell below it says,

4352.168 -> H5, or the subtotal, times H2,

4357.776 -> H2 has nothing in it.

4360.19 -> Oh I see,

4363.854 -> so just like in the formula before

4365.862 -> when I auto filled down, G4 times B4,

4368.521 -> became G5 times B5, this formula's value also increased.

4375.905 -> When you AutoFill down row-by-row the row value

4379.713 -> in this case right here four and one, both increase by one.

4387.41 -> This is called relative cell referencing

4389.372 -> when you AutoFill down it continues to reference

4391.915 -> those cells relatively based on

4394.632 -> the positioning of the cell formula.

4398.498 -> We do however have the ability to control

4400.506 -> that a little bit more so.

4402.77 -> By selecting the cell that we'd like to lock in,

4406.079 -> in this case here, what cell do we

4407.356 -> not want to change ever?

4410.351 -> H1, we don't want the tax rate to ever stop being H1.

4414.972 -> In fact we want every single formula to reference

4418.316 -> their subtotal and then multiply it by H1.

4425.015 -> So to lock that in we need to absolute reference this cell,

4428.823 -> meaning that we will always absolutely reference

4431.981 -> that one point in the spreadsheet.

4435.185 -> To do that we're going to engage the F4 key,

4439.202 -> so by selecting H1, the cell I don't want to change,

4442 -> I'm gonna tap my F4 key and you'll see that now

4445.704 -> H1 is surrounded by dollar signs.

4449.268 -> Once you've done that go ahead and lock it in

4450.754 -> by tapping the Enter key, and you'll see that

4455.317 -> the formula itself didn't change the result.

4458.475 -> It's still $1,142 but I fixed it, right?

4462.538 -> All the other cells should be good?

4465.743 -> Well unfortunately not, AutoFill is a one time deal,

4470.271 -> meaning that if I go from cell-to-cell here

4472.407 -> you'll see that they're still referencing H2, H3 and H4.

4477.794 -> If I would like to replicate this style of formatting

4481.196 -> to all the other cells, I'm going to need

4483.61 -> to AutoFill down again.

4486.107 -> So one last time I'm going to select I4,

4488.614 -> now with an absolute referenced H1 cell,

4492.922 -> I'm going to AutoFill down

4497.809 -> and there it is, so now H4 times H1

4503.603 -> and below that H5 times the absolute referenced H1.

4509.222 -> The cell below it H6 times the

4511.939 -> absolute referenced cell of H1.

4516.072 -> So when you're trying to build a function or formula

4518.289 -> that is going to consistently reference the exact

4520.774 -> same cell every single time no matter what,

4523.897 -> no matter how many times you AutoFill or copy and paste,

4527.879 -> you need to remember to absolute reference

4530.05 -> that cell using the F4 key.

4535.019 -> Go ahead and pause the video and catch up with me here.

4537.887 -> Remember how we got there, to calculate the tax rate

4541.045 -> we multiplied subtotal by the tax rate.

4545.434 -> However, to allow for us to absolute reference

4548.789 -> and AutoFill down, we remembered to lock in

4552.852 -> cell H1 in that function.

4555.79 -> Go ahead and take this opportunity to do just that.

4559.447 -> Welcome back, so now that we've got our

4561.699 -> basic menu up and running let's take a deeper look.

4566.108 -> Things look all right, we've got our

4567.42 -> weeks one through four sales,

4569.231 -> we've got total units sold,

4570.996 -> and the cool part about this entire process

4572.726 -> is that it's automated meaning that

4574.606 -> as soon as we update any of these sales values

4577.114 -> from weeks one through four our total units sold changes,

4580.597 -> our subtotal changes, our taxes change,

4583.093 -> our average units change, all because we told

4586.286 -> Excel how to do what we wanted it to do.

4590.129 -> Once we've gotten to this point here,

4591.406 -> maybe we'd like to expand.

4593.496 -> We're doing so well we think we're gonna add

4595.632 -> a new menu item, but there doesn't really seem

4599.278 -> to be anywhere for me to add that.

4601.53 -> We've got coffee, tea, bagels, English muffins,

4604.479 -> maybe I'd like to add a third drink.

4607.567 -> That'd be kind of silly for me to toss it

4608.96 -> underneath the total here so I actually need

4611.619 -> a little bit of room, in order to create this room

4615.508 -> I'm gonna insert a new row.

4618.051 -> To insert a new row go ahead and place your cursor

4620.268 -> anywhere below where you'd like to have your row inserted.

4624.285 -> In this case, I'd like to have a new blank row six.

4628.303 -> So I'm gonna place my cursor in row six.

4632.029 -> Having placed my cursor there I'm going to navigate

4633.759 -> up to the Home tab and find that Insert button.

4639.088 -> Go ahead and find that Insert button

4642.316 -> and select the Insert Sheet Rows.

4647.401 -> And just like that you've got a brand new, clean row.

4652.324 -> Now we could have also simply right-clicked

4654.704 -> on the row number six, and selected insert as well.

4660.265 -> Both will do exactly what you want it to do.

4664.684 -> Go ahead and try that for yourself and then come back.

4667.969 -> All right, so we've got our new blank row,

4671.498 -> our new coffee drink will be our

4678.986 -> And our Cofftea will be, I don't know, let's say $5.00.

4686.614 -> We'll go ahead and make up some sales figures here,

4688.588 -> it's super popular.

4692.57 -> And you'll notice that as we're going

4693.827 -> through this process here all of our totals

4695.545 -> are updating as well.

4697.774 -> I'll explain why in just a moment.

4702.86 -> Now you'll notice that, our totals down here

4705.077 -> at the bottom updated, however,

4707.62 -> our total units sold, subtotal and taxes did not.

4711.393 -> The reason being is that when you insert a row

4714.304 -> where there's an existing range of summation

4717.044 -> in this case we were already summing up

4718.716 -> these fields right here, when we inserted a row

4721.885 -> in the middle we stretched out that range.

4725.856 -> So any formulas that were referencing say

4728.236 -> from C4 to C7 if we stretched that out

4733 -> it would automatically grow to include that new C8.

4737.969 -> However, when we insert a new row it does not copy formulas.

4742.938 -> So you'll notice that here in cell G5 we have a formula,

4748.313 -> H5 we have a formula, I5 we have a formula.

4752.388 -> Those will not copy when you insert rows,

4755.058 -> so it is important to keep that in mind.

4757.23 -> Fortunately, we have our AutoFill technique,

4761.781 -> or by highlighting both one row that does have

4765.322 -> the formula and one row that does not,

4768.027 -> using the keyboard shortcut Control + D

4773.066 -> we can bring that down.

4775.898 -> Once again, the way we did that was by selecting

4778.511 -> a cell that already had the formula in place

4781.053 -> that we'd like to copy, and then auto filling down,

4785.314 -> using that little green box

4787.822 -> in the bottom right corner of the cell.

4793.557 -> Now that we've done that let's say we'd like to add

4795.763 -> a couple of columns in order to give ourselves

4797.772 -> some breathing room.

4800.07 -> I think I'd like to have maybe like

4803.147 -> let's say three columns of space.

4805.039 -> Just to kind of give myself some room,

4807.002 -> maybe I'd like to have this field reserved

4808.476 -> for notes about the week or the month.

4812.574 -> To insert a column it's just as easy as it was

4814.954 -> to insert a row, simply place your cursor

4817.207 -> where you'd like there to be a new column

4820.736 -> and then here in the Insert tab,

4822.779 -> I'm sorry in the Home tab find that Insert drop down,

4826.1 -> and select Insert Sheet Columns this time.

4832.137 -> Now that works for just one column at a time,

4835.074 -> however if you'd like multiple columns,

4838.035 -> let's say I'd like two additional columns inserted

4842.052 -> highlight two columns in this case column A and column B

4846.854 -> right-click and select Insert.

4851.649 -> And you'll see now that two columns were inserted.

4856.165 -> Go ahead and try that for yourself and then come back.

4859.852 -> Fantastic, so we've dropped in a new row,

4862.506 -> we've inserted a new menu item.

4864.387 -> We gave ourselves three extra columns of space,

4866.523 -> because we'd like to be able to take notes.

4869.971 -> Well you'll notice that when we moved things over

4871.736 -> a little bit we kind of lost the organization

4874.406 -> that we had set up here.

4876.171 -> For example, our title was initially supposed to be

4878.667 -> the first cell you saw and I'm not necessarily

4881.778 -> in love with how far off to the right

4883.427 -> average units is now, so let's fix that.

4887.444 -> Now there's two ways to move and copy data inside of Excel.

4891.635 -> The first way is relatively straightforward,

4894.549 -> click to select the cell that you'd like to move

4897.255 -> and then hovering over the border of that selected cell

4900.738 -> click and hold and then drag to the left,

4905.045 -> or to wherever you'd like to drop it off.

4907.622 -> And you'll notice that as I'm moving it

4909.224 -> I have that green silhouette with no content in it yet.

4913.125 -> But as soon as I release, boom.

4917.851 -> Now let's do something different with average units.

4919.534 -> I'd like to move average units from here to let's say here.

4925.792 -> This time instead of just

4927.069 -> highlighting and clicking and dragging,

4928.578 -> which would be the easiest way.

4930.761 -> I'm instead going to use my Cut tool,

4933.756 -> which can be found here inside the Home tab.

4936.38 -> You can also use the keyboard shortcut Control + X,

4939.515 -> as you can see in the parentheses

4941.105 -> of that tool tip that's popped up.

4944.437 -> Having cut the data it's now highlighted

4946.655 -> with that flashing marquee.

4949.267 -> I'm now going to place my cursor

4950.986 -> where I'd like the first cell to paste.

4954.097 -> In this case here this is the cell

4955.072 -> where I'd like average units to be.

4958.567 -> Once I've got my cursor where I'd like

4960.169 -> I can click the Paste button, or as the Status Bar says

4964.604 -> I can press Enter.

4969.376 -> Using either of those methods, I'll go ahead

4971.988 -> and paste the content.

4975.428 -> Go ahead and try that for yourself and then come back.

4978.835 -> Fantastic, so now you've had a chance

4980.89 -> to see how easy it is to move, cut and copy data

4984.826 -> without having to think about it too hard.

4988.889 -> Now, I'd like to go ahead and take this opportunity

4991.095 -> to create my notes section.

4993.266 -> This is where I'll take any notes on a week-by-week basis

4995.762 -> to keep an eye on fluctuations, annotate whether or not

4999.837 -> there are reasons for fluctuations

5002.09 -> in the business up or down.

5004.83 -> So first things first, I'm gonna go ahead

5006.141 -> and just type out notes here,

5010.334 -> and what I'd like to do is just

5012.876 -> on a day-by-day basis annotate

5014.594 -> what's going on throughout the week.

5015.988 -> So I'm gonna start with Sunday here,

5018.995 -> now I know what comes after Sunday,

5020.841 -> and hopefully all of you do too.

5022.942 -> So I don't necessarily want to have to go through

5024.498 -> this whole process of Monday, Tuesday, Wednesday.

5028.265 -> I already know what comes after all of those days.

5031.26 -> Well, so does Excel.

5033.35 -> As it turns out just in the same way that we're able

5035.73 -> to copy formulas like we did earlier.

5038.795 -> We're actually able to copy certain values

5041.163 -> inside of Excel just as easily.

5044.705 -> So I'm gonna take my cursor over here

5046.992 -> and I'm gonna highlight Sunday.

5051.624 -> Now, with my active cell on Sunday,

5055.235 -> you'll notice that we've got this drop down arrow

5057.162 -> just like we did in every other instance.

5058.764 -> This little box in the bottom right corner

5060.599 -> the AutoFill handle, I'm gonna take my cursor

5063.362 -> and hover over it and once again,

5065.243 -> make sure that your fat white cross

5067.251 -> becomes a thin black cross.

5071.233 -> Once I've got that thin black cross

5072.87 -> I'm gonna go ahead and click and hold,

5075.378 -> and I'm gonna drag and watch what happens.

5077.584 -> Look at the little tool tip on the right, it says Monday.

5082.216 -> I drop down again, Tuesday.

5085.339 -> It's actually auto filling the days of the week.

5089.612 -> Excel does know, just like you and I, that Sunday comes

5093.315 -> before Monday, comes before Tuesday and so on.

5096.717 -> All the way through Saturday, so I've taken it down

5100.084 -> to row 10 and I'm gonna release and look at that.

5105.425 -> So we can use AutoFill not only to continue

5107.306 -> the pattern of formulas that we're using

5109.279 -> but we can also use it to continue on annotating

5113.064 -> common knowledge fields.

5117.18 -> Well what else can we AutoFill?

5119.937 -> How about something as simple as the date?

5122.433 -> Today's date I've currently got

5127.145 -> 2015

5131.034 -> and of course I know full well that after November 25th

5134.435 -> we've got the 26th, 27th, and so on.

5139.079 -> But I really don't want to have to go through

5140.844 -> this whole process of typing.

5143.34 -> Well, once again, we've got AutoFill to the rescue.

5146.742 -> So I'm gonna highlight the little cursor

5149.447 -> and once I've got my fat white cross,

5152.57 -> and I take it and turn it into that thin black cross

5154.834 -> I'm gonna click and hold and I'm gonna drag down

5157.911 -> and boom, look at that, the tool tip

5160.047 -> on the right-hand side, 11/26, 27,

5165.711 -> 28,

5167.023 -> 29,

5168.915 -> 30

5170.68 -> now this is where you really have to ask yourself

5173.873 -> one is their a 31st day, and if there's not,

5177.031 -> does Excel know that?

5180.316 -> Boom, look at that, I'm gonna go ahead and release

5184.07 -> Excel actually knew that there isn't a 31st day in November.

5188.211 -> And so it jumped straight to December 1st.

5191.74 -> AutoFill can really save you a lot of time

5193.633 -> with just these little micro decisions,

5195.769 -> these little micro checks to see whether or not

5198.103 -> you're putting in the right information.

5200.018 -> Or if it's such common knowledge that it's beyond mundane

5202.886 -> AutoFill can alleviate the whole process and tedium

5206.45 -> of having to do something that simple.

5210.955 -> Go ahead and take this opportunity

5212.093 -> to try this out for yourself.

5214.357 -> Go ahead and type a familiar common value

5216.981 -> like Sunday or better yet maybe its

5219.117 -> abbreviated value of Sun and AutoFill down

5223.087 -> and watch what happens.

5228.022 -> You can also use the months of the year like January

5231.632 -> or their abbreviation of Jan.

5234.837 -> It's pretty cool, pause the video and

5237.089 -> take this opportunity to try it for yourself.

5239.806 -> We've got a pretty good-looking worksheet here.

5242.279 -> If we wanted to we could expand a couple of things

5244.206 -> here and there but right now the thing

5246.911 -> that's kind of driving me a little bonkers is the fact that

5250.603 -> it's not very visually striking,

5252.298 -> in fact it's kind of difficult to read.

5255.166 -> So with that having been said,

5256.315 -> let's try and spice things up a little bit.

5258.324 -> Let's make this a little bit more

5260.704 -> user-friendly should we say.

5263.281 -> Let's start with something simple, the title.

5266.918 -> Now at first glance you might not

5267.94 -> even know this is the title, so let's fix that.

5271.574 -> Now, what does a title look like?

5273.861 -> That can be a really difficult thing to determine.

5276.485 -> And in fact it's a really tedious process

5278.331 -> to have to go through this time and time again

5281.326 -> when it comes to deciding

5282.592 -> what something should or shouldn't look like.

5285.773 -> So instead of having to make those design decisions yourself

5289.256 -> let's let Excel do that for us.

5292.785 -> I'd like to introduce to you now, the Cell Styles tool.

5296.628 -> Which has a convenient series of pre-packaged styles

5300.726 -> that you can apply to a cell allowing you

5303.722 -> to take your hands off the wheel so to speak and allow

5307.959 -> Excel to change the visual aspect of cells for you.

5311.779 -> Once again, let's take the title, here inside the Home tab

5314.6 -> find that Cell Styles drop down

5317.909 -> and as you click on the drop down

5318.977 -> you'll see there's a number of different categories.

5320.916 -> You've got Good, Bad, and Neutral, Data and Model,

5326.292 -> Titles and Headings, and then we have our

5328.997 -> themed cell styles and number formatting.

5332.561 -> Now where in this might I find a title?

5338.633 -> It's not like it's just going to be

5339.62 -> named something so convenient, right?

5343.231 -> Well, there you go, so let's go ahead and find that.

5347.979 -> Click and select cell A1 and then hover over title

5352.24 -> to get a live preview of what that will look like.

5355.77 -> Now of course you're gonna get a live preview

5357.364 -> of what all of these title and headings and cell styles

5360.634 -> will look like just by hovering over them.

5364.319 -> In this case here however, we'd like a visual indication

5366.733 -> that this is the title, it's bigger,

5369.752 -> the font styling's a little bit different,

5371.969 -> I think this is good.

5374.35 -> Go ahead and find that Title button and give it a click.

5379.481 -> And there you go, without having to decide

5381.687 -> what a title should or shouldn't look like,

5383.669 -> we've allowed Excel to do that for us.

5386.409 -> Leaving us to focus on something more important

5388.418 -> like actually filling out the numbers,

5390.507 -> and things to that effect.

5393.747 -> Let's take a look at the headings,

5395.674 -> go ahead and highlight from

5397.241 -> D3 all the way down to L3 here, our headers.

5402.768 -> I'd like some visual indication

5404.254 -> that these are the headers.

5406.634 -> So once again, I'm gonna jump to my Cell Styles,

5411.403 -> if only there were a headings style.

5416.268 -> Well as it turns out there are.

5418.984 -> Heading one a little big, heading two getting there,

5423.919 -> I like heading three, something non-descript, low-profile,

5427.703 -> and I'm gonna lock it in with a click.

5430.362 -> You'll notice that we highlighted everything

5431.964 -> before we did it, in most instances in Excel

5435.575 -> you won't have to but there are some instances

5437.99 -> where you will have to.

5439.963 -> There's a saying we have at Learn iT

5441.325 -> and it's you've got to select to effect.

5443.252 -> If you're changing the visual effects of a cell

5445.713 -> you've got to select it first.

5449.742 -> Let's go ahead and do the same thing for our item names.

5454.351 -> From coffee down to English muffins I'm gonna go ahead

5457.091 -> and highlight those and this time I'll use

5458.937 -> the cell style heading four.

5465.381 -> So while it's all well and good that we're able to use

5467.517 -> these cell styles to change the visual aspect

5469.932 -> of things to make them look good,

5472.228 -> we can also use them from a functional standpoint as well.

5475.676 -> For example, the total row indicating

5477.882 -> that these are totaled values, because if I'm just going

5480.599 -> down week one, I'm not gonna have any real indication

5484.348 -> that this cell is any different than this cell.

5488.386 -> So let's fix that, using Cell Styles

5490.905 -> we're going to use the total styling.

5494.969 -> Which is gonna highlight the total row

5496.394 -> using bold font styling and borders.

5501.166 -> Go ahead and find that and give it a click.

5505.965 -> All right, this is starting to come together.

5508.775 -> What other kinds of functional changes

5510.006 -> can we implement here?

5511.77 -> Well as I'm looking at this here I know for a fact

5514.267 -> that these are cells that are going to change.

5516.925 -> These are cells that we're going

5518.075 -> to be inputting numbers into.

5520.455 -> So maybe we'd like to use some visual indication

5522.498 -> to ourselves and to other people using this

5525.366 -> that these are input cells.

5529.418 -> Now where oh were, might I find a cell that indicates input?

5535.083 -> Oh, well there you go.

5537.498 -> So highlight all the cells that you'd like to impact

5540.25 -> in this case I'm highlighting all of the sales fields

5542.456 -> from week one through four,

5544.987 -> and I'm gonna select the Input styling.

5548.11 -> Now, this might not mean anything officially,

5551.639 -> but if you continue to use this same naming scheme

5554.425 -> this same styling scheme, you're going to know

5557.792 -> every single time you see this style of cell

5560.219 -> that this is a cell that requires input.

5564.12 -> Much in the same way that we might also highlight

5566.372 -> all these cells that have formulas in them,

5569.983 -> and call them output cells.

5574.65 -> Go ahead and take this opportunity

5575.997 -> to spice up your worksheet a little bit here.

5579.816 -> Of course you can use other Cell Styles

5581.779 -> other than the ones provided in the Title and Headings,

5584.24 -> and Data and Model, in fact maybe you'd like to use

5587.514 -> some Themed Cell Styles that change and ebb and flow

5590.474 -> with the theme which we'll be

5591.46 -> talking about in just a moment.

5593.84 -> But go ahead and let's limit ourselves

5595.883 -> to just these ones for now.

5598.193 -> We'll talk a little bit more so

5599.331 -> about Good, Bad and Neutral in just a little bit.

5604.451 -> Welcome back, so now we're starting to see that

5606.541 -> using Cell Styles we're able to kind of hand-off

5609.269 -> control and allow ourselves

5612.613 -> to focus on the really important things,

5614.552 -> like the numbers, the things we're actually

5615.608 -> putting inside the cells.

5617.628 -> You don't need to waste time thinking about

5619.393 -> how something should look by simply allowing

5621.971 -> the Cell Styles to do what they do best,

5624.641 -> you can focus on what you do best.

5627.632 -> Now, what other benefits are there, to using Cell Styles?

5631.289 -> Because I certainly could have just used the fill paint can

5635.097 -> well there's a couple of different things

5636.781 -> that come in to effect with Cell Styles.

5637.617 -> Obviously we see that there are a couple of border changes

5640.055 -> that take place, in some instances depending on

5642.562 -> styling the font color and font styles change as well.

5647.032 -> But there's one other thing that comes into play here

5649.029 -> that's really important, and it's actually found here

5652.28 -> inside the Page Layout tab.

5655.066 -> A lot of people don't necessarily consider

5657.655 -> themes to be something that's relevant inside of Excel.

5660.151 -> In fact many people don't even know

5661.382 -> that Excel has themes, but it does.

5665.109 -> And you're in fact currently inside of a theme right now.

5669.3 -> Themes control everything from the default font styling

5672.412 -> to the default color scheme.

5675.001 -> They also control shape effect, but we don't see

5677.381 -> too much of that inside of Excel.

5679.842 -> So let's focus on the top two for now, Colors and Fonts.

5684.939 -> Themes are a combination of these two items.

5688.666 -> By clicking and dropping down the themes option here

5691.315 -> you'll see a series of options, we're currently

5693.042 -> inside the Office theme.

5695.779 -> But if I hover over some of these

5697.173 -> other tools watch what happens.

5701.735 -> Wow, it's pretty incredible to see

5704.115 -> how the entire look and feel of this document

5706.53 -> changes simply by hovering over these tools.

5710.687 -> This live preview gives you an idea of

5712.115 -> what these will look like if we were to choose them.

5715.644 -> Notice that not only is the color scheme changing,

5718.384 -> but so too is the font.

5721.797 -> By surrendering a little bit of control

5723.446 -> and using Cell Styles we allow ourselves

5725.872 -> to change the look and feel of a worksheet

5729.634 -> in a single click by changing the theme.

5733.158 -> Let's find a theme that works well for you.

5736.159 -> Personally, I like this Ion font theme,

5741.766 -> so I'm gonna go ahead and find that

5743.101 -> and give it a click to lock it in.

5746.19 -> And just like that this is a whole new worksheet now.

5749.382 -> It looks pretty good.

5752.297 -> Now of course I don't have to choose

5754.386 -> a theme I can always build my own in a sense.

5758.461 -> Simply by finding my own color scheme that I like

5761.352 -> and choosing my own combination of font styles.

5765.706 -> So let's say I like this color scheme,

5768.121 -> but I'm not in love with the font choices.

5770.896 -> I can always go to fonts and get a live preview

5774.228 -> of what some of these other options are available.

5778.361 -> In this case here, I think I like

5779.673 -> the default Cambria and Calibri.

5783.091 -> So I'm gonna go ahead and choose

5783.985 -> just the standard Office one at the very top here.

5786.156 -> Calibri Light for the headings and Calibri for the body.

5790.719 -> And I'm gonna click to lock it in.

5795.026 -> And just like that this is now my style.

5798.184 -> I like the color scheme, I like the font stylings,

5801.923 -> and if I want I can even save this setup

5805.534 -> as my own custom theme.

5810.085 -> In fact go ahead and do that.

5812.302 -> Find your own font combination and color scheme

5815.007 -> that you like and then join me here in the drop down

5819.024 -> where it says themes and select Save Current Theme.

5826.188 -> Now it's very important to note that where

5828.312 -> it's placing us right now, the Document Themes folder

5832.562 -> don't navigate out of that, this is the only place

5835.36 -> Excel looks for custom themes.

5838.959 -> So don't save it to the desktop, don't go to My Documents,

5842.372 -> stay inside the Document Themes folder.

5845.272 -> I'm gonna go ahead and change the file name

5847.163 -> to Sean's Theme, something I'm gonna remember.

5852.376 -> And I'm gonna click Save, and just like that

5856.184 -> I've built my own custom theme.

5858.982 -> If I click on the drop drown inside Themes

5860.84 -> check it out, there's my theme,

5865.514 -> pretty cool.

5868.189 -> Go ahead and try that for yourself and then come back.

5871.431 -> Once again what we did was,

5873.323 -> we decided on our own personal color scheme,

5875.735 -> and font combination that we liked for our workbook.

5881.508 -> Then, clicking on the drop down arrow,

5884.132 -> we selected Save Current Theme to save that combination.

5890.077 -> Go ahead and try that for yourself.

5892.863 -> Welcome back, this is starting to look pretty good.

5896.799 -> In fact it looks so good, that I might want

5898.935 -> to hold on to this and use it every month.

5901.965 -> Well there's a couple of different things

5903.265 -> that I need to worry about if that were the case.

5905.727 -> For example, I would have to worry about

5907.77 -> overriding last month's sales data.

5911.381 -> Now if I were to do this in the real world

5912.937 -> of course you would hope that I would go in

5915.073 -> and create another copy of this file

5916.512 -> before editing the data.

5918.239 -> But the reality is most of us are a little on the

5920.619 -> forgetful side when it comes to doing things like that.

5923.724 -> So let's take a look at some

5925.078 -> of the solutions that are available to us.

5927.443 -> First things first, I'm gonna get rid of all

5929.498 -> the sales data that wouldn't be relevant in a new month.

5935.86 -> Now, one of the things that you'll want to remember

5937.822 -> is that we've got formulas all over the place here.

5941.236 -> And as soon as new sales data gets put in

5943.767 -> all of these sales fields are going to kick into

5946.17 -> high gear and we're gonna get all the information we need.

5949.572 -> So pretty much everything that we need to worry about here

5954.007 -> needs to get cleared out, all right.

5957.583 -> So now that we've done that, how do we preserve this

5961.275 -> and make sure that every time we open it up

5964.92 -> it opens just as fresh as it is right now?

5968.113 -> Well in order to do something like that,

5969.309 -> we need to save it as a template.

5972.014 -> To save an item as a template it's really

5974.359 -> just as easy as saving any other file.

5977.401 -> We're going to start off by going up

5978.713 -> to the File tab here in the top left.

5982.846 -> We're gonna go ahead and click File, Save As,

5985.876 -> and in this case here I'm gonna go ahead

5987.653 -> and save it to let's say MyDocuments.

5991.636 -> Now as it stands, it's trying to save it as

5994.402 -> an Excel workbook, however we're gonna change that.

5998.082 -> Instead of saving it as an Excel workbook here

6000.822 -> I'm gonna find Excel Template, this is really important

6005.292 -> and in fact it's one of the

6006.325 -> most critical things you'll need to remember.

6010.795 -> Now, one thing you'll need to keep in mind here

6013.013 -> is check out what happened as soon as I changed

6014.928 -> it to an Excel Template, it took me to a whole

6017.628 -> new folder that I hadn't even noticed,

6020.005 -> the Custom Office Templates folder.

6022.338 -> Don't navigate out of this.

6023.894 -> This is the only place that

6024.897 -> Microsoft Office looks for templates, so once again,

6028.755 -> do not leave the Custom Office Templates folder.

6032.568 -> If you don't get automatically taken

6034.297 -> to the Custom Office Templates folder

6036.643 -> go ahead and take that opportunity to Google

6038.57 -> where that folder is.

6040.009 -> While it should work all the time,

6041.855 -> there are going to be occasions where

6043.214 -> it doesn't automatically do it,

6044.851 -> so you'll need to navigate there manually.

6047.324 -> Now for most of us this should have taken us

6048.752 -> right where we need to go, so I'm gonna go ahead

6050.424 -> and name this Monthly Sales.

6055.927 -> And from here all I've got to do is click Save.

6059.537 -> Boom, and just like that I've now created a template.

6064.541 -> Now what do I need to do now?

6066.596 -> Well first things first you need to remember

6068.106 -> you're still inside the template

6069.777 -> after you've done with that file Save As.

6072.429 -> So we're gonna close out of this

6073.614 -> by going to File and Close.

6078.293 -> Now, let's go ahead and open that template.

6084.248 -> We're gonna go to File, New, and I don't see

6088.312 -> my Monthly Sales template here.

6090.82 -> I see all the other templates we saw earlier,

6093.188 -> but no Monthly Sales.

6096.636 -> But you will notice that we have a new section

6098.517 -> available that wasn't there before, the Personal section.

6102.546 -> This is where Microsoft is going to log

6104.31 -> all of your custom templates.

6106.865 -> So I'm gonna go to Personal and there it is,

6110.197 -> there's my Monthly Sales template.

6114.167 -> I'm gonna go ahead and open it up here,

6117.836 -> and at first it looks exactly the same

6120.657 -> as everything we've done before.

6123.13 -> Except all of the fields are empty, and we've got no dates,

6127.066 -> and none of the formulas are kicking in.

6129.609 -> So, what was the difference between this

6131.582 -> and what we've just closed out?

6134.044 -> Well take a look at the name of the file here.

6137.109 -> Monthly Sales One, weird, that's almost like

6141.253 -> it's using the exact same naming scheme it does

6144.005 -> when it opens a brand new Excel workbook, Workbook One,

6149.009 -> Book Two, Book Three, and so on.

6152.248 -> So what's actually happened here is it's created

6154.756 -> a brand new copy of Monthly Sales.

6158.564 -> So let's say that I go ahead and fill out

6160.12 -> all the sales data here and nothing too crazy,

6163.208 -> I'll just go ahead and give it a couple of numbers here.

6168.212 -> Looks like we have a good week two.

6172.404 -> Week three maybe this is some sort of major sporting event

6176.13 -> or conference is in town.

6181.274 -> So I filled in all the sales data

6182.586 -> and we'll say that the notes are going

6183.979 -> to be for the week of

6187.897 -> 11/31 if there is a 31.

6194.212 -> There is not it doesn't look like it so let's try,

6199.741 -> there we go.

6201.277 -> All right, so now the concern would be of course

6204.353 -> that if I click Save it's gonna overwrite my template.

6207.499 -> So normally you'd have to go do a File, Save As.

6210.541 -> But check out what happens if I click the Save button now.

6216.114 -> It forces us into Save As, you can't overwrite a template.

6220.387 -> And this is kind of the magic of templates,

6222.233 -> you can create this pristine, protected data set

6226.946 -> that every time you open up a new copy of you start over.

6231.95 -> Nothing you can possibly do would overwrite

6233.842 -> this template unless you deliberately go

6236.304 -> and save over the template using the exact same

6239.88 -> methodology we used to create it, not bad.

6245.58 -> Go ahead and pause the video and take this opportunity

6247.682 -> to try that out for yourself.

6249.446 -> Remember, clear out all of the data that would change

6255.542 -> and then from there once you've got everything

6257.179 -> in what we call boilerplate status,

6259.268 -> which means that all of the data is generic

6261.416 -> and non-time specific, or non-situation specific,

6266.78 -> go to File,

6269.857 -> Save As,

6273.282 -> and save it as an Excel Template.

6278.378 -> Go ahead and pause the video

6279.59 -> and take this opportunity to catch up.

6283.358 -> All right, so we've now talked about how we can

6286.679 -> save these custom templates in order to

6289.418 -> prevent ourselves from doing more work.

6292.414 -> Let's keep on that trend.

6294.515 -> Now Birch & Daughters Coffee Shop is doing phenomenally well

6298.196 -> just crazy sales, really great.

6300.819 -> So great in fact, that we're actually going

6302.991 -> to be opening up a new location.

6305.905 -> How great is that?

6308.575 -> To make a duplicate of this worksheet here

6311.93 -> so that we can keep track of that new location's sales

6315.39 -> there are a couple of ways that we can do that.

6317.408 -> The first way is a little roundabout,

6319.904 -> by right-clicking on sheet one, the sheet that currently

6322.655 -> has this worksheet you can select Move or Copy

6329.389 -> and then select Create a Copy and click OK.

6332.803 -> But that was a lot of clicks.

6335.02 -> Let me show you an easier way.

6336.878 -> By pressing and holding the Control key

6339.858 -> click and drag sheet one to the right,

6345.071 -> and release.

6347.938 -> And you'll notice it instantly creates,

6349.575 -> Sheet One in parentheses two.

6354.498 -> And as I navigate from sheet to sheet here

6356.135 -> you'll notice it is an exact duplicate in every single way.

6362.776 -> Now of course we're gonna have to rename these worksheets,

6365.562 -> so let's do that, I'm gonna right-click

6367.745 -> and rename sheet one, this will be

6370.322 -> our San Francisco location.

6375.187 -> And our second sheet here will be our LA location.

6382.989 -> There you go, and of course we can create

6385.288 -> additional copies on top of that,

6386.925 -> once again by pressing and holding the Control key

6389.781 -> and clicking and dragging to the right and releasing.

6394.343 -> And let's say we'll call this our New York location.

6399.22 -> We're doing pretty good here.

6403.074 -> Now, when we've got multiple worksheets like this here

6406.65 -> one thing that's going to be a little bit problematic

6408.995 -> is the fact that now if we want to make

6410.876 -> a change to this entire styling here,

6413.581 -> we have to go from sheet to sheet to sheet.

6417.18 -> At least, that would be the impression

6418.446 -> that you might have.

6420.547 -> For example, let's say we'd like to change the price of tea

6426.314 -> from $3.50 down to $3.00.

6429.112 -> We found that sales just aren't quite what we hoped

6430.911 -> they would be so let's drop down the price.

6434.198 -> Now, I don't want to have to go from SF to LA to NYC

6438.877 -> to change that single cell in all three.

6442.209 -> In fact if I'm making any change like that

6444.461 -> having to make that same change

6445.889 -> three times is mind-numbing.

6449.5 -> Instead of going through that process

6451.3 -> because these are all the same style of worksheet

6454.748 -> I can actually group these together

6457.337 -> and make the change all at once.

6461.203 -> To select all the tabs in this workbook,

6463.722 -> simply right-click on any one tab

6468.401 -> or pressing and holding the Control key

6472.383 -> single-click on all the unselected tabs,

6476.946 -> and you're gonna have all three selected at this stage here.

6479.396 -> Notice that they all have a visual indication

6481.404 -> of that greet line underneath showing us

6483.54 -> that they're all selected.

6486.037 -> Having selected all three sheets,

6487.848 -> I'm now gonna go to this cell,

6491.609 -> and I'm gonna change the price from $3.50 to $3.00,

6497.809 -> and I'll tap the Enter key to lock it in.

6500.921 -> And what the heck, maybe I'll also change

6502.894 -> English muffins to crumpets.

6505.936 -> We really want to bring up our tea sales.

6508.769 -> So maybe if we sell crumpets that will help.

6513.32 -> Now I'm in the NYC tab here, but let's see

6516.64 -> if that made the change in LA and SF as well.

6521.853 -> Here's my LA tab, crumpets $3.00,

6525.836 -> SF crumpets $3.00.

6529.818 -> NYC crumpets $3.00.

6533.185 -> By grouping our worksheets together

6534.949 -> either by right-clicking and selecting

6536.795 -> Select All Sheets, or by pressing and holding

6539.837 -> the Control key and selecting all three worksheets

6543.448 -> we were able to make one change and have it

6546.397 -> propagate across all those selected worksheets,

6550.994 -> pretty cool.

6553.583 -> Go ahead and try that for yourself and then come back.

6557.032 -> All right, so keep on keeping on.

6561.722 -> We're doing really well, we've got three locations,

6563.893 -> we've got a template that allows us

6565.333 -> to inject our sales data and calculate total units sold,

6568.734 -> our subtotal, our average and so on.

6571.358 -> Great, now that we've got a template and a worksheet

6575.457 -> that allow us to input the data,

6577.999 -> let's talk about reading the data a little bit more so.

6581.331 -> Now for this I'm gonna go ahead and just

6583.386 -> jazz up some sales data here.

6596.947 -> All right, so let's say I'd like to analyze

6600.813 -> week-over-week how we're doing in any one particular item.

6605.701 -> Now, when it comes down to analyzing sales data,

6608.824 -> there's a couple of different ways we can come at this.

6610.414 -> Now this is a really small worksheet,

6611.935 -> so there's not a lot of problems reading it.

6614.722 -> But with that having been said,

6616.161 -> you're going to need to know how to navigate

6618.379 -> through that kind of sales data.

6620.515 -> For example, let's say I'd like to compare

6623.243 -> my items directly with week four.

6628.375 -> Now as it stands, week four is significantly

6631.649 -> more separated from coffee and even more so

6634.435 -> is my total units sold, subtotal and taxes.

6639.602 -> In order to fix this, I'd like to be able

6642.028 -> to bring in let's say total units sold

6645.07 -> and subtotal all the way over here

6647.566 -> to compare with my items.

6649.087 -> Instead of having to go from here to here,

6652.175 -> tea all the way over.

6654.869 -> To do that it would be nice if I could freeze

6658.236 -> everything off to the left here

6660.407 -> and scroll everything over here over.

6664.923 -> As it turns out here in the View tab

6666.514 -> we have exactly that tool, Freeze Panes.

6670.995 -> Now, Freeze Panes has three options available;

6674.617 -> Freeze Panes, Freeze Top Row, and Freeze First Column.

6680.202 -> Freeze Top Row and Freeze First Column do exactly

6681.967 -> what they sound like they do.

6684.347 -> By clicking Freeze Top Row, I can now,

6687.377 -> scroll down and you'll see that the top row stays intact.

6692.32 -> To unfreeze simply click the Freeze Panes

6694.537 -> drop down again and select Unfreeze Panes.

6699.82 -> Consequently on the other side of things,

6701.294 -> Freeze First Column same thing,

6703.14 -> by selecting Freeze First Column

6705.857 -> it only freezes the first column.

6710.234 -> While both of those are great in most cases

6712.811 -> they only do a limited amount.

6715.551 -> If we need to freeze anything more

6716.991 -> than just the top row or the first column

6719.139 -> we're gonna need to turn to this last option, Freeze Panes.

6724.793 -> Now Freeze Panes it's important

6725.989 -> to understand how it works,

6728.131 -> it freezes all rows above a cursor

6733.58 -> and all columns to the left of it.

6737.237 -> Meaning that if I've got my cell here in column F

6740.36 -> and row four it's going to freeze all rows

6743.483 -> above row four and to the left of column F.

6750.345 -> So let's take a look and see what that does.

6751.994 -> I'm gonna place my cursor here in row F4,

6755.302 -> and I'm going to Freeze Panes.

6758.449 -> As I scroll down you'll see that it's frozen

6761.154 -> rows one through three,

6762.756 -> all the rows directly above row four.

6766.32 -> And if I scroll to the right,

6770.345 -> it has frozen A through E

6773.263 -> all the columns to the left of column F.

6777.825 -> So now I can compare total units sold to my price,

6783.445 -> to see if there's any correlation with these items.

6786.068 -> Or by unfreezing panes and placing my cursor here

6789.993 -> in cell E4, I'll freeze panes again and now,

6795.577 -> I can control and compare the subtotal

6799.188 -> of each individual item, so I can see here

6801.73 -> that coffee sold $4,000, tea $7,000, cofftea $10,000

6806.119 -> we're doing all right in cofftea.

6809.312 -> Freeze Panes allows us to hold on to part of the document

6813.712 -> and navigate to where we need to be

6816.382 -> and it's a fantastic tool when it comes

6817.984 -> down to review and compare.

6822.373 -> Go ahead and take this opportunity to practice for yourself.

6825.902 -> One thing I'd also like to point out before you do,

6828.781 -> is let's say you don't want to freeze any rows at all.

6833.124 -> Simply place your cursor in the row one,

6837.234 -> meaning there are no rows above it to freeze.

6840.461 -> So what will it freeze?

6843.456 -> All the columns to the left and only that.

6846.823 -> So if I select Freeze Panes now and scroll down

6851.084 -> no rows are frozen, however all columns

6854.811 -> to the left of column E are.

6858.712 -> Consequently, on the other side of things,

6860.349 -> if you don't want any columns to be frozen

6862.601 -> but you do want rows to be frozen,

6865.504 -> let's say I'll place my cursor here in column A

6868.673 -> meaning no columns to the left can be frozen

6871.587 -> because there are no columns to the left.

6874.711 -> I'll select Freeze Panes again and there you go.

6880.318 -> Rows one through three, the rows directly above

6882.872 -> my active cell are frozen, however if I scroll

6885.868 -> to the right no columns are frozen.

6890.872 -> So there you go, go ahead and try that out for yourself

6894.064 -> pause the video and then come back.

6897.501 -> While freezing panes is a really useful tool,

6900.218 -> sometimes it's not enough.

6902.273 -> Sometimes there are so many different things

6904.119 -> that we need to compare that by freezing just one portion

6908.519 -> we're still not getting the whole picture.

6911.177 -> For example, let's say I just don't need

6913.221 -> to see average units at all,

6916.112 -> in fact maybe I don't need to see weeks one through four.

6919.432 -> Maybe as the CEO in this hypothetical scenario

6922.555 -> I only really care about what items are selling

6925.678 -> and how much am I making from them.

6928.174 -> Meaning that all this is superfluous, I don't need it.

6933.689 -> In fact, I really don't even need the taxes.

6936.232 -> So, what can I do?

6938.403 -> How can I hide all of that without having to

6941.201 -> impact the usability of this document?

6944.812 -> Well, I can use something called Custom Views,

6947.226 -> which means that I can create views

6949.56 -> that depending on the person viewing them and their needs

6952.892 -> I can change what is visible and what is not.

6956.905 -> Let's take a look, first things first,

6959.614 -> we're gonna need to create a snapshot of this.

6963.062 -> To do that let's find the Custom Views tool

6965.697 -> which can be found here in the View tab,

6968.31 -> go ahead and find that and give it a click.

6971.108 -> Now you'll see here that currently it's blank, for now.

6974.428 -> However, what we're gonna do at this stage

6975.984 -> is we're gonna create a snapshot of this current view

6980.256 -> the exact way everything looks.

6981.696 -> Currently no rows are hidden,

6983.461 -> no columns are hidden, nothing's changed.

6986.967 -> So I'm gonna click Add here on the right,

6989.591 -> and I'm gonna call this my Original view

6993.526 -> and I'm gonna click OK.

6996.719 -> A little anticlimactic nothing seems to have changed here

6999.308 -> but what I've done is I've taken a copy

7001.653 -> of what the view layout of this is currently.

7006.413 -> Now let's build this idealistic view,

7009.664 -> first things first, I don't need this average nonsense.

7013.02 -> Some I'm gonna highlight all the rows here

7015.852 -> and I'm going to right-click and select Hide.

7021.773 -> You can also from the Home tab find

7024.838 -> the Format drop down and inside

7027.949 -> Hide and Unhide select Hide Rows.

7032.884 -> Personally, I like the right-click myself,

7034.683 -> but to each their own.

7037.644 -> Next, I'm going to hide everything

7039.733 -> between items and subtotal,

7044.122 -> so that's columns E though J.

7049.741 -> Once again, having highlighted all of those

7051.634 -> I'm going to right-click and Hide.

7058.727 -> Lastly, I don't need taxes, so I'm gonna right-click

7061.351 -> on that and select Hide.

7065.728 -> And maybe I'd like to zoom in a little bit.

7067.446 -> So I'm gonna zoom in and let's say to about 160

7072.415 -> about 160% zoom

7077.465 -> and now I'm gonna take a snapshot

7079.718 -> of this view, this will be the CEO view.

7083.688 -> What are my items?

7084.838 -> How much money did I make from them?

7087.752 -> So I'm gonna click on Custom Views once again,

7090.086 -> and this time I'm gonna click Add again.

7094.683 -> And I'm going to call this the CEO view,

7099.325 -> and click OK.

7103.565 -> Now that I've done that, I'd like to get back

7105.771 -> to my original view.

7108.162 -> Here's the scary part with hiding rows and columns

7110.449 -> it's easy to forget where and how many were hidden.

7115.302 -> And then on top of that you've gotta find

7116.974 -> where they were, and then you've gotta find the exact border

7119.633 -> and right-click and it's so much.

7122.338 -> Instead we can use custom views, select the original view,

7128.201 -> and click Show to revert back to the original view.

7133.948 -> The exact zoom level and viewability,

7137.303 -> meaning no columns or rows were hidden,

7140.392 -> that we were when we originally started.

7143.747 -> Now, let's go ahead and build another custom view.

7147.404 -> Let's say I've got somebody who's specifically

7149.784 -> in charge of the beverage situation at Birch & Daughters.

7154.248 -> Meaning, they only really care about

7156.175 -> how many units we're selling in beverages.

7159.019 -> We're getting ready to order more drinks.

7162.224 -> So I'm gonna hide all the rows that have anything

7165.324 -> to do with food, in this case bagels and crumpets.

7169.759 -> I'm going to right-click and select Hide.

7175.25 -> Same deal once again, I'm going to hide

7177.305 -> any columns that don't mean exactly what I need.

7182.054 -> So I'm gonna right-click on these

7183.284 -> first three columns and hide.

7186.895 -> I'm going to hide price, and I really don't care

7189.565 -> about individual weeks sold,

7191.945 -> I only really care about total units sold.

7194.767 -> So I'm going to right-click and select Hide

7199.085 -> and now I've got items, total units sold, subtotal,

7204.658 -> and I really don't need taxes either,

7209 -> or total there you go.

7211.682 -> So now, I've only got my beverage information here.

7216.117 -> In fact let's get rid of average units too.

7220.877 -> Wow, we really hid a lot that time didn't we?

7224.244 -> But if this is all I need

7225.684 -> out of that entire worksheet,

7227.483 -> why should I see anything else?

7230.978 -> Once again, I'm going to click Custom Views,

7233.555 -> I'm going to click Add,

7236.191 -> and I'm gonna call this the Drink Order view.

7241.45 -> And just like last time I'm going to click Custom Views,

7245.751 -> Original, and Show to get back to home.

7252.148 -> Now, I've got two custom views,

7254.319 -> the CEO view which brings me to the exact

7258.081 -> view that I need to see,

7259.88 -> items and how much money I'm making from them.

7262.76 -> And I've got my drink order view,

7266.649 -> which only shows me the item and how many

7269.482 -> I've sold so I know how many more I need to order.

7273.452 -> And once I'm done with all those views

7275.426 -> I can always go back to the original.

7278.758 -> That's Custom Views, go ahead and try that

7281.01 -> for yourself and then come back.

7283.518 -> Welcome back, so now it's time to shift gears

7286.363 -> and talk about the final stages of presenting

7288.209 -> any worksheet or workbook that we've created.

7291.332 -> When it comes to presenting there's a number

7292.435 -> of different ways we tend to share these files,

7295.256 -> but they all start with something

7296.405 -> that should be incredibly important,

7298.948 -> making sure you spelled everything correctly.

7302.071 -> I mentioned it in the beginning,

7303.093 -> but I'll mention it again,

7304.648 -> Excel does not spell check in real time.

7307.4 -> Meaning that any time you're typing any word

7309.374 -> into any cell, even if you misspell it

7312.079 -> it's not going to warn you the same way

7313.762 -> it would inside of Microsoft Word.

7315.98 -> We actually have to trigger it ourselves.

7319.962 -> Go ahead and find the Review tab here

7321.378 -> at the top of the Ribbon and on the left-hand side

7323.805 -> you'll see the Spell Check tool.

7326.951 -> To trigger the spell check simply click the button.

7329.958 -> Now of course you can also see as I hover over it

7332.988 -> that the F7 key will also trigger spell check.

7337.714 -> So I'm gonna give it a click here,

7340.581 -> and the first thing it finds is here Englih muffins.

7344.378 -> Oops, but it gives me the appropriate suggestion

7348.07 -> did you mean English?

7348.975 -> Yes I did.

7350.903 -> So I'm going to select Change.

7354.803 -> Now interesting, notice that it says,

7356.603 -> do you want to continue checking

7357.961 -> at the beginning of the sheet?

7360.759 -> You might not have noticed it before,

7362.106 -> but my cursor placement was right here.

7366.994 -> It should have caught Coffe, shouldn't it have?

7371.255 -> However, because my cursor was below that value

7373.925 -> when I started the spell check it started

7376.34 -> spell checking everything from here down.

7380.74 -> It didn't check anything above it.

7383.236 -> Fortunately, Excel does have the ability

7385.21 -> to check from the beginning of the sheet

7386.847 -> once it's checked everything else.

7389.401 -> So I'm gonna go ahead and click yes.

7392.269 -> And now it catches Coffe, suggestions Coffee,

7396.901 -> I'm gonna select Change, and there I am.

7401.429 -> My spell check is now complete.

7404.007 -> Go ahead and spell check your own document,

7406.143 -> and then come back.

7408.082 -> Now that we've spell checked the document,

7410.044 -> it's time to set things up for if we're going

7412.412 -> to print this document.

7415.21 -> Now print tools aren't necessarily exactly

7417.672 -> where you'd expect them to be.

7419.517 -> Many of us anticipate that when we go to

7421.236 -> File and Print, we'll see everything we need to see here,

7425.16 -> but that's not entirely accurate.

7428.446 -> In fact, we're gonna need to make sure

7430.001 -> that set some print settings ahead of time

7432.822 -> using the Page Setup section of our Page Layout tab.

7437.93 -> Now you do have access to the Page Setup

7439.892 -> in a limited capacity from the Print Preview menu.

7443.619 -> But it's important to note that this is the only way

7445.999 -> to gain access to all the tools available

7448.739 -> is through this setup process.

7453.232 -> So find Page Setup and in the bottom right corner

7455.984 -> you're gonna see a little pop out button,

7459.244 -> give that a click.

7461.535 -> And this is going to give you the full Page Setup view.

7465.25 -> This is where we can control the page orientation

7467.586 -> whether we're printing in portrait or landscape,

7470.791 -> we can also dictate how it scales.

7474.401 -> Do we want it to be real size?

7478.058 -> Or do we want to fit it to a specific page?

7481.135 -> If we say fit to one page by one page,

7484.293 -> it will only fit this entire worksheet onto one page

7487.674 -> no matter what even if that means

7490.042 -> shrinking it down to an unreadable size.

7492.956 -> So do keep that in mind.

7496.01 -> Margins, of course we can control

7497.728 -> the margins if we'd like to.

7499.446 -> If you need a little bit of extra space

7500.805 -> on the left for punching holes in to put into a binder.

7506.436 -> Or maybe a little bit more at the top for notes.

7508.409 -> This is where you can input that there.

7510.708 -> Of course throughout this entire process

7512.194 -> by selecting Print Preview you can get an idea

7514.969 -> of what this will look like.

7519.981 -> We also have access to headers and footers,

7521.874 -> if you'd like to put a date down at the bottom.

7525.612 -> Maybe you'd like to put the location

7527.574 -> or name of the worksheet, the name of your company,

7531.963 -> how many pages there are.

7533.902 -> All these options are available inside

7535.411 -> the header and footer options.

7539.567 -> And lastly, the Sheet the most important part.

7545.024 -> Now, let's say for example we don't necessarily

7548.112 -> want everything to be printed we just want a select area.

7552.675 -> For example, let's say we only want to print the notes.

7558.341 -> Well by clicking inside the print area

7560.883 -> I can actually highlight just the notes field.

7566.723 -> And with that having been highlighted

7568.778 -> I'm gonna go ahead and click Print Preview

7571.878 -> and check it out, that's all that's being printed.

7575.93 -> This is called Set Print Area,

7580.168 -> you can also find the Print Area button here,

7582.884 -> in the Page Layout tab.

7587.807 -> To set the print area from here,

7589.606 -> simply click on the drop down, click Clear Print Area

7593.507 -> to remove any existing print area fields,

7596.642 -> and let's say I'd like to print just this.

7599.637 -> I'll highlight all of it, Set Print Area,

7604.398 -> and now by going to File, Print,

7608.496 -> you'll see only the selected area now is being printed.

7616.089 -> To clear your print area simply click on the drop down

7618.388 -> and select Clear Print Area, once again,

7622.335 -> and now if I'd like to do anything else more with this,

7627.095 -> let's say for example I'd like to see

7629.185 -> what the page view will look like,

7631.936 -> I can go to the View tab and I can use

7634.653 -> any of these workbook views either to view

7637.637 -> specific page breaks, meaning I'll see any individual

7642.641 -> page separation, in this case here because I've got it set

7646.298 -> to fit to a single page, I'm only seeing page one.

7652.776 -> I can also see it in Page Layout,

7655.702 -> this is what many people consider

7657.095 -> to be actual print preview, because it actually shows

7660.694 -> the page as it will show when it prints.

7666.314 -> So there's a lot of control over what we can do here,

7669.065 -> but it all starts with knowing

7670.249 -> that these tools are available.

7672.711 -> Go ahead and take this opportunity

7673.895 -> to try these options out for yourself.

7677.227 -> Remember, to set any of the page setup options

7681.035 -> we're going to be inside Page Layout.

7683.218 -> Where we can control the margins,

7685.215 -> orientation of the page, how big the page is.

7689.975 -> For my Excel veterans if you prefer that pop out box

7692.436 -> we were using select that pop out in the bottom right corner

7695.942 -> of the Page Setup command group,

7699.344 -> and you'll get the full set of options available.

7705.509 -> Go ahead and try that for yourself and then come back.

Source: https://www.youtube.com/watch?v=tuk99Sgc6Fw