Overview
What does the course cover?
Spreadsheets are widely used for reporting, tracking and administration functions across most organisations. We rely on Spreadsheets and trust the results they calculate. We make decisions on the information contained in them and require them to be correct for audit and compliancy purposes.
The Spreadsheet Structure and Design course was designed to provide simple rules and templates for the structure and of reliable and robust. The goal is to help create sustainable and reusable Spreadsheets within an organisation that can be updated and supported by new users providing they have a foundation skill in Excel.
Aims
Why take this course?
This course will cover the design fundamentals for creating and structuring a workbook, including features such as named ranges, VBA hyperlink code and using a control sheet. It will also include how to best protect your worksheets and workbooks and how to audit formulae in a worksheet checking for errors and linking to external sources. Finally, it will cover the preparation for testing and reviewing workbooks including checking for Macros, refreshing pivot tables and using the right type of charts for your data.
Audience
Who would benefit?
This course is perfect for those that are using Excel as part of their daily routine and find that they are having to share their spreadsheets with other users or have been given spreadsheets created by others and need to find their way around it. A level 2 standard of Excel is a prerequisite.
Course Details
Section 1: Introduction
- Lesson 1: Course Overview
Section 2: Design fundamentals
- Lesson 1: Working with Sheets
- Lesson 2: Relative and Absolute Referencing
- Lesson 3: Names Ranges
- Lesson 4: Conditional Formatting
- Lesson 5: Options when Calculating
Section 3: Structuring your workbook
- Lesson 1: Index Sheet with button hyperlinks
- Lesson 2: Index sheet with VBA Code
- Lesson 3: Documentation
- Lesson 4: Instructions
- Lesson 5: Control Sheet
- Lesson 6: Creating an effective summary sheet
- Lesson 7: Issues
- Lesson 8: Printer friendly report
Section 4: Password Protection
- Lesson 1: Protect Sheet
- Lesson 2: Protect Workbook
- Lesson 3: Locked and Unlock cells
Section 5: Formula auditing & Link Updates
- Lesson 1: Identifying and Correcting Errors
- Lesson 2: IFERROR
- Lesson 3: Working with Links
- Lesson 4: Linking to external sources
Section 6: Preparing for Testing and Review
- Lesson 1: Formatting & Display options
- Lesson 2: Checking for Macros
- Lesson 3: Checking PivotTable Refresh
- Lesson 4: Opening options
- Lesson 5: How to choose the right chart type
Want to start learning today?
Joining is simple, easy and risk free with our 7 day money back guarantee.
