Merehall Training Getting People on Course

Microsoft Excel 2007 Intermediate Level

MANIPULATING DATA, & NAMED RANGES

• Paste Special
• Transposing data
• Importing text and delimiting by space, comma or tab
• What does naming a cell range mean?
• Rules for naming cells and ranges
• Naming cell range(s) in a worksheet
• Named ranges within formulas
• Navigating through workbooks using named ranges
• Creating named ranges automatically based on cell values
• Deleting named cells/ranges
• Creating subtotals
• Removing subtotals

TEMPLATES

• Using templates
• Creating templates
• Opening and editing templates

FORMATTING & DISPLAY TECHNIQUES

• Formatting tables
• Formatting tables using table styles
• Formatting cell ranges using conditional formatting
• Creating custom number formats
• Freezing the top row
• Freezing the first column
• Freezing the top row and the first column at the same time
• Hiding and un-hiding rows
• Hiding and un-hiding columns
• Hiding and un-hiding worksheets

SORTING AND QUERYING DATA

• Sorting internal Excel databases
• Custom sort options
• Using AutoFilter to query data
• Multiple queries
• Removing filters
• Top 10 AutoFilter
• Filtering unique records
• Advanced Filter

LINKING & CONSOLIDATING DATA

• Linking individual cells within a worksheet
• Linking charts to data within a worksheet
• Linking a cell range on one worksheet to another worksheet (within the same workbook)
• Linking data on one worksheet to a chart in another worksheet (within the same workbook)
• Linking data from one workbook to another
• Linking a chart from one workbook to another
• Copying data from Excel into a Word document
• Linking data from Excel into a Word document
• Copying a chart from Excel into a Word document
• Linking a chart from Excel into a Word document
• Consolidating data over several worksheets or worksheet pages

CHARTS FORMATTING TECHNIQUES

• Changing the angle of pie chart slices
• Formatting the chart axis fonts
• Formatting the chart axis scales
• Formatting the chart axis text orientation
• Creating and positioning a chart title
• Re-positioning a chart legend
• Re-positioning chart data labels
• Exploding the segments within a pie chart
• Deleting a data series within a chart
• Adding a data series to a chart
• Modifying the chart type for a defined data series
• Widening the gap between columns / bars within a 2D chart
• Inserting an image into a 2D chart (as a background)
• Inserting an image into a 2D chart (to format a column or bar of data)

PROTECTION AND SECURITY

• Specifying a password for opening a workbook
• Using the “read-only recommended” option
• Removing a password from an Excel workbook
• Protecting a worksheet or worksheet elements
• Removing workbook protection
• Allowing selective editing of a protected worksheet