Excel, Power BI, and Access

Training: Excel

Your browser does not support video. Install Microsoft Silverlight, Adobe Flash Player, or Internet Explorer 9.

Develop your talent with more than 10,000 online courses from LinkedIn Learning

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 we'll 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 it 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 a sum. In Excel we have an autosum function. I can go ahead and enter 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.

LinkedIn Learning

LinkedIn Learning is an online learning platform that combines industry-leading content from Lynda.com with LinkedIn’s professional network of more than 500 million member profiles to provide highly personalized course recommendations and a more intuitive learning experience. Learn more.


  • Learn from recognized industry experts, and get the business, tech, and creative skills that are most in demand.

  • Receive personal recommendations based on your LinkedIn profile.

  • Stream courses from your computer or mobile device.

  • Take courses for every level – beginner to advanced.

  • Practice while you learn with quizzes, exercise files, and coding windows.

  • Provide learning for your team or entire organization, with an easy to use experience for managing users, curating content and measuring engagement

For businesses with 150+ licenses Request Office 365 onboarding assistance from FastTrack

You can request remote and personalized assistance with onboarding. Our FastTrack engineers will help you plan your Office 365 project, assess your technical environment, provide remediation guidance, and provide user adoption assistance. For businesses with at least 500 licenses, Microsoft also provides personalized assistance to migrate data to Office 365.

See the FastTrack Center Video: http://aka.ms/meetfasttrack

Get started today: http://fasttrack.microsoft.com

Tip: Businesses with 1-149 licenses still have access to FastTrack guidance via links in the Admin Center and also available at https://aka.ms/setupguidance.

Transitioning your company to Office 365? This course provides Level 1 IT administrators with an overview of the most common end-user support requests related to Office 365. Get a quick recap of Office basics, including the features of Word, Excel, PowerPoint, Outlook, Skype, OneDrive, SharePoint, OneNote, Access, Delve, Yammer, Publisher, Sway, and Power BI. Find out how to troubleshoot common account and permissions issues and repair broken apps. Plus, learn how to make sure files are synced correctly between a user's desktop and the cloud. Microsoft solutions expert Sharon Bennett will make sure you have the answers to your users' most frequent questions—before they even ask.

Topics include:

  • 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

Expand your Office skills
Explore training
Get new features first
Join Office Insiders

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.