E62 BMM.vp

Spreadsheets: Formatting

PREADSHEETS are used for a variety of tasks

including keeping a record of inventory,

equipment, or sales. They are also used to store

scientific data and reports as well as complicated

math manipulations. This unit explores the main

components and the formatting of spreadsheets.

Objective:

þ Use spreadsheet formatting, formulas,

and functions.

Key Terms:

E-unit: Spreadsheets: Formatting

Page 1 u www.MyCAERT.com

absolute cell reference

arguments

arithmetic operators

auto format

AVERAGE

bar graph

cell

cell alignment

cell border

cell reference

circular reference

column

comparison operators

data

data displays

data table

fill handle

financial ratios

font

formatting

formula

formula bar

function

horizontal analysis

keyboard shortcut

label

line graph

MAX

MIN

model

operators

order of operations

page setup

pie chart

range

reference operators

ROUND

row

sheet

sheet tab

spreadsheet

SUM

syntax

trend analysis

value

workbook

worksheet

Formatting Spreadsheets

SPREADSHEETS

A spreadsheet is a software application program that calculates, manages, and stores data

in rows and columns. It is the computer version of a paper-based accounting worksheet. The

uniqueness of a spreadsheet is the ability to calculate values using mathematical formulas and

the data in cells.

Versions

Excel, part of the Microsoft

Office Suite, is the spreadsheet

component of the Microsoft

Office Suite. Excel’s closest com

petitor is Google Sheets. Google

Sheets is a part of G Suite. Free

spreadsheet applications, includ-

ing OpenOffice and LibreOffice,

are available. Regardless of the

brand, all spreadsheet programs

present tables of values, arranged

in rows and columns, that you

can manipulate mathematically

using both basic and complex

arithmetic operations and

functions.

Uses

The primary purpose of a spreadsheet is to store data in a structured, organized format.

Spreadsheets are used in a variety of ways, depending on the needs of the business, industry, or

individual.

Spreadsheets organize, store, and link related information.

Spreadsheets allow you to sort data alphabetically, numerically, or chronologically.

Spreadsheets perform calculations on data using formulas.

Spreadsheets automatically recalculate answers when data components are changed in

them. Whenever data is updated inside the spreadsheet, all related data calculations auto

matically update.

Spreadsheets show different scenarios for businesses and aid in decision-making. For

example, a model is a spreadsheet containing data related to a specific situation. A model

E-unit: Spreadsheets: Formatting

Page 2 u www.MyCAERT.com

FIGURE 1. All spreadsheet programs present tables of values arranged in

rows and columns.

can be used to answer the question, “What if . . .?” Examples of common types of hypo

thetical questions are: “What if employees receive a 5% commission versus a 7% commis

sion?,” “What if the cost of baking ingredients increases or decreases?,” and “What if a stu

dent gets a score of 100 on a test compared to getting a score of 83?”

Data Displays

Data is often collected and held in spreadsheets before it is converted to a display. Data

displays are computer-generated charts, tables, and graphs, that are used to show combina

tions of facts, numbers, words, observations, or descriptions. Data displays summarize infor

mation and present it in a visually pleasing way. The most commonly used data display forms

are:

Pie Chart

A pie chart is a circular graphic divided into sectors that represent a proportion of the

whole. Information related to budgets may be presented through a chart that displays the per

centage components of various

budget line items. A pie chart can

show the breakdown of budgetary

expenses.

Bar Graph

A bar graph is a display of

data using horizontal or vertical

rectangular bars. Bar graphs are

often used to display budget

changes, or variances from one

accounting period to another.

Horizontal Analysis

Horizontal analysis is a data

display that compares financial

information over a series of his

torical periods. Horizontal analy

sis of the income statement usually involves comparing two years’ income statements side-by-

side with a third column to report the variance between the two years. For example:

If sales are reported for the year ended December 31, 2018 as $50,000 and sales are

reported for the year ended December 31, 2019 as $45,000, then a positive variance of

$5,000 would be reported. Statement users would note that sales had increased by 11%

((50,000 – 45,000) ÷ 5000). The same process would be completed for each line item of

the income statement.

E-unit: Spreadsheets: Formatting

Page 3 u www.MyCAERT.com

FIGURE 2. Data displays summarize information and present it in a visually

pleasing way.

Horizontal analysis of the balance sheet is performed in the same manner as for the

income statement. Two years’ balance sheets are presented side-by-side with a third col

umn to report the variance. Accountants may use bar graphs or data tables to present hor

izontal analysis in presentations. A data table is a display of information using rows and

columns.

Trend Analysis

Trend analysis is a technical

analysis that compares business

data over a certain time period, in

order to identify trends. Trend

lines may be drawn for key items

in the financial statements over

several accounting periods. Trend

lines are frequently drawn for rev

enues, expenses, net income, and

debt. Line graphs may be used to

display trend analysis in presenta-

tions. A line graph is a display of

information using a series of data

points (or markers) connected by

straight-line segments to display

changes in values.

Financial Ratios

Financial ratios are relationships/computations, based on a company’s financial informa-

tion, that are used for comparison purposes. Ratios are proportional, which allows small and

large businesses to compare financial information. Financial ratios may be displayed as pie

charts or bar graphs, depending on the comparison being made.

SPREADSHEET COMPONENTS

A workbook is an electronic spreadsheet file. A worksheet (sheet) is an individual

spreadsheet within a larger workbook (of multiple worksheets) used to organize, store, and

link related information. A sheet tab is used to display the worksheet a user is editing. The

tabs appear below the worksheet grid area and allow you to switch from one worksheet to

another in a workbook.

Data

Data is factual information, collected together for analysis and use. Data is any set of char

acters that can be stored in the cells of a spreadsheet. Spreadsheet character types include val

ues (numbers), labels, formulas, symbols, sound, pictures, functions, etc. A value is numeric

data entered into a cell.

E-unit: Spreadsheets: Formatting

Page 4 u www.MyCAERT.com

FIGURE 3. Data displays are computer-generated charts, tables, and graphs

used to display combinations of facts, numbers, words, observations, or

descriptions.

Row

A row is data that is stored in a series of cells, laid out horizontally in a spreadsheet. Rows

are designated by a number.

Column

A column is data that is stored in a series of cells, laid out vertically in a spreadsheet. Col

umns define the data contained in a table. Columns are identified by a letter.

Cell

A cell is the most basic storage unit of data in a spreadsheet program, located at the inter

section of a column and a row. A cell is a box where data is entered and stored. A cell refer

ence is the column letter and row number used to identify a specific cell (i.e., B3). A range is

a group of cells selected in a spreadsheet row or column. For example, in the formula “=

sum(D1:D8)” the cells in column D1 through D8 are the range of cells that are added

together.

Label

A label is text that is typed into the cells of a spreadsheet, usually describing data in the

rows and columns surrounding it. Labels have no numeric value and cannot be used in a for-

mula or a function.

Formula Bar

The formula bar is the spreadsheet location that shows the contents of the current cell

and allows the user to create and view formulas. The formula bar appears directly above the

column headings of a spreadsheet and displays the information typed into the active cell. For

example, if a cell that contains the formula is clicked, such as “=A9+B3,” the cell shows the

result of the formula. However, the formula bar continues to display what was actually typed

into the cell which, in this case, is “=A9+B3.”

FORMATTING SPREADSHEETS

Formatting is the arrangement of characteristics, such as fonts, numbers, cell borders, etc.,

found in spreadsheets. To prevent changes to formatting, Excel allows you to apply a “lock” to

prevent further editing.

Auto Format

Consistency is important in producing a professional document. Auto format is a software

feature that automatically changes the formatting or appearance of text. It also allows several

choices in creating enhancements. In Excel, format painter is a feature that copies formatting

E-unit: Spreadsheets: Formatting

Page 5 u www.MyCAERT.com

from one place to another, and enhances the consistency of the spreadsheet. To eliminate

either feature, you can “remove formatting.”

Page

Page setup is the term that describes the parameters set by the user to determine how a

spreadsheet appears when printed. You can set the margin width, the page orientation (portrait

or landscape), and insert a header or a footer.

Cell Format

You can format the cell alignment and utilize cell features to change the appearance of the

spreadsheet.

Alignment

Cell alignment is the feature that describes how text or numbers are arranged in a cell.

Left-aligned characters begin on the left edge of the cell. Right-aligned characters sit on the

right edge of the cell. Justified characters are equally spaced across a cell. Centered characters

are placed equal distance from the cell edge. Cell content can be indented (or tab-aligned).

Features

You can use the rotate feature (located in the format menu) to select and reorient an object

(images, text, etc.). The wrap text feature allows the entry of more than one line of text or

numbers in a cell. By using merge cells feature, selected cells can be allowed to lengthen

without changing column width. In Excel, you can merge two cells using a formula. Best fit

allows the creation of a column wide enough to show all the information. Row height

changes the standard row height to accommodate larger font sizes. Column width changes

the width of an entire column to view all cell content. An indication that the column width

must be changed is the “#####” display. Shrink to fit allows cell content to fit in the cell

without changing the cell size.

Appearance

Font and style formatting address the appearance of the spreadsheet and can enhance its

functionality.

Font

A font is the typeface used to create the spreadsheet. The font size is the dimension of the

font usually measured in “points.” For example, 72 points equals an inch. Points dictate the

height and dimension of the letters. Font color can also be changed.

Style

The style in which the text or numbers appear enhances the spreadsheet. Bold makes text

and numbers appear darker and heavier. Underline creates a line beneath the selected text or

E-unit: Spreadsheets: Formatting

Page 6 u www.MyCAERT.com

font to draw attention to the information. Italic slants the text or numbers to the right. Any of

the above can be used in combination.

Borders

In Microsoft Excel, Borders is a built-in tool to access predefined border styles built on

Excel’s grid system. A cell border is a frame that can be placed around the cell that includes

options to enhance the line style and the color. For example, cell shading (addition of color

and/or pattern) can be applied in any color or percent of color. If the finished appearance of the

cell border and shading is no longer desired, it is easy to alter or remove a formatting choice.

Numbers

Number formatting makes the spreadsheet easier to read. Currency formatting displays

numbers as monetary values, such as a dollar sign ($), a pound sign (£), etc. You can apply per

centage formatting to a cell that already contains a number. Percentage formatting tells Excel to

multiply a specific number by 100 and add the % sign. Large numbers are often easier to read if

a comma is used as a separator. To increase or decrease decimal places, you can apply the

decrease or increase option.

FORMULAS AND FUNCTIONS

Formulas are an expression that tells the computer what math operation to perform on a

specific value. In other words, a formula conducts a calculation. Spreadsheet formulas are often

used to automatically perform operations and tasks. Spreadsheet functions (preprogramed for-

mulas) automatically perform common calculations. Functions make a formula shorter and

reduce the possibility of errors.

Spreadsheet Formula

A formula is an expression that tells the computer what math operation to perform on a

specific value. The formula conducts a calculation. A formula must always begin with an equal

sign and use cell references as much as possible. For example, =C5 + D7 + E8 is a formula

that adds the numbers entered in cells C5, D7, and E8.

Benefits

Formulas are used to make business operations more accurate and efficient.

In order to recreate the same formula and perform the same operations over several sets

of data, formulas may be copied and pasted to different cells, and even to different

worksheets.

Formulas used in spreadsheets will recalculate answers automatically, as the data entered

in linked cells changes. Any time data is corrected or updated, the related cells holding

formulas also update their calculations.

E-unit: Spreadsheets: Formatting

Page 7 u www.MyCAERT.com

Hypothetical scenarios may be examined for decision-making based on quick changes to

data and the corresponding calculation changes.

When recalculations are necessary, spreadsheet formulas help eliminate common mis

takes caused by human error.

Operators

Operators are symbols used in a formula that indicate a specific type of calculation. There

are four categories of operators.

Arithmetic Operators

Arithmetic operators perform basic mathematical operations, such as addition, subtrac

tion, multiplication, or division. The Excel arithmetic operators are:

+ (plus sign) is the addition operator.

– (minus sign) is the subtraction operator.

* (asterisk) is the multiplication operator.

/ (forward slash) is the division operator.

% is the percent operator.

^ (caret) is the exponentiation operator.

Comparison Operators

Comparison operators are operators that allow the user to compare two values. Com-

parison operators are:

> (greater than)

< (less than)

= (equal to)

Text Concatenation Operator

The ampersand (&) is used to join one or more text strings to produce a single piece of text.

Reference Operators

Reference operators are operators that combine cell ranges for calculations. Reference

operators are the colon, the comma, and the space. The colon, “:” is the range operator. It pro

duces one reference to all the cells between two references. For example, A5:A15 references all

cells in the range including A5 through A15. The comma, “,” is the union operator, which

combines multiple references into one reference. A space is the intersection operator. It pro

duces one reference to cells common to the two references.

E-unit: Spreadsheets: Formatting

Page 8 u www.MyCAERT.com

Order

Order of operations is the sequence of calculations in a formula. The computer software

determines which calculation is first, which is second, etc. The order of evaluation is: expo

nents, multiplication/division, and addition/subtraction. Operators of equal priority are evalu

ated left to right. Parentheses change the priority order. Calculations within parentheses are

performed first. Excel follows general mathematics rules for calculations:

Parentheses

Exponents

Multiplication and division

Addition and subtraction.

You can use the acronym PEMDAS (Please Excuse My Dear Aunt Sally) to remember the

order of operations.

Efficiency

Always use the most efficient form of a formula. Frequently, there is more than one way to

perform a calculation. For example, when calculating an average, you could enter =(D4 + D5

+ D6 + D7)/4. However, using the AVERAGE function command would be more efficient.

In this case, you would simply enter =AVERAGE(D4:D7) or =AVERAGE (D4, D5, D6, D7)

Error Messages

Excel displays an error message for an invalid formula when a mistake has been made set-

ting up the formula. Common errors include:

Dividing by 0

Unequal number of parentheses

Misplaced operators or commas

Circular reference (A circular reference is a formula in a cell that directly or indirectly

refers to its own cell.)

Copying Formulas

When the same basic formula is needed, you can copy that formula instead of keying it

repeatedly. To copy a formula, use the following steps:

Enter the first formula in the row or column to be calculated.

Select the cell containing the completed formula.

Use the mouse to drag the fill handle across the remaining cells. Fill handle is a com

mand to copy data to cells in a column below the original cell.

E-unit: Spreadsheets: Formatting

Page 9 u www.MyCAERT.com

On each of the new formulas, notice the relative cell. The relative cell reference reflects

the row or column to which they have been copied.

Place dollar signs in the cell reference, if needed to remain the same when the formula is

copied. For example, if you want each cell the formula is copied in to multiply the corre

sponding percentage to a value store in the cell reference F5, F5 would be entered as

$F$5: an example of an absolute cell reference. An absolute cell reference is a cell

reference that remains constant when the shape or size of a spreadsheet changes. They are

important when discussing constant values in a spreadsheet. You can type numbers

directly into the formulas or use cell references. In short, the formula will use whatever

data the referenced cells contain.

Shortcuts

A keyboard shortcut is a combination of keystrokes that commands computer software

to perform a task. Shortcut keys typically combine Ctrl or Alt with some other keys to launch

a command.

Keying cell references into a formula increases the chance for error. Pointing or clicking

on individual cells to specify the cell reference in a formula decreases errors. A range of

cells can be selected through pointing and by dragging the mouse over (highlighting) the

cells to be included, beginning with the first cell.

To display formulas, you press Ctrl + ` (grave accent) to toggle between displaying val-

ues and displaying formulas at cell locations.

Excel automatically changes any cell references in any affected formulas when inserting

and deleting rows and columns. If the inserted row or column is the first or last cell refer-

ence in a formula, the formula will not automatically adjust. If the deleted row or column

is the first or last cell reference in a formula, the formula will automatically adjust the

same as if inserted within a range.

E-unit: Spreadsheets: Formatting

Page 10 u www.MyCAERT.com

FURTHER EXPLORATION…

ONLINE CONNECTION:

Spreadsheets and Your GPA

Using the spreadsheet software of your choice, create a

worksheet that calculates your GPA for the current semester as

well as your cumulative GPA. For assistance in this task, read

the article, “Create An Excel Spreadsheet to Calculate Your

GPA,” at https://ms-office.wonderhowto.com/how-to/create-

excel-spreadsheet-calculate-your-gpa-330487/

And view the YouTube video, “GPA Calculator Spreadsheet,”

at https://www.youtube.com/watch?v=z9jUcyckZag

Spreadsheet Functions

A function is an expression that performs common calculations and returns a single value.

Functions make a formula shorter and reduce the possibility of errors. Excel functions provide

one word access to a series of operations. Many functions are built-in to Excel.

Common Functions

The most commonly used functions include SUM, AVERAGE, MIN, MAX, IF, and

ROUND.

SUM is a function in Excel that adds all the numbers in cells. When creating program

ming functions, separate non-adjacent cells with commas: =SUM (D4, E5, F8). Use a

colon (:) to indicate adjacent cells or a range: =SUM (G1:G5). Within the parentheses,

list the first cell reference followed by a colon and then the last cell reference. For exam

ple, (G1:G5) would include cells G1, G2, G3, G4, and G5.

AVERAGE is a built-in function in Excel that calculates the average of a group of num

bers.

MIN is a function that finds the smallest number in a set of values.

MAX is a function that finds the largest number in a set of values.

IF is a function that is used to return one value if a condition is true and another value if it

is false. For example, if sales total more than $3,000 then return a “Yes” for Bonus, other-

wise, return a “No” for Bonus. The IF function is used often to analyze data by evaluating

specific conditions.

ROUND is a function in Excel that rounds a number to a specified number of digits. The

ROUND function rounds up or down. 1, 2, 3 and 4 get rounded down. 5, 6, 7, 8 and 9

get rounded up. For example, if the number 543.678 is located in cell A1 and the user

wishes the number to be rounded to no decimal places, the formula would be =

ROUND(A1, 0) and the formatted response is 544, with no decimal places. If however,

the user wishes the number to be rounded to one decimal place, the formula is =

ROUND(A1, 1) and the response is formatted as 543.7.

Arguments

Arguments are the values that functions use to perform calculations. Functions (built-in

formulas) require data, i.e., arguments, to be entered, in order to return a result. A function's

syntax is the layout of a function, including the function's name, parenthesis, comma separa

tors, and its arguments. The arguments are always surrounded by parentheses and individual

arguments are separated by commas. A simple example is the SUM function. The syntax for

this function is: SUM (Number1, Number2, ... Number255). The arguments for this func

tion are: Number1, Number2, ... Number255.

E-unit: Spreadsheets: Formatting

Page 11 u www.MyCAERT.com

Summary:

Spreadsheets consist of rows and columns of data. Spreadsheets organize, store, and

link related information. They allow you to sort your data alphabetically, numeri

cally, or chronologically. Spreadsheets perform calculations on data using formulas.

Spreadsheet formulas are often used to automatically perform operations and tasks.

Spreadsheet functions (preprogramed formulas) automatically perform common

calculations.

Checking Your Knowledge:

´ 1. Differentiate between a workbook and a worksheet.

2. List four common uses of spreadsheets by professionals.

3. What is the term for the intersection of a row and column on a spreadsheet?

4. What is the difference between a value and a label?

5. List the six mathematical Excel operators.

6. Explain how spreadsheets use ‘order of operations.’

Expanding Your Knowledge:

With your instructor’s permission, interview someone who works in a profession

that interests you. Discuss the various ways that professional uses spreadsheets in

the workplace.

Web Links:

: Excel Formulas

https://www.makeuseof.com/tag/15-excel-formulas-will-help-solve-real-life-

problems/

The Electronic Spreadsheet Revolutionized Business

https://www.npr.org/2015/02/27/389585340/how-the-electronic-spreadsheet-

revolutionized-business

Advanced Microsoft Excel Spreadsheets

http://www.pcworld.com/article/256530/how_to_create_advanced_microsoft_

excel_spreadsheets.html

E-unit: Spreadsheets: Formatting

Page 12 u www.MyCAERT.com