1.2 A Brief Introduction to Excel
This is not a course about Excel, but it does showcase some pretty amazing things that you can do with Excel. Excel is the de facto standard software used in business. There are competing products such as Apple’s Numbers or Google Sheets. But no other product is as comprehensive or powerful as Excel. Furthermore, once you learn concepts in Excel, you can generalize that knowledge to the other products.
Excel is far more than a calculating machine. The best use of the product is to model a business problem or a solution to a business problem. If that sounds rather abstract then consider the Excel spreadsheet below that models how much of two different kinds of coffee (drip coffee or espresso drinks) a coffee shop needs to sell in order to generate a desired level of revenue. The beauty of the model is that only the inputs, the yellow boxes, need to change. All other aspects of the model depend on those. The numbers highlighted in green represent good outcomes—where we exceed our desired revenue. But all the numbers in the matrix will change if we change any of the three inputs. Even which numbers are highlighted in green will change!
Coffee Shop Model
Before we can model with Excel, we need to learn some of the basic building blocks. These are covered below.
Formulas
The fundamental unit of work in Excel is a formula such as =B2*C2. This formula takes the values located in cells B2 and C2 and multiplies them together. It doesn’t matter what those values are or how frequently we change them—Excel is always monitoring the B2 and C2 cells ready to recalculate the minute anything changes. Now, the formula itself must live in some cell within the worksheet, say D2. So most cells in Excel contain values, calculations on those values, or are just left blank.
Starting the Spreadsheet
Here are some other examples of Excel formulas using the same two cells whether or not they make sense from a business standpoint:
=B2+C2 (addition)
=B2-C2 (subtraction)
=B2/C2 (division)
=B2^2+C2^2 (the sum of both values squared)
Copying Formulas
Our original formula, =B2*C2, calculates quantity * price as total price. But it is likely that we sell more than one product and these could appear on separate lines. The magic of Excel is its ability to identify and repeat a pattern when copying a formula. Repeating the pattern while adjusting to changing circumstances is called a relative copy, and it is the default behavior of Excel. Here’s how it works: When the formula is copied from D2 to D3, Excel recognizes that the row has changed and that it probably makes more sense to dive into the formula and change the row numbers there as well. So the new formula in D3 becomes =B3*C3. We can even copy the pattern to multiple rows in one command. Using the little black square (fill handle) in the lower right corner of D2, we can pull and copy the formula down to as many rows as we like.
Copying the formula
Naming Cells
There are times that we would like one or more of referenced cells not to adjust during the copy. There are real fixed values in the world, such as a tax rate, that we would like to apply to all the totals equally. The best way to stop a fixed value from updating while copying is to give the cell containing that value a name, and then reference that cell by its name, not by its cell location. The name will not change when copied.
Copying a formula with a named cell
To prove that the name did not change we show the same spreadsheet below in formula view. You can toggle back and forth from spreadsheet to formula view at any time by pressing Control + ~ on your keyboard. (If you ever ask your professor for help with Excel, it is likely that Control + ~ will be the first place he/she goes to diagnose the problem).
Formula view using CTL ~
Naming a cell in Excel is simply a matter of making the cell active (by clicking on it) then typing a name in the name box that appears in the upper left. Finally, you must press the ENTER key to make the name stick. Whenever that cell is active (clicked on), the name will show in the name box. Note that B6, the cell that actually contains the tax rate, is the cell that we named, not the label that we placed in A6.
Naming a cell
Unfortunately, Excel gives no other indication that the cell is named. There is no thought bubble that floats above the cell with its name. However, Excel knows all the named cells in the workbook and can show you a list of them at any time in Formulas > Define Name. (Windows: Formulas > Name Manger). This is also the only place that you can remove a name in case you make a mistake in naming—which happens more often than you might imagine.
Define Name
Functions
In addition to formulas that the user constructs, like =B2*C2, Excel also provides built-in functions that can perform complex calculations that we might want to avoid writing ourselves for the sake of efficiency and accuracy. The equivalent function for our formula would be =PRODUCT(B2:C2), which means multiply the numbers from B2 to C2 together. All functions begin with a keyword, in this case PRODUCT, followed by open parentheses. What goes inside the parentheses are one or more arguments separated by commas. We wrote the PRODUCT function as a single argument that is the range of cells from B2 to C2. But we could also have written it as cells separated by commas—for example, =PRODUCT(B2,C2). Excel is very helpful and will tell you exactly which arguments the function is expecting and whether any of them are optional. Optional arguments usually appear at the end of the comma list and are enclosed in square brackets. For example, the PMT function, which calculates loan payments, looks like this:
=PMT(rate, nper, pv, [fv], [type])
where
rate is the interest rate;
nper is the number of payment periods;
pv is the amount of the loan today;
fv] is the optional future value of the loan; and
[type] is the optional indication of whether payments take place at the beginning or end of each payment period.
Function vs. Formula
When there are only a few data points and/or the calculation is simple, then a formula is easier and more intuitive. But what if the operation is to compute loan payments? Then the PMT function is clearly easier to manage than a complex calculation. Furthermore, functions are less prone to error since they have been debugged and tested by Microsoft. There are also things that a formula simply can’t do. For example, looking up values in a long list can only be done with a function. But it is really not a competition. Most good spreadsheets contain a combination of formulas and functions. Use whatever the cognitive task at hand demands to model the business situation.