How to Create a workbook in Excel ?

In this post, you will learn how to create and save a workbook in Excel.

Create a worksheet –

Start Excel and make sure you have an empty workbook displayed. To create a new, blank
workbook, press Ctrl+N (the shortcut key for File ➪ New ➪ Blank Workbook).

In this example, we will create a simple monthly sales projection table plus a chart that depicts the data.

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.

Here’s how to begin:

1. Select cell A1 (the upper-left cell in the worksheet) by using the navigation (arrow)
   keys, if necessary. 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 selection to a different cell or the pointer remains in cell A1.
3. Select cell 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 –

In this step, you enter the month names in column A.

1. Select cell 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 fill 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.

Your worksheet should resemble the one shown in Figure below –

Entering the sales data –

Next, you provide the sales projection numbers in column B. Assume that January’s sales
are projected to be $50,000 and that sales will increase by 3.5 percent in each subsequent
month.

1. Select cell 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 51750. 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, your worksheet should resemble the one shown in Figure below. Keep in mind
that, except for cell B2, the values in column B are calculated with formulas. To demon-
strate, 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 of these formulas
depend on the initial value in cell B2.

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 fill 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 that the range B2:B13 is selected, choose Home ➪ Number, and click
   the Decrease Decimal button. One of the decimal places disappears. Click that but-
   ton a second time, and the values are displayed with no decimal places.

Making your worksheet look a bit fancier –

At this point, you have a functional worksheet, but it could use some help in the appear-
ance department. Converting this range to an “official” (and attractive) Excel table is a
snap.

1. Activate any cell within the range A1:B13.
2. Choose Insert ➪ Tables ➪ Table. Excel displays the Create Table dialog box to
   make sure that it guessed the range properly.
3. Click OK to close the Create Table dialog box. Excel applies its default table for-
   matting and displays its Table Tools ➪ Design contextual tab.

Your worksheet should look like the Figure below.

If you don’t like the default table style, just select another one from the Table Tools ➪
Design ➪ Table Styles group. Notice that you can get a preview of different table styles by
moving your mouse over the Ribbon. When you find one you like, click it, and the style will
be applied to your table.

Summing the values –

The worksheet displays the monthly projected sales, but what about the total projected
sales for the year? Because this range is a table, it’s simple.

1. Activate any cell in the table.
2. Choose Table Tools ➪ Design ➪ Table Style Options ➪ Total Row. Excel automati-
   cally adds a new row to the bottom of your table, including a formula that calcu-
   lates the total of the Projected Sales column.
3. If you’d prefer to see a different summary formula (for example, average), click
   cell B14 and choose a different summary formula from the drop-down list.

Creating a chart –

Let’s create a chart that shows the projected sales for each month?

1. Activate any cell in the table.
2. Choose Insert ➪ Charts ➪ Recommended Charts. Excel displays some suggested
   chart type options.
3. In the Insert Chart dialog box, click the second recommended chart (a column
   chart), and click OK. Excel inserts the chart in the center of the window. To move
   the chart to another location, click its border and drag it.
4. Click the chart and choose a style using the Chart Tools ➪ Design ➪ Chart
   Styles options.

The below figure shows the worksheet with a column chart. Your chart may look different,
depending on the chart style you selected.

Saving your workbook –

Until now, everything that you’ve done has occurred in your computer’s memory. If the
power should fail, all may be lost—unless Excel’s AutoRecover feature happened to kick in.
It’s time to save your work to a file on your hard drive.

1. Click the Save button on the Quick Access toolbar. (This button looks like an
   old-fashioned floppy disk, popular in the previous century.) Because the work-
   book hasn’t been saved yet and still has its default name, Excel responds with
   a Backstage screen that lets you choose the location for the workbook file. The
   Backstage screen lets you save the file to an online location or to your local
   computer.
2. Click Browse. Excel displays the Save As dialog box.
3. In the File Name field, enter a name (such as Monthly Sales Projection). If
   you like, you can specify a different location.
4. Click Save or press Enter. Excel saves the workbook as a file. The workbook
   remains open so that you can work with it some more.