A basic Excel 2003 tutorial

Excel is Microsoft's spreadsheet program that is included in Microsoft Office. A spreadsheet is a sheet made up of cells that can contain data such as numbers, text and dates. A spreadsheet is the digital equivalent of a ledger sheet, only spreadsheets are much more flexible and can perform many more tasks than a traditional ledger sheet.

Spreadsheet programs are used to administer data. Excel is often used in the financial administration of small businesses or private persons. Excel can be used to calculate costs, earnings and profits based on the data present in teh spreadsheet. This Excel tutorial offers an introduction into Microsoft Excel. The examples in this tutorial are taken from Excel 2003, but they apply to other versions of Excel as well. In this basic Excel 2003 tutorial we will show you the basics of Excel. In order to accomplish that goal we create a small cost administration for a bakery. The example we build can be easily changed to hold your own monthly, quartelry and annual expenses.

This Excel 2003 tutorial is accompanied by an example XLS file: excel_2003_tutorial_english.xls (right click > save target as... to download)

Excel spreadsheets

Excel is used to organize numbers and text into a spreadsheet. Each cell in the spreadsheet is identified by a row number and a column letter. When you start Excel a new spreadsheet is automatically opened and the cell A1 is selected by default. (Microsfot uses the name worksheet instead of spreadsheet). In Excel 2003 (and other versions) there are a number of ways to select cells using your mouse. Being able to select multiple cells at once will be helpful later in this Excel tutorial.

• Select an individual cell by clicking it with the mouse pointer.
• Select multiple adjacent cells at once by holding down the left mouse button and dragging the mouse over the cells you want to select.
• By holding down the CTRL key you can select multiple non-adjacent cells by clicking them with the mouse pointer.
• By clicking a row number all the cells in that row are selected.
• By clicking a column letter all the cells in that column are selected..

Entering data

You can enter data into a cell by selecting it and typing something. Each cell can contain any string of characters. The cost administration that we're building in this tutorial will contain products and the monthly expenditures for those products. In the sheet displayed below you see the cost administration for a number of products that a baker made in the first quarter. All data in this sheet were entered manually, except for the monthly and quarterly totals. They were generated by an Excel function. In this Excel tutorial you will learn how to apply Excel functions to groups of cells.

1. Open Excel
2. Copy column A from the example above (cells A2 - A8). You can display 'Monthly Total' in bold by selecting the cell and pressing the key combination CTRL + B.
3. Copy row 1 from the example above (cells B1 - E1)
4. Copy the numbers from the bright blue colums, the monthly cost per product.

Note: when you enter numbers into cell, Excel will display as many decimals as needed. For example, 375 is displayed without decimals and 3.20 is displayed as 3.2. This behaviour can be adjusted.

1. Select the fields B2 - D7 by dragging the mouse over them while holding the left mouse button. Right-click the selected cells and from the menu that appears select 'Format Cells'.
2. In the Format Cells menu choose 'number' in the list on the left. The Decimal Places field allows you to specify the number of decimal places. Since the fields will contain amounts of money set 2 decimal places.
3. You can set a background color to cells under the 'patterns' tab from the Format Cells menu.
4. Press OK to apply the new cell format

Excel formulas

The monthly and quarterly totals in the spreadsheet are not entered manually. They are generated by an excel formula that sums the amounts in the bright blue cells. In Excel this summation is denoted as =SUM(B2:B7). You can enter this Excel formula into the formula bar at the top of the screen. In this Excel tutorial I type formulas in the formula bar, but they can be entered directly into a cell as well. Excel will know you are enterting a formula when you start with the = sign.

1. Select cell B8 and type =SUM(B2:B7) in the formula bar. On pressing Enter the sum of cell B2 untill B7 will appear in cell B8.

Having generated the total cost for January we now want to do the same for Februari and March. You could repeat the same steps for February and March, but there is an easier way.

1. Select field B8
2. Press the key cobination CTRL +C (or choose Edit > Copy)
3. Select cells C8 and D8
4. Press the key combination CTRL + V (or choose Edit > Paste)

Excel doesn't copy the number value (755.55) from cell B8, it copies the formula behind that cell and adjusts it to the current column letter. Excel 2003 automatically guesses you want to apply the same summation to the other colums. Check the formula bar to see how Excel adjusted the formula for the monthly total for each month.

• Repeat this operation, but than for the Flour, Salt, Yeast, etc, rows. The total amount spent on flour in the first quarter will appear in cell E2 if you enter =SUM(B2:D2) in (the formula bar for) that cell.

The total cost for the first quarter is displayed in the bottom right cell with the blue border. The total quarterly cost figure can be calculated by summing either cell E2 untill E7 or cell B7 untill D7.

• Enter either =SUM(E2:E7) or =SUM(B8:D8) into cell E8 to obtain the quarterly cost total.

Formatting cells

The spreadsheet you've built so far looks a bit dull. Let's add some colors to make it more attractive and easier to read. (This tutorial focuses on the basics of Excel. If you want to read more one formatting cells, visit functionx.com)

1. Select the cells that you want to colorize
2. Click the small paint bucket in the top right corner of the screen. You can choose a color from the color menu that appears.

Excel fill color menu Excel border menu

The fill color menu is used to apply a background color to a cell. Behind the 'A' icon there is a font properties menu that allows you to set a font type. The border menu allows you to set cell border properties.

Extending the spreadsheet

Bakeries don't stop administering cost after the first quarter. Excel provides some features that let you easily extend the current spreadsheet to hold, for example, a new month. The next step in this Excel tutorial is to extend the spreadsheet with a new month, April.

• Right-click the column header of column E. In the menu that appears, choose 'insert'.

A new column appears between column D and E. The new column is called 'E' and the column that was previously named E is now column F. Excel assumes that you want to change the formulas used to hold the newly added column. Enter an amount into the E column and you will see the amount is added to the quarterly total.

• Enter 'April' into field E1 if you had not done so yet
• Create the formula voor field E8
  1. Select field E8
  2. Press the key combination CTRL + C (or choose edit > copy)
  3. Select field E8
  4. Press the key combination CTRL + V (or choose edit > paste)

Finally, we change the Excel formula for field F8, holding the total quarterly cost.

• Select field F8
• Change the formula from =SUM(B8:D8) to =SUM(B8:E8)
• Fill the April column with some values

Excel formulas

I so far discussed only one Excel function, namely the SUM function. This is the only function we will use in the spreadsheet we're building. You can however use many other types of functions in Excel. The table below summarizes the most used Excel functions. The examples listed in the 'how to use' column can be entered into the speadsheet you created in this Excel tutorial.

Excel formula	How to use
IF	Using IF you can create a conditional function. Example: =IF(B8 > 100, "You spent to much money!", "You remained within budget") If cell B8 contains a value greater than 100, than "You spent to much money!" is displayed in the cell. If B8 contains a value smaller than 100, than Excel displays "You remained within budget".
AVERAGE	AVERAGE calculates the average value of a group of numeric field. Example: =AVERAGE(B8:E8) This will display the average cost per month.
AND	AND allows you to combine conditions. AND is a logical operator that van be combined with IF. Example: =IF(AND(B8>100,C8>100), "both numbers greater than 100", "At least one number is smaller than 100"). The formula above says: if cell B* and cell C8 both contain a number greater than 100, display "both numbers greater than 100". If not both fields are greater than 100 (that is: if at least one field is smaller than 100), than display "At least one number is smaller than 100". Try it out in the spreadsheet you created in this Excel course!
OR	OR allows you to test if at least 1 of two conditions is true. Example: =IF(OR(B8>100,C8>100), "At least 1 value is greater than 100", "No cell is greater than 1000"). If at least 1 of the conditions is met, than "At least 1 value is greater than 100" is displayed, otherwise "No cell is greater than 1000". Try it out in the spreadsheet you created in this Excel course!
COUNT	Counts the number of cells in a group. For example =COUNT(B:B8) yields 7.

This Excel tutorial discusses only some basic Excel functions. Many more Excel formulas can be found at microsoft.com on their worksheet functions page.

Excel charts

In the final part of this Excel tutorial I will show you how to create a chart based on the cost administration we created. Excel allows you to generate a wide range of charts based on input data. In this course I will show you how to create a simple chart. You are of course welcome to experiment further.

1. Select the fields A1 untill E7.
2. Click the Chart Wizard icon on the taskbar
3. The chart wizard will appear. In the chart type list on the left choost 'line'. On the right hand side choose a subtype of the line chart.
4. Click Next. You will now see an example chart and you are offered the possibility to specify which data should be displayed in the chart. The right data is already in the chart, because you selected it before starting the chart wizard.
   On the bottom of the window choose 'rows' in order to display time (months) on the horizontal X-axis.
5. Click Finish

In this basic Excel tutorial you learned some of Excel's possibilities and functions, but a lot more is possible with Excel. Many small businesses use Excel tp organize their financial and other administrations. Excel can be used to create advanced applications by using a program language called Visual Basic for Applications (VBA).

Visual Basic for Applications (VBA) is an implementation of Microsoft's Visual Basic, an event driven programming language and associated development environment which is built into most Microsoft Office applications.
Source: Wikipedia

There are many online and offline Excel tutorials that can teach you more advanced functions of Excel or more specifically using VBA with Excel. Interested readers may find the Excel tutorials linked below useful.

More Excel tutorials

FunctionX Excelexamples - some interesting examples of Excel applications for those that are new to Excel and VBA.

North Carolina State University / Labwrite Excel graphing tutorial - An Excel tutorial that teaches how to create advanced charts.

Mistupid Excel Howto's - A nice collection of Excel video tutorials that show you how to perform some common tasks that will make you work easier and faster with Excel.

Excel XP tutorial - A very extensive Excel XP tutorial aimed at beginners.

1. 1