Excel Training Class

Working with Advanced MS Excel Functions

Excel provides the tools necessary to expedite data analysis using single or multiple conditions or variables. This might be as simple as true/false values or as complex as, for example, finding individuals who have been employed for more than two years and earn less than $50,000. In this training course, students learn how to work with advanced Excel functions, how to use the IF and nesting features, and how to create multiple condition formulas with the IF function. Lookup functions let Excel users find any value entered in a designated table. To perform these tasks, training course attendees learn how to use table lookup and text functions, and use one-input and two-input data tables.

Analyzing Data

Excel simplifies and automates complex data analysis, helping users organize information into easily manageable groups, combine data in the same cell across several ranges or combine row or column data with matching labels across several different layouts and ranges. MS Excel also allows for subtotaling to make long lists more manageable and advanced filtering so viewers can see only relevant data. In this Excel training course, students learn how to perform these tasks with hands-on instruction in the use of automatic outlining, consolidating information by position or category, inserting subtotals, creating advanced filters and using database functions.

Using MS Excel PivotTables

Popular with accountants, MS Excel PivotTables offer a convenient, multidimensional view for data reporting, sorting and summation completely independent of the original data layout. PivotTables let users drag and drop column headings and move data around freely. In this Excel training class, students learn the basics of PivotTables, how to use and design them. Course attendees walk through set-up using the MS Excel PivotChart Wizard and design a PivotTable on a Worksheet. This class also shows students how to modify Excel PivotTable fields, work with page fields and refresh a PivotTable. Delving deeper into enhancement features, course attendees learn how to format a PivotTable, work with summary functions, sort items within a PivotTable, group data and use a calculated field.

Enhancing MS Excel PivotTables

Once a PivotTable is designed, users can enhance its look and work with the data compiled. In this training course, students learn how to format a table, work with Excel summary functions and sort various items in their PivotTable. Class attendees also lean how to group data and use AutoShow, which displays a given number of field items based on values in a table’s data area. Course attendees walk through the steps in creating a chart and a calculated item from a PivotTable, or a user-created formula that determines an item’s value in a given field.

Working with Excel’s Data Analysis Tools

MS Excel makes it easy for users to create ‘what if’ scenarios, saving various data scenarios for review and assessment either individually or side-by-side. This is useful when developing items like budgets or sales forecasts. In this training class, students learn how to use Microsoft Excel to create multiple scenarios. They also find out how to work with each, use Goal Seek, understand Solver, define and solve a Problem. Training course attendees then walk through the various steps to generate results and alternate solutions reports.

Working with Excel Macros

MS Excel Macros automate repetitive tasks. Instead of repeating multiple steps over and over, users can record a macro once and play it each time they want to conduct the same task. In this training course, students learn how to record and execute a macro. They also learn how to create and work with macro buttons.

Using Excel’s Special Features

At some point, users might need to import or export spreadsheet data to or from a database, customer management application or other data repository. Training course attendees learn how to easily import and export data to and from a workbook. They also learn how to add and work with hypertext links.

Using Excel’s Shared Workbook Features (Optional)

Excel’s optional shared workbook feature provides a flexible means for groups to collaborate and allowing multiple list or data management sources. In this training course, students lean the basics of Excel’s shared workbook feature. They also learn how to work with shared workbooks, configure a workbook for shared use, highlight tracked changes, work within a shared workbook and review workbook changes. Course attendees also learn how to remove a workbook from shared use and merge multiple workbooks into one.

Prerequisites:

• Working experience with Microsoft Excel.