Selasa, 06 Desember 2011

alice in excelland

untuk istriku yang hari ini sedang berulang tahun...
cup cup cup mmmmuach.. :)
Read More

Custom Auto Fill in Excel


Custom auto fill is a function used to auto fill custom text, name, number etc.. It is particularly useful when you have to type the same text again and again which is very irritating. This trick will help you save time and make you more efficient at work.

Here is what we are going to do today.
  1. Look at the basic auto fill function in excel.
  2. Creating custom list of auto fill in excel (i.e. user defined auto fill).
Basic Auto Fill Function
This example will give you an idea of auto fill function.
  1.  Type "1" in cell A1.
  2.  Press Ctrl + Enter on keyboard.
  3.  Move your mouse pointer to the bottom right corner of the handler till it changes into a black " + "sign.
  4.  Hold Ctrl and then click and drag downwards. You will notice that the column is filled with ascending    number.



In a similar way you can auto fill name of months and days.The only thing you have to remember in case of months and is that you don't need to hold control. Simply type the first month and drag along the direction in which you want months to appear. You can either drag horizontally of vertically depending on your requirement. It works in both the directions.


This function is particularly useful in case of formulas. Suppose you have formula in C1 for adding values in column A and column B. See image below for description.

Type in the formula "=A1+B1" in cell C1.
Press Ctrl + Enter on the keyboard.
Move your pointer to bottom right corner till it turns into plus sign and then click and drag to fill the sum of column A and column B.

Custom Auto Fill Function.
To make custom auto fill possible, we must first define the custom fill i.e what we want to fill. Instruction to do this is below.
Click on Office button at the top of excel 2007 sheet.
Click Excel option at the bottom right corner of the office button window.
Click Popular.



Click on Edit Custom Lists... button
Type the custom list in List entries separated by comma.
Click Add and then OK.


Now go to excel sheet and type India and then click and drag. The columns or rows will auto fill with the custom values we just created. Your comments will be greatly appreciated to improve these tutorials. 
Read More

Custom Auto Fill in Excel


Custom auto fill is a function used to auto fill custom text, name, number etc.. It is particularly useful when you have to type the same text again and again which is very irritating. This trick will help you save time and make you more efficient at work.

Here is what we are going to do today.
  1. Look at the basic auto fill function in excel.
  2. Creating custom list of auto fill in excel (i.e. user defined auto fill).
Basic Auto Fill Function
This example will give you an idea of auto fill function.
  1.  Type "1" in cell A1.
  2.  Press Ctrl + Enter on keyboard.
  3.  Move your mouse pointer to the bottom right corner of the handler till it changes into a black " + "sign.
  4.  Hold Ctrl and then click and drag downwards. You will notice that the column is filled with ascending    number.



In a similar way you can auto fill name of months and days.The only thing you have to remember in case of months and is that you don't need to hold control. Simply type the first month and drag along the direction in which you want months to appear. You can either drag horizontally of vertically depending on your requirement. It works in both the directions.


This function is particularly useful in case of formulas. Suppose you have formula in C1 for adding values in column A and column B. See image below for description.

Type in the formula "=A1+B1" in cell C1.
Press Ctrl + Enter on the keyboard.
Move your pointer to bottom right corner till it turns into plus sign and then click and drag to fill the sum of column A and column B.

Custom Auto Fill Function.
To make custom auto fill possible, we must first define the custom fill i.e what we want to fill. Instruction to do this is below.
Click on Office button at the top of excel 2007 sheet.
Click Excel option at the bottom right corner of the office button window.
Click Popular.



Click on Edit Custom Lists... button
Type the custom list in List entries separated by comma.
Click Add and then OK.


Now go to excel sheet and type India and then click and drag. The columns or rows will auto fill with the custom values we just created. Your comments will be greatly appreciated to improve these tutorials. 
Read More

Sabtu, 03 Desember 2011

akrobat

Read More

Jumat, 02 Desember 2011

kutub

Read More

Kamis, 01 Desember 2011

Combo Box Dynamic Charting in Excel




In this excel 2007 tutorial I am going to show you how to create a drop down combo box to chart source data. We will create a drop down box in excel, which will show the name of the cities in the drop down list. On selecting a city in the drop down list, it will automatically create and display sales data in the form of chart in excel. So let's get started.

Create Sample Source Data.
Open excel 2007 and make a table similar to this.


Create Drop Down Combo box.
1. Go to  Developer  tab.
2. Click " Insert " in Controls
3. Select " Combo Box " in Form Control.

4.Hold down "Alt " and drag towards right to fill cell " H2 " completely.

Format Control for Combo Box
1. Right click on combo box and select "Format Control".

2. Select City Names in Input range in format object dialogue box.
3. Click " Cell link " and select cell " G1 ".
4. Click OK.


Checking Combo Box
1. Click on black triangle on combo box.
2. Select any city from drop down box.


Creating Chart with Source Data
1. Click " A10 " and type " =INDEX(A3:A6, $G$1) "and hit Enter.

The city you selected earlier from drop down box will appear in cell A10.

2. Again click A10.
3. Click on the bottom right corner of the cell and drag horizontally to auto fill all the months in row 10.
4. Click A9, hold shift on keyboard and then click on cell F10 to select all the data for charting.


5. Go to Insert > Charts > Column and Select Clustered Cylinder chart.
6. Click on chart and Move it Just below the combo box. 


Now try changing city from the drop down box and you will observe that it charts data for a particular city selected from the drop down box. Comments regarding improving the tutorials will be highly appreciated.

Referred Links
Combo Box Animation (Flash Lite Tutorial)
Read More

Combo Box Dynamic Charting in Excel




In this excel 2007 tutorial I am going to show you how to create a drop down combo box to chart source data. We will create a drop down box in excel, which will show the name of the cities in the drop down list. On selecting a city in the drop down list, it will automatically create and display sales data in the form of chart in excel. So let's get started.

Create Sample Source Data.
Open excel 2007 and make a table similar to this.


Create Drop Down Combo box.
1. Go to  Developer  tab.
2. Click " Insert " in Controls
3. Select " Combo Box " in Form Control.

4.Hold down "Alt " and drag towards right to fill cell " H2 " completely.

Format Control for Combo Box
1. Right click on combo box and select "Format Control".

2. Select City Names in Input range in format object dialogue box.
3. Click " Cell link " and select cell " G1 ".
4. Click OK.


Checking Combo Box
1. Click on black triangle on combo box.
2. Select any city from drop down box.


Creating Chart with Source Data
1. Click " A10 " and type " =INDEX(A3:A6, $G$1) "and hit Enter.

The city you selected earlier from drop down box will appear in cell A10.

2. Again click A10.
3. Click on the bottom right corner of the cell and drag horizontally to auto fill all the months in row 10.
4. Click A9, hold shift on keyboard and then click on cell F10 to select all the data for charting.


5. Go to Insert > Charts > Column and Select Clustered Cylinder chart.
6. Click on chart and Move it Just below the combo box. 


Now try changing city from the drop down box and you will observe that it charts data for a particular city selected from the drop down box. Comments regarding improving the tutorials will be highly appreciated.

Referred Links
Combo Box Animation (Flash Lite Tutorial)
Read More
Backlinks to my website? ping fast  my blog, website, or RSS feed for Free Tips dan Trik Sukses Bisnis OnlineindotradingUAW BMKGNTTfree indotradingcms | indotradingsakpa
Search Engine
www.e-referrer.com