Excel 2007 Advanced Course

Advanced Functions in Excel

The training starts by taking you deeper into Excel's powerful data calculation capabilities. You will go beyond simple sums and use different types of targeted functions to get the answers you need. This section of the course covers how to:

• Automate your work with logical functions (IF, AND, OR, NOT, IFERROR) that calculate values based on criteria you specify
• Apply math and statistical functions to calculate conditional sums and averages
• Use financial functions, such as the PMT function, which calculates loan payment amounts

V Lookups and Data Tables in Excel

This section of the Advanced Excel training class introduces built-in Excel functions for extracting specific values and information from ranges of data. Lookup functions help you find corresponding values in different rows or columns, for example, the fifteenth employee's fourth quarter earnings. And data tables make it easy to present a series of results based on different input values, for example, to determine how the final price of your home will be affected if you refinance your mortgage. You will learn how to:

• Create lookup functions to find exact or approximate matches in lists of data
• Use the MATCH function to determine a specified value's relative position in a list
• Use the INDEX function to return a value based on a specified cell in a range
• Create "what-if" scenarios with one-variable and two-variable data tables

Advanced List Management in Excel

Expand your ability to manage data effectively within Excel. Data validation reduces errors by restricting the values that may be entered into cells. And Excel's database functions allow you to extract information from your spreadsheet data, just like you would from a database. In this part of the Advanced Excel course, we will show you how to:

• Set up cells to accept only valid dates, whole numbers, or other valid values you specify
• Use database functions to summarize values that meet complex criteria, for example, the total sales in a specific region for a specific year

Excel PivotTables and PivotCharts

Heard about them, but still mystified? Let us solve the mystery! We'll show you how to use PivotTables to explore different relationships among data elements, helping you quickly make more informed decisions. And we'll also show you how to create graphical representations of the PivotTable relationships using PivotCharts. You will leave the Advanced Excel 2007 training class knowing how to:

• Create and use interactive PivotTables to analyze and compare large amounts of data from your workbooks or from external databases
• Make a PivotTable easier to read with effective automatic formatting
• Create a PivotChart to graphically display PivotTable data

Exporting and Importing within Excel

XML is one of the most popular formats for data transfer, supported by most modern data-related applications. We'll provide in-depth exploration and hands-on experience with Excel 2007's XML-related features. We'll also show you other ways to save time by getting data into and out of Excel fast, such as text files and the Microsoft Query and Web Query features. You will learn how to:

• Export your Excel data to a text file that can be read by other programs
• Import data from a text file and organize it automatically into appropriate columns
• Easily remove duplicate rows from your imported data
• Create an XML map that maps your Excel data to an XML schema
• Use Microsoft Query to access data in external databases
• Set up a Web Query to automatically pull data such as online currency quotes or stock quotes from the web

Analytical Options

Excel can help you make informed decisions with built-in data analysis tools like the Goal Seek and Solver utilities, the tools in the Analysis ToolPak, and the Scenario Manager. A simple example is using the Solver utility to find the right price to offer for a car so that your monthly car loan payment would be an exact dollar figure that you can afford. In this exciting part of the Advanced Excel training, you will discover how to:

• Perform a simple one-variable "what-if" analysis using the Goal Seek utility
• Perform complex "what-if" analyses with multiple variables using the Solver utility
• Expand your data analysis capabilities with the tools in the Analysis ToolPak, including Random Number Generation, Histogram, Regression, Sampling tools and many more
• Simplify your budget planning by creating and easily switching between Scenarios
• Save time by saving Views with different worksheet display and print settings

Macros & Custom Excel Functions

Save time and reduce errors by automating your routine Excel 2007 tasks. Refine your mastery of macros and make yourself indispensable! You can customize and automate any procedure or calculation which you find yourself repeating regularly. You will learn how to:

• Determine when a macro makes sense
• Record and run macros that work for you
• Enable and disable macros in a spreadsheet
• Make changes to existing macros using the Visual Basic Editor
• Create custom functions for your business, for example, a Profit function with sales and cost arguments

Conditional Formatting and SmartArt

Don't underestimate the power of visual design and formatting in presenting data clearly to your stakeholders. And you don't have to do it all by hand. Wrap up your Excel 2007 Advanced training by finding out how to apply automated conditional formatting and how to use SmartArt graphics. We'll teach you how to:

• Use data bars or icon sets to graphically display how different cell values compare to each other
• Communicate with color by setting up color scales to automatically format cells depending on their values
• Get your message across by inserting and customizing SmartArt graphics

Prerequisites:

• Working experience with Microsoft Excel.

Advanced Excel courses are offered at four locations::

• Advanced Excel course San Francisco
  
• Advanced Excel course Los Angeles
  
• Advanced Excel course San Jose
• Advanced Excel course Sacramento