WS O11 SAINS INFORMATIK
MICROSOFT EXCEL PROJECT : CREATING ORDER FORM
OPENING EXCEL APPLICATION
1. Launch Microsoft Excel. Click Start >
Programs > Microsoft Excel.
2. To start a new worksheet, Click New from
the Menu bar.
3. A new untitled spread sheet will be
created for you.
RENAMING WORKSHEET
1. Double click Sheet 1 and rename it as
ORDER FORM.
2. Then, rename sheet 2 as ITEM PRICE.
3. You can also right click the sheet 1, then
choose “Rename” from the pop up menu.
WS O11 SAINS INFORMATIK
WORKING WITH THE WORKSHEET:
CREATING “ORDER FORM” SHEET
1. Select the cell you want to merge (Click
and drag cell A1 till cell E1. Click merge
and centre on the formatting toolbar). Type
ORDER FORM. To split any merged cell
just deactivate “Merge and Centre” again.
2. Create a table like one you see in the
screen in Figure 3.
WORKING WITH THE WORKSHEET:
CREATING “ITEM PRICE” TABLE
1. Now, we need to create an ITEM
PRICE list table. Click Sheet 2 to
open it.
2. Create a table like the one you see in
Figure 4.
Figure 3
Figure 4
WS O11 SAINS INFORMATIK
FORMATTING CELL: CATEGORIZING THE
CELLS AS TEXT
1. Select all cells under CODE column
in ORDER FORM sheet.
2. Right click and select Format Cells
from the pop up menu.
3. Choose Text then click OK.
WS O11 SAINS INFORMATIK
FORMATTING CELL: CATEGORIZING CELLS
AS CURRENCY
1. Click and drag all cells under PRICE
column to select it.
2. Right Click and choose Format cells
from the pop up menu.
3. Under Category, choose Currency
and click OK.
4. Repeat the same step for PRICE and
TOTAL column in ORDER FORM
sheet.
USING VLOOKUP FUNCTION
1. Open worksheet named “ORDER
FORM”. Click cell B4 to select it.
Click Insert > Function.
2. In the “Insert Function” windows,
select “Look up & Reference”
3. Get the VLOOKUP function in the
insert function window.
WS O11 SAINS INFORMATIK
USING VLOOKUP FUNCTION: SETTING UP
VLOOKUP FUNCTION ARGUMENTS.
1. In the Lookup_value field, click the
cell where the value is reffered to.
For this Order Form, type A4 then
press F4 on your keyboard
2. In the Table_array field, click ‘ITEM
PRICE’ worksheet to open the Price
List table.
3. Click and drag entire ITEM table to
select it. Press F4 on your keyboard.
Excel add following argument:
‘ITEM PRICE’!$A$1:$C$21’
4. In the Col_index_num, type number
2 as we want Excel to automatically
insert any value from the second
column in the ITEM PRICE table.
5. Press OK.
WS O11 SAINS INFORMATIK
USING VLOOKUP FUNCTION: SETTING UP
FUNCTION ARGUMENTS FOR ITEM “PRICE”
1. Click cell C4, under PRICE column,
insert VLOOKUP function. In the
Lookup_value field, type A4. Then
press F4 on your keyboard
2. We need to identify the table where
the value is referred to. Click the
ITEM PRICE worksheet. Select
entire table to select it. Press F4 on
your keyboard again. Excel will add
the following function argument:
‘ITEM PRICE’!$A$1:$C$21’
3. In the Col_index_num, type number
3 as Excel will automatically add
values from the third column of the
ITEM PRICE table.
4. Press OK to continue
USING PRODUCT FUNCTION: GETTING
PRODUCT FUNCTION
1. Click cell E4 to select it.
2. Click Insert > Function
3. Select a function called PRODUCT
under category Math & Trig.
4. Click OK to continue
USING PRODUCT FUNCTION: SETTING UP
PRODUCT FUNCTION ARGUMENTS
1. In the Number 1 field, click cell C4
under Quantity column. Press F4 on
your keyboard.
2. In the Number 2 field, click cell D4
and press F4 on your keyboard
again. Excel add a string ( $ ) sign to
the cell address.
3. Click OK to continue.
WS O11 SAINS INFORMATIK
DUPLICATING A FUNCTION
1. We can copy a function from any cell
to other cell. To duplicate a function,
Click a cell containing an Excel
function.
2. Click and drag the small black square
to the last cell in the column.
3. Repeat step 1 and 2 for all column.
Click this small box
Drag the black box to
cell B23
Duplicate all function
WS O11 SAINS INFORMATIK
USING SUM FUNCTION
1. Click cell E24 to select it. This is the
total amount of the purchase.
2. Click Insert > Function. Select SUM
from the function list.
3. Click OK to continue.
USING SUM FUNCTION: SETTING UP SUM
FUNCTION ARGUMENTS
1. In the Number1 field, click the first
cell (E4) in the TOTAL column and
drag it to the last cell (E23)
2. Excel add the following function in
the function:
=SUM(E4:E23)
3. Press OK when you finish.
WS O11 SAINS INFORMATIK
USING IF FUNCTION: SETTING A TOTAL
PRICE AFTER DISCOUNT
A 20% discount will be given to any purchase
that is greater than RM200. To do that,
1. Click cell E26 to select it.
2. Click Insert > Function. Choose IF
from the function list.
3. Click OK to continue.
USING IF FUNCTION: SETTING UP IF
FUNCTION ARGUMENT
1. In the Logical_test field, type:
E24 > 200
“Excel check the value in cell E24.
For instant, the value in cell E24
must be greater than RM 200”
2. In the Value_if_true field, type
E24*0.8
“If the total amount is greater than
RM 200, the value is multiply with
0.8”
3. In the Value_if_false field, just
multiply the E24 with 1,
E6*1
4. Click OK to run the function.
WS O11 SAINS INFORMATIK
SAVE THE WORKSHEET
1. It is time for us to save the
worksheet. Click File > Save As…
2. Identify the location to save the
worksheet to.
3. Type “order_form” in the file name
field and save it as Microsoft Excel
Workbook filetype.
4. You have successfully created your
digital order form, congratulation.
