Excel Tips No 31 : Pivot Table, Create Table Resume

This free microsoft excel tips no 31 is telling how to create table resume. When we get raw data in a table form, sometimes we does't get a meaning of the table directly. It's need to be resummed to help "reading" the data meaning. To create table resumme, excel provide pivot table function. Pivot table is one of the most powerful tools in excel.

For example, we want to know the group of employee base on date of join.



Finding one by one data will consume a lot of time, and it maybe make frustration if we say about 100.000 raws of data or more.
Here the simple step to do it.

1. Click Insert Menu (Ms Office 2007)
2. Click Pivot Table
3. Pivot table window will be appear



4. In Table/range field, Click red arrow and block the table area, or you can click cell in the left - up corner of the table
5. Choose pivot table will be placed, you can select new sheet, or cell in the existiing worksheet



6. Click column will be resummed.
7. Click OK

Now, you have finished in creating table resume.
Continue Reading...

Excel Tips No 30 : Searching and Fill matching data automatically

This free microsoft excel training tips no 30 is telling how to Search and Fill matching data automatically. See the Figure below. We have 2 Table : Table A and Table B. We need to complete information in column D of Table A : "Year of Join". Unfortunately, information about "Year of Join" is provided in Table B, with the same/reference data is Personal Number. In the manual way, we can complete this by finding cell by cell Years of Join base on the same data, ie Personal Numbers.
Microsoft Excel provide simple way to do this ...



In This case, we will use vlookup Function


1. Click cell that will be filled with data, ie : D5 Cell
2. Click Formula menu fx and choose vlookup function (This function is in lookup group function)
3. Look up Menu Windows will be appear

4. On Look up value click red arrow, beside blank field. Click with reference data on table A (ie Personal Number). This data must exist in both Table (Table A & B). ie Cell B5. You can also fill this field by Typing reference cell, ie B5.

5.On Table array, click red arrow beside blank field, Block column from column which contain refence information (Personal Number) until column which contain information that will be pick up (Year of Join) on Table B (ie from column G to H).

6. On Collum_index_number, filled with column number of column that contain information will be pick up on Table B(column G,Personal Number ), Counting start from Reference Column in Table B (Column H, Year of Join). So in this case Column G is number 2 if counted start from column G. Filled Collum_index_number by 2.

7. On Ranged_Look_Up field, filled with: 0

8. Copy formula on D5 to all cell that will be filled on "year of Join" Column in table A.

9. It's already Finished

Table B can be a table from the same sheet, different sheet or different file.
It's important to copy - paste special the result if table B is not coming from the same file, Because it can make the result become error if the reference does not opened, and you choose update table when you open the file.

Formula also make your file become larger in size, and more time in loading the file.
So, if you do not need to make auto update, copy - paste special the result and delete the colum that contain formula to avoid this.

May this tips can be useful for you

Continue Reading...

Excel Tips No 29 : Transpose Data in Same Row to be in Same Column - Methode II

This free microsoft excel tips no 29 is telling how to ranspose data in a same row to be filled in a samecoloumn, or vise versa - second methode. Previously, I have told another way here : http://free-excel-tips.blogspot.com/2009/06/excel-tips-no-6-transpose-data-in-same_5928.html. Now, Iwill tell you another simple way to do This :



1. Block B2 - F2
2. Right click and select copy, or press Ctrl + C
3. Click destination cell, ie : Cell H3
4. Right click and select paste special
5. Select transpose option
6. Select "OK"

May this tips can be usefull for you
Continue Reading...

Excel Tips No 28 : Create Check Point Sign

This free microsoft excel tips no 28 is telling how to Create Check Point Sign in your spreadsheet.



It's simple:
1. Type "a" character.
2. Change the character type to Marlett
3. Adjust the font size
4. Done

You can copy paste this to another cell needed.
May this tips can be useful for you
Continue Reading...

Excel Tips No 27 : Rapid Cell Formatting

This free microsoft excel tips no 27 is telling how to format cell quickly. we often format cell in our spreadsheet to create an interesting display. The common way to do it is using right click and choose format cell.


here is the quick step to do this

If you have a cell that have a good formating, its just simple step to make another cell have the same format.
1. Click The cell that want to be copied of it's formatting.
2. Click Format painter, your cursor will change to be a brush
3. Click cell will be formatted


You can select multiple cell, rows or column to be copy formatted.
Continue Reading...