In this video, Sharon shows you how to create and save a workbook, enter and edit data, apply a number format, insert a chart, and use AutoSum and Quick Analysis.
- [Instructor] Excel is the power horse of Office 365. It's been the spreadsheet of choice for as long as I can remember. When we need to crunch numbers, Excel is our go to application. As with Word and PowerPoint, there two versions of Excel that you can leverage. The online version and the desktop version. For our demonstration today we're going to go ahead and work with the desktop version. I happen to have Excel pinned to my start menu and I will launch it from here. Your setup may be slightly different.
As with most Office 365 applications, we have the option to open a recent document, we can create a blank document or in our case here a workbook or we can use one of the many templates. Today will be going head and using a blank workbook. Our spreadsheet today will have our guest rooms and the rate per day of the week. I'm going to go ahead and type in Guest Room and then I'd like that column to automatically fit the term Guest Room, therefore I'm just going to double click in between the A and the B.
And we'll populate our list of guest rooms right below this. Next, I'm going to provide the title. Apparently, I have a typo, therefore I'm going to go ahead and fix that. And now I'm going to start entering in the days of the week. It gets a little boring and monotonous. I'm going to go ahead, highlight Monday, Tuesday, and then drag the cursor right across until I have Sunday. Perfect! Next I can go ahead and enter in the guest room names and the values.
I already have this copied to make a little bit easier and faster for our demo. I'm going to go ahead and just do a paste. And you'll notice that we're missing Sunday. I'm going to go ahead and enter in some values for Sunday. Now please note these are made up values. I do not know if these are true representations of hotel rooms within these cities, on these days. Let's go ahead and add a little bit of formatting to this. Let's bold our Guest Room and the days of the week, as well as the guest rooms themselves, perfect.
Now if we did not have rates or US dollars in the spreadsheet we wouldn't know what these values actually are. Let's go ahead and change these to currency. So I've highlighted all the cells that I'd like to change the formatting of. I'm going to go ahead and just right click and click on Format Cells. And you'll notice that we have a variety of options to choose from. In our case here it will be currency and I really don't want decimal places. Going to go ahead and click OK. Perfect! That wasn't so bad.
Let's go ahead and add up the values for the week for all of those rooms. Now I'm going to go ahead and skip a column here and you'll see why shortly. Let's go ahead and do sum. In Excel we have an autosum function. I can go ahead and answer the formula in manually, but why, when Excel provides it to me. I'm going to just go ahead, click Autosum, and you'll notice it recognized all the values and grouped those together for me. Now, in this example I actually would like to back up one.
I do not want to include I. I'm going to pop out and just go to H. Perfect. I could do the same procedure for the additional rows, but in this case I'm just going to go ahead, highlight and drag. Done. You'll notice it was very easy to add up the total value of each room for the week. There's so many other formulas we can use within Excel as well. There is an average formula that is available to you, but I'm going to show you how we could do this manually. I've added in the title Average.
I'm in the cell. I want to start off with an equal sign. That lets Excel know that this is a calculation. I'm going to take our sum, which happens to be in J3 and we're going to divide that by seven, which happens to be the number of days in a week. There we go. So the average room rate is $208.57 per week. As before, I'm just going to go ahead and drag that down. Perfect. Our chart is okay, but let's see what else we can do using Quick Analysis.
What we can do is select our table, and you'll notice as soon as I do that we have a Quick Analysis icon available to us, and this will do some handy dandy formatting for us. Let's start with formatting. We can put in data bars, colors, icon sets, greater thans. In our example let's go ahead and just add data bars. Perfect. We can also add in some spark lines and this is why I left the I column blank. I'm going to add those in, see we have some sparklines.
That shows us the lows and the highs from that table. And finally let's go ahead and add in a chart. To do so, click on Insert and we're going to go ahead and add in a Column or Bar Chart. And we'll just use a plain old boring 2D. Next, we can go ahead and select the data and then click OK. And now we have a chart.
In our chart, you'll notice if I change our Family Friendly room on Sunday, let's say down to $100, that will be represented in our chart as well. This was a really quick overview of Excel. We barely scratched the surface of everything you can do. The Excel Training Center has tips and tricks and other resources to help you get the most out of Excel.
- Training users
- Troubleshooting account and startup issues
- Repairing Office apps
- Working with Word, PowerPoint, Excel, Outlook, Visio, and OneNote
- Communicating with Skype and Yammer
- Using OneDrive and SharePoint
- Fixing file synchronization issues