EXC-307
 

Introduction

 Advanced Date Entry and Formatting
Techniques

·       Restricting cell entries to specific numbers, dates or times

·       Restricting cell entries to specific entries in a list

·       Creating a custom number format

·       Applying conditional formatting to a worksheet

Naming Ranges

·         Defining a range name

·         Using a range name in a formula

·         Selecting a named range

Using Advanced Functions

·         Using the VLOOKUP function, using the HLOOKUP function

·         Using the DSUM and DAVERAGE functions

Creating a Custom Workbook Template

·         Creating a new workbook template

·         Modifying the default workbook template style

·         Basing a new workbook on a custom workbook template

Auditing a Worksheet

·         Tracing precedents and dependents in a worksheet

·         Tracing errors in a worksheet, watch and evaluate formulas

Linking Multiple Workbooks

·         Entering a formula to link multiple workbooks

·         Creating a workspace

Filtering and Summarizing Worksheet Data

·         Adding subtotals to a list

·         Applying an advanced filter to a list, outlining a worksheet

Analyzing Worksheet Data

·         Creating a pivottable report

·         Resetting the summary function in a pivottable report

·         Creating a pivotchart report, creating and displaying a scenario

·         Using the analysis toolpak, using solver

Importing and Exporting Data

·         Importing data in a text file into Excel

·         Importing data in a database file into Excel

·         Importing data from a web page into Excel

·         Exporting Excel data to other applications

Working with other Excel Users

·         Tracking changes in a workbook

·         Responding to proposed changes and comments in a workbook

·         Protecting a shared workbook, protecting an unshared workbook

Additional Advanced Excel Features

·         Using the data consolidation feature

·         Adding a trendline to a chart, format charts and graphics

·         Manage workbook properties, modify Excel default setting