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

Certification Awarded

Includes Free Online Course

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

To get Dates, Pricing & more info please enter your details and we will get back to you.

Or call us on 01 434 7600 

Course Enquiry

  • This field is for validation purposes and should be left unchanged.

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

IACT - Ireland's leading training provider