Learn how to use the brand new Excel 2016 for Mac! You’ll learn how to set up a spreadsheet from scratch, update an existing spreadsheet, and improve the appearance of a spreadsheet to make it easy to read and understand. You’ll also learn how to create simple calculations, work with list based data, and print your spreadsheet.
Based in Manchester, England, Mike has 27 years experience in corporate IT training, working with and for organizations across a diverse range of industries. He designs and delivers face-to-face, virtual, written and video-based training, primarily focusing on Microsoft Office, Lync, SharePoint, VBA development and database development.
Entering and Editing Data In this module, I'm going to teach you about entering data into cells. We'll look at the difference between text, numbers, and dates, and I'll also show you a cool trick to solve a problem where you have numeric values that begin with a 0. I'll be showing you how to amend and delete the contents of a cell, and also you'll learn about a valuable, yet underused feature of Excel called cell comments. And finally, we'll look at four great ways to save time when entering data into Excel, cut, copy, autofill, and custom lists.
Formatting Your Data In this module, we're going to focus on formatting, which is all about improving the appearance of the spreadsheet, to make it easy to read and understand. You'll learn about how to change the width of a column and the height of a row, and how to insert and delete rows and columns within existing data. Then I'll teach you about cell formatting. In addition to learning how to change the font, size, and color of cells, we'll look at changing the alignment of data in a cell, and how to change the way that numbers and dates are displayed. Even something simple like adding a separator character for the thousands will make very large numbers easier to read. And you can do that with a single click. You'll also learn about a great time-saving feature called the Format Painter. Once you've mastered the basics of formatting, we'll step it up a gear and look at Conditional Formatting, a great feature that lets you apply formatting to a cell based on one or more conditions, for example, change the background color of A1, but only if it contains the word complete. And finally, you'll learn how to hide and protect cells, which are a couple of great ways to prevent accidental changes to important data.
Creating Formulas and Using Functions Although it is perfectly possible to use Excel without ever creating a formula or using a function, if you don't, you're missing out on a huge part of the application's power. Formulas allow you to do things such as add up numbers, subtract one number from another, as well as divide and multiply. In this module, you'll learn how to create formulas to do all these things. You'll also learn about the importance of using brackets or parentheses in formulas. I'll show you what happens when you copy a formula, and how sometimes you need to make a cell reference in a formula fixed or absolute so that the formula copies correctly, and you'll learn about how to use cell names to make your formulas more understandable. Functions are like formulas, except they are predefined and built into Excel. There's over 300 functions built into Excel and I'll show you a few of the basic, but useful ones. But let's start this module by looking at creating basic formulas.
Working with Lists This section of the course is all about working with lists. This could be a list of numbers, or dates, or text entries, or even a multicolumn list. Excel is perfect for manipulating lists of data. You can sort a list, for example, a list of numbers could be sorted into highest to lowest order, a list of names can be sorted alphabetically. You can apply a filter to a list, for example, show me a list of all the employees who are based in the New York office, or even, show me all the tasks where the cell color is green. And we'll look at sorting and filtering in this part of the course. More and more I'm finding that people are using Excel to work with data that has come from other sources. In other words, the data hasn't been typed directly into Excel, and a common issue with data that's exported from other systems is that it often needs to be tidied up and cleaned to make is useable, and you'll learn about some of the ways you can do this. And finally in this module, I'll answer an often-asked question, how do I stop my list headings from disappearing as I scroll down the screen? So with that, let's move on and look at sorting.
Printing Your Data In this module, you'll learn about printing an Excel workbook. There's more to it than simply hitting the Print button and waiting for the paper to come out of the printer, or PDF to be created. I'll start by showing you how to select what's to be printed. You can choose to print the entire workbook, a specific worksheet or worksheets, or even just a range of cells. We'll look at how to switch the page orientation between portrait and landscape. And I'll show you how to alter the margin settings and the scaling options in order to fit your workbook content neatly onto a printed page. I'll also teach you how to enhance the print out using headers and footers to add a page number, the date, and the file name to the top or bottom of each page. And finally, I'll show you how to print cell comments, and how in a large worksheet you can print the column headings on each page.
Course Summary You've now reached the end of the Getting Started with Excel 2016 for Mac training course. So just to recap what we covered. Module 1 was all about the interface and opening and saving files. Module 2 covered entering and editing data, starting with the basics and then looking at some great time savers like cut, copy, autofill, and custom lists. I also showed you how to add comments to a cell. In module 3 we looked at formatting to make your spreadsheet easy to read and understand. You learned about things like changing column widths, applying different colors and fonts, hiding data, and protecting data, as well as ways to speed up formatting. Module 4 covered creating calculations, or formulas and functions. We looked at the mathematical order of operations, making cell references absolute with the $ signs, and then covered a few of the basic functions. Module 5 was all about working with lists, sorting, filtering, freeze panes to keep headings on screen, and I showed you how to use text to columns to fix problems with imported data. And finally, in module 6, you learned about printing. We covered setting a print area, margins, headers and footers, portrait versus landscape, and how to fit the printout on to a specific number of pages. Well I hope you've found it a valuable use of your time. As I said at the beginning, this course focuses on what you need to know in order to be able to use the software at an introductory to intermediate level. What you need to do now is to take what you've learned, take the concepts, and apply them to your own real world spreadsheets. Good luck, and until we meet again, take care, and goodbye.