INST 3 – DATA SCIENCE ANALYTICS CHAPTER III
INSTRUCTOR: BRYAN CARL A. CAJURAO,
LPT,MIT
Republic of the Philippines
Isabela State University
Echague, Isabela
College of Computing Studies, Information and Communication Technology
CHAPTER III:
Excel as Data analytics tool
Microsoft Excel is a spreadsheet editor developed by Microsoft 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 statistical functions, Excel is widely used in many
industries, from finance and accounting to marketing and sales.
Basic parts of Microsoft Excel Windows
1. Quick Access Toolbar
This toolbar is located in the upper left corner of the screen. Its objective 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 button 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 operations.
INST 3 – DATA SCIENCE ANALYTICS CHAPTER III
INSTRUCTOR: BRYAN CARL A. CAJURAO,
LPT,MIT
3. Title Bar
The title bar of the spreadsheet is at the top of the window. It displays the active document's
name.
4. Control Buttons
Control buttons 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 diskette 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 distribution of information and
analysis of calculations.
6. Ribbon/Toolbar
Each menu bar contains several different elements. On the selection of the menu, a sequence of
command options/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, illustrations, additional, recommended graphics, graphics
maps, among others. On the other hand, if we select the "Formulas" option. Insert functions, auto sum
recently used, finances, logic, text, time, date, etc.
Ribbon/Toolbar is a set of commands organized into three sections.
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 little 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 options related to the
concerned group.
8. Name box
Show the location of the active cell, row, or column. We have the option of selecting multiple options.
INST 3 – DATA SCIENCE ANALYTICS CHAPTER III
INSTRUCTOR: BRYAN CARL A. CAJURAO,
LPT,MIT
9. Formula Bar
Formula bar permits us to observe, insert or edit the information/formula entered in the active cell.
10. Scrollbars
Scrollbars are the tools that enable us to move the document's vertical and horizontal views.
We can activate this by clicking on the platform's internal bar or the arrows we have on the sides.
Additionally, we can use the mouse wheel in order to automatically scroll up or down: or use the
directional 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 activities or formulas of arithmetic
operations (add, subtract, multiply, etc.). The insertion point is the blinking vertical bar known as the
"cursor." It specifies the insertion location of the typing.
12. Leaf Bar
Leaf bar is present at the bottom 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 vertically ordered series of boxes across the full sheet. This column bar is located
below the formula bar. The letters of the alphabet are used to label the columns. Begin with the
letter A to Z, and then after Z, it will continue 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 left 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, separating rows
and columns. A spreadsheet's first cell is represented by the first letter of the alphabet and the
number one (A1).
16. Status Bar
The status bar is present at the bottom of the window that displays critical information. 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: BRYAN CARL A. CAJURAO,
LPT,MIT
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 Buttons
View buttons are a set of three buttons arranged at the left of the Zoom control, close the screen's
right-bottom 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 printing.
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 (-).
Navigating the Excel Environment
THE BASIC FILE OF EXCEL
In Microsoft 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 [Link] 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,
Enabled Workbook Excel
(code) 2013, Excel 2010, and Excel 2007. Stores VBA macro code or
Excel 4.0 macro sheets (.xlm).
Excel Binary .xlsb The binary file format (BIFF12) for Excel 2010 and Excel 2007.
Workbook
Excel 97- [Link] 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
delimited) another 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: BRYAN CARL A. CAJURAO,
LPT,MIT
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 active. 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: BRYAN CARL A. CAJURAO,
LPT,MIT
SELECTING RANGES
A contiguous 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 combination of the two. Once selected, you can use the range for input, or apply
formatting, or copy the cells as required.
SELECTING ROWS & COLUMNS
If you want to make changes to an entire 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 left 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 entire 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: BRYAN CARL A. CAJURAO,
LPT,MIT
FORMATTING CELLS
The most formatting options are found on the Home Tab. All the options 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 Options button 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 letters.
2. Font Size – Sets the size of the letters (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 Options – This button will open the Format Cells dialog window.
2. ALIGNMENT
1. Top Align – Vertically aligns to the top of the cell.
2. Middle Align – Vertically aligns to middle of the cell.
3. Bottom Align – Vertically aligns to the bottom of the cell.
4. Orientation – Rotates the contents of the cell to the
currently displayed option.
5. Wrap Text – Displays contents on multiple lines within
the cell's column width.
6. Align Text Left – Horizontally aligns the contents to the
left 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 information from the upper left cell.
12. More Options – This button will open the Format Cells dialog window to the Alignment Tab.
INST 3 – DATA SCIENCE ANALYTICS CHAPTER III
INSTRUCTOR: BRYAN CARL A. CAJURAO,
LPT,MIT
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 option.
2. Currency Style – Sets the selected cell(s) to the
Currency Style, this style keeps the dollar signs on the
left 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 (€).
3. Percent Style – Sets the selected cell(s) to the
Percent Style, this style has zero decimal places.
Keyboard shortcut - Ctrl-Shift-%. This button can be
reset through Cell Styles on the Home Tab.
4. Comma Style – Sets the selected cell(s) to the Comma Style, this style has a comma for every
thousand and two decimal places. This button 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 Options – This button will open the Format Cells dialog window to the Number Tab
4. CELL STRUCTURE
There are a set number of cells within a Microsoft 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 shifting where your data lies on the
defined worksheet.
4.1 Inserting
We use Insert to make new cells, columns, and rows. Excel determines
what you are trying to insert based on your selection. 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 existing cells, columns, or rows to the right or down to make room for the new cells.
4.2 Deleting
We use Delete to remove cells, columns, and rows. Excel
determines what you are trying to delete based on your selection.
**To delete multiple 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, formatting and all, and the rows/columns/cells will
shift into this place.
INST 3 – DATA SCIENCE ANALYTICS CHAPTER III
INSTRUCTOR: BRYAN CARL A. CAJURAO,
LPT,MIT
4.3 Cell Size (Row Height/Column Width)
You cannot resize one cell; the structure is dependent on the entire row and
column where it resides. The Row Height and Column Width settings can be found
under the Format menu in the Cells group of the Home tab.
Adjusting with the Mouse: When we resize we are growing away from the left.
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 until you see the
resizing arrow pointing in two directions. Click and drag away from the column letter.
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 bottom side of the heading box until you see
the resizing arrow pointing in two directions. 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 option 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 bottom 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 direction (up, down, left or right). If
you want to go in two directions, you must first complete one way, let go of the mouse and then drag
the handle in the second direction.
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: BRYAN CARL A. CAJURAO,
LPT,MIT
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 pattern of the selected cells. It can only follow
simple addition and subtraction patterns.
Formulas and Functions
In Microsoft Excel, you can enter numbers and mathematical formulas into cells. Whether you
enter a number or a formula, you can reference the cell when you perform mathematical calculations
such as addition, subtraction, multiplication, or division. When entering a mathematical formula,
precede the formula with an equal (=) sign.
Examples:
= 1 + 19 (Addition)
= A3 – 2 (Subtraction)
= 12 * D17 (Multiplication)
= J16 / 6 (Division)
= A1^2 (Exponentiation)
By using functions, you can quickly and easily make many useful calculations, such as finding
an average, the highest number, the lowest number, and a count of the number of items in a list.
Microsoft Excel has many functions that you can use.
Functions are prewritten formulas. Functions differ from regular formulas in that you supply
the values(also called arguments) but not the operators, such as +, -, *, or /. Example of these functions
are SUM, COUNT, AVERAGE, LOWER, MID, and many other that will be discussed in future Modules