Want To See Possible Results Of CONCATENATE Function?

CONCATENATE is a function used to join text in one cell to another. You can find ‘concatenate‘ in paste function dialog box.

Click the ‘Paste Function’ icon in standard tool bar to open function dialog box. Select concatenate function from Text category.

A formula wizard appears in your screen that assists you in building the formula properly. You can drag it in case it covers your working area. Using mouse select the cells from which you want to join text. You can select several cells to form a long chain.

Tip: Use Shift and F3 keys at the same time to open Paste function dialog box.

If you want to type directly the formula please go through the following examples.

=CONCATENATE(G17,G18)

This formula simply joins text in the two cells G17 and G18

=CONCATENATE(G17," ",G18)

This formula joins text in the two cells G17 and G18 with a gap in between.

=CONCATENATE(G18," April’ 05")

This appends text ‘April’ 05’ with the value in G18.

Tips: You can even join texts that are in different cells using a simple method. Go through the previous examples using this easy way.

=G17 & G18

It joins text in the two cells G17 and G18

=G17&" "&G18

It joins text in two cells G17 and G18 with space in between.

=G18&" April' 05"

It appends text April’ 05 with the value in G18

Try yourself and simply your day to day task. This function is very easy to use.

:-)

How To Use 'IF' Function In Spread Sheet

‘IF’ worksheet function is used to check the logical result of a condition and based on that result, directing Excel to be perform an action. Actions include doing some calculation or returning to a value.

The logical result of a condition is either true or false. If the condition is true, the function performs an action. In case if the condition is false, the function performs another action.

The syntax is

=IF(CONDITION, if true what to be executed, if false what to be executed)

Go through some of these examples

=IF(E6>100,E6,””)

Here E6 is the cell, which has some value that may be pure number or result of any formula. If value of E6 is greater than 100 return E6 else return nothing (NULL)

=IF(E6>100,E6,0)

If value of E6 is greater than 100 return E6 else return 0

=IF(E6>100,(E6*5),E6)

If value of E6 is greater than 100 return multiplying E6 with 5, else return 0

=IF(E6>100,(E6*5),(E6*6))

If value of E6 is greater than 100 returns multiplying it with 5, else multiply with 6

=IF((E6+F6)>100,1,0)

If sum of E6 and F6 is greater than 100 return as 1 else 0

Important: Do not leave space within formula. Use commas ( , ) Parentheses correctly.

I hope the above examples helped you to get an idea about 'IF' function. Now, go ahead and try yourself to make some formulas using 'IF'.

Tips: You can even use more than one (up to 8) ‘IF’ function in a single formula.

=IF(E6=1,100,IF(E6=2,200,IF(E6=3,300,IF(E6=4,400,IF(E6=5,500,

IF(E6=6,600,IF(E6=7,700,IF(E6=8,800,0))))))))

Learn How To Summarize Your Data Instantly

If you want to summarize your data fast use Subtotal command to calculate automatically each similar groups and their grand total.

Info: Ensure yourself that your data is labeled and sorted properly before using Subtotal command.

You can find the subtotal command in the Data menu. Excel opens a dialogue box once you click it and displays all the labels in the drop menu ‘At each change in’ in the top of the dialogue box.

Now you need to select one label from the list. The label you selected becomes the Index and other labels are considered as items.

Indexing is done to create a tracking order for calculating subtotal of other items. If there is a change in data in the index Microsoft Excel stops summarizing the values till that point, makes a total and continues the summarizing next similar data.

You can use any one of the FUNCTIONS (COUNT, COUNTA, MAX, MIN, PRODUCT, STDEV, STDEVP, SUM, VAR & VARP) in the dialogue box to find value of items. You can find this option in the middle of the dialogue box.

In the bottom of dialog box all the labels are displayed in the scroll menu ‘Add subtotal to’ and Excel ask you to select one or more than one item that you want to summarize. Selected items are summarized by the FUNCTION based on Indexed label.

The other features in this dialogue box are:

Replace Current Subtotals: In case you summarized a particular field using Subtotals and want to change the index label or summarizing function or items, open subtotal dialogue box make necessary changes in inputs and enable ‘Replace Current Subtotals‘ option. Excel will remove existing subtotal and replaces with the new one based on your input.

Page break between Groups: If data taken to summarize is huge and it is required to take print out for each item, you need to enable the option ‘Page break between Groups’. Excel prepares page break between each group.

Summary below data: This option helps you to get summary details below each summarized item.

Tips: If you want to remove subtotals and get back original data, open subtotal dialogue box and select ‘Remove All’.

The result will have outlines that helps you to overview the data. Outlines help you to expand or collapse the nested items without affecting the data. You can turn off outlines if not required. Open options dialogue box and disable ‘outline symbols’ from windows options in the view tab.

Tips: Press Ctrl key and numerical key 8 at the same time to turn ON or OFF outlines.

Want To Manage WorkBook Easily?

If you aware about Excel file structure, the workbook handling task would become very easy. How a normal textbook has its name and number of pages within it, a Workbook also has a title and number of worksheets within it.

You can see worksheets in the sheet tab, which is just above the status bar. The default number of worksheet in a Workbook is three. Using the following path you can customize the number of worksheets to be opened every time when you create a new file.

Tools => Options => General tab => Sheets in new workbook and change the number.

Inserting worksheets:

To insert an additional worksheet, follow any one of the options below:

1. Press Shift and F11 key at a time.
2. Select ‘Insert worksheet’ from Insert Menu
3. Keep your mouse on sheet tab, which is above status bar and right click the mouse. A pop-up menu appears. Select ‘Insert’ from the pop-up menu. (Recommended option)

Tip: Using this pop-up menu option, you can even insert multiple worksheets. Hold shift key to select many worksheets and select ‘Insert’ from the pop-up menu.

Move or Copy Worksheets

Worksheets can be moved within workbook. It can also be moved to an existing workbook or a new workbook.

To move a worksheet, follow any one of the options below:

1. Select ‘Move or Copy’ from Edit menu.
2. Drag the sheet directly into the desired location.
3. Keep your mouse on sheet tab, which is above status bar and right click the mouse. A pop-up menu appears. Select ‘Move’ from the pop-up menu. (Recommended option)

A dialog box appears now. You need to choose the exact location where you want to move. Use ‘To book’ drop menu if you like to move it to an existing worksheet or a new worksheet.

Tip: To make a similar copy of worksheet please enable the option ‘Create a copy’ in the dialog box.

Info: Always save your workbook before performing this command.

Delete Worksheets

A workbook must have minimum one worksheet. Keeping any one worksheet available in the workbook you can delete one or more number of worksheets.

To delete a worksheet, follow any one of the options below:

1. Select ‘Delete sheet’ from Edit menu.
2. Keep your mouse on sheet tab, which is above status bar and right click the mouse. A pop-up menu appears. Select ‘Delete’ from the pop-up menu. (Recommended option)

Tip: Using this pop-up menu option, you can even delete multiple worksheets. Hold your shift key to select many worksheets and select ‘delete’ from the pop-up menu.

Info: Remember deleted sheets will be lost and unrecoverable so always save your workbook before performing deleting operations.

Hide and Unhide Worksheets

You can hide a worksheet if you do not like to keep it in display. You can unhide it whenever you want. Click ‘Format’ Menu, click ‘Sheet’ submenu and find Hide or Unhide options.

Info: Worksheets that are hidden by Visual Basic program cannot be Unhide by this method. We will discuss soon how to unhide a workbook that is made hidden by visual basic program.