Be empowered with the knowledge and skills necessary to be proficient in maintaining spreadsheets, by creating files faster by avoiding wasted time from lack of specification
Intermediate
1 Day Course
Certification Awarded
Online Resources
Course overview
- 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.
Skills you’ll learn
- Design better looking spreadsheets
- Standardise your workbooks
- The Dos and Don’ts of spreadsheet design
- Improve readability
- Use powerful formula auditing techniques
- Foil attempts to conceal data and formulas
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 this 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.
Certification
- At the end of the course, you’ll be Certified by the Irish Academy of Computer Training.
- CPD Certification
Requirements
- Experience with Excel is essential
- Software is provided in the Classroom and Live Virtual options
Learning paths
- Progress to our Excel Master Diploma
- Part of IACT’s Microsoft 365 Learning Path
Why choose IACT
- Ireland’s leading training provider for 30 years
- Full-time Certified Trainers who use the software every day
- Smaller class sizes to accelerate learning
Course curriculum
Section 1: Spreadsheet Risk
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
Section 2: Achieving Good Structural Design
- Preparation and Outline: decide, list and divide
- The following sheets are essential to spreadsheet design
- An Index sheet
- Documentation
- User instructions
- Versions and release history
- Tasks and checklists
- Assumptions and conventions
Section 3: Input Controls and Design Rules
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
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
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
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
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
Contact us.
We'll reply quickly.
Enquiry
I understand that my personal data is being processed in accordance with the privacy notice and accept the terms and conditions of use.