International experts in Excel

Your power-packed agenda  

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 formulae to split data.
• Use formulae 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 critera to select data.
• Have your queries update as you change critera stored in your workbook.
• Change the file queried.
• Create and edit SQL queries.
• 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 formulae.
• Check for missing records.
• Match the data in one table to data in another.
• Locate duplicates.
• Quickly shade duplicates in Excel 2007/2010.
• 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 formulae

• Total or count just certain specified records.
• Discover array formulae for conditional summing.
• Use functions SEARCH, SUMIF, VLOOKUP, DSUM, INDEX, MATCH, COUNTIF, DCOUNT, and AVERAGE.
• Discover the new conditional formulae 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.

Who should attend:

• 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.