| This Microsoft Excel Advanced training course is a 1 Day public programme, designed for candidates who would like to enhance their understanding and knowledge of Excel to an advanced level. It takes participants through the items listed in the course contents to a point where participants can use Microsoft Excel to advanced levels to increase efficiency in their everyday work.
Templates
• Using templates.
• Creating Excel templates.
• Displaying hidden template folders on a Windows 7 computer.
• Editing Excel templates on a Windows 7 computer.
• Displaying hidden template folders on a Windows Vista computer.
• Editing Excel templates on a Windows Vista computer.
• Displaying hidden template folders on a Windows XP computer.
• Editing Excel templates on a Windows XP computer.
Paste Special Options
• Using Paste Special to add, subtract, multiply & divide.
• Using Paste Special ‘values’.
• Using Paste Special transpose option.
Pivot Tables.
• Using a Pivot Table.
• Filtering and Sorting Data
• Grouping Data
• Creating and using a pivot table.
• Filtering and sorting data within a pivot table.
• Automatically grouping data in a pivot table and renaming groups.
• Manually grouping data in a pivot table and renaming groups.
Input Tables
• One-input data tables.
• Two-input data tables.
Charts
• Combined line and column charts.
• Adding a secondary axis to a chart.
• Adding a data series to a chart.
• Removing a data series from a chart.
• Re-positioning chart title.
• Re-positioning the chart legend.
• Moving and formatting chart data labels.
• Modifying chart axis scales.
• Formatting an axis to display using commas.
• Inserting images into chart columns.
• Inserting images to chart bars.
• Formatting the chart plot area using a picture.
• Formatting the chart area using a picture.
Linking & Embedding
• Linking data within a worksheet.
• Linking cells between worksheets
• Linking data between workbooks.
• Linking data from Excel to Word.
• Linking an Excel chart to Word.
• Updating, locking and breaking links.
Tracking and Reviewing Changes
• Enabling or disabling the ‘track changes’ feature.
• Sharing, comparing and merging worksheets.
Scenarios
• Scenario Manager.
• Scenario summary reports.
Validating
• Data validation – Whole number.
• Data validation – Decimal number.
• Data validation – List.
• Data validation – Date.
• Data validation – Time.
• Data validation – Text Length.
• Customising a validation input message and error alert.
• Removing data validation.
Auditing
• Tracing precedent cells.
• Tracing dependent cells.
• Identifying cells with missing dependents.
• Showing all formulas in a worksheet, rather than the resulting values.
• Inserting and viewing comments.
• Editing and deleting comments.
• Showing and hiding comments.
Macros
• Using Macros
• Assigning a Macro to a Button on the Quick Access Toolbar.
• Deleting Macros
• Using Macros
•• Deleting Macros
• Macro to change the page set-up.
• Macro to apply a custom number format.
• Macro to format a cell range.
• Macro to insert fields into the header or footer.
• Assigning a macro to a button on the Quick Access toolbar.
• Deleting macros.
Customising Excel
• Modifying Excel Options
• Minimising the Ribbon
• Autocorrect Options
Passwords & Security Issues
• Adding password protection to a workbook.
• Removing password protection
• Password protecting cells and worksheets.
• Hiding and un-hiding formulas.
Mathematical Functions
• ROUND.
• ROUNDDOWN.
• ROUNDUP.
Logical Functions
• IF.
• AND.
• OR.
Mathematical Functions
• SUMIF.
Statistical Functions
• COUNT.
• COUNTA.
• COUNTIF.
• COUNTBLANK.
• RANK.
Text Functions
• LEFT.
• RIGHT.
• MID.
• TRIM.
• CONCATENATE.
Financial Functions
• FV.
• PV.
• NPV.
• RATE.
• PMT.
Lookup Functions
• VLOOKUP.
• HLOOKUP.
Database Functions
• DSUM.
• DMIN.
• DMAX.
• DCOUNT.
• DAVERAGE |
