MS Excel Look Up Formulaes

VLOOKUP Perhaps one of Excels most commonly used Excel Formulas is the VLOOKUP. It is also possibly the Excel formula that most people have problems understanding.

Stop #N/A! In Vlookup

Excel HLOOKUP Another one of Excels most commonly needed Excel Formulas is the HLOOKUP.

Left Lookup in Excel Excel is very rich in Lookup formulas, with perhaps the VLOOKUP being the most popular. However, the draw-back with all Excel's Lookup formulas is that they will only look in the left most column and return the result from the corresponding cell to the right. There are times when users need to lookup data in any column of table and return the corresponding cell to the left.

Excel Lookups With Array Constants Would like to show you what I call: In-Cell-Lookups. These are the perfect replacement for multiple nested IF functions.

Dynamic Formulas Rather than bog you Spreadsheet down with hundreds, if not thousands of formulas, use a single formula with flexible and changeable Arguments. In this example I will use the INDEX/MATCH functions nested together. You can also instruct the end formula to return the corresponding cell, to the match, on the left or right.  However, the the same principles can apply to most Excel formulas.

INDEX/MATCH Functions While the Vlookup Function is very useful, it cannot look in any Column, only the 1st. Also, it cannot offset x columns to the left or return the value x rows before or after the found value. An INDEX & MATCH combo will allow for all of this flexibility.As you may already know, we can use VLOOKUP, or INDEX/MATCH to locate the first occurrence of a specified value in a list, or table of data. However, Excel has no ready made formula that allows us to locate say the second, or third occurrence etc of a specified value.

Find Nth Occurrence in Excel

Excel Lookup Table VLookup is the perfect Excel formula for numerical values contained in a range. However if you tried to use VLookup with text in a table, it's use would be limited, For example surnames such as Smith, Smithson, Smithy, Smithson-Jacobs would create problems.

Dynamic Excel Lookups These are very handy for when you lookup data but cannot be sure which column your returned data should come from. In other words, users may have inserted a column within the table.

Lookup & Return Corresponding Result While we can use any of the links above for lookup formulas, all require a table of cells in a Worksheet. If you only have small number of items to return based on the value of another cell, we can do the lookup without leaving the cell!

Excel Hyperlinks How to get the most from hyperlinks. Hyperlinks that still work when a Worksheet name changes. Create a hyperlink to a Chart Sheet

Hyperlink to Lookup Result Excel is quite rich in Lookup type formulas, some of the more popular ones are VLOOKUP , INDEX/MATCH and HLOOKUP . These all do a great job in looking up a value we specify and then return a corresponding result. However, it's often the case that we need to go to the row containing the found value, or its offset return.

Dynamic Reports Here you can see how we can the Database functions to produce dynamic reporting from a table of data.

Multi-Table Lookup Use the Dependent Validation Lists method to tell Excel to lookup any chosen item in any table you tell it.

MS Excel Summing Formulaes

Excel SUM Formula Probably, the most widely used Excel formula, the SUM function in Excel is specifically designed to add values from different ranges, or one range. The SUM formula can be typed into a cell in Excel, or inserted via the Insert Function tool to the left of your Formula bar.

Excel Autosum Function Because adding numbers is probably the most common function that Excel is used for, Excel has a built-in Feature called AutoSum located on the Standard toolbar.

Array Formulas in Excel I strongly suggest you read this very important information on using array formulas in your spreadsheets. Array formulas can let you specify more then one criteria to Sum, Average, Count etc by. Many examples of how to use them.

Excel Conditional Sum Wizard The Conditional Sum Wizard is an Add-In to Excel that is used to summarize values in a list based on set criteria.

Sum With Multiple Criteria Examples of Excel formulas to sum a range of cells that meet multiple criteria. ,DSUM, SUMPRODUCT and SUM with an IF function/formula.

Sum Every nth Cell

Sum nth Largest/Smallest

Sum Cells by Color

Increase/Decrease Values If you have values on an Excel Worksheet that you need to permanently increase, or decrease you can use Paste Special. No Excel formulas needed!

Excel Subtotals In Excel we can use the Subtotals feature found under Data on the Worksheet Menu Bar to Subtotal a table of data.

Bold Excel Subtotals Here is how we can use Conditional Formatting in Excel to automatically bold the results of Subtotals.

Making the SUBTOTAL Function Dynamic How to use the one function (not feature as above) to perform a chosen operation on only visible cells after using Auto Filter.

Excel Data Tables Data Tables are a range of cells that are used for testing and analyzing outcomes on a large scale.

Sum Between Date Ranges

MS Office Formulae Tutorial

 

 

Basic Excel 2007 Formula Tutorial: Step 3 of 3

Mathematical Operators in Excel Formulas

 

Excel Order of Operations

Make MS Office Files Always Save In 2003

Not everyone has updated to the 2007 edition of Microsoft Office. So a problem arises as we send them a Word 2007 document without converting it into 2003 format. However, it is possible to make Word 2007 always save in 2003 format. By changing a few settings, we can make Word 2007 always save in 2003 format automatically. This can save our time as we won’t have to convert a document each time we have to send one. Follow these steps to make Word 2007 always save in 2003 format:

1.     Open Word 2007.

2.     Click on the Office button.

3.     Click on the Word Options button at the bottom.

4.     The Word Options dialog box will appear.5.     Select the Save tab.

6.     Select “Word 97-2003 Document” from the menu of “Save files in this format”.  

7.     Click on the OK button.

After this you will never have to worry about mistakenly sending the incorrect format of Microsoft Word 2007 to anyone. The same procedure can be used to change the settings of Microsoft Excel 2007.

How to Record Narration for Microsoft PowerPoint Presentation

Microsoft PowerPoint is used for normally presenting complex data in graphical form, with the help of charts, graphs. There are various helpful tools in Microsoft PowerPoint2010; there is one in particular that is also very handy. If you want to add narration that is voice over to your presentation, that can be beneficial for various purposes. You can do that by doing few simple steps, please read step by step guide below.

Note: Make sure microphone is working properly. If you do not have built in microphone, please make sure that plug is secure. 

Instructions:

From the “Start” Menu, click on “All Programs” option and Select “Microsoft PowerPoint 2010”. Either browse an existing file or complete the new one.

Auto Backup Of Excel Files

You can use AutoRecover to have Excel automatically save a backup copy each time you save a workbook. The backup copy provides you with a previously saved copy, so you have the current saved information in the original workbook and the information saved prior to that in the backup copy. Each time you save the workbook, a new backup copy replaces the existing backup copy. Saving a backup copy can protect your work if you accidentally save changes that you don't want to keep or delete the original file.

1. On the Tools menu, click Options.
2. On the Save tab, select the Save AutoRecover info every check box.
3. In the minutes box, type or select a number to specify the interval for how often you want to save files.

The more frequently your files are saved, the more information is recovered if there is a power failure or similar problem while a file is open.

Note: AutoRecover is not a replacement for regularly saving your files. If you choose not to save the recovery file after opening it, the file is deleted and your unsaved changes are lost. If you save the recovery file, it replaces the original file (unless you specify a new file name).

MS Office २००७ Tips

Documents as Drafts

One thing that annoys us about Word 2007 is that it doesn't automatically let you open a document in Draft view (which was the Normal view in earlier versions of Word). To enable this, click the Office button > Word Options > Advanced > General. Then click the box next to "Allow opening a document in Draft View."

Formatting Marks

Some people can live without Word's marks for spaces and paragraphs, but for those Word 2007 users who can't, go to the Office button > Word Options > Display. Then, under "Always show these formatting marks on the screen," check the box for spaces, paragraph marks, and more.

Page Breaks in Excel

Printing an Excel spreadsheet can be a hassle, but you don't need to go to Print Preview in order to see where a page breaks. Click the Office button, then under Excel Options, click Advanced. Under "Display options for this worksheet" click the box next to "Show page breaks."

Check Your Style

First, Word could check your spelling, and then your grammar; now it can even critique your writing style. If you're concerned about things like wordiness and improper use of the passive voice, have Word 2007 check for them. Click the Office button > Word Options > Proofing. Under Writing Style, select Grammar & Style from the dropdown. If there are particular areas you don't Word to scrutinize, click the adjacent Settings button and then uncheck the appropriate boxes.

Presentation's Resolution

With larger wide-screen displays becoming the PC-viewing norm, you might not want your PowerPoint presentation to go online formatted for an old-school 800x600 resolution. To bump up your presentation's optimal screen size, click the Office button > PowerPoint Options > Advanced. Under the General area, click Web Options, select the Pictures tab, and choose the screen size you want.

Old Office File Formats

The latest version of Office "grants" users new default file extensions that aren't compatible with previous versions; you're forced to download and install a plug-in. But if you want to make the old Office file formats your default ones, click Office, and then Options for the specific program you're in. Select Save in the left-hand column, and then under "Save documents," choose the old Office file extension from the pull-down menu next to "Save files in this format."