Course Objective:

To equip participants with Database management tools in Excel to handle efficiently and effectively both Finance and non-Finance data

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 2-D 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