International experts in Excel

Your power-packed agenda  

Use best-practice database model structure

• Learn the advantages for many applications of one big database sheet feeding multiple reports.
• Filter database sheet data.
• Recap the six data-sheet types.
• Discover the free ESPMini add-in to help standardise your worksheets.

Build a multiple-units reporting application

• Create a simple structure of database sheet and report sheets.
• Code the data to allow filtering, easy report creation and PivotTable reporting.
• Easily create reports in any layout.
• Consolidate multiple units into one database sheet.
• Drill down from a report cell to see values summed on the database sheet.
• Add crosschecks to reports and locate missing data.
• Locate data with filters.
• Learn concatenation and the SUMIF, INDEX and MATCH functions.

Add PivotTable reports

• Create PivotTable reports from the database-sheet data of the above application.
• Stop PivotTables from changing column widths on refresh.
• Drill down to see items totalled.
• Customise the content and layout.
• Switch report to show values as a % of total.

Create charts for accountants

• Create a basic chart.
• Build a Bridging/Waterfall chart.
• Know how to build a Gantt chart.

Create succinct, readable dashboard reports

• Convey key information clearly and graphically in one concise report.
• Combine your tables and charts into a single dashboard report.
• Use linked pictures to fit varied tables into a maintainable report.
• Highlight above- and below-target items with traffic-light charts and on-sheet indicators.

Learn date & time calculations

• Calculate working days between two dates.
• List the dates of working days.
• Create a calendar that has weekends and holidays automatically coloured.
• Display month-year column headings that change automatically with start date.
• Discover accurate calculation of age in months.
• Calculate the difference between start and end times.
• Total times beyond 24 hours.
• Input and format minutes and seconds.
• Return week number from a date, month number from name and vice-versa.
• Learn how to use Conditional Formatting.
• Issue files with Analysis ToolPak functions.
• Learn functions NETWORKDAYS, WORKDAY, WEEKDAY, WEEKNUM, ROUNDUP, YEAR, MONTH, DAY, DATE, DATEVALUE, TEXT, OR, NOT, MATCH and ISNA.

Build a fixed assets register and learn DSUM

• Pick up a super-flexible fixed assets register that works from any past or future start and end dates.
• Learn the depreciation formulae.
• Know how to modify for different depreciation policies.
• Create advanced criteria for DSUM.
• Learn functions DSUM and TEXT.
• Discover the free ESPMini add-in to quickly freeze and unfreeze panes.

Discover more great time-saving tips

• Widen a column to best fit a selection of data.
• Fill in missing headings in imported data.
• Don't get muddled in Edit, Enter or Point mode.
• Use the Fill Handle to clear, insert and delete.
• Hide zero values.

Who should attend:

• Accountants (and many non-accountants, too) who have been using spreadsheets for some time and are quite familiar with the basics of formula construction, formatting and printing.
• The seminar is designed for those working in accounting: it is not essential for delegates to be qualified accountants. Though the course uses common accounting applications, the techniques can be applied widely.