One of the most common uses of Excel is to extract data from other computer systems, manipulate the data and then produce reports from it. That’s what this course is all about. There are many hurdles in those steps, but you can benefit from AbleOwl’s vast experience to overcome the problems, discover the best tools for the job and learn the tricks to speed it all up.

Use Text Import Wizard for fixed-width and delimited text files

• Know the different types of text files.
• Identify whether a file is text or binary.
• Keep leading zeroes.
• Ensure that dates import correctly.
• Be sure that Windows settings are correct.
• Split CSV files with the Wizard.
• Keep the settings for a repeat next month.
• Append formula columns.

Solve text import problems

• Cope with negatives on the right.
• Convert numbers to text and vice versa.
• Deal with multiple record types.
• Know how to keep leading spaces.
• Solve the problem of decimal separator being a comma.
• Use formulas to split data.
• Use formulas to concatenate data.
• Learn the functions for string manipulation: IF, LEFT, MID, RIGHT, ISTEXT, FIND, SEARCH, TRIM, VALUE.
• Import files that have more records than Excel has rows.

Extract directly from source

• Get data from Access, SQL Server and any other source that has an ODBC driver.
• Use MSQuery to create criteria to select data.
• Have your queries update as you change criteria stored in your workbook.
• Change the file queried.
• Use Excel 2007 Tables with your queries.

Use manipulation tricks

• Complete missing entries.
• Remove replicated entries.
• Transpose.
• Sort by more than three columns.
• Sort by non-sequential items such as month names.
• Divide by 1000.
• Count the number of entries in each category.
• Have alternate rows shade themselves.
• Find out which 'number' entries are text and which are number.
• Unlock the power of Conditional Formatting.

Split a column

• Split a column at the location of a certain character.
• Split a list into two or more lists.
• Extract, for example, the third number between dashes.

Convert and clean

• Convert text to number and vice versa.
• Convert to upper, lower and proper case.
• Convert dates in different formats to Excel dates.
• Remove certain characters.
• Convert imported minus on right.
• Learn functions UPPER, LOWER, PROPER, CLEAN and SUBSTITUTE.

Check records

• Check for new records.
• Join two Excel tables with formulas.
• Check for missing records.
• Match the data in one table to data in another.
• Locate duplicates.
• Quickly shade duplicates In Excel.
• Learn functions VLOOKUP and ISNA.
• Avoid problems caused by spaces.
• Discover the COUNTIF function.

Filter

• Use AutoFilter to display certain records of a table.
• Format or delete selected records.
• Discover Advanced Filter for tasks that AutoFilter cannot cope with.
• Build any filter criteria you might need.
• Know how to use wildcards in criteria.
• Remove unwanted records from imported print files.
• Create a unique list of items from a column of entries.

Subtotal

• Insert subtotals.
• Make subtotal numbers bold too.
• Work with Outlines to hide or show report detail.

Advanced formulas

• Total or count just certain specified records.
• Discover array formulas for conditional summing.
• Use functions SEARCH, SUMIF, VLOOKUP, DSUM, INDEX, MATCH, COUNTIF, DCOUNT, and AVERAGE.
• Discover the new conditional functions introduced in Excel 2007: SUMIFS, COUNTIFS and AVERAGEIFS.

Create cross-tabulation reports

• Master all aspects of PivotTables, no matter which Excel version you use.
• Discover advanced PivotTable features including calculated fields, grouping and the GETPIVOTDATA function.
• Stop PivotTable column widths changing on refresh.
• Add PivotCharts.
• Manipulate PivotTables to show different dimensions on different axes.
• Include variance calculations.
• Insert % difference into PivotTable.
• Group PivotTable data by week, month, quarter, hour, quarter hour or minute.
• Know the pros and cons of PivotTables.
• Carve up your data with Excel 2010's Slicers.

Learn great shortcut tips

• Throughout the two days, discover lots of great Excel shortcuts and handy features that few people know about.

Prerequisites

Regular Excel users who have been working with financial, production or administrative data/databases for some time and who are familiar with the basics of simple formula construction, formatting and printing.

• Be assured the advice you get is well-proven and utilised.
• Feel free to ask questions.
• Get time away from the office and constant interruptions.
• Enjoy a friendly environment where you don’t feel you’re holding others up.
• Instantly apply what you’ve learned on return to work.