Excel for Mac 2011 Essential Training

Introduction

• Welcome
• Using the exercise files

• Exploring the Excel 2011 window
• Introducing the Ribbon for Mac
• Customizing the Ribbon
• Setting program preferences
• Getting help in Excel

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

• Selecting cells and groups of cells
• Copying and pasting cell data
• Entering data using AutoFill and other techniques
• Inserting symbols and special characters
• 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
• 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

• Introducing Excel formulas and functions
• Adding a formula to a cell
• Introducing arithmetic operators
• Using absolute and relative 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
• Joining text in cells with concatenation
• Summarizing data using an IF function
• Summarizing data using SUMIF and other conditional functions
• Creating formulas to count cells
• Rounding cell values up and down
• Finding data using VLOOKUP and HLOOKUP
• Auditing formulas by identifying precedents and dependents
• Managing Excel formula error indicators
• Managing scenarios
• Performing Goal Seek analysis

• 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

• 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 a chart
• Managing chart axes and numbering
• Adding trendlines to charts
• Creating sparkline charts

• 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

• Creating and formatting shapes
• Adding and adjusting images
• Cropping, compressing, and removing image backgrounds
• Creating SmartArt graphics
• Creating WordArt
• Aligning and layering objects

• 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
• Creating and editing styles

• 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

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

• Additional resources