Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
With Word 2010, you are not limited to tables that you can create using the Table tools in Word. You can also insert part of an Excel Spreadsheet directly in your document. Now if that spreadsheet already exists, you'll do that using Copy and Paste. But I actually am referring to times that you want to use the power of Excel here in Word, when you don't already have an existing spreadsheet. And I want to show you an example of why you might want to do that. We've added some additional data to our list of Retail Store Locations.
We have their 2009 Sales over here in a column on the right. We also have the number of employees, and this kind of data often begs us to total of the data. It's a question that anybody might logically ask. How many total employees do you have at your retail locations, or what were the retail store sales in 2009? So I'm just going to press Tab so that we can add some totals, and I'm going to go to the Employees column. And on the Layout tab, there is a Formula button. I can perform a simple calculation here.
I can average, I can count the number of numbers, or I can sum them. I can set a particular number format for this cell. And here is my list of functions, very, very short. I'm going to do a Sum and tell it OK. And we will do that again to be able to sum our 2009 Sales. And let's right-align this entire column. So far so good. It looks great, but there's one problem. This formula result was created when I inserted the formula here and here.
If these numbers change, for example, if there is suddenly an explosion in the San Jose store, we have more sales than we know what to do with and we add more staff, watch this number because it's not going to change. So it's problematic if anybody could change this data. Most users would assume that the totals would change, but they won't. The only way I'll get that total to change is by going in and entering the formula again. This can easily lead to documents that have inaccurate data in them.
So if I'm going to create calculations in an Excel worksheet, I am actually going to do that using Microsoft Excel. Let's see how easy that is to do. I can Insert > Table and rather than choosing Insert or Draw, as we've done before, I'm going to insert a small Excel Spreadsheet. You don't need to necessarily worry about how big this is yet, but I'm going to drag it over so that it's roughly the size of the width of my page. Now I'm going to go back into my Word document, because I'm really in Excel here.
If you look at the Ribbon, these are all Excel tools that I'm seeing. I am going to click back into my Word document. This is the kind of thing, by the way, that you don't want to do when you have 50,000 other things running. And I'm going to select my entire table and copy it. I can copy it to the Windows Clipboard, and it'll go there, but I actually like to open Office Clipboard for something like this, and I am actually going to select this, and I'm going to copy it to make sure that it goes here to the Clipboard. Now I'm going to click back. Actuall,y I've selected the object.
You'll notice that it has handles around it, but I'm still here in Word. I'm going to double-click in his Excel spreadsheet table, and it will open up the spreadsheet, and I'll know I am back in Excel, because I'll have my Excel tools, or at least tools that I'm not familiar with from Word back again. And I'm going to paste this data. Now I have some choices. I can Match the Destination Formatting, which would be the Word Theme, or I can keep my Source Formatting. I'd like it to look a lot like it looks here. So I'm going to paste, keeping my Source Formatting, and there are my seven columns of data.
Now I'd like to make this box a little bit bigger, so I'm going to scroll down. That sort of stutter step that you see Excel taking isn't that unusual. Word's actually dealing with two programs at one time, and it struggles just a little bit to accurately refresh the screen as quickly as it should. I'm going to use now the tools that I know from Excel to double-click and space these columns. Despite the fact that I asked to have my Source Formatting, this is about the best that it's going to be.
My object, my Excel spreadsheet runs off the side of the page now, so I am just going to pull this back a little bit to get it back in bounds. There we go. And now I am going to add some formulas. So I have all the choices that I would have in Excel. If you're comfortable using the Excel Table tools, you can do Format as Table here and use a table row. I'm going to assume that you perhaps simply want to enter a formula. So we're going to enter the word Total here, and I'm going to insert an AutoSum and press Enter.
I'm going to do that again and press Enter again. Now when this number changes, when the San Jose store has 12 people, you'll notice that my Total changes automatically. I can do some basic formatting in here. Again, I'm using the tools that are here in Microsoft Excel. So I'll need to know that I can choose my borders here and remove all my borders. Or I can select all of this data and apply a border, or apply shading.
I'm actually going to apply some shading here. This is a fill bucket in the Font group. I'll apply some light shading here and across the top. If I wish then, I could apply a grid border right here to this section. I want to make sure this table looks good in Excel. We know that it works, because we checked it out already. Here is some centering that I want to do on these columns. All the rest of this looks good. Then I am going to click back into my Word document.
Now, I have a lot more Excel showing than I need to show here. I can adjust this, but if I do all I'm doing is making my text smaller. If I want to change the shape of this, I actually need to do it while the Excel window is engaged. So if I want to remove column H, for example, I want to do that right here. And if I want to remove the extra rows, I am going to do it here. Notice as I hold my mouse button down there is a dark border that shows me approximately where it will cut off.
Click and now I have my Excel table full size here in Microsoft Word. Now that I have the data I want, I no longer need this table, and I can right-click and Delete the table, and keep this table that works with Microsoft Excel. Anytime I want to update this table, for example, if I get a new report on what the actual sales were here, I can edit using my familiar Excel tools, and my Totals will automatically update. Anytime I want to do a calculation in a Microsoft Word document, I immediately reach for a spreadsheet in Microsoft Excel, because I know that every time that this document is updated, that my Totals will update as well.
Get unlimited access to all courses for just $25/month.Become a member
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.
Your file was successfully uploaded.