Create worksheets and workbooks

- [Instructor] Creating Worksheets and Workbooks are basic Excel concepts, but it's really important for us to review any possible task that you may come across on the Excel 2016 MOS Exam. So we're going to spend a little bit of time talking about how to quickly create new workbooks, and how to copy information from one workbook to another. In this video we'll create a new workbook from a text file. Did you know that you can just open a text file in Excel and it'll create a workbook and import the data all at the same time? It's true. Then we'll look at how to insert blank worksheets, how to create a blank workbook, and I'll show you a great keyboard shortcut you can use to complete that task. We'll create a new workbook from a template, copy worksheet information from one workbook to another, and import data from a text file. Did you notice that a couple of the topics have the MOS icon next to them? Anytime you see this during the introduction, know that it's very likely that you'll see that topic on the MOS exam so pay close attention to anything that has the icon next to it. I have Excel open so let's go ahead and get started. First, we're going to create a new workbook from a text file. I'll go to File, and Open. I'm going to browse to my Chapter 2 folder where I have all of my exercise files saved for this chapter. I'm currently seeing only the Excel files, and I need to go to the lower right hand corner and change the document type from Just Excel Files to All Files and magically all of my Text files appear. I'm going to go ahead and use the Customerlist to create a new workbook. I'll double-click on it, brings me into this great Text Import Wizard, and I'm just going to walk quickly through these steps. It is a delimited file. During exam they will actually tell you how the file is delimited, whether it's delimited with a tab, or a comma, or a space. So just pay close attention to the instructions that you're given. It's a delimited file. My data does have headers. I'll go ahead and click Next. It is a Tab delimited file so I'll leave Tab selected, but your other choices are Semicolon, Comma, and a Space. I'll go ahead and click on Next. That looks good. I'll just click on Finish. That quickly a new workbook has been created called Customerlist and all of our information has been imported into a new worksheet called Customerlist. Great, how easy was that? Super easy. Let's go ahead and close this file. I'll go to File and Close and that one's gone. What if we wanted to create a just a blank workbook? Control + N for New on your keyboard will do just that for you. If we wanted to create a new workbook based on a template, we would go to File and New and we see dozens of templates available to us. On the exam, they will give you the name of the document that they want you to create. For instance, if they ask you to create a cash flow analysis workbook, just type that name right in the search field, hit Enter. There's the Cashflow analysis template. All we have to do is double-click on it, and it opens right in Excel very quick. I'm going to close this document. I'll go to File and Close. Just close that, I'm not going to worry about saving my changes. Perfect. Let's go ahead and close the new document that I created also using that great keyboard shortcut, Control + N. Let's look at how we can copy information between workbooks. I'm going to go to File and Open and navigate back to my Chapter 2 folder. For this first video, we have two files we'll be using. The Commission file and the Create Workbooks file. If I hold down my Shift key and click on both of them, I can select them both at the same time and then click Open, and both of the documents will open for me. A little time saver for you. If I go to View and Switch Windows, this is where I can just verify that they both did indeed open for me, I'm going to go to Arrange All, and let's have them arranged vertically. Now I can see them side-by-side. What I want to do is copy the 2016 Commission into my other workbook. I'm going to show you the easy way first, and then I'll show you another way that you can do it. I'm going to click on the 2016 Commission worksheet tab, hold down my Control key. Notice that the icon that's attached to my mouse cursor arrow has a plus sign on it. That's telling me that I am actually creating a copy of that 2016 worksheet and putting it into my other workbook just by dragging and dropping it. I'll go ahead and let go of my mouse, and let go of my Control key and here's my Commissions worksheet. Much easier than the old-fashioned way of going back to my commission workbook, right clicking and choosing Move or Copy, letting Excel know that I want it to go into the Create Workbooks workbook, putting it before the Disclaimer, and making sure that I create a copy of it, and click OK. That's way to many clicks. So if you have the opportunity just to do a Control drag and drop from one workbook to another, it's much easier and will save you a lot of time. The last thing we want to talk about is how to import information into an existing workbook. I'm going to go ahead and close the Commission workbook. I'll go ahead and close the Create Workbooks workbook also. Save my changes. I'm going to go ahead and maximize my window, go to File and Open and browse back to the Chapter 2 folder, back to Create Workbooks. Let's click on our Merchandise worksheet. I want the information to go into Cell A6, so I'll start right there. Click on my Data tab, go to Get External Data, and select text file. Here's my Merchandise text file. I'll just double-click to get into that text import wizard. Once again, it's a delimited file. I do have headers. Item, Photo, Category, Size. Click Next, it is separated with Tabs. Everything looks good. I'll go ahead and click Next. Looks fine. Let's do Finish. This last window is asking me to confirm that I do want it to go into an existing worksheet in cell A6. I do, but I also want you to see that you can create a brand new worksheet, just by clicking this radial button. Let's leave it selected to go right into A6 and click OK. There's our imported information. So now you know several ways that you can either import information, copy information from another workbook, or just quickly create a new workbook using Control + N, or from a template. I feel it's important to review even the basic Excel concepts so you'll know everything you can expect to see on the MOS exam. Just be prepared for everything from the very basic, to more advanced tasks and you'll be ready to conquer the MOS exam.