Demonstration on Excel Cells and Creating Formulas
Session 17: Demonstration on Excel Cells and Creating Formulas
Total Session Time: 60 minutes
Prerequisites
None
Learning Tasks
By the end of this session, students are expected to be able to:
Describe on How to Insert and Delete Cells
Describe Text and Cell Alignments
Explain How to Apply Font, Color and Borders to Cells
Explain How to Create Simple Formulas
Resources Needed:
Flip charts, marker pens, and masking tape
Black/white board and chalk/whiteboard markers
Computer and LCD
Handout 17.1: Monthly Budget
SESSION OVERVIEW
Step
Time
Activity/
Method
Content
1
05 minutes
Presentation
Presentation of Session Title and Learning Tasks
2
35 minutes
Brainstorming Presentation, Exercise
Inserting and Deleting Cells
3
35 minutes
Presentation, Exercise
Manage Text and Cell Alignments
4
35 minutes
Presentation, Exercise
Applying Font, Color and Borders to Cells
5
50 minutes
Buzzing Presentation, Exercise
Creating Simple Formula
6
5 minutes
Presentation
Key Points
7
5 minutes
Presentation
Evaluation
8
10 minutes
Presentation
Assignment
SESSION CONTENT
Step 1: Presentation of Session Title and Learning Tasks (5 minutes)
READ or ASK student to read the learning objectives and clarify.
ASK students if they have any questions before continuing
Step 2: Inserting and Deleting Cells (35 minutes)
Activity: Brainstorming (5 minutes)
Ask students to brainstorm on the following question:
What is a cell in excel worksheet?
ALLOW few students to respond?
WRITE their responses on the flip chart/ board
CLARIFY and SUMMARISE by using the content below
Cell is Intersection between row and column
Inserting a cell
When working in an Excel worksheet, you may need to insert or delete cells without inserting or deleting entire rows or columns.
To Insert Cells
Select the location where the new cell(s) should be inserted. It can be a single cell or a range of cells.
Right-click and choose Insert.
Note: You could also choose Insert Cell on the menu bar.
Figure 1: Inserting A Cell
Source: Goodwill Community Foundation 2002
The Insert dialog box opens. Select either:
Shift cells right to shift cells in the same row to the right.
Shift cells down to shift selected cells and all cells in the column below it downward.
Figure 2: Inserting Dialog Box
Source: Goodwill Community Foundation 2002
Choose an option and click the OK button.
Your result displays in the spreadsheet.
Figure 3: Inserted Figure
Source: Goodwill Community Foundation 2002
Deleting a cell
To Physically Delete the Cell from the Spreadsheet
Right-click and choose Delete.
Figure 4:Deletind Cell
Source: Goodwill Community Foundation 2002
The Delete dialog box opens. Select either:
Shift cells left to shift cells in the same row to the left.
Shift cells up to shift selected cells and all cells in the column above it upward.
Figure 5: Shifting Cell
Source: Goodwill Community Foundation 2002
Choose an option and click the OK button.
Your result displays in your spreadsheet.
Merging cells
In Excel, you have another alignment option available to you: merge and centre. This is performed when you want to select one or more cells and merge them into a larger cell. The contents will be centered across the new merged cell.
The picture below shows why we might want to merge two cells. The spreadsheet presents Last Month and This Month Sales and Expenses for Sally. Notice that Sally's name appears above the Last Month column. To evenly centre Sally's name across the two cells we would perform a merge and centre.
Figure 6: Merging Cell
Source: Goodwill Community Foundation 2002
To Merge Two Cells into One
Select the cells that you want to merge. It can be cells in a column, row or both columns and rows.
Click the Merge and Center button on the standard toolbar.
Figure 7:Marging Cell More Than Two
The two cells are now merged into one.
Source: Goodwill Community Foundation 2002
Step 3: Manage Text and Cell Alignments (35 minutes)
Using the Standard Toolbar to Align Text and Numbers in Cells
You've probably noticed by now that Excel 2003 left-aligns text (labels) and right-aligns numbers (values). This makes data easier to read.
Figure 8: Align and Numbers in cells
Source: Goodwill Community Foundation 2002
You do not have to leave the defaults. T
ext and numbers can be defined as left-aligned, right-aligned or centered in Excel 2003. The picture below shows the difference between these alignment types when applied to labels.
Text and numbers may be aligned using the left-align, center and right-align buttons of the Formatting toolbar:
To Align Text or Numbers in a Cell
Select a cell or range of cells
Click on the Left-Align, Center or Right-Align buttons in the standard toolbar.
The text or numbers in the cell(s) take on the selected alignment treatment.
Changing Horizontal Cell Alignment
We've previously seen how to align text or numbers using the left-align, center and right-align buttons in the standard toolbar. You can also define alignment in the Alignment tab of the Format Cells dialog box.
Figure 9: Horizonatal Alignment
Source: Goodwill Community Foundation 2002
The Horizontal section features a drop-down that contains the same left, center, and right alignment options in the picture above and several more:
Fill-"Fills" the cell with the current contents by repeating the contents for the width of the cell.
Justify-If the text is larger than the cell width, Justify wraps the text in the cell and adjusts the spacing within each line so that all lines are as wide as the cell.
Centre Across Selection-Contents of the cell furthest to the left are centred across the selection of cells. Similar to merge and centre, except the cells are not merged.
To Change Horizontal Alignment using the Format Cells Dialog Box
Select a cell or range of cells.
Choose Format Cells from the menu bar.
Figure 10: Format-Cell Option
Source: Goodwill Community Foundation 2002
(You could also right-click and choose Format Cells from the shortcut menu.)
The Format Cells dialog box opens.
Click the Alignment tab.
Figure 11:Alignment Text Option
Source: Goodwill Community Foundation 2002
Click the Horizontal drop-down menu and select a horizontal alignment treatment.
Click OK to apply the horizontal alignment to the selected cell(s).
Changing Vertical Cell Alignment
You can also define vertical alignment in a cell, similar to how it is done for horizontal alignment. In Vertical alignment, information in a cell can be located at the top of the cell, middle of the cell or bottom of the cell. The default is bottom.
Figure 12: Vertical Alignment view
Source: Goodwill Community Foundation 2002
To Change Vertical Alignment using the Format Cells Dialog Box:
Select a cell or range of cells.
Choose Format Cells from the menu bar
(You could also right-click and choose Format Cells from the shortcut menu.)
The Format Cells dialog box opens.
Click the Alignment tab.
Click the Vertical drop-down menu and select a vertical alignment treatment.
Click OK to apply the vertical alignment to the selected cell(s).
Changing Text Control
Text Control allows you to control the way Excel 2003 presents information in a cell. There are three types of Text control: Wrapped Text, Shrink-to-Fit and Merge Cells.
Figure 14: Text Control
Source: Goodwill Community Foundation 2002
The Wrapped Text wraps the contents of a cell across several lines if it's too large than the column width. It increases the height of the cell as well.
Shrink-to-Fit shrinks the text so it fits into the cell; the more text in the cell the smaller it will appear in the cell.
Merge Cells can also be applied by using the Merge and Center button on the standard toolbar.
To Change Text Control using the Format Cells Dialog Box
Select a cell or range of cells.
Choose Format Cells from the menu bar.
The Format Cells dialog box opens.
Click the Alignment tab.
Click on either the Wrapped Text, Shrink-to-Fit or Merge Cells check boxes-or any combination of them-as needed.
Click the OK button.
Changing Text Orientation
The fourth type of cell alignment in the Format Cells dialog box is Text Orientation, which allows text to be oriented 90 degrees in either direction up or down.
Figure 15: Changing Text Orientation
Source: Goodwill Community Foundation 2002
To Change Text Orientation using the Format Cells Dialog Box
Select a cell or cell range to be subject to text control alignment.
Choose Format Cells from the menu bar.
The Format Cells dialog box opens.
Click the Alignment tab.
Increase or decrease the number shown in the Degrees field or spin box.
Click the OK button.
Activities 3: Monthly Budget (Take-home assignment)
Refer students to Handout 17:1 Monthly Budget
Center the text horizontally in Column A and Row 2.
Apply a distributed vertical text alignment to Row 2.
Save your document.
Use the text control and text orientation features so that you are familiar with them.
Close the document without saving any of the formatting from the text control and text orientation features.
Step 4: Applying Font, Colour and Borders to Cells (35 minutes)
Change font type, size and colour
In Excel a font consists of three elements: Typeface or the style of the letter; Size of the letter; and Color of the letter. The default font in a spreadsheet is Arial 10 points, but the typeface and size can be changed easily.
Selecting a Font Typeface
The amount of typefaces available for use varies depending on the software installed on your computer.
Figure 19: Box for Different Font
Source: Goodwill Community Foundation 2002
To Apply a Typeface to Information in a Cell
Select a cell or range of cells.
Click on the down arrow to the right of the Font Name list box on the Formatting toolbar.
Figure 20
Figure 20: A Drop-Down List Of Available Fonts Appears.
Source: Goodwill Community Foundation 2002
Click on the Typeface of your choice.
The selection list closes and the new font is applied to the selected cells.
Change font type, size and colour (continued)
To Apply a Font Size to Information in a Cell
The "Font Size" list varies from typeface to typeface. The Arial font sizes, for example, are 8, 9, 10, 11, 12, 14, 16, 18, 20, 22, 24, 26, 28, 36, 48, and 72.
Figure 21: Example of Different Font Size
Source: Goodwill Community Foundation 2002
Select a cell or range of cells.
Click on the down arrow to the right of the font size list box on the Formatting toolbar.
Figure 22: A Drop Down List of Available Font Sizes Appears.
Source: Goodwill Community Foundation 2002
Click on the Font Size of your choice.
The selection list closes and the new font size is applied to the selected cells.
Change font type, size and colour (continued)
To Apply Colour to Information in Cells
Select a cell or range of cells.
Click on the down arrow to the right of the font Color list box.
Figure 23: A drop-down list of available Colors appears.
Source: Goodwill Community Foundation 2002
Click on the Color of your choice.
The selection list closes and the new font Color is applied to the selected cells.
Underline, italics and bold
In addition to the typeface, size and Color, you can also apply Bold, italics, and/or underline font style attributes to any text or numbers in cells.
To Select a Font Style
Select a cell or range of cells.
Click on any of the following options on the Formatting toolbar.
Bold button (Ctrl + B).
Italics button (Ctrl + I).
Underline button (Ctrl + U).
The attribute(s) selected (bold, italics, or underline) are applied to the font.
The Bold, Italics, and Underline buttons on the Formatting toolbar are like toggle switches. Click once to turn it on, click again to turn it off.
Design and apply styles
Styles can save a lot of time when formatting a spreadsheet. A Style is a unique collection of font attributes (Number, Alignment, Font, Border, Patterns and Protection). Many different styles can be created in a spreadsheet, each with different attributes and names. When applied to a cell, information in it resembles the attributes defined for that style.
To Apply a style
Select the cell or range of cells.
Choose Format Style from the menu bar.
Figure 24: Formatting Style Box
Source: Goodwill Community Foundation 2002
Figure 25: Select A Style From The Style Name Drop-Down List.
Source: Goodwill Community Foundation 2002
You can change the style attributes (Number, Alignment, Font, Border, Patterns and Protection) for any Style Name.
You can create new styles by clicking on the Add button in the Style dialog box.
Adding a border to cells
Borders can be applied to cells in your worksheet in order to emphasize important data or assign names to columns or rows.
To Add a Border to a Cell or Cell Range
Select a cell or range of cells.
Click on the down arrow next to the Borders button.
The Border drop-down appears.
Choose a borderline style from the Border drop-down menu.
The selected cells display the chosen border.
Adding Colour to Cells
Colors can be applied to cells in your worksheet in order to emphasize important data or assign names to columns or rows.
To Add Colour to a Cell
Select a cell or range of cells.
Click the down arrow next to the Fill Color button. A Fill Color drop-down menu displays.
Figure 26: Figure 27: Choose a Fill Color From The Fill Color Drop-Down Menu.
Source: Goodwill Community Foundation 2002
Step 5: Create Simple Formulas (50 minutes)
Activity: Buzzing (5 minutes)
ASK students to pair up and buzz on the following question for 2 minutes
What are formulas?
ALLOW few pairs to respond and let other pairs to add on points not mentioned
WRITE their response on the flip chart/board
CLARIFY and SUMMARIZE by using the content below
Formulas
In school, you learned formulas used to calculate math problems. Microsoft Excel uses these same formulas to perform calculations in a spreadsheet.
The following table illustrates the mathematical operators learned in school and those represented in Excel.
School
Excel
Addition
+
+
Subtraction
–
–
Multiplication
X
*
Division
/
/
Equals
The result of a formula-the answer to 2+3, for example-displays in the cell on the Excel worksheet. The formula is visible only in the formula bar. A formula's result will change as different numbers are entered into the cells included in the formula's definition.
Creating a Simple Addition Formula
A simple formula in Excel contains one mathematical operation only: one number plus a second number equals a third number. Writing a simple formula is really no more difficult than that: 1+1. The only difference in Excel is that all formulas must begin with the equal sign (=). It is not enough to type 1+1 in Excel because what will appear in the cell is "1+1." You must begin the equation with an equal sign, or =1+1. This holds true for any formula, simple or complicated, that adds, subtracts, multiplies or divides.
Figure 1: Exaple of Simple Mathematics
Source: Goodwill Community Foundation 2002
To Create a Simple Formula that Adds Two Numbers
Click the cell where the formula will be defined.
Type the first number to be added (128, for example)
Type the addition sign (+) to let Excel know that an add operation is to be performed.
Type the second number to be added (345, for example
Press Enter or click the Enter button on the Formula bar to complete the formula.
Creating a Simple Addition Formula (continued)
But what if a column contains many numbers, each of which regularly changes? You don't want to write a new formula each time a number is changed. Luckily, Excel 2003 lets you include cell references in formulas.
A formula can add the value of two cells-B2 and B3, for example. Type any two values in these two cells and the formula will adjust the answer accordingly.
Figure 2: Show Formula and Total
Source: Goodwill Community Foundation 2002
To Create a Simple Formula that Adds the Contents of Two Cells:
Type the numbers you want to calculate in separate cells (for example, type 128 in cell B2 and 345 in cell B3).
Click the cell where the answer will appear (B4, for example).
Type the cell number that contains the first number to be added (B2, for example).
Type the addition sign (+) to let Excel know that an add operation is to be performed.
Type the cell number that contains the first number to be added (B3, for example).
Press Enter or click the Enter button on the Formula bar to complete the formula.
Creating a Simple Subtraction Formula Using the Point-and-Click Method
Formulas can be created by using either numbers or cell references in the definition. You can also use the mouse to select the cells to be used in the formula instead of typing the cell number or cell reference. Using this method, we are going to write a simple formula that subtracts one cell from another: =B3-B2.
Figure 3: Subtraction Formula
Source: Goodwill Community Foundation 2002
To Create a Simple Formula using the Point and Click Method
Type the numbers you want to calculate in separate cells (for example, type 128 in cell B2 and 345 in cell B3).
Click the cell where the answer will appear (B4, for example).
Click on the first cell to be included in the formula (B3, for example).
Type the subtraction sign (-) to let Excel know that a subtraction operation is to be performed.
Click on the next cell in the formula (B2, for example).
If you include multiple cells in the formula, repeat steps 4 and 5 until the entire formula is entered.
Press Enter or click the Enter button on the Formula bar to complete the formula.
Creating Simple Multiplication Formulas
Figure 3: Multiplication Formula
Source: Goodwill Community Foundation 2002
To Create a Simple Formula that Multiplies the Contents of Two Cells
Type the numbers you want to calculate in separate cells (for example, type 128 in cell B2 and 345 in cell B3).
Click the cell where the answer will appear (B4, for example).
Click on the first cell to be included in the formula (B2, for example).
Type a mathematical operator (Ex: the multiplication symbol *). The operator displays in the cell and Formula bar.
Click on the next cell in the formula (B3, for example).
If you include multiple cells in the formula, repeat steps 4 and 5 until the entire formula is entered.
Press Enter or click the Enter button on the Formula bar to complete the formula.
Creating Simple Division Formulas
Figure 4: Divides Formula
Source: Goodwill Community Foundation 2002
To Create a Simple Formula that Divides One Cell by Another
Type the numbers you want to calculate in separate cells (for example, type 128 in cell B2 and 345 in cell B3).
Click the cell where the answer will appear (B4, for example).
Click on the first cell to be included in the formula (B2, for example).
Type a mathematical operator (Ex: the division symbol /). The operator displays in the cell and Formula bar.
Click on the next cell in the formula (B3, for example).
If you include multiple cells in the formula, repeat steps 4 and 5 until the entire formula is entered.
Very Important: Press Enter or click the Enter button on the Formula bar. This step ends the formula.
Filling Formulas to Other Cells
Sometimes, you will write a formula that gets used a lot in different places of a worksheet. For example, a spreadsheet may contain several columns of numbers. Each column will contain a formula that adds all the numbers in it. You could write the formula several times, once in each column. Or you could copy-and-paste it into each column. The fill formula method allows you to copy a formula and fill it into many different consecutive cells at the same time.
The mouse pointer changes to a black crosshair when passed over the fill handle, or the square box in the lower right corner of the cell.
Figure 6: Filling Formulas to Other Cell
Source: Goodwill Community Foundation 2002
To Use the Fill Handle to Copy a Formula to a Surrounding Cell
Click on the cell that contains the formula to be copied.
Position the mouse pointer over the fill handle.
Click and hold the left mouse button, and then drag the contents to the cell that's to receive the fill formula.
Release the mouse button.
Select the Copy Cells option in the fill formula drop-down menu.
Figure 7: Fill Handle to Copy a Formula
Source: Goodwill Community Foundation
The cell references in a formula are automatically updated when the formula is copied to other cells in the spreadsheet.
You can also use copy and paste to copy a formula to other cells. Click next to learn more about the copy and paste method.
Copy and Paste Formulas
The process to copy and paste a formula is identical to that process used to copy and paste text.
To Copy and Paste a Formula
Select the cell that contains the formula to be copied.
Click the Copy button. Marching "ants" appear around the copied cell(s).
Figure 8: Marching ants appear
Source: Goodwill Community Foundation 2002
Select the cell where the copied formula is to be pasted.
Press the Enter key. The formula is copied to the new location.
Revising Formulas
You can revise any formula that was previously written in a worksheet.
To Revise a Formula using the Keyboard
Double-click the cell that contains the formula you want to revise.
The cursor can now move left and right between the values in the formula in cell B5.
Figure 9: To Replace Cell
Source: Goodwill Community Foundation 2002
Make the necessary changes to the formula.
Press the Enter key or click the Enter button to accept the new formula.
Finding the Sum of a Range of Data
The AutoSum function allows you to create a formula that includes a cell range-many cells in a column, for example, or many cells in a row.
Figure 13: AutoSum function
Source: Goodwill Community Foundation 2002
To Calculate the AutoSum of a Range of Data
Type the numbers to be included in the formula in separate cells of column B (Ex: type 128 in cell B2, 345 in cell B3, 243 in cell B4, 97 in cell B5 and 187 cell B6).
Click on the first cell (B2) to be included in the formula.
Using the point-click-drag method, drag the mouse to define a cell range from cell B2 through cell B6.
On the Standard toolbar, click the Sum button.
The sum of the numbers is added to cell B7, or the cell immediately beneath the defined range of numbers.
Source: Goodwill Community Foundation 200
Finding the Average of a Range of Numbers
The Average function calculates the average of a range of numbers. The Average function can be selected from the AutoSum drop-down menu.
To Calculate the Average of a Range of Data
Type the numbers to be included in the formula in separate cells of column B (Ex: type 128 in cell B2, 345 in cell B3, 243 in cell B4, 97 in cell B5 and 187 cell B6).
Click on the first cell (B2) to be included in the formula.
Using the point-click-drag method, drag the mouse to define a cell range from cell B2 through cell B6.
On the Standard toolbar, click on the drop-down part of the AutoSum button.
Select the Average function from the drop-down Functions list.
The average of the numbers is added to cell B7, or the cell immediately beneath the defined range of numbers.
Figure 15: Average Function from the Drop-Down
Source: Goodwill Community Foundation 2002
Source: Goodwill Community Foundation 2002
Accessing Excel Functions
To Access Other Functions in Excel
Using the point-click-drag method, select a cell range to be included in the formula.
On the Standard toolbar, click on the drop-down part of the AutoSum button.
If you don't see the function you want to use (Sum, Average, Count, Max, and Min), display additional functions by selecting More Functions.
The Insert Function dialog box opens.
There are three ways to locate a function in the Insert Function dialog box
Figure 17: Accessing Excel Functions
Source: Goodwill Community Foundation 2002
You can type a question in the Search for a function box and click GO, or
You can scroll through the alphabetical list of functions in the Select a function field, or
You can select a function category in the Select a category drop-down list and review the corresponding function names in the Select a function field.
Figure 18: Search for a Function Box
Source: Goodwill Community Foundation 2002
Select the function you want to use and then click the OK button.
Step 6: Key Points (5 minutes)
When working in an Excel worksheet, you may need to insert or delete cells without inserting or deleting entire rows or columns.
Text and numbers can be defined as left-aligned, right-aligned or centered in Excel.
Step 7: Evaluation (5 minutes)
Which steps are followed in inserting and deleting cells?
How can a text be aligned to 45 degrees?
Step 8: Assignment (10 minutes)
Activity: Take home Assignment (10 minutes)
DIVIDE students in groups or individual.
ASK the students to work on the following assignment
Insert a blank row above the current Row 1, which contains the months of the year.
Type My Budget in A1
Use the merge and centre function to centre My Budget over Columns A through N
Save and close the document
ALLOCATE time for students to do the assignment and submit
REFER students to recommended references
Handout 17.1: Monthly Budget
Reference
Bott, E. & Siechert, C. (2001). Microsoft Windows XP Inside Out. Microsoft Press
Herniter, M.E. (2000). Personal computer fundamentals for students, hardware windows 2000 Application (2nd ed). Prentice Hall.
Joos, I. Whitman, N. Smith, M. Nelson, R. et al. (2006). Introduction to computers for healthcare professionals (4th ed). Barb Mews: London.
Morris, M. & Charles, M. (2003). Logol Computer Designer Fundamentals. Prentice Hall.
University: Boston Burr Ridge.
Sagman, S. (1999), Microsoft Office 2000 for Windows: Visual Quick Start Guide. Peachpit Press The Basics of the Word Window. Retrieved from www.gcflearnfree.org/computer/
Handout 17.1: Monthly Budget
Handout 17.2: Monthly Budget
Unataka kutumiwa notes hizi kupitia WhatsApp?Kwa notes zilizopangiliwa vizuri kwa kusoma offline au PDF, bonyeza kitufe hapa chini. Ujumbe wenye Level, Semester, Module na Topic utaandaliwa moja kwa moja.TUMIWA NOTES WHATSAPP
