AGBM106:

Lesson 1: Spreadsheet Basics

Lesson 1 Overview (1 of 9)
Lesson 1 Overview

Introduction

A spreadsheet is a two dimensional calculator. In this course we will be learning about Excel. Excel is an electronic spreadsheet program that can be used for storing, organizing and manipulating data.

Lesson Objectives

After completing this lesson, you should be able to do the following:

Lesson Readings & Activities

By the end of this lesson, make sure you have completed the readings and activities found in the Lesson 1 Course Schedule.

The Spreadsheet (2 of 9)
The Spreadsheet

What is a spreadsheet?

A spreadsheet is a two dimensional calculator. Numbers, text, and formulas are entered into cells.  Each cell has an address; a column value denoted by a letter (eg. A) and a row value denoted by a number (eg. 1). Thus A1 means column A and row 1, P7 means column P and row 7, etc...

To access a cell simply click on it. Whatever cell you have most recently clicked on is considered the active cell. When you are in a cell you can enter text, numbers, and formulas.

Below you can see that tex has been entered t to create a heading named ‘Week’ and a heading named ‘Sales (Units)’. Under the ‘Week’ heading the numbers 1,2,3, and 4 have been entered. For the sake of the example imagine that cheese is being sold and for each week. Sales has been entered in lbs under the ‘Sales (Units)’ heading.

The Formula Bar (3 of 9)
The Formula Bar

The Formula Bar

One special location in a spreadsheet is the formula bar.

The formula bar associates itself with whatever cell is currently active (whichever cell you last clicked your way into).

Time Saving Features of Spreadsheets (4 of 9)
Time Saving Features of Spreadsheets

Time Saving Features of Spreadsheets

Let's add up all the sales in units over the four weeks listed. To do this you need to make C7 the active cell(click on the cell to make it active). Then what you type in cell C7 will appear in the cell and the formula bar. The formula bar basically gives you a larger canvas to work with compared to the actual size of a cell.

To mathematically manipulate numbers in Excel you use formulas. To begin a formula use ‘=’ followed by whatever mathematical operations you want to perform.

In this example - Method 1- This is an example of  an inefficient  use of Excel.  We will address the  reasons  shortly. The point here is that you can use Excel as a traditional calculator; taking numbers and applying mathematical operations to those numbers. From time to time we all use Excel in this manner but we are not really taking advantage of the tool. A slightly better formula is seen in Method 2 below.

In Method 2  instead of using numbers in the formula we are using cell references. In Excel, a cell reference identifies the location a cell or group of cells in the worksheet. The ability to use actual numbers and/or cell references is the attribute that has made spreadsheets so indispensable. Throughout the semester you will find many times when  being able to use cell references in formulas enables efficient calculations.

It turns out, adding things up is such a popular activity in the business world that Excel (and all other spreadsheets) contain a pre-programmed addition or SUM functions.

In C7 you can see that we entered =sum(c3:c7).  We begin the formula the same way  you begin every formula with an ‘=’ sign. Next we activate the Excel sum function by entering s u m. Doing this tells excel to start adding up the contents of c3 and continue to c6 using a ‘:’.

Excel will begin the calculation using the number stored in whatever cell is to the left of the ‘:’ and continue through whichever cell is to the right of the ‘:’.

Adding non adjacent cells: What if you wanted to add the contents of c1 and c4? In that case you could still use the sum function but instead of a ‘:’ We would use a ‘,’ to separate the beginning and ending cells. We would use =sum(c3,c4)

Using the SUM function as I did in Method #3 is far more efficient than either Method #1 or Method #2. By more efficient we mean that the process takes less time to program into Excel and the results will be more accurate. When properly using a spreadsheet the goal is to obtain accurate results in an efficient manner.

Imagine using Method 1 or Method 2 when you have 1000 numbers to add up? It would take a long time to manually enter such a large sum. Method 1 is further compromised because what if you make a mistake when entering one of the actual numbers in the formula? By using an appropriate cell reference instead of the actual number the range of possible mistakes is reduced. As long as the data in the cells is correct to begin with and you use the correct references in the formula then my results should be accurate.

Finding Excel Functions by Online Search (5 of 9)
Finding Excel Functions by Online Search

Finding Excel Functions by Online Search

Obviously you need to know the Excel name for the relevant Excel function, most commonly used functions have intuitive names (sum, average, count, etc….) and for the more specialized or complicated functions there is a way to search Excel for the proper syntax, we will cover this issue later in the semester.

Excel is such a popular tool you can also use an internet search engine to find if an Excel function exists for a particular task. For instance if I wanted the Excel formula for calculating the periodic payment in a constant payment amortized loan I would go to a search engine and type in: Excel loan constant payment. In this example the correct function would be the PMT function which we will look at later in the semester.

Spreadsheet Navigation (6 of 9)
Spreadsheet Navigation

Spreadsheet Navigation

Worksheet Names

Each Excel spreadsheet file contains multiple worksheets.

The worksheet name is found at the bottom of the screen.  Each worksheet has a name, the default names are ‘Sheet 1’, ‘Sheet2’ etc... 

In  Lab 1 you will learn how to change the name of a worksheet.
Menus (7 of 9)
Menus

Menus

At the top of each worksheet are a variety of windows driven menus.

There are different menus for different activities. The most important menus for beginners are ‘File’ and ‘Home’. (The instructional videos in this course may have menus that you do not, later in the semester we will learn about some hidden menus such as "developer".)

Files and Home (8 of 9)
Files and Home

Files and Home

The ‘File’ menu is used to open and save files.

The ‘Home’ menu contains many essential Excel commands such as clipboard operations (like copy and paste), font manipulations (change type, or size, boldface, etc...), and others we will take a closer look at in lab.

Conclusions (9 of 9)
Conclusions

Conclusions

What is a spreadsheet?

A spreadsheet is a two dimensional calculator that uses a system of interconnected cells where each cell has an address. Each cell address has two parts: (1) a column reference indicated with a letter, and (2) a row reference indicated with a number. When referring to a cell the column part of the reference ALWAYS comes first.

Why use spreadsheets?

Spreadsheets permit efficient and accurate calculations. The degree to which you create an accurate and efficient spreadsheet is correlated to your understanding of how to use spreadsheet references and predefined spreadsheet functions.


Top of page