98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsExcel Best Practice
Face to face / Online closed & onsite training. Restaurant lunch included at STL venues.
- 1 day Instructor-led
Syllabus
Who is this course for?
All Excel users who want to save time and be more productive throughout their organisation by learning best practice standards and techniques. Courses can be delivered at client sites UK wide, also at our Excel course Bloomsbury and Limehouse venues.
Prerequisites
Excel Intermediate
Benefits
• Work efficiently with Excel by setting standards and best practices• Save time by setting standards for workbook planning and design
• Learn how to best organise Excel source data for reporting and analysis
• Learn how to make tables and lists dynamic
• Build worksheets that are easy to understand and amend
• Learn to use the right Excel tool for the task
• Learn tips for how to fine tune and speed up workbooks
Course Syllabus
Setting standards and best practices
The FAST Standard Organisation
Is Excel the right tool for the job?
Do's and Don'ts
Planning a Workbook
Identifying the audience and co-users
Recognise inputs, workings and outputs
Creating consistency and durability
Designing a Workbook
Including a welcome and introduction sheet
Creating clear organisation of worksheets
Organising source data
Keeping it simple by choosing easy to use tools
Best Practices
Best practice when writing functions and formulas
Formatting and use of colours
Conditional formatting best practice
Use of tables and lists
When to use range names
Making tables dynamic
Working with Links
Best practice with linking worksheets and workbooks
Managing external links
Distributing Reports
Best practice for sharing reports
Using Shared folders versus sending attachments
Protection
When to protect worksheets and workbooks
Making a workbook read only
Version control
Naming of workbooks and folders
Workbook version control
Excel version control
Tips and suggestion
For keeping down the file size of workbooks
Making workbooks easy to amend
What you get
"What do I get on the day?"
Arguably, the most experienced and highest motivated trainers.
Face-to-face training
Training is held in our modern, comfortable, air-conditioned suites.
Modern-spec IT, fully networked with internet access
Lunch, breaks and timing
A hot lunch is provided at local restaurants near our venues:
- Bloomsbury
- Limehouse
Courses start at 9:30am.
Please aim to be with us for 9:15am.
Browse the sample menus and view joining information (how to get to our venues).
Refreshments
Available throughout the day:
- Hot beverages
- Clean, filtered water
- Biscuits
Online training
Regular breaks throughout the day.
Learning tools
In-course handbook
Contains unit objectives, exercises and space to write notes
Reference material
Available online. 100+ pages with step-by-step instructions
24 months access to Microsoft trainers
Your questions answered on our support forum.
Training formats & Services
Training Formats & Services
Training formats available
|
Testimonials
Government Actuary's Department
Sara Sanchez,
Project Manager
Great trainer! absolutely loved him
Excel Best Practice
Southeastern
Matthew Smith,
Assistant Management Accountant
Jens was extremely passionate about Excel and helped us to get to grips with some basic techniques to make us more efficient with our time.
Excel Best Practice
Southeastern
Ronak Shah,
Management Accountant
WOW ! Simply fantastic, Jens is a fantastic trainer. My knowledge was at a good level but I felt I learnt soo much with Jens today. This course is a fantastic way to learn how to become more efficient when using excel. Jens enthusiasm was the highlight of today's session ! Thank you Jens!
Excel Best Practice
Training manual sample
Below are some extracts from our Excel Best Practice manual.
Is Excel the right tool for the job?
Excel worksheets has 1048576 rows and 16384 columns. There is no reason for Microsoft to give us all these rows and columns because Excel cannot handle this amount of cells. Excel is a spreadsheet for analysis and calculations not a database.
4 Do’s and Don’ts
1. Do plan your spreadsheet
Spending time planning a new spreadsheet is a very good investment. Too many waste a huge amount of time because of bad or no planning when it comes to spreadsheet design. Spreadsheets must be structured right to be usable.
2. Do focus on Output before Input
Don’t focus on the input creating spreadsheets but the output. Too many want it to be easy to enter the data in the spreadsheet and make the spreadsheets for the eye not for output. Do we need to use PivotTables to analyse the date we must have the source data in a list not in a tabular table. If we want to query the data from an external workbook the source data must be in a list starting from A1.
3. Don’t Merge cells
Merged cells in Excel can cause limitations in the spreadsheets. You have tools in Excel to provide you with the same effect as merge cells can do, without causing any problems.
4 Don't Leave Blank Rows or Columns When Entering Related Data
KEEP RELATED DATA TOGETHER
Leaving blank rows or columns in data tables or related ranges of data can make it very difficult to properly use a number of Excel's features such as charts, pivot tables, and certain functions.
2. Planning a Workbook
Identifying the audience and co-users
Make sure that you identify the audience and co-users when planning a new workbook. If the workbook needs to visualise data in a report/dashboard make sure that your audience commit to the expectations of what they expect to be able to see in the report.
Recognise inputs, workings and outputs
Keep inputs, workings and outputs strictly separated.
Creating consistency and durability
A high priority when creating a new workbook should be to decide on a set of rules and stick to them throughout the whole workbook to keep the consistency. This can avoid many problems later in the workbook’s lifecycle. Make sure that new workbooks are also created for future durability.Include a welcome and introduction sheet (see below)
Creating clear organisation of worksheets
Clear organising of the worksheets within your workbook can make your Excel workbooks easier to maintain, manage and to understand for co-users.
The Input Sheet:
The input sheet should only contain the source data and nothing else. The input data should be organised in a list and not a tabular table. The list should be formatted as an Excel table which will save you a lot of time when new data is added.
Best practice when writing functions and formulas
· Limited use of nested functions
You can nest 60 functions in Excel. Nested functions make it much more difficult to understand Excel and increases the chances of making mistakes. Sometimes you have to nest functions to get the output you need, but many times it is not necessary.
· Double check functions and formulas to make sure they return the right answer
In estimated 80% of all workbooks there are errors which cost companies a huge amount of money. Avoid this by testing the calculation before you start using the workbook.
· Use the right functions
Many Excel users use Vlookup. This can slow down the computer’s memory and can take several minutes to open the workbook. The combination of Index and Match (and indeed the Xlookup) can do the same as Vlookup and is much faster.
Conditional formatting best practice
Conditional formatting is very useful to spot trends, useful information and errors.
Use of tables and lists
Only use tabular tables on the Output sheet (the report or the dashboard) and never on the Input sheet. Always have the source data in lists!
Use range names
Best Practice is to assign a descriptive name to a cell range. This makes it easy to remember the contents of the range. Your functions and formulas will be much easier to understand =Vlookup(StaffId,StaffList,2,False). The function looks up the staff id in the staff list.
Converting data ranges to dynamic tables.
This tool is probably the most important tool in Excel and should be used for all source data in workbooks. This will save you a lot of time and your formulas and functions will be much easier to read and understand. All tools used in your worksheets will automatically update when you add or change the source data.
What is a Table?
In Excel, a table is a specially designated range of numbers. This special range of numbers has added functionality that other cell ranges do not have. You can have more than one table in a workbook or worksheet if you want, and tables can be as large or small as the amount of data you want to work with. Here is an example of a Table below: