Belajar Rumus Microsoft Excel 2010 Pdf

• Rumus Excel 2013
• Rumus Excel Lengkap

Important: This feature isn’t available in Office on a Windows RT PC. Want to see what version of Office you're using?

By using forms and the many controls and objects that you can add to them, you can make data entry on your worksheets easier and improve the way your worksheets look. You can also do this yourself with little or no need for Microsoft Visual Basic for Applications (VBA) code.

Rumus Excel 2013

Latihan 1 Belajar Microsoft Word 2010 Untuk Pemula Edi Soal Latihan, Word Tidak ada Komentar. Latihan kali ini kita mengerjakan rumus aritmatika sederhana.

A worksheet form is not the same thing as an Excel template. A template is a pre-formatted file that can get you started creating a workbook that looks the way you want. A form contains controls, such as boxes or dropdown lists, that can make it easier for people who use your worksheet to enter or edit data. To find out more about templates you can download, see Excel templates.

What is a form?

A form, whether printed or online, is a document designed with a standard structure and format that makes it easier to capture, organize, and edit information.

• Printed forms contain instructions, formatting, labels, and blank spaces for writing or typing data. You can use Excel and Excel templates to create printed forms.

• Online forms contain the same features as printed forms. In addition, online forms contain controls. Controls are objects that display data or make it easier for users to enter or edit data, perform an action, or make a selection. In general, controls make the form easier to use. Examples of common controls include list boxes, option buttons, and command buttons. Controls can also run assigned macros and respond to events, such as mouse clicks, by running Visual Basic for Applications (VBA) code.

You can use Excel in several ways to create printed and online forms.

Types of Excel forms

There are several types of forms that you can create in Excel: data forms, worksheets that contain Form and ActiveX controls, and VBA UserForms. You can use each type of form by itself, or you can combine them in different ways to create a solution that's right for you.

Data form

A data form provides a convenient way to enter or display one complete row of information in a range or table without scrolling horizontally. You may find that using a data form can make data entry easier than moving from column to column when you have more columns of data than can be viewed on the screen. Use a data form when a simple form of text boxes that list the column headings as labels is sufficient and you don't need sophisticated or custom form features, such as a list box or spin button.

Excel can automatically generate a built-in data form for your range or table. The data form displays all column headers as labels in a single dialog box. Each label has an adjacent blank text box in which you can enter data for each column, up to a maximum of 32 columns. In a data form, you can enter new rows, find rows by navigating, or (based on cell contents) update rows and delete rows . If a cell contains a formula, the formula result is displayed in the data form, but you cannot change the formula by using the data form.

Worksheet with Form and ActiveX controls

A worksheet is a type of form that enables you to enter and view data on the grid, and there are several control-like features already built-in to Excel worksheets, such as comments and data validation. Cells resemble text boxes in that you can enter and format them in a variety of ways. Cells are often used as labels, and by adjusting cell height and width and merging cells, you can make a worksheet behave like a simple data entry form. Other control-like features, such as cell comments, hyperlinks, background images, data validation, conditional formatting, embedded charts, and AutoFilter can make a worksheet behave like an advanced form.

For added flexibility, you can add controls and other drawing objects to the drawing canvas of a worksheet, and combine and coordinate them with worksheet cells. For example, you can use a list box control to make it easier for a user to select from a list of items. Or, you can use a spin button control to make it easier for a user to enter a number.

Because controls and objects are stored on the drawing canvas, you can display or view controls and objects alongside associated text that is independent of row and column boundaries without changing the layout of a grid or table of data on your worksheet. Most of the time, many of these controls can also be linked to cells on the worksheet and do not require VBA code to make them work. You can set properties that determine whether a control floats freely or moves and resizes together with a cell. For example, you might have a check box that you want to move together with its underlying cell when the range is sorted. However, if you have a list box that you want to keep in a specific location at all times, you probably do not want it to move together with its underlying cell.

Excel has two types of controls: Form controls and ActiveX Controls. In addition to these sets of controls, you can also add objects from the Drawing tools, such as a AutoShapes, WordArt, SmartArt graphic, or text boxes.

The following sections describe these controls and drawing objects, and also explain how to work with these controls and objects in more detail.

Form controls

Form controls are the original controls that are compatible with earlier versions of Excel, starting with Excel version 5.0. Form controls are also designed for use on XLM macro sheets.

You use Form controls when you want to easily reference and interact with cell data without using VBA code, and when you want to add controls to chart sheets. For example, after you add a list box control to a worksheet and linking it to a cell, you can return a numeric value for the current position of the selected item in the control. You can then use that numeric value in conjunction with the INDEX function to select different items from the list.

You can also run macros by using Form controls. You can attach an existing macro to a control, or write or record a new macro. When a user of the form clicks the control, the control runs the macro.

However, these controls cannot be added to UserForms, used to control events, or modified to run Web scripts on Web pages.

Summary of form controls

Note: The following controls are unavailable in Office Excel 2007 workbooks. These controls can be used in Excel version 5.0 dialog sheets only.

Rumus Excel Lengkap

ActiveX controls

ActiveX controls can be used on worksheet forms, with or without the use of VBA code, and on VBA UserForms. In general, use ActiveX controls when you need more flexible design requirements than those provided by Form controls. ActiveX controls have extensive properties that you can use to customize their appearance, behavior, fonts, and other characteristics.

You can also control different events that occur when an ActiveX control is interacted with. For example, you can perform different actions, depending on which choice a user selects from a list box control, or you can query a database to refill a combo box with items when a user clicks a button. You can also write macros that respond to events associated with ActiveX controls. When a user of the form interacts with the control, your VBA code then runs to process any events that occur for that control.

Your computer also contains many ActiveX controls that were installed by Excel and other programs, such as Calendar Control 12.0 and Windows Media Player.

Important: Not all ActiveX controls can be used directly on worksheets; some can be used only on Visual Basic for Applications (VBA) UserForms. If you try to add any one of these particular ActiveX controls to a worksheet, Excel displays the message 'Cannot insert object.'

However, ActiveX controls cannot be added to chart sheets from the user interface or to XLM macro sheets. You also cannot assign a macro to run directly from an ActiveX control the same way you can from a Form control.

Summary of ActiveX controls

Drawing Tool objects

You may also want to include SmartArt graphics, Shapes, WordArt, and text boxes on your form. You can resize, rotate, flip, color, and combine these objects to create even more complex shapes. When you type text directly in a Shape or text box object, the text becomes part of that object — if you rotate or flip the object, the text rotates or flips with it. Unlike ActiveX controls, you can assign different attributes, such as font size and font style, to individual words and characters in the object. You can also assign macros and add hyperlinks to these objects. You can even link text in a Shape or text box object to a worksheet cell and dynamically display updated values in those objects.

Working with controls and objects on the worksheet form

After adding forms and ActiveX to a worksheet form, you usually want to fine-tune and rearrange the controls in a variety of ways to create a well-designed, user friendly form. Common tasks include the following:

• Controlling the display of gridlines while you work with the controls, and deciding whether to display the gridlines to the user on the final worksheet form.

• Selecting and deselecting controls so that you can specify properties or make additional adjustments.

• Editing text in a control, such as the caption or label.

• Grouping, copying, moving, and aligning controls to organize the layout of the worksheet form.

• Resizing and formatting controls to obtain the appearance that you want.

• Positioning or sizing a control with a cell.

• Protecting controls and linked cells according to your specific data protection needs.

• Enabling or disabling the printing of controls when the worksheet form is printed.

• Deleting unused controls.

You can design a worksheet form with or without cell gridlines in the background. For example, you might want to turn off cell gridlines and then format all the cells with the same color or pattern, or even use a picture as a sheet background. To hide or show the gridlines, on the View tab, in the Show/Hide group, clear or select the Gridlines check box.

Determining the type of control that is on your worksheet

Because there are three different types of controls and objects that you can modify uniquely, you might not know for sure which type of control it is just by looking at it. To determine the type of control (Form or ActiveX), select and right-click the control, and then display the shortcut menu:

• If the shortcut menu contains the command Properties, the control is an ActiveX control, and you are in design mode.

• If the shortcut menu contains the command Assign Macro, the control is a Form control.

  Tip: To display the correct shortcut menu for the group box Form control, make sure that you select the perimeter instead of the interior of the group box.

• If the shortcut menu contains the command Edit Text, the object is a Drawing object.

VBA UserForms

For maximum flexibility, you can create UserForms, which are custom dialog boxes, that usually include one or more ActiveX controls. You make UserForms available from VBA code that you create in the Visual Basic Editor. The high-level steps for creating a UserForm are as follows:

1. Insert a UserForm into your workbook's VBAProject. You access a workbook's VBAProject by first displaying the Visual Basic Editor (press ALT+F11) and then, on the Insert menu, clicking UserForm.

2. Write a procedure to display the UserForm.

3. Add ActiveX controls.

4. Modify properties for the ActiveX controls.

5. Write event-handler procedures for the ActiveX controls.

By using UserForms, you can also utilize advanced form functionality,. For example, you can programmatically add a separate option button for each letter of the alphabet or you can add a check box for each item in a large list of dates and numbers.

Before creating a UserForm, consider using built-in dialog boxes available from Excel that might fit your needs. These built-in dialog boxes include the VBA InputBox and MsgBox functions, the Excel InputBox method, GetOpenFilename method, GetSaveAsFilename method, and the Dialogs object of the Application object, which contains all the built-in Excel dialog boxes.

For more information, explore the Microsoft Office Excel Developer Center.

When a row of data is very wide and requires repeated horizontal scrolling, consider using a data form to add, edit, find, and delete rows.

Learn about data forms

A data form provides a convenient means to enter or display one complete row of information in a range or table without scrolling horizontally. You may find that using a data form can make data entry easier than moving from column to column when you have more columns of data than can be viewed on the screen. Use a data form when a simple form of text boxes that list the column headings as labels is sufficient and you don't need sophisticated or custom form features, such as a list box or spin button.

Microsoft Excel can automatically generate a built-in data form for your range or table. The data form displays all column headers as labels in a single dialog box. Each label has an adjacent blank text box in which you can enter data for each column, up to a maximum of 32 columns. In a data form, you can enter new rows, find rows by navigating, or (based on cell contents) update rows and delete rows. If a cell contains a formula, the formula result is displayed in the data form, but you cannot change the formula by using the data form.

Note: You cannot print a data form. In addition, because a data form is a modal dialog box, you cannot use either the Excel Print command or Print button until you close the data form. Alternatively, you can use the Windows Print Screen key to make an image of the form, and then copy it to Microsoft Paint or another program.

Add the Form button to the ribbon

1. If necessary, add a column header to each column in the range or table. Excel uses these column headers to create labels for each field on the form.

   Important: Make sure that there are no blank lines in the range of data.

2. Click a cell in the range or table to which you want to add the form.

3. To add the Form button to the Quick Access Toolbar, do the following:

   1. Click the arrow next to the Quick Access Toolbar, and then click More Commands.

   2. In the Choose commands from box, click All Commands, and then select the Form button in the list.

   3. Click Add, and then click OK.

4. On the Quick Access Toolbar, click Form .

Important: If you see a message that states 'Too many fields in the data form,' you need to reduce the number of columns, because a data form can contain only up to 32 columns. One solution (when working with a range of columns) is to insert a blank column, effectively breaking one range into two ranges. Then, if necessary, create a separate data form for the columns to the right of the blank column.

Use a data form

You can use a data form to add, find, change, and delete rows in a range or table.

Add a new row of data

1. In the data form, click New.
   

2. In the data form that appears, type the data for the new row in the fields provided.

   To move to the next field in the row, press Tab. To move to the previous field, press Shift+Tab.

3. After you have finished typing data, press Enter to save your changes and add the row to the bottom of the range or table.

   Important: If you see a message that states 'Cannot extend list or database,' this could mean that existing data may be overwritten if you continue. If you add a row to a table or range by using a data form, Excel expands it downward. If expanding the table would overwrite existing data, Excel displays the message Cannot extend list or database. Rearrange the data on your worksheet so that the range or table can expand downward from the last row.

Note: Before you press Enter, you can undo any changes by clicking Restore in the data form. Any data that you have typed in the fields is discarded.

Find a row by navigating

Do one or more of the following:

• To move through rows one at a time, use the scroll bar arrows in the data form.

• To move through 10 rows at a time, click the scroll bar in the area between the arrows.

• To move to the next row in the range or table, click Find Next.

• To move to the previous row in the range or table, click Find Prev.

Find a row by entering search criteria

1. Click Criteria, and then enter the comparison criteria in the data form.

   All items that begin with the comparison criteria are filtered. For example, if you type the text Dav as a criterion, Excel finds 'Davidovski' and 'Davis.' To find text values that share some characters but not others, use a wildcard character as your criterion.

   The following wildcard characters can be used as comparison criteria for filters, and when you search for and replace content.

1. To find rows that match the criteria, click Find Next or Find Prev.

2. To return to the data form so that you can add, change, or delete rows, click Form.

Change data in a row

1. Find the row that you want to change.

2. Change the data in the row. Ace base flac.

   To move to the next field in the row, press Tab. To move to the previous field, press Shift+Tab.

3. After you finish changing data, press Enter to update the row.

   Excel automatically moves to the next row.

   Note: Before you press Enter, you can undo any changes by clicking Restore.

Delete a row

1. In the data form, find the row that you want to delete.

2. Click Delete.

   Warning: Excel prompts you to confirm the operation. You cannot undo a row deletion after you confirm it.

Close a data form

To close the data form and return to the worksheet, click Close in the data form.

Need more help?

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.