Electronic Data Processing with Software Application & Business Programming

Week 9

MODULE 5

I. MODULE TITLE:

Microsoft Excel Part 1

II. LEARNING OUTCOMES:

At the end of the module, the students will be able to:

 Examine spreadsheet concepts and explore the Microsoft Office Excel

environment.
 Create, open and view a workbook.
 Enter and edit data.
 Learn how to use the different Numbers Formats

III. LEARNING CONTENT:

CHAPTER 1 – GETTING STARTED WITH EXCEL

Excel is a spreadsheet program that allows you to store, organize,

and analyze information. While you may think Excel is only used by certain
people to process complicated data, anyone can learn how to take
advantage of the program's powerful features. Whether you're keeping a
budget, organizing a training log, or creating an invoice, Excel makes it easy to
work with different types of data.

Video Tutorial: [Link]

I. The Excel Start Screen

When you open Excel for the first time, the Excel Start Screen will appear.
From here, you'll be able to create a new workbook, choose a template, and
access your recently edited workbooks.
 From the Excel Start Screen, locate and select Blank workbook to access
the Excel interface.

1
II. The parts of the Excel window
Some parts of the Excel window (like the Ribbon and scroll bars) are standard
in most other Microsoft programs. However, there are other features that are
more specific to spreadsheets, such as the formula bar, name box,
and worksheet tabs.

1. Quick Access Toolbar

The Quick Access Toolbar lets you access common commands no matter
which tab is selected. You can customize the commands depending on
your preference.

2
2. The Ribbon
The Ribbon contains all of the commands you will need to perform
common tasks in Excel. It has multiple tabs, each with several groups of
commands.

3. Tell Me
The Tell me box works like a search bar to help you quickly find tools or
commands you want to use.

4. Microsoft Account
From here, you can access your Microsoft account information, view
your profile, and switch accounts.

3
5. Name Box
The Name box displays the location, or name, of a selected cell.

6. Formula Bar
In the formula bar, you can enter or edit data, a formula, or a function that
will appear in a specific cell.

7. Column
A column is a group of cells that runs from the top of the page to the
bottom. In Excel, columns are identified by letters.

4
8. Cell
Each rectangle in a workbook is called a cell. A cell is the intersection of a
row and a column. Simply click to select a cell.

9. Row
A row is a group of cells that runs from the left of the page to the right. In
Excel, rows are identified by numbers.

10. Worksheets
Excel files are called workbooks. Each workbook holds one or
more worksheets. Click the tabs to switch between them, or right-click for
more options.

5
11. Vertical and Horizontal Scroll Bars
The scroll bars allow you to scroll up and down or side to side. To do this,
click and drag the vertical or horizontal scroll bar.

12. Worksheet View Options

There are three ways to view a worksheet. Simply click a command to
select the desired view.

13. Zoom Control

Click and drag the slider to use the zoom control. The number to the right
of the slider reflects the zoom percentage.

6
 CHAPTER 2 – CELL BASICS

Whenever you work with Excel, you'll enter information—or content—

into cells. Cells are the basic building blocks of a worksheet. You'll need to learn
the basics of cells and cell content to calculate, analyze, and organize data in
Excel.

Video Tutorial: [Link]

I. Understanding Cells

Every worksheet is made up of thousands of rectangles, which are called cells.

A cell is the intersection of a row and a column. In other words, it's where a row
and column meet.

Columns are identified by letters (A, B, C), while rows are identified by numbers
(1, 2, 3). Each cell has its own name—or cell address—based on its column and
row. In the example below, the selected cell intersects column C and row 5, so
the cell address is C5.

Note that the cell address also appears in the Name box in the top-left corner,
and that a cell's column and row headings are highlighted when the cell is
selected.

7
You can also select multiple cells at the same time. A group of cells is known as
a cell range. Rather than a single cell address, you will refer to a cell range using
the cell addresses of the first and last cells in the cell range, separated by
a colon. For example, a cell range that included cells A1, A2, A3, A4, and A5
would be written as A1:A5. Take a look at the different cell ranges below:

 Cell range A1:A8

 Cell range A1:F1

8
 Cell range A1:F8

 If the columns in your spreadsheet are labeled with numbers instead of

letters, you'll need to change the default reference style for Excel.

II. Cell Content

Any information you enter into a spreadsheet will be stored in a cell. Each cell
can contain different types of content, including text, formatting, formulas,
and functions.
 Text: Cells can contain text, such as letters, numbers, and dates.

 Formatting attributes: Cells can contain formatting attributes that change the
way letters, numbers, and dates are displayed. For example, percentages
can appear as 0.15 or 15%. You can even change a cell's text or background
color.

9
 Formulas and functions: Cells can contain formulas and functions that
calculate cell values. In our example, SUM(B2:B8) adds the value of each cell
in the cell range B2:B8 and displays the total in cell B9.

10
A. To insert content:
1. Click a cell to select it. In our example, we'll select cell F9.

2. Type something into the selected cell, then press Enter on your keyboard.
The content will appear in the cell and the formula bar. You can also
input and edit cell content in the formula bar.

11
B. To delete (or clear) cell content:
1. Select the cell(s) with content you want to delete. In our example, we'll
select the cell range A10:H10.

2. Select the Clear command on the Home tab, then click Clear Contents.

12
 3. The cell contents will be deleted.

 You can also use the Delete key on your keyboard to delete content
from multiple cells at once. The Backspace key will only delete content
from one cell at a time.

C. To copy and paste cell content:

Excel allows you to copy content that is already entered into your spreadsheet
and paste this content to other cells, which can save you time and effort.
1. Select the cell(s) you want to copy. In our example, we'll select F9.

13
2. Click the Copy command on the Home tab, or press Ctrl+C on your
keyboard.

3. Select the cell(s) where you want to paste the content. In our example,
we'll select F12:F17. The copied cell(s) will have a dashed box around
them.

4. Click the Paste command on the Home tab, or press Ctrl+V on your
keyboard.

14
5. The content will be pasted into the selected cells.

15
 CHAPTER 3 – APPLYING NUMBER FORMATS

Whenever you're working with a spreadsheet, it's a good idea to use

appropriate number formats for your data. Number formats tell your
spreadsheet exactly what type of data you're using, like percentages (%),
currency ($), times, dates, and so on.

Video Tutorial: [Link]

Why use number formats?

Number formats not only make your spreadsheet easier to read, but they also
make it easier to use. When you apply a number format, you're telling your
spreadsheet exactly what types of values are stored in a cell. For example,
the date format tells the spreadsheet that you're entering specific calendar
dates. This allows the spreadsheet to better understand your data, which can
help ensure that your data remains consistent and that your formulas are
calculated correctly.
 If you don't need to use a specific number format, the
spreadsheet will usually apply the general number format by
default. However, the general format may apply some small
formatting changes to your data.

I. Applying Number Formats

Just like other types of formatting, like changing the font color, you'll apply
number formats by selecting cells and choosing the desired formatting
option. There are two main ways to choose a number format:

 Go to the Home tab, click the Number Format drop-down menu in

the Number group, and select the desired format.

16
  Click one of the quick number-formatting commands below the drop-
down menu.

 You can also select the desired cells and press Ctrl+1 on your keyboard to
access additional number-formatting options.

In this example, we've applied the Currency number format, which adds
currency symbols ($) and displays two decimal places for any numerical values.

If you select any cells with number formatting, you can see the actual value of
the cell in the formula bar. The spreadsheet will use this value for formulas and
other calculations.

17
II. Using number formats correctly
There's more to number formatting than selecting cells and applying a format.
Spreadsheets can actually apply number formatting automatically based on
the way you enter data. This means you'll need to enter data in a way the
program can understand, then ensure that cells are using the proper number
format. For example, the image below shows how to use number formats
correctly for dates, percentages, and times:

Now that you know more about how number formats work, we'll look at a few
number formats in action.

A. Percentage formats
One of the most helpful number formats is the percentage (%) format. It displays
values as percentages, like 20% or 55%. This is especially helpful when
calculating things like the cost of sales tax or a tip. When you type a percent
sign (%) after a number, the percentage number format will be be applied to
that cell automatically.

18
As you may remember from math class, a percentage can also be written as
a decimal. So 15% is the same thing as 0.15, 7.5% is 0.075, 20% is 0.20, 55% is 0.55,
and so on. You can review this lesson from our Math tutorials to learn more
about converting percentages to decimals.

There are many times when percentage formatting will be useful. For example,
in the images below notice how the sales tax rate is formatted differently for
each spreadsheet (5, 5%, and 0.05):

As you can see, the calculation in the spreadsheet on the left didn't work
correctly. Without the percentage number format, our spreadsheet thinks we
want to multiply $22.50 by 5, not 5%. And while the spreadsheet on the right still
works without percentage formatting, the spreadsheet in the middle is easier to
read.

B. Date formats
Whenever you're working with dates, you'll want to use a date format to tell the
spreadsheet that you're referring to specific calendar dates, like July 15, 2014.
Date formats also allow you to work with a powerful set of date functions that
use time and date information to calculate an answer.

Spreadsheets don't understand information the same way a person would. For
instance, if you type October into a cell, the spreadsheet won't know you're
entering a date so it will treat it like any other text. Instead, when you enter a
date, you'll need to use a specific format your spreadsheet understands,
like month/day/year (or day/month/year depending on which country you're
in). In the example below, we'll type 10/12/2014 for October 12, 2014. Our
spreadsheet will then automatically apply the date number format for the cell.

19
Now that we have our date correctly formatted, we can do different things with
this data. For example, we could use the fill handle to continue the dates
through the column, so a different day appears in each cell:

20
If the date formatting isn't applied automatically, it means the spreadsheet did
not understand the data you entered. In the example below, we've
typed March 15th. The spreadsheet did not understand that we were referring to
a date, so this cell is still using the general number format.

On the other hand, if we type March 15 (without the "th"), the

spreadsheet will recognize it as a date. Because it doesn't include a year, the
spreadsheet will automatically add the current year so the date will have all of
the necessary information. We could also type the date several other ways,
like 3/15, 3/15/2014, or March 15 2014, and the spreadsheet would still recognize
it as a date.

C. Other date formatting options

To access other date formatting options, select the Number Format drop-down
menu and choose More Number Formats. These are options to display the date
differently, such as including the day of the week or omitting the year.

21
The Format Cells dialog box will appear. From here, you can choose the desired
date formatting option.

As you can see in the formula bar, a custom date format not only changes the
actual date in our cell but also the way it's displayed.

22
 I. TEACHING AND LEARNING ACTIVITIES
 Lecture & Discussion
 Self-directed Learning using Module
 Drill and practice
 Watching sample video/s

II. REFERENCES:

III. FLEXIBLE TEACHING AND LEARNING MODALITY (FTLM) USED:

 Google Meet
 Online via Google Meet
 Asynchronous learning via messaging app
 Modular Approach

Prepared by:

Lyle Jan Z. Tarun

CCSICT, COS Faculty

Jerald B. Caguioa
CCSICT, COS Faculty

23