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.

Diffrence between VB and VBA (visual basic for application)

VBA is based on Visual Basic, a programming language derived from BASIC. BASIC stands
for Beginner’s All-Purpose Symbolic Instruction Code. BASIC is designed to be user-friendly, because it employs recognizable English words (or quasi-recognizable variations on them) rather than abstruse and incomprehensible programming terms. In addition to its English-like diction, BASIC’s designers endeavored to keep its punctuation and syntax as simple and natural as possible.
Visual Basic is visual in that it offers drag-and-drop programming techniques and many graphical elements. Visual Basic for Applications consists of Visual Basic implementations that share a common core of objects and commands, but also include application-specific objects and language elements. The set of objects available in each application differs because no two applications share the same features and commands. For example, some VBA objects available in Word are not available in
Excel (and vice versa), because VBA implements features and commands that Word has but Excel does not.
However, the large set of primary commands, fundamental structure, and core programming techniques of VBA in Word and VBA in Excel are the same. So you’ll find that it’s often quite easy to translate your knowledge of VBA in Word to VBA in Excel (or indeed in any VBA-enabled application).
For example, you’d use the Save method (a method is essentially a command) to save a file in Excel VBA, Word VBA, or PowerPoint VBA. What differs is the object involved. In Excel VBA, the command would be ActiveWorkbook.Save, whereas in Word VBA, it would be ActiveDocument.Save, and in PowerPoint, it would be ActivePresentation.Save.
VBA always works with a host application (such as Access or Word). With the exception of some large-scale standalone programs that are usually best created with Visual Studio Tools for Office, a host application always needs to be open for VBA to run. This means that you can’t build standalone applications with VBA the way you can with Visual Basic .NET or VSTO. If necessary, you can hide the host application fromusers so that all they see is the interface (typically user forms) that you give to your VBA procedures. By doing this, you can create the illusion of a standalone application. Whether you need to employ this technique will depend on the type of programming you do.

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

Shortcut Keys for Excel

Shortcut Keys
On most of Excels menu items you will see the shortcut key associated with it. To see a complete list push F1 and type "Shortcut Keys".

Quick Help
To get quick help on any menu item push Shift+F1 and click the menu item

Insert Today's Date
To insert Today's date push Ctrl+; (semicolon)

Insert Current Time
To insert the current time push Ctrl+Shift+: (Colon)

Show the Paste Function (Function Wizard)
Push Ctrl+F3

Show the GoTo dialog
Push F5

Show the Paste Names dialog
Push F3. This will only work if you have named ranges.

Name a Range
To name a selected range, click in the "Name box" (far left on the formula bar) and type a one word name.

Go To a Named Range
To go to a named range select it from the "Name box" (far left of the formula bar). Or push F5.

Edit a Named Range
To delete or edit a named range go to Insert>Name>Define or Push Ctrl+F3.

Headings as Range Names
Highlight your range including the headings and go to Insert>Name>Create or push Ctrl+Shift+F3.

Named Formula
To make a Name refer to a constant formula e.g. "TaxRate", go to Insert>Name>Define and type TaxRate in the "Names in Workbook" box and 36% in the "Refers To". Now enter =(10*TaxRate) anywhere on the Worksheet.

Named Range List
To obtain a list of all Named Ranges and where they refer, select any blank cell (make sure you have no data underneath or 1 column over) and go to Insert>Name>Paste then Paste List.

Nested Formulas
To help write nested formulas (more than 1 formula in a single cell) use the "Paste Function" i.e. Insert>Function or Shift+F3. Select the function that you need, enter the reference, number or text then select the drop arrow to the left of the formula bar to add more Formulas. Doing it this way ensures all your parentheses are in the correct places.

Debugging Formulas
To troubleshoot complex formulas select the cell containing it and then click the = (Equal sign) to the left of the formula bar, this will activate the "Paste Function". To step through your formula simply click in the part of the formula you want to debug.

Personal Help
To add your own text to any of the Office Assistants help files, push F1, enter your question then open the file. Go to Options>Annotate and type in your own text then click OK. You will now notice a paperclip symbol next to the heading, this will let you know that you have added your own Help in a way you will understand.

Different Help
Sometimes the Office Assistant is not very helpful to your needs, so try the "Context and Index" help by either clicking Help>Context and Index or selecting "Help Topics" from any "Help" file.

Customizing Toolbars
Right click on any Toolbar and select "Customize" or push Ctrl+Shift+F10 twice then "Customize". Now click the "Commands Tab" and drag menu items both on and off the Toolbars. If things get a bit messy simply click the "Toolbars" tab and click "Reset". This will return all menu items to their default.

Quick Charts
To create quick charts, click anywhere within your data and push F11.

Worksheet Template
Set up your Worksheet how you want it e.g. formatting, formulas etc then delete all other sheets in the Workbook. Now go to File>Save or Alt+F2 and select "Template (*.xlt)" from the "Save as Type". Type a name and click "Save" Now right click on the sheet tab and select Insert you should see your Template sheet.

Secret Menu
Click in any cell, then move your mouse pointer over any border of the cell until the mouse pointer changes to an arrow, right click and drag to it's destination and then release.

Secret Menu 2
Place a date in any cell, then move your mouse pointer over the bottom right corner of the cell (Fill handle) until the mouse pointer changes to a small black cross. Now right click and drag to any cell and release.

Quick Cell Move
Click in the cell(s) then move your mouse pointer over any border until the mouse pointer changes to an arrow, left click and drag to it's destination and then release.

Quick Cell Copy
Click in the cell(s) then move your mouse pointer over any border until the mouse pointer changes to an arrow, left click and hold down the Ctrl key and drag to it's destination and then release.

Change Formulas to Values
Click in the cell(s) with the formula(s) then move your mouse pointer over any border until the mouse pointer changes to an arrow, right click and drag to the next cell, now still holding down the right mouse button drag back to where you Start ed and release. Now select Copy here as values only.

Quick List
To quickly copy down the contents of a cell that has a list in the column to the left or right of it, simply click in the cell you want to copy and then Double click the Fill handle (little black square on the bottom right of the cell).

Fill Blank Cells Within a List
Let's say you have a list of entries in column A and within the list you have many blank cells. Here is a quick way to fill those blanks with the value of the cell above. Highlight column A, then push Ctrl+G and click Special then check the Blanks option and click OK. Now push Equals (=) then the Up arrow and finally holding down the Ctrl key push Enter.

Auto Fill
To fill a series across columns or down rows type January or Jan in any cell and place your mouse pointer over the bottom right corner of the cell (Fill handle) until the mouser pointer changes to a small black cross. Left click and drag down or across. This can also be done with Numbers, Weekdays, Quarters or any text that ends in a number e.g. Day1.

Custom Auto Fill
Type your list across columns or down rows. Now go to Tools>Option and select the "Custom Lists" tab. Click the collapse dialog box to the right of the "Import list from cells" box, highlight your range, click the expand dialog and then click "Import". Or type your entries in the "list Entries" box.

Adding Text to Formulas
To show a formula result and text or number(s) in the same cell type a & (Ampersand) after the formula then your text/number(s).

Adding Hidden Text to Formulas
Imagine you have a formula like: =$2018+$1056-4*$120. When you initially wrote it you knew what each number represented, but you come back later and can't remember. Add a hidden note to your formula by using the N() formula i.e. =$2018+$1056-4*$120+N("My Wage+Bonus-4 weekly loan repayments"). The N() function will convert text to zero.

Custom Format
You can format a cell to show any number or text without changing it's real value using "Custom Format". To see this type the number 20 in any cell then go to Format>Cells or push Ctrl+1. Select the "Number" tab and then select "Custom." Using any one of the pre-defined formats type "Twenty" (without quotations) or any text and then click "OK". To test it use the cell in any formula.

No More Chart Gaps
If you have a chart that is plotting empty text ("") or 0 (zero) from a formula then instead of using "" or 0 if the formula is False try using "#N/A" (without the quotations) or the formula =NA(). Or you can hide the Row(s) or Column(s). Either way Excel won't plot #N/A or hidden Rows or Columns.

My List
If you have a long list of Text with no blank cells between and you want to see a preview of what is in your list. Click in any cell within your list then right click and select "Pick from list", If you select one of the entries, Excel will insert it in the cell for you.

Remove Blank Rows
Highlight your range and go to Edit>Go to>Special and select "Blanks" then "Ok" now go to Edit>Delete or Ctrl+Shift+= (equal) and then select "Entire row" from the "Delete" dialog and click "Ok".

Sort Out Blank Rows
The quickest way to remove all blank rows is to select you range then go to Data>Sort.

See Formula cells
If you have a sheet full of formulas and you want to identify these cells at a glance go to Edit>Go to>Special and select "Formulas" then click "OK". Now go to Format>Cells or Ctrl+1 and select the "Patterns" tab and choose a color.

En Masse Changes
To make changes to more than one worksheet at the same time select one of the sheets, hold down your Ctrl key and click on each sheet name tab. Now any data entered one sheet will also be entered on the other(s). When you have finished right click on any of the sheet name tabs and select "Ungroup sheets".

En Masse Changes 2
Another way to have changes on one worksheet reflected on other sheets is to make all the changes you want on one sheet then hold down your Ctrl key and select the other sheet tabs. Go to Edit>Fill>Across Worksheets and Excel will give you 3 choices of what to copy to the other sheets i.e. "All", "Contents" or "Formats".

Worksheet Copy
Select the sheet name tab then hold down your Ctrl key and simply drag it to the position you want it.

Paste Reference
An easy way to reference another cell is to select the cell you wish to reference then right click and select Copy or Ctrl+C then select the cell you want the reference in, right click again and select "Paste Special" then click "Paste Link"

Absolute/Relative Toggle
If you have a formula you want to make absolute or relative then double click in the cell or F2 then place the insertion point anywhere in the cell address and push F4 1, 2 or 3 times.

Repeat
To repeat an operation push F4

Undo
To undo an operation push Ctrl+Z

Linked Picture
A good alternative to a textbox or any shape is a linked picture that reflects any changes made to its reference. To make one, copy your cell(s), select the destination cell and holding down your Shift key go to Edit b="" link<="" picture="">.

Run a Macro by Clicking a Cell
This is possible with use of VBA but let's face it most people don't know VBA so here is an easy way. Select the cell you want to run the macro and hold down your Shift key and go to Edit>Copy Picture then select "As shown on screen" from the "Copy Picture" dialog then hold down your Shift key again and go Edit and click "Paste Picture". Now right click on the cell picture and "Assign Macro".

Non Formula Result
Sometimes you just want the result from the Sum, Average, Min, Max etc from a group of cells without typing a formula in a cell. Excel allows you to do this very easily, first highlight the cells you want to evaluate then right click on the "Status Bar" and select the function you want and your result will be displayed in the "Status Bar".

Reduce File Size
When you have a workbook that is very large in size you can reduce this dramatically by saving the file as "Microsoft Excel Workbook (*.xls)" as apposed to "Microsoft Excel 5/95 Workbook (*.xls)". In other words avoid saving as multiple versions whenever possible. Also click here for much more details and other methods.

Cell Navigation
To move through a group of cells that you are working with without going outside the range highlight the group of cells and then use the "Enter" key to move through them.

Quick Formula Syntax
When writing formulas for Excel sometimes you just need a quick reminder of the formula syntax. In this is the case then type an equal sign followed by the function name and push Ctrl+Shift+A. For Example typing =Vlookup and then pushing Ctrl+Shift+A will give you: =vlookup(lookup_value,table_array,col_index_num,range_lookup). The non-bolded arguments are optional.

How to copy formulas without the reference changing
This can be achieved by either pressing F2 and then highlighting the formula, Copy, Enter then paste to destination. Or doing the same in the Formular bar. However, this is not much good for large amounts of data, so try this: Select the range of cells with Formulae, use the Ctrl key for non-contiguous ranges. Now go to Edit>Replace and Replace = with #. Copy and paste to your location and then simply use Edit>Replace # with =

How to copy and transpose formulas without the reference changing
In cell A1 of sheet 2 put: =Sheet1!A1 now copy this down a max of 255 rows. Now with the formulas selected go to Edit>Replace and Replace = with #. Now copy, select cell B1, go to Edit>Paste special and choose Transpose. Delete Column "A" and with Row 1 selected go to Edit>Replace and Replace # with =

Turn a List Upside-Down
1. Copy the list to another location using Copy, Edit>Paste Special>Value.
2. Now select all data in the list, go to Tools>Options>Custom Lists.
3. Ensure the list address is in the "Import list from cells:" and click "Import".
4. Now go back to the column next to your list and in the top cell place the LAST entry from your list.
5. Now in the cell below, place the second last entry.
6. Select both cells and double click on the Fill Handle (small black square bottom right).

The list should now be reversed. You could now also sort you original list using Data>Sort>Options, nominate your list then sort!

Formula Errors
Whenever typing one of Excels functions (especially nested ones) into a cell always use lower case. This way when you push Enter Excel will capitalize only the names of the functions you have entered correctly.

Entering Named Ranges Into Formulas
When you write a formula, sometimes you want to use a Named Range as one of the arguments for the formula, but you cannot remember the name. In these times simply push F3 when you reach the argument that you want the Named Range in and Excel will display the Paste Name dialog. Click the name you want then OK.

Optional Function Arguments
Sometimes you may not be sure what arguments in a function are optional and which are not. If your using the Paste Function (Function Wizard) then the non-bolded arguments are optional.

Sort by more than 3 Columns
Excels sort feature only allows to nominate up to 3 columns to sort by, here is how to get around this. The key to this is sorting by the last key first and working back to the first key. Say you data is in Column A:E and you want to sort by A, B, C , D then E

1. Select all of Columns A:E
2. Go to Data>Sort> sort by C then by D then by E
3. Click Sort
4. Now again with Columns A:E selected
5. Go to Data>Sort> sort by A then by B
6. Click Sort

Printing Workbooks
If you have quite a few Workbooks to print, go to File>Open from within Excel, select the Workbook(s) using the Ctrl key, then right click and choose Print.