Excel 2007 Intermediate Class

Excel: Multiple Worksheets & Workbooks

Excel 2007 allows you to reference data from other worksheets, and even incorporate data from multiple Excel files into a single formula. In this part of the course you will learn how to:

• Open and switch between multiple workbooks
• Easily copy worksheets from one workbook to another
• Insert powerful 3D formulas to reference data in multiple worksheets
• Add a convenient Watch window to keep track of a 3D formula's results even when the cell isn't visible
• Create and maintain links between workbooks
• Set up your workspace so that you can easily view multiple workbooks at the same time

Advanced Formatting in Excel

Excel 2007 gives you several formatting tools to improve the look and feel of your spreadsheets, or even to compact your worksheet or a portion of it onto one printable page. We'll show you several tricks to accomplish this. In this part of the course we will explore how to:

• Apply built-in or custom formatting options for numbers
• Automate conditional formatting for text
• Save time by using styles to control formatting
• Create theme colors that capture the look and feel you want
• Control cell content alignment by merging cells, indenting cell contents, or changing the orientation
• Quickly convert a row into a column and vice versa
• Add a background image or a watermark effect to your worksheet
• Organize your worksheets with custom headers and footers

Outlining & Subtotals in Excel

Excel 2007 offers a number of tools and techniques that make it easier to navigate and work with large worksheets with lots of data. Since it isn't always necessary to see all the data on a particular worksheet, we'll show you how to use Excel's handy options for summarizing data. In this section you will practice how to:

• Organize data by outlining it
• Consolidate data either by position or by category
• Create automated subtotals for lists of data
• Use multiple subtotal functions to summarize information

Cell & Range Names in Excel

Simplify your life! Instead of referring to cells as A8 or C23, you can assign logical names like "expenses" or "tax rate" to cells or ranges. You can then use these names when you create formulas elsewhere in your spreadsheet. In this part of the class we cover how to:

• Assign names to cells or ranges of cells
• Reference cells or a range of cells using their name
• Save time by using column and row labels to assign names
• Use the Name Manager to view, edit, or delete names across multiple worksheets
• Define 3D names that refer to multiple worksheets

Excel: Lists and Tables

Another way to organize data in a concise and logical format is with lists and tables - separate sets of data within your worksheets. Once a list or table has been defined, you can sort, filter, and subtotal its contents. We will show you how to:

• Sort data into lists and explore the structure of a list
• Create powerful data filters with one or multiple criteria
• Re-use filtered results by copying them
• Create, format, and edit tables
• Reduce errors by using "structured references" for working with table data

Excel Web & Internet Features

With just a few simple steps you can save your workbook as a web page, and make it available to anyone with a browser and a connection to your web site or intranet. Moreover, Excel 2007 makes it simple to ensure that the web-enabled version of the workbook is always up-to-date. In this section of the course we'll explore how to:

• Enable the Web commands in your Excel configuration
• Save and preview workbooks as web pages
• Publish web pages and make sure they are automatically updated
• Insert and edit hyperlinks in a worksheet
• Distribute workbooks without publishing them
• Share workbooks via email

Advanced Charting in Excel

Though you may know how to create a chart in Excel, you may not be aware of all the helpful chart tools that Excel 2007 provides. Here, we explore some of the more complex and interesting chart options to take your presentations and data management to the next level. In particular, you will learn about how to:

• Make your charts more informative with customized scales and data point labels
• Apply special pie chart formatting for more impact
• Combine chart types when needed
• Add easy-to-interpret trend lines to data arrays
• Draw and format your own graphics to add impact to your charts
• Add, resize, and modify pictures

Auditing in Excel

Make sure your data is error-free and clean. Auditing is the process of tracing how formulas are dependent on each other and on cells, so you can track down errors and circular references. In this section of the course, we discuss how to:

• Trace precedent and dependent cells
• Trace errors that crop up in your worksheet
• Add comments to cells, worksheets, or workbooks
• Protect your data with passwords and restricted permissions
• Use digital signatures to protect worksheets published to the internet
• Easily share and merge workbooks in a workgroup
• Track changes in a shared workbook
• Prepare and finalize workbooks for safe distribution

Excel Templates

Excel 2007 offers over a hundred built-in or downloadable templates: calendars, balance sheets, expense reports, budgets, invoices, and more. You can save a lot of setup and formatting time by using these built-in templates, and you can even create your own templates. In this final part of the course, you will master how to:

• Select from built-in Excel templates
• Create and manage customized templates
• Organize your template files

Prerequisites:

• Familiarity with the Ribbon
• Understanding of cell references, formulas and functions

Intermediate Excel classes are offered at four locations::

• Excel class San Francisco
  
• Excel class Los Angeles
  
• Excel class San Jose
• Excel class Sacramento