Crystal Training

Please click here to view the class outline.

Upcoming classes:

Who Should Attend:

This course is recommended for individuals who are first-time users of Excel and who want to learn how to use Excel; or for existing Excel users looking for a refresh on the variety of basic Excel features. The course will offer an insight into the basic operations including spreadsheets, charts and pivot tables and will enable the participant to understand and start using the basic features of this program.

Excel Level 1 Outline:

Introduction into Excel

• Using the help feature in Excel
• How to get started
• How to navigate through the program
• How the basic functions work
• Toolbars and menus, customize quick access, mini toolbars and the ribbon bar
• File new, on-line and off-line templates, file types and uses
• Get to know the conventions of Excel
• Comparing the different versions of the software (Excel 2003, 2007, and 2010)
• Dealing with formula auditing, errors, tracing, watch window and calculation options

Entering Numbers and Text

• Overview about the electronic spreadsheet
• How to organize text and numeric data in lines and columns
• How to format and modify worksheets
• How to add, delete, copy and move worksheets
• Working with Multiple worksheets
• Insert and delete cells, clipboard and paste options
• Ensuring consistency and accuracy, protecting and hiding cells
• Using templates, styles, and AutoFormats

Entering Excel Formulas and Formatting Data

• How to format text and numerical data
• Working with formulas
• Perform basic mathematical calculations
• Working with date and time
• Importing data from Microsoft Word and the web, delimited data, export data, linked and shared workbooks
• Finding and replacing data

Creating Excel Functions, Filling Cells, and Printing

• How to understand and use basic Excel functions
• Introduction in common functions like SUM, AVERAGE, MAX and MIN
• Using and extending AutoSUM
• IF, related functions and nested IFs
• Lookup and reference functions like VLOOKUP and HLOOKUP
• Power Functions, and how to tabulate information on single and multiple criteria with COUNTIF(S), SUMIF(S) and AVERAGEIF(S)
• Statistical functions: Middle value, ranking data, magnitude data
• How to use basic math functions
• How to create a series, working with the name manager and how to consolidate data
• How to create headers and footers, freeze panes, new window and arrange all options
• Preparing documents for printing, layout and page beak preview

Creating Charts

• How to represent numbers in a chart and spark lines (thumb nails)
• Selecting the right chart type; line, bar, scatted, pie, stock, radar, surface, area, bubble and doughnut graphs
• How to change the layout
• Chart terminology
• How to use the chart wizard
• How to format charts
• Adding labels to a chart
• How to switch data and add multiple data ranges
• How to deal with empty and hidden cells
• Creating SmartArt diagrams
• Analyzing and communicating data with charts, quick table and filtering formats with predetermined data

Creating Pivot Tables for Data Analysis

• How to create a Pivot Table, and data slicers
• Formatting data for Pivot tables
• Configuring, sorting and filtering,
• Sorting across data sources to show relative importance
• Adding, removing, and positioning subtotals and grand totals
• Creating conditional formats to highlight subsets of data and comparative analysis
• Using color scales to emphasize specific information
• How to update and refresh PivotTable data sources

Macros

• How to use Macros
• Recording and adding functionality to a Macro
• How to generate a VBA code by using the Macro recorder
• Dealing with the Excel Macro Toolbar, developer tools, XML and the document panel
• Global Macros