If function statement

The IF function, one of Excel's logical functions, tests to see if a certain condition in a spreadsheet is true or false.


The syntax for the IF function is:

=IF ( logical_test, value_if_true, value_if_false )


logical_test - a value or expression that is tested to see if it is true or false.
value_if_true - the value that is displayed if logical_test is true.
value_if_false - the value that is displayed if logical_test is false.

Example Using Excel  IF Function:

This example will test to see if the value in cell D1 is less than 26. If it is, the IF function will place the number 50 in cell D2. If the value in D1 is not less than 20, the IF function will place the number 100 in cell D2.

1. Enter 30 into cell D1.
   
   
2. Click on cell E1 - the location where the results will be displayed.
   
   
3. Click on the Formulas tab.
   
   
4. Choose Logical Functions from the ribbon to open the drop down list.
   
   
5. Click on IF in the list to bring up the function's dialog box.
   
   
6. On the Logical_test line in the dialog box, click on cell D1. After this type the less than symbol ( < ) and then the number 20.
   
   
7. On the Value_if_true line of the dialog box, type 50.
   
   
8. On the Value_if_false line of the dialog box, type 100.
   
   
9. Click OK.
   
   
10. The value 100 should appear in cell E1, since the value in D1 is greater than 20.
    
    
11. To change the result in cell E1, change the number in cell D1 to 10 and press the Enter key.
    
    
12. The value 50 should now be present in cell E1 since the value in D1 is now less than 20.
    
    
13. If you click on cell E1, the complete function = IF ( D1 < 20 , 50 , 100 ) appears in the formula bar above the worksheet.

GO TO function and Special Cells method.

Go To function :-
Are you familiar with the Go To Special functionality in Excel? This is another chunk of functionality
that many Excel users either don’t know exists or don’t take advantage of. Check it out in Excel; select
Edit > Go To and then click the Special button at the bottom left corner of the Go To dialog box.
If you haven’t used this yet, let me tell you—this handy little dialog box can be a real time-saver.
Quick, what’s the easiest way to select all of the text values in the range, The answer is Press CTRL+G to display the Go To dialog box and then click the Special button.
Choose the Constants option with only the Text checkbox checked and click OK.

SpecialCells method :-
Most of the functionality served up by Go To Special can be accessed programmatically using the
SpecialCells method.
YourSearchRange.SpecialCells(Type As XlCellType, [Value]) As Range

The Type parameter is required and should be one of the xlCellType constants:-

xlCellType Constants for Use with the SpecialCells Method

Constant                                                 Selects
xlCellTypeAllFormatConditions        Cells of any format
xlCellTypeAllValidation                    Cells using Data Validation
xlCellTypeBlanks                             Empty Cells
xlCellTypeComments                       Any Cell Containing a comment
xlCellTypeConstants                        Cells with constant (or literal) values
xlCellTypeFormulas                         Cells with formulas
xlCellTypeLastCell                            The last cell in the used range
xlCellTypeSameFormatConditions     Cells having the same format
xlCellTypeSameValidation    Cells having the same data validation criteria
xlCellTypeVisible                              All visible cells

If you chose either xlCellTypeConstants or xlCellTypeFormulas for the Type parameter, you can
further define which cells to select using the optional Value parameter. By default, all constants or
formulas are selected. Use one or more of the following constants: xlErrors, xlLogical, xlNumbers, or
xlTextValues. For example, to duplicate the functionality you’d use something similar to this:

ActiveSheet.Cells.SpecialCells(xlCellTypeConstants, xlTextValues)

Note that you can specify more than one kind of value by adding constants together.

ActiveSheet.Cells.SpecialCells(xlCellTypeConstants, _
xlErrors + xlTextValues)

One more thing, SpecialCells requires special care. If SpecialCells doesn’t find any special cells, it
generates a run-time error, so be sure to use error handling in any procedure that uses SpecialCells.

Be a Rockstar on conditional Formatting

Excel conditional formatting is a hidden and powerful gem that when used well, can change the outlook of your project report / sales budget / project plan or analytical outputs from bunch of raw data in default fonts to something truly professional and good looking. Better still, you dont even need to be a guru or excel pro to achieve dramatic results. All you need is some coffee and this post to learn some cool conditional formatting tricks.

So you got your coffee mug? well, lets start!

The 5 tricks we are going to learn are,
1. Highlighting alternative rows / columns in tables
2. No-nonsense project plans / gantt charts
3. Extreme Incell graphs
4. Highlight mistakes, errors, omissions, repetitions
5. Create intuitive dashboards

1. Highlighting alternative rows / columns in tables:

Using MS Excel conditional formatting to change background color of alternative rows or columns
Often when you present data in a large table it looks monotonous and is difficult to read. This is because your eyes start interpreting the data as grid instead of some important numbers. To break this you try highlighting or changing the background color of alternative rows / columns. But how would you do this if you have rather large table and it keeps changing. The trick lies in Conditional Formatting. (Of course you can use the built-in auto format feature, but we all know how the default settings of various Microsoft products are like).

First select data part of the table you want to format.
Go to Conditional formatting dialog (Menu > Format > Conditional Formatting)
Change the “cell value is” to “formula is” (YES, you can base your formatting outcome on formulas instead of cell values)
Now, if you want to highlight alternative rows, the formula can go something like this,
=MOD(ROW(),2)=0
which means, whenever row() of the current cell is even, to change the coloring to odd rows, you just need to put =MOD(ROW(),2)=1 as formula
Also, if you want to highlight alternative columns instead of rows you can use the column() formula.
What if you want to change background color of every 3rd row instead, just use =MOD(ROW(),3)=0 instead. Just use your imagination.
Set the format as you like, in my case I have used yellow color. When you are done, the dialog should look something like this:
Excel Conditional Formatting dialog box, entering formulas to set the format
Click OK.
Congratulations, you have mastered a conditional formatting trick now :)

2. Creating a quick project plan / gantt chart using conditional formatting:

How to create Microsoft excel based gantt chart / project plan
Project plans / gantt charts are everyday activity in most of our lives. Creating a simple and snazzy project plan template in excel is not a difficult job, using conditional formatting a bit of formulas you can do it no time.

First create a table structure like shown above, with columns like Activity, start and end day, day 1, 2,3, etc…
Now, whenever a day falls between start and end day for a corresponding activity, we need to highlight that row. For that we need to identify whether a day falls between start and end. We can do that with the below formulas,
=IF(AND(F$8>=$D9, F$8<=$E9),"1","") Which means, whenever, the day number represented on the top row is between start and end we will in 1 in the corresponding cell. Next, whenever the cell value is 1, we will just fill the cell with a favorite color and change the font to same color, so that we dont see anything but a highlighted cell, better still, whenever you change the start or end dates, the color will change automatically. This will be done by conditional formatting like below: Excel Conditional Formatting Dailog, highlight a cell Congratulations, you have mastered the art of creating excel gantt charts now 3. Extreme In-cell Graphs: Incell graphing is a nifty trick that basically uses REPT() function (used to repeat a string, character given number of times) to generate bar-charts with in a cell. You can apply conditional formatting on top of them to give the charts a good effect. Here is a sample: Excel Condtional Formatting along with In-cell Graphs The above is a table of visits to Pointy Haried Dilbert ;) in the month of January 2008. As you can see I have highlighted (by changing the font color to red and making it bold) for the cells that have more than average number of visits in the month. I am not going to tell you how to do it, it is your home work :) 4. Highlight mistakes / errors / omissions / repetitions using conditional formatting: Conditional formatting errors Often we will do highly monotonous job like typing data in a sheet. Since the work is monotonous you tend to make mistakes, omit a few or repeat something etc. This can be avoided by conditional formatting. I use this trick whenever I am typing something or pasting a formula over a rather large range of cells (for eg. vlookup on annual revenue data of all your accounts, could run in to thousands of rows across multiple states /regions etc.). Lets see how you can highlight a cell when it has an error: First select the cells that you want to search for errors Next go to menu > format > conditional formatting and mention the formula as: =iserror() (see below)
Microsoft Excel conditional formatting dialog box
In the same way you track repetitions, a simple countif() would do the magic for you, or Omissions (again a countif())
Thats it, you have learned how to save tons of time by letting excel do the job for you. Sit back and sip that coffee before it gets cold.

5. Creating dash boards using excel conditional formatting:

As I said before you can use conditional formatting to create intuitive sales reports or analytics outputs. Like the one shown here,
dash board how to using excel

Here is how you can do it:

Copy your data table to a new table.
Empty the data part and replace it with formula that can go like this (I am using the above table format to write these formulas, may change for your data)
=ROUND(C10,0) & " " & IF(C9 Essentially, what we are doing is, whenever the cell value is more than its predecessor in the data table we are appending the symbol â–² (go to menu > insert > symbols and look for the above one) etc.
Next, conditionally change the color of cell to red / green / blue or pink (if you want ;) ) and you are done
Show it to your boss, bask in the glory :)


This page is copied from a website. you can visit that by clicking here

Moving and Copying Data and formulas in Excel

Hello friends how are you today we are learned about copying and paste feature of Excel. Actually copy and paste is not only two commands. Excel used the maximum feature of copying and paste function than the other software. So let’s start.

Copying cell contents by dragging and dropping: -

The easiest way to move or copy a cell or range of cells is to drag the cell or the range of cells to the new location and drop it.

1. Select the cell or range or range of cell you want to move.
2. Move the mouse pointer over the selections border. The pointer changes to an arrow.
3. Drag the pointer and the gray outline of the selection to the new location. Drag past the edge of a window scroll. Go to the point where you want to paste the data.
4. When you rich to the point where you want to paste the data simply release the mouse button when the gray outline is where you want to place the selected range.

To copy cell’s contents using drag and drop, do this:

1. Select the range of cells you want to copy.
2. Hold down the Ctrl key and move the pointer over an edge of the selection. The pointer becomes an arrow with a + (plus) sign.
3. Continuing holding down Ctrl key as you drag the edge of the selection to where you want the copy. The copy’s location appears enclosed by a wide gray border.

Using the drag-and-drop method, you can make only a single copy. You cannot copy to multiple locations or fill a range.

If you release the Ctrl key before you release the mouse button, the copy operation. The plus sign next to the arrow disappears. You can press Ctrl key again to switch back to copy operation.

Copying data across a Workbook:

1. Select the cell or range of cells you want to copy.
2. Click the Edit menu and choose Copy. Alternatively, click the Copy button on the standard toolbar or press Ctrl + C. The cells to copy appear, surrounded by marquee.
3. Select the worksheet to which you want to move data.
4. Select the cell at the at the top-left corner, where you want the duplicate to appear.

Using Data validation

Another great feature of Excel is data validation its validate data controls the creation of input criteria for a cell or range of cells. It can prompt a user for correct information or can display an error message if the data entered does not match the criteria.

To specify the data validation settings, do this:

1. Select a cell or range of cells for which you want to validate.
2. Click Data menu and choose validation….. .
3. In the data validation dialog box, click the settings tab property sheet.
4. Select the type of data to be validated from the Allow drop-down list.
5. Select an operator for validation from the data: drop-down list.
6. Enter the appropriate values in Minimum and Maximum: collapsable box.

To Specify an input message, do this:

1. Click the input message tab in data validation dialog box after specifying the settings property sheet.
2. Click and select the Show input message when cell is selected.
3. In the Title box enter a title for the message it is displayed in bold in the message box.
4. In the Input message: box enter a message this message is displayed below the title. The length of the message is up to 255 characters long. Press enter to start a new text in the message.
5. Click OK to save your settings.

To specify an error alert, do this:

1. Click Data menu and choose Validation…. .
2. Click the Error Alert tab in the data validation dialog box after specifying your settings property sheet.
3. Click the Show error alert after invalid data entered, so that the check box is checked.
4. Select a Style: for the message from the drop-down list.
5. Enter a title for the message in the Title: box.
6. Enter the text for the error message in the Error message: box.
7. Click OK to save your settings.

For check the criteria you select for your data validation select a cell which is under validation and then check the input message is appeared or not then enter a wrong data in this cell and press enter if error message is appeared then you success and it means which criteria you select is working.

So friends keep practicing for data validation it is very important for future use.

Auto Correct option in Excel

Excel has a great flexible feature of auto correct option which is the most important option which is commonly used in office and now those days it is used worlds wide. Auto correct will used for common typing errors and automatically corrects them as you type, it is also correct two initial capitals. You can also use Autocorrect in expand abbreviations means when you type incl, Auto automatically expand it to include. Autocorrect option is flexible because you can manage the Autocorrect option yourself.

To add AutoCorrect entries manually, do this:

1. Click the tools menu and choose AutoCorrect….. . The Auto correct dialog box appears.
2. In the replace text box, type the error or abbreviation as you usually type it.
3. In the with text box, type the correct spelling of the word or phrase or expansion of an abbreviation.
4. Click Add button to add the new entry to the list of Autocorrect entries.
5. Repeat all the steps from 2 for add more entries.
6. When you finished your work then click on the OK button.

To delete an Autocorrect entry, do this:

1. Click tools menu and choose Autocorrect….. . The Auto correct dialog box appears.
2. Select the entry you want to delete.
3. Click delete.
4. Repeat the steps from 2 how many entry you want to delete
5. When you complete your work click on the OK button.

To customize Autocorrect, do this:

1. Click the Tools menu and choose Autocorrect….. . The Autocorrect dialog box appears.
2. To turn off an option click on the check box you want to turn off the option.
3. When you finished making changes, click OK button.

Entering and Editing Data in excel

In Excel there are four distinct types of data that can reside in a cell. These types of data are:

a. Text

b. Numerals

c. Logical values

d. Error

Here we discuss about the all type of data in brief.

Text: -

The behavior of the text data are following

1. Text in a cell can contain any combination of letters, numbers, and keyboard symbols.

2. A cell can contain up to 32,000 characters.

3. If column width prevents a text string from fitting visually in a cell, the display extends over neighboring cells. However, if the neighboring cells are occupied the display is truncated.

Numerals: -

As the name of the data types it is clear that this data can contain all the decimal digits such as 0 to 9 which you ca addition, subtraction, multiplication, divides and also many mathematical and statistical calculation. We are most commonly used excel for use numerical calculation.

· Date and time are also numbers but they are in a special formatting. Let after formatting a cell if u enter 1-9 as a text string. Excel will interpret this as a date and display it as 9-jan or etc

· When a unformatted number does not fit in a cell, it is displayed in scientific notation.

· When a formatted number does not fit in a cell, number signs like hash (###) are displayed.

Logical Values: -

Excel support logical values like TRUE or FALSE in to cells. Logical values are often used in writing conditional formulas. Also there are many condition and formulas which are return logical values example if you say 3 = 4 the result is FALSE.

Error: -

It is a distinct type of data. Let in a formula you are try to divide a number by zero then the result is #DIV/0

Error value, some time when you enter a formula you can see a error which is #N/A its mean the formula is not applicable in this situation.

Date and Time in Excel

Hi friends today we are discuss about Date and Time format in Excel. Excel automatically understands date and time typed in most of the common ways. Normally when you entered a date or time in a cell Excel automatically converts it in a serial number. The serial number represents the number of days from the beginning of the century until the date you type.


If your entry is recognized as a valid date or time format you will se the date or time on screen. Correctly entered date appear in the formula bar with the format mm/dd/yyyy, regardless of how the cell is formatted.


To enter a date, do this:
1. Select the cell in which you want to enter the date.
2. Type the date into the cell with any of these formats. For example to enter a date 5th January 2008 type:
1/05/2008
05-Jan-08
05-Jan
Jan-08
Also you can enter more many types how much you can think to enter a date in Excel. And with newer version of Excel the types of entered a date format is also increased.
You must be separate a date entry with the following things /,-, or use a space to separate a date but we recommended that u must be separate with a / or -.


To Enter a Time, do this;
1. Select the cell on which you want to enter the time.
2. Type the time in any of the following formats. For example to enter 1:32 PM, type:
13:32
13:32:00
1:32 PM
1:32:00 PM
The first two examples are from a 24-hour clock. If use a 12-hour clock. Follow the time with a space and A, AM, P, PM in either upper or lower case.


To enter the current date/time in a cell, do this: -

1. To enter current date in a cell first select the cell and press Ctrl + ; keys together
2. To Enter current time in a cell first select the cell and press Ctrl + : keys together


Tips: - To format a cell in default date format, select the cell and press Ctrl + # (means Ctrl +Shift + #)
To format a cell in default time format, select the cell and press Ctrl + @ (means Ctrl +Shift +@)


Now I think all of you become expert in a date and time format of Excel. We are quickly start our one most important step which name is Formulas of Excel, it is a long lesson so friend be prepared.

Open a Workbook

Open a Workbook in Microsoft Excel.

For opening a existing excel workbook, do the following steps.

1) Click on File menu and Open
2) A open dialog box is appeared
3) From the look in: box specify the correct location of your file
4) In the File name box specify your file name. or select your file from the correct location.
5) In the file type box you can select your file type
6) Press the ok button.

Then you look the desired workbook is opened in the Excel window

Shortcut Key for opening a workbook is "Ctrl + O"

Friends every time I am said to you that my main aim is Share my knowledge with you, I am also invites my readers that please Came and help us with your best knowledge.
I am appreciate all of my readers .
so kindly post your comments in regularly basis.

You must be followed our following posts because all of those posts are related to each other.

1) Getting started with Excel.

2) Creating a Workbook.

3) Saving a workbook.

4) Closing a workbook.

Closing a Workbook Window

After finishing your work in worksheet you need to close this workbook.

We prefer before closing a Excel workbook don’t forgot to save your work.

If you not do this and make some changes since last saved then before closing a workbook Excel displays an alert box asking whether you want to save your work before closing.

To close a Workbook: Follow these Steps

1) Click the File menu and choose Close. Shortcut Key for close a workbook is

(Ctrl + W.)

Alternative method for closing a workbook Click the closed button on the right edge.

If the workbook closed it means no changes have been made the work book since it was saved last.

2) If you have made some changes to the workbook since the last save and forget to save it. The alert dialog box appears. In the alert dialog box Excel asked to you that, do you want to save changes you made since after last saved.

The alert dialog box gives you three choices. The choices are following.

1) Yes: - If you click on yes button excel save all your work in the existing Workbook

Note: - In some version of excel you look after clicking yes a save as dialog box appears, now you can saved your file as a different name or overwrite the existing file.

2) No: - If you click on No button Excel ignore all of your changes since last saved and close the work book

3) Cancel: - If you click on cancel button Excel cancel the closed command.

Note: - You can used the cancel button if you think forget to save your work and little confused that you do the correct thing or not

You must be followed our following posts because all of those posts are related to each other.

1) Getting started with Excel.

1) Creating a Workbook

2) Saving a workbook.

3) Open a workbook.