2015
Lynda
Curt Frye
6:26
English
Learn everything you need to master the Mac 2016 version of the world's most popular spreadsheet program. Curt Frye provides a comprehensive overview of Excel for Mac 2016, including manipulating workbook and cell data, using functions, printing worksheets, and collaborating with others. In the last half of the course, he covers more complex techniques, such as summarizing data with charts, working with external data, adding images and shapes, and automating routine tasks.
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
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
Download File Size:865.14 MB
|
