Excel for Mac 2016 Essential Training

Learn everything you need to master Excel for Mac 2016, including using functions, printing worksheets, and collaborating with others.

Introduction

• Welcome
• Using the exercise files

1. Getting Started with Excel

• What you can do with Excel 2016
• Exploring the Excel 2016 program window
• Introducing the Ribbon for the Mac
• Setting program preferences
• Getting help in Excel

2. Managing Workbooks

• Opening, creating, and saving workbooks
• Setting workbook properties
• Creating and modifying workbook templates
• Managing workbooks across multiple versions of Excel

3. Working with Worksheets, Cells, and Cell Data

• Selecting cells and groups of cells
• Copying and pasting cell data
• Entering data using AutoFill and other techniques
• Inserting symbols and special characters
• Creating named ranges
• Creating an Excel table
• Locating and changing data using Find and Replace
• Restricting input using validation rules
• Using lists to limit data entered into a cell

4. Sorting, Filtering, and Managing Worksheets

• Sorting worksheet data
• Creating a custom sort order
• Filtering worksheet data
• Inserting, moving, and deleting cells and cell ranges
• Splitting and freezing rows and columns
• Managing worksheets
• Creating, editing, and deleting headers and footers

5. Summarizing Data Using Formulas and Functions

• Introducing Excel formulas and functions
• Adding a formula to a cell
• Introducing arithmetic operators
• Using relative and absolute cell references
• Controlling how Excel copies and pastes formulas
• Referring to Excel table data in formulas
• Creating an AutoSum formula
• Summarizing data on the status bar
• Summarizing data using an IF function
• Summarizing data using SUMIF and other conditional functions

6. Analyzing Data and Formulas

• Creating formulas to count cells
• Rounding cell values
• Joining text in cells with concatenation
• Finding data using VLOOKUP and HLOOKUP
• Auditing formulas by identifying precedents and dependents
• Managing Excel formula error indicators
• Managing scenarios
• Performing Goal Seek analysis

7. Formatting Worksheet Elements

• Applying fonts, background colors, and borders
• Applying number and date formats to cells
• Managing text alignment
• Copying cell formats
• Managing cell styles
• Managing Office themes
• Creating rule-based conditional formats
• Defining top 10 conditional formats
• Defining data bar, color scale, and icon set conditional formats
• Editing, ordering, and deleting conditional formats

8. Working with Charts

• Creating bar and column charts
• Creating pie charts
• Creating line charts
• Creating XY (scatter) charts
• Creating stock charts
• Changing chart types and layouts
• Changing the appearance of chart elements
• Managing chart axes and numbering
• Adding trendlines to charts
• Creating sparkline charts

9. Working with External Data

• Importing data from comma-separated value (CSV) or text files
• Connecting to an external data source
• Using hyperlinks
• Including an Excel workbook in another Office document
• Linking to an Excel chart from another Office program

10. Working with Objects

• Creating and formatting shapes
• Adding and adjusting images
• Manipulating text boxes
• Cropping, compressing, and removing image backgrounds
• Creating SmartArt graphics
• Creating WordArt
• Aligning and layering objects
• Managing objects using the Selection pane

11. Exploring PivotTables

• Introducing PivotTable reports
• Creating a PivotTable report
• Pivoting a PivotTable report
• Managing subtotals and grand totals
• Summarizing more than one data field
• Changing the data field summary operation
• Changing the data field number format
• Filtering a PivotTable report
• Applying a PivotTable style

12. Reviewing and Sharing Your Spreadsheets

• Checking spelling
• Setting AutoCorrect and automatic Replace options
• Managing workbook comments
• Tracking and reviewing changes
• Printing a worksheet or workbook
• Setting and removing print areas
• Exporting to other formats
• Protecting a workbook

13. Automating Workbooks Using Macros

• Running an existing macro
• Recording a macro
• Renaming, viewing, and deleting macros
• Adding comments to a macro
• Turning off screen updating in a macro

Conclusion

• Next steps