Planning the Design and Functionality of Spreadsheets

Submitted by coleen.yan@edd… on Tue, 05/21/2024 - 18:06
Sub Topics

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

A group of people having a casual meeting

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:

A diagram depicting types of information

Internal information sources are resources that you utilise from within your organisation. These may be:

  • personal sources (such as your own knowledge)
  • interpersonal sources (relying on the expertise of others), or
  • documented sources from the information systems within your organisation. The principal advantages of this type of data are that the data is readily available, reasonably accessible on a continuous basis, and that it is particularly relevant to the organisation’s situation.

An example of an internal source is a company’s internal accounting records and control systems. These sources provide the most basic data on management inputs and the resulting outcomes. Data on the inputs to the management system can range from budgets and schedules to costing reports and materials planning information. Extensive data on outcomes can be obtained from billing records, shipment information, and other aspects of the accounting information system. The internal information from an organisation can be used for many reasons.

External sources, on the other hand, are wide-ranging and include any information that does not come from official organisational sources.

This may come from information providers such as the government, published data sources, or primary research conducted for the organisation by market research houses. 

These sources are by far the most popular source of external information. Not only is the data readily available, but often it is sufficient to answer a decision question.

For example, a manager may be asked to determine whether the market for a given product exists. By consulting statistics available from various sources, they may be able to answer this question. The major published sources are the various government publications, periodicals and trade journals, industry associations, and other companies. Of all these sources, one of the most effective decision-making tools comes from census data – this is particularly true for marketing decisions.

Primary research involves employing an organisation to conduct a research project on your behalf to gather the information you need. While this can pinpoint the exact piece of data you may need, it is very costly. Often, marketing decisions utilise this type of service; however, there may also be a need for this type of research and data gathering for other reasons as well. The most important considerations are cost and time.

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:

 
Side view of an accountant sitting at a desk working on a desktop computer

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. 

Remember:

The amount of time spent sitting remains a risk, even if you exercise regularly.

 

 

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

A desk with a variety of ergonomic devices displayed

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
TIPS
  • Sit up straight on the chair, allowing arms to hang loosely by the sides.
  • Make a right angle at the elbow and keep the wrist straight.
  • The underside of one's hand should now sit naturally on top of the keyboard. If not, adjust the chair height to enable one's hand to rest on the keyboard.
  • Adjust the back in or out, up or down, so that one can feel the lumbar support in the lower curve of the back.
  • Ensure the angle of the seat's base is either neutral or tilted slightly forward to ensure the front of the chair is not pushing into one's thighs.
  • If the chair has armrests, be sure to position them to fit under the desk.
  • Position the footrest, ensuring that feet are firmly on the floor.

Workstation arrangement

  • Monitor position
  • Keyboard and mouse position
  • Positioning other work items
TIPS
  • A keyboard should be positioned as centred as possible. The keyboard is positioned at the same height as the elbows and forearms. Shoulders are relaxed at the side.
  • Place the mouse as close as is practical to the keyboard so that both elbows are directly under the shoulders while working.
  • The mouse pad should be within easy reach and on the same surface as the keyboard. Use a soft mouse pad and avoid pressing one's hands or forearms against the desk edge.
  • Always be mindful of posture, ensuring the spine remains lifted, with one's shoulders rounded back.
  • Adjust the monitor/screen height so that the top of the screen is at or slightly below eye level. One's eyes should look slightly downwards when viewing the middle of the screen. Position the monitor at arm's length away from the body.

Environmental controls

  • Lighting
  • Ventilation
  • Noise control
TIP
  • Adjust screen brightness and contrast for clear and comfortable viewing. Minimise screen reflection and glare by using an anti-glare filter.

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.

A diagram showing various workday behaviours

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 planning a spreadsheet, you should consider the following:
  • purpose
  • audience
  • end-user
  • application suitability
  • data source
  • relevant policies and procedures
A person squinting while reading on a laptop

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.

Example

For example, imagine a user is presented with a spreadsheet full of data and formulas. Yet, there are no instructions or design elements to show how the spreadsheet is intended to be used or simplify its use. How would you feel being presented with a spreadsheet in this scenario? The user may likely feel overwhelmed, frustrated and confused.

Now imagine that the user was presented with a spreadsheet that contained the same information. Still, it was formatted to appear clean and contained relevant headings and instructional information, and it was obvious at first glance what the purpose of each dataset was to be used. In this instance, the user is much more likely to want to engage with the spreadsheet and use it as intended. The audience the spreadsheet is intended for will also impact its readability and appearance.

Suppose, for example, the spreadsheet creator works in the Finance Team and creates the spreadsheet for a colleague who works alongside them. In that case, it could be expected that minimal formatting may be required due to the colleague's technical understanding of the data. However, if the same person in the Finance Team were creating the spreadsheet for mounting on the business's website for the public to interact with, this would require rich formatting so that the purpose of relevant fields and functions is obvious.

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.

Note

Entering data into a spreadsheet is simple. Microsoft provides the following instructional information on data entry:

  1. Click an empty cell.
  2. For example, cell A1 on a new sheet. Cells are referenced by their location in the row and column on the sheet. For example, cell A1 is in the first row of column A.
  3. Type text or a number in the cell.
  4. Press Enter or Tab to move to the next cell.

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.
    A screen shot of the help dialogue box in excel
  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.

A screen shot in excel that shows its basic design features
Tip: If you right click on the image, you can zoom in using the 'Magnify image' option to be able to have a closer look at it.

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

Module Linking
Main Topic Image
A person making a spreadsheet
Is Study Guide?
Off
Is Assessment Consultation?
Off