Microsoft Excel is the single most useful software tool on any businessperson’s hard drive. It is the multitool of computer applications. Besides it’s primary function of performing calculations in an organized manor, it is sometimes the only program available to create charts and graphics, perform statistical or numerical analysis, or manipulate large tables of data. It is capable of all these functions, if not as efficient as specialized software. Once, I even worked with an engineer who typed all of his business letters in Excel (not recommended).
While the discussion below is targeted at Excel, most of the information applies to any modern spreadsheet software (such as Open Office Calc, Gnumeric, etc.). Likewise, although all of my screen shots come from Excel 2003 , Excel’s basic functionality hasn’t changed in two decades or so.
The basic Excel file is known as a book. Each book is made up of one or more sheets (1), each of which is a grid of cells contained in rows (2) and columns (3). Rows are referenced by numbers; columns by letters. A particular cell is called by a letter and number. Thus, the cell in column C and row 3 is called C3 (4). A range of cells is referenced using a colon. Thus, I7:L12 refers to the rectangle of cells that has cell I7 as it’s top left corner and L12 as it’s bottom right corner (5).
To reference a cell in a different sheet, use an exclamation point. For example, Sheet2!C7 refers to cell C7 in Sheet 2. Generally, your books will be easier to debug if you make each sheet as self contained as possible, with only a few references between sheets.
Any given cell can contain one of three things: a number, a text string, or a formula. For instance, a sheet for an income statement might contain text cells for the names of accounts, number cells for the value of the accounts, and formula cells to sum expenses and to subtract total expenses from total income to get net income. The example below shows the formulas used:
Notice how all formulas start with an equals sign (“=”). This is how Excel knows that that cell is a formula, and not text. In the old days, text cells had to start with a single quote (“‘”), so the spreadsheet would know they weren’t a formula. Modern spreadsheet programs are pretty good at figuring out what is supposed to be text, so the “‘” is usually left off. It can still be added, though, to force a given cell to be treated as text.
Cells can be formatted in various ways, to control the font, color, number display type, etc. In the example, bold face, underlining and centering are used to make the income statement more readable. The “comma” and “currency” number types are used to format the account values with commas and dollar signs.
The formula =sum(C11:C14) in the example contains a function. We could have manually entered a formula to sum all the cells, but it is much easier to call a function to do it for us. Excel comes with hundreds of built in functions. If you need a function that isn’t included, you can write your own–a process that is easier than it sounds.
One of the neat things about Excel functions is that they work pretty much exactly like mathematical functions…because they’re the same thing. Thus, all of the rules for the algebra, composition, and transformation of mathematical functions also apply to excel functions. Knowing this can really come in handy when you’re using Excel for modeling or simulation.
Most of the time when you need an excel function you find it by searching the help file. Some functions are so useful, though, that you should probably learn them by heart.
- average(range) – Returns the mean of a range of cells
- counta(range) – Counts the number of non-empty cells in a range
- if(expr1, expr2, expr3) – If expr1 is true returns expr2, otherwise returns expr3
- irr(range) – Returns the internal rate of return of a series of cash flows contained in range
- max(range) – Returns the highest value from a range of cells
- min(range) – Returns the lowest value from a range of cells
- npv(rate, range) – Returns the net present value, at interest rate rate, of a series of cash flows contained in range
- rand() – Returns a uniformly distributed number between 0 and 1
- round(value, digits) – Returns value rounded to digits digits
- stddev(range) – Returns the standard deviation from a range of cells.
- sum(range) – Returns the sum of a range of cells
One of the things spreadsheet programs do very well is answering “what if?” questions. For instance, let’s say that Hypothetical Inc. (from the example above) wants to find out how much they need to decrease their expenditure on office candy to make a net profit of $30,000 (ceteris parabus). They could take the income statement above and enter values for Office Candy Expense in cell C14 until the net income in cell D18 came out to be $30,000. This example is so trivial that they would probably come up with the right value ($10,000) on the first guess.
The same sort of question on a more complicated spreadsheet (for instance, with compound functions and non-linear relations) would take much longer by trial and error. Luckily, Excel comes with a tool called Goal Seek, which is accessed from the Tools menu. To use goal seek, just tell it which cell you want set to which value, and which cell to change.
A similar–but much more sophisticated–tool is the Solver. Solver lets you change the values of more than one cell at once, subject to a list of constraints. Solver is a powerful tool, but it does have limitations. Usually, it works better when you have “guess values” in the cells which are already fairly close to the real answers. Otherwise, the solver solution either does not converge, or takes forever to run.
A final tool that deserves mention is the Scenario Manager. Often when performing this sort of analysis you want to look at multiple scenarios (best case, worst case, most likely, etc.). You could change the numbers in your spreadsheet for each scenario and do a “save as”, but that approach would get messy quickly. Scenario manager lets you save snapshots of your sheet with different assumptions which you can cycle through or merge together.
The charts produced by Excel are neither as attractive nor as flexible as those produced by specialized charting software. Nonetheless, it is probably the most widely used software to make simple charts for reports and presentations (after all, who wants to buy and learn a whole other program?). You should become absolutely familiar with the process of creating simple line, bar, pie, and scatter graphs in Excel. Luckily, this is usually just a matter of selecting the range that contains your data, then going through a graphical wizard which is accessed on the Insert menu.
Most of the time, you will want to stay away from using the “fancy” 3-D charts. They are harder to read and look gimmicky.