Spreadsheet Excel

Module 1 Course Notes

Spreadsheet Basics PF 106 Page 1

SPREADSHEET BASICS

This document contains general information about spreadsheets and the spreadsheet software

package for Microsoft Excel 2019/Office 365. Spreadsheet applications are used extensively in

industry to analyze data, create balance sheets, prepare budgets, and keep track of other

information such as project costs, inventory, etc. Here we will explore the basic concepts of using

a spreadsheet and briefly introduce some of the features found in an Excel workbook. Subsequent

notes will cover additional tools and develop an understanding of how Excel can be used as a

problem solving tool. Please be advised that figures used here may differ in look from yours if you

are using a different version of Excel for this course. Links provided come form the official

Microsoft support site for Excel in most cases. Feel free to go there and search on any topics you

wish to seek more information on as you read through these course notes.

BASIC FEATURES OF A 2019/ OFFICE 365 EXCEL WORKBOOK

THE WORKSHEET:

As seen in Figure 1, a spreadsheet is a work area composed of columns and rows. The

intersection of a column and row is referred to as a cell. Each cell is identified by an address

which indicates the column and row in which it resides. Columns are named by letters (A, B …

Z, AA, AB … AZ, BA, BB … BZ, etc.) and the rows are named by numbers (1, 2, … 1048576). The

cell address first lists the column name and then the row name. So the cell in the first column and

first row has the cell address A1 and the cell in the third column second row has the cell address

C2. To enter a value or formula, click on the cell where you wish to place your entry and type.

This selected cell is referred to as the active cell. The contents of the active cell will also be

displayed on the formula bar which is located directly above the column headings.

Figure 1

Module 1 Course Notes

Spreadsheet Basics PF 106 Page 2

A single Excel file may contain many spreadsheets, which are referred to as worksheets.

Together, these worksheets comprise an Excel workbook file. The tabs at the bottom of the

Excel window list the worksheets in the workbook. The worksheet you are currently using is

referred to as the active worksheet. The active worksheet tab appears highlighted. To

reference cells on different worksheets in a workbook place the worksheet name in front of the

cell address. So a cell that is on sheet3, in the second column third row would be referred to as

Sheet3!B3. Note the use of ! to indicate a sheet name. When referring to a cell on the active

worksheet the sheet name is not required. A worksheet can be renamed by right-clicking on the

tab and selecting Rename. A worksheet’s name can be almost any text string (e.g. data, Financial

Summary, January, etc.). In addition, worksheet tabs can be colored or reordered, and worksheets

may be copied or deleted. If a worksheet name contains a space or special character, it is required

to place single quotes around the worksheet name. For example, if a worksheet is named Profits

Second Quarter then to reference the cell in the third column and first row in that worksheet, you

would use ‘Profits Second Quarter’!C1.

NAVIGATING THE WORKSHEET:

The easiest way to move from one cell to the next is to use the mouse pointer and click on the

desired cell. The up, down, left, right arrow keys on the keyboard can also be used to move around

the worksheet one cell at a time. However not all worksheets are as simple as the one seen above

in Figure 1. The window displays columns A through M and rows 1 through 17. What if your

worksheet required more than 14 columns or 17 rows? To view rows 18 and above on the screen

use the Vertical Scroll bar on the right side of the worksheet window or the down arrows on

your keyboard. Similarly, to view additional columns on your screen use the Horizontal

scrollbar. Using the scroll bars changes the columns/rows displayed in the window but does

change the active cell. Other methods of navigating the worksheet that change not only the view

but also the cursor/active cell are the following:

• Ctrl+ Simultaneously holding down both the Ctrl key and down arrow key will move the

cursor to bottom of a list of consecutive cells in a column or to the next entry (non-empty

cell) in that column. This new cell will then become the active cell.

• Ctrl+ will move the cursor to the top of a consecutive list in a column or to the next entry

above the active cell in that column.

• Ctrl+ → will move the cursor to the rightmost consecutive entry in a row or to the next

entry in that row that contains data.

• Ctrl+ will move the cursor to the leftmost consecutive entry in a row or to the next entry

located to the left of the current active cell.

• Ctrl+Home will move the cursor to cell A1 of the worksheet.

• Ctrl+End will move the cursor to the bottom of the used worksheet area. i.e. the rightmost

column and last used row.

• The Page Down and Page Up buttons will move the cursor up or down a distance equal to

the number of rows displayed in the window. If the spreadsheet window shows 10 rows

then the cursor will be moved 10 rows, if the spreadsheet window shows 20 rows the cursor

will be moved 20 rows.

Module 1 Course Notes

Spreadsheet Basics PF 106 Page 3

• The Tab key will move the cursor to the right one column.

Additional tools for managing large spreadsheets are available using the Excel ribbon buttons and

will be covered later.

ACCESSING EXCEL’S TOOLS:

The Excel Window itself contains much more than just the worksheet cell area and the scroll bars.

The default format, as seen in Figure 1 above, displays several sets of drop-down buttons, tabs,

and icons located on both the top and bottom of the worksheet. Many of the basic features of

Excel 2019/Office 365 require the use of the various objects found in Excel’s ribbons and

toolbars. At the very top of the window is the Quick Access Toolbar, and the file title in the title

bar. Just below this are the Ribbon Tabs, Help Button, and Sizing buttons. The area just below

this is the selected ribbon and its associated icons and drop-down menus. Additional buttons and

tabs are displayed at the bottom of the window. The functions of each of these tools are as follows:

• The File Ribbon: This tab reveals a drop-down menu “backstage” view containing commands for creating a new Excel file, opening and existing file, saving, printing, emailing and closing the file. In addition to the menu are two buttons – Excel Options launches a dialog box allowing the user to customize their Excel features, and Exit Excel which closes the application.

• Quick Access Toolbar: This toolbar is located just to the right of the Office Button and can be customized by the user. This is an excellent location to place icons of features the user wishes to have most easily accessible. By default the toolbar contains the file save icon and the undo and redo icons. To customize the toolbar click on the down arrow located just to the right of the icons (Figure 2).

• The Ribbon Tabs: Many of the features available in Excel can be accessed from one or more of the Excel ribbons. To use a tool on a specific ribbon, click on the ribbon tab. Excel contains the following ribbons: Home, Insert, Draw, Page Layout, Formulas, Data, Review and View. The ribbon displayed in Figure 1 above is the Home ribbon.

Ribbons are further organized into groups of icons/buttons of related tasks. For example the Font group on the Home tab contains buttons to change the font, modify the font size, apply a font style, etc. A summary of the contents of each ribbon is presented later. Additional context sensitive ribbons are also available depending on the tool being used. For example, if a workbook contains a chart, when the chart is selected several chart tool ribbon tabs will appear that allow the user to edit the chart.

• The Help Button: This button launches the Excel help feature and is located in the top right-hand side of the Excel window.

• Sizing Buttons: As with all Microsoft Windows applications, these buttons can be used to size the Excel application window. Clicking on these buttons allows the user to close, maximize, minimize, or allow custom sizing of the window. A second set of these icons appear in each worksheet window within the Excel window. One or more worksheet windows can be opened simultaneously and sized independently using these buttons.

Figure 2

Module 1 Course Notes

Spreadsheet Basics PF 106 Page 4

• View Buttons: These buttons are located in the bottom right hand side of the Excel window and allow the user to change the view of the active window between Normal, Page Layout, and Page Break View.

• Zoom: The Zoom Bar allows the user to customize the screen magnification in the active window by sliding the zoom selector left and right. The Zoom Bar is located in the bottom right corner of the Excel window.

THE RIBBONS:

Accessing many of the Excel tools requires the use of ribbons. An illustration and description of

each ribbon is listed below. Additional content sensitive ribbons are also available – these will be

presented as the features associated with the ribbons are introduced. Please note that some of the

tools described here may have no meaning to you as you have not yet begun to create your

spreadsheets or write formulas. You need not memorize the contents of the ribbons – only be

aware of the general categories and use this section as a reference as you begin to use these tools.

The look of each ribbon may differ depending on how much space is available on the monitor

being used and the font size of the application in general.

The Home Ribbon:

The home ribbon (Figure 3) contains many of the most commonly used Excel tools.

They are arranged into the following groupings:

• The Clipboard group contains the buttons for copying and pasting, including the Cut

button to delete selected text, the Copy button to copy selected text and the Paste

button to paste selected text. Excel allows the user to cut/copy and paste parts of

cells, entire cells, ranges of cells, and even entire worksheets with these tools. Also

included in this group is the Format Painter tool that allows the user to copy the

format of a cell or range of cells without copying the actual content.

• The Font group contains buttons and drop down menus for changing the font, font size,

and font styles (bold, italics, underline). There are also two buttons that can be

used to increase or decrease font size one size at a time. The Font group also contains

buttons to apply borders t0 a cell or to apply a color to the fill and/or text of the cell.

• The Alignment group allows the user to adjust the alignment of text within the cell (left,

right, centered, justified to top of cell, justified to bottom of cell). There are even tools to

rotate text within the cell, automatically wrap text within in the cell, and merge several

cells and center the center the text over those cells.

Figure 3

Module 1 Course Notes

Spreadsheet Basics PF 106 Page 5

• The Number group contains tools for changing the format of cells containing numerical

values. A value such as 0.5 can be displayed as $.50 (currency), 50% (percent) or even as

1 using the increase and decrease buttons.

• The Styles group contains additional tools for formatting groups of cells using

conditional formatting, table formatting and cell styles. Cell styles are pre-set formats that

allow the user to quickly and easily setup professional and easy to read spreadsheets.

• The Cells group contains several important buttons that open drop down lists. The Insert

button allows the user to insert a row, column, or worksheet. Correspondingly the Delete

button allows the user to delete a cell, set of cells, columns, or rows from the worksheet

and adjust the location of adjacent cells. From the Format button menu one can adjust

the height of rows, width of columns, hide or unhide rows or columns, and rename/move

worksheets.

• The Editing group located on the far right of the ribbon contains miscellaneous buttons

that are also found on some of the other ribbons. They include the AutoSum button, a

Continue Pattern button (for copying patterns of cells), a Clear button, a Filtering/Sorting

button, and the Find/Select button.

Please note that the view of the ribbons depends on the size of the Excel window. If you size the

Excel window into a long narrow box, it is likely that the icons will be compressed and/or replaced

with a drop-down arrow (note the Number, Styles, and Cells groupings in Figure 4 below). Also

note the arrows located on the bottom right-hand corner of some ribbon groupings. These arrows

can be used to launch dialog boxes which allow the user access to an even greater array of features

for that tool.

The Insert Ribbon:

Figure 5 is an illustration of the Insert ribbon. This ribbon contains groupings for inserting tables,

illustrations, charts, links, and specific textual elements such as a header/footer, WordArt, etc.

The Draw Ribbon:

This ribbon contains tools and colors to use for drawing.

Figure 4

Group Dialog Box launch arrow

Figure 5

Module 1 Course Notes

Spreadsheet Basics PF 106 Page 6

The Page Layout Ribbon:

Figure 6 is an illustration of the Page Layout ribbon. From this ribbon the general layout and print features can be modified.

The groupings include the following:

• Themes – includes preset color and font themes for customizing your worksheet displays

• Page Setup – includes access to drop-down menus for customizing print layouts including page margins, orientation, paper size, print area, page breaks, backgrounds and setting print titles to appear on each page.

• Scale to Fit – includes features that allow the user to scale the selected print area to fit within the page width and/or page length or to just scale it to a specific percent up or down.

• Sheet Options – includes options to modify the printout area, add sheet titles, repeat column and/or row headings, insert gridlines, print in draft, or order pages.

• Arrange – includes options specific to creating drawings on a worksheet. These include alignment of the elements, snap to grid, view gridlines, etc.

The Formulas Ribbon:

Figure 7 is an illustration of the Formulas ribbon. This ribbon is useful when entering calculations

into the workbook. It contains the insert function button (Fx- also available on the formula bar),

some of the AutoSum commands (Σ – also available on the home ribbon editing group), and drop

down menus to access many of the most commonly used functions such as Logical functions, Text

functions, Lookup and Reference functions, etc. This ribbon also contains a Defined Names group

with features for specifying and using named ranges. The formula auditing features such as Trace

Precedence and Show Formulas are located in the Formula Auditing grouping. There is a button

to access the calculation options within Excel.

The Data Ribbon:

The Data ribbon (Figure 8) contains tools for managing data within a spreadsheet using

database-type features such as sorting and filtering. This ribbon also provides access to tools to

help “clean-up” data by dividing text into columns or removing duplicates. A useful feature is

the ability to group rows and columns and display subtotals for groupings. The Data ribbon also

contains the What-If Analysis button for access to the Goal Seek tool.

Figure 7

Figure 6

Figure 8

Module 1 Course Notes

Spreadsheet Basics PF 106 Page 7

The Review Ribbon:

Excel provides a set of tools to support collaboration. These tools allow users to share documents,

block a workbook or worksheet from being edited, insert comments into a workbook, and trace

changes to a spreadsheet. These tools are located in the Review ribbon (Figure 9). The Review

ribbon also contains links to the spelling and thesaurus features.

The View Ribbon:

Figure 10 is an illustration of the View ribbon. The View ribbon contains tools for changing the

way Excel displays content. The View ribbon contains features for changing screen views and

zoom level and displaying gridlines, headings, formula bars, etc. The most useful features of this

ribbon are the commands that allow the user to open multiple worksheet windows within the

Excel window (New Window) and arrange these windows. Window views can also be customized

to freeze or split certain portions of the worksheet to that titles or a specific set of data can be

viewable no matter where you scroll to in the worksheet. This ribbon also contains a toolbar

button to launch and create Macros. Macros are “little programs” that tell Excel to execute a

specific set of instructions.

WRITING SIMPLE FORMU LAS

Now that you have seen the general structure of a workbook and the interface for launching Excel’s

tools you can begin to create your own spreadsheets. The most important element of the

worksheet is the cell. We use cells to store data and formulas that perform calculations on data

contained within other cells.

ENTERING A SIMPLE FORMULA:

Cells may contain textual labels, numeric and

Boolean values, or formulas that result in

either labels or values. For example, in Figure 11

cell A1 contains the label “Item,” cell B2 contains

the numeric value 25, and cell D2 contains a

formula which multiplies the values in cells B2

and C2. To type in a label or value, click on the

cell to make it active and begin typing. To enter a formula into a cell, begin by typing an equal sign

(=).

A B C D E

1 Item

Price

$/day

Quantity -

days Total % of total

2 Food 25 10 250 56%

3 Lodging 15 10 150

4 Travel 10 5 50

5 total 450

Figure 11

Figure 9

Figure 10

Module 1 Course Notes

Spreadsheet Basics PF 106 Page 8

Formulas may consist of a combination of arithmetic operators (+,-,*, / , ^), relational

operators (<,>,<=,>=,=,<>), and operands (constants/cell references). In the context of

Figure 11, suppose we want to write a formula in cell E2 to calculate food costs (D2) as a

percentage of total costs (D5). An example of such a formula might be =D2/450. This formula

contains the cell reference D2, the arithmetic operator for division (/), and the numerical constant

450. The result of this formula will be the numerical value resulting from dividing the contents

of cell D2 by 450. If the value in cell D2 is modified, the value resulting from this formula would

be automatically updated. However, if the total in cell D5 (450) is updated, the formula =D2/450

will not be affected as the value 450 has been placed directly in the formula as a constant and not

as a cell reference. Experienced spreadsheet users know that explicitly listing all input data and

using cell references in formulas to refer to the input data greatly improves the readability,

maintenance, and even reliability of the spreadsheet.

Formulas may also include functions. Functions are pre-defined worksheet formulas –

“shortcuts” available in Excel. In Figure 11, cell D5 displays the value 450. However, if you click

on cell D5, in the formula bar you will see the cell actually contains the formula =SUM(D2:D4).

This formula contains the SUM function, which adds all values contained in cells D2, D3, and D4.

The syntax D2:D4 represents a range of values in Excel. If any of the values in this range were

to be modified, the value in cell D5 would be automatically updated.

Formulas in Excel must be written in a very specific format, referred to as syntax. Syntax is not

the language itself; it can be compared to rules of spelling or the grammar of a language. For

example, the syntax for the multiplication operator in Excel is an asterisk (*) not a dot (•) or an

(x) as is used in algebraic expressions.

If a cell contains a formula, this formula can be viewed in one of several ways:

• Click on the cell and view/modify the formula in the Formula Bar.

• Double click on the cell to activate Edit Mode and view/modify the formula directly in the

cell.

• Display formulas for all cells by modifying the worksheet properties. Click on the Formulas

ribbon and then select the Show Formulas button . To revert back to the original view,

again click the Show Formulas button. Alternatively, the keyboard shortcut keys Ctrl+`

(grave accent) will activate this feature.

Before you begin writing meaningful formulas, there are several things that you will need to know

in order to obtain the desired results.

ORDER OF PRECEDENCE

As in mathematics, spreadsheets analyze formulas based on a specified order of operations. When

writing formulas for spreadsheets, you must pay careful attention to this order of precedence or

run the risk of obtaining incorrect values. The order of precedence in which a formula is evaluated

is as follows:

1. Operations enclosed in parentheses including functions

2. Exponentiation

3. Multiplication and division from left to right

Module 1 Course Notes

Spreadsheet Basics PF 106 Page 9

4. Addition and subtraction from left to right

5. Relational operators (<, >, =, <=, >=, <>)

Consider the formula =B2+3*C2. Excel would first multiply 3 by the value in cell C2 and then

take the result of that calculation and add it to the value in B2. If cell C2 contains the value 10 and

cell B2 the value 25, the resulting value would be 55. If, however, you meant to add 3 to cell B2

and then multiply it by the value in cell C2 you would not obtain the desired result (280). To

indicate that B2 should be first added to 3, parentheses must be used around the operation. Since

operations in parentheses are always performed first, the calculations will now be completed in

the desired order. The Excel formula =(B2+3)*C2 results in the value 280.

DATA PRECISION

Excel provides the user a variety of methods for formatting text and data within a cell. In addition

to standard text formatting tools available in word processors (font, font style, color, etc.), users

can also specify specific number formats (i.e., commas and decimal places) and styles (negative

number display, currency, etc.). These tools can be accessed from the home tab groups or by

clicking on the group launch arrows and opening the Format Cells dialog box. These features all

focus on the display of a value in the cell.

Consider the spreadsheet in Figure 12. In

cell B5 is the formula =B2+B3+B4. The

value in cell B5 is $45.52. Column C

contains the same numbers as column B,

but the cells in column C are displayed

with zero decimal places. To modify

the number of decimal places displayed

use the increase and decrease buttons

on the Number group of the Home

ribbon. Thus, cell C2 contains the value 25.10 but it is displayed as 25 and similarly cells C3, C4

and C5 contain the values 10.42, 10.00 and 45.52 but they are displayed as 10, 10 and 46. Looking

only at the display of column C and adding up these values (25 + 10 + 10), the result is 45.

However if you enter the formula =C2+C3+C4 and display it with zero decimal places, the

computer comes up with a result of 46. Has Excel made an addition error? No.

In order to understand why the value displayed in cell C5 is correct, it is important to differentiate

between the value that Excel stores in a cell and the value Excel displays for a cell. The value

that Excel stores for a cell is the precise value you enter in the spreadsheet or the precise value

resulting from a formula in that cell. The value Excel displays to you depends on how the cell is

formatted. For example, if you type 0.25 into cell A1 and cell A1 is formatted as percent, the

number 0.25 is stored in the cell and Excel will display 25%. When Excel evaluates a formula that

contains cell reference, it uses the precise values stored in the cells to calculate the result.

What is important to understand is that when you change the format of the cell display

it does not affect the actual value of the number stored by the computer. So you may

enter a value that has 5 decimal places but for clarity only show the value to one decimal place.

When you do this, Excel still uses all 5 decimal places in calculations involving that cell. As a

A B C D E

1 Item

Price

$/day

Price $/day -

displayed to

zero decimal

places

Price $/day -

rounded to zero

decimal places % of total

2 Food 25.10$ 25$ 25$ 55%

3 Lodging 10.42$ 10$ 10$ 23%

4 Travel 10.00$ 10$ 10$ 22%

5 total 45.52$ 46$ 45$

Figure 12

Module 1 Course Notes

Spreadsheet Basics PF 106 Page 10

result of this, what can sometimes occur is that the result of arithmetic operations appears to be

incorrect.

In column D, the Round function (which will be covered later) has been used to change the actual

precision of the value. Hence, in column D the formula =D2+D3+D4 results in the value 45 since

it is actually adding the exact values 25 + 10 +10 and not 25.1 + 10.42 + 10 as is the case in columns

B and C.

Consider these of examples where calculations using the precise value in a cell may not be consistent with the cell display:

• Multiplication of Decimals: Consider the formula =1/3 displayed with two decimal

places and placed in cell A1. What would the value of A1*100 be? The value displayed in

cell A1 would be 0.33. One may expect that the result of A1*100 would be 33. However,

the precise value stored would be .3333333333333333…. Thus A1*100 would not be 33

but instead would be 33.3333333333333….

• Percentages: In cell E2 on the spreadsheet in Figure 12 the user has entered the formula

=B2/B5 to represent the cost of food as a percentage of total cost and formatted the cell

using the percent style button located in the Numbers group on the Home tab. The

result displayed is 55%. But what is the actual precise value stored in this cell? When

computing percent on a pencil and paper exercise this would be done by dividing 25 by 45

to get 0.55 and then multiplying 0.55 by 100 to arrive at 55 percent. In Excel after writing

the formula =B2/B5 the % toolbar button was used. Did the % toolbar button modify the

precise value from .55 to 55? The answer is no. The percent toolbar button displayed the

value as a percentage by dividing it by 100 and inserting the percent symbol, but the

precise value was not changed from the original .55 calculated in the formula =B2/B5.

Thus, when using cell E2 in subsequent calculations, the computer will use the value 0.55

and not the value 55.

Another display element to be aware of is the use of commas in a workbook. The value 25235 can

be displayed as 25,235 by clicking on the comma button in the Number group of the Home tab. If

25,235 is typed directly into a cell including the comma, Excel will interpret it correctly and setup

the cell with a comma format. However, Excel will not infer that a number typed with commas is

a numerical constant when it is typed in a formula. As you will see later, the comma has a special

use in Excel formulas. As an example, consider the formula =25,233+2. If this formula, including

the comma, is typed directly into a cell an error message window would appear; Excel is unable

to interpret the formula as written. When using functions in formulas, typing commas can present

even more of a problem. A best practice recommendation is that numbers never be typed with

commas; instead, use the formatting options instead to modify the display.

USING FORMULAS IN SPREADSHEETS

Now that the importance of order of precedence and the difference between precision and format

display are understood, the next step is to begin writing simple formulas. As you have already

seen, formulas can not only perform simple mathematical calculations using constants, but can

also reference data contained in other cells. As a result of this, the values required for a calculation

Module 1 Course Notes

Spreadsheet Basics PF 106 Page 11

can reside in other parts of a workbook. A cell reference can be used again and again in many

formulas as a “shortcut” to the value it contains. Later when that value changes, all calculations

referencing this value will be automatically updated. This feature makes our spreadsheets

extremely flexible and easily modifiable.

Consider the spreadsheet display in Figure 13 below which lists a company’s costs for employee

travel. The costs for travel vary based on whether the travel day is during the week (Monday to

Friday) or a weekend day (Saturday and Sunday). Currently it is assumed that an employee travels

4 weekday days and 1 weekend day. What if the number of weekdays they travel changes from 4

to 3? How many of the values on this spreadsheet would change? Almost all of the values in

columns D and G would be affected. On a large spreadsheet it becomes difficult, if not impossible,

to keep track of all the calculations that would be affected by a single change to one or more value.

If spreadsheets are designed properly, values need only to change in one place and all subsequent

calculations will be automatically updated.

On the spreadsheet in Figure 13 above, the number of weekdays and weekend travel days are

placed in cells F1 and F2, respectively. Any formula that requires the use of these values would

reference these cells and not the actual values 4 and 1. For example, cell D5 contains the formula

for total cost per week for food: =B5*F1+C5*F2. Cell D6 contains another formula,

=B6*F1+C6*F2, which also references cells F1 and F2. Later, if a change is required to the

number of weekday days, only cell F1 would need to be updated. Excel would automatically

recalculate the values in cell D5 and D6.

Proper spreadsheet design becomes increasingly important as spreadsheets become larger and

more complicated. Using formulas and setting up the spreadsheet so that no actual value appears

more than once for a given variable will ensure that your numbers can be reliably and quickly

updated.

DESIGNING & USING WO RKBOOKS WITH MUTIPLE WORKSHEETS

Thus far the workbooks we’ve used have contained only a single worksheet. As the sets of data

inputs and calculations become more complex, the design of our workbooks will be critical to their

ultimate usability and maintenance. This section will explore how to design your workbooks to be

efficient and effective and how to execute workbook solutions requiring multiple worksheets.

Figure 13

A B C D E F G

1 data: 4

2 1

3

4 Item

Price per

weekday

day

Price per

weekend

day

Total

Weekly

Cost

% Cost

Weekday

% Cost

Weekend

% Total

Cost

5 Food 25 50 150 50% 59% 53%

6 Lodging 15 35 95 30% 41% 33%

7 Transportation 10 40 20% 0% 14%

8 total 50 85 285

# weekend days per week

# weekdays per week

Module 1 Course Notes

Spreadsheet Basics PF 106 Page 12

DESIGNING EFFICIENT/EFFECTIVE SPREADSHEETS

The problem in Figure 13 above only required a simple single Excel worksheet. Frequently

problems are more complex and require more and varied inputs, multiple calculations, and even

multiple solution cases. What other techniques can be used to best solve complex problems using

an Excel workbook? This section will look at some of the tools that are available to us in modern

day spreadsheets, including the use of multiple worksheets, 3-D formulas, and a technique that

allows us to work backwards to determine what input value is required for a desired output value.

Before beginning any workbook, planning a design is critical to implementing an effective

solution. Good planning will help avoid time consuming changes later. There are three main

reasons why a spreadsheet solution may require changes:

• It is difficult to read and use.

• It does not allow additional variables to be easily included.

• It does not allow values to be easily changed.

The next few sections provide guidelines that can prevent a spreadsheet from having any of these

three problems.

LIST DATA INPUTS

There are several concepts that should be taken into consideration when designing effective

spreadsheets. One such concept, that has already been discussed previously, is the use of formulas

that reference input values rather than use constants, i.e. “hard coded” values. Data should

never appear more than once in a spreadsheet. We have seen how this allows us to easily

make changes and updates.

Figure 14 shows a spreadsheet to calculate monthly

travel costs based on the service fee shown in cell B1. If

the Service Fee changes to 10%, we only need to change

the value in cell B1 to 10%, and all affected values will be

automatically updated.

The user does not need to know specifically which cells are affected by the changes.

This type of analysis, in which alternative values are substituted for data inputs, is known as a

“what-if” analysis. A well designed spreadsheet will allow the user to vary one or more values to

determine how they will affect the solution.

VERTICAL VERSUS HORIZONTAL ORIENTATION

Figure 14

Figure 14

Module 1 Course Notes

Spreadsheet Basics PF 106 Page 13

Figure 15 displays a spreadsheet that lists

two different job opportunities with

varying wages and hours. The worksheet

shows data organized with each category as

a separate column and each job as a

separate row.

Figure 16 displays the same spreadsheet

except now the data is organized with each

category as a separate row and each job as

a separate column.

Which configuration is best? That answer

depends on the data being presented, its

readability, and ease of maintenance. In

general if you have 4 categories and 200 jobs, it would be best to use a configuration similar to

Figure 15, with the columns listing the categories and the rows the jobs. If you have 4 jobs and

200 categories, it’s best to put the jobs as the column headings, as similar to Figure 16. But this

rule of thumb assumes common categories. In some cases there are different input categories for

each calculation, making these formats difficult. In such cases making separate tables is a better

option.

What if data is shared between calculations but never varies from one calculation to another? This

is the case when listing common constants such as inches per foot or ounces per pound. Often

this type of data is placed in a separate input area where it can be updated in only one location.

For example if 50 work weeks per year is common to all jobs, this value can be placed in only one

cell. If the number of weeks varies from job to job, it should be listed as a separate input for each

job. Knowing if a value may vary from case to case will help a designer select a robust design that

is flexible enough to maintain.

To accommodate different inputs and cases, the spreadsheet designer has the ability to do the

following:

1. Add additional columns.

2. Add additional rows.

3. Create additional “tables” on a worksheet. These “tables” can be placed anywhere on the

spreadsheet in additional columns, rows, etc. and can vary in format as the data inputs vary.

4. Create additional worksheets in the workbook.

5. Create additional workbooks.

USING MULTIPLE WORKSHEETS – INPUTS & OUTPUTS

So far we have looked at examples with inputs and outputs on the same sheet. But, what if the

previous worksheet we created now had to accommodate six different jobs with varying wages,

hours, and overtime opportunities. There would be six different $/hour values, six different

numbers for hours per week, six different numbers for weeks per year, etc. As we’ve seen, there is

no one right way to organize this information. Each layout will offer its own advantages and

1

2

3

4

A B C D E

hours per

week

weeks

per year

worked

$/hour $/year

Job #1: 20 50 9.00$ 9,000.00$

Job #2 20 50 8.50$ 8,500.00$

Should he switch? don't switch

1

2

3

4

5 6

7

A B C

Job #1: Job #2

$/hour 9.00$ 8.50$

hours per week 20 20

weeks per year worked 50 50

$/year 9,000.00$ 8,500.00$

Should he switch? don't switch Figure 16

Figure 15

Module 1 Course Notes

Spreadsheet Basics PF 106 Page 14

disadvantages. It will partly depend on how the data ultimately needs to be presented, and what

information is likely to vary and which is not.

This scenario would best be designed with inputs and outputs on separate worksheets.

Often when there are many varying inputs several worksheets are used, with each set of inputs on

a separate worksheet and another worksheet for outputs. As seen in Figure 17, two worksheets

have been used for this simple example. The worksheet Inputs lists the wage and hour

information for each job offer. Sheet Calc contains the calculations for weekly pay including

overtime at time and a half (1.5) and double time (2) as well as annual wages.

This configuration is excellent for comparing categories of values, as outputs are together

and inputs are together. It’s easy to compare the $/hour, the hours per week, or even the total

wages per year across the different job offers. Even though some absolute cell referencing is

required, it is also convenient for copying formulas, as similar formulas are located in adjacent

cells.

Figure 18 below displays the Calc worksheet in formula view. Notice there are no hard coded

values in the formulas and the design minimizes repetitious typing of formulas.

Remember when writing formulas that reference cells on other worksheets, the cell address must

be preceded by the sheet name and an exclamation point. For example, the formula in cell Calc!B6

Remember when writing formulas that reference cells on other worksheets, the cell address must

be preceded by the sheet name and an exclamation point. For example, the formula in cell B6 in

the Calc worksheet which calculates overtime wages paid at time and a half (1.5x) references both

Figure 4

Inputs

Calc

Figure 18

Figure 17

Module 1 Course Notes

Spreadsheet Basics PF 106 Page 15

the $/hour and average overtime hours per week values on the Inputs worksheet. To reference

these values, the formula =Inputs!B$2*$B1*Inputs!B5 precedes these references with the

Inputs worksheet reference. The reference to the 1.5 OT multiplier is on the same worksheet as

the cell where the formula is written and does not need a sheet name.

Note that common “constants” such as 1.5 and 2.0 are listed in a separate location from the other

variable inputs. These could have just as easily been placed on the worksheet Inputs or even on a

separate worksheet. The layout used here is excellent in cases where additional calculations are

later required, such as adding hours per week of overtime at 3 times the regular wage rate.

However, it’s not as easy to add new jobs. Additional jobs will require changes be made to both of

the worksheets and formulas copied into new column. It is frequently the case that flexibility with

respect to one concern is sacrificed to make another more flexible or easier to read/use.

One more note regarding this input/output layout. This layout should not be used if the data needs

to be presented in a case by case layout, where all inputs and outputs from a specific case need to

be presented together (i.e., Job1 on one sheet, job2 on another sheet, etc.). In such a situation a

case by case layout may be preferable. Designing such solutions will be discussed in a subsequent

section.

DISPLAYING MULTIPLE WORKSHEETS IN THE EXCEL WINDOW

One tip when writing formulas with multiple worksheet references is to display these worksheets

simultaneously on your screen. This can be accomplished by opening new worksheet windows

within the Excel window and sizing each as needed. A diagram of a single maximized worksheet

in the Excel window can be seen in Figure 19 below. Figure 20 is a diagram showing multiple sized

worksheet windows within the Excel window. The steps to get from the display in Figure 19 to the

display in Figure 20 are follows:

If the worksheet is maximized within the Excel window, click on the Restore Worksheet

Window Button in the upper right corner of the worksheet window.

Excel Window Restore/Sizing button

Worksheet Window Restore/Sizing button

Figure 19

Module 1 Course Notes

Spreadsheet Basics PF 106 Page 16

• Once the worksheet window is no longer maximized, Excel allows the user to size the

worksheet. When the cursor hovers over any edge of the worksheet window it will turn into a

sizing handle (two sided arrow). Clicking and holding when the sizing handle is displayed

allows the user to drag the cursor up/down to adjust the height of the window and left/right

to adjust the width of the window. Size the window to show only the necessary headings and

data, leaving ample room in the Excel window for other worksheets to be displayed.

• To open a second window, select the View Ribbon Tab and select the New Window button

from the Window group. A second window will now be visible within the Excel window. To

display the desired worksheet, click on the sheet tab of the worksheet you wish to display and

then size this window as needed.

• Repeat the previous step, until as many windows as needed (or as possible to view) are

displayed. In this case the Inputs worksheet window and the Calc worksheet window can be

arranged side-by-side, as seen in Figure 20.

When writing formulas that references a cell on one of these other worksheets, just click on the

worksheet cell needed and it will be automatically inserted into the formula with the appropriate

sheet name.

Figure 20 Sizing handles appear in corner when the mouse hovers at the edge of the worksheet window

Module 1 Course Notes

Spreadsheet Basics PF 106 Page 17

USING MULTIPLE WORKSHEETS –

CASE BY CASE METHOD

An alternate workbook design

configuration which is better for

displaying an entire case, with all

inputs and outputs for that case, can

be seen in Figure 22. In this section, each

job is placed on a separate worksheet

(Figure 21).

Setting up layouts where each case has

identical input categories and calculations

is extremely easy. First create the

worksheet for Case 1 (in this instance Job1).

Then write the appropriate formulas as

needed. Make sure not to use sheet

names to reference items contained on

this Job 1 sheet. Then once the calculations

are complete and the worksheet formatted

appropriated, copy the entire worksheet as

many times as needed and simply modify

the input values. One method of copying an entire worksheet is as follows:

• To select the worksheet click on the sheet selector button located just above the row number

1 and to the left of the column label A.

• Use the copy feature to copy the worksheet: either click the copy icon in the Clipboard Group

of the Home Ribbon or press Ctrl+C (control key and the C key held down simultaneously).

• If necessary, insert a new worksheet using the Insert Worksheet button to the right of the sheet

tabs at the bottom of the worksheet.

• In the new worksheet, use the paste features to paste the data (the paste icon in the Clipboard

Group of the Home Ribbon or Ctrl+V).

Once the worksheets are copied, name each sheet appropriately (Job1, Job2, etc.) and substitute

the corresponding input values. The calculations will automatically be updated on each worksheet

as the formulas will reference the values on the corresponding sheet.

The major disadvantage of this method is if new variables are needed, for example OT hours at 3x

the $/hour pay rate, each would then need to be added to each worksheet. In an upcoming section

a method for accomplishing this task will be discussed.

What if you still need each job to be displayed in separate tables but still wish to compare the total

values across cases? Instead of designing two sets of worksheets for each configuration

(inputs/outputs vs. case by case) one can set up a summary sheet from the case by case

worksheets. This is especially simple if the values for each element are always located in the same

cell. In this case, the cost elements are the same in each case and reside in the same relative

location: the total wages is always in cell B19 on each worksheet. In instances where each case

Figure 21

Module 1 Course Notes

Spreadsheet Basics PF 106 Page 18

has different elements, each case could be setup as needed but the summarizing of the data would

not be as trivial. The next section will discuss just how to easily set up this type of summary using

3-D formulas.

USING 3-D FORMULAS TO CREATE SUMMARIES

When inputs and outputs are placed in separate areas of the workbook it is easy to compare and

aggregate data between different cases. To similarly compare and aggregate data when organizing

each case is on a different sheet in the workbook, a summary worksheet can be setup using 3-D

cell referencing.

Figure 22 is a summary of the six job offers,

where data categories to be aggregated are in

column A. The total values for each category are

calculated in column B, and the average values

for each category are in column C.

The simplest method to create such a summary

is to first copy the titles from the output section of any individual case to a new worksheet. If the

category descriptions are listed down a column, keep that configuration. Similarly if the category

descriptions are listed across rows, copy them in the same configuration. Figure 22 displays the

summary categories in a vertical list.

To total the values for the wages per week category for all six jobs, write the following formula in

cell B2: =Job1!B15+Job2!B15+Job3!B15+Job4!B15+Job5!B15+Job6!B15. Since the

order of the categories matches those in each of the six case worksheets, this formula can then be

copied down the column to calculate the values for OT wages/week at 1.5x, OT wages/week at 2x,

etc. The cell references will change relatively but the sheet names will remain the same.

A trick to writing this formula in cases where there are many worksheets is to use a range of cells

spanning multiple sheets: =SUM(Job1!B15:Job6!B15). This will only work if the sheets

are adjacent to each other in the workbook. Using this second method, assuming these

worksheet tabs are all adjacent to one another, write the formula

=AVERAGE(Job1!B15:Job6!B15) to obtain the average value of regular wages as follows:

To simultaneously select multiple adjacent worksheets complete the following steps:

• On the summary worksheet (Figure 22) select cell C2 and begin writing the formula as

usual =AVERAGE( …

• At the point where you are ready to enter the range, click on the appropriate cell (or cells)

in the 1st worksheet of the range, in this case Job1!B15.

• Hold down the shift key and then using the mouse, click on the tab of the last worksheet

to be included in the range and then press Enter.

• Complete the formula by typing a close parenthesis.

This formula =AVERAGE(Job1!B15:Job6!B15) may also be typed out using the keyboard,

though errors can be introduced when doing so. Later, additional Jobs can be added to these totals

by simply adding a worksheet somewhere between the worksheet tab range in the formulas. But

1

2

3

4

5

6

A B C

Calculations: Total Average

Regular wages per week 1,602.50$ 267.08$

OT wages/week at 1.5x 251.25$ 41.88$

OT wages/week at 2x 78.00$ 13.00$

Total Wages per week 1,931.75$ 321.96$

Total Wages per year 97,772.50$ 16,295.42$ Figure 22

Module 1 Course Notes

Spreadsheet Basics PF 106 Page 19

be careful – if you add a worksheet somewhere among Job1 through 6 that should not be included,

errors will occur.

What if a more detailed summary is desired, as seen in Figure 23? This summary is more like the

comparisons setup using the first layout where data inputs are on one page and outputs are on

another. With the case by case method workbook layout this type of summary is also easy to set

up.

Cells B1:G1 each contain cell references to the values calculated on the individual worksheets;

=Job1!B15 in cell B1, =Job2!B15 in cell C1, etc. These formulas are then copied down the column.

The totals and averages are easily obtained from this summary worksheet. Why not just copy and

paste the data from each job worksheet as values instead of referencing the cells? References

ensure that if any data input or calculation changes on an individual case, the summary will be

automatically updated.

Changing the Orientation of the Data:

What if you have listed your data in a vertical format but wish to present your summary in a

horizontal format? It can be tedious writing each formula out across a row when they cannot be

copied down the column to match the data inputs.

A temporary solution is to create the vertical summary and then copy it, use paste special

and choose Values Only and the Transpose options. This will copy the summary values

and simultaneously change the orientation of the presentation. It’s temporary because you

need to copy values and since they’re values, any changes to the worksheet will render the

summary incorrect.

CHARTS AND GRAPHS

Frequently we need to present our data in a more “user friendly” format than a table. Often a

simple graphical representation of our results greatly enhances the impact of the message. The

Excel charting tools can represent information stored in a workbook in graphical format such as

pie charts, line plots, bar/column graphs, and pivot tables or pivot charts. This section will focus

on which type of chart best represents the information we wish to convey. This section does not

focus on the mechanical details of using the charting tools; creating and modifying charts can be

learned from any step-by-step instruction manuals available on this topic. Note that the types of

charts below are actual links to the Microsoft support tutorials for each type.

There are several basic types of charts that will be discussed in this section:

• Line Charts • XY Scatter Plots

Calculations: Job1 Job2 Job3 Job4 Job5 Job6 Total Average

Regular wages per week 360.00$ 280.00$ 300.00$ 332.50$ 150.00$ 180.00$ 1,602.50$ 267.08$

OT wages/week at 1.5x 67.50$ -$ 112.50$ 71.25$ -$ -$ 251.25$ 41.88$

OT wages/week at 2x 18.00$ -$ -$ -$ 60.00$ -$ 78.00$ 13.00$

Total Wages per week 445.50$ 280.00$ 412.50$ 403.75$ 210.00$ 180.00$ 1,931.75$ 321.96$

Total Wages per year 22,275.00$ 14,000.00$ 21,450.00$ 20,187.50$ 10,500.00$ 9,360.00$ 97,772.50$ 16,295.42$

Figure 23

Module 1 Course Notes

Spreadsheet Basics PF 106 Page 20

• Pivot Tables & Pivot Charts

• Pie Charts

• Bar & Column Charts

LINE CHART:

A Line Chart plots a series of data on the X and Y-axes. Each value is plotted in equal intervals

along the X-axis. This type of chart is an excellent method of illustrating trends. It should not

be used to show the relationship between two variables. For example, look at (fictitious)

population data for the United States in Figure 24. Notice the points are evenly spaced on the

horizontal axis of the chart. However, these “even spaces” represent different time intervals. The

same eighth of an inch represents time spans ranging from as little as 20 years to 1000 years.

The chart clearly illustrates how this population has grown. The trend line drawn between data

points shows that the population grew slowly and then took a small dip before rapidly increasing

during the 1800-1950 period. In recent years the chart shows another small population decrease.

XY SCATTER CHART:

An XY Scatter Chart is used to plot the relationship between two variables on an XY plot.

Most students learn a simple form of this type of chart in algebra class – the general formula for

a straight line in the XY plot is y=mx+b. The XY Scatter chart options allow plotting of the data

points with no connecting lines, creation of a straight line between points, or creation of a curved

line between points.

What if the data in Figure 24, time vs. population, was plotted on an XY Scatter chart rather than

on a line chart? Many of the points would be clustered around the year 2000. This would not be

a very effective method of showing a trend. In addition, time is not really a “variable” in the sense

that a specific year is not by itself a predictor of the population. That is, population does not

depend on time – it depends on variables like infant mortality rate, immigration, average expected

life spans, etc.

To illustrate the use of an XY Scatter chart, look at a plot of immigration and population

growth as seen in Figure 25. This chart shows the relationship between the two variables, in

this case population growth over a twenty year time span versus the average yearly immigration

rate over this same twenty year time span. Population growth is shown as a function of

immigration. The line connecting these points allows the user to extrapolate population growth

Year

Population in

Millions

-1000 2

0 12

1000 25

1500 5

1800 35

1900 100

1950 190

1980 250

2000 240

Population

0

50

100

150

200

250

300

-1000 0 1000 1500 1800 1900 1950 1980 2000Year

P o

p u

la ti

o n

i n

M il li o

n s

Figure 24

Module 1 Course Notes

Module 1 Course Notes PF 106 Page 21

for a specific immigration rate. Notice that the points are not plotted in chronological order, time

is not explicitly shown. The resulting graph shows a positive sloping line, indicating that as

immigration increases so will population growth.

BAR & COLUMN CHARTS:

Bar charts are graphs that use horizontal bars to represent a quantity for a specific item. Similarly,

Column charts are graphs that use vertical bars to represent a quantity for a specific item. Both

of these types of charts are well suited for comparing quantities of discrete items.

In the line chart plotted in Figure 24, population is plotted along a timeline. Population on the

timeline can be extrapolated by selecting the point on the line corresponding to a specific time

period. While this value may not be exactly accurate as there is no functional dependency, it at

least gives a reasonable estimate. Now consider another set of data, the population in the year

2007 for 5 major US cities: Boston, Hartford,

New York, Washington D.C. and Miami. Figure

3 shows this data plotted on a line chart. Could

we estimate the population of Philadelphia (the

triangle point drawn on the line) as between

that of New York and Washington since it

geographically lies between the two? Of course

not, each city is a discrete entity and relevant

data cannot be extrapolated on a graph joining

the population data points. This type of

discrete information is best displayed in a

Bar or Column chart as seen in Figure 27.

Span of

Years

Total

Population

Growth Immigration/yr

1900-1920 60 1.5

1920-1940 40 0.7

1940-1960 20 0.5

1960-1980 15 0.25

1980-2000 10 0.25

in miions of people: The effects on immigration to the US

population growth

0

0.5

1

1.5

2

0 20 40 60 80

20 year population growth in millions

a v

e ra

g e

y e

a rl

y i m

m ig

ra ti

o n

in m

il li

o n

s

0

2

4

6

8

10

12

14

Boston Hartford NY Washington Miami

Population

Figure 26

Philadelphia?

Figure 155

Module 1 Course Notes

Module 1 Course Notes PF 106 Page 22

Figure 27 shows two charts representing the same data. The left hand chart is a Column chart

and the right hand chart is Bar chart. Each is equally good at displaying this type of discreet

data. Which one to choose is a matter of individual preference and limitations such as width and

length of the page.

There are several useful options in Excel for enhancing Column and Bar charts. Each bar/column

can be individually shaded, labels may be placed on each bar, a Legend may be displayed, the

background can be modified, and the text fonts for the titles and axes may be specified. These

charts also have options for displaying groups of values using a “clustered” bar/column charts or

a “stacked” bar/column charts.

Clusters: Cluster Bar and Column

charts allow for the use of multiple data

sets, such as populations for each city

for multiple years. Data for each city is

then plotted as a series of bars

(representing years) clustered

together. An example of a clustered

column chart is illustrated in Figure

28.

City Population

Boston 7

Hartford 3

NY 12

Washington 6.5

Miami 8

Population

0

2

4

6

8

10

12

14

B os

to n

H ar

tf or

d N Y

W as

hi ng

to n

M ia

m i

P o

p u

la ti

o n

i n

M il li

o n

s

Population

0 2 4 6 8 10 12 14

Boston

Hartford

NY

Washington

Miami

Population in Millions

Figure 27

0

2

4

6

8

10

12

Boston Hartf ord NY Washington Miami

1990 6 3.3 11.7 6 7.3

2000 7 3 12 6.5 8

Population in Millions

Population Decade Comparison - by City

Figure 28

Population data

In millions

Module 1 Course Notes

Module 1 Course Notes PF 106 Page 23

Stacked: Stacked Bar and Column

charts make it possible to take a

group of data and subdivide it. This

data can then be illustrated as a series

of bars, where each bar contains

multiple sub-sections. For example,

populations in each city for two

different age groups can be “stacked,”

showing each component and the

total population for that city. An

example of a stacked bar chart is

illustrated in Figure 29.

PIE CHARTS:

A pie chart is basically a circle subdivided to represent “parts of a whole”. A pie chart is

excellent for representing percentage comparisons between categories. In the

population example, a pie chart can be used to illustrate the relative sizes of each of the 5 cities.

The illustration shown in Figure 30 is a pie chart comparing populations in each of these cities as

a percentage of the total population for all 5 cities. The pie chart does not readily illustrate that

there are 12 million people in NY versus 12,000 or 1200. However, it can be easily extrapolated

that NY has the largest population of these five cities, whi

le Hartford has the smallest population.

Figure 30

City Population

Boston 7

Hartford 3

NY 12

Washington 6.5

Miami 8

Population Comparisons Boston

19%

Hartford

8%

NY

33%

Washington

18%

Miami

22%

0 5 10 15 20 25

Boston

Hartford

NY

Washington

Miami

6

3.3

11.7

6

7.3

7

3

12

6.5

8

Population in Millions

City Populations - By Age Category

under 25 over 25

Figure 29

Module 1 Course Notes

Module 1 Course Notes PF 106 Page 24

PIVOT TABLES & PIVOT CHARTS

Pivot tables are one of Excel's most powerful features. A pivot table allows you to extract the

significance from a large, detailed data set. Let’s use a data set that consists of 213 records and 6

fields. Order ID, Product, Category, Amount, Date and Country. A partial listing is shown in

Figure 31 below:

To insert a pivot table, click any cell inside the data set and then on the Insert tab (in the Tables

group), click PivotTable. The following dialog box appears. Excel will automatically

select the data for you. The default location for a new pivot table is a new worksheet.

The PivotTable Fields pane as shown in Figure 33 appears. To get the total amount exported of

each product, drag the Product field to the Rows area, the Amount field to the Values area, and

the Country field to the Filters area as shown below.

Figure 31

Figure 32

Module 1 Course Notes

Module 1 Course Notes PF 106 Page 25

Below in Figure 34 you can see a sample of the pivot table. Bananas are the main export product.

You can sort the pivot table to get Bananas at the top of the list by cicking on any cell inside the

Sum of Amount column and then by right clicking and selecting Sort and Sort Largest to

Smallest. See Figure 35 below.

Figure 35

Because we added the Country field to the Filters area, you can also filter this pivot table by

Country if you wish. For example, which products do we export the most to France? Simply

click the filter drop-down and select France. As can be seen in Figure 36 below, apples are the

main export product to France.

Figure 33

Figure 34

Module 1 Course Notes

Module 1 Course Notes PF 106 Page 26

Figure 36

Note that you can use the standard filter (the triangle next to Row labels) to only show the

amounts of specific products as well.

By default, Excel summarizes the data by either summing or counting the items. To change the

type of calculation that you want to use, click any cell inside the Sum of Amount column then

right click and click Value Field Settings. Then, choose the type of calculation you want to use.

For example, click Count. The result is that 16 of the 28 orders to France are ‘Apple’ orders.

Figure 37

If you drag a field to the Rows area and Columns area, you can create a two-dimensional pivot

table. First, insert a new pivot table. Next, to get the total amount exported to each country, of

each product, drag the Country field to the Rows area, drag the Product fiels to the Columns area,

drag the Amount field to the Values area, and drag the Category field to the Filters area. Figure

38 show the PivotTable Fields window and corresponding two-dimensional Pivot Table that is

created.

Module 1 Course Notes

Module 1 Course Notes PF 106 Page 27

Figure 38

To easily compare the numbers, you can create a pivot chart and apply a filter. A pivot chart is

the visual representation of a pivot table in Excel. Pivot charts and pivot tables are connected with

each other. To insert a pivot chart, click any cell inside the pivot table. Then, on the Analyze tab,

in the Tools group, click PivotChart. The Insert Chart dialog box

appears. Click OK. Figure 39 shows a couple of possible pivot charts produced from the data.

Note: any changes you make to the pivot chart are immediately reflected in the pivot table and

vice versa. Use the standard filters (triangles next to Product and Country). For example, use the

Country filter to only show the total amount of each product exported to the United States.

Figure 39

Module 1 Course Notes

Module 1 Course Notes PF 106 Page 28

GOOD PRACTICES FOR CHARTS:

It’s always easy when working with a tool such as charting to either create charts that are either poorly documented or overly busy or messy. Here are some good rules of thumb for creating effective, readable charts – whether they be in Excel or anywhere else:

• Choose the correct chart type to convey the points you wish to make.

• Charts should appear neat and clean and be done as simply as possible to convey the most important message. Well-chosen colors and patterns clearly illustrate similarities and differences.

• Charts should have titles that clearly indicate the subject matter.

• The axes scales should be clearly titled and delineated with well scaled and well-marked intervals. Unit values should reflect the needed “significance.” e.g., values in the millions need not show decimal places.

• Data should be clearly marked with labels or on an accompanying legend/key.

• If a chart is rotated it should be readable either from the bottom or the right hand side of the page.

A good overall chart should be easy to read and tell a complete story which can stand alone even without the text. When displaying data, it really is true that a picture is worth a thousand words.

WORKING WITH UNITS P ROBLEMS

Sometimes a more complex set of problems are introduced that require calculations with multiple

unit conversions and workbook designs that span multiple worksheets. As the sets of data inputs

and calculations become more complex, we will need to understand how to use and ultimately

design worksheet solutions that are easy to use and maintain.

WORKING WITH UNIT CONVERSIONS:

THE PROBLEM

First consider a simple problem that analyzes the compensation received at two different places

of employment.

Alex works for a fast food restaurant 20 hours a week for 50 weeks per year. He makes $9.00 an hour.

The chain across the street is offering him an annual wage of $8500 for the same number of hours per

week and a two week paid vacation. He is willing to switch jobs only if the new job pays more than his

old one. Should he switch jobs?

The first step in solving this problem is recognizing that not all of the information is in comparable

units. To compare the compensation received at Alex’s current job versus the compensation he

would receive at the other, the salary amounts must be in equivalent units (dollars per hour,

dollars per year, etc.). The current employment wage data is in dollars per hour, while the other

offer is in dollars per year. The paid vacation is already taken into account by using 50 weeks per

year.

APPROACH #1 – CONVERTING ALL UNITS TO DOLLARS PER YEAR

Module 1 Course Notes

Module 1 Course Notes PF 106 Page 29

One approach is converting Alex’s current wages to dollars per year. The following technique can

be used given the wage information provided: dollars per hour, hours per week, and weeks per

year.

• First convert dollars per hour to dollars per week using the number of hours per week Alex

works. Notice how hours in the denominator cancel out hours in the numerator resulting in

the unit dollars per week.

• Then take dollars per week just calculated and convert it to dollars per year. Alex works 50

weeks per year. The weeks in the denominator will cancel out the weeks in the numerator

resulting in the unit dollars per year.

Those with good mathematical skills should be able to convert dollars per hour directly into

dollars per year as follows:

ANALYZING THE DATA

The current job’s wage per year has now been calculated and can be compared with the wage

offered from this new job. The job at the other restaurant pays $8500 per year. Now that the

values are in “like units,” in this case dollars per year, the two values can be compared in several

different ways:

• Use a relational expression to see if the proposed job pays more than the current job. By doing

this analysis, it becomes clear that the remuneration at this proposed job is less than what

Alex receives at his current job. An IF function can also be used if a different result is desired

other than a TRUE or FALSE value.

$8500>9000 → FALSE

• Use subtraction to quantify the cost difference of the current job vs. the proposed job.

$8500 dollars/year - $9000 dollars/year → - $500 dollars difference

• Compare the percent difference by calculating the ratio of the difference in wages vs. the

current wage.

(new wage - current wage)/current wage → (8500-9000)/9000 → -5.6%

APPROACH #2 – CONVERTING ALL UNITS TO DOLLARS HOUR

A second approach is to convert $8500 per year into dollars per hour. First take the dollars per

year and divide it by weeks per year to arrive at dollars per week. Notice that only the weeks

worked are used (50) so the two salaries are for the same amount of work. The paid vacation is

Module 1 Course Notes

Module 1 Course Notes PF 106 Page 30

already taken into account by including it in the wage. Finally divide dollars per week by hours

per week to obtain dollars per hour.

These conversions can become quite complex. The easiest way to deal with this complexity is to

write out the units and perform each of the term cancellations. Engineers have always been taught

that if you solve the “units” conversion problem the rest is easy. A couple of rules of thumb from

math that you should remember:

• A unit in the numerator can be canceled out by a like unit in the denominator.

• When dividing two fractions, the denominator of the denominator becomes the numerator.

Alternatively, invert the 2nd fraction and multiply. In the example below you can cancel out

weeks to get dollars per year.

• Only add and subtract like units.

• Always make sure your conversions make sense. For example: You convert dollars per year

into dollars per week and find your result is more than what you started out with, is this

correct? Logic would tell you that weeks are smaller units than years. Thus, you would be

expecting dollars per week would be less than dollars per year.

What if you were told that the first job pays $9 per hour in Canadian dollars and the second job

pays $8.50 in US dollars? How can $8.50 US be compared with $9.00 Canadian? The conversion

rate on a website gives the following info: There are 0.95451 US Dollars per Canadian Dollar. How

can these values be compared?

• To convert $9 Canadian to US dollars multiply Canadian dollars by the conversion of US

Dollars per Canadian dollar

It would be incorrect to divide 9 by .95451, from a units standpoint that would result in the unit

Candadian$2 per US$. Logically, if you would divide 9 by .95451 the resulting value will be larger.

As the US dollar is worth more than the Canadian dollar (at this point in time), one would expect

$9 Canadian dollars to result in fewer US dollars.

Alternatively, $8.50 US could be converted to Canadian dollars by dividing by this conversion

factor.

Module 1 Course Notes

Module 1 Course Notes PF 106 Page 31

USING A SPREADSHEET TO EXECUTE THE SOLUTION

Now that the first step, understanding the problem,

of our problem solving approach is completed, the

next step is to execute it within an Excel workbook.

The spreadsheet in Figure 40 contains the input

information given in this problem, with each input

value explicitly listed. For a simple problem such as

this, it may be easier to just plug the values into a

calculator. But, what if later on one or more of the

input values change? The advantage of listing each

variable explicitly becomes quickly apparent.

Using the spreadsheet in Figure 40, write the necessary formulas to determine whether or not to

switch jobs.

Using the first approach, calculate the annual income earned for Alex’s current job in dollars

per year. This requires multiplying the dollars per hour by hours per week and weeks per year

to arrive at dollars per year. Translated into Excel syntax we have the formula =C2*B3*B4 in

cell D2. Then compare the result with the $8500 proposed salary for the new job. Previously this

was shown using a simple relational expression. Here, in cell D11, use an IF function to explicitly

list whether or not to switch. =IF(D7>D2, “switch”, “don’t switch”). This can also be done

in one step by substituting C2*B3*B4 for D2 in the IF statement as follows:

=IF(D7> C2*B3*B4, “switch”, “don’t switch”)

Using the second approach to compare the dollar per hour rate for both jobs by writing the

formula =D7/B8/B7 in cell C7. Cell C7 will then contain the hourly rate for this new job. A

formula can then be written to compare these two rates.

WHAT-IF ANALYSES AND GOAL SEEK

You now have the ability to create fairly extensive worksheets using a host of arithmetic formulas.

As previously discussed, the greatest benefit of using a spreadsheet tool is the ability to easily

incorporate changes and instantly know the effects of those changes. This section will look at

techniques that lets the user not only know how changes will affect the final values, but allow the

user to determine what input will be need to reach a specified final value.

WHAT-IF ANALYSIS

Consider the worksheet in Figure 41. This worksheet includes a list of data inputs (cells A3:B12),

calculation section A15:B18 and a final value in cell B19. The worksheet calculated the Total

Figure 40

1

2

3

4

5

6

7

8

9

10

11

A B C D

Job #1: $/hour $/year

rate 9.00$ 9,000.00$

hours per week 20

weeks per year 50

Job #2:

rate 8.50$ 8,500.00$

hours per week 20

weeks worked per

year 50

Should he switch? don't switch

Module 1 Course Notes

Module 1 Course Notes PF 106 Page 32

Wages per year from a specific job based on a list of expected hours worked, including hours at

regular pay and hours at 1.5x and 2x regular pay.

Each of the formulas in cells B15 through B19 contain cell

references to the data inputs above. By constructing the

worksheet in this format, the user can easily perform What-

If analysis by simply substituting one or more of the given

input values.

For example, if the dollar per hour pay rate were to increase

to $9.50 per hour, substituting this new value in cell B4

would be all that is necessary to arrive at a new Total Wages

per year. All of the intermediate calculations in cells

B15:B18 would be automatically updated as needed. The

user may substitute one or more data inputs as desired to

perform a variety of What-If analyses. This is especially

important in business when trying to analyze the effects of changes on the viability of a project or

the prosperity of a business.

THE GOAL SEEK TOOL

In addition to being able to perform “what-if” scenarios on spreadsheets, many spreadsheet

applications provide tools that allow you to work backwards to determine what input value is

required for a specific output value. In Microsoft Excel this tool is called Goal Seek and can be

accessed by clicking the What-If button in the Forecast group on the Data Ribbon.

Using the worksheet in Figure 41 consider the question, “To earn $25,000 in wages from the same

number/type of hours, what base payrate in $/hour would be required?” This question can be

answered using the Goal Seek tool.

Before going through the detailed steps of using the Goal Seek tool, it is important to know its

limitations:

• It is only possible to vary values in cells that do not already contain formulas. So cell B16, OT

wages per week, cannot be varied since it already contains the formula =B$4*B6*B11.

• Only one value can be varied per analysis. For example, the number of hours and the payrate

cannot be varied simultaneously to arrive at a specific outcome for total wages per year.

The mechanical steps to execute this Goal Seek are as follows:

1. On the Data Ribbon in the Forecast group, click on the

What-If button and select Goal Seek. This will display the

Goal Seek dialog box as seen in Figure 42. Each box must

now be filled in with the proper data.

In the question, “To earn $25,000 in wages from the same

number/type of hours, what base payrate in $/hour would be

required?”, the total wages is the value being set to Figure 42

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

A B

Job #1:

Job Inputs:

$/hour 9.00$

regular hours per week 40

weeks per year worked 50

average OT hours/week at 1.5x 5

average OT hours/week at 2x 1

Multipliers:

Multiplier for 1.5x 1.5

Multiplier for 2x 2

Calculations:

Regular wages per week 360.00$

OT wages/week at 1.5x 67.50$

OT wages/week at 2x 18.00$

Total Wages per week 445.50$

Total Wages per year 22,275.00$

Figure 41

Module 1 Course Notes

Module 1 Course Notes PF 106 Page 33

$25,ooo. This calculation resides in cell B19 which currently contains $22,275. Cell B4 contains

the $/hour, the value being varied to arrive at this $25,000 total wage.

2. In the Set cell box place the cell reference of the value being set. In this case, the cell B19.

This can be done by clicking directly on the cell or typing the reference into the text box. Note

that by default this reference is made absolute (anchored in).

3. In the To value box type the new value being set in the set cell box. In this case, the value

$25,000 (no comma’s please!).

4. In the By changing cell box place the cell reference of the value being varied: the $/hour

in cell B4 in this case. Again this reference can be selected by clicking on the cell or typing and

defaults to an absolute reference (anchored in).

5. Once all of the inputs are specified, click OK. This will

bring up the Goal Seek Status dialog box as seen in

Figure 43. Excel uses trial and error to find the solution,

varing the cell to be changed in increments. If the value

has been found then the Target Value and Current

Value of the set cell will be the same. If not, you can

actually modify the starting “change” value used and the

increments (other options will be displayed). In this

example an answer has been found. Note that the

“changed” value is not displayed in the dialog box, but can be viewed in the worksheet (Hint:

at this point you can’t change worksheets so start the goal seek on the worksheet page

containing the “change” value and it will be easy to view at this point).

6. To accept the changes, click OK. To return to the original values, click Cancel.

Once the changes are accepted the old values are deleted. One trick to preserve these old values

is to copy the worksheet and run the goal seek on the new worksheet. When working with a

complex workbook with calculations/variables on multiple worksheets, copy the workbook and

perform any what-if analyses and/or goal seeks in the copied workbook. In this manner the

original values will remain intact.

NOTE: Excel does provide some more advanced features for these types of analyses – Scenario Manager for doing

What-If analysises and Solver for performing multivariable “goal-seeks”.

MAIL -MERGE TOOL IN E XCEL

Performing a Mail Merge is a great way to generate personalized letters or emails. There are three

documents involved in the mail merge process:

• Your main document ( a word document for our purposes)

• Your data source (excel for our purposes)

• Your merged document (a pdf file)

Figure 43

Module 1 Course Notes

Module 1 Course Notes PF 106 Page 34

The most important step in the mail merge process is to set up and prepare your data. For this

course and as part of the final project, you will be using data in an excel spreadsheet with customer

names and addresses as your data source for your recipient list that will consist of a personalized

letter in MS Word. You will take an existing template of a letter in Word (given to you as part of

the final project), and will use the mail merge button in Word and will insert various mail merge

fields into your letter to personalize it, then preview the mail merge and save it to a single pdf file.

The pdf file will include separate letters for each of the customers (rows) in the excel spreadsheet.

Click HERE for a good reference on how to do this.