Excel 2016 Advanced for Mac

Spreadsheet application for Mac10 H 52 M

Take your Excel skills to the next level with advanced Excel 2016 training for macOS. Learn about analyzing data, formatting tables, collaboration and more.

Just you? Training a whole team? There's an ITProTV plan that fits.

Start Training Today
  • Analyze Data
    • Overview
    • Sort and Filter
    • Conditional Formatting
    • Outline and Subtotals
    • Tables
    • Data Tables
    • Consolidate Data
    • Scenarios
    • Goal Seek
    • Remove Duplicates
    • Advanced Filter
    • Shapes: Add and Format
  • Graphical Elements
    • Images: Basics
    • Images: Format
    • Arrange Images and Shapes
    • SmartArt
  • Collaboration and Security
    • Shared Workbooks
    • Track Changes
    • Collaborate with Co-authoring
    • Co-authoring: Troubleshooting
    • Co-Authoring: Troubleshooting Part 2
    • Version History
    • Data Validation
    • Workbook Security
    • Document Properties
  • Additional Topics
    • Customize Interface


8 M

itprotv course thumbnailitprotv course thumbnailitprotv course thumbnail
  • Episode Description
  • Transcript

Excel 2016 Advanced for Mac explores ways to enhance Excel workbooks with topics in analyzing data, collaborating and workbook security, and adding graphical elements. This series covers important Excel functionality such as format as table, conditional formatting, shared workbooks and tracked changes, and data validation. While Excel 2016 Advanced for Mac is a continuation from the Excel 2016 Essentials for Mac series, it can be viewed as a standalone show for users looking to take their Excel skills to the next level. This series is specifically for locally installed Excel 2016 on Mac OS.

[MUSIC] Hello everyone, my name is Vaughn Smith, and welcome to Excel 2016 Advanced for Mac. In this course overview, we're going to be going over, well, exactly what we're gonna cover in this particular series. Who is the target audience, what you might need to know before you get started, and any extra tidbits that I have for you. So we have three main topics within this series. We have analyze data, graphical elements, and collaborate and security. In the analyze data topics we're gonna go over things that a lot of your Excel users are wanting to know about. Once you get past the, okay, I know how to enter information in to cells, now I need to analyze the data. I need to do things like sort and filter, I need to do conditional formating, which is really cool to be able to pull things out of it in a visual format sense. How do I make sub totals? How do I create outlines? How do I group different rows and columns into making it a little bit easier for me to look at the data and be able to make some determinations from that data? Formatting as a table, one of my favorite things. I use tables all the time in Excel, I think it's probably one of my favorite features. So we have some episodes on that. The What-If Analysis tools, this is where we're gonna go over creating data tables, creating scenarios, and using the Goal Seek functionality within that analyzing tool set. We're gonna consolidate data, we're gonna remove duplicates, and we're also going to do some advanced filters. Now, this particular series is not going to be focused on performing calculations and functions within our excel workbooks. Yes, we're going to allude to some of it, you're going to see some formulas and functions within these files, but we're not concentrating on how you actually create those functions. That's in a whole other series. It's really more of looking at your data and be able to analyze it. Then we're gonna move on to our graphical elements. This is where we get to talk about pictures and shapes. You're gonna be like, but this is Excel, Excel is more for number crunching, it's not for creating diagrams. But every program has this, and in Excel you do have things like charts and diagrams that can really illustrate your points within your information. Now, charts and graphs are in another whole series as well. But the basics that you're going to learn in this particular series, with, how do I create a circle, and fill it in, and do all the different formats? That will apply over into how you can format more advanced graphical elements, like charts. But we will get into some SmartArt as well, really get your that all of your office products have, and you could very well have this in an Excel workbook. The we're gonna move on to collaborate and security, where we're gonna collaborate and listen with people. And we're going to go over shared workbooks, the old shared workbooks, we'll tell you about that. And how you can track changes in Excel. It's very similar to how you can track changes in Word, but in Excel, it has a little bit different flavor of how it actually, what it tracks and how you can look at those and accept and reject those different changes. Then we have a little mini series on co-authoring, again, more of our new ways of being able to collaborate. And this is using our cloud services and functionality of the Office 365 applications, where we use our OneDrive for business accounts, of saving files up into that location, and being able to coauthor or collaborate in real-time with people, and how that all works. We do have a couple episodes on troubleshooting that because it's a great feature, but it has a few little gotchas in there. So we go over hopefully all the different factors and determinants that will maybe make it not so easy at first. We'll also get into data validation, a great way to control how other people might be inputting information into your cells. So input masks, it's kind of called that in other applications, such as Access, but in here were gonna call it data validation. So good things there. We're also gonna get into workbook security. We're going to be protecting our worksheets, protecting our workbooks. Also putting on password protections for even opening the files or editing the files. Then we end with document properties, understanding what that metadata that is behind the scenes in all of your files. Actually, I forgot one thing on our slide. We have an additional topic, and the one episode that is in there is the customized interface. And if ever we need to add more information or additional topics, we'll go ahead and put them in there. And then we move on to our target audience. Who is this for? All Office users, capital O, meaning you're using Office 365 suite, this is the locally installed version of Excel. We do use Excel online a little bit in the collaboration, but it is focused on Excel 2016 for Mac. And I would call this something that is in Upper basics and wants to take up their skills on to the intermediate level. Like, you have a couple base knowledge of Excel, like I said, how to enter some data in there, and now you're looking for how to really work with it. Things you need to know, you need to be familiar with the Mac OS environment in the sense of opening files from your finder, knowing where your doc bar is, those basic things. I already said, foundational knowledge of Excel, you know how to move around, you know how to switch between open workbooks, you know how to copy and paste. And familiarity with other Office products is always helpful in any other Office product cuz if you know a little in one, usually you can correlate it to the other. And always other options, yes. [LAUGH] What that means is there's multiple ways to do the same functionality within Excel. I tried not to say functions because that's a reserved word in Excel, but there's many different ways to get to the same end result. And so if I'm doing it one way, there might be another way of doing it, and that's totally valid. Updates happen, and man, they can mess with you a little bit when it goes ahead and updates and then your buttons move around, and things like the shared workbooks doesn't exist anymore, really? It does they just took it away, they took it off the ribbon. It's not in the actual menus, you have to go hunt and pick it, so that happens. And always being able to search for answers using whatever search engine that you want. You can find it, the Microsoft Help files are always helpful, but there's tons of resources out there to help answer your questions. Everybody uses Excel for different reasons, okay? So I cover all the different things that Excel can do. Some of it you might not have any use for, and that's totally fine cuz we all use it in a little bit different manner. But for the most part, in this episode and in how we learn, have fun. Have fun learning, follow along, and hopefully the learning will make using Excel more fun, and you won't be as frustrated. So please stick around, and we'll see you in some episodes very shortly. [MUSIC]