UNIT ONE: Prepare and Develop Spreadsheet – Summary
Notes
1. Overview
This unit provides the knowledge and skills needed to:
Organize personal work environment based on ergonomic requirements.
Analyze and specify tasks.
Identify organizational and task requirements.
Apply organization strategies, energy, and resource conservation techniques.
1.1 Organizing Personal Work Environment (Ergonomic Requirements)
Definition:
Ergonomics is the study and design of work systems to fit human physical and mental
capabilities for safety, comfort, and efficiency.
Five Aspects of Ergonomics:
1. Safety
2. Comfort
3. Ease of use
4. Productivity/Performance
5. Aesthetics
Responsibilities:
Designers: Identify and control ergonomic hazards.
Supervisors/Managers: Provide proper equipment, training, and promote hazard
reporting.
Key Ergonomic Requirements:
Proper posture
Correct chair height and screen position
Use of footrest and document holder
Adequate lighting and noise control
Avoid radiation from screens
Five Requirements for Sustainable Workplace Ergonomics:
1. Purpose & Goals: Prevent injuries, improve productivity and morale.
2. Training: Everyone should understand ergonomics principles.
3. Metrics: Measure and track ergonomics improvements.
4. Documentation: Record processes and results for proof and improvement.
5. Teamwork & Accountability: Shared responsibility among all staff.
1.2 Analyzing and Specifying Task
Specification:
A statement defining how a spreadsheet will meet user requirements.
Spreadsheet Definition:
A computer program (e.g., MS Excel) used to organize, calculate, and analyze data through cells
arranged in rows and columns.
Basic Excel Components:
Menu Bar: Displays application menus.
Formula Bar: Shows data or formulas in active cell.
Title Bar: Displays workbook title.
Toolbars: Quick access to commands.
Worksheet: Area for data entry, calculation, and analysis.
Cell: Intersection of a row and column.
Creating a Worksheet:
1. Open Excel → Select New Workbook
2. Enter data in active cells
3. Check and correct data as needed
4. Format text and numbers for clarity
1.3 Identifying Organizational and Task Requirements
Organizational Requirements:
Rules and guidelines on how users should perform their work safely and efficiently.
Task Requirements:
Physical, functional, mental, and administrative needs necessary to complete tasks effectively.
Examples of Task Requirements:
Data Entry: Inputting information accurately into systems.
Output: Converting processed data into usable form (e.g., printouts, monitors).
Data Presentation: Displaying data using text, tables, or diagrams.
Storage: Saving data using devices like hard drives or flash disks.
Formats: Following consistent style rules for appearance and citations.
1.4 Applying Organization Strategies, Energy, and Resource Conservation
Techniques
1. Work Organization Strategies:
Methods to manage workload efficiently and maintain productivity:
Schedule exercise breaks
Alternate repetitive and varied tasks
Take regular rest periods
2. Energy and Resource Conservation Techniques:
Efforts to reduce waste and promote sustainability:
Use double-sided printing and recycled paper
Reuse paper for drafts (respect confidentiality)
Enable power-save modes on devices
Turn off lights and unplug equipment when not in use
Replace bulbs with CFLs or LEDs
Here’s a short and summarized note for UNIT TWO: Develop a Linked Spreadsheet
Solution — simplified for study or quick revision.
UNIT TWO: Develop a Linked Spreadsheet Solution –
Summary Notes
1. Unit Overview
This unit covers:
Utilizing spreadsheet design software functions and formulas
Linking spreadsheets
Formatting cells and assigning data attributes
Testing formulas
2. Utilizing Spreadsheet Design Software Functions and Formula
Spreadsheet Design
Keep spreadsheets simple, clear, and consistent.
Remove unnecessary data and formatting.
Use cell styles to identify input, calculation, and heading cells.
Avoid blank rows and columns.
Include headers, footers, and clear titles.
Functions
Predefined formulas that perform specific calculations.
Common functions include:
o Mathematical: =SUM(), =AVERAGE(), =ROUND()
o Statistical: =COUNT(), =MAX(), =MIN()
o Logical: =IF(), =TRUE(), =FALSE()
o Date/Time: =TODAY(), =NOW()
o Nested Functions: Combining more than one function.
Example:
=IF(A3>B3,"A is larger","B is larger")
Formulas
Begin with an equal sign (=).
Use cell references (e.g., A1, B2) instead of direct values.
Automatically update when referenced data changes.
Arithmetic operators:
Operator Name Example
+ Addition =A1+B1
Operator Name Example
- Subtraction =A2-B2
* Multiplication =A3*B3
/ Division =A4/B4
^ Power =A5^2
√ Square Root =SQRT(A6)
3. Linking Spreadsheets
Definition: Connecting data from one sheet/workbook to another.
Changes in the source automatically update in linked sheets.
Syntax Example:
=SheetName!A2
e.g., =Roster!A2 pulls data from cell A2 of the sheet named “Roster”.
Benefits:
Reduces data duplication.
Keeps information accurate and consistent.
Saves time when updating multiple sheets.
4. Formatting Cells and Assigning Data Attributes
Formatting:
Improves readability without changing the data itself.
Common formatting options:
o Font style, size, color
o Bold, Italic, Underline
o Cell borders and background color
o Alignment (Left, Center, Right)
o Merge cells, wrap text, rotate text
o Number formatting (Currency, %, Decimal)
Tools:
Home Tab: Quick formatting icons.
Format Painter: Copy formatting from one cell to others.
Shortcut: Ctrl + 1 opens Format Cells dialog.
Tabs in Format Cells:
Number | Alignment | Font | Border | Background | Protection | Validation
5. Testing Formulas
Purpose:
To check whether formulas are correct and produce accurate results.
Steps:
1. Go to Formulas → Formula Auditing → Evaluate Formula.
2. Click Evaluate to see how Excel processes each part.
3. Use Step In to view other referenced formulas.
4. Correct any errors before using results.
UNIT THREE: Automate and Standardize Spreadsheet
Operation – Summary Notes
1. Unit Overview
This unit focuses on:
Evaluating tasks
Creating, using, and editing macros
Developing, editing, and using templates
By the end of the unit, learners will be able to:
Evaluate tasks for automation
Create, edit, and use macros
Develop and modify templates for efficient spreadsheet use
3.1 Evaluating Tasks
Purpose:
Automation simplifies repetitive tasks, reduces human error, increases accuracy, and saves time.
Key Qualities of a Good Spreadsheet:
1. Correct – Produces accurate results.
2. Flexible – Adapts to changes in input data.
3. Documented – Clear and easy to understand.
Examples of Automated Tasks:
Scheduling social media posts
Sending payroll or reminders
Backing up files
Generating routine reports
Automation in Excel:
Go to Automate → Automate a Task
Select a template, sign in, and create an automated process
3.2 Creating, Using, and Editing Macros
Definition:
A macro is a recorded set of commands or actions in Excel that can be saved and reused to
automate repetitive tasks.
Steps to Record a Macro:
1. Enable Developer Tab (if hidden).
2. Go to Developer → Record Macro or press Alt + T + M + R.
3. Enter a macro name (letters/numbers, no spaces).
4. (Optional) Assign a keyboard shortcut (e.g., Ctrl + Shift + M).
5. Choose where to store it:
o This Workbook → for use in one file.
o Personal Macro Workbook → for all Excel files.
6. Add a short description.
7. Perform the actions you want to record.
8. Click Stop Recording to finish.
Tips:
Keep macros short and specific.
Use meaningful names and descriptions.
Macros can interact with other Office apps (e.g., Outlook).
Creating Charts in Excel
Charts visually represent data for easy comparison and understanding.
Steps to Create a 2-D Column Chart (Excel 2007):
1. Enter data in cells.
2. Select the data range.
3. Click Insert → Column Chart → 2-D Column.
4. Use Layout Tab to edit chart title, axes, and legend.
5. Move chart to a new sheet if needed:
o Design Tab → Move Chart → New Sheet.
Example:
Title: First Quarter Regional Income
Vertical Axis: Million Birr
Horizontal Axis: First Quarter
3.3 Developing, Editing, and Using Templates
Definition:
A template is a pre-designed spreadsheet that includes formulas, formatting, and structure—
ready for users to enter data.
Benefits:
Saves time and ensures consistency.
Contains built-in formatting and formulas.
Suitable for repetitive tasks (e.g., budgets, invoices, trackers).
Accessing Templates:
1. Click File → New.
2. Choose from categories like Business, Personal, Budgets, Calendars, etc.
3. Select and open the desired template.
Editing Templates:
1. Go to File → Open → This PC → Custom Office Templates.
2. Open your template, make changes.
3. Save and close to update it for future use.
Here’s a short and precise summarized note for UNIT FOUR: Use Spreadsheets —
simplified for study or quick revision.
UNIT FOUR: Use Spreadsheets – Summary Notes
1. Unit Overview
This unit provides skills and knowledge to:
Enter, check, and amend data
Import and export data between compatible spreadsheets
Use manuals, user documentation, and online help
Preview, adjust, and print spreadsheets
4.1 Entering, Checking, and Amending Data
Entering Data
Select the cell or range where you want to enter data.
Type data → press Enter or Tab to move to the next cell.
Example:
o A2 → Month
o B2 → Unit Sales
o C2 → Average Price
o D2 → Sales Dollars
Tip: If data doesn’t fit in a cell, increase the column width or use Wrap Text.
Editing Data
Double-click the cell or edit via the Formula Bar.
Use checkmark (✓) to apply changes.
Example: Type Tot, then edit to Total in Formula Bar.
4.2 Importing and Exporting Data
Export Data
Used to transfer data from Excel to another system or program.
Steps:
1. File → Export → Change File Type
2. Choose file type:
o Text (Tab delimited) → data separated by tabs
o CSV (Comma delimited) → data separated by commas
o Formatted Text (space delimited) → data separated by spaces
3. Click Save As, choose location → Save → Yes
Import Data
Used to bring external data into Excel.
Steps:
1. Go to Data → Get Data → From File → From Text/CSV
2. Select file → Import
3. Check preview → set delimiter (comma, tab, etc.)
4. Click Load to insert data into worksheet
Note:
Some data sources (databases, web pages) may need login or permission to access.
4.3 Using Manuals, User Documentation, and Online Help
Manual Spreadsheet
Traditional paper-based ledger with rows and columns.
Data entered manually using pen or pencil.
Electronic Spreadsheet
Computerized version (like Excel) that allows automatic calculations and editing using
formulas.
User Documentation
Guides, manuals, or help files that explain how to use a product or software.
Helps users learn and troubleshoot features.
Sources of Help in Excel:
F1 key → opens Excel Help
Help tab or question mark icon (❓)
Online user guides and tutorials
4.4 Previewing, Adjusting, and Printing Spreadsheets
Steps to Preview and Print:
1. Select worksheet(s).
2. Go to File → Print (or press Ctrl + F2).
3. Preview print layout and adjust settings:
oOrientation: Portrait or Landscape
oMargins: Normal, Narrow, or Custom
oScaling: Fit Sheet on One Page
oPages: Next Page / Previous Page
4. Click Print when ready.
Notes:
Preview shows black and white unless using a color printer.
To print the entire workbook, select Entire Workbook under Settings.
Key Takeaways
Enter and edit data directly in cells or via the Formula Bar.
Import/export files in formats like CSV, TXT, or XLSX.
Manuals and user guides help with software use and troubleshooting.
Always preview and adjust layout before printing to ensure accuracy.
Would you like me to combine Units 1–4 into a single summarized booklet (2–3 pages) for
easy printing or revision?
Here’s a short and precise summarized note for UNIT FOUR: Use Spreadsheets —
simplified for study or quick revision.
UNIT FOUR: Use Spreadsheets – Summary Notes
1. Unit Overview
This unit provides skills and knowledge to:
Enter, check, and amend data
Import and export data between compatible spreadsheets
Use manuals, user documentation, and online help
Preview, adjust, and print spreadsheets
4.1 Entering, Checking, and Amending Data
Entering Data
Select the cell or range where you want to enter data.
Type data → press Enter or Tab to move to the next cell.
Example:
o A2 → Month
o B2 → Unit Sales
o C2 → Average Price
o D2 → Sales Dollars
Tip: If data doesn’t fit in a cell, increase the column width or use Wrap Text.
Editing Data
Double-click the cell or edit via the Formula Bar.
Use checkmark (✓) to apply changes.
Example: Type Tot, then edit to Total in Formula Bar.
4.2 Importing and Exporting Data
Export Data
Used to transfer data from Excel to another system or program.
Steps:
1. File → Export → Change File Type
2. Choose file type:
o Text (Tab delimited) → data separated by tabs
o CSV (Comma delimited) → data separated by commas
o Formatted Text (space delimited) → data separated by spaces
3. Click Save As, choose location → Save → Yes
Import Data
Used to bring external data into Excel.
Steps:
1. Go to Data → Get Data → From File → From Text/CSV
2. Select file → Import
3. Check preview → set delimiter (comma, tab, etc.)
4. Click Load to insert data into worksheet
Note:
Some data sources (databases, web pages) may need login or permission to access.
4.3 Using Manuals, User Documentation, and Online Help
Manual Spreadsheet
Traditional paper-based ledger with rows and columns.
Data entered manually using pen or pencil.
Electronic Spreadsheet
Computerized version (like Excel) that allows automatic calculations and editing using
formulas.
User Documentation
Guides, manuals, or help files that explain how to use a product or software.
Helps users learn and troubleshoot features.
Sources of Help in Excel:
F1 key → opens Excel Help
Help tab or question mark icon (❓)
Online user guides and tutorials
4.4 Previewing, Adjusting, and Printing Spreadsheets
Steps to Preview and Print:
1. Select worksheet(s).
2. Go to File → Print (or press Ctrl + F2).
3. Preview print layout and adjust settings:
o Orientation: Portrait or Landscape
o Margins: Normal, Narrow, or Custom
o Scaling: Fit Sheet on One Page
o Pages: Next Page / Previous Page
4. Click Print when ready.
Notes:
Preview shows black and white unless using a color printer.
To print the entire workbook, select Entire Workbook under Settings.
UNIT FIVE: Represent Numerical Data in Graphic Form –
Summary Notes
Unit Overview
This unit focuses on how to represent numerical data visually using graphs in spreadsheets. It
covers:
Determining suitable graph styles
Creating graphs with titles and labels
Saving, viewing, and printing graphs
Learning Outcomes
After completing this unit, you will be able to:
1. Determine the appropriate style of graph for data.
2. Create graphs with titles and labels.
3. Save, view, and print graphs effectively.
5.1 Determining Style of Graph
Definition:
A graph is a visual representation of data that helps in easy analysis and comparison of
numerical information.
Purpose:
Makes data easier to interpret.
Highlights trends and relationships.
Presents complex information clearly.
Common Types of Graphs:
Type Description Example Use
Line Graph Shows trends over time. Sales growth per month
Pie Chart Displays proportions of a whole. Market share distribution
Bar Graph Compares data between categories. Revenue by department
Cartesian Graph Uses x and y axes to plot points. Mathematical relationships
Type Description Example Use
Scatter Plot Shows correlation between two variables. Height vs. weight
Heatmap Uses colors to represent data values. Performance levels
Histogram Shows data distribution frequency. Age or income groups
Box Plot Displays data spread (min, max, quartiles). Statistical data overview
Tip:
Choose the graph type that best fits your data purpose — e.g., Pie Charts for proportions, Line
Graphs for trends.
5.2 Creating Graphs with Labels and Titles
Purpose of Titles & Labels:
Titles and labels make graphs understandable and professional by clearly describing what data is
represented.
Steps to Add Titles and Labels:
1. Run or open your report in Graph View.
2. Go to Graph → Titles and Labels.
3. Add or edit:
o Main Title
o Subtitle
o Footnote
o X-Axis (Category) title
o Y-Axis (Value) title
4. Enter meaningful text or variables (like report name, date, or user).
5. Adjust font, size, or color for clarity.
6. Click OK to apply changes.
Adding Titles in Excel (2013–365)
Click the chart → Type directly into the Chart Title box.
If missing, go to:
Design → Add Chart Element → Chart Title → Above Chart or Centered Overlay.
Alternatively, click the Chart Elements (+) button → check Chart Title.
Adding Titles in Excel 2010 / 2007
1. Click chart → go to Layout tab → Chart Title.
2. Choose Above Chart or Centered Overlay.
Tip: Always include:
Main Title: What the chart shows
Axis Labels: Describe X (categories) and Y (values)
Units: (e.g., "Revenue in Million Birr")
5.3 Saving, Viewing, and Printing Graphs
Saving Graphs
To save a chart for future use:
1. Click the Account Tools (or File) button.
2. Select Save Graph.
3. Sign in (if prompted) and choose a save location.
4. In Excel: File → Save As → Choose folder and format (.xlsx, .xlsb, .pdf).
Shortcut:
You can also store the graph via File → Store Output.
Printing Graphs
To print only the chart:
1. Click on the chart to select it.
2. Go to File → Print.
3. Choose your printer from the list.
4. Adjust Settings (orientation, page size, margins).
5. Click Print.
Tip:
You can print either:
The entire worksheet (data + chart)
Or only the chart (recommended for reports).