Ready to watch this entire course?
Become a member and get unlimited access to the entire skills library of over 4,900 courses, including more Business and personalized recommendations.Start Your Free Trial Now
- View Offline
- Using the Slicer feature for dynamic PivotTable filtering
- Sharing workbooks via e-mail, the Excel Web App, and SharePoint
- Using Paste Preview for more effecient copying and pasting
- Inserting Sparklines to see patterns in data
- Taking advantage of enhancements to the Conditional Formatting feature
- Analyzing data from multiple sources using the PowerPivot for Excel add-in
- Maintaining file compatibility with older versions
Skill Level Appropriate for all
In addition to editing worksheets in Excel, you can also edit your worksheets in a web browser using the free web-enabled version of Excel. Word, PowerPoint, and OneNote also have web versions. So you might be asking yourself a couple of questions. Number one, why did I buy Excel if I can use it for free over the web? Number two, why would I even want to do this? Well, the answer is that you might want to share your work with someone who doesn't have Excel on their computer, or maybe you'll be on the road without your own computer, but you'll have Internet access, and you want to be able to modify your worksheets.
Also, the web version of Excel does about 10% of what the desktop version does. So it's not a complete substitute. Well, right now, I'm logged into Windows Live, into the SkyDrive application. We did this in Chapter 2. I created this folder called Two Trees where we're putting some of our files for the fictitious Two Trees Olive Oil Company. So let's just click that. It's a new folder, so there is nothing in there yet. You can see it's empty. Well, it won't be empty for long. Let's go to Excel.
If you're following along in the Exercise Files, you can open up 06_01_web app. It's in the Chapter 6 folder. Before we do anything to this file, let's immediately put it up in SkyDrive. So click the File tab, go down here to Save & Send > Save to Web, and there is the Two Trees folder that we just saw. So you can double-click it. Now, you can see up here in the title bar, we're in the Two Trees folder. Yes, it's empty. I'm going to keep the same File name.
So I'll just click Save. You can see down there it's uploading to the server. Now we don't want any conflicts between what's happening in the desktop version of Excel, and what's happening in the web version of Excel, So I'm just going to close this file here. Let's go back into the web browser. Now, you see it's empty just because we haven't refreshed. You can click the Refresh button or press the F5 key - will refresh any web browser. There it is. There is the file that we just saved. So click it.
Let's go up here, and click Edit. You see it opens up with part of a Ribbon bar. We can scroll down and see everything that's in there. So let's go and modify a few things. We're not just looking at it. We can actually do some editing. Well, let's say we want to do some math here. We have Hours, and we have Rate. Let's say we want to multiply them, and get a Total. So we can just click here in F4. Well, one thing you may notice when you're looking at the Ribbon bar is there is no AutoSum tool.
There is no Format Painter. So some of the stuff we have to do manually. Let me show you a couple of ways of doing this. First here in F4, let's just type the word Total, and press Enter. Now, we want to format it, so click on any of these column headers. Copy to the Clipboard. I'll just press Ctrl+C, click on Total, and we're just going to paste formatting. So you can click on this Paste here, and choose Paste Formatting. There it is. We can just press the Escape key. Now, that's one way of doing it, but there is a way that I think is little faster.
So I'm just going to press Ctrl +Z a couple of times to Undo. I'm going to click any of these headers, doesn't really matter which. I'm going to Ctrl+C, Copy to Clipboard, click over here in F4, Ctrl+V to paste. Now, I don't need two Rate columns. So I'm just going to go up here, and I'm going to change this to Total, and Enter. So I've kind of done the formatting, and the typing sort of in one step. It's up to you how you want to do it. Well, we want to calculate Hours times Rate, so we have to do this manually. So using F, we can say equals Hours, times, Rate, press Enter, and scroll down.
You might think to yourself oh boy! I've got all the stuff here. There is no AutoFill. I've got to do all of the stuff manually. Well, there is an easy way, if we know what some of the shortcuts are. Let' click this first one here that we just did. I'll press Ctrl+C. Copy to the Clipboard. Click the cell right underneath it. By the way, this is going to work in regular Excel also, and not just the web-enabled version. Scroll down, and Shift+Click the last one here and scroll back up, and now Paste. I'm just going to press Ctrl+V. Now, we have everything pasted.
I'll just hit Escape. Here is the original one. You can see there is the formula. Each one is now pasted correctly. So even though you don't have AutoFill and you don't have the AutoSum tools, if you know what some of the shortcuts are, it'll help you out. There we are down to the bottom. Well, let's do a couple of other things. Let's go over here to the Department column. Let's say we want to sort this. So let's go up here to Sort & Filter as Table. Let's click that. Yes, it confirms that's the area where we have the data, and it recognizes that we have headers. So click OK.
If you need to, you can stretch out the columns. So this looks very much like Excel, doesn't it, with our tables in there. So let's say we're going to sort by Department, and we can sort in ascending, or maybe we want to go to State, and Sort Descending, all the stuff that you normally do you can do here. Well, let's do some filtering while we're at it. Let's go to Department, and go down here to Filter. I'm going to deselect all of them. Let's say I want to see Finance, HR and Sales, just those three. Click OK.
Now, we see just HR, Finance, and Sales, and maybe let's sort this in ascending order while we're at it. Maybe I want to see only those people in those departments who are in maybe New Jersey or Pennsylvania. I'll click State, and I'll Filter, and I'll get rid of this. Let's say we'll do New Jersey and Pennsylvania. Click OK. So now we can see only those people who are in the Finance or HR or Sales departments, and are also in New Jersey or Pennsylvania.
Well, that's great, but what if we want to bring this back into Excel? Well, you notice there is no Save button, there is no Close button, and that's because Excel autosaves all the time. To close it, what you really do is you just click back on the folder name here. So that's kind of closed. Well, let's go back to Excel. Let's go up to Backstage View, click the File tab, and go to the Recent section. And you see here is the file that we were just working on, on the web with this very long file name.
Now, on your computer, it's probably going to be a different file name, but it's going to be a long one, nonetheless. Click it. Now we are opening it from the web. You can see, down here, it's probably telling you that it's downloading. There it is, filtered just the way we had it before. Again, in real life, we wouldn't be flipping back and forth like this. We might be two different people. This might be two different times, day one, day two. We're just kind of wearing two different hats here. Well, I'm going to un-filter this. So I'm going to clear that filter. I'm going to go here, and clear that filter.
But what I do want is I want a total of the Hours, and I want a total of the Total. So let's go down here. I'll type Total. I'll strike a Total. I'll just press Alt+Equal, the shortcut for Sum. Go over here and again Alt+Equal is the shortcut for the Sum, and maybe let's bold it while we're at it. Okay, and let's go up here. Let's check that Payroll. We'll bold it, as well. Okay, that's all we need. Now, this is the regular desktop version of Excel, so we do have to manually save and close.
So we'll just Ctrl+S to Save, and you see it's uploading to the server here. When it's done, Ctrl+F4 to close it. Now, let's go back to the web. There is a file. We were just editing that in Excel. Click that. Edit. Here it is. You see the Payroll is bolded. It's all unfiltered. There is the Total row that we just created. So the Excel web app is really great. It's not a substitute for Excel, but if you just need to do a few things, or you need to share your files with a few other people on the web, this is a really great way to do it. And hey! It's free. So check it out.