First Excel Workbook
you may want to follow along on your computer to get a feel for how this software works.
In this example, you create a simple monthly sales projection table, plus a chart that
depicts the data.
Getting started on your worksheet
Start Excel and make sure that you have an empty workbook displayed. To create a new,blank workbook, press Ctrl+N (the shortcut key for File ➪ New ➪ Blank Workbook).
The sales projection will consist of two columns of information. Column A will contain the
month names, and column B will store the projected sales numbers. You start by entering
some descriptive titles into the worksheet.
1. Move the cell pointer to cell A1 (the upper-left cell in the worksheet) by using
the navigation (arrow) keys. The Name box displays the cell’s address.
2. Type Month into cell A1 and press Enter. Depending on your setup, either Excel
moves the cell pointer to a different cell or the pointer remains in cell A1.
3. Move the cell pointer to B1, type Projected Sales, and press Enter. The text
extends beyond the cell width, but don’t worry about that for now.
Filling in the month names
1. Move the cell pointer to A2 and type Jan (an abbreviation for January). At this
point, you can enter the other month name abbreviations manually or you can let
Excel do some of the work by taking advantage of the AutoFill feature.
2. Make sure that cell A2 is selected. Notice that the active cell is displayed with a
heavy outline. At the bottom-right corner of the outline, you’ll see a small square
known as the fill handle. Move your mouse pointer over the fi ll handle, click, and
drag down until you’ve highlighted from cell A2 down to cell A13.
3. Release the mouse button, and Excel automatically fills in the month names.
Entering the sales data
Next, you provide the sales projection numbers in column B. Assume that January’s sales
are projected to be Rs.50,000 and that sales will increase by 3.5 percent in each subsequent
month.
1. Move the cell pointer to B2 and type 50000, the projected sales for January. You
could type a dollar sign and comma to make the number more legible, but you do
the number formatting a bit later.
2. To enter a formula to calculate the projected sales for February, move to cell B3
and type the following:
=B2*103.5%
When you press Enter, the cell displays 71750. The formula returns the contents
of cell B2, multiplied by 103.5%. In other words, February sales are projected to be
103.5% of the January sales — a 3.5% increase.
3. The projected sales for subsequent months use a similar formula, but rather
than retype the formula for each cell in column B, take advantage of the
AutoFill feature. Make sure that cell B3 is selected. Click the cell’s fill handle, drag
down to cell B13, and release the mouse button.
At this point, Keep in mind that, except for cell B2, the values in column B are calculated with formulas. To demonstrate, try changing the projected sales value for the initial month, January (in cell B2). You’ll find that the formulas recalculate and return different values. All these formulas
depend on the initial value in cell B2, though.
Formatting the numbers
The values in the worksheet are difficult to read because they aren’t formatted. In this
step, you apply a number format to make the numbers easier to read and more consistent in
appearance:
1. Select the numbers by clicking cell B2 and dragging down to cell B13. Don’t drag
the fi ll handle this time, though, because you’re selecting cells, not filling a range.
2. Access the Ribbon and choose Home. In the Number group, click the drop-down
Number Format control (it initially displays General), and select Currency from the
list. The numbers now display with a currency symbol and two decimal places. That’s
much better, but the decimal places aren’t necessary for this type of projection.
3. Make sure the range B2:B13 is selected, choose Home ➪ Number, and click the
Decrease Decimal button. One of the decimal places disappears. Click that button
a second time, and the values are displayed with no decimal places.
No comments:
Post a Comment