Quantcast

Start with 7 free days of training.

Gain instant access to our entire IT training library, free for your first week.
Train anytime on your desktop, tablet, or mobile devices.

This Microsoft video training course with Chris Ward covers all the important features of Microsoft Excel 2013 such as creating spreadsheets and using data. It also prepares students to take the 77-420 Microsoft Excel Office Specialist exam....
This Microsoft video training course with Chris Ward covers all the important features of Microsoft Excel 2013 such as creating spreadsheets and using data. It also prepares students to take the 77-420 Microsoft Excel Office Specialist exam.

Related area of expertise:
  • Office productivity

Recommended skills:
  • Basic keyboarding and computing skills
  • Understanding of spreadsheets
  • Basic math (addition, subtraction, multiplication, division)


Recommended equipment:
  • PC running Windows 7 or Windows 8
  • Office 2013


Related certifications:
  • Microsoft Office Specialist (MOS)


Related job functions:
  • Students
  • Office worker
  • Tech worker
  • Management
  • Accounting
  • Database and systems analysis


Excel is probably the most powerful of all the applications in the Microsoft Office Suite. In this course, you will learn how to use this powerful software to create spreadsheets, charts, and manipulate data.

While there may be specialty software that uses spreadsheets, the world runs on Excel. This course will put you in the game, helping you to understand the ins and outs, even a few tricks that will help speed up your production!
 show less
1. What's new in Office 2013 (21 min)
2. Installing Office 2013 (17 min)
3. Workspace Fundamentals (37 min)
4. Data Entry - Text, Numbers, Dates (27 min)
5. Cell Formatting (26 min)
6. Advanced Cell Formatting (29 min)
7. Rows, Columns, and Worksheets (25 min)
8. Print Area and Page Setup (25 min)
9. Basic Database Functionality (28 min)
10. Graphics and Charts (33 min)
11. PivotTables (32 min)
12. Advanced Charts and Graphics (25 min)
13. Formulas and Functions - Basics (26 min)
14. Formulas and Functions - Formatting (27 min)
15. Formulas and Functions - Advanced Conditional and Financial Functions (30 min)
16. Data Summarization and Outlines (24 min)
17. What If Analysis (34 min)
18. Securing Data (23 min)
19. Exam Prep #1 -Data Validation, Macros, Formulas (28 min)
20. Exam Cram #2 - Test Simulation (17 min)

What's new in Office 2013

00:00:00

Hello, everyone. Welcome to the CBT Nugget series for Excel 2013 as we kick things off by taking a look at what's new in Office 2013. So what is new in Office 2013? I'm sure a lot of you are pretty excited to see some of the new features-- some of the new things that we have available to us.

00:00:20

And one thing, of course, that I think is kind of cool is SharePoint and SQL integration. We'll tell you why here in just a second. Active Directory activation. And of course, Apps for Office. Well those are the first three that we'll take a look at right now.

00:00:35

And probably, the one that I know for a lot of my friends who are really big in to Access is that the SharePoint and SQL integration now means that when you run Access 2013 basically, it is an app that runs with SharePoint. So it's basically like a web app that runs in a browser utilizing SharePoint as it's kind of back in-- for at least the tools.

00:01:06

Now the cool thing is, is that also now accesses the database through SQL 2012. So if you create the app in a SharePoint Server that the company has, Access is going to create the database in the SQL Server 2012 installation-- that you, obviously, are integrating anyway with SharePoint.

00:01:29

I mean, that's one of the big things with SharePoint 2013. And so, in fact, check out Tim Warner's series on SharePoint 2013. Absolutely incredible, and it'll give you a little bit more idea. But those of you who are more like, oh. It used to be Access was kind of the orphaned stepchild of the Office products, because everybody was like it, I mean if you really want to use a database you want to use SQL.

00:01:52

Well now access 2013 can become that web app that you use in a browser and have access to all of your database objects, the data, the tables, the queries, the macros, the forms, everything else is going to be accessible to you through that. Which is pretty stinking sweet.

00:02:11

Active Directory activation. Now a lot of people were thinking I was going to probably say at this point Active Directory integration. Well here's the thing. Active Directory activation is actually a pretty nifty thing. Now for those of you who are the IT directors or anything, remember we have that thing called VL, right?

00:02:31

Volume Licensing. Now, I'm going to tell you why this is cool for both you as an IT admin, and you as an end user. Volume Licensing as an IT admin when you have 800 to 1,000 employees and you need to roll out your new versions of Office-- like office 2010, and office 2013-- it used to be with the Microsoft policy you had to activate those Volume License additions.

00:03:03

And that was both on a physical computer, or whether it was running in a virtual machine. Well now with Active Directory activation, anytime a user logs in to a domain-- so in this case maybe it's MrCBTNuggets.com or as we always say my AcmeMusicalInstruments.com-- and the user logs in to the domain, guess what?

00:03:31

There is a Group Policy object out there that's going to say, yup, this person has access to Office 2013. Boom, installation. Boom, it is activated. Boom, you're done. So the Office 2013 Volume License clients can automatically activate against the domain.

00:03:47

However, there are some-- the quid pro quos, and the little et cetera, et cetera-- additions to that. It has to be Windows 8, or Server 2012. Those are the two machines that it can allow you to do that. So if you're using that-- and that would of course be a client computer running Server 2012-- you would need that.

00:04:14

Very cool feature. Obviously, it takes a lot of work away from the, I would say, the IT admin guy, because now he just puts in a Group Policy object. Boom, you're done. For the end user you get a brand new Windows 8 machine. You log in to the domain.

00:04:30

The IT admin doesn't need to worry about whether you are [INAUDIBLE] because he's already put your username-- or you as a user, I should say-- in to a group. That group then has the Group Policy activated to it against the domain. And you will then get Office 2013, and it will automatically activate for that, which I think is pretty dang stinking cool.

00:04:51

Now we also have what are called Apps for Office. Apps for Office. Now Apps for Office is basically you can use and I know this sounds kind of strange you can create new applications. So this would be like a new application, and it actually integrates and works with Office 2013.

00:05:21

And the way that you can do this is you can use things like HTML5. You can use XML, of course, because that's what we're using anything else. You can use CSS3. You can use JavaScript, JScript. And even, and I had to check this but it's true, Rest API's.

00:05:46

Now for those of you guys that are programming, and you're thinking, wow, that's kind of cool. So think about this. Now for an end user you're like, what does this mean. Basically, an App for Office is a web page that's inside a client application. So, for example, if I have Word 2013 running, and I build an app for it.

00:06:13

And within that application Word 2013 you can add a Translation app that has been written that is a web accessible. In other words, you can go out and access this through the web, it brings it in to the interface, and they work together. And by the way, this can be out on a catalog that you can locate-- yeah you've got it-- out here through SharePoint, or out on the web somewhere.

00:06:43

So again, this is kind of a cool thing that you can use, Apps for Office. And by the way, and you notice all of these are very common-- and open source of most of them-- type of environments. So it's not like something like, it's only Microsoft stuff that can work with this.

00:07:01

It's not true. One of the best examples I've seen is, of course, Microsoft owns this, but Bing. Bing has the translation app, and they can use that as a task pane. So something really cool, and something I think most of you would really be interested in if you are more in the development side of things for your particular company.

00:07:23

So else is new in 2013 that we can get all excited about? Office Web Apps and Office Web App Server, which is pretty cool. SkyDrive Pro and Sign-In ID's. These are just three other things that I think are probably more relevant to most people as both end users and as IT admins.

00:07:42

Now some you-- especially if you're an end user-- you're thinking to yourself, OK, what's the difference between an App that you just talked about Chris, and these Office Web Apps. Well, Office Web Apps have been around for just a little bit, especially with the introduction of Office 365.

00:07:59

Office Web Apps basically allow you to have a browser-- you can use any supported web browser-- and you can use a would say kind of a stripped down version of Word, Excel, PowerPoint, and OneNote. So again, you can use Word, PowerPoint, Excel, and one of my favorites OneNote.

00:08:29

Check out my OneNote series, by the way. Or the Office 365, and you'll see I actually talk about Web Apps. And so web apps, basically, any supported web browser will work-- and by the way, that includes on mobile devices. Specifically. Windows Phone.

00:08:46

You can tell she's really excited about that. IPhones, I have it on mine, and I have in on my iPad, as well. So you can do that. Now understand, on the iPhone the screens a little small, so you're not going to be doing a tremendous amount of different things.

00:09:02

Basically, Office Web Apps used to be all about SharePoint. But now we actually have what is known as the Office Web Apps Server, which is a stand alone product. You can actually get this, and you don't have to have SharePoint running. Now, I would say, you definitely want to try to have the Office Web App Server running as well.

00:09:28

But for the most part, you don't absolutely need it. Now most people, I would say this, most people in a Microsoft shop are going to be running SharePoint. Especially SharePoint 2013, because I already showed you some really neat integration that happens.

00:09:43

But it is now unnecessary for those Web Apps to be running, because you now have the Office Web Apps Server. One last things on that is you could actually create an Office Web App Server farm, which can provide Office services to just about everybody.

00:10:00

Which is kind of cool. And also, of course, SharePoint 2013. Link 2013. Exchange 2013, if you're going to be using Outlook also somewhat important for doing that. Now SkyDrive Pro. SkyDrive Pro. What can we say about SkyDrive Pro? SkyDrive Pro, remember SkyDrive's been around for a little bit, and a lot of people have used it, but now you can take it to larger amounts of data, or documents, that are now enabled.

00:10:35

Users can take their documents, basically, in SkyDrive. Used to be you had to have that web connectivity. You now have offline access, which is nice. So let's say you take a document offline, you do some editing, as soon as you a link back in and sign back in up in to the cloud, it will then immediately synchronize-- we'll do a little lightning bolt here-- and it will synchronize that to your document.

00:11:06

And by the way, if you have your out Windows phone it will then automatically update that one. And then I've got my iPad over here and boom it will update that one, as well. So you now you can work from-- whether you're online or offline-- you can work on any devices.

00:11:22

Plus, now with the offline access what makes Pro even better, is if I've got Bob here, Sue, and Jeremy-- because I was have to throw Jeremy in to one of these things. So I've got Bob, Sue, and Jeremy. Jeremy's on an iPad. Sue's on her laptop. And Bob's on his Window's phone.

00:11:44

So you've got three people, three different devices. Guess what? We can all work on the same document at the same time offline or online, and the SkyDrive Pro Window's Sync Client will synchronize the changes between the users. And, obviously, as we've seen before in my previous Office products that are shown to you, if there are conflicts it will show both and say, which one do you want to use.

00:12:16

And it will inform the other users, hey, there's a conflict. Bob said he wanted it to say something, Jeremy said he wanted to say something else. And so SkyDrive Pro very, very cool. So you can share, collaborate documents, and I like to say, I like what they've done.

00:12:32

They've really improved SkyDrive. It used to be kind of one of those, it's all right. Apple was doing a little bit better job. That's my personal opinion. But now iCloud's good, but SkyDrive works really well. And, of course, it is integrated directly, because it actually has a Window's sync.

00:12:50

Or what they call SkyDrive Windows Sync application that runs in the background doing that. Sign-In ID's. Here's the thing, remember now, Office is more and more now web-enabled. Office 365. Office 2013, obviously, can be localized on your computer, but how do you sign-in.

00:13:11

Now you can assign in several different ways, because, again, now-- as I'm going to show you as soon as I talk about it I'll actually demonstrate what we mean by sign-in-- on this Sign-In ID's you can sign in with a Live ID. So you can use Microsoft's Live if you've got a Hotmail account.

00:13:33

If you've got Microsoft MSN-- the old MSN account now it's a Live account-- you can sign in that way. Or, get this, you can Sign-In with an Office 365 account. You can use this one, or this one. And the other cool thing is you can say, you know what?

00:13:55

I only want to allow people to sign in with this one. Or I only want people to sign in with this one. Or you can actually have it to where it completely locks it down, and say, no, you can't sign in by using either one of those IDs. And so, usually used in a locked down environment, especially if you've got that running and you don't want people to get out there and try to log in on the portal.

00:14:18

I mean, think about it, it's a web based portal to get in to that environment, and if you're using their access you might want to shut that down. So that's a little bit better security. So you can sign in with your Live ID, or Office 365. So the next thing I want to show you though is the actual new Start to the interface that we have here.

00:14:39

Just to kind of get you a little used to that. And show you what I mean by, especially, the Sign-In ID. So here we are out on my desktop. And I'm actually using Windows 8.1 on a Windows tablet. So one of the great things about this series is you're going to get to see some of the advantages now the Windows 8 provides with Office, and how office 2013 actually allows for touch laptops.

00:15:06

We'll show you that when we're installing in a subsequent Nugget here. But in this case, I went ahead and I created a couple of shortcuts on my desktop here for the three most used-- besides Outlook-- of the Office products. Word 2013, Excel 2013, and PowerPoint 2013.

00:15:25

Now part of the reason why I do this is because when you use either one of these, and a couple others, when you first open the product-- now, if I double-click on it, Excel loads up. And you'll notice that we get a Start page. I don't have what I used to have.

00:15:39

Remember, over here I would have the Office Button or the File Button and then the Ribbon would be across here. And then I'd have a blank document and that would be it, right. That's just typically how it would install. In this case though, this is the Start Page.

00:15:55

And the Start Pane gives you recently opened documents, or workbooks, in this case because we're in Excel. And you can, obviously, go and hunt for anything locally. You can also look for things on SkyDrive, which we mentioned, or SharePoint, which we'll also get a chance to see.

00:16:12

And then you're also given an option to-- if you want to start off with a nice blank workbook, and once you're done with this series, that will be no problem. You'll click on that and start wacking away and creating some really cool spreadsheets. You can take a tour, which is fun, and go ahead if you want to after you do this series.

00:16:29

Go through and they'll point out a few things maybe that I might not. And then Paypal invoicing, financial portfolio, all these things, you're like, what are these. These are the most popular of the templates that are used for Excel, at least according to Microsoft and most of the things.

00:16:44

Now the nice thing is is you don't have to use that. If you want to go and find something a little different, in this case, let's say I want to look up a budget template. I click on that, or you can actually type in whatever you want. And her you go.

00:16:57

You got a garden landscaping, a wedding budget. Congratulations for all of you are the engaged. Here's a nice wedding budget to help you keep yourself on the straight and narrow with your money. Home construction budget, boy, I could have used this about a six months ago.

00:17:11

So these are things that you can utilize, and, of course, notice you have categories. Or just do a search. There's also a lot of websites out there, some Microsoft Office user forums. Go look out there, where they're more than happy to say hey, here's one that I use.

00:17:25

Here's a spreadsheet that I use for this. And some of them are very job specific, and so that's a great thing to do. Just make sure, though, that they are utilizing these. And if you're in it a place it does not accept macros, or some of the scripting, you will need something that does not have that included as part of the template. so you've got to watch out for things like that.

00:17:45

So let's go back here. And so, that's what we do. We'd go in here, and pick one of those. Or create a new one. But what we're talking about is one of the new features. This right up here. And that is your Sign-In ID. Now the Sign-In ID that I have right here is-- typically, you are going to get one then is handed to you by your administrator, in this case, that's me.

00:18:07

And that's my Sign-In ID. And you'll also notice this is what we would call a Office 365 account. How do I know that? Well, because, the clue that will usually get you is, if you're not running with your own domain you will always have the dot on Microsoft dot com at the end of your domain that you're using.

00:18:26

In this case, Mr. CBT Nuggets. Now you've see this already if you've seen the Word 2013, or the Office 365. And that's kind of cool. But you also have the ability to not only use something that's like in an Office 365 environment, you could also use a SkyDrive account, or a Microsoft Live account, actually.

00:18:44

And the way that I do this, is just simply switch over to Account. So I will use, what many people know, I've had this for years. And this is where I have my SkyDrive account with. So I go ahead and click Next, it's going to say, all right, I'm going to look for that.

00:19:03

Oh, yeah that's a Microsoft account. That's right. And so, what I can do is now when I do this, and I sign-in it's going to say, all right sounds good. Uh, oh, we need to make sure that something unusual has happened, and we need to verify your account.

00:19:19

The reason why, just FYI, I'm actually recording this in the UK. So they're going, wait a minute, somebody is accessing your account from another country. And so, they're going to go ahead and then send the information based upon my text information here.

00:19:38

Now, I am going to go ahead and I'm not do this right now. Just because, well actually, I'll just pause. And I will put in the digits. And boom, I went ahead and had it go send me a secret code to my phone so I can sign-in. So here we go. You'll notice that with this that now I have a different Sign-In ID.

00:20:04

Now, what does this do for me. Well, when I get over here in to my Recent, or when I want to open up files, I can go to my SkyDrive, which is associated with this particular account-- my DJ Woody 28 at Hotmail.com. I had it forever. By the way, if you try to email me, I don't really check it too much.

00:20:22

And most of the time, everything gets flagged as spam anyway. So it's just kind of a nice account to keep for Microsoft. But if I also had a SharePoint account that's assigned with this, that would be different than the one that I had previously, which was, of course, my Chris.Ward@Mr.CBTNuggets.com one.

00:20:40

So the Sign-In ID really delineates what you are attaching to when it comes to the cloud. Are you attaching to your Office 365 account? Are you attaching to your Live ID, now most of the time they just call the Microsoft account, which is going to be really important when you're using Windows 8.

00:21:01

And so once you say, yup, that's what I want to do, and I'm done and I go ahead and I click Blank work. And bada bing bada boom, here we go. We're in to the interface. Now, we're going to take a real big look at this interface later on. But just to kind of give you an idea of what we have here, of course, they've really cleaned up the Ribbon.

00:21:22

It's a lot more streamlined. They have a lot more options here for you. You're going to get to utilize a few more features that we're going to talk about when we get in to it. But it's all good. Including, the one that I'm really happy about, and that is right here.

00:21:36

And that is Touch features. Touch features. You'll notice this on your Windows 8 and 8.1 tablets that have the ability for touch capability. So your touch tablets. And so, we'll showcase that to you a little bit later. Which is another really cool new feature in Office 2013.

00:21:54

Well I hope this has been informative for you. And I'd like to thank you for joining me.

Installing Office 2013

Workspace Fundamentals

Data Entry - Text, Numbers, Dates

Cell Formatting

Advanced Cell Formatting

Rows, Columns, and Worksheets

Print Area and Page Setup

Basic Database Functionality

Graphics and Charts

PivotTables

Advanced Charts and Graphics

Formulas and Functions - Basics

Formulas and Functions - Formatting

Formulas and Functions - Advanced Conditional and Financial Functions

Data Summarization and Outlines

What If Analysis

Securing Data

Exam Prep #1 -Data Validation, Macros, Formulas

Exam Cram #2 - Test Simulation

Please help us improve by sharing your feedback on training courses and videos. For customer service questions, please contact our support team. The views expressed in comments reflect those of the author and not of CBT Nuggets. We reserve the right to remove comments that do not adhere to our community standards.

comments powered by Disqus
Entry 9 hrs 20 videos

COURSE RATING

Training Features


Practice Exams
These practice tests help you review your knowledge and prepare you for exams.

Virtual Lab
Use a virtual environment to reinforce what you are learning and get hands-on experience.

Offline Training
Our iOS and Android mobile apps offer the ability to download videos and train anytime, anywhere offline.

Accountability Coaching
Develop and maintain a study plan with one-to-one assistance from coaches.

Supplemental Files
Files/materials that supplement the video training.

Speed Control
Play videos at a faster or slower pace.

Bookmarks
Included in this course
Pick up where you left off watching a video.

Notes
Included in this course
Jot down information to refer back to at a later time.

Closed Captions
Follow what the trainers are saying with ease.