Select Page
Home » Courses » Office Productivity » Microsoft Excel Courses » Spreadsheet Safety, Structure and Design
Spreadsheet Safety, Structure & Design Course
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
g

Intermediate

1 Day Course

Certification Awarded

Online Resources

Learning Options for this Course

Classroom

This course can be taken at our Training Centre in Dublin 2. Socially distanced learning. Full interaction with expert trainers.

Virtual Classroom

Daytime or evenings using our iLiveLearning platform. Full interaction with the trainer – just like in the classroom. Software provided.

Self-paced eLearning

Self-paced online course with Assessment and Certification. Learn anytime, anywhere from any device. 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
  • Improve readability
  • Use powerful formula auditing techniques
  • Foil attempts to conceal data and formulas

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.

Requirements

Experience with Excel is essential or are familiar with Microsoft Office Specialist Expert Excel.

Certification

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

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

For Dates, Pricing & more info, please enter your details and we'll get back to you quickly.

Or call us on 01 434 7600 

Course Enquiry

  • Agreement for us to contact you

  • We'd love to keep you informed of useful, relevant offers and services. This may be via email or phone.
  • This field is for validation purposes and should be left unchanged.

*Required info. We treat your data with care, please see our Privacy Notice for more details

IACT - Ireland's leading training provider