Planning the Design and Functionality of Spreadsheets

Spreadsheets are an extremely useful tool for many different business applications. They can be customised to perform various activities and formatted to represent your brand. With so many possibilities, planning the design and functionality of a spreadsheet is instrumental to the user experience.

In this topic, we will explore the key elements of spreadsheet planning, including task and organisational requirements, layout and formatting.

By the end of this topic, you will understand:

• the critical elements of planning for a spreadsheet and why they matter
• the importance of identifying relevant policies and procedures
• how to use the basic design features of a spreadsheet to enhance readability
• how to use a spreadsheet's 'search' function to receive instructional information to complete tasks.

Watch the following 12-minute video to learn about the essential functions of spreadsheets across a variety of different programs. This video will give you a great overview of some of the functions we will examine and put into action later on.

To create a good spreadsheet that is fit for purpose and consistent with organisational policies and procedures, you will need to plan how the spreadsheet will be designed. To do this, you may ask yourself the following questions:

What is the purpose of the spreadsheet?

Understanding the purpose of the spreadsheet is critical to its design. For example, will it be used in-house or published on a website? These factors may impact what data is displayed (for example, sensitive or confidential data may be withheld if the spreadsheet is publicly available) as well as the format of the spreadsheet.

Who will use the spreadsheet (the audience)?

The audience for the spreadsheet depends on the type of information to be displayed. For example, suppose the spreadsheet is to be used to update board members of an organisation on the financial status of a business. In that case, the spreadsheet will likely be formatted to show financial highlights and outcomes. In contrast, if an accountant used the spreadsheet to work out the business's financial status, there would be a lot more information within the spreadsheet to allow the accountant to produce the report. Who will update the spreadsheet? Understanding who the end-user is, or who is accountable for updating the spreadsheet, is important to its design for two main reasons:

• The spreadsheet will need to be formatted in a way that is easy for the person to understand, whether that be a single subject matter expert solely responsible for updating the spreadsheet or an entire business where many people are responsible for updating the spreadsheet.
• The spreadsheet will need to contain appropriate security and quality controls to ensure the integrity of the data. If multiple people have access to and are required to update the spreadsheet, this heightens the risk of incorrect usage of the spreadsheet as well as data entry errors and mistakes. In this case, it is recommended that the spreadsheet contain parameters to limit incorrect usage.

Consulting stakeholders

Anyone in a business who can affect or be affected by your actions is a stakeholder. Stakeholders are usually divided into internal and external stakeholders. As the definitions suggest, internal stakeholders are stakeholders found within the company, and external stakeholders are outside the company but are concerned with its activities.

When planning any business document, its aims, objectives, and functionality need to be clarified by stakeholders. Listening to the requirements of the people in your business can help to enhance the design, quality and outcomes of Excel documents. Your stakeholders will help you identify the business needs and expectations for the Excel documents you build and provide feedback as you develop your workbooks. Working with your stakeholders will also help you to identify and source data that you will incorporate into your spreadsheets. Most organisations keep a variety of quantitative data such as business financials, like revenue, cost of goods sold, profit margins and similar numerical information, which is ideal for recording and analysing in spreadsheets.

Information sources

Determining where your information comes from is an important part of gathering information. The information that you can gather can be of two major types, namely, primary and secondary information.

Refer to the table below to know the difference between the two:

Type	Definition	Sources	Problem
Primary	Information you gather and record yourself.	Questionnaires, surveys, interviews, observations, experiments, historical information, and raw data.	Information must be gathered carefully to ensure it is accurate and bias-free.
Secondary	Information gathered and recorded by others.	Books, internet, reports, newspapers, magazines, pamphlets, and journals.	Information may be inaccurate, out of date, or biased.

These types of information can then be further divided into internal and external sources:

What application best suits your purpose

The most commonly used spreadsheet application across businesses is Microsoft Excel, which traditionally runs from data hosted on localised servers. As the world is becoming more mobile, many users are seeking cloud-based spreadsheet solutions. Accordingly, Microsoft Excel is now available as a cloud-based system, allowing data to be accessed via the internet.

Another popular example of a cloud-based spreadsheet is Google Sheets. The benefit of a cloud-based system is that the data is stored on the internet, making it accessible from anywhere at any time.

Where will data come from?

Understanding where data will come from is key to the design of a spreadsheet. Will data be imported from an existing source (for example, an existing database), or will users enter it manually? What checks and balances do you have to ensure that the data entered is correct? If so, can these checks and balances be built into the spreadsheet, or does it require manual checking and approval?

Watch the following 11-minute video for a rundown on alternative spreadsheet solutions.

How will policies and procedures guide the development and use of the spreadsheet?

It is important to be abreast of any relevant policies and procedures that your business may have that impact the creation of documents and spreadsheets. Understanding this will save you time in the long run.

For example, policies and procedures may include:

• storage requirements
• output requirements or
• style guides.

 

Another important policy to be aware of when producing spreadsheets is workplace ergonomics. The workplace ergonomics policy will include information on work hours, equipment setup and breaks. You must follow this policy to ensure that you sustain good health and work practices.

The following 5-minute video covers the Dos & Don'ts of Excel Spreadsheet Design (with Examples) from Excel guru Leila Gharani:

 

Policies and procedures

Organisations will consider many factors when producing policies and procedures to assist workers when working at a desk and producing spreadsheets.

Some policies and procedures may directly relate to the design or use of spreadsheets, such as:

• style guides
• procedures manuals/quality manuals
• templates for spreadsheet development, including:
  • font types
  • forms
  • headers and footers
  • headings
  • page formats
  • reports etc.

Other policies and procedures for when producing spreadsheets may relate to health and safety practices and sustainable work practices. 

Organisations must implement policies and procedures that systematically manage health and safety at work and can help minimise the risk of injury and illness from workplace operations.

Health and safety  practices when using computers for long periods

You can use several health and safety practices to improve how you work at your computer. Most of these behaviours fall into two (2) main groups:

1. Set up an ergonomic workstation.
2. Reduce and interrupt periods of sitting.

 

The following 2-minute video by WorkSafe Qld explains how you can stay active with computer-based work:

 

Policies and procedures for ergonomics

Different organisations may require different ergonomic practices based on their situation and needs. The set-up and appropriate use of workstations should be included to prevent risk and maintain the worker's health.

The following list provides examples of key elements of policies and procedures that could be included to help protect the health and safety of workers when using digital devices at a workstation:

• Mandatory induction courses for new employees to include discussions relating to safe work practices 
• Practical ergonomic principles on work design and ergonomic equipment, such as adjusting your desk and chair position following safe working practices.
• Practical principles to promote a safe working culture, such as reporting any WHS issues to your supervisor and following relevant commonwealth, state or territory legislation.
• Responsibilities of employers and employees 
• Assessing risks associated with ergonomics
• Employee workstation evaluations, such as assessing the safety of the workstation
• Employee training on workstation hazard identification

Coordinate with your organisation to ensure that your work environment addresses the ergonomic requirements for your specific role.

Before working on any spreadsheet document, you must ensure that your work environment, whether in a designated office space or working remotely offsite, is in a condition that allows you to do your tasks comfortably and meets your organisation's WHS responsibilities.

Ergonomic requirements should include the following:

• Allocate a designated workspace
• Ensure your desk and chair are adjusted correctly
• Ensure the correct positioning of the monitor, keyboard and mouse
• Ensure adequate lighting and ventilation
• Take regular rest breaks, around every 30-60 minutes, to move away from your desk and aim to change your position, as staying in the same position and using the same muscles for hours at a time is not good for your back or neck, leading to the feeling of being tense across your shoulders, back, neck and arms. 

 

 

Having an organised workspace will help maintain a healthy work environment and avoid accidents.

Assessing ergonomics in the workplace

Ergonomic requirements are guidelines for setting up your work environment to ensure your comfort and well-being while working. Many organisations have policies and procedures for organising your work environment safely.

The following lists the key aspects of a workstation that should be examined for correct use:

Office furniture arrangement

• Desk position
• Chair adjustment
• Footrest

Workstation arrangement

• Monitor position
• Keyboard and mouse position
• Positioning other work items

Environmental controls

• Lighting
• Ventilation
• Noise control

Work design

• Task schedule

Undertaking activities for short breaks when working

• standing up and stretching
• taking micro pauses, for example, moving one’s hand off the mouse or keyboard when not in use
• exercises such as neck tilt, shoulder roll, and/or backward shoulder press
• standing up to read a document or talk on the telephone
• moving around and doing something different, such as getting a drink of water or a cup of tea
• having a standing or walking meeting
• conducting administrative tasks away from the workstation
• walking over to talk to someone rather than emailing them
• going for a quick walk around the building
• using sit/stand workstations.
• making or taking phone calls while standing or walking

Reduce and interrupt periods of sitting

Safe Work Australia recommends substituting periods of sitting with standing and interrupting periods of sitting with walking. The figure below shows some examples of how you could break up your day by standing and walking.

For more information, check out Safe Work Australia.

Stretching at your desk

Stretching is a great way to break up blocks of sitting. Stretching can also help relieve any muscle stiffness or soreness you may have from sitting still. There are many simple stretches you can do at your desk. You should consult a health professional if you have any questions or concerns about the stretches that may be appropriate for you.

Stretching should be gentle. It should not hurt. Do not bounce when stretching. Instead, slowly move into the stretch until you feel it working. SafeWork NSW recommends ‘slow, sustained stretches’ and to ‘hold each stretch for 10 to 20 seconds’. Simple stretches you can do at your workstation include:

• Neck stretches
• Shoulder rotations
• Wrist and forearm stretches
• Back twist rotations
• Arm stretches/ side bends

Watch the video below for demonstrations of neck, shoulder and back rotations, wrist stretching, and side bends.

Take regular breaks

It is important to set a reminder to take regular breaks when sitting at the workstation. Especially if we work from home, which makes us much more sedentary. We don't have to walk, get a coffee, speak to someone on a different floor, or catch public transport.  Some people use devices to set alarms throughout their workday to trigger them to move, stretch, take a bathroom break, rest their eyes, etc.

It is recommended to take short breaks every 30-60 minutes, even if it is for just a 2-minute stretch or a quick walk to the bathroom.

Completing general exercises is essential as well. Take yourself out for a walk during the day and take a break from the computer screen.

The following summarises the importance of taking regular breaks from your workstation.

• Improving the overall health of the body: Taking regular short breaks for rest and movement is an essential part of caring for your body at work
• Preventing tension: Staying in the same position and using the same muscles for hours at a time can make you feel tense across your shoulders, back, neck and arms. Ultimately causing aches and pains.
• Preventing severe illness and disease: Evidence suggests that prolonged sitting increases the risk of cancer and cardiovascular disease. The amount of time spent sitting remains a risk, even if you exercise regularly.

Working sustainably

Staying energy-conscious has become increasingly important. For a business, it is not only beneficial to their bottom line, but it is also the right thing to do for the environment. More than one-third of Australia’s carbon emissions come from electricity consumption. Minor changes to the way businesses use energy can make a significant difference.

All businesses should have documented procedures in place to conserve resources and energy. These should be centred around sustainability:

• Reduce the amount of waste you create
• Reuse items that could have a future purpose
• Recycle whatever you can

In an office environment, energy is mainly used to power:

• lighting
• air conditioning, ventilation and heating
• office equipment (e.g., computers and printers)
• pantry equipment (e.g., coffeemaker, microwave, etc.).

Your organisation will provide you with guidelines on how to save energy and maximise office resources.

The common organisational requirements for energy conservation include:

• Switching off lights in unoccupied rooms
• Turning off equipment when not in use
• Shutting down computers and turning off lights and appliances at the end of the day
• Ensuring air conditioning and heating systems are set to timers and not scheduled to come on over the weekend or public holidays
• Use office supplies that contain recycled materials and nontoxic products, such as paper and markers.
• Staff communications should be electronic, not paper-based.
• Use electronic scheduling, inventory management and record-keeping systems.
• Limit photocopy machine use and use refillable or reusable toner cartridges.
• Encourage double-sided printing and a 'think before you print' campaign - is there a reason you need a printout of the document.
• Ensure office equipment and computers are powered down after hours.
• Make use of 'power save' modes on equipment.
• Office equipment and appliances are to be energy efficiency models (energy star rating).
• Recycle or donate computers that are no longer needed.
• Encourage the use of laptops, which tend to be more energy-efficient than personal computers/PCs.
• Use PowerPoint for presentations rather than printed notes.
• Encourage staff to turn off lights when not in use.

Activity: 1A

Identifying Organisational Policies, Procedures and Official Documents

Imagine you are an employee at Complete Business Solutions Australia (CBSA). CBSA provides consultancy services to businesses regarding compliance, finance, human resources, information technology, and other business needs to ensure they have the expertise and support they need to survive and prosper.

Before undertaking this activity, you should navigate the CBSA website to become familiar with the business's services and history.

The organisation's website can be found at www.cbsa.com.au.

Become familiar with the organisational structure of the business and its employees, the Business Plan for CBSA (BD001 – Business Plan) (which includes its mission, vision and business objectives), as well as reviewing the policies, procedures, forms and templates it uses, and in particular, the Style Guide (BD002 – Style Guide).

Access the Intranet section by clicking on the Intranet login link and entering the login and password.

Case sensitive: All Caps

Username: SOE

Password: SOE

Activity: 1B

Using the information from CBSA's Style Guide, what are six (6) critical elements of a spreadsheet that you must consider in your planning?

When a spreadsheet is first opened, it is a blank canvas allowing you to customise its look depending on its purpose. However, the readability and appearance of a spreadsheet can go a long way to providing a good user experience.

Watch the following 4-minute video for an overview of the basics of Excel.

You can use consistent formatting, white space, graphs, and colour to make spreadsheets more reader-friendly.

You can use headings to make content easy to identify, white space to separate elements, and charts to illustrate data. At the same time, colour can visually group like cells or make something stand out.

When a spreadsheet is being created for external use, it is important to stick to the style and branding requirements of the business. Usually, a business will have a style guide that will provide information on how a document will be formatted and the naming convention under which the document should be saved. Organisational policies may also provide guidance regarding what type of information should a spreadsheet contain and how certain data should be presented, such as in the form of charts, tables or graphs.

One of the advantages of spreadsheets is that their blank canvas basis allows for complete customisation. Spreadsheets, therefore, can be formatted according to a style guide to appear professional and on-brand.

The following 4-minute video from GCFLearnFree.org covers the basics of Cells.

Activity: 1C

Accessing basic design features

For this activity, you will familiarise yourself with basic design features that can be used to enhance the readability and appearance of a spreadsheet.

1. Open Microsoft Excel
2. Open a new workbook
3. Save the workbook as 'BSBTEC302 – Design and produce spreadsheets – Learning activities.'
4. In cell A1, write the title of the workbook as 'Basic design features' and format the text so that it contains:
5. Arial font
6. Size 14
7. Bold font
8. Blue text
9. Double click on the right-hand side of the column A top row to expand the cell to fit the text
10. From cells A3 to A17, write the following descriptor words per row (i.e.: 'Bold' will appear in A3, and 'Top and double bottom border' will appear in A17):
    1. Bold
    2. Italics
    3. Underline
    4. The font is 'Broadway.'
    5. Size is 18
    6. Border
    7. The fill colour is yellow
    8. The font colour is red
    9. Align left
    10. Align centre
    11. Align right
    12. Merge
    13. Merge (note that this cell will merge with the above cell when formatting design elements)
    14. Diagonal text orientation
    15. Top and double bottom border
11. Explore with the basic formatting and design elements on the 'Home' tab to format each cell according to its descriptor (for example, cell A3 will appear as Bold, A4 will appear as Italics, A5 will appear as Underline)
12. Adjust the column width to fit all text inside column A
13. Ensure that the font of all descriptor words, except for cell A6, is Arial
14. Ensure that the size of all descriptor words, except for cells A1 and A7, is size 10
15. Use the 'Search' or 'Help' functions at the top of the workbook to help with any formatting queries. Even if not required, use this function on at least one occasion when formatting your descriptor words.

    

16. Once you have finished formatting all descriptor words, Right-click on the worksheet tab at the bottom of the document titled 'Sheet 1' and rename the tab 'Basic design features.'
17. Save your work (it is good practice to do this as you go)
18. Exit the Microsoft Excel application.

Basic design features screenshot example:

If you have followed the steps correctly, your document should show cells A1 to A17 and the saved title at the top of the workbook and resemble the following screenshot.

Try to complete the following five (5) quiz questions to test your understanding of this topic: