Ozgrid, Experts in Microsoft Excel Spreadsheets




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



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.

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.

Let’s give our numbers a dollar value now.

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:

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.

Now let’s put some borders on our data.

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


Instant Download and Money Back Guarantee on Most Software


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

Some of our more popular products are below...
Convert Excel Spreadsheets To Webpages | Trading In Excel | Construction Estimators | Finance Templates & Add-ins Bundle | Code-VBA | Smart-VBA | Print-VBA | Excel Data Manipulation & Analysis | Convert MS Office Applications To...... | Analyzer Excel | Downloader Excel | MSSQL Migration Toolkit | Monte Carlo Add-in | Excel Costing Templates