diktat microsoft excel

I.       Introduction

1. Start Microsoft Excel

Using the Start menu program

Ø   Click Start

Ø   Select the menu Programs / All Programs, and then select Microsoft Office

Ø   Click Microsoft Excel.

Using the Desktop Shortcut

Ø   Left click twice quickly on the Microsoft Excel icon on the Desktop screen.

Ø   Screen Microsoft. Excel will appear on the screen.

2. Know the Excel Window

a)       Cells                          : the meeting between the rows and columns

b)       Column                    : letters that show the columns in a worksheet.

c)       Classified                      : figures which show the lines on a worksheet.

d)      Formula Bar           : featuring characters typed into the selected cell.

e)       Name Box             : active cell address

f)        Sheet active             : active worksheet section of the workbook.

 

3. Know Your Excel Data Types

Ø  Data Text à data is a combination of numbers, characters, spaces, etc. which is read by the data format text.

Ø  Data digits (numeric) à numerical data consisting of 1,2,3,4,5,6,7,8,9,0.

Ø  Data Date / Time à numerical data formatted date and time data.

II.    Basic Operation One

1. Activating Toolbars

Ø   Click on the View Menu

Ø   Click the Toolbars

Ø   Select Group toolbars you want to enable. À Standard, Formatting, Drawing.

Ø   Icon toolbars will appear on the screen.

 

2. Entering Data

Ø   Click the cells that will be typed data.

Ø   Type the desired data. Data typed will be displayed on the active cell and in the Formula Bar.

Ø   Press Enter (on the keyboard).

3. Deleting Data ( Delete )

Ø   Select the cells whose data you want to delete.

Ø   Click Delete (on the keyboard).

4. Data copy ( Copy - Paste )

Ø   Block of the text or click on the image you want copied.

Ø   Click the Edit menu, then click Copy or click the Copy icon on the Standard toolbar.

Ø   Place the cursor at the location of interest.

Ø   Click the Edit menu, and then select Paste, click the Paste icon on the Standard toolbar

Ø   So the results of copying will be placed according to the location of the cursor point 3

5. Moving Data ( Cut - Paste )

Ø   Block of the text or click on the image you want to move.

Ø   Click the Edit menu, and then click Cut or click the Cut icon on the Standard toolbar.

Ø   Place the cursor at the location of interest.

Ø   Click the Edit menu, and then select Paste, click the Paste icon on the Standard toolbar

Ø   So the results laid removal corresponds to the cursor point 3.

6. Editing Data ( F2 )

Ø   Select the cells you want to edit the data.

Ø   Left click 2x faster on the cell or press F2 (on keyboard) or click on the Formula Bar, so that the cursor appears in the cell.

Ø   Perform edit data / change the desired data. To move the cursor use the arrow buttons (on the keyboard).

Ø   Press Enter or click on another cell to end

7. Sorting Data ( Sort )

Ø   Block All Data Table

Ø   Click Menu Data

Ø   Click the Sort

Ø  Specify RangeData Selected Cells To Order First Time In Grid Sort By

Ø   Determine Ordering options Ascending (A - Z) or Descending (Z - A)

Ø   On My List Has Select Option In Header Row, and then click Ok

8. Searching Data ( Find )

Ø  Click the Edit Menu

Ø  Click Find

Ø  Enter the data you want to search in the Find What

Ø  Click Find Next

Ø  To end the, click Close.

9. Changing Auto ( Replace )

Ø  Click the Edit Menu

Ø  Click Replace

Ø  Enter the data you want to be replaced / changed on the Find What

Ø  Enter the new data in the Replace With box

Ø  Click Replace All.

Ø  To end the, click Close.

10. Creating Automatic Numbering

Ø   Type in the numeric data in the cells, namely: 1.

Ø   Press enter.

Ø   Type in the numeric data in the cells, namely: 2.

Ø   Press enter.

Ø   Block Cells 1 and 2 there is data such.

Ø   Put the mouse pointer down on the corner of the cell that is the data point 2, so that it automatically turns into a + (plus).

Ø   Press and Hold, shifted as desired proficiency level until the data sequence.

III.            Operating Basics Two

1.   Managing Worksheet

Selecting Sheet Sequentially

Ø   Click on Tab Sheet The Preferred At The Bottom Workbook, So Work Screen Appears On Selected Worksheet

Ø   For Choosing More Than One Sheet Sequentially, Use Shift Keyboard Along Choose Sheet Other

Ø   To Cancel More Than One Sheet Selection, Use Right Mouse Click, then select Ungroup Sheet

Inserting Worksheet

Ø   Select What's New Worksheet Sheet Where Will Inserted

Ø   Click Menu INSER t

Ø   Select Worksheet

Ø   Worksheet New By Number Sheet Newsletter Will Appear In Left Worksheet Selected

Deleting Worksheet

Ø   Right Click Mouse On Worksheet To Be Removed

Ø   Select Delete

Ø   Click Ok

Renaming Worksheet

Ø   Click Twice Sheet Tab Will Be Replaced His name Or Right click and select Rename

Ø   Type New Name On The Sheet Blocked

Ø   Press Enter

Copy And Moving Worksheet

Ø   Select a Worksheet To Be Copied or Moved

Ø   Click Menu Edit

Ø   Click Move Or Copy

Ø   Specify Workbook Options As Interest Copying Or Moving On Box To Book

Ø   Set Location Copying Or Transfer of Worksheet In the Box Before Sheet

Ø   Determine Options For Copying On Box Create A Copy

Ø   Click Ok

2.       Insert Rows / Columns / Cells

Ø   Select the row or column to be inserted.

Ø   Click the Insert menu, select Row to insert a row, or Columns to insert the column.

3.       Delete Rows / Columns / Cells

Ø   Select the row or column to be deleted.

Ø   Click the Edit menu, and then select Delete.

4.       Merge Cells ( Merge & Center )

Ø   Block Cells that want to combine into one Sel.

Ø   Click on the icon Merge and Center on toolbars formating.

Ø   Then Cells will merge into one Sel.

Ø   Keep in mind that Cells would like to aggregate only one cell can be no data, while other cells are empty (no data).

5.       Centering Data Cells

Ø   Save cells or block of data in the cells you want centered.

Ø  Click icon Center Reviewed Formatting toolbars.

Ø   To cancel a command, clickicon Center back.

IV.            Set Font and Data

1. Set the Display Font Format ( Format - Cells - Font )

Ø  Block of data you want to adjust the look of the letters.

Ø   Click the Format menu.

Ø   Click Cells

Ø   Appearing Format Cells dialog box

Ø   Select Font Tab

Ø   Define the desired font format

v  Box Font: for changing the typeface.

v  Box Font Style: to change the shape of bold / italics.

v  Box Size: to change the font size.

v  Box Underline: to give the bottom line on the letter.

v  Box Effects:

v  Options Striketrought: to give the center line on the letter.

v  Options Superscript: to see small letters at the top.

v  Option Subscript: to see small letters at the bottom.

v  Box Color: to give color to the letter.

v  Click OK

v  Or you can use the icon toolbars formating group.

2. Leveling Data Display ( Format - Cells - Alignment )

v   Block data / text you want to adjust alignment.

v   Click the Format menu.

v   Click Cells

v   Appears Format Cells dialog box

v   Select the Alignment tab.

v   Specify the alignment of data in the Alignment box.

Ø   Box Horizontal: used for horizontal alignment.

ü   Option General: for standard alignment.

ü   Option Left: for left flat.

ü  Selection Center : Average middle.

ü   Option Right: for the right price.

ü   Option Justify: to average right-left

ü  Selection Center across selection: to be centered on a selected range of cells.

Ø   Box Vertical: used to adjust the alignment Vertically.

ü   Picks Top: for a flat top

ü  Selection Center : To be centered.

ü   Options Bottom: for flat bottom.

ü   Option Justify: to average right-left.

Ø   Box Text Control: Used to regulate cell incorporation.

ü   options Text Wrap: for compressing data in a single cell.

ü   Options Shrink to fit: to adjust the font size of data on cell size.

ü   options Merge Cells: to combine some range of cells (cells that are blocked) were selected.

v  Click OK

V.    Column Settings and the Line

1. Set the Column Width

Ø   Select the columns to be set.

Ø   Click the Format menu, then select Column.

Ø   Click Width.

Ø   Specify / contents size column in the box Column Width.

Ø   Click OK

2. Set the Row Height

Ø   Select the row to be set.

Ø   Click the Format menu, then select Row.

Ø   Click Height

Ø   Specify / Line sized content in the Row Height box.

Ø   Click OK

3. Hiding Rows / Columns ( Hide / Unhide )

Ø  Click the row / column you want to hide

Ø  Click the Format Menu

Ø  Click the Row / Column

Ø  Click Hide

Ø  Then the row / column will be hidden.

Ø  To show the row / column is hidden, click Unhide.

4. Set the Size column / row Manually

Ø   Click on Heading Column / Row, so that the mouse pointer changes into a pointer 2 children Panak.

Ø   Press and hold down the mouse left side, slide / withdrawn as desired.

Ø   To adjust the width of the column, is shifted to the left / right.

Ø   To adjust the height row, shifted up / down.

VI.            Opening, Saving, Closing

1. Creating a New Worksheet ( New )

Ø   Click the File menu,

Ø   Then select New or click the New icon on the standard toolbar.

Ø   Click Ok.

2. Activating worksheet

Ø   Click the Sheet Tab desired at the bottom of the workbook, so that the screen appears work on the worksheet are selected.

Ø   To select another worksheet, click the Sheet tab to another, it would appear that another worksheet anyway.

3. Opens ( Open )

Opens Workbook

Ø   Click on the File Menu

Ø   Select Open, or click the Open icon on the toolbar standard

Ø   Perform site selection Look in workbook box

Ø   Select the name of the workbook you want to open and click on the name of the workbook

Ø   Click Open.

Opens Workbook in diskettes

Ø   Click on the File Menu

Ø   Select Open, or click the Open icon on the toolbar standard

Ø   Perform site selection workbook in 3 ½ Floppy A: in the Look in box

Ø   Select the name of the workbook you want to open and click on the name of the workbook

Ø   Click Open

4. Saving With Other File Name ( Save As )

Saving Workbook

Ø   To save the workbook first time that has never been saved using the Save As command.

Ø   Click the File menu,

Ø   Select Save As

Ø   Make your selections on a storage location in the Save

Ø   Type the name of the desired workbook in the File Name box

Ø   Click Save

Storing the diskettes

Ø   Click the File menu,

Ø   Select Save As

Ø   Make your selection storage locations in 3 ½ Floppy A: in the box Save in

Ø   Type the name of the desired workbook in the File Name box

Ø   Click Save

5. Save ( Save )

Ø   As to save the workbook that has been saved using             the command File à Save .

6. Ending Excel

Closes Microsoft Excel

Ø   Click on the File Menu

Ø   Click Exit

Ø   Or click the cross button at the top right corner of the Title Bar.

Closing the Active Workbook

Ø   Click on the File Menu

Ø   Click the Close

Ø   Or click the cross button at the top right corner of the Menu Bar.

Ø   When the active workbook is saved, it will display a dialog box as follows:

a.        Button Yes ; used to store the workbook before closing.

b.       Keys No ; used to not save the workbook.

c.        Button Cancel ; is used to cancel an order closing the workbook.

VII.         Setting the View

1.       Set Score ( Format - Cells - Number )

v   Block data / text you want to organize the data numbers.

v   Click the Format menu.

v   Click Cells

v   Appears Format Cells dialog box

v   Select Tab Number.

v   In the Category box, select Number.

v   Specify the use of decimals in the box Decimal Places.

v   Options use separator with a check mark in the Use 1000 Separator.

v  Click OK

v   To change to a standard data format, a selection box is the General Category.

2.       Set Rp Indonesian (Accounting)

v   Data block numbers on the cell.

v   Click the Format menu.

v   Click Cells.

v   Click the Number tab.

v   In the Category box, select Accounting.

v   In the Symbol dialog box, select Rp. Indonesian.

v  Click OK

v   If the data in the form of cell ######## , then widen the column cells. 

3.       Setting the Date ( Date )

v   Block data / text you want to organize the data numbers.

v   Click the Format menu.

v   Click Cells

v   Appears Format Cells dialog box

v   Select Tab Number.

v   Click Date in the Category box.

v   Define and submit the form date in the Type box.

v  Click OK

4.       Set Time ( Time )

v   Block data / text you want to organize the data numbers.

v   Click the Format menu.

v   Click Cells

v   Appears Format Cells dialog box

v   Select Tab Number.

v   Click the Time in the Category box.

v   Define and submit the form when the Type box.

v  Click OK

VIII.     Drawing Lines Tables and Graphs

1.       Drawing Lines Table ( Format - Cells - Border )

Ø   Select the cell or range of cells to be created line of the table.

Ø   Click Format, and then select Cells.

Ø   Click on Tab Border.

v   Determine the type of line that will be used in the Presets box.

v   Specify the form line on the Style box.

v   Specify the line color on the Color box.

v   Specify the use of the line on the Border box.

v   Click the box Inside

v   Click the Outline box.

v   Note the Border box.

v   Click OK.

2.       Make Colour Cells ( Format - Cells - Patterns )

v   Block Sel.

v   Click the Format menu.

v   Click Cells

v   Appears Format Cells dialog box

v   Select the Patterns tab.

v   Click the Patterns box and select one of the existing image.

v   Click the color in the Color box.

v   Click OK.

3.       Creating Charts ( Insert - Chart )

v  block data table.

v  Click the Insert menu.

v  Click Chart.

v  Choose one type of chart on the box Chart Type and Chart Sub-Type.

v  Click Next.

v  Click Next.

v  Fill in the chart title in the Chart Title box.

v  Click Next.

v  Click Finish.

v  Adjust the position of the chart by clicking on the graphic image and hold down the left mouse button, then scroll to the desired position.

IX.            Printing

1. Page Setup

Ø   Click File, then select Page Setup

Ø   Click on Tab page, specify the desired paper format setting.

1. box Orientation
2. Portrait options: to print vertically.
3. Landscape options: to print horizontally.
4. Scaling box
5. Adjust options to: to scale printing on one page.
6. Paper Box Size: is used to determine the type of paper, select LEGAL example for the size F4.

Ø   Click the Margins tab, specify the desired settings page format.

7. Top box: the upper limit.
8. Box Bottom: lower limit (+1 inch when select Portrait).
9. Box Left: the left boundary.
10. Right Box: right limit (+1 inch when select Landscape).
11. Horizontally options: for setting horizontally based on the width of the paper.
12. Picks Vertically: for setting vertically based on the height of the paper.

Ø   Click OK

2. Print Area

v  Block data you want to print.

v  Click the File menu.

v  Click Print Area.

v  Click Set Print Area.

v  Then Excel will print only the block data mentioned above.

v  To cancel Print Area, can click Clear Print Area.

3. Print Preview

Ø   Click File, then select Print Preview

1. Next: to the next page.
2. Previos: to the previous page.
3. Zoom: zoom in / zoom out.
4. Print: print the page to the printer.
5. Setup: enable Page setup.
6. Margins: show border edge / edge.
7. Page Break Preview: dividing the worksheet more than one page.
8. Normal View: disable command Page Break Preview.
9. Close: closes tampialn Print Preview.

4. Print Into Printer

Ø   The cell block of data you want to print.

Ø   Click File, then select Print Area.

Ø   Click Set Print Area.

Ø   Set the Page Setup and Print Preview

Ø   Click   File, then click Print or click the Print icon on the Standard toolbars.

Ø   Specify printing on the box page Range

1. All options: to print all the pages.
2. Page selection (s): to print certain pages.
3. Box From ...... To ... ..: to fill the page number you want printed.

Ø   Click OK

X.    Operation Image and Object

1. FromFile ( Insert - Picture - FromFile )

Ø  Click the Insert menu.

Ø  Save Picture.

Ø  Click FromFile.

Ø  In the Lookin box, select the location for storing pictures.

Ø  Select a desired image.

Ø  Choose Insert key.

Ø  The image will appear in the work area of Excel.

2. AutoShape ( Insert - Picture - AutoShapes )

Ø  Click the Insert menu.

Ø  Save Picture.

Ø  Click AutoShapes.

Ø  Click on one of the categories autoshapes.

Ø  Select one picture autoshapes.

Ø  Draw to press and hold the left mouse in the work area excel.

Ø  Picture AutoShape will appear in the work area of Excel.

3. WordArt ( Insert - Picture - WordArt )

Ø  Click the Insert menu.

Ø  Save Picture.

Ø  Click WordArt.

Ø  Select and 2x click on one of WordArt.

Ø  Enter the text pad Edit WordArt Text box that says Your Text Here.

Ø  Set the font size on the Icon Size.

Ø  Arrange bold / italics letters on Icon Bold / Italic.

Ø   Click Ok.

Ø  WordArt will appear in the work area of Excel.

4. ClipArt ( Insert - Picture - ClipArt )

Ø  Click the Insert menu.

Ø  Save Picture.

Ø  Click ClipArt.

Ø  Select and click Organize Clips.

Ø  Save Later.

Ø  Click the Office Collections.

Ø  2x Click on kategory picture on the left.

Ø  Choose the right image, and click the triangle symbol on the image, click Copy

Ø  Click on the work area of Excel, click Paste icon.

Ø  Image clipart will appear in the work area of Excel.

5. Set the Picture Object

Ø  Click on the image object you want to organize.

Ø  then around the side of the object image will appear Sizing Handle.

Ø  Put the mouse pointer on Sizing Handle so that the mouse pointer turns into like two darts.

Ø  Press and hold the left mouse on Sizing Handle and move to enlarge or reduce the image size of the object.

Ø  To move the drawing object, click in the center of the image and press and hold, then scroll to the desired position.

Ø  To delete an object image, click on the image object so that it appears Sizing Handle and press the Delete key on the keyboard.

XI.            Using Formulas and Functions basic

Creating a Formula Operator

Ø   ^           reappointment

Ø   *           Multiplication

Ø   /            Distribution

Ø   +           Addition

Ø   -            Reduction

Ø   =           Equals

Ø   >           Larger

Ø   <           Smaller

Ø   > =         Greater or Equal

Ø   <=         Smaller or Equal

Ø   <>         Not Equal

Steps to Make Formula / Formula

1. Select the cells that will be created formula / formulas.
2. Type an equal sign (=) to start building a formula.
3. Type the desired formula.
4. Press Enter (on the keyboard).

Example of How to Use Formulas:

1. For example in cells B2 of data the figure is 10
2. For example in cells C2 of data the figure was 15
3. For example in cells D2 data is the figure was 20
4. Select the cells that will be made formulas, for example in cell E2 .
5. Type à = B2 + C2 + D2 
6. press Enter
7. Description B2 , C2 , D2 is the cell address that is the data equivalent figure was 10 + 15 + 20 .
8. Then in cell E2 of data the figure was 45 .
9. Exercise makes the formula subtraction, division, multiplication by using the same method in accordance Formula Operator.

Ø   Step Example of How to Use Formulas Function

SUM function formula

1. Click on the cell E2
2. Type the same with à   =
3. Type à SUM
4. Type the opening parenthesis à (
5. Blocks of cells B2, C2, D2 wanting summed à B2: D2
6. Type a closing parenthesis à )
7. Or directly press Enter
8. Then in cell E2 there will be the sum of which is 45 .
9. = SUM (B2: D2)   and press enter
10. 10 + 15 + 20 = 45

AVERAGE function formula

1. Click on the cell E2
2. Type the same with à   =
3. Type à AVERAGE
4. Type the opening parenthesis à (
5. Blocks of cells B2, C2, D2 wanting summed à B2: D2
6. Type a closing parenthesis à )
7. Or directly press Enter
8. Then in cell E2 there will be the sum of which is 15 .
9. = AVERAGE (B2: D2)   and press enter
10. (10 + 15 + 20) / 3 = 15

Formula MAX function

1. Click on the cell E2
2. Type the same with à   =
3. Type à MAX
4. Type the opening parenthesis à (
5. Blocks of cells B2, C2, D2 wanting summed à B2: D2
6. Type a closing parenthesis à )
7. Or directly press Enter
8. Then in cell E2 there will be the sum of which is 20 .
9. = MAX (B2: D2)   and press enter
10. 10,15,20 = 20

Formula Functions MIN

1. Click on the cell E2
2. Type the same with à   =
3. Type à MIN
4. Type the opening parenthesis à (
5. Blocks of cells B2, C2, D2 wanting summed à B2: D2
6. Type a closing parenthesis à )
7. Or directly press Enter
8. Then in cell E2 there will be the sum of which is 10 .
9. = MIN (B2: D2)   and press enter
10. 10,15,20 = 10

How to copy a formula:

1. After making formula / formula mentioned above, then turn on the mouse pointer in the cell that top that had contained the formula / formula.
2. Hover the mouse pointer over the lower right corner of cells that (to the Fill Handle ) until the mouse pointer automatically changes to the Plus ( + ) black.
3. Click the Fill Handle and drag the mouse pointer to the last cell that will contain a formula so that the formula will be copied / copied automatically.

Writing Procedure Formulas and Functions

 Each formula must begin by writing an equals sign   ( = ).

1. Mathematical operator

Operator	Information	Example
(   )	Grouping level calculations mathematical operation	= (8 * 2) - (2 + 3) à 11
+	Plus / Add	= 10 + 5 à 15
-	Minus / Less	= 15-3 à 12
*	Multiplication	= 4 * 5 à 20
/	division	= 45/5 à 9
%	Percent	= 70 * 25% à 17.5

2. operator Data

Operator	Sign	Information	Example
"   "	Double Quotation Mark	To type text data	= IF (A2 = " A " , " Pass " , " Fail " )
$	Signs Absolut	To type text data	= ( $ B $ 2 * C5) / 2
:	Colon	To block / range seluji-1 up to seluji-n	= SUM (A2 : A10)

3. Comparative operator

Operator	Information	Example
&	Combining two text data / formula	= "Junior" and "Kragan"
=	Together with	= IF (A2 = "A", ...
<	Less than	= IF (D2 <100, ...
>	More than	= IF (D2> 50, ...
<=	Less than Equals	= IF (D2 <= 100, ...
> =	More than Equals	= IF (D2> = 50, ...
<>	Not equal to	= IF (D2 <> "Computer", ...

4. Separator Operator Antarsyarat

Operator	Information	regional Settings	Example
,	Coma	English (United States)	= IF (A2 = "A" , "Pass" , "Fail")
;	Semicolon	Indonesian	= IF (A2 = "A" ; "Pass" ; "Failed")

FORMULA CATEGORY AND FUNCTION

1.       SUM

Function to produce a total sum.

Shape = SUM (number1, number2, angka3, .....)

Information :

Ø  number1, number2, angka3, ..... are the values of the data to be searched total.

Sample Formulas	Information
= SUM (3,2)	Finding the value of the total sum of 3 and 2, then the result 5
= SUM (A2: A15)	Searching for the total value of the range A2 up to A15

1         AVERAGE

Function to generate an average value.

Shape = AVERAGE (number1, number2, angka3, .....)

Information :

Ø  number1, number2, angka3, ..... are the values of the data to be searched average value.

Sample Formulas	Information
= AVERAGE (4,6)	Looking average value of 4 and 6, then the result 5
= AVERAGE (A2: A15)	Looking for the average value of the range A2 up to A15

2         MAX

Function to find the value of the largest / highest of a series of numbers.

Shape = MAX (number1, number2, angka3, .....)

Information :

Ø  number1, number2, angka3, ..... is a series of numbers that will be searched highs.

Sample Formulas	Information
= MAX (3.2)	Looking for the highest value of 3 and 2, then the result 3
= MAX (A2: A15)	Looking for the highest value of the range A2 up to A15

3         MIN

Function to find the smallest value / lowest of a series of numbers.

Shape = MIN (number1, number2, angka3, .....)

Information :

Ø  number1, number2, angka3, ..... is a series of numbers that will be searched lows.

Sample Formulas	Information
= MIN (3.2)	Looking for the lowest score of 3 and 2, then the result 2
= MIN (A2: A15)	Looking for the lowest value of the range A2 up to A15

4         IF SINGLE

Function to generate a value based on two conditions / requirements.

Shape = IF (logic value if the condition is true, value if the condition wrong)

Information :

Ø  Logic, filled seluji using comparison operators and the conditions / requirements.

Ø  value if the condition is true, is filled with the value produced when the logic is true.

Ø  value if the terms wrong, filled with the value produced when the logic is false.

Sample Formulas	Information
= IF (A2 <80, "TL", "L")	If the value is less than 80 then the TL, if the value is more than 80 then L
= IF (A2 = "L", 100.50)	If the value is L then 100, if a value other than L then 50
Note: If the requirement bertype text data, the text enclosed in double quotes à "text"
Note: If the requirement bertype numeric data, the numbers do not use double quotes

5         IF DOUBLE

Function to generate value by more than 2 conditions / requirements.

Shape = IF (Logika1, if syarat1 true value, IF (logika2, if syarat2 true value, IF (Logika3, syarat3 value if true, value if all terms one))) à ........ and so on.

Information :

Ø  Logika1, logika2, Logika3 .... filled seluji using comparison operators and the conditions / requirements.

Ø  Value if syarat1 true, value if syarat2 true, value if true syarat3, .... filled with the value generated when the logic is true.

Ø  value if all is one, filled with the value produced when the logic is false.

Sample Formulas	Information
= IF (A2 = 10, "A", IF (A2 = 9, "B", IF (A2 = 8, "C", "D")))	If the value of 10 then A, then B if the value 9, If the value 8 then C, if in addition to the value of the D.
Note: Brackets closed and open à (   ) follows the number of IF used in the formula.

6         CONCATENATE

Function to combine multiple text data.

Shape = CONCATENATE (text1, text2, ......)

Information :

Ø  Text1, text2, ..... is the text that will be merged. Text can be strings, numbers, or the address of the cell.

Ø  Signs and serves to incorporate some of the text. Sign and have the same value with CONCATENATE.

Sample Formulas	Information
= CONCATENATE ( "SMP", "1", "Kragan")	The result is a Junior 1 Kragan
= A1 & B4	The result is a combination of cell data A1 and B4

7         LEFT

Function to retrieve a few letters of a text data of the left position.

Shape = LEFT (text, jumlah_karakter)

Information :

Ø  Text, is a text or cell address (seluji) are retrieved character.

Ø  Jumlah_karakter, is the number of digit characters to be drawn from the text.

Sample Formulas	Information
= LEFT (A3,2)	Taking the two characters from the left in cell A3. if the text data in cell A3 is Kragan , then the result shown is KR .
= LEFT (A2,4)	Taking four characters from the left in cell A2. if the text data in cell A2 is Kragan , then the result shown is Krag .

8         MID

Function to retrieve a few letters of a text data of the position of the middle section.

Shape = MID (text, nomor_urut_mulai, jumlah_karakter)

Information :

Ø  Text, is a text or cell address (seluji) are retrieved character.

Ø  Nomor_urut_mulai, is a numerical value that indicates the position of the start of the retrieval characters is calculated from the left position.

Ø  Jumlah_karakter, is the number of digit characters to be drawn from the text.

Sample Formulas	Information
= MID (A2,3,4)	Taking the characters begin to number 3 by 4 characters of cell A2 is YUDHISHIP , the results shown are DHIS .
= MID ( "yudhiship", 4.3)	Taking the characters begin to number 4 by 3 characters of text data " yudhiship ", then the result shown is his .

9         RIGHT

Function to retrieve a few letters of a text data of the position to the right.

Shape = RIGHT (text, jumlah_karakter)

Information :

Ø  Text, is a text or cell address (seluji) are retrieved character.

Ø  Jumlah_karakter, is the number of digit characters to be drawn from the text.

Sample Formulas	Information
= RIGHT (A3,2)	Taking the two characters from the right on the cell A3. if the text data in cell A3 is Kragan , the results shown are AN .
= RIGHT (A2,4)	Taking four characters from the right on the cell A2. if the text data in cell A2 is Kragan , the results shown are Agan .

11. VALUE

Function to convert the data into data bertype text bertype number / numeric.

Shape = VALUE (text)

Information :

Ø  Text is text data writing enclosed in double quotation marks ( "text") or preceded by alamt cells.

Sample Formulas	Information	result
= VALUE ( "$ 2,000")	The numerical value of the text "$ 2,000"	2000
= VALUE ( "16:48:00")	The numerical value of the text "16:48:00"	16:48:00

12.   PROPER

Function to replace the first character of the text data to uppercase (capital letters).

Shape = PROPER (text)

Information :

Ø  The text is a text or cell address to be changed character.

Sample Formulas	Information
= PROPER ( "yudhi Setyono")	Changing the text data into Yudhi Setyono
= PROPER (A3)	Changing the text data in cell A3

13.   UPPER

Function to convert the data writing text to uppercase (capital letters) all.

Shape = UPPER (text)

Information :

Ø  The text is a text or cell address to be changed character.

Sample Formulas	Information
= UPPER ( "yudhi Setyono")	Changing the text data into YUDHI Setyono
= UPPER (A3)	Changing the text data in cell A3

14.   LOWER

Function to convert the data writing text to lowercase all.

Shape = LOWER (text)

Information :

Ø  The text is a text or cell address to be changed character.

Sample Formulas	Information
= LOWER ( "YUDHI Setyono")	Changing the text data into yudhi Setyono
= LOWER (A3)	Changing the text data in cell A3

15.   ROUND

Functions for the data rounding numbers up to a certain number of decimal digits.

Shape = ROUND (number, jumlah_digit)

Information :

Ø  Score, is a numeric value or cell address data will be rounded.

Ø  Jumlah_digit is the number of decimal digits to round.

Sample Formulas	Information	result
= ROUND (2.15,1)	Rounds 2:15 to 1 decimal digit.	2.2
= ROUND (2.51,0)	2:51 rounding to 0 decimal digits (without decimals)	3
= ROUND (A3,2)	Rounds the numeric data in cell A3 to 2 decimal digits	2:15

16.   ROUNDDOWN

Function for rounding down to the numeric data.

Shape = ROUNDDOWN (number, jumlah_digit)

Information :

Ø  Score, is a numeric value or cell address data will be rounded.

Ø  Jumlah_digit is the number of decimal digits to round.

Sample Formulas	Information	result
= ROUNDDOWN (A3,0)	Rounds the numeric data in cell A3 with 0 decimal digits (no decimal), such data is 3.2	3
= ROUNDDOWN (5.6,0)	Rounds 5.6 to 0 decimal digits (without decimals)	5

17.   ROUNDUP

Function for rounding up the numerical data.

Shape = ROUNDUP (number, jumlah_digit)

Information :

Ø  Score, is a numeric value or cell address data will be rounded.

Ø  Jumlah_digit is the number of decimal digits to round.

Sample Formulas	Information	result
= ROUNDUP (A3,0)	Rounds the numeric data in cell A3 with 0 decimal digits (no decimal), such data is 3.2	4
= ROUNDUP (5.6,0)	Rounds 5.6 to 0 decimal digits (without decimals)	6

18.   TRUNC

Function to generate a value of decimal / fraction regardless rounding factor.

Shape = TRUNC (number, jumlah_digit)

Information :

Ø  Score, is a numeric value or cell address data will be rounded.

Ø  Jumlah_digit is the required number of decimal digits.

Sample Formulas	Information	result
= TRUNC (45769.0)	Taking the integer value of 45 769 with the number of decimal digits 0	45
= TRUNC (45769.1)	Taking the integer value of 45 769 with the number of decimal digits 1	45.7

19.   INT

Function to search for the results of ignoring / eliminate the value of the decimal / fractional.

Shape = INT (number)

Information :

Ø  Score is a numerical value or a cell address data will be rounded.

Sample Formulas	Information	result
= INT (8.9)	Rounds the value of 8.9	8
= INT (10/3)	Rounds the value of the division of 10 and 3	3

20.   RANK

Functions for generating sequence numbers rank of a value in a list of values.

Shape = RANK (number, range_data, sequence)

Information :

Ø  Score, is a value or cell address would look for serial numbers.

Ø  Range_data, is an array or cell address that contains a list of values which is the numeric data and press F4.

Ø  The order is the value that determines the order number. If 0 or omitted mean values Descending sort (ZA). If the value of 1 means sort Ascending (AZ).

Sample Formulas	Information
= RANK (A3, $ A $ 3: $ A $ 5.0)	Looking rank order Descending cell A3
= RANK (A3, $ A $ 3: $ A $ 5.1)	Looking rank order Ascending cell A3

21.   OR

Function to generate value, if the conditions / requirements are met one of them.

Shape = OR (logika1, logika2, ......)

Information :

Ø  logika1, logika2, ..... is a condition that will contain Duji that can generate value.

Ø  Usually OR formula is used in conjunction with the formula IF.

Sample Formulas	Information
= IF (OR (B4> = 65, C4> = 60), "L", "TL")	If the terms to meet one of the cells B4 and C4, then L. If the condition does not meet one of the cells B4 and C4, then the TL.

22.   AND

Function to generate value, if the conditions / requirements are met them all.

Shape = AND (logika1, logika2, .....)

Information :

Ø  logika1, logika2, ..... is a condition that will contain Duji that can generate value.

Ø  Normally AND formula is used in conjunction with the formula IF.

Sample Formulas	Information
= IF (AND (B4> = 65, C4> = 60), "L", "TL")	If the terms to meet one of the cells B4 and C4, then the TL. If the terms to meet all the cells B4 and C4, then L.

23.   HLOOKUP

Function to fill in the data table by table array horizontally.

Shape = HLOOKUP (Sel_Kunci, range_tabel_horisontal, nomor_urut_baris) 

Information :

Ø  Sel_Kunci, is the address of the cell that is used as a key to the reading table.

Ø  Range_tabel_horisontal, is the table name array (primary data table) that will be read by means of data blocks on a table array and press F4 .

Ø  Nomor_urut_baris is in the form of numerical data which is the serial number of horizontal rows of the table array.

Sample Formulas	Information
= HLOOKUP (A5, $ B $ 19: $ C $ 20.2)	Filling the data in cell array table A5 based on cell B19 up to K20 with numerical line 2.

24.   VLOOKUP

Function to fill in the data table by table arrays vertically.

Shape = VLOOKUP (Sel_Kunci, range_tabel_vertikal, nomor_urut_kolom) 

Information :

Ø  Sel_Kunci, is the address of the cell that is used as a key to the reading table.

Ø  Range_tabel_vertikal, is the table name array (primary data table) that will be read by means of data blocks on a table array and press F4 .

Ø  Nomor_urut_kolom is in the form of numerical data which is the serial number of the vertical columns of the table array.

Sample Formulas	Information
= VLOOKUP (A5, $ F $ 5: $ G $ 14.2)	Filling the data in cell array table A5 based on cell F5 to the G14 with the serial number of the 2nd column.

25.   MODE

Function to produce a particular value that most often arises from a group of numbers.

Shape = MODE (number1, number2, .....)

Information :

Ø  number1, number2, .... is numeric data, the address of the cell, or range of data.

Sample Formulas	Information
= MODE (C29: C38)	Looking for the most value appears in cell C29 through C38.
= MODE (2,2,3,2,2,4,5,2,5)	Looking for the most value appears on the data is 2.

26.   COUNT

Function to calculate the number of cells that contain numeric data from data group.

Shape = COUNT (value1, value2, .....)

Information :

Ø  Value1, value2, .... is the cell address / data contain some variation of types of data, but only the type of numbers that counted.

Sample Formulas	Information
= COUNT (A2: A8)	Count cells that contain numeric data from cell A2 through A8.
= COUNT (2,3,4,2,1)	Count cells that contain numeric data, then the result 5

27.   COUNTA

Function to calculate the number of cells   containing the data (not empty) regardless of the data type of the data group.

Shape = COUNTA (value1, value2, .....)

Information :

Ø  Value1, value2, .... is the cell address / data contain some variation of types of data, and entirely calculated.

Sample Formulas	Information
= COUNTA (A2: A8)	Count cells that contain data from cell A2 through A8.
= COUNTA (2,3,4,2, "a", "b")	Count cells that contain the data, then the result 6

28.   COUNTBLANK

Function to calculate the number of empty cells of group data.

Shape = COUNTBLANK (value1, value2, .....)

Information :

Ø  Value1, value2, .... is the cell address / data contain some variation of types of data, and entirely calculated.

Sample Formulas	Information
= COUNTBLANK (A2: A8)	Count cells that contain empty data from cell A2 through A8.
= COUNTBLANK ( "", 3,2)	Calculate the cell that contains the data is empty, then the result is 1

29.   COUNTIF

Function to count the number of cells in a range (group data) with the data by certain criteria.

Shape = COUNTIF (range, criteria)

Information :

Ø  Range, is a group of data that contain variations of the data by the data block in the data group and press F4 .

Ø  The criteria are criteria that contains the data to be tested.

Sample Formulas	Information
= COUNTIF ($ A $ 2: $ A $ 8, "> 55")	Counting the number of cells in the cell with the criteria of> 55
= COUNTIF ($ A $ 2: $ A $ 10, "ani")	Counting the number of cells in the cell with the criteria of "ani"
= COUNTIF ($ A $ 2: $ A $ 8, "7")	Counting the number of cells in the cell with the criterion 7

30.   SUMIF

Function to sum the data or the number of values ​​that meet certain criteria.

Shape = SUMIF (range, criteria, Sum_range)

Information :

Ø  Range, is the address of the cell to be tested by means of data blocks in the data group range and press F4 .

Ø  Criteria, is contains criteria to be searched.

Ø  Sum_range is the cell address range of data that will be summed by means of data blocks in the data group Sum_range and press F4 .

Sample Formulas	Information
= SUMIF ($ C $ 4: $ C $ 8 "> = 2000", $ D $ 4: $ D $ 8)	Searching for a total of cells D4: D8 having a value in C4: C8 with criteria> = 2000.
= SUMIF ($ C $ 4: $ C $ 8, "> 1000")	Searching for a total of cells C4: C8 that has value by criteria> 1000.

31.   AVERAGEIF

Function to find the average value of the range group data by certain criteria.

Shape = AVERAGEIF (range, criteria, Average_range)

Information :

Ø    Range, is the address of the cell to be tested by means of data blocks in the data group range and press F4 .

Ø  Criteria, is contains criteria to be searched.

Ø  Average_range is the cell address range of data to be searched averaged by means of data blocks in the data group average_range and press F4 .

Sample Formulas	Information
= AVERAGEIF ($ C $ 4: $ C $ 8, "> 2000", $ D $ 4: $ D $ 8)	Looking average value of cell D4: D8 having a value in C4: C8 with a criterion of> 2000.
= AVERAGEIF ($ C $ 4: $ C $ 8, "<1000")	Looking average value of cell C4: C8 having a value criteria <1000.

32.   AVERAGEA

Function to find the average value of a value with numeric data, text and logical values.

Shape = AVERAGEA (value1, value2, ........)

Information :

Ø  Value1, value2, ..... is the value that will be sought after the average value.

Sample Formulas	Information
= AVERAGEA (A2: A5)	Looking for the average value of the data in the range A2: A5, the data containing the text also calculated and considered to be worth 0. while the data is empty, do not count.

33.   SUBTOTAL

Function to generate value subtotals in a list or database (data collection).

Shape = SUBTOTAL (Nomor_kode, value1, value2, ......)

Information :

Ø  Nomor_kode, is the code number of functions with the option type.

Ø  Value1, value2, ...... is the numeric data to be searched subtotalnya value.

Code numbers	Function	Information
1	AVERAGE	Function to generate an average value
2	COUNT	Function to calculate the number of cells that contain numeric data from data group
3	COUNTA	Function to calculate the number of cells   containing the data (not empty) regardless of the data type of the data group
4	MAX	Function to find the value of the largest / highest of the series of numbers
5	MIN	Function to find the smallest value / lowest of a series of numbers
9	SUM	Function to produce a total sum

Sample Formulas	Information
= SUBTOTAL (4, B2: B5)	Looking maximum value / high for the data in cell range B2: B5
= SUBTOTAL (1, A2: A5)	Looking average value for the data in the cell range A2: A5
Note: Nomor_Kode function adjusts the formula to be used in problem solving.

34.   DATE

Function to write data date.

Shape = DATE (year, month, date)

Information :

Ø  Year, is the figure in the writing of the data.

Ø  Moon, is the numeric data in the writing of the month.

Ø  Date is the numeric data in the writing date.

Sample Formulas	Information	result
= DATE (2001,12,20)	Generate data date from the date of December 20, 2001	12/20/2001

35.   TIME

Function to write time data.

Shape = TIME (hour, minute, second)

Information :

Ø  Clock, is the numeric data in the writing hours.

Ø  Minutes, is the numeric data in the writing minutes.

Ø  Second is the numeric data in the writing seconds.

Sample Formulas	Information	result
= TIME (12,20,30)	Generate data on the time clock 12:20:30	12:20:30

Toolbars MICROSOFT EXCEL

A.     Function Standard Toolbar

1

2

3

4

5

6

7

8

9

10

11

12

13

14

Here is a function of the standard toolbar icons:

NO	NAME   ICON	INFORMATION
1	NEW	To create a document / new worksheet
2	OPEN	To open a document that has been saved
3	SAVE	To save the document
4	PRINT	To print a document onto paper
5	PRINT PREVIEW	To print the document to the screen
6	CUT	To move the text / object
7	COPY	To copy text / object
8	PASTE	To paste the result of Cut / Copy
9	UNDO	To countermand
10	REDO	To repeat the last command
11	SORT ASCENDING	To sort the data from A to Z
12	SORT DESCENDING	To sort the data from Z to A
13	CHART WIZARD	To create an object graph
14	ZOOM	To zoom in / out the display on the screen

B.      Function Formatting Toolbar

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

Here is a function of the formatting toolbar icons:

NO	NAME   ICON	INFORMATION
1	FONT	To select the font / text
2	FONT SIZE	To set the size of the font / text
3	BOLD	To make it bold / text
4	italic	To tilt the letter / text
5	Underline	To underline the letter / text
6	ALIGN LEFT	To flatten the left
7	CENTER	For the average middle
8	ALIGN RIGHT	To flatten right
9	MERGE AND CENTER	To merge 2 cells / over and centering the data
10	CURRENCY	To make the currency symbol on the numeric data
11	PERCENT STYLE	To change the numeric data into a percent (%)
12	COMMA STYLE	To menagktifkan use 1000 separator on numeric data
13	INCREASE DECIMAL	To add a decimal value on the numeric data
14	DECREASE DECIMAL	To reduce the decimal value on the numeric data
15	FILL COLOR	To give color to the cells
16	FONT COLOR	To give color to the font / text
17	FORMAT CELLS	To set the cell

C.     Functions Drawing Toolbar

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

Here is a function of the drawing toolbar icons:

NO	NAME   ICON	INFORMATION
1	DRAW	To display a submenu Draw
2	SELECT OBJECTS	To select an object
3	AutoShapes	To display a submenu autoshapes
4	LINE	To create a straight line
5	ARROW	To create a line of arrows
6	Rectangle	To make objects rectangular / box
7	OVAL	To make circular objects
8	TEXT BOX	To create a text box
9	INSERT WORD ART	To create an artistic writing
10	INSERT CLIP ART	To create a standard image of Microsoft Excel
11	INSERT PICTURE FROM FILE	To create an image from another file
12	FILL COLOR	To give color background / basic cells / object
13	COLOR LINE	To give color to the object line
14	FONT COLOR	To give color to the article / letter
15	LINE STYLE	To select a form the size of a straight line
16	DASH STYLE	To select shapes, sizes dotted line
17	ARROW STYLE	To select the line shape arrow

D.     Functions Toolbar WordArt

1

2

3

4

5

6

7

NO	NAME   ICON	INFORMATION
1	WordArt	To activate WordArt
2	EDIT TEXT	To edit / change the text written in WordArt
3	WordArt GALLERY	To display several types of options WordArt
4	OBJECT FORMAT	To set the color / size of WordArt objects
5	WordArt SHAPE	To display the options modify WordArt
6	WordArt VERT / horz	To change the WordArt in Vertical / Horizontal
7	WordArt ALIGNMENT	To display format alignment

HAPPY LEARNING AND GOOD LUCK