ExecuTrain Austin

Microsoft Excel 2007
Part Two

Course Length: 1 Day
Hours: 8:30 AM - 4:30 PM
Learning Method: Instructor-Led
Upcoming Dates:

Microsoft Excel 2007
Part Two

Request Information

Working with Multiple Worksheets

WHILE DATA ARE ROUTINELY STORED ON A SINGLE WORKSHEET, EXCEL DOES NOT LIMIT YOUR CALCULATIONS TO THAT SHEET.  USE A VARIETY OF METHODS TO COLLECT, CALCULATE, AND PRESENT INFORMATION FROM MANY SHEETS SIMULTANEOUSLY IN THE FORMAT YOU CHOOSE.

Details:

• Use the Fill Across Worksheets feature to quickly copy worksheet data to multiple worksheets. You can also link information between worksheets to update the copied data whenever the source data has changed.

• You can create formulas and functions that contain references to cells that are on separate worksheets or workbooks.

• The Consolidate feature enables you to summarize multiple worksheets that contain related lists into one location. You can choose to display a total of each item or use another function to calculate the data in a different way.

• Use conditional formatting to apply a specified format to only the information that meets the criteria you specify.

• There may be times when it would be nice to share data across applications. You can use the Text Import Wizard to import delimited or fixed width text files into Excel. You can save a workbook in a different format to be used in another application.

Sorting and Filtering

USE SUBTOTALS TO STAY APPRISED OF ACTIVITY IN SMALLER GROUPS OF NUMBERS AND  EXPLORE A VARIETY OF TECHNIQUES FOR DISPLAYING PRECISELY THE INFORMATION YOU NEED IN A GIVEN MOMENT OR FOR A PARTICULAR PURPOSE.

Details:

• Allow Excel to quickly calculate subtotals to a list based on the data of a particular field.

• For information arranged as a database use a data form to view, add, modify, or delete records.

• AutoFilter is a quick and easy way to display only those rows the meet the criteria you specify.

• Use Advanced Filters to query your data and display only the information that meets the criteria you specify.

Pivot Tables

PIVOTTABLES ENABLE YOU TO ORGANIZE AND SUMMARIZE YOUR DATA IN SUCH A WAY THAT INCLUDES THE INFORMATION YOU WANT TO DISPLAY AND EXCLUDES THE REST. CHANGE A PIVOTTABLE’S FOCUS BY MOVING THE FIELDS TO DIFFERENT LOCATIONS, AND CREATE PIVOTCHARTS TO REPRESENT THE DATA IN A GRAPHICAL FORMAT.

Details:

• Field buttons in PivotTables enable you to quickly change the information being displayed. Calculated fields can be modified to display the data using a different calculation or a new calculation can be added to meet your individual needs.

• Group fields to broaden the view of the selected field. This feature works especially well to group dates that span several years into months or quarters, but can be used for text and numbers as well.

• PivotCharts present you PivotTable data in graphical form. Just like PivotTables, the information viewed can be quickly changed by using the field buttons to include or exclude field items.

Macros & Objects

INSTEAD OF REPEATEDLY PERFORMING ROUTINE AND/OR COMPLEX TASKS, LET EXCEL REMEMBER THEM FOR YOU SO THEY CAN BE CARRIED OUT WITH A CLICK OR A KEYSTROKE – THEN STORE THEM ON YOUR MENU OR TOOLBAR FOR EASY ACCESS. ENHANCE THE APPEAL AND IMPACT OF YOUR WORKSHEET BY INCLUDING VISUAL OBJECTS THAT SUPPLEMENT YOUR DATA, THEN FORMAT AND ARRANGE THEM FOR MAXIMUM EFFECT.

Details:

• Macros replay a series of events such as keystrokes or mouse clicks. Learn how to record a macro, assign it to a menu or toolbar, and then run the macro.

• Learn to make simple edits to a macro such as renaming the macro or formatting a column heading by using the Visual Basic Editor.

• Learn how to use the Drawing toolbar to enhance a spreadsheet. Learn to insert, move, and resize AutoShapes and to insert ClipArt.

Collaborating with Others

WORKING WITH OTHERS ON A SINGLE WORKBOOK CAN QUICKLY BECOME UNMANAGEABLE, RESULTING IN MULTIPLE FILES AND E-MAIL MESSAGES CONTAINING RECOMMENDED CHANGES WHICH MUST BE MANUALLY ENTERED.  INSTEAD, CLICK A BUTTON TO TRACK EDITIONS MADE BY EACH PERSON, MERGE THEM ALL INTO ONE WORKBOOK, THEN DECIDE WHICH CHANGES TO ACCEPT AND REJECT.  SEND WORKSHEET DATA TO OTHERS IN THE BODY OF AN E-MAIL MESSAGE, AND PASSWORD-PROTECT YOUR WORKBOOK FOR ADDED SECURITY.

Details:

• Assign password protection to your workbook to either prevent an unauthorized user from opening the file or to prevent someone from saving changes to your workbook. You can also protect the worksheet by preventing other users from using selected features such as inserting or deleting rows or columns. You can also protect the structure of the workbook to prevent users from inserting, deleting or moving worksheets.

• Set up a workbook to be shared to allow other users to view and/or edit the workbook while you are working in it. Save a copy of the workbook for each editor and email the copies as attachments or save the files on a network drive.

• Add comments to cells in a workbook as a note to yourself or for another user. Comments can be edited and deleted.

• Enable the Track Changes feature to enable you to view changes made to the workbook. You can use the Accept or Reject Changes feature to browse the workbook by each change made and select whether to accept or reject each change.

• Send workbooks by email as an attachment or in the body of a message.

• View changes made to a shared workbook by merging the edited copies to the original workbook.