1 Day Course
Learning Options for this Course
This course can be taken at our Training Centre in Dublin 2. Socially distanced learning. Full interaction with expert trainers.
Daytime or evenings using iLiveLearning. Full interaction with the trainer, just like in the classroom. Software provided.
Self-paced online course on our Learning Hub with Assessment and Certification. 1 Year access. Software required.
Skills You will have at the end of the Course
Design better looking spreadsheets
Standardise your workbooks
The Dos and Don’ts of spreadsheet design
Use powerful formula auditing techniques
Foil attempts to conceal data and formulas
This course is essential to overall design and structure, and to develop safe and secure automated spreadsheets. It begins with the process of design, the use of appropriate index and documentation sheets, user information, security, backup, protection, and the development of conventions, and formats. You will also learn ways to identify and correct errors by using the formula auditing tools and few other tricks.
Experience with Excel is essential or are familiar with Microsoft Office Specialist Expert Excel.
At the end of the course, you’ll be an Excel Expert – Certified by the Irish Academy of Computer Training
Who would benefit
Anyone wanting to learn how to redesign and restructure their spreadsheets, to ensure they are easy to follow, update and maintain, and at the same time reduce the potential for errors making future development easier.
Why take the Course
On completion, you will be able to produce reliable and secure spreadsheets for data intensive industries. You will acquire an in-depth knowledge of formatting tools and the power of security settings. Your spreadsheets will be easy to maintain, review and audit.
Online Option Available
Section 1: Spreadsheet Risk
- Spreadsheets; a tool to organise data
- What are the risks of using Excel?
- What can be done about it?
- Recognising a good spreadsheet model.
- People involved in the building of a Spreadsheet
Section 2: Achieving Good Structural Design
- Preparation and Outline: decide, list and divide
- The following sheets are essential to spreadsheet design
- An Index sheet
- User instructions
- Versions and release history
- Tasks and checklists
- Assumptions and conventions
Section 3: Input Controls and Design Rules
A spreadsheet is only as good as the data that feeds it.
- Rules of design
- Data validation
- Natural flow of a spreadsheet
Section 4: Calculations
- Automatic and manual mode
- What is precision as displayed and its impact?
- Using the ROUND function, instead of formatting.
- Formula Tips
- Understanding Order of Precedence
- Constants and how they should be stored, and used in formulas.
- The importance of cell referencing
- Naming ranges
- Rules for naming cell ranges
- Array formulas; what are they and we recognise them?
- Intrinsic error checks; using IF statements
- Error Handling using IFERROR | ISERROR | ISNA in conjunction with VLOOKUP/HLOOKUP or INDEX and MATCH functions, as well as with conditional formatting.
- Consolidating worksheets using the 3D formula
- Creating and maintaining links between spreadsheets.
Section 5: Protecting Your Data
- Locking and unlocking cells
- Hide and unhide sheets, columns and rows
- Hiding formulas from external aggressors
- Protect cell content
- Protect sheet structure
- Password protect the file.
Section 6: Auditing and Debugging
- Display all formulas using a keyboard shortcut
- Use the FORMULATEXT function
- Use the formula auditing tools in Excel
- Checking for macros
- Setup independent reviews – second pair of eyes.
- Identify and correct errors.
- Explanation of some common errors; such as circular references and how to locate them
Section 7: The Final Report
- Formats and Settings
- If using charts; some tips and tricks
- Reveal hidden data using Find and Replace and the Paste Special
- Printing Options