MOZA 4 LEARNING

Symbol	The resulting display
General	Digit serial number of days starting from the early 20th century. For example on 23 Dec 2007 39 439 will be displayed as numbers.
Date
d	Figures date from 1 to 31 without starting with 0.
Dd	Figures date from 01 to 31 starting with 0.
Ddd	Name days in abbreviated form, for example, Sun, Mon, Tue.
Dddd	Name day in the form of the writing intact, for example Sunday, Monday, Tuesday.
Month
M	Figures from 1 to 12 months without beginning with 0.
Mm	Figures month from 01 to 12 with the prefix 0.
Mmm	Abbreviation of writing the name of the month, the example of Jan, of Feb, Mar.
Mmmm	Writing the name of the month as a whole, such as January, February, March.
Year
Yy	Writing only a two-digit figures last year alone, from 00 to 99 (note, in 1900 and 2000 in two equally digit display with 00).
Yyy	Writing numbers in full four-digit year.
Jam (Hour)
h	Figures from 0 to 24 hours without the first 0.
hh	Figures hours from 00 to 24 starting 0.
Minute
m	The minutes from 0 to 59 without the first 0.
mm	The minutes from 00 to 59 beginning with 0.
Second
s	A number of seconds from 0 to 59 without the first 0.
ss	A number of seconds from 00 to 59 starting 0.
[ ]	Writing hours greater than 24, writing minutes and seconds over 60.
AM/PM
A/P	Displays clock with AM or PM time information behind.

Symbol m used for months or minutes. Excel will differentiate writing m for minute when following symbols h for hours. If m standing in front or standing behind the symbol d, will be considered as a month.

Format	Display
dddd	Saturday
ddmmmmyyyy	09 August 2007

Hiding Zero Value
If in a table appearance zero value is not desired, you can hide a zero value in two ways.

Hiding Zero Value in a Worksheet
Click the Microsoft Office button and click Excel Options. At Advanced, under the Display options for this worksheet, deactivate the option "Show a zero in cells that have zero value".

Through this manner specified definition will apply to all cells in the active worksheet. If you want to define for several worksheets simultaneously choose the tabs and the title of the new worksheet, use the steps above.

Hiding Zero Value in a Certain Cells
In the cells that null values do not want to appear, use the following simple custom format #, ###. In principle, only the # sign stating the number of digits of a display of numbers only, so if the value is zero, then it is not displayed. Unlike the format #, ## 0 when the value of zero will be displayed as 0.

In addition through the tools in the Group Number, you can format the numbers by pressing Ctrl + 1 so that the Cell Format dialog box appears. Then select the Number tab.


Formatting Numbers By Custom
There is a numeric format in Excel custom options. In this category provided a choice of diverse format that is expected to meet the needs of the wearer. However, in addition to formatting options that you have available, you can also design their own form of the display format numbers according to the needs and your creations.

Format	Data typed	Positive values are displayed
General	125	125.00
0	125	125
0.00	125.00	125.00
#,##0	1257	1,257
#,##0.00	1257	1,257.00
#,##0_);(#,##O)	1257	1,257
#,##0_);[Red](#,##O)	1257	1,257
#,##0.00_);(#,##O.OO)	1257.126	1,257.13
#,##0.00_);[Red](#,##O.OO)	1257.126	1,257.13
$#,##0_);($#,##0)	1257.126	$1,257
$#,##0_);[Red]($#,##0)	1257.126	$1,257
$#,##0.00_);($#,##0.00)	1257.126	$1,257.13
$#,##0.00_);[Red]($#,##0.00)	1257.126	$1,257.13
0%	0.25	25%
0.00%	0.2575	25.75%
O.OOE+OO	1257.126	1.26E+03
##O.OE+O	1257.126	1.3E+3
#?/?	1257.126	1257 118
# ??/??	1257.0126	1257 1/79
m/d/yy	20jul	7/20/97
d-mmm-yy	20jul97	20-Jul-97
d-mmm	20jul97	20-Jul
mmm-yy	20jul97	Jul-97
h:mmAM/PM	11:31	11:31 AM
h:mm:ss AM/PM	11:31	11:31:00 AM
h:mm	11:31	1:31
h:mm:ss	11:31	11:31:00
m/d/yy h:mm	20jul97	7/20/97 0:00
mm:ss	11:31:45	31:45
mm:ss,O	11:31:45	11:31:45
@	20 jul 97	35631
[h]:mm:ss	11:31:45	11:31:45
_($* #,##O_);_($* (#,##0);_($"-"_);(@_)	10035.75	$ 10,036
_(* #,##O_);_(* (#,##0);_(*"-"_);(@_)	10035.75	10,036
_($* #,##0.00 );_($* (#,##0.00);_($"-"?? );(@_)	10035.75	$ 10,035.75
_(* #,##0.00 );_(* (#,##0.00);_(*"-"?? );(@_)	10035.75	10,035.75

Writing Custom Numeric Format consists of four parts as in the following example.
positive number format; format negative numbers; the format of writing the number zero; text format
• The first position format to write the number with a positive value.
• The second position states the format of writing numbers with a negative value.
• The third position to state how the format to write a value of zero.
• The fourth position turns writing format hila its entry in text format.
Each section separated sign a semi colon (semicolon).

This group provides a number of formatting tools quickly. Being if necessary formatting specifically click the arrow button    in the lower right corner of this group to display the Cell Format dialog box, Tab Number.

 Excel has a lot of numeric formats including the writing of the date and time format. In addition to the default formats are ready for use, you will still be able to define its own format to suit your needs. The additional format is known as custom formats.

In the top selection box is provided formats are generally the most commonly used. If we click on the small button  the right of the box will display the names of numeric format.
While the formats are numerically more as follows.

General Format
Display figures without specific format. If the integer numbers written exceeds Iebar column, Excel will automatically change in exponent form. .1257 Typed will be displayed 0.1257, and 345.0 will be displayed 345 only.

Number Format
Writing numbers by taking into account the positive or negative value. Positive values shown no sign +, but it has a negative value of 4 kinds of writing format.


Currency
Writing numbers as the value of the currency. Currency symbols adapted to regional format is selected via the Control Panel, Regional Settings. Regional choice English (United Stated) will display the symbol $, while the regional selection Indonesian Rp will display. Writing currency symbols attached on the left of the first digit number without spaces separated.


Accounting Format
Displays the value of the currency with the currency symbol the left and the right with the average number is the thousands separator and two decimal digits. This format also offers the desired number of decimal digits.


Date Format
To display an assortment of writing the date.


Time Format
To show various forms of writing / time value.


Percentage Format
Displays percentage (1/100) complete with sign% behind the figures, along with defining the number of digits in a decimal value.

Fraction Format
Displaying the form of writing a float value.

Scientific Format
Form numbers exponentially.


Text Format
Display figures are converted as text so it is not worth.

Special Format
Forms special numbers such as postal area code number, phone number, social security number that is valid in the USA.

Custom Format
The forms display numbers that you define yourself. Form a very wide choice and you can modify yourself.

Displaying and Hiding Decimal
At Group Number there are two tools that serves to show and hide single-digit decimal number by one digit
Increase Decimal     Decrease Decimal

Excel provides a fast typist for the ease with some shortcut buttons for formatting numbers with a particular format as follows:

Format	Tombol Shortcut
General	Shift+Ctrl+ -
Number (2 decimal places)	Shift+Ctrl+ !
Currency (2 decimal places)	Shift+Ctrl+ $
Percent (without decimal)	Shift+Ctrl+ %
Scientific	Shift+Ctrl+ "

When you copy a cell, you can paste a certain part of the cell contents. It can be you specify via the Paste Special dialog box is accessed via the button Paste, Paste Special on the Home tab Clipboard group.

In the Paste Special dialog box are jerked group options how to paste the object. The first is "Paste" to determine what is pasted on the objects that have been copied. Both "Operation" is to determine how the value of the copied object is calculated by the value of objects that exist in a cell at the destination location. The third group of options Skip Blank and Transpose. Being the fourth is Paste Link.

All	All components of the copied cell or range that will participate in the paste. Component by default it consists of values, formulas, formatting, notes and borders as well as the reference. This option has the same effect with the command Edit, Paste.
formulas	which is pasted only formula alone.
Values	which is pasted only the value of the cell was just so that when the value it occupies its new location, it will use the format that was applicable at the new location of the cell.
Formats	which is pasted only format that applies to cells in the copy, where the format of the component consists of font formatting (font name, size, font color, style, bold / italic / underlined, borders, shading, the format of the form of writing numbers, and alignment).
Comment	which is pasted only notes (95) or comment (97/2003) which is inserted in the cell source.
All Except Borders	were pasted all of the components are similar in all options, only without including borders and shading.
None	copied object is pasted to the destination location without any operation, just put it. If the destination location has no content, then it will be over-write the alias stacked with new data.
Add	copied objects are added value to the value of the destination cell.
Substract	copied objects are subtracted value by the value of the destination cell.
Multiply	the copied object value multiplied by the value of the destination cell.
Divide	the copied object value divided by the value of the destination cell.
Skip Blanks	if the range is copied cells are empty, then when pasted to the destination cells that had been empty it will be left empty, meaning that it does not accumulate data on the destination cell.
Transpose	Paste the copied object in a different order. If originally in the form of columns Paste Special, Transpose will be poured in the form of lines, and vice versa.
Paste Link	Paste the copied object while providing a link relationship with cell source. If the source cell value is changed, then the cells become correspondentnya will also change.

Sometime in the work, you create a report read by others (your boss or another colleague who will follow up with you). To simplify the others watch on your worksheet, then the figures obtained from a formula will be more easily understood when read in the formula bar using the names of the cell or range.

For example, the following simple formula will be more elusive readers:

=(04*F4)*(100%-G4)

When compared with the formula below:

(UnitPrice * OrderNumber) * (100%-Discount)

Not to mention the more complicated formula.

Difficulty to understand the meaning of the cell address can be facilitated by naming the cell or range of cells with a name that represents its function, eg D4 cells easier when named the " UnitPrice ", while cell F4 as " OrderNumber ". And so on.

Provisions Name Cell / Range

• The name of the cell / maximum range using 225 pieces of character, but it is recommended to use the shortest possible origin can be understood its meaning. This is because the cell name will be displayed in the formula bar or the Name Box. If it is too long, space available will not fit.
• Writing the cell name should not contain spaces and hyphen (hyphen).
• Name of the cells do not just use numbers only because readers will be interpreted as a constant value.
• Naming cells may use capital letters and ordinary letters.

Here is an example of the cell name is wrong and right.

The wrong name	The correct name
TGL TRANSAKSI	TGL_TRANSAKSI
Account-Book-Big	AccountBigBook
2008	Th2008
%	Persen
A2	Desain_A2

Steps named cell / range:

1. Select the cell or range to be named.
2. Click the Name Range on the Formula tab, Group Named Cells
3. Select Name a range so that the displayed dialog box New Name
4. After typing the name of the cell in the Name box, click OK to close the dialog box.

Choosing a Cell or Range

There are several ways to choose or indicate to a particular cell that is possible when the position was not visible on the screen.

• Typing in the name or address is set in the Name box .


In the Name box, located to the left of the formula bar type the name or address the targeted cells and then press ENTER.


• To type an address in the Name Box Range.

Click the small triangle at the end of the Name box, and then type the address range in the Cell Name box, and then press ENTER.


If there is a cell or range that has been named, then you can call the name of the cell by clicking on the button in the Name Box so that the names of these cells displayed.

To select multiple names at once, after selecting the name of the first press and hold down the Ctrl key and click the next name, and so on.


• Choose a set with a command button Go To.

GO TO commands are shortcut keys Ctrl + G or pressing the F5 function key. After the Go To dialog box appears type the address of the cell that you are headed in the Reference box. Click OK, then the active cell will be at the address you're going.

• Utilizing the Go To Special command to select specific cells.
In the dialog box there is a button GO TO Special, which if you click will display the Go To Special dialog box.

Comment	select the cell containing the comment
Constants	select the cell containing the data constants (typed directly into the cell).
Formulas	selecting the cells containing the formula (there is an option formula number, text, 'logical, and errors).
Blanks	selecting the cells were empty.
Current Region	choose a region, which is a block containing the data without separated by blank rows or columns.
Current array	choose a range of array data.
Objects	select objects that were pasted to the worksheet.
Row differences	to look for differences from the other rows in the range you are looking.
Column differences	to look for differences from other columns in the range that you are looking for.
Precedents	when a formula is selected, then you choose the precedent, then Excel will select the cells are referred to as a reference of that formula.
Dependents	If a cell that contains the data to be referenced from a formula that is typed in a cell to another, so if you choose Dependent, then the active cell will select the cell which utilizes the contents of the cell as a reference formula.
Last cell	selecting cells located in the bottom right corner of the range that includes all of the cells that once you fill the data.
Visible cells only	select all cells except that when it was hidden.
Conditional formats	selecting cells that are defined using the Conditional Format.
Data validation	selecting cells that are formatted using the Data Validation.

Go To Special command in Excel 2007 can also be accessed via the Find & Select button, the Go To Special on the Editing group of the Home tab.

Recognizing and Utilizing Fill Handler

When a cell (or cells) was selected, then in the lower right corner there is a selection called mark Fill Handler. You can utilize this Handler Fill to create a series of data and management of other cells.



Disabling Fill Display Handler

Although by default .Fill Handler in an invisible, but you can hide this facility through the Excel Options dialog box, Advanced, and disable the option Enable fill handle and cell drag-and-drop.



Using Fill Handler for Creating Serial Number

Various kinds of data beamed (understood Excel) can be made with menyeretkan Handler to fill the column direction (downward) or in the direction of the line (to the side).


Creating a Data Series Threaded

If you have the data first, then draw the fill Handler will make the data series 1,2,3,4 ... etc.
Data A will happen Series A, B, C, D .... and so on.
April data series will take place in April, May, June, July .... etc.
Data Friday, the series will take place Friday, Saturday, Sunday •••• ff.
Data Number 100, will occur serial No. 100, No. 101, No. 102 •••• ff.


Creating a Data Series with Interval

To make the data series at intervals you should have at least two data showing before dragging Fill Handler intervals, for example as follows:

Cell A1 contains 1, A2 contains 5, if the handler dragged fill will occur 1, 5, 10, 15, 20 ... and so on. In this case Excel will calculate the difference between the first data and the second interval sebaga1 used to prepare the next series.

Creating a Data Series to Side and Down with the Fill Handler

If you must make a serial number with the order sideways and decreased as shown in the figure below, it is with a certain tricks can be done easily.


steps:

1. Type 51, 52, 53,54 in cell B3, C3, D3 and D3, then 55, 56, 57, 58 in cell B4, C4, D4, and E4.
   
   
   
2. Block the cells B3: E4, and then drag the fill handler who is on the bottom right corner of the block toward the bottom as much as you need.

Creating Series with Double Click on the Fill Handler

If the cell A1: A1OO (for example), pre-filled data in a row (not contain empty cells) then the column next to it you can make a serial number with an easier way. The way the question is to double click on Fill Handler.


In figure you see a few examples of serial composition, including the composition of the name, serial number, preceded another text. To provide input series, you simply select one cell, looking for a particular berinterval series, you are asked to choose at least two cells to take up interval of the series. In principle, double-click the fill handler is to copy these cells down following the right or left cells already filled data. If you choose are the two data down, there will be a series of data with the same interval between the first and second of data.

In particular if you type in the 1st Quarter (meaning the first quarter), the data series that takes place only until the 4th and not forwarded to the 5th Quarter.

The following kinds of series that can be accepted by the system:

1, 2, 3, 4, 5, 6, …
9:00, 10:00, 11:00, 12:00, ...
Mon, Tue, Wed, Thu, ...
Monday, Tuesday, Wednesday, Thursday, ...
Jan Feb, Mar, Apr, ...
Jan, Apr, Jul, Oct, Jan, ...
Jan-99, Apr-99 Jul-99, Oct-99, Jan-00, ...
15-Jan, 15-Apr, 15-Jul, 15-0ct, .. .
1999, 2000, 2001, 2002, 2003, .. .
1-Jan, 1-Mar 1-May, 1-Jul, 1-Sep, ...
Qtr3 (atau Q3 atau Quarter3) Qtr4, Qtr1, Qtr2, ...
1st Period, 2nd Period, 3rd Period, ...
Product 1, Product 2, Product 3, ...
Text1, textA, text2, textA, text3, textA, ...


Data Series Date Behind Another text

If at any time you require the writing of the data series in front of her date but no other text such as "Sales Results Date", then there is a little trick that should be done.

The following steps:

1. In cell F1 type 25 April 2007, after pressing ENTER in the cell by default will happen display 25-Apr-07.
2. Double click on the fill handler resulting in a data series of 25-Apr-07, 26-Apr-07, 27 -Apr-07 ... and so on until all the column next to the pre-filled data.
3. Choose the data and format by pressing Ctrl + 1. In the Cell Format dialog box select the Custom tab and in the Type box Dab " Hasil Penjualan pada tanggal " (include also quote mark) in front of d-mmm-yy.
   
   
   
4. Click OK then the data currently selected in front of him will grow with the special text that you insert earlier.
   
   
   

Copying Formulas Jumping Line

In the figure below there is a formula (formula) in cell C2. If pulled fill his handler will generate the number 0 in cell C3, C4, and C5. Therefore, to avoid the copy in an empty cell, select the cell source including the empty cells are desired, namely C2: C5. Further drag it fill handler corresponding multiples of 4 cells, then the result will be as shown in the following figure.



Techniques to Make More Series

You try to complete mastery of the technique as follows:

1. Type 1 Jan 2000 in line 1.
2. Drag it fill handler using the right mouse button until the 20th row and then release. Shortcut menu will appear next to it.
3. Select Fill Weekdays, then the result will be seen in the Figure below.

Weekdays are the days of work a week (without Saturday and Sunday)
Also try the other menu options to see the resulting effect.



Enter data using the Custom Fill Series
You can create a Custom Fill series in accordance with your own, such as making order of the day in Indonesian Monday Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday, or any other sequence.

Therefore the system Excel with the option Regional English (US) does not understand the names of days as above, then you should enter the series with the following steps:

1. Click the Microsoft Office button and click Excel Options.
2. On Personalized, under Top Option for working with Excel click the "Edit Custom List".
3. In the Custom Lists dialog box, type in the box List Entries Monday, Tuesday, Wednesday ... ff. Then click the Add button.
4. After the above process, Excel now recognize the order of the day in Indonesian.

In Excel 2003 you must already know the Fill command to fill the same data in sequence to the cells to the side or up / down. In Excel 2007 if you want to enter data to fill proceed as follows:

1. Type of data in the first cell.
2. Make the cell block to the right side.
3. Click the Fill button located on the Home tab, Editing group.
4. Select the Right. Then the data that you entered in the first cell before the cell block will fill you created.

If you make a cell block down choose Down, if the upward select UP, and if left to select Left.

If you intend to make the data series, for example 1, 2, 3 ... etc. or Sunday, Monday, Tuesday .... etc. choose Series ... so that the Series dialog box will appear.