EXCEL VIDEO TUTORIALS / EXCEL DASHBOARD REPORTS

CREATING A BASIC EXCEL SPREADSHEET

FREE EXCEL HELP

Lesson 34 - Creating a Basic Excel Spreadsheet. Level 1 Free Training

<< PREVIOUS LESSON |NEXT LESSON>> | BACK TO EXCEL LEVEL 1 TRAINING INDEX

EXCEL BASIC SPREADSHEET

Let�s look at creating and formatting a basic spreadsheet to chart the income of the various Departments within a small organization.

Create a new blank Workbook.

Click on cell A2.

Type in the word Department.

Click in cell A3 and type in Administration.

Click in cell A4 and type in Marketing.

Click in cell A5 and type in Finance.

Click in cell A6 and type in Stores.

Click in cell A7 and type in Total. Notice here that the contents of the cells A2 and A3 spill over into cells B2 and B3. This is because cells B2 and B3 are empty. Once these adjacent cells have data in them, the data in cells A2 and A3 will appear to be cut off, although this is not really so. If you were to have a formula result that is too wide for a column, the cell would display ####. This is telling you not that your formula is incorrect, just that your column is too narrow for your result to be seen.

Click in cell B2 and type in the month January.

Click back in cell B2 and using your Fill handle, fill to the right until you reach December (cell M2).

Click in cell B3 and type in 1000.

Click in cell B4 and type 1500.

Click in cell B5 and type 1750.

Click in cell B6 and type in 2000.

Click in cell C3 and type in 1750.

Click in cell C4 and type 1600.

Click in cell C5 and type 2300.

Click in cell C6 and type in 1900.

Now highlight the range B3:C6 and using your Fill handle, fill right until you reach December (column M).

Let�s put in the totals in for the months now, by clicking in cell B7, selecting the AutoSum icon (the backward Z on your standard toolbar) , then Enter.

Click back on cell B7 and using your Fill handle , fill the AutoSum formula to your right until you reach column M (December).

You will notice now that the Department names in cells A2 and A3 appear to be cut off, and the month September in J2 also seems to be cut off. If you click in either of these cells, you will note that the whole cell entry is still there as you can see it in your formula bar. Let�s have a look at quickly best-fitting the width of these columns, so we can see their whole contents.

There are a few different ways to best fit a column. You can go via the Format>Column option on your Worksheet Menu Bar, following this method you can see the options available to you to widen or shorten your columns, or you can double click between the column references on your Worksheet. Let�s try this as this is by far the easiest and quickest way of best-fitting a column.

Wave your mouse on the cell border between the A and B column heading (black writing, grey background) until your mouse changes to a black cross with a left and right pointing arrow.
Double click with your left mouse button.
Now wave your mouse pointer on the cell border between J and K until your mouse changes to a black cross with a left and right pointing arrow.
Double click with your left mouse button.

Pretty simple hey!!! If you wanted to stretch or shorten your columns, you could wave your mouse over the desired the column borders until it changes to a black cross, then holding your left mouse button down, drag either left or right to widen or shorten your column.

The same technique will work for widening rows, except that when you wave your mouse over the row border, the black arrow with the left and right pointing arrows will change to a black arrow with an up and down pointing arrow.

Formatting the Spreadsheet

Let�s pretty up our Worksheet just a little now by using some basic formatting techniques. We will not go into this too deeply, as we stated earlier in the course, we believe it is far more important to get the �guts� of the spreadsheet correct, rather than having a pretty Worksheet that does not come up with the goods, so to speak!We will use some of the options on the Formatting toolbar in this case. Note that all of these options are available via the Format option on the Worksheet Menu Bar.

Highlight the range A2:M2.
Click the on the B on the Formatting Toolbar. This is your bold key and will bold our highlighted text.
Although we have stated before that it is easier to work with text and numbers that are aligned correctly (that is numbers to the right and text to the left of a cell), if you were required to centre them for �looks� sake, you can do so easily.
Keep the range A2:M2 selected and select the Center icon on your Formatting toolbar. This will center the contents of your cell. The Center icon is normally four icons to the right of the Bold icon. Remember to wave your mouse over an icon to see what action it will perform if selected.
Let us now select the range A7:M7 and bold the totals of the months by selecting the B on the Formatting Toolbar.
Now highlight the range A3:A6 and select the I on the Formatting toolbar. This is your �italics� key and will italic the Department names for us.

Let�s give our numbers a dollar value now.

Highlight the range B3:M7.
Select the $ icon from your Formatting Toolbar.

You will notice that column L is full up with #####. Best fit this column using the technique described above.

When you give a range a dollar value within Excel, it, as a default, will place two decimal places within your cell. To remove them do the following:

Select the range B3:M7.
Click on the Decrease Decimal Spaces icon on your Formatting toolbar. This is the icon with the . 00 and . 0 and a blue right pointing arrow on it. If you click it once, you will remove one decimal place, and if you click it again, you will remove the next decimal place.
Let us now put a heading on our table. Click in cell A1.
Type in CY2004 INCOME and click Enter.
Now click back in cell A1.
Highlight the range A1:M1.
Now select the Merge and Center icon on your Formatting toolbar. This will merge and center the words CY2004 INCOME across the cells that you highlighted.

Note here that it is NOT a good idea to use Merge and Center on anything that you may wish to use in a calculation. My advice is to use it to format a heading only. You will run into all sorts of problems if you try to do calculations with a merged cell(s).

Let us now bold our heading and change the font size and cell alignment.

Click in cell A1 (note here that your range A1:M1 has now all become cell A1 as indicated by the Name Box.
Bold your heading using the toolbar icon.
Now change the font to a font of your choice by selecting the appropriate font name from the Font drop down box to the left of the bold icon on your Formatting Toolbar.
Once you have selected a font, change it�s size to 26, by selecting this number from the Font size box (located immediately to the left of the bold icon on your Formatting Toolbar, and to the right of the Font Name Box).

Now let�s put some borders on our data.

Highlight your whole table.
Select the drop down arrow to the right of the Borders icon on the Formatting Toolbar. As a default, the borders icon is the third from the left on your Formatting Toolbar.
Select the icon named All Borders with your left mouse button.

If you wished to use different types and widths of lines for your borders, or change the colors of them, you will need to do this via the Format>Cells/Borders option which has a much wider range for you to choose from

Go To Free Excel Training Lesson 35 . Back to Previous Lesson

Go to Excel Basic/Level 1 Training Index

<< PREVIOUS LESSON |NEXT LESSON>> | BACK TO EXCEL LEVEL 1 TRAINING INDEX

Instant Download and Money Back Guarantee on Most Software

EXCEL VIDEO TUTORIALS / EXCEL DASHBOARD REPORTS

Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!

Microsoft � and Microsoft Excel � are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft