Microsoft Excel specialists

Excel training courses

See dates of all courses
Contact us

  








  WHAT YOU GET

Course manual
& Excel files

40  minutes’
hotline time

Excel expertise
30 years’
specialisation

Conventions

Conventions for clarity, error reduction and productivity

     Duration: 2 days  
      Format: Hands-on, computer provided  
   Arrival and refreshments: 8:45am  
    Start 9:00am  
    Finish: 4:30pm  
  YOUR POWER-PACKED AGENDA
If you open a book, you can find your way around it. If you open a workbook, you probably can’t. That’s because the book has many conventions applied and the workbook does not.

30 years ago, spreadsheets revolutionised the computer world, changing application development away from being the sole preserve of the IT department to mostly that of all sorts of people in all sorts of jobs. However, little has been done to manage that massive resource, and, so, the quality and productivity of that activity is much lower than it should be.

The use of spreadsheets is still in its infancy, much like in the early days of cars when few road rules and standards existed. Like cars, spreadsheets can be dangerous, but the benefits outweigh the disadvantages; there is no practical alternative for over 90% of business applications. As happened with cars, you can mitigate the risks and greatly improve efficiency.

Improvements never end. Before long, cars will become safe and run off carbon-free energy. There are massive economies of scale to progress that faster. The same economies of scale apply to spreadsheets, which will follow the same improvement path.

There are benefits to individuals who apply conventions, but there is a step up in benefits if the whole department drives on the same side of the road.

Set up Excel in a standard, optimal way

  • Change settings
  • Attach the GenieMini add-in
  • Set up a somewhat-standardised Quick Access Toolbar

Adopt conventions for layout of a sheet

    Discover the benefits of range names
  • Build comprehensible, reliable formulas quickly
  • Modify the range of a range name
  • Delete a range name
  • Make range names easy to find
  • Start the visible display at K11 rather than A1
  • Toggle Freeze Panes instantly
  • Understand workbook- and worksheet-scope range names
    • Format quickly and consistently with styles
  • Solve the cell formatting problem
  • Change the default workbook and worksheet
    • Set a sheet’s structure and formatting conventions
  • Position headings
  • Give everything a heading
  • Put headings that uniquely identify the sheet at the top left
  • Shade the headings and column titles range
  • Put sheet headings in a hierarchy of movement
  • Centre column headings in the bottom row of titles range; wrap if needed
  • Use shading in column titles range to group columns
  • Fill with shades of the same colour
  • Don’t merge cells; centre across selection
  • Indent to show the side splits
  • Utilise borders, gutters and totals
  • Minimise the use of horizontal and vertical lines
  • Put the subtitle text in the total row
  • Use GenieMini to include subtotal formulas
  • Place totals at the bottom
  • Identify input cells with a blue font
  • Do not underline
  • Use sentence case
  • Document cells with comments, text boxes and in-formula text
  • Apply standard page-setup settings

Adopt conventions for layout of sheets within a workbook

  • Structure the sheets of a workbook for clarity
  • Create a sheet for settings
  • Document for ease of use and maintenance
  • Create step-by-step procedures
  • Keep procedure steps on display as you follow a task
  • Create a flow-of-data diagram to enable comprehension
  • Compare a non-standardised workbook to a standardised one
  • Include Lists and Welcome sheets
  • Include a Welcome ‘start here’ sheet
  • Include a Menu sheet when there are many sheets
  • Hyperlink back and forward
  • Do not have overlap; split into sheets
  • Use sheet names and tab colour to group related sheets
  • Align rows and columns across sheets to allow quick navigation and changes
  • Synchronise sheets’ top left and display

Manage files

  • Understand and use different file types
  • Access files and folders efficiently
  • Save common file locations as Favorites
  • Apply conventions to manage file versions
  • Recover data and safeguard against potential crashes
  • Restore a previous version of a file or folder

Adopt charts conventions

    Select the appropriate chart or table
  • Determine whether a chart, table or both is the best way to communicate
  • Know which chart types to use and which to avoid
    • Arrange chart elements in the best way
  • Split dimensions into multiple charts
  • Place labels at the ends of lines
  • Align legend to series order
  • For dual axes, do not have lines that cross at a point of no significance
  • Learn how to use colour
  • Include titles
    • Remove distractions
  • Avoid fancy fills
  • Remove gridlines
  • Avoid colour distractions
  • Remove axis tick marks
  • Don’t smooth
  • Don’t mark
  • Remove chart border

Adopt Tables conventions

  • Apply conventions for Tables, PivotTables, Slicers and Timelines
  • Use Tables to make formulas and other features always refer to the correct ranges

Prerequisites

Familiarity with formatting, creating basic formulas and creating basic charts.
Applies to all Excel versions including 2016, 2013, 2010 and 2007. Any differences are pointed out.
Note that the course makes use of the free GenieMini add-in. No installation other than copying files to a folder is needed. The add-in is no different from an Excel file that contains macros. While GenieMini is not essential to apply conventions, taking advantage of the applied conventions is greatly enhanced by the add-in.


  LEARN IN A POSITIVE ENVIRONMENT
 
  • Be assured the advice you get is well-proven and utilised.
  • Feel free to ask questions.
  • Get time away from the office and constant interruptions.
  • Enjoy a friendly environment where you don’t feel you’re holding others up.
  • Instantly apply what you’ve learned on return to work.

Presenters

AbleOwl has a number of different presenters, all of whom are Excel specialists.

See dates of all courses  

  COURSE CANCELLATION POLICY
 
A course booking is considered final when received in writing (online, email, fax or post) by AbleOwl, and can be cancelled up to ten working days before the course. After that, payment is expected in full and no refund will be given. All cancellations must be notified in writing, i.e. post, fax or email. Another delegate may be substituted at any time.