Read an Excerpt
CHAPTER 1
Creating a Simple Spreadsheet
Where Do I Start?
As we move forward in our journey of creating projections, it's apparent that everyone has different skill levels and abilities. So where do we start? We start at the very beginning; the basic skills. Some of what we cover may be redundant, but I've always found that there are always basic concepts to learn. I constantly review my basic books and am always amazed to discover new ways of making programming even easier. These solutions have always been in front of me, but it wasn't until further review of basic concepts that I discovered them.
To create a simple spreadsheet in Microsoft Excel, you enter text, numbers, and formulas. And that is what this chapter is about — the basics!
Parts of an Excel Spreadsheet
1. Active cell is the cell with the bold black outline.
2. Formula bar. Located above the work area (the cells) of the worksheet. It displays the contents of the active cell. It can also be used for entering or editing data and formulas.
3. Name box. Displays the active cell's address or the name of a cell. You can also enter a cell's (or range of cells) name.
4. The columns are named by letters
5. The rows are named by numbers.
6. The tabs at the bottom of a worksheet tell you the worksheet's name.
7. Tools are also grouped by tabs at the top of the worksheet (e.g., File, Home, Insert).
8. Within a tab are commands grouped by function (e.g., Clipboard).
9. On the ribbon there are the actual working commands such as Cut, Copy, and Paste.
10. The Quick Access Toolbar contains shortcuts to common tasks such as: Print, Copy, Paste, Save, etc. They are on the File tab Quick Access Toolbar.
Entering Data
A cell is a unit in which you enter text, numbers, and formulas. Cells are addressed by their column and row numbers like A1, A2, B1, or B2. You enter data in Excel simply by clicking on a cell and typing within it. Once you click in a cell, it becomes the active cell (fig 1.1). When you finish entering data in a cell, press Enter or one of the arrow keys to move to the next cell.
Entering Data Automatically
Instead of entering data one cell at a time, you can use the Autofill feature to fill cells with data based on information in other cells or data that follows a pattern. Use the fill handle to enter data automatically. For example, if your data starts with "Jan," you can drag your cursor from the lower left hand corner of that cell and a plus sign (+) will appear (fig 1.2).
Drag your cursor over the adjacent cells you want to automatically fill. Because this group includes patterned data, Excel will automatically fill in the following months. You can also do this with quarters (i.e., Q1, Q2, etc.) and numbers. However, with numbers you need to fill at least two cells (i.e., 1 in A1, 2 in A2) before you can Autofill the subsequent numbers.
Selecting Data
A range is a group of cells in a worksheet that have been selected or highlighted. When cells have been selected, they are surrounded by an outline or border. You can also select ranges, rows, columns, and collections. To select a range, click and drag over the desired block of cells (fig 1.3).
Once you have selected a range of cells, you can drag the border to move the selection, or drag the corner of the border to expand it.
In figure 1.3, cells A2 through C5 are selected. These are the cell references of the cells in the upper left and lower right corners of the range. We refer to this range as A2:C5. The two reference points are separated by a colon, which tells Excel to include all the cells between these start and end points.
To select a row, click the row number (fig 1.4).
To select a column, click the column letter, indicated by the arrow (fig 1.5). Figure 1.5 Click on the column letter to select the entire column.
To select a collection, hold the Ctrl key while you select the rows, columns, and ranges you want. Non-adjacent cell ranges are identified by separating the range of each cell block with commas like A2:B4, or D6:E8. The two cell blocks in the range are A2:B4 and D6:E8.
Finally, to select all the cells in a worksheet, click the Select All button, which is at the intersection of the row and column headers (fig 1.6).
How to Select All Cells in a Blank Worksheet:
* Open a blank Excel worksheet
* Press and hold down the Ctrl key on the keyboard
* Press and release the letter A without releasing the Ctrl key
* All cells in the worksheet should be selected.
Beware: If there is data on the worksheet, Ctrl + A may pick up only the immediate adjacent cells, so I do not recommend this method.
Editing Data
To modify data, double click the cell you want to change and edit the data that's there. When you finish making your changes, press Enter. To cancel the change before you press Enter, press ESC. You can also edit data by selecting a cell and clicking the contents in the edit line (fig 1.7). To undo your changes, choose Edit-Undo from the Quick Access Toolbar.
Copying or Moving Data
Using the Cut, Copy, and Paste commands, you can move or copy entire cells and their contents. You can also copy specific contents or attributes from the cells. For example, you can copy the resulting value of a formula without copying the formula itself, or you can copy only the formula.
* To move cells, click the Cut icon (scissors) on the standard toolbar, or press Ctrl + X.
* To copy cells, click Copy icon (two pages) on the standard toolbar, or press Ctrl + C.
* To insert copied information, click Paste icon (clipboard and a page) on the standard toolbar, or press Ctrl + V.
There is another way to move cells with only your mouse. Select the cell or range you want to move. Move the cursor to the edge of the cell until the cursor changes to a four-pointed arrow. Press the left mouse button, and drag the cell to the new location.
Formatting Cells
You can easily change the format of your data by using the quick format keys (fig 1.8).
You can also click in a cell and right click. Click on Format Cells from the pop-up menu. This brings up complete Format Cells dialog box.
You can format numbers (using percent, comma, date, currency formatting, etc.), align the text in a cell, change the font, choose colors of both the text and the background, create borders, and protect cells.
Resizing Columns and Rows
You can also adjust the column width and row height. To adjust the column to fit the widest entry, double click the border to the right of the column letter. (When you get to the border, a double horizontal arrow with a line through it appears.) To resize to a specific width, drag the border to the right to widen the column or to the left to make it narrower. Excel displays an indicator to show the number of characters the column will hold.
To change the height of a row, drag the bottom border down to make the row taller or drag it up to make it shorter. To adjust the row height to the tallest entry, double click the border below the row number. The second way to resize a row or column is to click on the column heading or the row number and then right click. This will bring up the appropriate menu (fig 1.9). With this menu, you can reformat any column or row you wish.
Calculating Tool
The Autosum feature adds all the data in a group of cells in a single step. Caution: Do not assume that the range the AutoSum selects is always correct. In fact, it is suggested that you NEVER use this feature.
To use the AutoSum button:
* Select the cell where you want the answer to appear;
* Click the AutoSum button located on the Home tab in the Editing group. The function will automatically select the closest range of data cells. The selected cells are surrounded by a flashing border (fig 1.10).
* Make sure you have selected the correct range of cells.
* If it is correct, press Enter.
In figure 1.10, the AutoSum button is circled in the upper right corner of the Home tab. The second circle is a screenshot of what happens when your cursor is in the cell where you want the sum and the AutoSum button is clicked.
The AutoSum function has a priority for suggesting a range to include. First, it looks to an uninterrupted group of cells containing data above the active cell. Then it looks for an uninterrupted group of cells containing data to the left of the active cell. If there are no cells adjacent to the active cell, it will put a null formula in – "SUM()" waiting for parameters.
Freezing Panes
You can freeze rows and columns to keep your headings in view while you scroll through the data. When you freeze rows or columns, the frozen cells stay in view even when you scroll through the sheet.
To keep both the first four rows and the first column in figure 1.11 visible, you would click in cell B5. Then go to the View tab, click freeze panes, and select Freeze Panes.
To unfreeze columns, select the freeze panes again and then click Unfreeze Panes (fig 1.12).
Annotating Cell Contents
Use cell comments to document individual cells of your spreadsheet without taking up valuable display space. Select the cell and right click. A pop-up menu will appear. Click on Insert Comment (fig 1.13).
You will see a box appear with the selected cell. Enter the text that you wish to include as a comment. In this case, we are using cell B2 (fig 1.14).
When a cell has a comment, you will see a red comment marker in the top right corner. If you want to edit the comment, right click the cell containing the comment, and click Edit Comment.
Naming and Adding Sheets
By default, each workbook in Excel contains three pages or worksheets — Sheet1, Sheet2, and Sheet3. Switching between worksheets is done by clicking on the sheet tab at the bottom of the screen. You can give your worksheet a more appropriate name in order to make your workbook more readable, to help document the contents of the sheet, and to make it easier to find if you have several sheets in your workbook. To change the name of the tab, double click on the sheet and type the name you want, or right click on the sheet tab to choose Rename (fig 1.15).
You can easily add sheets, name sheets, and customize the way the sheets look in your workbook. To insert a new worksheet at the end of the existing worksheets, click the Insert Worksheet tab at the bottom of the screen (fig 1.16).
You can also right click the sheet tab after the one you want to insert click insert and select worksheet. A new worksheet tab will appear before the tab you right clicked.
You can easily change the look of your sheets. For example, you can:
* Change the default colors in the cells.
* Add lines around a selected cell by adding a border.
* Turn off the grid display.
* Change the default font and colors used for data.
* Automatically display negative values in red.
That's a review of the basic layout and frequently used commands in building meaningful spreadsheets. Likely, you already knew much of what we just covered, but there were probably one or two points that you had either forgotten or had never used before. The more these foundational elements become second nature to you, the better you'll be able to build on them as we move forward.
The fact is that we've barely scratched the surface — there's plenty more to learn as we move into the next chapter.
CHAPTER 2
More Power with Functions and Formulas
Learning Microsoft Excel is really about learning to write your own formulas and using Excel functions to save a lot of programming time. Functions are the real value of Excel. Used properly, they will allow you to create spreadsheets in record time. But functions and formulas will fall short if you don't know the difference between absolute and relative referencing.
Think of using formulas like addressing a letter. You can send something to 1 Main Street, but without a city and state or a zip code, the mail may never get to the intended recipient. So, like "1 Main Street", cell A1 could be on Sheet1, Sheet2, or Sheet3. It could even be in an entirely different workbook. Getting in good habits right from the beginning will help you avoid many problems that may evolve in the future.
Writing Your Own Formulas
You can write your own formulas when you need a specialized calculation not found in the functions list. When you press Enter, Excel calculates the formula and puts the result in the cell.
Formulas use these standard symbols for operators:
* (asterisk) for multiplication
+ (plus sign) for addition
- (hyphen) for subtraction
/ (forward slash) for division
^ (caret) for exponentiation (e.g., 3)
You can create a simple formula to add, subtract, multiply, or divide values in your worksheet. Simple formulas always start with an equal sign (=), followed byconstants that are numeric values and calculation operators such as plus (+), minus (-), asterisk (*), or forward slash (/) signs.
For example, enter the formula: =5+2*5 in cell C1. Excel multiplies the last two numbers and adds the first number to the result. Following the standard order of mathematical operations, multiplication is performed before addition.
* On the worksheet, click the cell in which you want to enter the formula.
* Type the equal sign followed by the constants and operators that you want to use in the calculation.
* You can enter as many constants and operators in a formula as you need — up to 8,192 characters.
Use Cell References in a Formula
While the formula in the previous step works, it has one drawback. If you want to change the data being calculated you need to edit or rewrite the formula. A better way would be to write formulas so that you can change the data without having to change the formulas themselves. This is the key to everything else in this book.
To do this, you need to tell Excel what cells contain the data. A cell's location in the spreadsheet is referred to as its cell reference. To find a cell reference, simply look at the column headings to find which column the cell is in and across to find which row it is in.
The cell reference is a combination of the column letter and row number, such as A1 or B3. Think Apples, Apples, Apples! When writing cell references, the column letter always comes first.
Instead of writing this formula in cell C1: = 5 + 2*5. Put a value of 5 in cell A1 and a value of 2 in cell B1. Then enter this instead: = A1+B1*A1. You can refer to a single cell, a range of cells, a location in another worksheet, or a location in another workbook (fig 2.1).
Figure 2.1 Entering a formula
* As you enter the formula, the first cell reference is A1, the color is blue, and the cell range has a blue border with square corners.
* The second cell reference is B1, the color is green, and the cell range has a green border with square corners.
* Note the second reference to A1 is also in blue. If the third reference were to a different cell, it would be in purple and so on.
* Finally, once you put in the equal sign, you can click on cells, and Excel will type in the reference for you.
Copying Formulas and Functions
Once you put a function or formula in one cell, you can easily copy it to other cells to perform the same calculation on those cells. Select the cell with the formula, choose Copy and then highlight the desired cells, and choose Paste. Excel copies the formula for each row.
Note: You can also copy formulas into adjacent cells by using the fill handle. After verifying that the cell references in the formula are working, select the cell that contains the formula to be copied, and then drag the fill handle over the range that you want to fill.
Absolute versus Relative References
In working with spreadsheets, you need to know about relative versus absolute cell references. Absolute and relative references influence the way you copy formulas from one cell to the next.
(Continues…)
Excerpted from "Financial Forecasting in Microsoft Excel"
by .
Copyright © 2019 Jeffrey Kenneth Prager.
Excerpted by permission of National Association of Home Builders.
All rights reserved. No part of this excerpt may be reproduced or reprinted without permission in writing from the publisher.
Excerpts are provided by Dial-A-Book Inc. solely for the personal use of visitors to this web site.
