Duration
1 day
Course Overview
This one-day instructor-ledcourse provides students with the skills to analyze alternative data sets, create dynamic worksheets by using PivotTables, create charts and graphics, automate repetitive tasks, work with other Microsoft Office programs, and collaborate on workbooks.
Audience Profile
This course is intended for novice information workers who want to learn advanced-level Excel 2010 skills.
At Course Completion
After completing this course, students will be able to:
- Define an alternative data set.
- Define multiple alternative data sets.
- Vary your data to get a desired result by using Goal Seek.
- Find optimal solutions by using Solver.
- Analyze data by using descriptive statistics.
- Analyze data dynamically by using PivotTables.
- Filter, show, and hide PivotTable data.
- Edit PivotTables.
- Format PivotTables.
- Create PivotTables from external data.
- Create charts.
- Customize the appearance of charts.
- Find trends in your data.
- Summarize your data by using sparklines.
- Create dynamic charts by using PivotCharts.
- Create diagrams by using SmartArt.
- Create shapes and mathematical equations.
- Enable and examine macros.
- Create and modify macros.
- Run macros when a button is clicked.
- Run macros when a workbook is opened.
- Include Office documents in workbooks.
- Store workbooks as parts of other Office documents.
- Create hyperlinks.
- Paste charts into other documents.
- Share workbooks.
- Manage comments.
- Track and manage colleagues’ changes.
- Protect workbooks and worksheets.
- Authenticate workbooks.
- Save workbooks for the Web.
Module 1: Analyze Alternative Data Sets
This module explains how to use alternative data sets to analyze the results of changes to your data.
Lessons
- Defining an Alternative Data Set
- Defining Multiple Alternative Data Sets
- Varying Your Data to Get a Desired Result by Using Goal Seek
- Finding Optimal Solutions by Using Solver
- Analyzing Data by Using Descriptive Statistics
After completing this module, students will be able to:
- Define alternative data sets.
- Determine the necessary inputs to make a calculation produce a particular result.
Module 2: Create Dynamic Worksheets by Using PivotTables
This module explains how to use PivotTables to create worksheets that can be sorted, filtered, and rearranged dynamically to emphasize different aspects of the data.
Lessons
- Analyzing Data Dynamically by Using PivotTables
- Filtering, Showing, and Hiding PivotTable Data
- Editing PivotTables
- Formatting PivotTables
- Creating PivotTables from External Data
After completing this module, students will be able to:
- Create and edit PivotTables from an existing worksheet.
- Focus PivotTable data using filters and Slicers.
- Format PivotTables.
- Create a PivotTable with data from a text file.
Module 3: Create Charts and Graphics
This module explains how to show trends in data by creating charts to summarize a worksheet's data visually, and to use sparklines to summarize the data in a single cell. It also discusses changing the appearance of charts by changing formatting; creating a PivotChart dynamic view of data; adding shapes and mathematical equations; and creating diagrams.
Lessons
- Creating Charts
- Customizing the Appearance of Charts
- Finding Trends in Your Data
- Summarizing Your Data by Using Sparklines
- Creating Dynamic Charts by Using PivotCharts
- Creating Diagrams by Using SmartArt
- Creating Shapes and Mathematical Equations
After completing this module, students will be able to:
- Create a chart and customize its elements.
- Find trends in overall data.
- Summarize data using sparklines.
- Create dynamic charts.
- Create and format shapes, diagrams, and shapes containing mathematical equations.
Module 4: Automate Repetitive Tasks by Using Macros
This module explains how to run and edit macros; make frequently used macros more accessible by assigning them to a new button on the Quick Access Toolbar; and create macros that run when a workbook is opened.
Lessons
- Enabling and Examining Macros
- Creating and Modifying Macros
- Running Macros When a Button is Clicked
- Running Macros When a Workbook is Opened
After completing this module, students will be able to:
- Open, run, create, and modify macros.
- Create Quick Access Toolbar buttons and shapes in order to run macros with a single mouse click.
- Define macro security settings.
- Run a macro when a workbook is opened.
Module 5: Work with Other Microsoft Office Programs
This module explains how to include Office documents in Excel workbooks, include Excel workbooks in documents, and create hyperlinks in files.
Lessons
- Including Office Documents in Workbooks
- Storing Workbooks as Parts of Other Office Documents
- Creating Hyperlinks
- Pasting Charts into Other Documents
After completing this module, students will be able to:
- Include an Office 2010 document in a worksheet.
- Store an Excel workbook as part of another Office document.
- Create hyperlinks.
- Paste and Excel chart into another document.
Module 6: Collaborate with Colleagues
This module explains how to make workbooks available to other people; manage their comments; manage changes to workbooks from multiple users; add password protection; digitally sign a workbook; and save a workbook as a Web page.
Lessons
- Sharing Workbooks
- Managing Comments
- Tracking and Managing Colleagues' Changes
- Protecting Workbooks and Worksheets
- Authenticating Workbooks
- Saving Workbooks for the Web
After completing this module, students will be able to:
- Share a workbook.
- Manage comments in workbook cells.
- Track and manage changes made by colleagues.
- Protect workbooks and worksheets.
- Digitally sign workbooks.
- Save workbooks for the Web.
Prerequisites
Before attending this course, students must have:
- Basic computer knowledge, such as keyboard and mouse skills.
- Basic file-management skills. The student should know how to navigate to folders and files on a computer running Windows 7.
This is a version-specific reference outline. Confirm the current syllabus and delivery arrangements with EIEIO.