INST 3 – DATA SCIENCE ANALYTICS CHAPTER III

INSTRUCTOR: ENGR. JONATHAN REY S. DE JESUS

Republic of the Philippines

Isabela State University
Echague, Isabela

College of Computing Studies, Information and Communication Technology

CHAPTER III:
Excel as Data analy cs tool

Microso Excel is a spreadsheet editor developed by Microso for Windows, macOS, Android,
iOS and iPadOS. Excel is a naturally powerful tool for data analysis that enables users to manipulate,
analyze, and visualize large amounts of data quickly and easily. With built-in excel formulas for data
analysis such as pivot tables, data tables, and various sta s cal func ons, Excel is widely used in many
industries, from finance and accoun ng to marke ng and sales.

Basic parts of Microso Excel Windows

1. Quick Access Toolbar

This toolbar is located in the upper le corner of the screen. Its objec ve is to show the most
frequently used Excel commands. We can customize this toolbar based on our preferred commands.

2. File Tab

Excel 2007's Office bu on has been replaced by the File tab. We can click it to check the Backstage
view, where we can open or save files, create new sheets, print sheets, and perform other file-related
opera ons.
INST 3 – DATA SCIENCE ANALYTICS CHAPTER III
INSTRUCTOR: ENGR. JONATHAN REY S. DE JESUS

3. Title Bar

The tle bar of the spreadsheet is at the top of the window. It displays the ac ve document's
name.

4. Control Bu ons

Control bu ons are the symbols that are present in the upper-right side of the window, enabling
us to change the labels, minimize, maximize, share, and close the sheet.

5. Menu Bar

Under the diske e or save icon or the excel icon (this will depend on the version of the program),
labels or bars which enable changing the sheet which is shown. These are the menu bar and contain a File,
Insert, Page Layout, Formulas, Data, Review, View, Help, and a Search Bar with a light bulb icon. These
menus are divided into subcategories which simplify the distribu on of informa on and analysis of
calcula ons.

6. Ribbon/Toolbar

Each menu bar contains several different elements. On the selec on of the menu, a sequence of
command op ons/icons will show on a ribbon. For example, if we select the "Home" tab, we will see cut,
copy, paste, bold, italic, underline, and more commands. In the same way; we can click on
the "Insert" tab, we will see tables, illustra ons, addi onal, recommended graphics, graphics
maps, among others. On the other hand, if we select the "Formulas" op on. Insert func ons, auto sum
recently used, finances, logic, text, me, date, etc.

Ribbon/Toolbar is a set of commands organized into three sec ons.

o Tabs
They are the Ribbon's top part, and they include groups of related commands. Ribbon tabs
include Home, Insert, Page Layout, Formula, Data.

o Groups
They organize related commands; the name of each group is displayed below the Ribbon.
For example, a set of commands related to fonts or a group of commands related to
alignment, etc.

o Commands
They appear within each group, as previously stated.

7. Dialog Box Launcher

Dialog box launcher is a very li le down arrow that is present in the lower-right corner of a
command group on the Ribbon. By clicking on this arrow, we can explore more op ons related to the
concerned group.

8. Name box

Show the loca on of the ac ve cell, row, or column. We have the op on of selec ng mul ple op ons.
INST 3 – DATA SCIENCE ANALYTICS CHAPTER III
INSTRUCTOR: ENGR. JONATHAN REY S. DE JESUS

9. Formula Bar

Formula bar permits us to observe, insert or edit the informa on/formula entered in the ac ve cell.

10. Scrollbars

Scrollbars are the tools that enable us to move the document's ver cal and horizontal views. We
can ac vate this by clicking on the pla orm's internal bar or the arrows we have on the sides. Addi onally,
we can use the mouse wheel in order to automa cally scroll up or down: or use the direc onal keys.

11. Spreadsheet Area

It is the place where we enter our data. It includes all the rows, cells, columns, and built-in data in
the spreadsheet. We can use shortcuts to perform toolbar ac vi es or formulas of arithme c opera ons
(add, subtract, mul ply, etc.). The inser on point is the blinking ver cal bar known as the "cursor." It
specifies the inser on loca on of the typing.

12. Leaf Bar

Leaf bar is present at the bo om of the spreadsheet, which says sheet1 is shown. This sheet bar
describes the spreadsheet which is currently being worked on. Using this, we can alternate a number of
sheets or add a new one as per our convenience.

13. Columns Bar

Columns are a ver cally ordered series of boxes across the full sheet. This column bar is located
below the formula bar. The le ers of the alphabet are used to label the columns. Begin with the
le er A to Z, and then a er Z, it will con nue as AA, AB, and so on. The number of columns that can be
used is limited to 16,384.

14. Rows Bar

The row bar is the le part of the sheet where a sequence of numbers is expressed. Begin with
number one (1), and further rows will be added as we move the pointer down. There are a total
of 1,048,576 rows available.

15. Cells

Cells are those parallelepipeds that divide the spreadsheet into many pieces, separa ng rows and
columns. A spreadsheet's first cell is represented by the first le er of the alphabet and the number
one (A1).

16. Status Bar

The status bar is present at the bo om of the window that displays cri cal informa on. It also indicates
whether something is incorrect or whether the document is ready to be printed or delivered.

Status Bar Modes

 Ready mode. This means nothing is being entered or edited on the spreadsheet.
 Enter mode. This mode is when you are doing data entry, just typing in the contents.
INST 3 – DATA SCIENCE ANALYTICS CHAPTER III
INSTRUCTOR: ENGR. JONATHAN REY S. DE JESUS

 Edit mode. Edit the contents of the current cell. Double-click on a cell with data in it, or
click inside the formula bar for this mode.
17. View Bu ons

View bu ons are a set of three bu ons arranged at the le of the Zoom control, close the screen's
right-bo om corner. We can see three different kinds of sheet views in Excel using this method.

o Normal View: - Normal view displays the Excel page in normal view.

o Page Layout View: - The Page Layout view shows the precise layout of an Excel page it will be
printed.

o Page Break View: - This displays page break preview before prin ng.

18. Zoom Control

The zoom control is present at the lower-right side of the window. It enables us to ZOOM-
IN or ZOOM-OUT a specific area of the spreadsheet. It is represented by magnifying icons with the
symbols of maximizing (+) or minimizing (-).

Naviga ng the Excel Environment

THE BASIC FILE OF EXCEL

In Microso Excel the data you enter, whether it consists of numbers, text, or formulas, is stored
in a file known as a workbook. Workbooks are just like huge electronic books with pages (or sheets) that
have been ruled into columns and rows.

File Formats in Excel

Format Extension Description

Excel Workbook .xlsx The default XML-based file format for Excel 2010 and Excel 2007.
Cannot store Microsoft Visual Basic for Applications (VBA) macro
code or Microsoft Office Excel 4.0 macro sheets (.xlm).
Excel Macro- .xlsm The XML-based and macro-enabled file format for Excel 2016, Excel
Enabled 2013, Excel 2010, and Excel 2007. Stores VBA macro code or Excel 4.0
Workbook (code) macro sheets (.xlm).
Excel Binary .xlsb The binary file format (BIFF12) for Excel 2010 and Excel 2007.
Workbook
Excel 97- Excel .xls The Excel 97 - Excel 2003 Binary file format (BIFF8).
2003 Workbook
CSV (comma .csv Saves a workbook as a comma-delimited text file for use on another
delimited) Windows operating system, and ensures that tab characters, line
breaks, and other characters are interpreted correctly. Saves only the
active sheet.
INST 3 – DATA SCIENCE ANALYTICS CHAPTER III
INSTRUCTOR: ENGR. JONATHAN REY S. DE JESUS

TYPING TEXT OR NUMBERS INTO A WORKSHEET

Generally, when you start a new spreadsheet project, the first task is to enter some headings into
rows and columns. To type anything into a worksheet you need to make the cell into which you wish to
enter the data ac ve. This can be done in several ways but the most common is to click in it first before
typing.

MANIPULATING WORKSHEETS

INSERTING AND DELETING

COPYING OR MOVING
INST 3 – DATA SCIENCE ANALYTICS CHAPTER III
INSTRUCTOR: ENGR. JONATHAN REY S. DE JESUS

SELECTING RANGES

A con guous range is any group of selected cells that form either a square or a rectangle. A single
cell that is selected is also considered to be a range. Ranges can be selected using the mouse, the keyboard
or a combina on of the two. Once selected, you can use the range for input, or apply forma ng, or copy
the cells as required.

SELECTING ROWS & COLUMNS

If you want to make changes to an en re row, such as bolding all of the headings in a row or
changing the font of all the cell entries, you must first select the row. This is done by clicking on the row
header to the le of the row. Remember that any changes you make will apply to every cell in the row all
the way across to column XFD, so be careful!

If you want to make changes to an en re column, such as bolding all of the headings in a column
or changing the font of all the cell entries, you must first select the column. This is done by clicking on the
column header directly above the column. Remember that any changes you make will apply to every cell
in the column all the way down to row 1,048,576!
INST 3 – DATA SCIENCE ANALYTICS CHAPTER III
INSTRUCTOR: ENGR. JONATHAN REY S. DE JESUS

FORMATTING CELLS

The most forma ng op ons are found on the Home Tab. All the op ons can be found in the
Format Cells window. This contains several tabs to help us format the contents of our spreadsheet. This
window can be opened by using the More Op ons bu on at the end of the Format, Alignment and
Number groups. You can also use the Keyboard Shortcut – Ctrl-1 or choose Format Cells… from the right-
click shortcut menu.

1. FONT
1. Font – Sets the font of the selected cell(s). Fonts are
different ways to show the same le ers.
2. Font Size – Sets the size of the le ers (the font). Larger
numbers give larger fonts.
3. Increase Font – Increases the font size
4. Decrease Font – Decreases the font size
5. Bold – Makes the selected cell(s) Bold
6. Italic – Makes the selected cell(s) Italicized
7. Underline – Makes the selected cell(s) Underlined. The drop down has a double underline.
8. Borders – Adds and removes borders for the selected cell(s). The drop down has More Borders…
9. Fill Color – Changes the background color of the selected cell(s).
10. Font Color – Changes the color of the font of the selected cell(s).
11. More Op ons – This bu on will open the Format Cells dialog window.

2. ALIGNMENT
1. Top Align – Ver cally aligns to the top of the cell.
2. Middle Align – Ver cally aligns to middle of the cell.
3. Bo om Align – Ver cally aligns to the bo om of the cell.
4. Orienta on – Rotates the contents of the cell to the
currently displayed op on.
5. Wrap Text – Displays contents on mul ple lines within
the cell's column width.
6. Align Text Le – Horizontally aligns the contents to the
le side of the column.
7. Center – Horizontally aligns the contents to the center of the cell.
8. Align Text Right – Horizontally aligns the contents to the right side of the cell.
9. Decrease Indent – Decreases the space between the text and the cell border
10. Increase Indent – Increases the space between the text and the cell border
11. Merge and Center – Joins selected (adjacent) cells into one cell and centers the result.
If there is data in more than one cell, Excel will only keep the informa on from the upper le cell.
12. More Op ons – This bu on will open the Format Cells dialog window to the Alignment Tab.
INST 3 – DATA SCIENCE ANALYTICS CHAPTER III
INSTRUCTOR: ENGR. JONATHAN REY S. DE JESUS

3. NUMBER
1. Number Format – Allows you to change the way
numeric values are displayed on the spreadsheet. The
drop down arrow gives you a list of the most common
formats, including a More Number Formats op on.
2. Currency Style – Sets the selected cell(s) to the
Currency Style, this style keeps the dollar signs on the
le side of the cell, and the number on the right side.
The drop down arrow gives you a list of other currency
formats, such as the Euro (€).
[Link] Style – Sets the selected cell(s) to the Percent
Style, this style has zero decimal places. Keyboard
shortcut - Ctrl-Shi -%. This bu on can be reset through
Cell Styles on the Home Tab.
[Link] Style – Sets the selected cell(s) to the Comma Style, this style has a comma for every
thousand and two decimal places. This bu on can be reset through
5. Increase Decimal – Increases the number of decimal places showing to the right of the decimal.
6. Decrease Decimal – Decreases the number of decimal places showing to the right of the
decimal.
7. More Op ons – This bu on will open the Format Cells dialog window to the Number Tab

4. CELL STRUCTURE
There are a set number of cells within a Microso Excel worksheet. In the Ribbon versions
(2007 and later) there are 16,384 columns and 1,048,576 rows. As you insert and delete
structures, you are not reducing the number of cells, merely shi ing where your data lies on the
defined worksheet.
4.1 Inser ng
We use Insert to make new cells, columns, and rows. Excel determines
what you are trying to insert based on your selec on. If a full column is selected,
Excel will assume you mean a full column and it will skip the Insert window.

***The size and format of the new space is determined by the previous row or column. This will
push the exis ng cells, columns, or rows to the right or down to make room for the new cells.

4.2 Dele ng

We use Delete to remove cells, columns, and rows. Excel

determines what you are trying to delete based on your selec on.
**To delete mul ple at once, select the number of cells/rows/columns you would like to delete and follow the
steps above. This will completely remove the structure, forma ng and all, and the rows/columns/cells will
shi into this place.
INST 3 – DATA SCIENCE ANALYTICS CHAPTER III
INSTRUCTOR: ENGR. JONATHAN REY S. DE JESUS

4.3 Cell Size (Row Height/Column Width)

You cannot resize one cell; the structure is dependent on the en re row and
column where it resides. The Row Height and Column Width se ngs can be found under
the Format menu in the Cells group of the Home tab.

Adjus ng with the Mouse: When we resize we are growing away from the le .
To resize the column, place your mouse cursor between the lines of the column headings.
The current column heading is in a box; all you need to do is resize the box to make it
wider. Put your mouse along the right side of the heading box un l you see the resizing
arrow poin ng in two direc ons. Click and drag away from the column le er. When you
let go of the mouse, the column will resize.

To resize the row, place your mouse cursor between the lines of the row headings.
The current row heading is in a box; all you need to do is resize the box to make it wider.
Put your mouse along the bo om side of the heading box un l you see the resizing arrow
poin ng in two direc ons. Click and drag away from the row number. When you let go of
the mouse, the row will resize

Auto-fi ng

You can use the op on found on the Format menu, or place your mouse cursor between the
headings, with the two-way arrow to help resize, and double-click. The row or column should AutoFit
to the largest data length within its structure.

5. FILL HANDLE

The Fill Handle is in the bo om right corner of the selected cell. When you place your mouse over this
handle, it changes from a thick white cross, to a thin black cross. Once you see the thin cross (no arrows)
you can click and drag the cell to fill its contents in a single direc on (up, down, le or right). If you want
to go in two direc ons, you must first complete one way, let go of the mouse and then drag the handle in
the second direc on.

1. When you use the Fill Handle to pull down a single number or plain text, it will copy the data.
When you use the Fill Handle to pull down a text with numbers, a date, a month or a weekday
it will fill in a series.
INST 3 – DATA SCIENCE ANALYTICS CHAPTER III
INSTRUCTOR: ENGR. JONATHAN REY S. DE JESUS

2. When you select two or more numbers (including dates) and then use the Fill Handle, Excel
will fill in the series, following the original pa ern of the selected cells. It can only follow simple
addi on and subtrac on pa erns.

Formulas and Func ons

In Microso Excel, you can enter numbers and mathema cal formulas into cells. Whether you
enter a number or a formula, you can reference the cell when you perform mathema cal calcula ons
such as addi on, subtrac on, mul plica on, or division. When entering a mathema cal formula,
precede the formula with an equal (=) sign.

Examples:

= 1 + 19 (Addi on)
= A3 – 2 (Subtrac on)
= 12 * D17 (Mul plica on)
= J16 / 6 (Division)
= A1^2 (Exponen a on)

By using func ons, you can quickly and easily make many useful calcula ons, such as finding an
average, the highest number, the lowest number, and a count of the number of items in a list. Microso
Excel has many func ons that you can use.

Func ons are prewri en formulas. Func ons differ from regular formulas in that you supply the
values(also called arguments) but not the operators, such as +, -, *, or /. Example of these func ons are
SUM, COUNT, AVERAGE, LOWER, MID, and many other that will be discussed in future Modules