js02 mohammad hanifzam bin ahmad kamal
Post on 01-Jun-2018
239 Views
Preview:
TRANSCRIPT
-
8/9/2019 Js02 Mohammad Hanifzam Bin Ahmad Kamal
1/16
-
8/9/2019 Js02 Mohammad Hanifzam Bin Ahmad Kamal
2/16
QUESTION/DISCUSSION
Question 1 : How to create the table.
In addition to its other spreadsheet features, Microsoft Excel oers you the ability
to create tables within a spreadsheet. Known as “lists” in Excel 200, they can
be !ana"ed separately fro! data you ha#e elsewhere on that spreadsheet pa"e
or any other pa"e in the spreadsheet. $ee $tep % below for instructions to !a&e
and !anipulate tables in Microsoft Excel.
Creating a Table
1) Select a range o cells. The cells can contain data or they can be empty, or a
combination of both. You don’t have to pick your cells before you create the table
if you aren’t sure yet.
-
8/9/2019 Js02 Mohammad Hanifzam Bin Ahmad Kamal
3/16
2) Insert the table. To start the table creation process, you will need to insert a table
into your spreadsheet.
• In !cel 2""#, click the $ata menu and select %ist.
• In !cel 2""&, 2"1", and 2"1# select either 'Table' on the Insert menu ribbon or
'(ormat as Table' in the tyles *roup on the +ome menu ribbon. The former option
applies !cel-s default table style, while the other lets you choose a style when you
create the table. You can later apply or chan*e the table style by selectin* one of the
options from the Table tyles *roup in the Table Tools $esi*n menu ribbon.)
-
8/9/2019 Js02 Mohammad Hanifzam Bin Ahmad Kamal
4/16
#) !ro"i#e a #ata source or $our table. If you did not previously select a *roup of
cells, you will need to select the cells now. fter you pick your ran*e, a dialo* bo!will appear, either the /reate Table dialo* /reate %ist dialo* in !cel 2""#) or
the (ormat s Table dialo*.
• The '0here is the data for your table' field displays the absolute references) for the
current cells) selected. If you want to chan*e this information, you can type in a
different cell or ran*e reference.
-
8/9/2019 Js02 Mohammad Hanifzam Bin Ahmad Kamal
5/16
) In#icate whether $our table has hea#ers. If your table has headers, check the
'3y table has headers' bo!. If you don-t check this bo!, the table will display
default header names '/olumn 1,' '/olumn 2,' etc.).
• You can chan*e a column name by selectin* the header and typin* in your own name
in the formula bar.
-
8/9/2019 Js02 Mohammad Hanifzam Bin Ahmad Kamal
6/16
Question % : &i"e e'a()le or (athe(atics or(ula using E'cel
4n a worksheet, you can enter simple formulas to add, divide, multiply, and subtract two or
more numeric values. You can also enter a formula that uses the 53 function also known
as utoum) to 6uickly total a series of values without havin* to enter any of them manuallyin a formula. 4nce you have created a formula, you can fill it into ad7acent cells 8 no need
to create the same formula over and over a*ain.
0hen you become familiar with these simple formulas, you may want to learn more about
how to create comple! formulas and try some of the many functions that are available in
!cel. (or more information, see 4verview of formulas and %ist of worksheet functions by
cate*ory).
Learn more about simple formulas
The first thin* that you have to know is that all formula entries start with an e6ual si*n *).
(or simple formulas, you type the e6ual si*n followed by the numeric values that you want to
calculate and the math operators that you want to use 8 for e!ample the plus si*n +) to add,
the minus si*n ,) to subtract, the asterisk -) to multiply, and the forward slash /) to divide
the values that you enter. 0hen you press 9T:, !cel instantly calculates and displays the
result of the formula.
(or e!ample, when you type a simple formula in a cell for e!ample, *1%.+1. in cell /;
in the followin* picture) and then press 9T:, !cel calculates the result and displays it in
that cell. The formula itself appears in the formula bar.
The formula that you enter in a cell remains visible in the formula bar, and you can see it
whenever that cell is selected.
'lic& (uto$u!
To 6uickly total a series of values without havin* to enter any of them manually in a formula,
you can enter a formula that uses the 53 function, also known as utoum.
-
8/9/2019 Js02 Mohammad Hanifzam Bin Ahmad Kamal
7/16
0utoSu( is the button that displays the 'summation' symbol the uppercase i*ma of the
& andthen click 0utoSu(. color mar6uee surrounds the cells that are selected in the formula that
is entered in cell >&. fter pressin* 9T:, the result of the formula is displayed in the
selected cell >&), and the formula appears in the formula bar.
/ell >& displays the result of the formula. The formula itself appears in the formula bar
whenever that cell is selected.
-
8/9/2019 Js02 Mohammad Hanifzam Bin Ahmad Kamal
8/16
In a formula that uses a function, such as SU3, the cell reference that appears inside the
parentheses is the ar*ument of the formula. The ar*ument determines which values the
formula is *oin* calculate. The colon :) in the cell reference 45:4 in the e!ample)
indicates that the cell reference is a ran*e of cells. ?arentheses are always re6uired in a
formula that uses a function 8 they separate the ar*ument from the function name in theformula.
0hen a cell reference is used in the ar*ument of a formula instead of the actual values, !cel
can automatically update the formula result whenever the values in the referenced cells are
updated.
$top typin" the sa!e for!ula o#er and o#er
4nce you have created a formula, you can copy it to other cells 8 no need to create the same
formula over and over a*ain.
(or e!ample, when you copy the formula in cell >& to the ad7acent cell /&, the formula in
that cell automatically ad7usts to the new location, and calculates the numeric values in
column /.
)he copied for!ula references and calculates the nu!eric #alues in colu!n '.
-
8/9/2019 Js02 Mohammad Hanifzam Bin Ahmad Kamal
9/16
Use a simple formula to add, subtract, multiply, or divide
numeric values
In a simple formula, you can enter values and math operators to calculate those values.
+owever, instead of enterin* values directly in the formula, you can also refer to the cells that
contain the values that you want to calculate. 5sin* cell references in a formula makes sure
that any chan*es to the values are automatically updated in the calculation result of the
formula.
1. 4n a worksheet, click the cell in which you want to enter the formula.
2. To start the formula, type *
#. To enter the first numeric value, do one of the followin*@
o Type the value that you want to use.
Ti) (or e!ample, type 16
o elect the cell that contains the value that you want to use.
Ti) (or e!ample, select cell 01.
. To enter the math operator that you want to use, do one of the followin*@
o To use a plus si*n +) to add the values in the formula, type +
o To use a minus si*n ,) to subtract the values in the formula, type ,
o To use an asterisk -) to multiply the values in the formula, type -
o To use a forward slash /) to divide the values in the formula, type /
A. To enter the ne!t value numeric value, do the followin*@
-
8/9/2019 Js02 Mohammad Hanifzam Bin Ahmad Kamal
10/16
o Type the value that you want to use.
Ti) (or e!ample, type 7.
o elect the cell that contains the value that you want to use.
Ti) (or e!ample, select cell 41.
;. :epeat steps and A for any additional math operations and values that you want to
include in the formula.
Ti) (or an operation to take precedence in the calculation, use parentheses around
that operation. (or e!ample, type *816+79-% or *801+419-C1.
&. 0hen the formula is complete, press 9T:.
>y default, the resultin* value of the formula appears in the selected cell, and the
formula itself is displayed in the formula bar.
-
8/9/2019 Js02 Mohammad Hanifzam Bin Ahmad Kamal
11/16
Use the SUM function to total numeric values in a column or
row
To calculate the total of a series of numeric values in a row or column, you do not have to
enter all those values manually into a formula. Instead you can use a predefined formula that
uses the 53 function.
1. 4n a worksheet, click a cell below or to the ri*ht of the numeric values that you want
to total.
2. 4n the Ho(e tab, in the E#iting *roup, click 0utoSu( .
Ti) 0utoSu( is also available on the or(ulas tab, in the unction 2ibrar$
*roup.
#. ?ress 9T: to display the SU3 function result in the selected cell.
The formula itself appears in the formula bar.
Ti) If you are lookin* for a 6uick way to total values in a column, you may want to place
your data in an !cel table. In an !cel table, you can add a total row so that you can
instantly summariBe the values in a column. (or more information, see the trainin* course
5se !cel tables to mana*e information.
)here is no $*+)(') function in Excel
lthou*h there is a 53 function, there is no 5>T:/T function. Instead, use the minus
C) operator in a formulaD for e!ample, EFC#G2CG12. 4r, you can use a minus si*n to converta number to its ne*ative value in the 53 functionD for e!ample, the formula E5312,A,C
#,F,C) uses the 53 function to add 12, A, subtract #, add F, and subtract , in that order.
-
8/9/2019 Js02 Mohammad Hanifzam Bin Ahmad Kamal
12/16
Fill a formula into adjacent cells
0hen you fill a formula into ad7acent cells, either in a row or column, the formula
automatically ad7usts to calculate the values in the correspondin* row or column.
1. 4n a worksheet, select the cell that contains the formula that you want to fill into
ad7acent cells.
2. $ra* the fill handle across the cells that you want to fill.
Note The fill handle is displayed by default, but if it is not available you must first
specify an option in !cel to enable the fill handle.
#. To specify how you want to fill the selection, click 0uto ill O)tions , and then
click the option that you want.
Note If automatic workbook calculation is not enabled, formulas will not recalculate when
you fill cells. To check your workbook calculation options, do the followin*@
1. /lick the ile tab, click 4ptions, and then click the or(ulas cate*ory.
2. 5nder Calculation o)tions, look under or;boo; Calculation.
o 0uto(atic (ormulas automatically recalculate.
o
0uto(atic e'ce)t or #ata tables (ormulas automatically recalculate,unless the formula is in a data table.
o 3anual (ormulas never automatically recalculate.
o
-
8/9/2019 Js02 Mohammad Hanifzam Bin Ahmad Kamal
13/16
• You can also fill the active cell with the formula of an ad7acent cell by usin* the ill
command on the Ho(e tab in the E#iting *roup) or by pressin* /T:%G$ to fill a
cell below or /T:%G: to fill a cell to the ri*ht of the cell that contains the formula.
• You can automatically fill a formula downward, for all ad7acent cells that it applies to,
by doubleCclickin* the fill handle of the first cell that contains the formula. (or
e!ample, cells 1@1A and >1@>1A contain numeric values, and you type the formula
*01+41 in cell /1. To copy that formula into cells /2@/1A so that /2E2G>2,
/#E#G>#, and so on), select cell /1 and doubleCclick the fill handle.
• If you are lookin* for an even faster method for fillin* formulas, you may want to
place your data in an !cel table. In an !cel table, you can create a calculated
column by simply enterin* one formula, and !cel automatically uses that formula in
the whole column. 3a*ic mathH (or more information, see 4verview of !cel tables.
-
8/9/2019 Js02 Mohammad Hanifzam Bin Ahmad Kamal
14/16
QUESTION 5 : How to insert s$(bol
-
8/9/2019 Js02 Mohammad Hanifzam Bin Ahmad Kamal
15/16
5pon click, the data will be converted into default currency values.
If want to chan*e the currency values into another country’s currency, click button at the
bottomCri*ht of the Number *roup as shown in the screenshot below.
You will reach Format Cells dialo*, from Symbol options you can choose desired currency
symbol. (rom $ecimal places you can chan*e the decimal positionin* in value. /lick OK to
continue.
-
8/9/2019 Js02 Mohammad Hanifzam Bin Ahmad Kamal
16/16
The values will be converted into specified currency.
This is the pro*ress How to insert s$(bol
top related