Free Excel Tips

Excel Tips No 31 : Pivot Table, Create Table Resume

2009-07-25T12:33:00.000-07:00

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.

Excel Tips No 30 : Searching and Fill matching data automatically

2009-07-05T09:12:00.000-07:00

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

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

2009-07-03T23:10:00.000-07:00

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

Excel Tips No 28 : Create Check Point Sign

2009-07-03T19:54:00.000-07:00

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

Excel Tips No 27 : Rapid Cell Formatting

2009-06-30T07:22:00.000-07:00

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.

Excel Tips No 26 : Pick From Drop Down List

2009-06-27T21:22:00.000-07:00

This free microsoft excel tips no 26 is telling how to use Pick From Drop Down List menu in your Excel spreadsheet.
Some time we input an occurance data in excel. It's better to use pick from drop down list to make your time spent efficiently.

It's the step to do this:

Actually it's quite simple, just pointing your cursor below the list and right click your mouse here, and the drop down list will appear automatically.

May It Can be usefull for You

Excel Tips No 25: Use Rounding Function

2009-06-27T20:26:00.000-07:00

Excel Tips No 25: Use Rounding Function

This free microsoft excel tips no 25 is telling how to use Rounding Function. Some time we need rounding number behind decimal code. We will share several type of rounding function

There are 3 main function related to rounding function : Round, Roundup and Rounddown function.
General formula form is:

=ROUND(Value,Digits Number)

1. ROUND Function
This function is used to rounding value in general mode. If you have a value behind digits number you have specified less than 5, than it will be Round down, and If you have a value behind digits number you have specified equal or more than 5, than it will be Round Up.

2. ROUNDUP Function
In this function, every value behind digits number you have specified will be roundup

3. ROUNDDOWN Function
In this function, every value behind digits number you have specified will be round down

You can see the result from an example below.

May the tips can be useful for you

Excel Tips No 24 : The Art of Counting Function

2009-06-19T16:08:00.000-07:00

This free microsoft excel tips no 24 is telling The Art of counting Function. May be you often use count function. In this tips I will share how to count with a certain condition.

In this case we use COUNTIF function

General form of COUNTIF function is :
=COUNTIF(Range that will be put a criteria,Criteria)

For example, see the example below

1.Count in column "Mounth" where month is equal to January
=COUNTF(B4:B15,"January)

2.Count in column "Mounth" if Month not equal to January
=COUNTIF(B4:B15,"<>January")

3.Count in column "Mounth" if Month equal to January or Month equal to February. You can use "+" to represent "OR" Function
=COUNTIF(B4:B15,"january")+SUMIF(B4:B15,"February")

4.Count all Cost that only more than 100
=SUMIF(D4:D15,">100")

May this tips can be usefull for you.

Excel Tips No 23 : The Art of Sum Function

2009-06-17T16:27:00.000-07:00

This free microsoft excel tips no 2 is telling to The Art of Sum Function. May be you often use sum function, In this tips I will share how to sum with a certain condition.

In this case we use SUMIF function

General form of SUMIF function is :
=SUMIF(Range that will be put a criteria,Criteria,range that will be summed)

For example, see the example below

1.Sum Cost where month is equal to January
=SUMIF(B4:B15,"January",D4:D15)

2.Sum Cost if Month not equal to January
=SUMIF(B4:B15,"<>January",D4:D15)

3. Sum Cost if Month equal to January or Month equal to February. You can use "+" to represent "OR" Function
=SUMIF(B4:B15,"january",D4:D15)+SUMIF(B4:B15,"February",D4:D15)

4. Sum all Cost that only more than 100
=SUMIF(D4:D15,">100",D4:D15)

May this tips can be usefull for you.

Excel Tips No 22 : Hide & Unhide Column/Row

2009-06-17T15:14:00.000-07:00

This free microsoft excel tips no 22 is telling how to hide or unhide a column/rows. Sometime we want to make our data display is more simple, so it's better to hide some unnecessary column/rows. Here is the step to hide/unhide columns/rows in your excel spreadsheet

1. Click a column/rows you want to hide
2. Press right click to your mouse
3. Select hide to hide column/rows, and Select unhide to unhide it.

Excel Tips No 21 : Freeze Pane

2009-06-17T07:47:00.000-07:00

This free microsoft excel tips no 21 is telling how to Freeze Row/Column. When we have a big data that contain hundred of rows, and we scroll down the data, the column title will be hidden by the raw below the title column. I Think it's better if you freeze the pane, so wherever you scroll right/down you will alwayss see the column title. Here the step to do it.

Please see the figure below :

In this example we want that the 3rd Rows and column A, B & C will always be seen wherever we scroll the data.
1. Click the cell that will be the boundaries the raw and column (ie cell C3)
2. Click View - Freeze Pane.

Now, when you scroll down your mouse, title column (Third rows) will always be sheen, and when you scroll right your mouse column A, B & C (First Third column) will always be sheen.

To unfreeze, click View - Unfreeze Pane.

Excel Tips No 20 : Add Comment in a Cell

2009-06-14T08:17:00.000-07:00

This free microsoft excel tips no 20 is telling how to Add Comment in your spreadsheet Cell. A comment sometime needed to add additional information that make your data more easy to be understanded by your reader. Here is the step to add comment

Actually it is very simple : Just Right Click your mouse in selected cell and choose Insert comment option.

To remove comment,Right Click your mouse in selected cell and choose Delete Comment option.

May this tips can be useful for you

Excel Tips No 19 : Filtering Power

2009-06-14T06:50:00.000-07:00

This free microsoft excel tips no 19 is telling to use filtering menu and optimize your effort when processing data in your spreadsheet.
Here is the tip in filtering for you:

To activate filtering menu, you can click Menu Data - Filter or you can use shortcut CTRL+SHIFT+L .

1. After activating Filter Menu, You can filter content by Colour of the cell(Excel 2007).

2. You also be able to filter cell in a column that contain a specific word by click Number Filter - Custom Filter.

With this filter, you can filter a word within a sentence in a cell. For example, if we fill filter contain excel, so every sentence conain excel will be filtered : Microsoft Excel,Excel Tips,about Excel etc.

You also be able to filter cell with logical value, ie = less than, more than, between etc.

May this tips can be useful for you.

Excel Tips No 18 : Optimize Paste Special

2009-06-14T04:25:00.000-07:00

This free microsoft excel tips no 18 is telling to optimize using paste special.
We are very common in using copy & paste, but some excel user haven't familiar with this function yet. Paste special is a powerfull tools in some case. Here are the tips around Paste Special.

Activate paste special is very simple :
1. After you click copy (or CTRL + C), then click the right mouse and select paste special.
2. Paste Special menu windows appear :

Here is the menu:

Formula
If you select this option, then only formula of cell origin will be paste in cell destination

Values
If you select this option, then only Values of original cell will be pasted in cell destination. It's often used when we deal with formula. Paste all option of the formula may result a defferent value

Format
If you select this option, then only format of original cell will be pasted in cell destination

Comment
If you select this option, then only comment of cell original cell be pasted in cell destination

You can make a trial of this option.

Excel Tips No 17 : Remove Duplicate Data in Your Spreadsheet

2009-06-14T03:34:00.000-07:00

This free microsoft excel tips no 17 is telling to Remove Duplicate Data in Your Spreadsheet. Sometimes we receive a big Data, but if we check the data, actualy it's contain a lot of repetitive data. So in some case we need to remove the duplicate Data. Here is the simple step to do this.

1. Block Cell Area that will be cleaned from duplicate data
2. Click Data
3. Click Remove Duplicates
4. Done

May This Tips can be useful for You

Excel Tips No 16 : Using IF Function

2009-06-14T02:47:00.000-07:00

This free microsoft excel tips no 16 is telling how to use if function. For program developer if function is very familiar in their daily work. This tips is trying to introduce if function for excel user that may have't been familiar with this faction. Actualy it is quite simple, just read this tips.

The general formula using if function is :

=if(logical_test,Value for right logical test,Value for wrong logical test)

For example, please see the picture below.

May this tips can be useful for you.

Excel Tips No 15 : Print A Big Data With The Same Column Title

2009-06-14T00:52:00.000-07:00

This free microsoft excel tips no 15 is telling How to Print A Big Data With The Same Column Title. For Example is the data below. The Data contain thousand number of transaction (no 1000, 1001, ...), but we want the title of column (ie 3rd rows) will be printed on the first row for every page you print. He is the setting

1. Click Page Set up
2. Click "sheet" tab
3. Fill field "Rows to repeat at top (ie : Third Row, that contain column title) You can do it by click the red arrow and click Third Row or fill the constant number $3:$3.
4. Click OK
5. Now, every time you print this worksheet, the third row will be printed first time before another data.

May it can be useful for you.

Excel Tips No 14 : Simple way to Create & Copy a Worksheet.

2009-06-14T00:28:00.000-07:00

This free microsoft excel tips no 14 is telling a Simple way to Create & Copy Your Worksheet. The Common way to to this is create a worksheet and then copy all data from the old worksheet to the new worksheet. Here is the way to do this in more simple step:

1. Pointing your cursor in the worksheet Title
2. Press right click of your mouse

3. Select Move or copy
4. Select the new worksheet position
5. Check Create a Copy Option

May this tips will be useful for you

Excel Tips No 13 : Easy to Adjust Coloum Width

2009-06-13T23:00:00.000-07:00

This free microsoft excel tips no 13 is telling to adjust Column Width in your microsoft excel spreadsheet more easy. The common way to set column width is by select Option Home - Format - Column Width (Excel 2007) or Format - Column Width (excel 2003). Here is the simple way to Adjust colum width :

For example we want to adjust column "D"
1. Ponting your cursor between column name "D" and "E", till your cursor change to the Column adjustment sign

2. Drag the column adjustment sign rigt/left to adjust the column width.

Note :
If You want to adjust column width of column D & E for a same column width , you just need to block column D & E (By Click Letter D & E) and Drag the column adjustment sign rigt/left to adjust the column width.

If you double click the column adjustment sign, the column width value will be set following the longest cell in the same column.

I hope it will be useful for you.

Excel Tips No 12 : More Easy in CopyingYour Data

2009-06-13T22:51:00.000-07:00

This free microsoft excel tips no 11 is telling to copy data in your speadsheet more easy.
Sometime we need to copy a table to another area in our sheet. The common step is using cupy and paste cell to other area. This tips tell how to get the same result in the more simple step.

Please see the figure below,

1. Block table/cells area will be copied
2. Pointing your cursor in the edge of your blocked area (not in the corner), till your cursor change to the " + " sign. For example in the red circle area.
3. Right Click & drag " + " sign to the new area you want to copy the cells area to.
4. Relase your drag, and select copy here
5. Done

May this tips can increase your speed in processing data in your Microsoft Excel spreadsheets.

Excel Tips No 11 : More Easy in Moving Your Data

2009-06-13T21:41:00.000-07:00

This free microsoft excel tips no 11 is telling to move data in your speadsheet more easy.
Sometime we need to move a table to another area in our sheet. The common step is using cut and paste cell to other area. This tips tell how to get the same result in the more simple step.

Please see the figure below,

1. Block table/cells area will be moved
2. Pointing your cursor in the edge of your blocked area (not in the corner), till your cursor change to the " + " sign. For example in the red circle area.
3. Left Click & drag " + " sign to the new area you want to moved the cells area to.
4. Relase your drag, and done

May this tips can increase your speed in processing data in your Microsoft Excel spreadsheets.

Excel Tips No 10 : Include Print Date when You Print Report Automatically

2009-06-12T16:33:00.000-07:00

This free microsoft excel tips no 10 is telling to Include Print Date when You Print Report Automatically. Sometimes,it is important to put print date in your report to know when the report is printed. You can put it on report header or footer. This tips tell ho to write print date automatically.

Its simple, just type the formula below in your report :
="Report printed on "& TEXT(NOW(),"mmmm d,yyyy at h:mm AM/PM")
or
="Report printed on "& TEXT(NOW()," dd mmmm yyyy at h:mm AM/PM")

Here is the result :

You can modify the date format.
Just copy the formula to all your report and it will display the current date automatically.

Excel Tips No 9 : Create Data Validation

2009-06-11T16:11:00.000-07:00

This free microsoft excel tips no 9 is telling to Create Data Validation. Sometimes we need someone that filled our data inquiry, limited in a given number range value. It is very important to create a standard value in the questionnaires to get valid analytical result.

Here the step to do this

1. Select cell that data will be filled in (ie, Cell C5)
2. Click Menu Data - Data Validation
3. Fill Allow field by List
4. Make sure check in-cell drop down option
5. In Source Field, filled cell range for value validation. You can click red arrow and block cell range for value range, ie Cell E5:E9
6. Click Done.
7. Copy this cell (ie, cell C5) to another cell that will be filled a data/value.

May this data can be usefull for you

Excel Tips No 8 : Protecting Your Excel File with Password

2009-06-11T15:38:00.000-07:00

This free microsoft excel tips no 8 is telling to Protect Your Excel File with Your Own Password. It will be useful to increase protection of our file from unauthorized people to open it.

To put password in your file, do the following step :
1. When you save document click Tools
2. Click General Option
3. General Option Windows appear, fill your password to open or your password to modify. You can fill both password.
4. Clik OK, and retype your password to ensure you put the right password.
5. Click OK, and Your File now has been protected by your own password.

May it can be useful for you

Excel Tips No 7 : Make Your Sheet Tabs to be Colourfull

2009-06-11T09:19:00.000-07:00

This free microsoft excel tips no 7 is telling to make your sheet tabs to be colourfull. It may can make your worksheet display more beautiful and easier to switch to another sheet

Here is the step to do this

1. Right click on the choosed sheet.
2. Click Tab Colour
3. Select your preffered colour
4. Done

May this tips can be useful for you.

Excel Tips No 6 : Transpose Data in Same Row to be in Same Coloumn

2009-06-11T09:00:00.000-07:00

This free microsoft excel tips no 6 is telling how to ranspose data in a same row to be filled in a samecoloumn, or vise versa. :

Here is the step to do this

In this example, we will transpose data in cell B2 - F2 (same Row), to H3 - H7 (same coloumn) :
1. Click H3, and type the function =transpose(B2:B7)
2. Click Enter
3. Block H3 - H7, and Press F2
4. Press SHIFT + CTRL + ENTER

You finished transpose your data.

Excel Tips No 5 : List of The Most Frequently Used Short Cut

2009-06-10T16:15:00.000-07:00

This free microsoft excel tips no 5 is the list of the most frequently used short cut. I Hope it will increase speed when working with Excel spreadsheet.

CTRL+O - Open File
CTRL+A - Selects the entire worksheet.
CTRL+B - Make letters bold.
CTRL+C - Copy the selected Text or Cell.
CTRL+I - Make letters italic.
CTRL+J - Insert Hyperlink to acive Cell
CTRL+S – Saves your Files
CTRL+U - Make letters underline.
CTRL+V - Paste the selected Text or Cell.
CTRL+X - Cut the selected Text or Cell.
CTRL+Y - Redo the last action.
CTRL+Z - Undo the last action.
CTRL+F - Opens the search dialog box with the find tab selected.
CTRL+H - Opens the search dialog box with the replace tab selected.
SHIFT+F4 - Opens the search box and repeats the last Find action.
CTRL+K - Opens the Hyperlink dialog box
CTRL+N - Creates a new, blank workbook.
CTRL+P - Displays the Print dialog box.
CTRL+W - Closes the selected workbook window.
CTRL+; - Enters the current date.
CTRL++ - Displays the Insert dialog box to insert blank cells.
CTRL+- - Displays the Delete dialog box to delete the selected cells.
CTRL+SHIFT+L - Display Coloumn Fltering
CTRL+SHIFT+O - selects all cells that contain comments.
CTRL+SHIFT+: - Enters the current time.
CTRL+SHIFT+P - Opens the Format Cells dialog box with the Font tab selected.
SHIFT+CTRL+End - Block/Select till the endd of your sheet

Excel Tips No 4 : Disapear Zero in Your Table View

2009-06-10T15:35:00.000-07:00

This free microsoft excel tips no 4 is telling to Disapear Zero in Your Table View. Sometime zero value in our table display make the table become crowded, and disturb our view to analize the table. Here is the step you can do to disapear the zero value display

1. Block the table area you want to disapear the zero value.
2. Click Right mouse and select Format Cell
3. In Category Click Customize
4. In Type type #
5. Click OK

Or You can view the setting display below

Excel Tips No 3 : Combining Text in Excel

2009-06-09T16:06:00.000-07:00

This free microsoft excel tips no 3 is telling to combine text/word from several different cell.

To combine text/word from several different cell we can use function "concatenate"

In this example: we need to combine text in coloumn B4 and C4
The formula is "=concatenate(text1,text2,text3,...)
1. Click the cell you want to put the result
2. Type the Function, ie: in cell D4 "=concatenate(B4,C4)

If we want copy the result to another cell/sheet, dont forget to copy only the value of the cell:
1. Click the cell you want to copy
2. Click Copy or you can use short cut "Ctrl + C"
3. Click Cell Destination
4. Right Click your mouse, select "paste Special"
5. Select Value
6. Click OK

Excel Tips No 2 : Autogenerate Series of Number

2009-06-09T15:27:00.000-07:00

This free microsoft excel tips no 2 is telling how to generate series of number within a second. Just do the simple step below :

For Example we need a serial number in coloum B,

Here is the step to complete our task
1. Entry start number in coloum B6, ie : 1
2. Entry start number in coloum B7, ie : 2
3. Block / Select coloum B6 and B7,
4. Pointing your cursor in right and below corner of coloum B7, till you find "+" sign
5. Doube Click "+" sign and your serial number already generated : 1,2,3 and so on.

If you put the first and second number : 1 & 3 , you will get series : 1,3,5,7 and so on

Note : this tips will work if coloum A or C have a value/content, if coloum A or C are blank, you need to click "+" sign and drag it down till the cell you need.

Excel Tips No 1 : Change Sentence Case in Excel

2009-06-08T15:54:00.000-07:00

This free microsoft excel tips no 1 is telling to change Sentence Case in Excel to be title case or Upper/lower case in our spread sheet

You can use this function

=Upper(cell address) : Change case in the adreesed cell to CAPITAL CASE
example : =upper(C3) --> Change case in cell C3 to CAPITAL CASE

=proper(cell address) : Change case in the adreesed cell to Sentence Case
example : =proper(C3) --> Change case in cell C3 to Sentence Case

=lower(cell address) : Change case in the adreesed cell to lower case
example : =lower(C3) --> Change case in cell C3 to lower case

I hope this tips can help you

Hello World

2009-05-30T08:34:00.000-07:00

To day, I feel very happy, because I can start sharing Ms Excel tips for everone.

I hope from this blog, I can help all reader for improving his/her skill in ms excel.

Ms Excel is the most familiar spread sheet that used for small-medium data processing. For bigger size of data you can use Ms Access, and if it is not sufficient, you can use SQL, Oracle and other database.

In my experience, if we we can improve our skill in Ms Excel, It will time significantly to complete our job related on spreed sheet data processing in our home, office, and bussiness.

May the content can be an added value for you.

Don't heasitate to share tips or discuss to improve our skill in excel.

Best Regard