Microsoft Excel®: A Comprehensive Hands-On Introduction

Introduction to Microsoft Excel

• Examining the Excel interface
• Working with cell ranges—navigating and selecting cells with the mouse and keyboard
• Entering and editing data, and employing AutoFill to enter data
• Inserting and deleting rows and columns, and adjusting column width and row height
• Manipulating data with cut, copy, paste, and the clipboard
• Dragging and dropping data with the mouse without using the clipboard
• Leveraging multiple worksheets

Formulas and Functions

• Writing simple formulas
• Exploring audit tools to examine and evaluate formulas
• Referencing cells in formulas using relative and absolute references
• Configuring cells for the number format and alignment
• Constructing common functions with the aid of the function wizard and the Formula tab
• Implementing names in formulas and functions

Worksheet Formatting and Presentation

• Setting up initial cell formatting
• Drawing attention to data with formatting
• Working with templates
• Protecting cell ranges and worksheets
• Analyzing data with conditional formatting

Getting Data In and Out

• Importing data into Excel from various sources: text, web, Microsoft Access, SQL Server
• Fixing data with global replace and data tools
• TEXT and DATE functions
• 2013 Flash Fill assistant
• Sorting, filtering, and summarizing data with auto filter and tables

Creating Visual Presentation of Data

• Leveraging charts to create a visual presentation
• Creating charts using wizards
• Adjusting chart settings, including titles, labels, and chart types
• Setting up your workbook for printing

Introducing Advanced Features

• Implementing dates
• Summarizing data from other worksheets with 3-D formulas
• Designing IF functions and conditional formatting
• Analyzing summary data with Pivot tables and slicers

• Summarize data with Excel by incorporating formulas, formatting, and charts and graphs
• Efficiently navigate and manipulate multiple spreadsheets in a workbook
• Optimize your worksheets using templates, graphs and formulas
• Design simple-to-complex formulas by nesting Excel functions
• Present data visually using Excel tables and charts
• Import, fine-tune and share Excel data in your workgroup

Introduction to Microsoft Excel

The spreadsheet model

• Worksheets and their contents
• Differentiating workbooks and worksheets

Navigating the interface

• General navigation
• Office Button
• The Ribbon
• Customizing the interface
• Managing large spreadsheets by zooming
• Identifying worksheet tabs by color
• Changing worksheet views

Basic data editing

• Modifying cell contents and formats
• Inserting data with AutoFill
• Knowing when to use Paste Special
• Finding and replacing data

Acquiring and Conforming Data

Importing data from outside sources

• Integrating data from other Excel sheets
• Utilizing data from servers
• Managing external links
• Finding and removing duplicates

Excel tables and lists

• How Excel tables work
• Converting text to columns
• Sorting and grouping table data
• Adding data in a table or list

Worksheet Formatting and Presentation

Initial formatting

• Configuring cell numeric formats
• Aligning data within cells
• Efficient presentation using the Format Painter
• Fitting cells to data size
• Naming cells and cell ranges

Worksheet presentation

• Freezing worksheet areas
• Hiding content in worksheets
• Editing, modifying and saving templates

Innovative formatting

• Styling cells and sheets with prebuilt styles
• Conditionally formatting cells and data
• Rotating and wrapping text

Leveraging Formulas and Functions

Formula basics

• Determining the order of operations
• Specifying relative and absolute cell references
• Automatic sums with AutoSum
• Conditionally summarizing data
• Introducing conditional logic in formulas
• Nesting expressions in formulas

Extending formulas and functions

• Putting strings together with concatenation
• Finding data with a formula: VLOOKUP, HLOOKUP
• Copying and pasting formulas and/or values
• Handling formulas with dates

Auditing worksheet formulas

• Deciphering tagged cells with errors
• Identifying formula error indicators
• Tracing spreadsheet formula errors

Creating a Visual Presentation of Data

Charting your data

• Determining when charts are required
• Creating and modifying charts with wizards
• Adjusting chart layouts, title and labels
• Switching chart type or data source

Printing workbooks

• Setting options on the Page Layout tab
• Viewing and adjusting page breaks
• Developing appropriate headers and footersHands-On Training
  Throughout this course, exercises provide you with experience applying Excel features, formulas and formatting. Hands-on exercises include:
  • Navigating the Excel interface for efficiency and speed
  • Formatting existing data for greater visual impact
  • Designing formulas to manipulate, summarize and refine raw data
  • Troubleshooting and tracing formula errors
  • Charting your data for clearer presentation
  • Recording macros to streamline tasks
  • Linking worksheets with references