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.
After completing this lesson, you should be able to do the following:
By the end of this lesson, make sure you have completed the readings and activities found in the Lesson 1 Course Schedule.
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.
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).
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 ‘:’.
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.
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.
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...
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".)
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.
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.
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.