Excel 2007 Level 3
Short course
In Washington
Description
-
Type
Short course
-
Location
Washington
-
Duration
1 Day
If you're already up to speed with Microsoft Excel but you would now like to automate some common tasks, apply advanced analysis techniques to more complex data sets. This course is aimed at someone desiring to gain the skills necessary to create macros, audit and analyse worksheet data, create PivotTables and PivotCharts, use lookup tables and import and export data.
Facilities
Location
Start date
Start date
About this course
Before starting this course, we recommended that you have attended the Excel 2007 Level 1 and 2 courses or have equivalent knowledge.
Reviews
Course programme
If you're already up to speed with Microsoft Excel but you would now like to automate some common tasks, apply advanced analysis techniques to more complex data sets. This course is aimed at someone desiring to gain the skills necessary to create macros, audit and analyse worksheet data, create PivotTables and PivotCharts, use lookup tables and import and export data.
Prerequisites
- Before starting this course, we recommended that you have attended the Excel 2007 Level 1 and 2 courses or have equivalent knowledge.
Objectives
- Delegates will be able to :
- Change the view of a spreadsheet to show a different layout without using any formatting tools, then change the data on the sheet to display different data values.
- Create a PivotTable to manipulate and extract data, then create a chart to display the results.
- Create a spreadsheet that will display multiple groups of records that can be updated from a single source.
- Use the Goal Seek and Solver facilities to enable results to be predicted in a spreadsheet where data may be missing or unavailable.
- Create a formula that will only add up or count records that match specific criteria.
- Record a Macro that will automate a repetitive task and then assign it to a new button on a new toolbar.
- VBA using Excel
- Private course at your company office throughout the UK, at our training room or at an independent location, convenient to you
- Scheduled public course at one of our training venues
- Bespoke course (a course written to your specific needs) at your company office throughout the UK, at our training venue or at an independent location, convenient to you
- 1 delegate per computer
- 12 Months telephone and email support
- Comprehensive colour course manual and exercises
- Use of a Modern Training suite with 19 inch flat screen monitors
- Lunch (When at our training venue)
- Refreshments (When at our training venue)
- Relaxed refreshment area at our training room
Organising Data Using Tables
- Formatting tables
- Modifying tables
- Creating tables
- The outline symbol
- Removing an outline from a worksheet
- Ungrouping an outline manually
- Creating an outline manually
- Creating an outline automatically
- Displaying a different view
- Deleting a view of a worksheet
- Creating a view of a worksheet
- Removing all tracer arrows
- Tracing precedents and dependents
- Description of tracer arrows
- Displaying the auditing toolbar
- Using tracers to help audit a worksheet
- Deleting a Scenario
- Editing a scenario
- Viewing a scenario
- Setting up a scenario Adding a Comment to a cell Read a comment attached to a cell Print the comments with a Worksheet Edit a Comment on a cell Viewing comments all the time
- Using VLOOKUP
- Entering a workbook name and path into a cell
- Round: rounding a number to decimal places
- Functions, including Nested IF, SUMIF, COUNTIF and COUNTBLANK function
- Printing formulas
- Performing logical analysis
- Calculating data with lookup & reference functions
- Performing numeric calculations
- Formatting text using formulas Tips using VLOOKUP HLOOKUP
- Moving a pivot table from one sheet to another or around the same sheet
- Creating a second (or more) pivot table on the same data
- Number formatting of the data area
- Rearranging fields in a pivot table
- Dynamically building a pivot table
- Data in the summary area
- Fields to create column headings
- Fields to create row headings
- Creating your first pivot table Deleting a Pivot Table
- Grouping and hiding multiple row and column groupings
- Removing AutoFormat
- Autoformat
- Turning off the summary rows and columns
- Activating automatic refresh options
- Refreshing the pivot table
- Dealing with empty cells
- Setting values based on a target cell
- Using goal seek to find a required result
- Setting a security option for your macros
- Creating your own toolbar
- Viewing the code behind a macro stored in the Personal workbook
- Viewing the code behind a macro stored in the current workbook
- Editing a Button on a Worksheet
- Assigning a macro to a button on a worksheet
- Removing a button from a toolbar
- Assigning a macro to a toolbar button
- Deleting a macro
- Running a macro
- Using elative references when recording a macro
- Recording a macro Viewing the code behind a macro stored in the Personal workbook Creating your own toolbar Setting a security option for your macros
Excel 2007 Level 3