MY INFORMATICS PRACTICAL

BY: ODEH VICTOR C.

BARNAMFEL INFORMATION TECHNOLOGY INSTITUTE | Dan Magana Str, G.R.A, Gboko, Benue State.
 STEP BY STEP INFORMATICS PRACTICAL 1

MICROSOFT OFFICE EXCEL

CONTENTS

CONTENTS

INTRODUCTION TO SPREADSHEET SOFTWARE

1. What is Spreadsheet?
2. Uses of Spreadsheet.
3. Advantages and Disadvantages of Spreadsheet
4. Examples of Spreadsheet Software’s

INTRODUCTION TO MICROSOFT OFFICE EXCEL

1. What is Microsoft Office Excel
2. Steps in Launching Microsoft Office Excel
3. Features of Microsoft Office Excel

IDENTIFYING & CUSTOMIZATION

1. Workbook & Worksheet
2. Columns & Rows
3. Cells

FORMULAS AND FUNCTIONS

1. What is Formulas
2. Examples of Formulas
3. What is Function
4. Example of Function
5. Combination of Formulas and Function

PREPARING OF RECORDS
1. School Record
2. Sales Record
3. Payroll
4. Statistics
5. Budget

CHARTS AND PRINTING

shortkeys

Email: [Link]@[Link] BY: ODEH VICTOR C.

Facebook: [Link].39@[Link]
Whatsup: 08146998692
Tel: 08146336476, 08146998692, 08124120782, 08078216587, 09080175619
 STEP BY STEP INFORMATICS PRACTICAL 2

INTRODUCTION TO SPREADSHEET
What is Spreadsheet?

Spreadsheet is an electronics file that contains a grid of columns and rows used to organize related
data and to display results of calculations, enabling interpretation of quantitative data for decision
making. Performing calculations using a calculator and entering the results into a ledger can lead to
inaccurate values. If an input value is incorrect or needs to be updated, you have to recalculate the
results manually, which is time-consuming and can lead to inaccuracies. A spreadsheet makes data
entry changes easy. If the formulas are correctly constructed, the results recalculate automatically
and accurately, saving time and reducing room for error.

In this section, you will learn how to design spreadsheet. In addition, you will explore the Excel
window and learn the name of each window element. Then, you will enter text, values, and dates in
a Spreadsheet.

Uses of Spreadsheet

There are many different uses for spreadsheets; the main use for spreadsheets is using basic
formulas to work out various sums. They can also use to represent data graphically using graphs
and charts. In this document I plan to explore the various uses for spreadsheets in different areas
and focus on one specific area and look at the uses for a spreadsheet in that area and also I will look
at some of the limitations that using spreadsheets can bring.

1. One of the main uses for spreadsheets is in FINANCE, businesses can use a spreadsheet to
forecast sales, and show graphically how sales will rise or drop based on past events.
Spreadsheets can also be used to work out interest rates and monthly payments based on
variables such as time, i.e. 5 years, 10 years or amount borrowed and also both such as &
pound;100,000 borrowed over 30 years.
2. Another use for spreadsheets is in EDUCATION, spreadsheets can be used to store students
personal data along with grades and scores, this data can then be used to predict a student's
overall or average grade, this can also be used to forecast how they will do at higher levels. A
spreadsheet can also be used to gather all pupils' scores and represent the data in a chart
displaying the pass percentages for the school. A spreadsheet can also be used to record pay
records for staff of the school and when they get paid and how much they get paid.
3. Other uses for spreadsheets are in HEALTH CARE; health organisations such as the NHS can
use spreadsheets to record patient's details for easy access by doctors and other staff. They
can also be used to show the date they were admitted and the date they were signed out by
a doctor. And the doctor that is treating them. This can also be useful to see what dates are
busiest and other trends. And plan accordingly for the future.
4. Spreadsheets can be used by SHOP KEEPERS to keep detailed lists of their stock, how much
the item costs and the bar code number of the item, the shop keeper can also record sales of
each item in his/her stock and then use graphs to see what items are selling well, and also
what items are not selling so that the shop keeper can then change stock accordingly. As
spreadsheet can also be used for a shop owner to do their VAT and Tax returns at the end of
each quarter of the year.
Email: [Link]@[Link] BY: ODEH VICTOR C.
Facebook: [Link].39@[Link]
Whatsup: 08146998692
Tel: 08146336476, 08146998692, 08124120782, 08078216587, 09080175619
 STEP BY STEP INFORMATICS PRACTICAL 3

5. Spreadsheets can be used in SPORTS to record the team statistics, and then work out goal
per game percentages, and also it can be used to keep a game roster, games played and also
draw up graphs and charts that show team performance, it can also be used to get a players
averages such as average goals per game, injuries per season and other vital statistics. Also
making them a lot easier to be read and interpreted by the user.
6. The application area I have chosen to look into is Shop keeping: In shop keeping spreadsheet
applications can be very useful in shop keeping as it can help a shop owner keep a detailed
inventory of all the items in the shop keeping information such as availability and barcode
number easily accessible.
7. Shop keepers can also use spreadsheets to record sales of all of their stock. They can view
what items are selling well, what are not selling at all and what items are sold out. This data
can be used to make graphs, charts and tables this can be used by the shop keeper to decide
what he needs to get more off, or what stock he needs to order less of, or stop ordering. This
can also be useful for the shopkeeper to use when deciding what offers to put on certain
items.
8. Shop keepers can also use spreadsheets to calculate their finances; they can calculate daily
profit or loss and also predict using past sales what should happen for the next few weeks
months or years. They can also calculate their monthly, quarterly or yearly profits or losses
and they can do their VAT and Tax returns for the financial years. Making it easier, quicker
and more convenient with fewer mistakes.
9. Another use for spreadsheets by shop keepers is keeping staff details stored for quick and
easy hours, this also is a good way for managers or owners to see their wage expenses and
also keep track on who is working what hours and also staff holiday bookings, this makes it
easier than using a conventional book and allows comparison of data faster.
10. Spreadsheets can also be used by shop keepers by using them to work out break even points
if the shop keeper wanted to obtain loans from banks or investors for expansion or other
reasons.
11. One Spreadsheet use that would be useful for shop keepers is the tables, they can clearly
block out their inventories and using the "SORT" tool they can sort the data in any way they
want, from A - Z to number values. They can also group data types together making it a clear
more organised list than just typing it up in Microsoft word.
12. In conclusion, spreadsheets can help turn huge amounts of data into simple and easily
interpreted. They can also be used to sort and group large amounts of data in tables and make
them easier to read and explain. They also allow people to work out the average of a set of
data and cast predictions along with basic arithmetic. However they can be complicated to
use and sometimes very unclear.

Email: [Link]@[Link] BY: ODEH VICTOR C.

Facebook: [Link].39@[Link]
Whatsup: 08146998692
Tel: 08146336476, 08146998692, 08124120782, 08078216587, 09080175619
 STEP BY STEP INFORMATICS PRACTICAL 4

The Advantages & Disadvantages of Spreadsheets

In business, strategic planning is essential and requires valid information in order to make key
decisions. Choosing the right tools for inputting, tracking, analyzing and storing data will help
business owners and managers make the best choices for their company's business. One of the
components within software productivity suites is the spreadsheet. Spreadsheets are popular
among accountants and among those who like to collect and track data, yet there are some
limitations, which may not make them the best choice for every office application.

Efficiency (Advantage)

You need only enter values, variables and formulas into a spreadsheet once to calculate results. If
you notice a mistake in one number, or if a situation differs later, you only have to update the specific
entry. All related values change automatically. This lets you concentrate on entering new values or
planning what-if scenarios, rather than tediously re-entering editing numbers and formulas.

Formatting (Advantage)

The appearance of every part of the spreadsheet lies under your total control. You can specify the
font style and size used for numbers and text, define the border width and cell size of tables, add
images, and color everything with a brilliant palette. You can also export your creation in a variety
of file formats so it can be used in everything from printed documents, group presentations and
websites.

Organizing Data (Advantage & Disadvantage)

Spreadsheets are frequently the go to tool for collecting and organizing data, which is among the
simplest of its uses. Information can easily be placed in neat columns and rows and then sorted by
information type. Although a large collection of data may be overwhelming to view in its raw state,
tools within the program allow the user to create presentations where the data is analyzed and
plugged into pie charts or tables for easy viewing and interpretation. However, the downside is that
only the information that the user chooses for analysis is included in these presentations, and
therefore, other pertinent information that may influence decision making might be excluded,
unintentionally. To make reporting of data more user friendly and comprehensive, companies are
choosing to use reporting tools such as Tableau and Qlik, instead of relying solely on the
spreadsheet.

Calculations and Errors (Advantage & Disadvantage)

No one likes to spend all their time at work doing repetitive calculations. The great appeal of
spreadsheets is that the program does all the math for the user. Once a formula is written and the
program has a set command, complex calculations can easily be computed for the related data that
has been input. This allows users to ask "what if" types of questions and to easily get the answers
they need without the need to rework the calculations. For example, if the spreadsheet is set up to
calculate your gross profit, when any variable such as cost per unit, shipping costs, or sales discount
are changed, the software automatically recalculates the new gross profit based on the new
information.

Email: [Link]@[Link] BY: ODEH VICTOR C.

Facebook: [Link].39@[Link]
Whatsup: 08146998692
Tel: 08146336476, 08146998692, 08124120782, 08078216587, 09080175619
 STEP BY STEP INFORMATICS PRACTICAL 5

The difficult part for many users, however, is that the calculations must be entered into the
spreadsheet as formulas. This requires learning the correct syntax for each type of calculation you
wish to make. Although many classes are available to learn the skills necessary to use these
formulas, many users still find them difficult. If the syntax is incorrect, the program will not return
the correct information when the calculations are run. Additionally, if users input the wrong data,
even in only one cell of the spreadsheet, all related calculations and cells will be affected and have
incorrect data.

Security (Advantage & Disadvantage)

Another spreadsheet disadvantage is the lack of security for your files. Typically, spreadsheets are
not that secure and therefore are at greater risk for data corruption or mismanagement of
information. Files that contain sensitive financial information may not be safe from hackers, even if
password protected. Other types of data collection software therefore may be a more suitable
option. Access, Oracle or some other form of relational database has built in safeguards that protect
data integrity and prevent the reorganization of information. For example, in a spreadsheet, a user
might sort a column of information and may inadvertently cause related information, such as first
and last name, to become out of sync. In contrast, a database will keep all parts of a record unified,
thereby ensuring better data integrity.

Challenge!

Briefly List Two Advantage/Disadvantages and Explain

Email: [Link]@[Link] BY: ODEH VICTOR C.

Facebook: [Link].39@[Link]
Whatsup: 08146998692
Tel: 08146336476, 08146998692, 08124120782, 08078216587, 09080175619
 STEP BY STEP INFORMATICS PRACTICAL 6

INTRODUCTION TO MICROSOFT EXCEL

What is Microsoft Office Excel?

Microsoft Office Excel is a versatile program present on most computers at home and in computer
clusters. It is a business tool for accounting and managing large sets of data. It can also simplify
graphing and analyzing data from the labs.

Versions of Microsoft Excel

▪ Excel 97
▪ Excel 2000
▪ Excel 2003
▪ Excel 2007
▪ Excel 2010
▪ Excel 2013
▪ Excel 2016
▪ Excel 2019

Steps in Launching Microsoft Office Power point

Before you start working with Excel, You need to launch it, (Launching means opening) in the
memory of the computer, and we have three method by which we can launch the program.
Using the Keyboard Method (Shortcut)
Using the Mouse Method (Standard)
Using the Search Method (Combination of both the Mouse & Keyboard)

Using the Keyboard Method

You Hold down the Window Key + R
Type in Excel and Click Ok

Using the Mouse Method

On the Desktop Environment, Navigate your Mouse Pointer and Click on the Windows Logo
which is located at the left hand side of the Task Bar.
On the Pop-Up Menu that appears, Navigate your Mouse Pointer and click on “ALL
PROGRAMS”
On the Sub-Menu that appears, Navigate your Mouse Pointer and click on the (Folder)
“MICROSOFT OFFICE”
On the Sub-Sub-Menu that appears, Navigate your Mouse Pointer and click on the (Program)
“EXCEL”.

Email: [Link]@[Link] BY: ODEH VICTOR C.

Facebook: [Link].39@[Link]
Whatsup: 08146998692
Tel: 08146336476, 08146998692, 08124120782, 08078216587, 09080175619
 STEP BY STEP INFORMATICS PRACTICAL 7

Using the Search Method

Depress the Window Key + E (My Computer Menu)
Double click on Local Disk
Double click on Program Files
Double Click on Microsoft Office
Double click on Office e.g 12, 15 etc…
Choose the Program you want to launch by typing its Name Faster.
Click Ok to Launch the program

Elements of Microsoft Office Excel.

+ QUICK ACCESS TOOLBAR

TITLE BAR

THE RIBBON INSERT

CANCAL FUNCTION

FORMULA BAR EXPAND &

NAME BOX COLLAPES
ENTER FORMULAR BAR
SELECT ALL
SPITTER BAR

COLUMN HEADINGS

ACTIVE CELL

VERTICAL
ROW HEADINGS NAVIGATIONAL VERTICAL BUTTON
SCROLL
BUTTON

SHEET TAB NAVIGATIONAL HORIZONTAL BUTTON

INSERT NEW WORKSHEET

ZOOM BAR
HORIZONTAL VIEW BAR
NAVIGATIONAL BAR SCROLL BUTTON

STATUS BAR

1. The Title bar is located at the top in the center of the Excel window. The Title bar displays the
name of the presentation on which you are currently working and also the file name. By default,
Excel names presentations sequentially, starting with Workbook 1. When you save your file, you
can change the name of your Workbook. It also consist of four buttons (Minimize, Maximize,
Restore down, & Close) which is located at the top right corner of the title bar. You use the
Minimize button to remove a window from view. While a window is minimized, its title
appears on the taskbar. You click the Maximize button to cause a window to fill the screen.
After you maximize a window, clicking the Restore button returns the window to its former
smaller size. You click the Close button to exit the window and close the program.
Email: [Link]@[Link] BY: ODEH VICTOR C.
Facebook: [Link].39@[Link]
Whatsup: 08146998692
Tel: 08146336476, 08146998692, 08124120782, 08078216587, 09080175619
 STEP BY STEP INFORMATICS PRACTICAL 8

2. The Quick Access Toolbar It provides you with access to commands you
frequently use. By default, Save, Undo, and Redo appear on the Quick Access toolbar. You use
Save to save your file, Undo to rollback an action you have taken,
and Redo to reapply an action you have rolled back. It can also be
customized by making use of the drop down arrow that appears
at the right hand side of the bar.
3. File Menu is used for the customization of the application
window. It also consist of commands like New, Open, Save,
SaveAs, Print and Other important Commands.

TAB

COMMAND BUTTONS

COMMAND GROUP LAUNCHER

4. You use commands to tell PowerPoint what to do. In PowerPoint, you use the Ribbon to issue
commands. The Ribbon is located near the top of the PowerPoint window, below the Quick
Access toolbar. At the top of the Ribbon are several tabs; clicking a tab displays several related
command groups. Within each group are related command buttons. You click buttons to issue
commands or to access menus and dialog boxes. You may also find a dialog box. Launcher in the
bottom-right corner of a group. When you click the dialog box launcher, a dialog box makes
additional commands available.
5. Name Box: The Name Box is an identifier that displays the address of the current cell in the
worksheet. Use the Name Box to go to a cell, assign a name to one or more cells, or select a
function.
6. Cancel (x) : It helps clear an unwanted text in a cell without using the keyboard
7. Enter: With the absence of the keyboard you click the enter button after typing an information
in order the make the mouse cursor stop blinking in the cell.
8. Insert Functions (fx) : It displays the in-built formulae contained in the program that can be
used for arithmetical calculations
9. Formula Bar: A place where you can enter or view formulas or text.
10. Expand/Collapse Formula Bar Button: This button allows you to expand/Collapse the formula
bar. This is helpful when you have either a long formula or large piece of text in a cell.
11. The Splitter Bar enables the magnification of the Formula Bar. This is helpful when you have
either a very long formula or large piece of text in a cell.
12. Select All: The triangle at the intersection of the row and column headings in the top-left corner
of the worksheet. Click it to select everything contained in the active worksheet.
13. Active cell: The active cell is the current cell, which is indicated by a dark green border.
14. Column headings: The letters above the columns, such as A, B, C, and so on.
15. Row headings: The numbers to the left of the rows, such as 1, 2, 3, and so on.

Email: [Link]@[Link] BY: ODEH VICTOR C.

Facebook: [Link].39@[Link]
Whatsup: 08146998692
Tel: 08146336476, 08146998692, 08124120782, 08078216587, 09080175619
 STEP BY STEP INFORMATICS PRACTICAL 9

16. Worksheet Navigation Tabs: If your workbook contains several worksheets, Excel may not
show all the sheet tabs at the same time. Use the buttons to display the first, previous, next, or
last worksheet.
17. Sheet tab: A sheet tab shows the name of a worksheet contained in the workbook. When you
create a new Excel workbook, the default worksheet is named Sheet1.
18. Insert Worksheet Button: Click the Insert New Worksheet button to insert a new worksheet
in your workbook.
19. Horizontal/Vertical Scroll: Allows you to scroll vertically/horizontally in the worksheet.
20. Navigational Horizontal/Vertical Button: It enables you to scroll through the Worksheet
21. Status bar: Displays information about a selected command or operation in progress. For
example, it displays Select destination and press ENTER or choose Paste after you use the Copy
command.
22. View Bar: Allows you to View your Working Environment in different Styles
We have Three (3) types of View Buttons
Normal View: This is the “normal view” for working on a spreadsheet in Excel. A page break
is a divider that breaks a worksheet into separate pages for printing. Excel inserts automatic
vertical page breaks (shown as a broken line) based on paper size, margin settings, scaling
options, and the positions of any manual page breaks (shown as a solid line) that you insert.
Excel provides a Page Break Preview window in which you can quickly adjust automatic
page breaks to achieve a more desirable printed document.

23. Zoom allows you to magnify on the window.

We have four (4) types of Zoom

Zoom in:- Zooming in makes the window larger so you focus in on an object.
Zoom out:- Zooming out makes the window smaller so you can see the entire window.
Zoom Slider:- Enables you to Reduce & Increase the Size of your Working Environment.
Zoom to:- Enables you Zoom to any level of your Choice.

Challenge!

Draw the Diagram of Microsoft Excel Fully Labelled

Email: [Link]@[Link] BY: ODEH VICTOR C.

Facebook: [Link].39@[Link]
Whatsup: 08146998692
Tel: 08146336476, 08146998692, 08124120782, 08078216587, 09080175619
 STEP BY STEP INFORMATICS PRACTICAL 10

IDENTIFYING & CUSTOMIZATION

WORKBOOK & WORKSHEET COLUMNS & ROWS CELLS

IDENTIFYING
Workbook & Worksheet

Workbook: is a collection of one or more related worksheet contain within a single file.
Worksheet: is a single spreadsheet that typically contains descriptive labels. Numeric &
Alphabetical values, formulas, functions and graphical representation of data.

DESCRIPTIVE LABELS

FORMULAS & FUNCTIONS

NUMERIC & ALPHABETICAL VALUES

GRAPHI CAL REPRESENTATION OF DATA

WORKSHEET
WORKBOOK
Columns & Rows

A worksheet contains columns and rows, with each column and rows assigned
a heading. Columns are assigned alphabetical heading from columns A to Z,
continuing from AA to AZ, and then from BA to BZ until XFD, which is the last of
the possible 16,384 columns. Rows have numeric headings ranging from 1 to
1,048,576.

Cells.
Email: [Link]@[Link] BY: ODEH VICTOR C.
Facebook: [Link].39@[Link]
Whatsup: 08146998692
Tel: 08146336476, 08146998692, 08124120782, 08078216587, 09080175619
 STEP BY STEP INFORMATICS PRACTICAL 11

The intersection of a column and row is a CELL; a total of 17,179,869,184 cells are available in a
worksheet. Each cell has a unique cell address, identified by first its column letter and then its
row number. For example, the cell at the intersection of (column A) and (row 9) is cell A9. Cell
references are useful when referencing data in formulas, or in navigation.

CUSTOMIZATION
Workbook: is a collection of one or more related worksheet contain
within a single file. In customizing of the Book, we are going to learn
how we can
1. Create New Workbook: to create a new book, you need to
depress the Ctrl + N.
2. Save a Workbook: To save a Workbook, you need to depress
the Ctrl + S, type the Name of your choice on the File name and
click OK.
3. Password the Workbook: On the Workbook environment,
Click on the FILE TAB, and navigate to INFO. Click on Protect
Workbook and select ENCRYPT WITH PASSWORD, Now type
the Password of your Choice, Retype it and click OK.

Worksheet: is a single spreadsheet that typically contains descriptive labels. Numeric &
Alphabetical values, formulas, functions and graphical representation of data. In customizing of
the sheet, we are going to learn how we can

1. Create a New Sheet

▪ By making Use of the Insert Worksheet Icon
▪ By making use of the shortcut key (Shift + F11)
▪ By Right Clicking at any sheet. Which displays a Pop-Up Menu. Click on
Insert, a dialog box appears showing you two (2) Options (General and
Spreadsheet Solution). Click on General and select Worksheet OK
▪ By making Use of the Home Tab, On the Cells Segment group, click
on Insert Segment Button. A drop down Menu appears, then Click on
Insert Sheet, Automatically a New Sheet will be added to your
Workbook.

2. Delete a Sheet
▪ By Right Clicking on the sheet you wish to delete, on the Pop-up Menu that appears click on
delete, automatically the sheet will be remove from the book.
▪ By making Use of the Home Tab, On the Cells Segment group, click on Delete Segment
Button. A drop down Menu appears, then Click on Delete Sheet, Automatically a Sheet will
be added to your Workbook.

Email: [Link]@[Link] BY: ODEH VICTOR C.

Facebook: [Link].39@[Link]
Whatsup: 08146998692
Tel: 08146336476, 08146998692, 08124120782, 08078216587, 09080175619
 STEP BY STEP INFORMATICS PRACTICAL 12

3. Rename a Sheet
▪ By double clicking on the sheet you wish to Rename, if you double click on
a sheet, it will be selected telling you it’s ready for editing now you type the name you
want and Press Enter Key on the keyboard.
▪ By Right Clicking on the sheet you wish to Rename, When you right click a pop-up menu
appears, navigate your mouse pointer and click on Rename, automatically the sheet will
be selected. Now type the name you want and OK
▪ By making Use of the Home Tab, On the Cells Segment group, click on Format Segment
Button. A drop down Menu appears, then Click on Rename Sheet, Automatically the sheet
will be highlighted, type in your name and OK
4. Copy of a Sheet
▪ By right clicking on the sheet you wish to copy, on the popup menu that
appears, navigate your mouse pointer and click on Move or Copy. Then a
dialog box appears, you select the place you want it to be and Tick “Create a
Copy” and OK.
▪ By Making Use of the Home Tab, On the Cells Segment group, click on
Format Segment Button. A drop down Menu appears, Click on Move or Copy.
Then a dialog box appears, you select the place you want it to be and Tick
“Create a Copy” and OK.

TIPS: You can also copy a sheet to another book by right clicking on the sheet you wish
to copy, on the move or copy dialog box, Select the book you want to copy the sheet (If you
already created one) on “To Book” and Tick “Create a Copy” and OK.

5. Move a Sheet
▪ By Holding and Dragging the Sheet through the Sheet Tab.
▪ By right clicking on the sheet you wish to Move, On the popup menu that appears,
navigate your mouse pointer and click on Move or Copy, Select the Place you want the
Sheet to Move and Click OK
▪ By Making Use of the Home Tab, On the Cells Segment group, click on Format Segment
Button. A drop down Menu appears, Click on Move or Copy. Select the Place you want
the Sheet to Move and OK.

TIPS: You can also move a sheet to another book by right clicking on the sheet you wish to move, on the move or copy
dialog box, Select the book you want to move the sheet (If you already created one) on “To Book” and OK.

Email: [Link]@[Link] BY: ODEH VICTOR C.

Facebook: [Link].39@[Link]
Whatsup: 08146998692
Tel: 08146336476, 08146998692, 08124120782, 08078216587, 09080175619
 STEP BY STEP INFORMATICS PRACTICAL 13

6. Change the Sheet Color

▪ Right click on the sheet you wish to change, on the popup menu
that appears, Navigate your mouse pointer and click on “Tab
Colour”, then a fly out menu appears displaying numerous of
Colors, Click on any color of your choice and execute.
▪ By Making Use of the Home Tab, On the Cells Segment group,
click on Format Segment Button. A drop down Menu appears,
Click on “Tab Colour”, then a fly out menu appears displaying
numerous of Colors, Click on any color of your choice and
execute.

7. Protect Our Sheet

▪ Right click on the sheet you wish to protect Sheet, on the dialog
box that appears, Type in your Password in the box provided
and OK. Another dialog box will appear telling you to reenter
the password, retype the password and click Enter.
▪ By Making Use of the Home Tab, On the Cells Segment group,
click on Format Segment Button. A drop down Menu appears,
Click on Protect Sheet, on the dialog box that appears, Type in
your Password in the box provided and OK. Another dialog box
will appear telling you to reenter the password, retype the
password and click Enter.

8. Hide a sheet
▪ By right clicking on the sheet you wish to hide, a popup menu
appears, Navigate your mouse pointer and click on Hide then it
will automatically hide.
▪ By Making Use of the Home Tab, On the Cells Segment group,
click on Format Segment Button. A drop down Menu appears,
Click on Hide or Unhide. On the Flyout menu, click on Hide Sheet
and automatically it will be hidden from view.

9. Unhide a sheet
▪ Right click at any active sheet, on the popup menu that appears
Navigate to unhide; on the dialog box that appears, choose the
sheet you wish to Unhide and Click OK.
▪ By Making Use of the Home Tab, On the Cells Segment group,
click on Format Segment Button. A drop down Menu appears,
Click on Hide or Unhide. On the Flyout menu, click at unhide
Sheet and automatically a dialog box will appear, select the sheet
you wish to unhide and OK.

Email: [Link]@[Link] BY: ODEH VICTOR C.

Facebook: [Link].39@[Link]
Whatsup: 08146998692
Tel: 08146336476, 08146998692, 08124120782, 08078216587, 09080175619
 STEP BY STEP INFORMATICS PRACTICAL 14

Columns & Rows: A worksheet contains columns and rows, with each column and rows assigned a
head. Columns are assigned alphabetical heading from columns A to Z, continuing from AA to AZ,
and then from BA to BZ until XFD, which is the last of the possible 16,384 columns. Rows have
numeric headings ranging from 1 to 1, 048,576. In customization of the Columns & Rows we are
going to learn we can
1. Insert Rows/Columns
▪ Right click on the column/row heading and navigate your
mouse pointer and click on Insert and automatically a new
row/column will be lefty inserted.
▪ By Making Use of the Home Tab, On the Cells Segment group,
click on Insert Segment Button. A drop down Menu appears,
Click on Insert Column/Row.

2. Delete/Clear Content in a Rows/Columns

▪ Right click on the Column/Row Heading you wish to delete, on
the drop down menu that appear click on Delete/Clear
Content. And automatically the contents/information inside
that very Row/Column will be removed. (NB. It will not delete the
Row/Columns Headings, It will only erase the contents that are inside the
Row/Column, for it is dogmatic).
▪ Select the Column/Row Heading you wish to clear its contents,
Make use of the Delete Key on the keyboard.
▪ By Making Use of the Home Tab, On the Cells Segment group,
click on Delete Segment Button. A drop down Menu appears,
Click on Delete Column/Row.

3. Increase the Columns Weight/Rows Height

▪ On the Column/Row Heading, Navigate your mouse pointer and
position it in between the line that holds the headings together,
click and hold then drag to either increase or decrease the size of
the Column/Row.
▪ Select, navigate your mouse pointer and click on format. On the
drop down menu, Click on either “Autofit Column Width/ Autofit
Row Height to adjust the size for you automatically. But just in
case you want to measure it yourself, then you click on Column Width/ Row Height which we
display a little box for you telling you to measure it yourself and OK.

Email: [Link]@[Link] BY: ODEH VICTOR C.

Facebook: [Link].39@[Link]
Whatsup: 08146998692
Tel: 08146336476, 08146998692, 08124120782, 08078216587, 09080175619
 STEP BY STEP INFORMATICS PRACTICAL 15

4. Hide a Column/Row
▪ Right click on the column Headings, on the dropdown menu that
appears click on Hide.
▪ Making Use of the Home Tab, On the Cells Segment group, click on
Format Segment Button. A drop down Menu appears, Click on
Hide Column/Row

5. Unhide a Column/Row
On the Column/Row Heading, Navigate your mouse pointer and
position it in between the line that the hidden Column/Row is,
click and hold then drag to either increase or decrease the size of the Column/Row.

6. How to Freeze Pane: This enables a particular row or column heading to stop rolling… For you
to apply it to your worksheet, You need to select the row or column heading you wish to
freeze, Navigate your mouse pointer, on the “VIEW TAB”, click on Freeze Pane, and
automatically that particular row/column heading will stop rolling as the others are rolling.

The Difference between a Sheet & a Column/Rows

SHEETS COLUMNS/ROWS
▪ Hiding is not easily Noticeable ▪ Hiding is easily Noticeable
▪ Editing Sheet name is possible ▪ Editing C/R name is not possible
▪ Rearranging of Sheets is possible ▪ Rearranging of C/R is not possible
▪ Moving of Sheets is easily ▪ Moving of C/R is easily

The Cells and Contents: The intersection of a column and row is a cell and also the
Information/Data you type into a cell is known as Contents. In customization of the Cells and
Contents, we are going to learn we can.
Navigating in Cells
The active cell is the current cell. Excel displays a dark green border around the active cell in the
worksheet, and the cell address of the active cell appears in the Name Box. The contents of the
active cell, or the formula used to calculate the results of the active cell, appear in the Formula Bar.
You can change the active cell by using the mouse to click in a different cell. If you work in a large

Email: [Link]@[Link] BY: ODEH VICTOR C.

Facebook: [Link].39@[Link]
Whatsup: 08146998692
Tel: 08146336476, 08146998692, 08124120782, 08078216587, 09080175619
 STEP BY STEP INFORMATICS PRACTICAL 16

worksheet, use the vertical and horizontal scroll bars to display another area of the worksheet and
click in the desired cell to make it the active cell.
To navigate to a new cell, click it or use the arrow keys on the keyboard. When you press Enter,
the next cell down in the same column becomes the active cell. The Table below lists the keyboard
navigation methods. The Go To command is helpful for navigating to a cell that is not visible
onscreen.
Keystrokes and Actions

Inserting of Contents
Text is any combination of letters, numbers, symbols, and spaces not used in calculations. Excel
treats phone numbers, such as 555-1234, and Social Insurance Numbers, such as 123-456-789, as
text entries. You enter text for a worksheet title to describe the contents of the worksheet, as row
and column labels to describe data, and as cell data. To enter text in a cell, do the following:
1. Make sure the cell is active where you want to enter text.
2. Type the text.
3. Do one of the following to make another cell the active cell after entering data:

Press Enter on the keyboard.

Press an arrow key on the keyboard.
Press Tab on the keyboard.
Do one of the following to keep the current cell the active cell after entering data:

Press Ctrl+Enter.
Click Enter (the check mark between the Name Box and the Formula Bar).

Email: [Link]@[Link] BY: ODEH VICTOR C.

Facebook: [Link].39@[Link]
Whatsup: 08146998692
Tel: 08146336476, 08146998692, 08124120782, 08078216587, 09080175619
 STEP BY STEP INFORMATICS PRACTICAL 17

Values are numbers that represent a quantity or a measurable amount. Excel usually distinguishes
between text and value data based on what you enter. The primary difference between text and
value entries is that value entries can be the basis of calculations, whereas text cannot. Values align
at the right cell margin by default. After entering values, you can align decimal places and apply
formatting by adding characters, such as $ or %.

Dates; you can enter dates and times in a variety of formats in cells, such as 9/1/2016; 9/1/16;
September 1, 2016; or 1-Sep-16. You can also enter times, such as 1:30 PM or 13:30. You should
enter a static date to document when you create or modify a workbook or to document the specific
point in time when the data were accurate, such as on a balance sheet or income statement. Later,
you will learn how to use formulas to enter dates that update to the current date. Dates are values,
so they align at the right cell margin.

Excel displays dates differently from the way it stores dates. Excel stores dates as serial
numbers starting at 1 with January 1, 1900, so 9/1/2019 is stored as 43466 so that you can create
formulas, such as to calculate how many days exist between two dates.

Edit and Clear Cell Contents

You can edit a cell’s contents by doing one of the following:

Click the cell, click in the Formula Bar, make the changes, and then click Enter (the check mark
between the Name Box and the Formula Bar) to keep the cell the active cell.

Double-click the cell, make changes in the cell, and then press Enter.
Click the cell, press F2, make changes in the cell, and then press Enter.
You can clear a cell’s contents by doing one of the following:

Click the cell and press Delete.

Click the cell, click Clear in the Editing group on the HOME tab, and then select Clear Contents.

Highlighting/Selecting Areas Using the Mouse To Select Cells: Click and

drag using the big puffy cross

To Select a Column: Click on the column letter

To Select a Row:
Click on the row number

To Select the Entire Worksheet:

Click above row 1 and to the left of column A
Auto fill
Simplify entering repetitive or sequential lists of information, and also it allows preprogrammed
lists, as well as custom lists, to be easily added to a spreadsheet.
Email: [Link]@[Link] BY: ODEH VICTOR C.
Facebook: [Link].39@[Link]
Whatsup: 08146998692
Tel: 08146336476, 08146998692, 08124120782, 08078216587, 09080175619
 STEP BY STEP INFORMATICS PRACTICAL 18

Excel recognizes January as the beginning of a natural series and completes the series as far as
you take the fill handle. By definition, a natural series is a formatted series of text or numbers
that are in a normal sequence such as months, weekdays, numbers, or times.
For example, a natural series of numbers could be 1, 2, 3, or 100, 200, 300, or a
natural series of text could be Monday, Tuesday, Wednesday, or January,
February, March.

Examples of Auto fill Series

Initial Selection Extended Series
1 1 , 1, 1, 1, …
1, 2 3 , 4, 5, …
2017, 2018 2019 , 2020, 2021, …
8:00 9:00 , 10:00, 11:00, …
6:00 PM 7:00 PM, 8:00 PM, …
Mon Tue, Wed, Thu, …
Monday Tuesday, Wednesday, Thursday, …
Jan Feb, Mar, Apr, …
January February, March, April, …
Qtr1 Qtr2, Qtr3, Qtr4, Qtr1, …
2/8/2017, 2 /22/2017, 3/1/2017, 3/8/2017, …
2/15/2017
1st anytext 2nd anytext, 3rd anytext, 4th anytext, …
Anytext 1 Anytext 2, Anytext 3, Anytext 4, …

Note that you might have to select two cells rather than one to continue some of the previous
patterns. To create your own custom list, go to File > Options > Advanced > General section > Edit
Custom Lists.

Using Auto Fill

Auto Fill enables you to copy the contents of a cell or a range of cells by dragging the fill handle
(a small green square appearing in the bottom-right corner of the active cell) over an adjacent cell or range
of cells. To use Auto Fill, do the following:

1. Click the cell with the content you want to copy to make it the active cell.
2. Point to the fill handle in the bottom-right corner of the cell until the mouse pointer changes to
the fill pointer (a thin black plus sign).
3. Drag the fill handle to repeat the content in other cells.

Email: [Link]@[Link] BY: ODEH VICTOR C.

Facebook: [Link].39@[Link]
Whatsup: 08146998692
Tel: 08146336476, 08146998692, 08124120782, 08078216587, 09080175619
 STEP BY STEP INFORMATICS PRACTICAL 19

Copy Formulas with Auto Fill

After you enter a formula in a cell, you can duplicate the formula without retyping it by using the
fill handle to copy the formula in the active cell down a column or across a row, depending on
how the data are organized. Excel adapts each copied formula based on the type of cell references
in the original formula.

Complete Sequences with Auto Fill

You can also use Auto Fill to complete a sequence. For example, if you enter January in a cell, you
can use Auto Fill to enter the rest of the months in adjacent cells. Other sequences you can complete
are quarters (Qtr 1, etc.), weekdays, and weekday abbreviations, by typing the first item and using
Auto Fill to complete the other entries. For numeric sequences, however, you must specify the first
two values in sequence. For example, if you want to fill in 5, 10, 15, and so on, you must enter 5 and
10 in two adjacent cells, select the two cells, and then use Auto Fill so that Excel knows to increment
by 5.
Immediately after you use Auto Fill, Excel displays the Auto Fill Options button in the
bottomright corner of the filled data (Auto Fill Example). Click Auto Fill Options to display five fill
options: Copy Cells, Fill Series, Fill Formatting Only, Fill without Formatting, or Flash Fill.

Clipboard Tasks
Although you plan worksheets before entering data, you might decide to move data to a different
location in the same worksheet or even in a different worksheet. Instead of deleting the original
data and then typing it in the new location, you can select and move data from one cell to another.
In some instances, you might want to create a copy of data entered so that you can explore
different values and compare the results of the original data set and the copied and edited data
set.

In this section, you will learn how to select different ranges. Then you will learn how to move a
range to another location, make a copy of a range, and use the Paste Special feature.

Incremented values filled in

Click to see Auto Fill Options

Auto Fill Examples

Email: [Link]@[Link] BY: ODEH VICTOR C.

Facebook: [Link].39@[Link]
Whatsup: 08146998692
Tel: 08146336476, 08146998692, 08124120782, 08078216587, 09080175619
 STEP BY STEP INFORMATICS PRACTICAL 20

Selecting, Moving, Copying, and Pasting Data

You may already know the basics of selecting, cutting, copying, and pasting data in other programs,
such as Microsoft Word. These tasks are somewhat different when working in Excel.

Select a Range
A range refers to a group of adjacent or contiguous cells. A range may be as small as a single cell
or as large as the entire worksheet. It may consist of a row or part of a row, a column or part of a
column, or multiple rows or columns, but will always be a rectangular shape, as you must select
the same number of cells in each row or column for the entire range. A range is specified by
indicating the top-left and bottom-right cells in the selection. The date is a single-cell range in cell
A2, the Colour Laser Printer data are stored in the range A6:G6, the cost values are stored in the
range B5:B10, and the sales prices and profit margins are stored in range F5:G10. A nonadjacent
range contains multiple ranges, such as B5:B10 and F5:G10. At times, you need to select
nonadjacent ranges so that you can apply the same formatting at the same time, such as
formatting the nonadjacent range C5:C10 and E5:E10 with Percent Style.

Quick Analysis button

Single-cell range 9/1/2016

Range of cells

Range in a row

Range in a column

The Table below lists methods you can use to select ranges, including nonadjacent ranges.

Selecting Rangers
To Select: Do This:
A range Drag until you select the entire range. Alternatively, click the
first cell in the range, press and hold Shift, and then click the
last cell in the range.
An entire column Click the column heading.
An entire row Click the row heading.

Current range containing Click in the range of data and press Ctrl+A.
data
All cells in a worksheet Click Select All or press Ctrl+A twice.

Nonadjacent range Select the first range, press and hold Ctrl, and then select
additional range(s).

Email: [Link]@[Link] BY: ODEH VICTOR C.

Facebook: [Link].39@[Link]
Whatsup: 08146998692
Tel: 08146336476, 08146998692, 08124120782, 08078216587, 09080175619
 STEP BY STEP INFORMATICS PRACTICAL 21

A green border appears around a selected range, and the Quick Analysis button displays in the
bottom-right corner of the selected range. Any command you execute will affect the entire range.
The range remains selected until you select another range or click in any cell in the worksheet.
Move a Range to another Location
You can move cell contents from one range to another. For example, you might need to move an
input area from the right side of the worksheet to above the output range. When you move a range
containing text and values, the text and values do not change. However, any formulas that refer to
cells in that range will update to reflect the new cell addresses. To move a range, do the following:
1. Select the range.
2. Use the Cut command to copy the range to the Clipboard. Unlike cutting data in other Microsoft
Office applications, the data you cut in Excel remain in their locations until you paste them
elsewhere. After you click Cut, a moving dashed green border surrounds the selected range and
the status bar displays Select destination and press ENTER or choose Paste.
3. Make sure the destination range—the range where you want to move the data—is the same size
or greater than the size of the cut range. If any cells within the destination range contain data,
Excel overwrites that data when you use the Paste command.
4. Click in the top-left corner of the destination range, and then use the Paste command to insert
the data contained in the selected range and remove that data from the original range.

Copy and Paste a Range

You may need to copy cell contents from one range to another. For example, you might copy your
January budget to another worksheet to use as a model for creating your February budget. When
you copy a range, the original data remain in their original locations. Cell references in copied
formulas adjust based on their relative locations to the original data. To copy a range, do the
following:

1. Select the range

2. Use the Copy command to copy the contents of the selected range to the Clipboard. After you click
Copy, a moving dashed green border surrounds the selected range and the status bar displays
Select destination and press ENTER or choose Paste.
3. Make sure the destination range—the range where you want to copy the data—is the same size or
greater than the size of the copied range. If any cells within the destination range contain data,
Excel overwrites that data when you use the Paste command.
4. Click in the top-left corner of the destination range where you want the duplicate data, and then
use the Paste command. The original range still has the moving dashed green border, and the
pasted copied range is selected with a solid green border. Figure 30 shows a selected range and a
copy of the range.
5. Press Esc to turn off the moving dashed border around the originally selected range.

Email: [Link]@[Link] BY: ODEH VICTOR C.

Facebook: [Link].39@[Link]
Whatsup: 08146998692
Tel: 08146336476, 08146998692, 08124120782, 08078216587, 09080175619
 STEP BY STEP INFORMATICS PRACTICAL 22

Click to Copy

Click to Paste

Click to see paste options

Original range

Duplicate data pasted here

Instructions on status bar

Use Paste Options and Paste Special

Sometimes you might want to paste data in a different format than they are in the Clipboard.
For example, you might want to copy a range containing formulas and cell references, and paste
the range as values in another workbook that does not have the referenced cells. If you want to
copy data from Excel and paste them into a Word document, you can paste the Excel data as a
worksheet object, as unformatted text, or in another format. To paste data from the Clipboard
into a different format, click the Paste arrow in the Clipboard group, and hover over a command
to see a Scrip Tip and a preview of how the pasted data will work. The preview below shows that
a particular paste option will maintain formulas and number formatting: however, it will not
maintain the text formatting, such as font colour and centered text. After previewing different
paste options, click the one you want in order to apply it.

Click Paste arrow

ScreenTip when mouse

is over option

Displays how pasted data will

look with current Paste option

Email: [Link]@[Link] BY: ODEH VICTOR C.

Facebook: [Link].39@[Link]
Whatsup: 08146998692
Tel: 08146336476, 08146998692, 08124120782, 08078216587, 09080175619
 STEP BY STEP INFORMATICS PRACTICAL 23

For more specific paste options, click the Paste arrow, and then select Paste Special to display the
Paste Special dialog box. This dialog box contains more options than the Paste menu. Click the
desired option and click OK.

Copy Excel Data to Other Programs

You can copy Excel data and use it in other applications, such as in a Word document or in a
PowerPoint slide show. For example, you
might perform statistical analyses in Excel,
copy the data into a research paper in Word
or create a budget in Excel, and then copy
the data into a PowerPoint slide show for a
meeting.

After selecting and copying a range in Excel,

you must decide how you want the data to
appear in the destination application. Click
the Paste arrow in the destination
application, such as Word, to see a gallery of
options or to select the Paste Special option.

Formatting
After entering data and formulas, you should format the worksheet. A professionally formatted
worksheet—through adding appropriate symbols, aligning decimals, and using fonts and colours
to make data stand out—makes finding and analyzing data easy. You apply different formats to
accentuate meaningful details or to draw attention to specific ranges in a worksheet.

In this section, you will learn to apply different alignment options, including horizontal and
vertical alignment, text wrapping, and indent options. In addition, you will learn how to format
different types of values.
Wrap Text

Vertical alignment

Horizontal alignment

Indent options

Main title merged and

centred over columns

Merge & Center

Fill colour applied to cells

Labels centred and wrapped

Border surrounding a range

Indented cell contents

Email: [Link]@[Link] BY: ODEH VICTOR C.

Facebook: [Link].39@[Link]
Whatsup: 08146998692
Tel: 08146336476, 08146998692, 08124120782, 08078216587, 09080175619
 STEP BY STEP INFORMATICS PRACTICAL 24

Applying Alignment and Font Options

Alignment refers to how data are positioned in cells. Text aligns at the left cell margin, and
dates and values align at the right cell margin. You can change the alignment of cell contents to
improve the appearance of data within the cells. The Alignment group on the Home tab contains
several features to help you align and format data.

Merge and Centre Labels

You may want to place a title at the top of a worksheet and centre it over the columns of data in
the worksheet. You can centre main titles over all columns in the worksheet, and you can centre
category titles over groups of related columns. To create a title, enter the text in the far left cell
of the range. Select the range of cells across which you want to centre the title and click Merge
& Center in the Alignment group on the Home tab. Only data in the far left cell (or top right cell)
are merged. Any other data in the merged cells are deleted. Excel merges the selected cells
together into one cell, and the merged cell address is that of the original cell on the left. The data
are centred between the left and right sides of the merged cell.

If you merge too many cells and want to split the merged cell back into its original multiple cells,
click the merged cell and click Merge & Center. Unmerging places the data in the top-left cell.
For additional options, click the Merge & Center arrow. The Table below lists the four merge
options.

Merge Options
Option Results

Merge & Merges selected cells and centres data into one cell.
Center
Merges the selected cells but keeps text left-aligned or values
Merge Across
right-aligned.

Enables you to merge a range of cells on multiple rows as well as

Merge Cells
in multiple columns.

Unmerge Separates a merged cell into multiple cells again.

Cells
Change Horizontal and Vertical Cell Alignment
Horizontal alignment specifies the position of data between the left and right cell margins, and
vertical alignment specifies the position of data between the top and bottom cell margins. Bottom
Align is the default vertical alignment (as indicated by the light green background), and Align Left is the
default horizontal alignment for text.

If you increase row height, you might need to change the vertical alignment to position data
better in conjunction with data in adjacent cells. To change alignments, click the desired alignment
setting(s) in the Alignment group on the Home tab.

Email: [Link]@[Link] BY: ODEH VICTOR C.

Facebook: [Link].39@[Link]
Whatsup: 08146998692
Tel: 08146336476, 08146998692, 08124120782, 08078216587, 09080175619
 STEP BY STEP INFORMATICS PRACTICAL 25

Wrap Text
Sometimes you have to maintain specific column widths, but the data do not fit entirely. You can
use wrap text to make data appear on multiple lines by adjusting the row height to fit the cell
contents within the column width. When you click Wrap Text in the Alignment group, Excel wraps
the text on two or more lines within the cell. This alignment option is helpful when the column
headings are wider than the values contained in the column.
Increase and Decrease Indent
To offset labels, you can indent text within a cell. Indenting helps others see the hierarchical
structure of data. Accountants often indent the word Totals in financial statements so that it stands
out from a list of items above the total row. To indent the contents of a cell, click Increase Indent in
the Alignment group on the Home tab. The more you click Increase Indent, the more text is indented
in the cell. To decrease the indent, click Decrease Indent in the Alignment group.

Apply Borders and Fill Colour

You can apply a border or fill colour to accentuate data in a worksheet. A border is a line that
surrounds a cell or a range of cells. You can use borders to offset some data from the rest of the
worksheet data. To apply a border, select the cell or range that you want to have a border, click the
Borders arrow in the Font group, and then select the desired border type..
To add some colour to your worksheet to add emphasis to data or headers, you can apply a fill
colour. Fill colour is a background colour that displays behind the data. You should choose a fill
colour that contrasts with the font colour. For example, if the font colour is Black, you might want to
choose Yellow fill colour. If the font colour is White, you might want to apply Blue or Dark Blue fill
colour. To apply a fill colour, select the cell or range that you want to have a fill colour, click the Fill
Color arrow on the Home tab, and then select the colour choice from the Fill Color palette. If you
want to remove a fill colour, select No Fill from the bottom of the palette.
For additional border and fill colour options, click the Dialog Box Launcher in the Font group to
display the Format Cells dialog box. Click the Border tab to select border options, including the
border line style and colour. Click the Fill tab to set the background colour, fill effects, and patterns.

Applying Number Formats

Values have no special formatting when you enter data. You should apply number formats based
on the type of values in a cell, such as applying either the Accounting or Currency number format to
monetary values. Changing the number format changes the way the number displays in a cell, but
the format does not change the number’s value. If, for example, you enter 123.456 into a cell and
format the cell with the Currency number type, the value shows as $123.46 onscreen, but the actual
value 123.456 is used for calculations. When you apply a number format, you can specify the number
of decimal places to display onscreen.

Apply a Number Format

The default number format is General, which displays values as you originally enter them.
General does not align decimal points in a column or include symbols, such as dollar signs, percent
signs, or commas. The Table below lists and describes the primary number formats in Excel.

Email: [Link]@[Link] BY: ODEH VICTOR C.

Facebook: [Link].39@[Link]
Whatsup: 08146998692
Tel: 08146336476, 08146998692, 08124120782, 08078216587, 09080175619
 STEP BY STEP INFORMATICS PRACTICAL 26

Number Format
General A number as it was originally entered. Numbers are shown as integers (e.g.,
12345), decimal fractions (e.g., 1234.5), or in scientific notation (e.g., 1.23E+10) if
the number exceeds 11 digits.
Number A number with or without the 1,000 separator (e.g., a comma) and with any number
of decimal places.
Negative numbers can be displayed with parentheses and/or red.

Currency A number with the 1,000 separator and an optional dollar sign (which is placed
immediately to the left of the number). Negative values are preceded by a minus
sign or are displayed with parentheses or in red. Two decimal places display by
default.

Accounting A number with the 1,000 separator, an optional dollar sign (at the left border of
Number the cell, vertically aligned within a column), negative values in parentheses, and
Format zero values as hyphens. Two decimal places display by default. Changes
alignment slightly within the cell.
Comma A number with the 1,000 separator. Used in conjunction with Accounting Number
Style to align commas and decimal places.
Date The date in different ways, such as Long Date (March 14, 2016) or Short Date
(3/14/16 or 14-Mar-16).

Time The time in different formats, such as 10:50 PM or 22:50.

Percent The value as it would be multiplied by 100 (for display purpose), with the percent
Style sign. The default number of decimal places is zero if you click Percent Style in the
Number group or two decimal places if you use the Format Cells dialog box.
However, you should typically increase the number of decimal points to show
greater accuracy.
Fraction A number as a fraction; use when no exact decimal equivalent exists. A fraction is
entered into a cell as a formula such as =1/3. If the cell is not formatted as a
fraction, the formula results display.
Scientific A number as a decimal fraction followed by a whole number exponent of 10; for
example, the number 12345 would appear as 1.23E+04. The exponent, +04 in the
example, is the number of places the decimal point is moved to the left (or right if
the exponent is negative). Very small numbers have negative exponents.
Text The data left aligned; is useful for numerical values that have leading zeros and
should be treated as text, such as postal codes or phone numbers. Apply Text
format before typing a leading zero so that the zero displays in the cell.
Special A number with editing characters, such as hyphens in a Social Insurance Number.

Custom Predefined customized number formats or special symbols to create your own
customized number format.

Email: [Link]@[Link] BY: ODEH VICTOR C.

Facebook: [Link].39@[Link]
Whatsup: 08146998692
Tel: 08146336476, 08146998692, 08124120782, 08078216587, 09080175619
 STEP BY STEP INFORMATICS PRACTICAL 27

The Number group on the Home tab contains

commands for applying Accounting Number Format,
Percent Style , and Comma Style numbering formats. You
can click the Accounting Number Format arrow and select
other denominations, such as English pounds or euros.
For other number formats, click the Number Format
arrow and select the numbering format you want to use.
For more specific numbering formats than those
provided, select More Number Formats from the Number
Format menu or click the Number Dialog Box Launcher to
open the Format Cells dialog box with the Number tab
options readily available. The box shows different
number formats applied to values.

After applying a number format, you may need to adjust the number of decimal places that display.
For example, if you have an entire column of monetary values formatted in Accounting Number
Format, Excel displays two decimal places by default. If the entire column of values contains whole
dollar values and no cents, displaying .00 down the column looks cluttered. You can decrease the
number of decimal places to show whole numbers only.

Increase and Decrease Decimal Places

To change the number of decimal places displayed, click Increase Decimal in the Number group on
the Home tab to display more decimal places for greater precision or Decrease Decimal to display
fewer or no decimal places.

Challenge!

1. Using the total number of Rows/Columns in a worksheet, Calculate for the Cells
2. Create a Personal and School Time Table
3. Create dBoss Time Table below

Email: [Link]@[Link] BY: ODEH VICTOR C.

Facebook: [Link].39@[Link]
Whatsup: 08146998692
Tel: 08146336476, 08146998692, 08124120782, 08078216587, 09080175619
 STEP BY STEP INFORMATICS PRACTICAL 28

FORMULAS AND FUNCTIONS

Formulas
A formula is an equation that performs calculations, such as addition, subtraction, multiplication,
and division, on values in a worksheet. In Excel, a value can be a number, a cell address, a date, text,
or Boolean data, but is usually a number or cell address in terms of formulas. A formula consists of
two elements: operands and calculation operators. Operands identify the values to be used in the
calculation. An operand can be a constant value, or a variable such as a cell reference, a range of cells,
or another formula. A constant is a number or text value that is entered directly into a formula. A
variable is a symbol or name that represents something else, which can be a cell address, a range of
cells, and so on. Calculation operators specify the calculations to be performed. To allow Excel to
distinguish formulas from data, all formulas begin with an equal sign (=).

Entering and Editing Formulas

Entering a new formula into a worksheet appears to be a straightforward process:
Select the cell in which you want to enter the formula.
Type an equal sign ( = ) to tell Excel that you’re entering a formula.
Type the formula’s operands and operators.
Press Enter to confirm the formula.

However, Excel has three different input modes that determine how it interprets certain keystrokes
and mouse actions:

▪ When you type the equal sign to begin the formula, Excel goes into Enter mode , which is the
mode you use to enter text (such as the formula’s operands and operators).
▪ If you press any keyboard navigation key (such as Page Up, Page Down, or any arrow key),
or if you click any other cell in the worksheet, Excel enters Point mode . This is the mode you
use to select a cell or range as a formula operand. When you’re in Point mode, you can use
any of the standard range-selection techniques.
Note that Excel returns to Enter mode as soon as you type an operator or any character.
If you press F2, Excel enters Edit mode, which is the mode you use to make changes to the
formula. For example, when you’re in Edit mode, you can use the left and right arrow keys to
move the cursor to another part of the formula for deleting or inserting characters. You can
also enter Edit mode by clicking anywhere within the formula. Press F2 to return to Enter
mode.

After you’ve entered a formula, you might need to return to it to make changes. Excel gives you
three ways to enter Edit mode and make changes to a formula in the selected cell:
■ Press F2.
■ Double-click the cell.
■ Use the formula bar to click anywhere inside the formula text.

Email: [Link]@[Link] BY: ODEH VICTOR C.

Facebook: [Link].39@[Link]
Whatsup: 08146998692
Tel: 08146336476, 08146998692, 08124120782, 08078216587, 09080175619
 STEP BY STEP INFORMATICS PRACTICAL 29

Excel divides formulas into four groups: arithmetic, comparison, text, and reference. Each group
has its own set of operators, and you use each group in different ways. In the next few sections, I
will show you how to use each type of formula.
Using Arithmetic Formulas
Arithmetic formulas are by far the most common type of formula. They combine numbers, cell
addresses, and function results with mathematical operators to perform calculations.
Table 1. Summarizes the mathematical operators used in arithmetic formulas.
Table 1. The Arithmetic Operators
Operator Name Example Result
+ Addition =10+5 15
Subtraction =10-5 5
Negation =-10 –10
* Multiplication =10*5 50
/ Division =10/5 2
% Percentage =10% 0.1
^ Exponentiation =10^5 100000
Most of these operators are straightforward, but the exponentiation operator might require
further explanation. The formula =x^y means that the value x is raised to the power y . For
example, the formula =3^2 produces the result 9 (that is, 3*3=9). Similarly, the formula =2 ^ 4
produces 16 (that is, 2*2*2*2=16).

Using Comparison Formulas

A comparison formula is a statement that compares two or more numbers, text strings, cell
contents, or function results. If the statement is true, the result of the formula is given the logical
value TRUE (which is equivalent to any nonzero value). If the statement is false, the formula
returns the logical value FALSE (which is equivalent to zero). T able 2. Summarizes the
operators you can use in comparison formulas.

Table 2. Comparison Formula Operators

Operator Name Example Result
= Equal to =10=5 FALSE
> Greater than =10>5 TRUE
< Less than =10<5 FALSE
>= Greater than or equal ="a">="b" FALSE
to
<= Less than or equal to ="a"<="b" TRUE
<> Not equal to = "a"<>"b" TRUE
Comparison formulas have many uses. For example, you can determine whether to pay a
salesperson a bonus by using a comparison formula to compare actual sales with a
predetermined quota. If the sales are greater than the quota, the rep is awarded the bonus. You
also can monitor credit collection. For example, if the amount a customer owes is more than
150 days past due, you might send the invoice to a collection agency.

Email: [Link]@[Link] BY: ODEH VICTOR C.

Facebook: [Link].39@[Link]
Whatsup: 08146998692
Tel: 08146336476, 08146998692, 08124120782, 08078216587, 09080175619
 STEP BY STEP INFORMATICS PRACTICAL 30

Using Text Formulas

The two types of formulas that I discussed in the previous sections, arithmetic formulas and
comparison formulas, calculate or make comparisons and return values. A text formula , on the
other hand, is a formula that returns text. Text formulas use the ampersand ( & ) operator to
work with text cells, text strings enclosed in quotation marks, and text function results.
One way to use text formulas is to concatenate text strings. For example, if you enter the
formula ="soft"&"ware" into a cell, Excel displays software. Note that the quotation marks and
the ampersand aren’t shown in the result. You also can use & to combine cells that contain
text. For example, if A1 contains the text Ben and A2 contains Jerry , entering the formula
=A1&" and "&A2 returns Ben and Jerry .

Function
A function is a predefined formula that performs calculations using specific values in a particular order.
One of the key benefits of functions is that they can save you time since you do not have to write the
formula yourself. Excel has hundreds of different functions to assist with your calculations. Functions
provide an easy way to perform mathematical work on a range of cells, quickly and conveniently.
This section shows you how to use some of the basic functions in Excel

SEVEN BASIC EXCEL FORMULAS FOR YOUR WORKFLOW

Since you’re now able to insert your preferred formulas and function correctly, let’s check some
fundamental Excel functions to get you started.

1. SUM

The SUM function is the first must-know formula in Excel. It usually aggregates values from a
selection of columns or rows from your selected range.
=SUM(number1, [number2], …)

Example:
=SUM(B2:G2) – A simple selection that sums the values of
a row.
=SUM(A2:A8) – A simple selection that sums the values of
a column.
=SUM(A2:A7, A9, A12:A15) – A sophisticated collection
that sums values from range A2 to A7, skips A8, adds A9,
jumps A10 and A11, then finally adds from A12 to A15.
=SUM(A2:A8)/20 – Shows you can also turn your
function into a formula.
.AVERAGE

The AVERAGE function should remind you of simple averages of data such as the average
number of shareholders in a given shareholding pool.

Email: [Link]@[Link] BY: ODEH VICTOR C.

Facebook: [Link].39@[Link]
Whatsup: 08146998692
Tel: 08146336476, 08146998692, 08124120782, 08078216587, 09080175619
 STEP BY STEP INFORMATICS PRACTICAL 31

=AVERAGE(number1, [number2], …)

Example:
=AVERAGE(A1:A10) – Shows a simple average, also similar
to (SUM(A1: A10)/9)
[Link]

The COUNT function counts all cells in a given range that

contains only numeric values.
=COUNT(value1, [value2], …)

Example:
COUNT(A:A) – Counts all values that are numerical in A column.
However, it doesn’t use the same formula to count rows.
COUNT(A1:C1) – Now it can count rows.
[Link]
Like the COUNT function, COUNTA counts all cells in a given rage.
However, it counts all cells regardless of type. That is, unlike
COUNT that relies on only numerics, it also counts dates, times,
strings, logical values, errors, empty string, or text.
=COUNTA(value1, [value2], …)

Example:
COUNTA(A:A) – Counts all cells in column A regardless of
type. However, like COUNT, you can’t use the same formula
to count rows.
[Link]

The IF function is often used when you want to sort your data
according to a given logic. The best part of the IF formula is
that you can embed formulas and function in it.
=IF(logical_test, [value_if_true], [value_if_false])

Example:
=IF(C2<D3, ‘TRUE,’ ‘FALSE’) – Checks if the value at C3 is less than the value at D3. If the logic is
true, let the cell value be TRUE, else, FALSE

IF(SUM(C1:C10) > SUM(D1:D10), SUM(C1:C10, SUM(D1:D10)) – An example of a complex

IF logic. First, it sums C1 to C10 and D1 to D10, then it compares the sum. If the sum of C1 to
C10 is greater than SUM of D1 to D10, then it makes the value of a cell equal to the sum of C1 to
C10. Otherwise, it makes it the SUM of C1 to C10.

Email: [Link]@[Link] BY: ODEH VICTOR C.

Facebook: [Link].39@[Link]
Whatsup: 08146998692
Tel: 08146336476, 08146998692, 08124120782, 08078216587, 09080175619
 STEP BY STEP INFORMATICS PRACTICAL 32

[Link]

The TRIM function makes sure your functions do not return errors due to unruly spaces. It
ensures that all empty spaces are eliminated. Unlike other functions that can operate on a range
of cells, TRIM only operates on a single cell. Therefore, it comes with the downside of adding
duplicated data in your spreadsheet.
=TRIM(text)

Example:
TRIM(A4) – Removes empty spaces in the value in cell A4.

[Link] & MIN

The MAX and MIN functions help in finding the maximum

number and the minimum number in a pull of values.
=MIN(number1, [number2], …)

Example:
=MIN(B2:C11) – Finds the minimum number between
column B from B2 and column C from C2 to row 11 in both
column B and C.

=MAX(number1, [number2], …)

Example:
=MAX(B2:C11) – Similarly, it finds the maximum number
between column B from B2 and column C from C2 to row 11
in both column B and C.

Understanding Formula Errors

When Excel comes across a formula that it cannot calculate, it displays an error value. Error values occur because
of incorrectly written formulas, referencing cells or data that don’t exist, or breaking the fundamental laws of
mathematics. Excel includes an Error Checking feature to help Error
deal with errors. bChecking
u
1. Click the Formulas tab on the Ribbon and click the Error
Checking button in the Formula Auditing group.
The Error Checking dialog box also has several buttons to help
with errors:
• Help on this error: Displays a Help topic that explains the
type of error being seen.

Email: [Link]@[Link] BY: ODEH VICTOR C.

Facebook: [Link].39@[Link]
Whatsup: 08146998692
Tel: 08146336476, 08146998692, 08124120782, 08078216587, 09080175619
 STEP BY STEP INFORMATICS PRACTICAL 33

• Show Calculation Steps: Displays the Evaluate Formula dialog box, which breaks down the formula
arguments so that the error can be isolated. Click
Evaluate to show the current value of the
underlined argument or click Step In to examine
the source of a particular argument.

• Ignore Error: Allows the current error to be

skipped and move to the next error in the
worksheet.
• Edit in Formula Bar: Places the cursor in the
formula bar, where the formula arguments can be directly edited and the error fixed.
Tip: Click the Previous or Next buttons to move between errors in the worksheet, and click the Options
button to change the error checking rules. Other Ways to display the Evaluate Formula Dialog Box:
Click the Evaluate Formula button in the Formula Auditing group.
2. Click the button to use in the Error Checking dialog box.
Now follow Excel’s advice to fix the error.
Other Ways to Fix an Error:
Select the cell that contains an error and point to the SmartTip icon that appears next to the cell. A tip
appears, telling why this type of error is appearing. Click the list arrow and select an error checking option.

Tips
✓ Another way to analyze errors is by tracing them with arrows. Select a cell with an error, click the Error
Checking list arrow in the Formula Auditing group, and select Trace Error. Arrows appear, pointing out the
cells that are involved in the erroneous formula.

Excel Errors

##### The numeric value is too wide to display within the cell. Resize the column by dragging the boundary
line between the column headings.
#VALUE! A mathematical formula that references a text entry was entered instead of a numerical entry.
#DIV/0! An attempt was made to divide number by zero. This error often occurs when creating a formula that
refers to a blank cell as a divisor.
#NAME? Excel doesn’t recognize text entered in a formula. The name or function may have been misspelled,
or a deleted name typed. Text in a formula without enclosing the text in double quotation marks may
also have been entered.
#N/A This error occurs when a value is not available to a function or a formula. If certain cells on the
worksheet contain data that is not yet available, enter #N/A in those cells. Formulas that refer to
those cells will then return #N/A instead of attempting to calculate a value.
#REF! The #REF! Error value occurs when a cell reference is not valid. A cell range was probably deleted that
is referenced in a formula.
#NUM! The #NUM! Error value occurs when using an invalid argument in a worksheet function.
#NULL! A specified intersection of two ranges in a formula do not intersect.

Email: [Link]@[Link] BY: ODEH VICTOR C.

Facebook: [Link].39@[Link]
Whatsup: 08146998692
Tel: 08146336476, 08146998692, 08124120782, 08078216587, 09080175619
 STEP BY STEP INFORMATICS PRACTICAL 34

formula contains its own cell location as a reference, it results in a circular reference, and the formula
can’t calculate correctly. To locate circular references in the worksheet, click the Error Checking list arrow
in the Formula Auditing group, point to Circular References, and select a cell that contains a circular
reference from the list.

Challenge!

Calculate this Manually and get the answer

Email: [Link]@[Link] BY: ODEH VICTOR C.

Facebook: [Link].39@[Link]
Whatsup: 08146998692
Tel: 08146336476, 08146998692, 08124120782, 08078216587, 09080175619
 STEP BY STEP INFORMATICS PRACTICAL 35

PREPARING OF RECORDS

DEFINITION OF RECORD
. A collection of related data contained in one or more files or a database. For example, a
support company may have a record on you or your computer, this record may contain such
data as information about the computer and its history. Another example is a hospital or clinic
that has a medical record for you or a family member.

IMPORTANCE OF RECORDS

• Monitor the progress of your business

• Prepare your financial statements
• Identify sources of your income
• Keep track of your deductible expenses
• Keep track of your basis in property
• Prepare your tax returns
• Support items reported on your tax returns

TYPES OF RECORDS

1. School Record
2. Sales Record
3. Payroll Record
4. Statistics
5. Budget
School Record
▪ Class record
▪ Student record

Attributes Needed to Create a Class Record

▪ Name of the School
▪ Names of Students
▪ Subjects Offered (Arrange Horizontally)
▪ Total Marks Obtained in all Subjects (Over 1000 or More)
▪ Average gotten by Per Student
▪ Position gotten by Students
▪ Remarks from the Principal/Head Teacher

Email: [Link]@[Link] BY: ODEH VICTOR C.

Facebook: [Link].39@[Link]
Whatsup: 08146998692
Tel: 08146336476, 08146998692, 08124120782, 08078216587, 09080175619
 STEP BY STEP INFORMATICS PRACTICAL 36

Attributes Needed to Create Per Student Record

▪ Name of the Student
▪ Subject Offered (Arrange Vertically)
▪ 1st Test (4 mks)
▪ 2nd Test (4 mks)
▪ Assignment (5 mks)
▪ Debate/Quiz (4 mks)
▪ Project (5 mks)
▪ Note Copying (4 mks)
▪ Attendance/Discipline (4 mks)
▪ Total CA (30 mks)
▪ Exam (70 mks)
▪ Total Scores (Over 100)
▪ Average for Each Subject
▪ Highest in Class
▪ Lowest in Class
▪ Position gotten by Students
▪ Grade
▪ Remarks from the Teachers

Challenge!

Practical question for Class & Student Record

A class of 100 Students took Tests, Exams and other important things needed to be taken
on the following Subjects; Mathematics, English, Civic, Economics, Agric, Biology,
Government., Geography, Physics.
You are required to work out the Class result with their various scores on the above-
mentioned subjects. You are also required to:
▪ Calculate their Totals
▪ Calculate their Average Scores
▪ Calculate their Various Position
▪ Calculate their Remarks

Secondly you are also required to preparing the record for each of the student making use
of the class result.

Email: [Link]@[Link] BY: ODEH VICTOR C.

Facebook: [Link].39@[Link]
Whatsup: 08146998692
Tel: 08146336476, 08146998692, 08124120782, 08078216587, 09080175619
 STEP BY STEP INFORMATICS PRACTICAL 37

Email: [Link]@[Link] BY: ODEH VICTOR C.

Facebook: [Link].39@[Link]
Whatsup: 08146998692
Tel: 08146336476, 08146998692, 08124120782, 08078216587, 09080175619
 STEP BY STEP INFORMATICS PRACTICAL 38

SALES RECORD
Sales record is the information you have on your customers including their contact
information, Name, and Address, how often they purchase goods from you, what they
purchase and how they pay their bills.

TYPES OF SALE RECORDS

1. Company sales record
2. Wholesalers record
3. Retailers sales record

RETAILERS
This are people who buy goods in small quantities from the wholesalers and sell it in bits to
the consumers
A Trader bought 200 Articles with different Prices & Quantities, After the Trader’s analysis,
he decided to sell each item higher than the cost price. He further embarks on some expenses
within the period those items were on sale. The expenses grew up to some certain Amount,
in his wisdom; He decided to remove this amount from his Profit. He budgeted that at the
end of the sales that He is going compare his Final Profit to The Budgeted Amount.

Challenge!
1. Calculate the Trader’s Profit in each of the items
2. What will be the Trader’s Final profit, if the expenses he made is being remove from his
Profit?
3. Calculate the Comment, if the Trader’s Final Profit is above the Budgeted Price, the system
should display “Surplus”, if exactly that Budgeted Amount, the system should display
“Target”, if not up to that Budgeted Amount the system should display “Loss”.

Items require to Create A Retailer’s Sales Record

▪ Items (Description of goods/Articles)
▪ Quantities: Number of Items bought.
▪ Cost Price
▪ Total Cost =Cost Price *Qty
▪ Selling Price =Cost Price + Markup rate
▪ Total Sold = Selling Price *Qty
▪ Per Unit Profit =Total Sold –Total Cost

Email: [Link]@[Link] BY: ODEH VICTOR C.

Facebook: [Link].39@[Link]
Whatsup: 08146998692
Tel: 08146336476, 08146998692, 08124120782, 08078216587, 09080175619
 STEP BY STEP INFORMATICS PRACTICAL 39

▪ Capital
▪ Expenses
▪ Gross Profit = (Sum all “Per Unit Profit”)
▪ Net Profit
= (Gross Profit – SUM ( Capital:Expenses)
▪ Comment =IF(Final Profit >Budgeted
Amount, “Surplus”, IF(Final Profit = Budgeted
Amount, “Target”, IF (Final Profit < Budgeted
Amount “Loss”)))

WHOLESALERS RECORD
Wholesaler are people who buy goods in large quantity from the manufactures or Company
and sells in Small quantities to the Retailers and Consumers
Fundamental Functions Considered before Creating Wholesalers Record
▪ Warehouse (were goods are stored for future purpose)
▪ Discount (Money added to the Goods to Increase/ Decrease the Selling Price)
▪ Capital Money (Money Used in starting up the business)
▪ Date of the Product (Date when the goods were bought and Warranty)

Challenge!
Emeka and Sons Ltd bought goods in large quantities from different companies and sells in
small quantities to the retailers with discount. Assuming He is having a Warehouse stored
with goods, and also assuming He decides to Add & Remove discount in his business.
You are required to Calculate
▪ Calculate the Total Quantities in Warehouse
▪ Calculate the Discounts
▪ Calculate the Total Sold
▪ The Profit for Per Item
▪ The Total Profit for Per Item
▪ The Working Capital

Items require to Create A Wholesalers Record

▪ The Company Name
▪ Product date
▪ Product name
▪ Quantity supply
Email: [Link]@[Link] BY: ODEH VICTOR C.
Facebook: [Link].39@[Link]
Whatsup: 08146998692
Tel: 08146336476, 08146998692, 08124120782, 08078216587, 09080175619
 STEP BY STEP INFORMATICS PRACTICAL 40

▪ Quantity left In Store (Warehouse)

▪ Total quantity In Store (=Qty Supplied +Qty Left in store)
▪ Cost Price /Rate
▪ Mark Up Rate
▪ Selling Price (=Rate*(1+Mark-up Rate))
▪ % OFF
▪ Retail price (=Selling Price – (Selling Price * %OFF)
▪ Quantity Sold
▪ Quantity Remaining in Store (=Total Qty in Store – Qty Sold)
▪ Total sold (=Retail Price * Qty Sold)
▪ Profit Per Unit (=Retail Price – Rate)
▪ Total Profit Per Unit (=Qty Sold * Profit)
▪ Working Capital (=Sum all the Total Sold)
▪ Gross Profit (=Sum Total Profit Per Unit)
▪ Expenses
▪ Net Profit (=Gross Profit – Expenses)

COMPANY SALES RECORD

Company are those people that makes goods available to the wholesalers, Retailer and
Consumers.

Challenge!
A company that sales electronics, sold 40 TV Sets at the rate of N15000 each, 50 Fans at
the rate of N4000 each, 84 Radio Set to at N8000 each, 100 Vide Sets at N4500 each. VCD
at N10000 each; 50 pieces, 30 Antenna at the rate of N4000 each, 10 Microphone at the

Email: [Link]@[Link] BY: ODEH VICTOR C.

Facebook: [Link].39@[Link]
Whatsup: 08146998692
Tel: 08146336476, 08146998692, 08124120782, 08078216587, 09080175619
 STEP BY STEP INFORMATICS PRACTICAL 41

rate of N5000 each, 10 Speakers at N15000 each, 100 Cable wires at the rate of N1000, and
lastly 500 bulbs to Chika and Sons at the rate of N1500 each.
If the Companies allowed the following discount percentages to the customer on the goods
purchased;
▪ 10% to TV.
▪ 5% to Antenna
▪ 8% to Speaker
▪ 6% to VCD
▪ 2% to Wires

Calculate the following;

▪ Amount
▪ Discount Amount
▪ Calculate the balance

Items require to Create A Company’s Sales Record

▪ The Customer Name
▪ Description of Goods
▪ Quantity
▪ Rate
▪ Amount (=Qty * Rate)
▪ Discount Allow
▪ Discount Amount
(=Amount * Discount Allowed)
▪ Total Amount (-Sum Amount)
▪ Total Discount Amount
(=Sum Discount Amt)
▪ Debt/Paid
▪ Balance
(=Amount – SUM (Total
Discount Amount:Debt))

Email: [Link]@[Link] BY: ODEH VICTOR C.

Facebook: [Link].39@[Link]
Whatsup: 08146998692
Tel: 08146336476, 08146998692, 08124120782, 08078216587, 09080175619
 STEP BY STEP INFORMATICS PRACTICAL 42

PAYROLL
Payroll is defined as the total amount of wages paid by a company to its employees and
otherworkers. Your company’s payroll may be your firm’s largest business expenses, and
processing of payroll is complicated. You must collect long contribuction and tax with
holdings from employee pay. Why hiering full time employees can make your business
grow, and you should understand how much each worker will cost beyound their salary, as
well as the work involve in managing payroll.

Components of Payroll
Your firms payroll can be seperated into this four component pare
1. Gross pay: The total amount paid to the worker,before any deductions is being made. The
items listed below are deducted from gross wages to arrival at the workers
2. Net pay: Net pay is the total amount paid to worker after deductions is being made . Gross
wages include commission, bonuces and other payment arrangment.
3. Tax: Little token remove from employees gross wages to pay to the government of the state.
4. Benefit: Your company may provide health insurance,retailment plans and other benefit to
workers while your business pay some of these cost. A possion of the benefits may be
deducted from the employees gross wages in any of pay before taxes are calculated.

Challenge!
A Bank pays the Departments the following Amount: Managers N200,000, Engineers
N150,000, Secretaries N100,000, Marketers N80,000, Seurities N40,000, Cleaners
N30,000. Compute the Salaries.
Perform your record in such a way that Each of the Department will have some certain
number of workers operating under it. E.g. Five (5) Securities
For the allowances,
▪ 2% of their basic Salary is for House allowances
▪ 5% of their basic Salary is for Transport allowances
▪ 6% of their basic Salary is for Feeding allowances
Compute their Gross Pay
Remember that 5% of their basic Salary is for V.A.T, 20% for PAYEE
Compute their Net Pay
Also perform a Calculation that determines if a worker is to be Retained or Retired, using
their Year Hired and Current Year

Items require to Create A Payroll Record.

Email: [Link]@[Link] BY: ODEH VICTOR C.

Facebook: [Link].39@[Link]
Whatsup: 08146998692
Tel: 08146336476, 08146998692, 08124120782, 08078216587, 09080175619
 STEP BY STEP INFORMATICS PRACTICAL 43

▪ Staff Names
▪ Sex
▪ Department
▪ Basic Salary
▪ Allowances (%*Basic Salary)
▪ Gross Pay (Sum Basic Salary to Allowance)
▪ Year Hired
▪ Current Year
▪ Years in Services (Current Year – Year Hired)
▪ VAT (%*Basic Salary)
▪ PAYEE (%*Basic Salary)
▪ Net Pay (Gross Pay – SUM (VAT: PAYEE)
▪ Remark (IF(Years in Service >= Age of Retirement, “Retired”,”Retained”))

Email: [Link]@[Link] BY: ODEH VICTOR C.

Facebook: [Link].39@[Link]
Whatsup: 08146998692
Tel: 08146336476, 08146998692, 08124120782, 08078216587, 09080175619
 STEP BY STEP INFORMATICS PRACTICAL 44

Statistics
Assuming you as businessman/Civil servant keeps buying food stuffs for your nuclear family
in large quantities with different prices. And buys anyone that finishes as soon as possible
for One year.
Questions:
▪ Calculate the Analysis (Goods bought)
▪ Calculate the NO. of times you bought each ite.
▪ Calculate the Total amount Spent on each item
▪ Claculate the Total amount spent in buying all the items

Items require to Create A Payroll Record.

▪ Item
▪ Amount
▪ Analysis
▪ No. of Occurrence (=COUNTIF(Items,
Anaysis))
▪ Amount
(=SUMIF(Item, Analysis, Amount))
▪ Total Amount
(=SUM(Highlight all the Amount from
the Top down to the Bottom))

BUDGET
Budgeting is the process of creating a plan to spend your money. This spending plan is
called a budget. Creating this spending plan allows you to determine in advance whether
you will have enough money to do the things you need to do or would like to do.
Budgeting is simply balancing your expenses with your income. If they don’t balance and
you spend more than you make, you will have a problem. Many people don’t realize that
they spend more than they earn and slowly sinks deeper in to dept every year.
If you don’t have money to do everything you would like to do then you can use this
planning process to periodic your money on the things that are most important to you.

Email: [Link]@[Link] BY: ODEH VICTOR C.

Facebook: [Link].39@[Link]
Whatsup: 08146998692
Tel: 08146336476, 08146998692, 08124120782, 08078216587, 09080175619
 STEP BY STEP INFORMATICS PRACTICAL 45

Challenge!

A Small town of Evercrease, The

Council Managed to Collect Half a
Million Pounds (€500,000) from its
Citizens. Unfortunately, The Council
spent all of this, and another Fifty
eight thousand Six Hundred Pounds
(€58,600) besides, naturally, the
Good People of Evercrease objected
to being asked for another Fifty eight
thousand Six Hundred Pounds
(€58,600). So they got rid of the
Council and appointed a New One.
Have a look at the last year’s budget
and see if you can do better. Here’s
the budget where the previous
Council overspent by Fifty eight
thousand Six Hundred Pounds
(€58,600).
The final budget figures is in Cell B33,
it says (Minus €58,600). Your Job is to
construct the same as above, But
Making Sure that YOU DO NOT HAVE
A MINUS FIGURE in Cell B33.
HINT: You can change the NUMBERS but don’t Change the AMOUNT.

Email: [Link]@[Link] BY: ODEH VICTOR C.

Facebook: [Link].39@[Link]
Whatsup: 08146998692
Tel: 08146336476, 08146998692, 08124120782, 08078216587, 09080175619
 STEP BY STEP INFORMATICS PRACTICAL 46

WORKING WITH CHARTS

CHART
A chart is a tool you can use in Excel to communicate your data graphically charts allow your audience
to see the meaning behind the number, and they make showing comparisons and trends a lot easier.
In this lesson, you will learn how to insert charts and modify them so they communicate information
effectively

Chart

Excel workbooks can contain a lot of data, and that data can often
be difficult to interpret. For example, where are the highest and
lowest values? Are the numbers increasing or decreasing?

The answers to questions like these can become much clearer when
the data is represented as a chart .Excel has many different types of
charts, so you can choose one that most effectively represents the
data.

Types of charts

Click the arrows in the slideshow below to view examples of some of the types of charts available in
Excel.

Excel has a variety of chart types, each with its own advantages.

Column charts use vertical bars to represent data. They can

work with many different types of data, but they're most
frequently used for comparing information.

Email: [Link]@[Link] BY: ODEH VICTOR C.

Facebook: [Link].39@[Link]
Whatsup: 08146998692
Tel: 08146336476, 08146998692, 08124120782, 08078216587, 09080175619
 STEP BY STEP INFORMATICS PRACTICAL 47

Line charts are ideal for showing trends. The data points are
connected with lines, making it easy to see whether values are
increasing or decreasing over time.

Pie charts make it easy to compare

proportions. Each value is shown as a slice of
the pie, so it's easy to see which values make
up the percentage of a whole.

Bar charts work just like Column charts, but they use
horizontal bars instead of vertical bars.

Part of Chart

• Chart area :The entire chart, including all the

labels and extras: everything in the chart frame
• Plot area :The part of the chart that contains
the data
bars/area/pie/points
• Legend :The key that shows what each color
represents
• Wall :The background of the plot area, if any
• Floor :On certain types of 3-D charts, the bottom
of the plot area
• Data series :All the data points in the same data
series (represented by a single color or legend
key item)
• Data point :A single numeric value represented on the chart (for example, a single bar or point)
• Chart title :A text label that describes the entire chart
• Axis :A line on which data is plotted

Email: [Link]@[Link] BY: ODEH VICTOR C.

Facebook: [Link].39@[Link]
Whatsup: 08146998692
Tel: 08146336476, 08146998692, 08124120782, 08078216587, 09080175619
 STEP BY STEP INFORMATICS PRACTICAL 48

To create a chart:

1. Select the cells you want to chart, including the column titles and row labels .These cells will
be the source data for the chart.

2. Click the Insert tab.

3. In the Charts group, select the desired chart
category (Column, for example.)

4. Select the desired chart type from the drop-

down menu (Clustered Column, for example)

5. The chart will appear in the worksheet

Email: [Link]@[Link] BY: ODEH VICTOR C.

Facebook: [Link].39@[Link]
Whatsup: 08146998692
Tel: 08146336476, 08146998692, 08124120782, 08078216587, 09080175619
 STEP BY STEP INFORMATICS PRACTICAL 49

Chart tools .

Once you insert a chart, a set of Chart Tools arranged into three tabs will appear on the Ribbon.
These are only visible when the chart is selected. You can use these three tabs to modify your
chart.

To change the chart type:

1. From the Design tab, click the Change Chart Type command. A dialog box appears

2 . Select the desired chart type , then click OK .

To switch row and column data :

Sometimes when you create a chart, the data may not be grouped the way you want it to be.
In the clustered column chart below, the Book Sales statistics are grouped by Fiction/NonFiction,
with a column for each year. However, you can also switch the row and column data so the chart
will group the statistics by year, with columns for Fiction and Non-Fiction. In both cases, the chart
contains the same data; it’s just organized differently.

Email: [Link]@[Link] BY: ODEH VICTOR C.

Facebook: [Link].39@[Link]
Whatsup: 08146998692
Tel: 08146336476, 08146998692, 08124120782, 08078216587, 09080175619
 STEP BY STEP INFORMATICS PRACTICAL 50

1. Select the chart.

2 . From the Design tab, select the Switch Row/Columncommand .

3 . The chart will then readjust .

To change the chart layout

1. Select the Design tab.

2. Click the More drop-down arrow in the Chart Layouts group to see all of the available layouts

Email: [Link]@[Link] BY: ODEH VICTOR C.

Facebook: [Link].39@[Link]
Whatsup: 08146998692
Tel: 08146336476, 08146998692, 08124120782, 08078216587, 09080175619
 STEP BY STEP INFORMATICS PRACTICAL 51

3. Select eh desired layout

4. The chart will update to reflect the new layout.

Some layouts include chart titles, axes, or legend labels .To change them, just place the
insertion point in the text and begin typing. Change the chart style:

1. Select the Design tab.

2. Click the More drop-down arrow in the Chart Styles group to see all of the available styles

3. Select the desired style.

Email: [Link]@[Link] BY: ODEH VICTOR C.

Facebook: [Link].39@[Link]
Whatsup: 08146998692
Tel: 08146336476, 08146998692, 08124120782, 08078216587, 09080175619
 STEP BY STEP INFORMATICS PRACTICAL 52

4. The chart will update to reflect the new style

To move the chart to a different worksheet

1. Select the Design tab.
2. Click the Move Chart command. A dialog box appears. The current location of the chart is
selected.

3. Select the desired location for the chart (i.e., choose an existing worksheet, or select New
Sheet and name it .)

4. Click OK .The chart will appear in the new location.

Email: [Link]@[Link] BY: ODEH VICTOR C.

Facebook: [Link].39@[Link]
Whatsup: 08146998692
Tel: 08146336476, 08146998692, 08124120782, 08078216587, 09080175619
 STEP BY STEP INFORMATICS PRACTICAL 53

PAGE SETUP AND PRINTING

Although you might distribute workbooks electronically as e-mail attachments or you might upload
workbooks to a corporate server, you should prepare the worksheets in the workbook for printing. You
should prepare worksheets in case you need to print them or in case others who receive an electronic copy
of your workbook need to print the worksheets. The Page Layout tab provides options for controlling the
printed worksheet.

Page Layout Tab

In this section, you will select options on the Page Layout tab. specifically, you will use the Page Setup,
Scale to Fit, and Sheet Options groups. After selecting page setup options, you are ready to print your
worksheet.

Selecting Page Setup Options

The Page Setup group on the Page Layout tab contains options to set the margins, select orientation, specify
page size, select the print area, and apply other options. The Scale to Fit group contains options for adjusting
the scaling of the spreadsheet on the printed page. When possible, use the commands in these groups to apply
page settings. The Table below lists and describes the commands in the Page Setup group.

Page Setup Commands

Command Description

Margins Displays a menu to select predefined margin settings. The default margins are 1.9 cm (0.75") top and
bottom and 1.78 cm (0.7") left and right. You will often change these margin settings to balance the
worksheet data better on the printed page. If you need different margins, select Custom Margins.
Orientation Displays orientation options. The default page orientation is portrait, which is appropriate for worksheets
that contain more rows than columns. Select landscape orientation when worksheets contain more
columns than can fit in portrait orientation. For example, the OKOS worksheet might appear better
balanced in landscape orientation because it has eight columns.
Size Displays a list of standard paper sizes. The default size is 21.59 cm x 27.94 cm (8.5” x 11”). If you have a
different paper size, such as legal paper, select it from the list.
Print Area Displays a list to set or clear the print area. When you have very large worksheets, you might want to print
only a portion of that worksheet. To do so, select the range you want to print, click Print Area in the Page
Setup group, and then select Set Print Area. When you use the Print commands, only the range you specified
will be printed. To clear the print area, click Print Area and select Clear Print Area.
Breaks Displays a list to insert or remove page breaks.
Background Enables you to select an image to appear as the background behind the worksheet data when viewed
onscreen (backgrounds do not appear when the worksheet is printed).
Print Titles Eables you to select column headings and row labels to repeat on multiple-page printouts.

Specify Page Options

To apply several page setup options at once or to access options not found on the Ribbon, click the Page Setup
Dialog Box Launcher. The Page Setup dialog box organizes options into four tabs: Page, Margins,
Header/Footer, and Sheet. All tabs contain Print and Print Preview buttons.

Email: [Link]@[Link] BY: ODEH VICTOR C.

Facebook: [Link].39@[Link]
Whatsup: 08146998692
Tel: 08146336476, 08146998692, 08124120782, 08078216587, 09080175619
 STEP BY STEP INFORMATICS PRACTICAL 54

Select Portrait for worksheets

that have more rows than
columns

Select Landscape for

worksheets that have
more columns than rows

Click to see a preview of

how the worksheet will
print with the current settings

Page Setup Dialog Box –Page Tab

The Page tab contains options to select the orientation and paper size. In addition, it contains scaling
options that are similar to the options in the Scale to Fit group on the Page Layout tab. You use scaling
options to increase or decrease the size of characters on a printed page, similar to using a zoom setting
on a photocopy machine. You can also use the Fit to option to force the data to print on a specified number
of pages.

Set Margins Options

The Margins contains options for setting the specific margins. In addition, it contains options to centre
the worksheet data horizontally or vertically on the page. To balance worksheet data equally between the
left and right margins, Excel users often centre the page horizontally.

Select option(s) to centre

worksheet data between
the margins

Email: [Link]@[Link] BY: ODEH VICTOR C.

Facebook: [Link].39@[Link]
Whatsup: 08146998692
Tel: 08146336476, 08146998692, 08124120782, 08078216587, 09080175619
 STEP BY STEP INFORMATICS PRACTICAL 55

Create Headers and Footers

The Header/Footer tab lets you create a header and/or footer that appears at the top and/or bottom of every
printed page. Click the arrows to choose from several preformatted entries, or, alternatively, you can click
Custom Header or Custom Footer, insert text and other objects, and then click the appropriate formatting
button to customize your headers and footers. You can use headers and footers to provide additional
information about the worksheet. You can include your name, the date the worksheet was prepared, and page
numbers.

You can create different headers or footers on different pages, such as one header with the file name on
odd-numbered pages and a header containing the date on even-numbered pages. Click the Different odd and
even pages check box in the Page Setup dialog box
You might want the first page to have a different header or footer from the rest of the printed pages, or
you might not want a header or footer to show up on the first page but want the header or footer to display
on the remaining pages. Click the Different first page check box in the Page Setup dialog box to specify a
different first page header or footer

Click to see list of

preformatted headers

Specify if you want a

different header/footer on
odd and even pages

Specify if you want the first

page to have a different
header/footer from the
rest of the pages

Box—Header/Footer Tab
Page Setup Dialog

Instead of creating headers and footers using the Page Setup dialog box, you can click the Insert tab and click
Header & Footer in the Text group. Excel displays the worksheet in Page Layout view with the insertion
point in the centre area of the header. You can click inside the left, centre, or right section of a header or
footer. When you do, Excel displays the Header & Footer Tools Design contextual [Link] can enter text or
insert data from the Header & Footer Elements group on the tab. Table 8 lists and describes the options in
the Header & Footer Elements group. To get back to Normal view, click any cell in the worksheet and click
Normal in the Workbook Views group on the View tab.

Email: [Link]@[Link] BY: ODEH VICTOR C.

Facebook: [Link].39@[Link]
Whatsup: 08146998692
Tel: 08146336476, 08146998692, 08124120782, 08078216587, 09080175619
 STEP BY STEP INFORMATICS PRACTICAL 56

Design tab options

Header & Footer Tools

Design contextual tab

Click here to display

contextual tab

Header & Footer

Tools Design Contextual Tab
Header & Footer Elements Options
Option Name Result
Page Number Inserts the code &[Page] to display the current page number.

Number of Pages Inserts the code &[Pages] to display the total number of pages that will print.

Current Date Inserts the code &[Date] to display the current date, such as 5/19/2016. The date updates to the current
date when you open or print the worksheet.

Current Time Inserts the code &[Time] to display the current time, such as 5:15 PM. The time updates to the current time
when you open or print the worksheet.
File Path Inserts the code &[Path]&[File] to display the path and file name, such as C:\Documents\e01h4Markup.
This information changes if you save the workbook with a different name or in a different location.

File Name Inserts the code &[File] to display the file name, such as e01h4Markup. This information changes if you save
the workbook with a different name.
Sheet Name Inserts the code &[Tab] to display the worksheet name, such as September. This information changes if you
rename the worksheet.

Picture Inserts the code &[Picture] to display and print an image as a background behind the data, not just the
worksheet.

Format Picture Enables you to adjust the brightness, contrast, and size of an image after you use the Picture option.

Select Sheet Options

The Sheet tab contains options for setting the print area, print titles, print options, and page order. Some
of these options are also located in the Sheet Options group on the Page Layout tab on the Ribbon. By
default, Excel displays gridlines onscreen to show you each cell’s margins, but the gridlines do not print
unless you specifically select the Gridlines check box in the Page Setup dialog box or the Print Gridlines
check box in the Sheet Options group on the Page Layout tab. In addition, Excel displays row (1, 2, 3, etc.)
and column (A, B, C, etc.) headings onscreen. However, these headings do not print unless you click the
Row and column headings check box in the Page Setup dialog box or click the Print Headings check box in
the Sheet Options group on the Page Layout tab.

Email: [Link]@[Link] BY: ODEH VICTOR C.

Facebook: [Link].39@[Link]
Whatsup: 08146998692
Tel: 08146336476, 08146998692, 08124120782, 08078216587, 09080175619
 STEP BY STEP INFORMATICS PRACTICAL 57

Click to print gridlines

Click to print row numbers and column letters

Previewing and Printing a Worksheet

Before printing a worksheet, you should click the File tab and select Print. The Microsoft Office Backstage
view displays print options and displays the worksheet in print preview mode. This mode helps you see in
advance if the data are balanced on the page or if data will print on multiple pages.

You can specify the number of copies to print and which printer to use to print the worksheet. The first
option in the Settings area enables you to specify what to print. The default option is Print Active Sheets. You
can choose other options, such as Print Entire Workbook or Print Selection. You can also specify which pages
to print. If you are connected to a printer capable of duplex printing, you can print on only one side or print
on both sides. You can also collate, change the orientation, specify the paper size, adjust the margins, and
adjust the scaling.

The bottom of the Print window indicates how many pages will print. If you do not like how the worksheet
will print, click the Page Layout tab so that you can adjust margins, scaling, column widths, and so on until
the worksheet data appear the way you want them to p

Email: [Link]@[Link] BY: ODEH VICTOR C.

Facebook: [Link].39@[Link]
Whatsup: 08146998692
Tel: 08146336476, 08146998692, 08124120782, 08078216587, 09080175619
Email: [Link]@[Link] BY: ODEH VICTOR C.
Facebook: [Link].39@[Link]
Whatsup: 08146998692
Tel: 08146336476, 08146998692, 08124120782, 08078216587, 09080175619