Organising, Managing and Presenting Information Using Essential Productivity Tools

This section is a continuation of year one activities, that is designed to help improve your understanding of the use of ICT in society. The section introduces you to spreadsheets which will enable you to understand how to use programs like Microsoft Excel to work with data. You will understand basic parts like cells, rows, and columns, which make up the spreadsheet application layout.

You will learn how to use formulas and functions to do calculations, such as adding up numbers or finding averages and also explore how to organise your data by sorting and filtering it. You also will learn how to use charts and graphs to help you visualise your data, making it easier to see trends and comparisons.

Learning spreadsheets is very essential in our everyday life, they help you manage information better and makes tasks like budgeting or project planning much easier.

KEY IDEAS

• Basic Functions and Formulas: This helps you perform calculations and analyse data quickly and easily in a spreadsheet. Just remember to start a formula or function with the equal sign (=), choose a function, and reference the cells you want to use. We will learn how to navigate and manipulate cells, rows, and columns, starting with simple formulas like SUM, AVERAGE, MIN, and MAX. as well as understand relative vs.

absolute references (e.g., A1 vs. $A$1).

• Charts and Visualisation: These tools in Excel help turn numbers into easy-to- understand pictures. It involves using different types of charts to display your data clearly, making it easier to see trends and comparisons.

• Data Analysis Tools: These tools help you understand and summarise your data in Excel. Tools like pivot tables, sorting, filtering, and functions are used to analyse information, and create charts to visualise your findings.

• Data Organisation: Organising data in Excel helps you keep things neat and find what you need quickly. The use of sorting, filtering, validation, tables, and grouping helps to manage your data effectively.

• Formatting: Formatting in Excel helps make your data clearer and more visually appealing. It involves the use of font styles, colours, borders, and alignment to improve the overall look of your spreadsheet. Conditional formatting makes use of rules to format cells based on their values (e.g., highlighting duplicates).

In Year one, you were introduced to spreadsheet applications. You were taken through how to launch Microsoft Excel and also touched on the various components of the Microsoft Excel interface. In this lesson, we shall again revisit certain features of Microsoft Excel.

A spreadsheet application is an application program designed for organising, analysing, and storing data in tabular either in rows or in columns. A spreadsheet application is mainly used for numerical analysis and data organisation. Some common examples of spreadsheet application include; Microsoft Office Excel, Quattro Pro, VP plan, Smart Cracker, Lotus 1,2,3, Visi Calc, Super Calc, Corel Calculate, Open Office Calc, Libre Office Calc,

Activity 1.1 Launching Microsoft Excel

There are so many ways to launch Microsoft Excel, which includes the use of the icon on the taskbar, the use of the icon on the desktop, the application in the start menu, etc. Use the skills gained in your year one on how to open a program to launch MS. Excel using the “Search bar”. After successfully launching the MS Excel, Figure1.1 will be displayed.

Components of the Microsoft Excel Window/

Interface Figure1.1: Microsoft Excel Window/Interface

Some commonly used features of the excel window

Table 1.1: Features of Excel Window

S/N NAME FUNTION

1. Auto Sum It is a special button found on the formula bar which is used for summation/addition of numbers automatically.

2. Name Box It is the rectangular area at the top left corner of the worksheet in the Excel which displays the cell reference or the selected cell (Active cell).

3. Active Cell This is the selected cell ready to receive data.

4. Row It is the horizontal portion of a cell on a worksheet. Cells are represented by numbers.

5. Column It is the vertical portion of a cell on a worksheet. Columns are represented by letters.

6. Status bar It is the bar on top of the task bar. This bar displays the views and the zoom tool as well as the state of the workbook.

7. Horizontal Scroll bar It is the bar that assists the user to view hidden portion of a page or file. The user should just press and hold down the left mouse button on the bar and move either in left or right direction with the intention to view a hidden portion of a page or file.

8. Cell Cell is the area or region created by the intersection of rows and columns on a worksheet. In naming a cell, the column letter comes first followed by the row figure. Examples of cell names can be; A6, V1, B2, B3, C8, P12, etc.

9. Vertical Scroll Bar

It is the bar that assists the user to view a hidden portion of a page or document vertically. The user should just press and hold down the left mouse button on the bar and move either up or down with the intention to view a hidden portion of a file.

10. Worksheet Tabs

It is a small label at the bottom of an Excel workbook that represents a single worksheet.

11. Formula Bar It is a space opposite the name box of the worksheet where you can see and enter data or formulas for the selected cell.

Some terminologies in Excel

Table 1.2: Excel Terminologies

S/N TERM EXPLANATION

1 Worksheet It refers to the electronic page in a workbook made of cells. Worksheet is the page on which a user works at a given time. The page is made up of series of cells.

2 Workbook It refers to an electronic file which contains two or more worksheet in them.

3 Graph Graph is another important feature of Excel application. Graph s in Excel refers to a diagram or a chart which represents collected data or information diagrammatically or pictorially in excel window or worksheet.

4 Range It refers to a group of specified or selected adjacent cells on a work sheet. It can also be explained as a sequence of adjacent cells on a worksheet.

5 Cell Reference

It refers to the unique name of a cell which is made of column letter and Row number. Example of cell reference are, A1, G4, K23, K5.

Workbooks A workbook in Excel is a collection of worksheets. This means that two or more worksheets forms a workbook. Each worksheet is like a page where you can enter and organise data, perform calculations, and create charts. Think of a workbook as a notebook, where each sheet is a different page for your information.

Figure 1.2: Workbook

From Figure 1.2 Workbook, it can be seen that, the window has three worksheets on the sheet tab. This means the whole window is a workbook.

Features of Excel Workbooks

Workbooks in Microsoft Excel serve as essential tools for managing, organising, and analysing data across various professions and industries. Here are some key uses and purposes of workbooks in Excel.

1. Workbooks help organise and manage information, making it easy to find and work with data.

2. Workbooks allow you to do advanced calculations and analyse data, helping to make better decisions and find useful insights.

3. They help with tasks like planning budgets, predicting future costs, and creating financial models accurately.

4. Workbooks make reports and presentations clear and neat, making it easier to share and understand information.

Uses of Excel Workbooks

1. It is used for numerical analysis.

2. Spreadsheet applications are used for accounting and financial analysis.

3. Spreadsheet applications such as Microsoft Excel is used for sales management.

4. They are used in tax preparation.

5. Spreadsheet application is used for budgeting.

6. Spreadsheet application such as Microsoft Excel is used for record keeping.

7. Spreadsheet application is used for statistical analysis.

8. Spreadsheet application is used for data analysis (examination result).

How to create and manage workbooks Creating an Excel workbook is easy is fun to do! Now, let us look at how you can do it in simple steps:

1. Start by opening the Excel program on your computer.

2. Create a New Workbook:

a. If you see a welcome screen, click on “Blank Workbook.”

b. If you are already in Excel, go to the top left corner and click on “File,” then select “New” and choose “Blank Workbook.”

3. Give your workbook a name (File name):

a. Click on “File” again, then select “Save As.”

b. Choose a location on your computer where you want to save it. To do this, click on browse to display the “Save As” dialogue box where you can choose a desired location.

c. Type a name for your workbook in the “File Name” box and click “Save.”

(see Figure 1.3)

Figure 1. 3: “Save As” Dialogue Box

Note

Files in excel are by default named as “book”, so you can have book1, book2, etc.

depending on the number of new workbooks opened. When you save your file with a name, the default name changes to the new given name.

4. Add Worksheets: By default, a new workbook will have one or more worksheets.

If you need more, click the plus sign (+) next to the existing sheet tabs at the bottom of the workbook. See Figure1.4.

Figure 1.4: Sheet Tabs

5. Start Entering Data: Click on any cell to start typing your data. You can use the formula bar to enter formulas if needed.

6. You can press “Ctrl + S” or go to “File” and click “Save.” To save changes made.

Activity 1.2 Comparing workbook and worksheet

1. With your understanding on workbook, write two differences between workbook and worksheet in your own words.

2. Share your response with a peer.

Activity 1.3 Creating a new workbook from start menu

1. Click on the Start button to display the start menu

2. Locate and click on Microsoft Excel to display the Ms. Excel dashboard or start-up screen

3. Locate and click on “Blank Workbook” as shown in Figure 1.5.

Figure 1.5: MS Excel Dashboard or Start-up Screen

4. The Microsoft Excel window will be displayed as shown in Figure 1.1.

5. Enter data and save.

Activity 1.4 Create a Workbook in Excel from a Template This activity introduces you to another way of creating a workbook from some already existing templates or built-in template.

Instructions: open a workbook and follow these steps to create a new workbook.

Steps:

1. Open Microsoft Excel

2. Click on the “File” tab to open the backstage view.

3. From the list of options, choose “New”. This will take you to the available templates.

4. Browse or Search for a Template. Excel will display a variety of templates, such as Budgets, Invoices, Calendars, Planners, etc. Example, “Simple invoice” in Figure 1.6.

Figure 1.6: Template window

5. Click on the template that best fits your needs. A preview of the template will appear.

6. After reviewing the template, click on “Create” to download the template.

7. Customise the Template by editing the workbook to suit your specific needs by entering your data, modifying formulas, or customising layouts.

8. Once you’ve made your changes, save the workbook.

Open an Existing Workbook in Excel

A computer user can open an existing file from a current window of the same application. In this section, we shall look at how to open an existing Excel file from a current Excel window.

Saving a Newly Created Workbook

In Excel, we can save a newly created workbook of excel file using several steps. You can save the file using keyboard shortcut (Ctrl +S) or using the “Save As” command button of the backstage view of the file tab. Though you can explore other ways of saving a newly created file, this lesson will take you through how to save Excel file using the “file” tab.

Activity 1.5 Opening Existing file Now let us take this activity to open an existing file from a current excel window.

Instruction: Follow the steps systematically and write down tour observation.

Steps:

1. Click on the “File” tab then, select “Open” to display the backstage view.

2. Click on “Browse” command button from the open dialogue box as shown

3. Find the storage location of the existing file.

4. Select your desired file. Example, “SHS Year 2 LM”

5. Click on the “open” button to open the existing file. (You see that, additional Excel window will be launched).

6. Share your experience on this activity with a peer or group members.

Activity 1.6 Saving a Newly Created Workbook

1. Click on the “File” menu.

2. From the “Backstage” view, click on “Save As” command.

3. Save your work in “2 Science 3” folder on the desktop

4. Type your group name for the file in the file name box.

5. Click on the “Save” button to save the document.

Figure 1.7: Saving a workbook Inserting text in Spreadsheet Inserting text in a spreadsheet simply means placing or typing information such as letters, words, or numbers, data/time, currency, into specific cells within a worksheet.

Each cell in a worksheet can hold different types of content Steps to Insert Text in a Spreadsheet

Table 1.3: Inserting text in Excel SN. Main Point Explanation 1 Open the Spreadsheet Launch Microsoft Excel or Google Sheets on any other

example of spreadsheet application.

2 Select a Cell Click on the cell where you want to insert the text. Cells are identified by their column letter and row number (e.g., A1, B2).

3 Enter the Text: Type the text directly into the selected cell (active cell). The text will appear both in the cell and in the Formula Bar at the top of the spreadsheet.

4 Confirm Entry: After entering the text, press Enter or Tab to move to the next cell or click anywhere else on the sheet. The text is now inserted.

Figure 1.8: Worksheet containing Data Observation It can be seen that the name of the selected cell “D4” which contains the data “AMA” has appeared in the name box while the content of the cell “AMA” has been displayed in the formula bar.

How to Enter Numbers, Text, Date/time, Series Using AutoFill

1. Entering Numbers

Single Number: Type the number in a cell (e.g., 1), then click and drag the small square (fill handle) at the bottom right corner of the cell down or across to fill adjacent cells with consecutive numbers.

2. Entering Text

a. Single Text Entry: Type a word or phrase in a cell (e.g., “Apple”), then drag the fill handle to copy that text to adjacent cells.

b. Repeating Text: If you want to repeat a word, type it in one cell and drag the fill handle to fill the adjacent cells.

3. Entering Dates/Times

a. Single Date/Time: Enter a date (e.g., 01/01/2024) or time (e.g., 12:00 PM), then drag the fill handle to fill with consecutive dates/times.

b. Custom Series: If you want to create a series (e.g., days of the week), type the first few entries (e.g., “Monday”, “Tuesday”) to establish a pattern, then drag the fill handle.

4. Entering a Series

a. Custom Lists: To create a custom series (like months or quarters), type the first few entries (e.g., “Q1”, “Q2”), then select them and drag the fill handle to continue the series.

b. AutoFill Options: After using AutoFill, a small icon appears. Click it to choose options like “Fill Series,” “Fill Formatting Only,” or “Fill Without Formatting.”

How to Edit and Format a Worksheet

1. Editing Cells

a. Click on the cell you want to edit.

b. Double-click the cell or press F2 to start editing. You can change the text, numbers, or formulas.

c. To the content of a cell, select the cell and press the Delete key.

2. Formatting Cells: Select Cells: Click and drag to highlight the cells you want to format.

Change Font: to change font Style and Size: Go to the Home tab and use the Font group to change the font type, size, and colour.

3. Cell Background colour Fill colour: Click on the paint bucket icon in the Home tab to change the background colour of selected cells.

4. Borders Add Borders: Use the Borders icon in the Home tab to add borders around cells.

You can choose different styles.

5. Number Formatting

Format Numbers: In the Home tab, use the Number group to format numbers as currency, percentages, dates, etc.

6. Adjusting Column Width and Row Height

a. Auto Fit: Move your mouse to the line between column letters or row numbers until it changes to a double arrow. Double-click to auto-fit the width or height.

b. Manual Adjustment: Click and drag the line to set your preferred width or height.

7. Merging Cells

Merge Cells: Select the cells you want to merge, then click on the Merge & Canter button in the Home tab. This combines them into one larger cell.

Insert and Delete Cells

a. Insert a Row/Column: Right-click on a row number or column letter and select “Insert” to add a new row or column.

b. Delete a Row/Column: Right-click on the row or column and select “Delete” to remove it.

Activity 1.7 Differentiating between “Name box” and “Formula bar” Steps:

1. Reflect on what has been learned so far,

2. Write a definition for both the name box and formula bar after your reflection in step 1.

3. Share what you wrote in step 2 with your peers.

Activity 1.8 Creating a file using different data format In this activity, we will open an existing file and add extra data into it based on the following criteria.

Steps:

1. Open the file you created in “Activity 1.6” from the 2 SCIENCE 3 folder.

2. Under the column “A” type ten names of your classmates using “General” data format.

3. In column “B” type the date of birth of the following classmate using “Long date” format.

4. In column “C” type the amount of money spent by each classmate using “Currency” data format.

5. Save the newly created document in a given folder on the “Desktop” with a different name.

6. Discuss the process involved with the class.

In this lesson, you will learn how to create and use functions and formular through cell referencing.

Now, let us understand cell references so we can use them in creating functions and formulas.

A cell reference is the name of a specific box (cell) in an Excel worksheet. Each cell has a unique address made up of a letter (for the column) and a number (for the row) such as A1 or B2. It is also known as cell address or cell name.

When you use a cell reference in a formula or a function, you are telling Excel to use the value from that specific box (cell). For example, if you write =A1+B1, Excel will add the numbers in those two cells together. Cell references help you perform calculations easily and keep your data organised.

Table 1.4: Types of Cell References

S/N TYPES OF CELL

REFERENCES EXPLANATION

1 Relative Reference This is the default type. When you refer to a cell (like A1) and copy the formula to another cell, the reference changes based on the new location. For example, if you copy a formula from cell B2 that refers to A2 in cell B3, it will refer to A3.

2 Absolute Reference This type locks the cell reference, so it does not change when you copy the formula. You use a dollar sign before the column letter and/or row number (like $A$1). Regardless of where you copy it, it will always refer to A1.

3 Mixed Reference This combines both relative and absolute references. For

example, $A1 keeps the column fixed while allowing the row to change, and A$1 keeps the row fixed while allowing the column to change These cell references are used in formulas and other calculations once the cell reference is created. It will be displayed as a regular cell reference in the formula bar

Activity 1.9 Cell Referencing

Now let us take this activity to aid a better understanding of cell reference and put them into practice. In this activity you will explore various ways of referencing cells in a workbook.

Instructions:

1. Go through steps 1-4 below to explore the default cell reference (Relative Reference).

2. Edit steps 1-4 and list your refined steps in your writing material.

3. Go through the written steps to help you explore Absolute and Mixed Cell Reference.

4. Tabulate the differences between the various ways of referencing cells in excel Steps:

1. Open the Excel workbook

2. Enter two numbers into two different cells

3. In a third cell, create a formula that adds the two cells together using cell referencing.

4. Change each of the original numbers and confirm that the new total has changed.

5. Discuss with a peer to see how they got on.

6. If you finish early, see if you can create formulas to subtract, multiply and divide the same numbers.

Note

Ask your teacher for assistance if you are not able to get the right results after going through steps a with friend and try it again Formula Using the Arithmetic Operators A formula in Excel is an expression that calculates the value of a cell. It normally includes numbers, operators, cell references, functions, and constants. It is a way to perform calculations or analyse data using specific instructions. Formulas always start with an equal sign (=). In Excel formulas, standard arithmetic operators are used for calculations

Table 1.5: Arithmetic Operators

S/N OPERATOR SIGN OPERATOR NAME

1 + Addition 2 - Subtraction 3 * Multiplication 4 / Division 5 ^ Exponentiation Components of Excel Formulas ₁. Operators: These could be arithmetic operators such as (-, +) or comparison operators such as (=, >, <)

2. Cell References: Refer to the contents or name of specific cells (e.g., A1, B2).

3. Functions: Predefined calculations that perform specific tasks (e.g., SUM (), AVERAGE (), COUNT (), MAX (), MIN ()).

4. Constants: Fixed values used in calculations (e.g., numbers like 10, 3.14).

5. Parentheses: It is used to group operations and control the order of calculations (e.g., = (A1 + B1) * C1).

Figure 1.9: An example of Formula

Activity 1.10 Creating formulas First, we will watch the following video: Excel Formulas and Functions Tutorial Your teacher will stop after each formula, as a group, discuss what you could use these formulas to do.

Now, let us take this activity to create formulas and perform basic calculations in Excel ourselves. You are required to use the given table to create your worksheet.

Steps:

1. Open Excel and create a new worksheet with data in Figure 1.10: Sample Excel Worksheet with data.

Figure 1.10: Sample Excel Worksheet with data

2. Create a Formula to calculate the total score for each students using arithmetic operator.

3. Test your formulas by changing the values in the total score of students in some subjects and observe how the total scores update automatically.

4. Save your work in a chosen location with your surname as the file name.

5. Write down any observations you make about how the changing values in the subject scores affects the total scores

6. Share your observations in the class to discuss how it worked and any alternatives.

Functions Functions in Excel are built-in (ready-made) formulas used to perform calculations.

These functions make it easier to perform calculations and analyse data in Excel. For

example, if you are to add values in cells ranging from A1 to A20, a function can be used to reduce the stress of repeating individual cells in the formula. So instead of =A1+A2+A3+A4+A5…… A20, you can use =SUM (A1:A20) to find the total of values in cell A1 to A20.

Table 1.6: Key Point about Functions s/n Key Point Explanation Example 1 Equal sign It indicates the start of a formula or function in Excel. When you type = at the beginning of a cell, Excel understands that you are entering a formula or function, and it will calculate the result based on the expression that follows.

=PRODUCT(B5, A5): with the equal sign beginning this function, excel understands that you want to multiply values of B5 and A5 so it will calculate the result for you.

2 Function name It is the word you use to tell Excel the kind of calculation or task you want to perform. Each function name represents a specific action = SUM (A1:A10): “SUM” is a function name for addition. This function formula will add all values from A1 to A10.

2 Arguments These are the inputs you provide to the function, such as numbers, cell references, ranges, or text Some functions require only one argument while others take multiple arguments.

=AVERAGE (M1:M10): A range of cells from M1 to M10 (the numbers for which you want to find the average).

=IF (B1 > 50, “Pass”, “Fail”): in this argument, excel will check if B1 is greater than 50 then record pass else it will record fail

Table 1.7: Types of Functions

Types Examples Explanation

1. Mathematical Functions

=SUM(B1:B12) Adds up values in B to B12 =AVERAGE(B1:B12) Calculates the average of numbers =PRODUCT(B1, B1) Multiplies values in B1 and B12 =SQRT(D4) It will find the square root of the value in cell D4.

2 Statistical Functions

=MAX(G1:C6) Gives you the highest number from G1 to G6.

=MIN(D1:D5) Shows the smallest number from D1 to D5.

=COUNT(C1: D12) This will count all the number of cells (it will count only numbers) specified C1 to D12 3 Text Functions =CONCATENATE(A1, “ “, B1) Combines the text in A1 and B1 with a space in between.

=UPPER(D3) Changes text from lowercase letters in cell D3to uppercase letters.

4 Logical Functions

=IF(D5 > 50, “Pass”, “Fail”) Checks if D1 is greater than 50 and returns “Pass” or “Fail.”

=AND(G1 > 0, G1 < 100) returns TRUE if G1 is between 0 and 100.

5 Date and Time

Functions =TODAY() Returns the current date. This function will show today’s date.

=DATEDIF(A1, B1, “D”) It finds the number of days between the dates in A1 and B1. This function calculates the difference between two dates

Figure 1.11: Example of Function

Activity 1.11 Creating and using Functions In this activity, we will create a continuous assessment tracker for students in your group and Excel functions to calculate total scores, averages, and grades.

Instructions

1. Open Excel and create a new worksheet and label the columns as follows:

a. A2: “Student Name”

b. B2: “Assessment 1”

c. C2: “Assessment 2”

d. D2: “Assessment 3”

e. E2: “Total Score”

f. F2: “Average Score”

g. G2: “Grade”

2. Input Sample Data:

a. In column A, enter names of students in your group.

b. In columns B, C, and D, input scores for each assessment.

3. Calculate Total Score: In cell E3, enter the formula to calculate total score and drag the fill handle down to copy the formula for all students.

4. Calculate Average Score: In cell F3, enter the function to calculate average score and drag the fill handle down to copy the function for all students.

5. Assign Grades: In cell G3, use a nested IF function to assign a grade based on the average score and drag the fill handle down to apply this formula to all students.

Example=IF(E3>=90, “A”, IF(E3>=80, “B”, IF(E3>=70, “C”, IF(E3>=60, “D”, “F”))))

6. Calculate Class Averages: In the row after the last name, calculate the group average for each assessment

7. Present your work for peer assessment and make corrections where necessary.

Note

You can expand this assessment tracker by adding more assessments, additional criteria, or even using advanced functions like VLOOKUP for more complex grading systems.

Activity 1.12 Comparison between formulas and functions Now that you have been able to create and use both formula and functions in Excel, let us take this activity to enable you to identify the differences between formula and functions This activity is to help you clearly differentiate between functions and formulas in Excel. It will reinforce your understanding through a structured comparison, making it easier to remember the unique characteristics of both formulas and functions.

Steps:

1. Write out five characteristics of formula with examples learned in this lesson

2. Write five characteristics of functions with examples learned earlier in this lesson

3. Complete Table 1.8 to clearly state the differences between formula and function.

Table 1.8: Difference between Formula and Function 1 Key Aspect Formula Function 1 Definition 2 Usage 3 Syntax 4 Examples 5 Complexity 6 Return Type

4. Discuss the outcome of your tables with a peer for review and share views on each other’s work.

Hello learners, welcome to another interesting lesson on Microsoft Excel.

In our previous lessons, we discussed creating and using formulas and functions to perform calculations. When your formulas do not run as expected, then it is possible it contains errors or bugs.

Debugging and troubleshooting formulas are processes used to identify and fix problems in your calculations, especially in MS Excel and other spreadsheets applications Debugging It is checking your work to see if the formula created is working. If a formula is not giving you the right result, you look at it closely to find mistakes. Debugging involves;

1. Making sure the formula points to the right cells and avoiding incorrect cell referencing.

2. Ensuring you are using the right math functions (like adding instead of subtracting) by avoiding wrong operators.

3. Syntax errors: Looking for missing parentheses or other typing mistakes in your formula (Syntax errors).

Debugging Techniques

₁. Check for Errors: Look for error messages in cells, such as #DIV/0!, #N/A, or #VALUE!. These indicate specific issues that need to be addressed.

2. Use the Formula Bar: Click on a cell with a formula to see the entire formula in the formula bar at the top. This helps you review and edit the formula easily.

3. Evaluate Formula Tool: Go to the Formulas tab and select Evaluate Formula.

This tool allows you to see how Excel calculates the formula step by step, which can help you spot mistakes.

4. Break Down Complex Formulas: If a formula is complicated, break it into smaller parts. You can create separate cells for each part of the calculation to see where the error occurs.

5. Check Cell References: Make sure you are referencing the correct cells. If you copy a formula, check for relative and absolute references (using $ signs) to ensure they behave as expected.

6. Use Functions for Testing: Use functions like IFERROR() to handle errors gracefully. For example, =IFERROR(A1/B1, “Error”) will return “Error” instead of an error message if there’s an issue.

7. Highlight Errors: Use conditional formatting to highlight cells with errors.

This can make it easier to spot problematic areas in your worksheet.

8. Check Data Types: Ensure the data types are correct. For example, if you’re trying to perform math operations, make sure the cells contain numbers, not text.

9. Use the Trace Precedents and Trace Dependents Tools: These tools can show you which cells affect a formula (precedents), or which formulas depend on a cell (dependents). You can find them in the Formulas tab.

10. Consult the Help Function: Use Excel’s built-in help feature or search online for specific functions if you are unsure how they work or what might be going wrong.

Troubleshooting It is about finding out why a formula is not working as expected. To find out why your formula is not working as expected, you should;

1. Test the formula by breaking it down into parts to see which part is not working.

2. You should check the data type in the formula. Make sure you are using the right kinds of data for example using text instead of number can result in error.

3. Look for hidden issues because sometimes, formatting or hidden characters can cause problems during calculation.

Troubleshooting Steps

1. Look for error messages like #DIV/0!, #N/A, or #VALUE!. These indicate specific problems. For example, #DIV/0! means you’re trying to divide by zero.

2. Click on the cell with the formula and check the formula bar. Make sure the formula is written correctly and that all necessary parts are included.

3. If the formula is complex, break it into smaller parts. Put each part in its own cell to see where it might be going wrong.

4. Ensure you’re referencing the correct cells. If you copied the formula, check that the references are still appropriate (relative vs. absolute).

5. In the Formulas tab, click on Evaluate Formula. This tool lets you see how Excel calculates the formula step by step, helping you pinpoint where the issue is.

6. Ensure the data types are correct. For instance, if you’re adding numbers, make sure the cells contain numerical values, not text.

7. Highlight cells with errors using conditional formatting. This makes it easier to spot issues in your worksheet.

8. Sometimes, hidden spaces or characters can cause problems. Use the TRIM() function to remove extra spaces from text.

9. Create simple test cases or use known values to see if the formula works as expected. This can help you isolate the problem.

10. If you are still stuck, look for help online or ask a colleague. Sometimes a fresh perspective can make all the difference.

Activity 1.13 Characteristics of Debugging and Troubleshooting Formulas

Instructions: Match each characteristic (1-8) to either Debugging or Troubleshooting by shading the blank column next to each characteristic.

Table 1.9: Characteristics of Debugging and Troubleshooting

Characteristics Debugging Troubleshooting

1. dentifying specific errors in formulas

2. Checking for incorrect cell references

3. Analysing the overall system for issues

4. Finding out why something isn’t working

5. Testing different parts of a formula

6. Looking for patterns in repeated problems

7. Correcting syntax errors in formulas

8. Ensuring all data types are appropriate Some common errors in using formula and functions in Excel

Table 1.10: Formula Errors

Error Description

1 #DIV/0! It occurs when you try to divide a number by zero or by an empty cell. In simple terms, it means you’re trying to perform a calculation that doesn’t make sense because you can’t divide something into zero parts.

2 #NUM! This error happens when a formula has invalid numeric values. This means there’s something wrong with the numbers being used in the calculation.

If a calculation results in a number that is too large or too small for Excel to handle, it will also show #NUM!

3 #REF! This error means that, a formula is trying to use a cell reference that is no longer valid. This usually happens if you have deleted a cell or a row/ column that the formula was pointing to.

4 #NAME? If you type a function name incorrectly (like writing =SUMM(A1:A10) instead of =SUM(A1:A10)), Excel doesn’t know what you mean.

If you use a name for a cell or range that hasn’t been defined, Excel can’t find it.

If you forget to put quotes around text in a formula or you use wrong quotes.

5 #VALUE! You are trying to perform a calculation with text instead of numbers. For

example, if you have =A1 + “hello” and A1 contains a number, Excel can’t add a number and text together.

Improper Arguments: If a function expects a number but you give it something else, like a cell with text, it will show this error.

6 #N/A This shows up when a formula cannot find a value it’s looking for. This commonly happens with lookup functions, like when you use VLOOKUP or HLOOKUP, and the item you are searching for doesn’t exist in the specified range.

Activity 1.14 Finding solutions to errors This activity is to help identify errors in formulas and what to do to solve them.

Instructions:

1. Carefully study the diagram in Figure 1.12

2. Fill each red box with the error that each description will resolve

Figure 1.12: Solutions to Formula Errors

Activity 1.15 Identifying and fixing errors in using formula in excel

1. Open an existing sample Excel file that you’ve been provided by your teacher containing errors.

2. Locate and list all the errors you find in the sample file.

3. For each identified error look at the meaning of the error and write down possible solutions for each error type.

4. Fix the errors in the sample file with a friend based on what you wrote in

step 3.

5. Discuss the errors and the solutions found.

6. Share different approaches to resolving the errors.

Note

If after taking this activity you still have challenges fixing some of the errors, ask your teacher to guide you to do it right and try again on your own.

Good job on completing the lesson on debugging and troubleshooting formulas in Excel. In this lesson, we would look at how data can be represented in a visual form to make it more appealing Purpose of Using Graphs and Charts Visual representations of data in Excel are ways to turn numbers and information into pictures, making it easier to understand and analyse.

Types of Visual Representations

₁. _(Graph): A graph is a visual representation of data that helps you understand relationships, trends, and comparisons at a glance. It uses various graphical elements like lines, bars, or points to display your data.

2. Chart: A chart is a visual tool used to present data in a structured format, often using bars, lines, or slices to compare different categories or show parts of a whole.

3. Infographics: Combine text, images, and data visualisations to communicate information engagingly and effectively.

Table 1.11: Examples of Graphs and Charts

Type Examples Description

Graph Line Graph Shows trends over time by connecting data points with lines.

Bar Graph Uses horizontal or vertical bars to compare different categories or values.

Column Graph Similar to a bar graph but displays data in vertical columns.

Pie Chart Represents parts of a whole, with each slice showing a percentage of the total.

Scatter Plot Displays values for two different variables, showing how they relate to each other.

Histogram shows the frequency distribution of a set of continuous data Charts Bar Charts Show comparisons between different categories using bars.

Line Charts Display trends over time with lines connecting data points.

Pie Charts Show parts of a whole, with slices representing percentages Benefits of Using Graphs and Charts in Excel

1. Easy Understanding: They make complex data easier to understand by turning numbers into visuals, allowing you to see trends and patterns quickly.

2. Better Comparisons: Graphs and charts help you compare different sets of data side by side, making it easier to spot differences and similarities.

3. Highlight Key Information: Important data points stand out in a visual format, so you can quickly identify highs, lows, and significant changes.

4. Engaging Presentation: Visuals are more engaging than plain numbers and texts, making it easier to grab your audience’s attention during presentations or reports.

5. Improved Decision-Making: By visualising data, you can make informed decisions more easily, as it helps you see the bigger picture and understand the implications of the data.

6. Timesaving: Quickly assessing data through visuals saves time compared to reading through rows of numbers, allowing for faster analysis.

7. Effectiveness: They improve the effectiveness of communication by conveying messages clearly and concisely Factors to consider when choosing charts and for data When choosing charts for visualisation in Excel, it’s important to consider a few key factors to ensure the data is effectively communicated. The factors to be considered include;

1. Type of data

2. Purpose of the chart or graph

3. Number of data points

4. Data relationship

5. Clarity

6. Simplicity, etc.

Identifying and Selecting the Most Suitable Chart Types

1. Understand the data to determine what type of data you have (categorical, numerical, time-series).

2. Define the purpose of what you need to communicate (comparison, distribution, relationship, trend).

3. Choose the chart that best fits the data and the intended message. Figure 1.13 shows the “chart” section of the insert tab.

Figure 1.13: Charts Views

Note

Bar charts compare categories, and Pie charts show parts of a whole. Line charts show trends over time, and Scatter plots show relationships between variables. You can therefore choose any of the available charts that best suit your purpose.

Creating Charts in Microsoft Excel

1. Open your excel workbook and enter your data

2. Select the data for which you want to create a chart.

3. Click INSERT > Recommended Charts.

4. On the Recommended Charts tab, scroll through the list of charts that Excel recommends for your data, and click any chart to see how your data will look.

5. If you don’t see a chart you like, click All Charts to see all the available chart types.

6. When you find the chart you like, click it > OK.

7. Use the Chart Elements, Chart Styles, and Chart Filters buttons command at the upper-right corner of the chart to add chart elements like axis titles or data labels, customise the look of your chart, or change the data shown in the chart.

Activity 1.16 Creating Pie Chart

Instruction: Follow these steps to create a pie chart in excel.

Steps:

1. Launch Microsoft Excel application.

2. Type your data in your worksheet using the data in Figure 1.14.

Figure 1.14: Sampe Excel Data for Pie Chart

3. Select the data range that you want to turn into a pie chart, including headings

4. Go to the Insert tab on the Excel ribbon.

5. In the Charts group, click on the small arrow to Pie chart icon to display the various pie chart styles.

Figure 1.15: chart Group Icons

6. Click on the specific pie style you like, and Excel will automatically create a visual representation of the data you selected in step 2.

Figure 1.16: Pie Chart Styles

7. Save your work and share your experience with a peer

Activity 1.17 Creating a Column

1. Open the program (MS Excel)

2. Enter your data. (Use Figure 1.17)

Figure 1.17: Sample Excel data for column chart

3. Select your data (use total score for your column chart)

4. Go to the Insert tab on the Excel ribbon.

5. In the Charts group, click on the small arrow to column chart icon to display the various column or bar chart styles.

Figure 1.18: Bar or Column Chart Icon

6. Click on the specific style you like, and Excel will automatically create a visual representation of the data you selected in step 2.

Figure 1.19: Column chart styles

Manipulating charts in Excel allows you to customise them to better display your data.

It makes your charts more informative and visually appealing, making it easier for your audience to understand the data. This involves changing the chart type, adjusting the data range, formatting axes, adding labels, and customising colours or styles to make the chart more informative and visually appealing Customise the Title of the chart ₁. Click on the default title “Chart title” of the chart. This will select the text in the textbook.

2. Erase the text in the name textbox “Chart title”

3. Type your desired title for the chart. Example, “TERM 2 EXAMS SCORE OF STUDENTS”

4. The charts shall then bear the customised name as shown in Figure 1.20.

Figure 1.20: Customised Chart Title

Giving Colour to the Columns

₁. Click on the column chart to activate it. The Chart Filters buttons appear beside it

2. Locate and click on the chart styles.

3. Click on the “Colour” button to display the colour pallet.

4. Select your desired colour to be applied to the chart.

5. The chart will then bear the customised colour as in Figure 1.21.

Figure 1.21: Colouring Charts

Activity 1.18 Creating Bar Charts

1. Having gone through how to use the style button of the “Chart Filters” buttons; pair with your peer and perform the task below using the guided instruction.

2. Create an Excel file of ten students and indicate their names. Remember to add their ages using figures rather than numerals.

3. Create a bar chart with the text data.

4. Use the “Style” button from the “Chart Style” view to modify the shape(style) chart.

5. Share your approach with other groups.

6. Activity 1.19 Creating a Line Chart

7. In the existing workbook, click on the “New Sheet” button beside the Sheet1 to add additional worksheet

8. On the new worksheet, create an Excel file with the names of seven learners under column A.

9. In column B, type their corresponding ages

10. Select the data (ages of learners) in column B.

11. Go to the Insert tab on the Excel ribbon.

12. In the Charts group, choose the type of chart (Line chart).

13. Click on the specific line chart style you like, and Excel will automatically create a visual representation of the text data.

Note

Your chart should look similar to or same as Figure 1.22 Sample of a line chart. If your chart does not look similar to or same as Figure 1.22, go over the steps again to get it right. A Peer who had it right could guide you.

Figure 1.22: Sample of a line chart

Activity 1.20 Customising Style of the Line chart

1. Click on the column chart to activate it. The Chart Filters buttons appear beside it

2. Locate and click on the chart styles.

3. Click on the “Style” button to display the style options.

4. Select your desired style for the line chart.

Figure 1.23: Customised Line Chart Window

Saving the Chart

Saving a document is an important activity in file creation. It helps prevent loss of data in case of power failure or system crashes. It allows you to access and edit your work later without needing to start over. A saved document can also be easily shared with others for collaboration or future use.

Activity 1.21 Saving a Chart as a PDF File Instruction:

1. Use your knowledge on how to save a newly document to save the excel file using the Save As command from the “Backstage view”

2. Choose PDF from the “Save as type” option in the “Save As” dialogue box

3. Choose a storage location for the file and assign it a desired name.

4. Share your experience in performing this activity with a peer

Welcome to another interesting lesson in Excel. In this lesson you will deal with sorting and filtering in Excel.

Sorting Sorting in Excel simply means arranging data in a specific order to make it easier to understand and analyse. You can sort data in ascending (A to Z or smallest to largest) or descending (Z to A, largest to smallest) order based on a particular column. For

example, you can sort a list of learners’ names alphabetically or arrange sales figures from highest to lowest.

Benefits of Sorting Data

₁. Organises data: Makes it easier to find information when data is arranged in a logical order.

2. Improves readability: Helps users quickly understand trends, such as highest to lowest sales or alphabetical lists.

3. Facilitates comparison: Sorting allows for easy comparison between data points by grouping similar items together.

4. Speeds up analysis: Sorting helps identify outliers, patterns, or trends faster Filtering Filtering in Excel is a way to display only the data you want to see while hiding the rest.

It helps you to focus on specific information within a larger dataset.

Benefits of filtering data

1. Focuses on relevant data: Helps you view only the specific data you need, making large datasets easier to work with.

2. Saves time: By narrowing down the information, filtering allows you to find what you’re looking for quickly.

3. Improves data analysis: Filtering helps in isolating key data points for deeper analysis without distractions from irrelevant information.

4. Simplifies reporting: You can create focused reports by displaying only data that meets certain conditions, making insights clearer.

Activity 1.22 Sorting and filtering

1. Consider the importance of sorting and filtering of data in Microsoft Excel.

As an ICT student, you were given different textbooks made of different subjects and classes to sort and filter.

a. Indicate any two ways you will sort the textbooks.

b. What way will you filter the books to your desired condition or criteria?

c. Outline two benefits you may have when you sort and filter a given set of books in the table below.

Table 1.12: Sorting and filtering SN Benefits of Sorting Benefits of Filtering Difference between Data Sorting and Data Filtering

Table 1.13: Differences between sorting and filtering SN Data Sorting Data Filtering 1 Organises data in a specific order (ascending or descending) to make it easier to navigate and analyse.

Displays only the rows that meet specific criteria, hiding the rest of the data temporarily.

2 Rearranges the entire dataset according to the chosen order but keeps all data visible Hides data that doesn’t meet the criteria, showing only the filtered portion.

3 Applied to organise data based on a single or multiple columns (e.g., sorting names alphabetically or prices from low to high).

Applied to focus on specific data by conditions (e.g., displaying only products above a certain price or students who scored above 80%).

4 Changes the position of the data in the worksheet, affecting the entire dataset.

Does not change the order of data but temporarily hides rows that do not match the filter criteria.

5 All data remains visible, just in a different order.

Only the data that meets the set conditions is visible; other data is hidden.

Types of Sorting

There are different ways you can sort your excel data. Alphabetic data can be sorted either A-Z or Z-A while numeric data can be sorted either Small –large or large – small.

1. Alphabetical Sorting: Arrange text data in alphabetical order, either AZ (ascending) or ZA (descending).

2. Numerical Sorting: Orders numerical data from smallest to largest (ascending) or largest to smallest (descending).

3. Date Sorting: Organises date and time data from oldest to newest (ascending) or newest to oldest (descending).

4. Custom Sorting: Uses user-defined lists or criteria to sort data in a specific order Sort Data in Excel Numerically ₁. Launch Microsoft Excel application and create your desired data using the experience gained in the previous lessons.

2. Select the range of cells you want to sort. Make sure the data selected are values or numbers.

3. Click on the drop-down arrow beside the “Sort and Filter” button or tool.

4. From the drop-down options, choose your desired sorting command. (Descending order).

Note

After going through step 1-5, the selected data will be rearranged from the biggest to the smallest.

Figure 1.24: Sorting Numeric Data

Sort Data in Excel Alphabetically

Alphabetic data sorting simply means sorting label data (non-figure) in a particular order. This means that sorting can be applied to numbers. The sorting can either be A-Z or Z-A.

How to Sort Data Alphabetically

1. Select the range of cells you want to sort. Make sure the data selected are labels (alphabetic data.)

2. Click on the drop-down arrow beside the “Sort and Filter” button or tool.

3. From the drop-down options, choose your desired sorting command. (ascending order: A-Z or Descending Z-A)

Figure 1.25: Sorting Alphabetic Data

Activity 1.23 Sorting Data in Excel

Taking this activity aids a better understanding of sorting data in excel workbooks.

Instructions

1. Create a workbook with two different worksheets.

2. Enter the names of your group members and their ages on each worksheet.

3. Change your sheet tabs name (Numbers for sheet 1 and Alphabets for sheet 2)

4. Applying your sorting skills to the data you have created.

5. Save your work with your first name.

6. Present and explain your work to a peer and the class at large.

Note

Your workbook should look like Figure 1.26

Figure 1.26: Sorting Practical work Data Filtering Filtering in Excel as explained earlier is a way to display only the data you want to see while hiding the rest. For example, you can filter a list of products to show only those with prices above a certain amount or filter a table to display students who scored above 80%. In this case the cells which meet the criteria will show up over those which do not meet the set criteria. That is to say that data filtering helps you focus on specific parts of your data, making it easier to analyse or find information without changing the original data set.

How to filter data in Excel Now let us look at how we can hide some data sets and show some.

1. Select Your Data: First, you click on a cell in your data set, usually a table with headers (like names, dates, or numbers).

2. Turn on Filter: Go to the “Data” tab and click on “Filter.” This will add small arrows to each header.

3. Choose What to See: Click on the arrow in the header of the column you want to filter. You’ll see a list of options which you can check or uncheck boxes to include or exclude specific items. You can also sort the data or set conditions (like showing only numbers greater than a certain value).

4. View Filtered Results: After you make your selections, Excel will hide the rows that don’t meet your criteria, showing only the data you want to see.

5. Clear or Remove Filter: To see all your data again, you can click the filter arrow and choose “Clear Filter” or turn off the filter completely.

Filtering Excel Data

₁. Create your Excel file.

2. Select the data range you want to filter (e.g., A3:A9)

3. On the Excel ribbon, click on the Data tab.

4. In the Sort & Filter group, click on the Filter button. This will display drop- down arrows to each column header of the selected column.

5. Click on the drop-down arrows to each column header to display the ‘Text filter” dialogue box.

Figure 1.27: Data Filtering Process

6. Click the checkboxes of the cell you want to hide its data to uncheck them leaving the cells you want to display unchecked. In this lesson, we shall filter data to display names of students with the initial letter “A”.

7. Click OK to apply the filter.

Figure 1.28: Data Filtering Dialogue Boxes

Note: It will be noted that cells which were checked are now hidden leaving those cells which were not checked as shown in Figure 1.29.

Figure 1.29: Sample Filtered Data set

In Excel, you can hide certain columns while displaying others by manually hiding them. However, if you want to filter multiple columns based on specific data criteria while showing only the columns you need, you will use a combination of filters and the hide column feature.

Activity 1.25

In this activity, we shall look at how to filter to display learners who had total score marks above 230 as shown in Figure 1.30.

Figure 1.30: Sample Date set for Advanced Filter Steps:

1. Create your worksheet with data from Figure 1.30: Sample Data set for Advanced Filter

2. Select the column “E” containing the total score of students.

3. Click on the “Data” tab to display in views or groups.

4. Locate and click on the “Filter” tool. A drop-down arrow appears at the column heading of “E”.

5. Click on the drop-down arrow at the column heading “E”.

Figure 1. 31: Advance filtering Process

6. From the filter option dialogue box, click on “number filter”.

7. From the sub-menu, click on “Greater Than” command to display the “Custom filter” dialogue box.

Figure 1.32: Custom filter” dialogue box

8. Type or specify your desired value in it.

9. Click on “OK” to complete the filtering process.

Figure 1. 33: Custom AutoFilter

Note

It will be noted that, the records (Rows) showing total score of students less than 230 are hidden as shown in Figure 1.34.

Figure 1. 34: Sample of Advanced filtered data Applying Multiple Filters To apply multiple filters across different columns in Excel, you can combine conditions on multiple columns. Let’s use the sample data from Figure 1.35: Sample data set for Multiple Filters to apply multiple filters.

Figure 1. 35: Sample Data set for Multiple filtering

Activity 1.26 Applying Multiple Filtering

Instruction: Follow the steps to filter to apply two conditions:(Total score greater than 230 and Maths score greater than 60) Steps to Apply Multiple Filters Selecting desired data

1. Select your entire data range from A3 to E9, including headers and rows (names and scores).

2. Go to the Data tab in the Excel ribbon.

3. Click on the Filter tool, then drop-down arrows will appear next to each column header.

Apply the First Filter (Total Score > 230)

4. Click on the drop-down arrow next to the Total column (E).

5. Select Number Filters, then choose Greater Than.

6. In the dialog box that opens, enter 230.

7. Click on the OK tab.

Figure 1.36: Filtering total score column Apply the Second Filter (Maths Score > 60)

8. Now, click on the drop-down arrow next to the Maths column (B).

9. Select Number Filters.

10. Choose Greater Than.

11. In the dialog box that opens, enter 60.

12. Click on the OK tab.

Figure 1.37: Filtering Maths score column

Note

This will filter and display only students who have Maths scores above 60 and Total scores above 230.as shown in Figure 1.38.

Figure 1. 38: Multiple filtered data

Saving a file is an important activity in file creation. It helps prevent loss of data in case of power failure or system crashes. It allows you to access and edit your work later without needing to start over. A saved file can also be easily shared with others for collaboration or future use.

Saving A Workbook with the Default File Format

₁. Click on the “File” menu.

2. From the “Backstage” view, click on the “Save As” command.

3. Choose a storage location for the file. Example; “Documents”.

4. Type your desired name for the File in the file name box. Example: “Year 2 LM”.

5. Click on the “Save” tab to save the file.

Figure 1.39: Steps for Saving a Workbook

Saving A Workbook in Portable Document Format (PDF)

on an External Drive ₁. Insert your external drive into the computer

2. Click on the “File” menu.

3. From the “Backstage” view, click on the “Save As” command.

4. Choose the external drive as the storage location. Example: “New Volume G”.

5. Type your desired name for the file. Example: “Learner Manual 2”.

6. Click on the “Save as type” to display the available file formats.

Figure 1.40: Save As Dialogue Box

7. Select your desired file format, example “PDF”.

8. Click on the “Save” tab to save the file.

Figure 1.41: Samples File Formats

Activity 1.27 Saving a workbook

1. Use the skills gained in working with workbooks and worksheets to perform this activity.

2. Save workbooks to a cloud service of the choice of the group (e.g. OneDrive, Google Drive, or Dropbox)

3. Make sure your computer has internet access

4. Discuss benefits of saving a document of cloud storage such as OneDrive or Google

5. Share your group’s procedure and approach performing this activity with other groups.

Printing Worksheets

Printing is the process of making copies of text or images on paper or other materials.

It usually involves a machine, like a printer, that uses ink or toner to transfer the design onto the surface. It is converting a softcopy document into a hardcopy format.

1. To print a file in a workbook, the computer user needs to consider the following;

2. The paper orientation, whether portrait or landscape

3. The paper size to be used. Example, A5, A4, A3, A2, A1, etc.

4. The page to print. Whether the active page or selection

5. The number of copies to be printed. Etc.

How to Print an Active Worksheet

₁. Click on the file tab to display the backstage view.

2. In the backstage view, click on “Print” to display the print dialogue box.

3. Select the printer you want to use if there are more than one printer connected to your computer.

4. Select the pages you want to print. For the purpose of this lesson, select “Active Sheet”.

5. Specify the number of copies you need using the “Copies” arrows.

6. Select the orientation you want. It defaults, the paper orientation will be “Portrait”. Therefore, select, “Landscape” to change the default orientation.

7. Indicate the paper size. The default paper size is “Letter”, therefore, select you desired paper size, example, A4.

8. Click on the “Print” button to issue the print command.

Figure 1.42: The Print Dialogue Box

Customise Printing Output

Headers and footers Headers and footers are useful for including additional information such as the document title, page numbers, date, etc., at the top (header) or bottom (footer) of printed pages. Header allows you to add additional information at the top margin of the document page while footer is used to insert additional information at the bottom of the page margin.

How to Add and Edit Headers/Footers

1. Go to the Insert tab on the Excel ribbon.

2. Click on Header & Footer in the Text group.

Figure 1.43: Header and footer

3. Excel will switch to Page Layout view. You can now click to edit the header or footer area.

Figure 1. 44: Page Layout View

To Customise Headers/Footers

In the Header & Footer Tools design tab (which appears when you are editing the header/footer), you can:

1. Insert page numbers: Click on Page Number.

2. Insert the date: Click on Current Date.

3. Insert the file name or sheet name: Use File Name or Sheet Name.

4. Switch between header/footer: Click on Go to Footer or Go to Header to edit both areas.

Adjusting Page Breaks

Page breaks control where a new page begins when printing large worksheets.

How to View and Move Page Breaks

1. Go to the View tab.

2. Click on Page Break Preview. This will show you where Excel automatically inserts page breaks.

Figure 1.45: Page Break Preview

3. Blue lines indicate where page breaks occur. You can click and drag these lines to manually adjust them.

Figure: 1.46: Page Breaks

Adjusting Print Settings (Page Setup Dialog Box)

The Page Setup dialog box is where you can make fine adjustments to your print settings, including scaling, margins, headers/footers, and more.

Manage Print Jobs using Pause, Cancel, and Resume Print Job Access the Printer Queue A printer queue is a list of print jobs waiting to be processed by a printer. When multiple documents are sent to a printer simultaneously, they line up in the queue in the order they were submitted, and the printer processes them one by one.

Accessing Printer Queue

1. Open the Control Panel.

2. In the Control Panel dialogue box, select Devices and Printers.

3. Find the printer you used to send the Excel job.

4. Right-click the printer and select open.

5. This will open the print queue, showing the list of active print jobs.

Manage the Print Job

In the printer queue, you’ll see the Excel document you sent to print. Here, you can Pause, Cancel, or Resume as needed:

1. To Pause: Right-click the print job and select Pause. This stops the job temporarily.

2. To Cancel: Right-click the print job and select Cancel. This permanently stops and removes the job from the queue.

3. To Resume: Right-click the paused job and select Resume to continue printing.

Activity 1.29 Printing worksheet Now, take this activity to assess yourself on what you have learned on printing worksheets.

Instructions:

1. Open an existing workbook.

2. Print Preview your worksheet.

3. Issue a print command.

4. In your print dialogue box, adjust your print setting.

5. Print your document.

6. List the steps for performing steps 1 to 5.

7. Exchange your write up in step 6 with a peer and go through to print your document again.

Note

Where you do not have a printer, your teacher will make available a printer and a computer to take this activity. He can also take you on a field trip to a printing press to observe how printing is done.

Activity 1.30 Importance of Print Preview.

After going through Activity 1.29, create a flyer to emulate three (3) importances of print previewing a document before final printing. Print your flyer for class presentation.