Microsoft Excel VLOOKUP function-finds the value of MS Excel database or in the table.

Consider a simple spreadsheet in Microsoft excel, including C column A data table with the following:

Employees of column A unique Rep number column B-name column C of their salary

Assume are 99 people (consider the column headings mean the end table row 100 in) table. To search someone's salary, number of employees. Using Excel functions to do can be done?

The answer is sure to specify that only exact matches well in searches, person details lookup tables (that contains the name of the person or employee number i.e., table) returns the value in the third column.

To verify this behavior on the cell E1 (for example, 12345) the personnel number, type and cell F1 let's type the following formula.

= VLOOKUP (E1, A1:C100, 3, FALSE)

There are arguments used here four; (is a bit of information passed in the parenthesis function arguments) what each is located here.

E1-this is the number of personnel to search for our table A1:C100-tables have seen a number of employees is. Looking for things we in the lookup table (personnel number is here), must in the first column of the lookup table. 3-Columns that are returned we (here, third in the table column values: i.e., salaries) false-in other words, should we do an exact match. If you don't specify this, can be discovered, 12220 this dire consequences and Excel. Many people use 0 instead of the long travel time to enter the FALSE, (the same result as) decide.

One use the VLOOKUP function in Excel is: to return to the database in a particular field value. Our range searched value when use of the other is the subject of another article.

Andy Brown is a Microsoft Excel trainier and wise OWL business solutions developers. MS Excel courses for more information, a wise OWL here http://www.wiseowl.co.uk/excel/courses/index.htm, and then you can try some exercises in the VLOOKUP function in Excel http://www.wiseowl.co.uk/training/exercise-list/t-1621.htm.

Excel table or PivotTable?

In Excel, there are tables and pivot tables. You'll wonder why you would need to create a table when the entire worksheet already looks like one. And did you hear about pivot tables and complex as they are. To be able to work effectively, it helps to know what each one does and when to use one or the other.

An Excel table is simply a set of rows and columns in a worksheet that contains data related and is displayed in a table format. If you have a large list of data, it is often useful to display the data in that table. Not only as a guide to related data table is to organize, is also useful for calculating values and totals and grand totals.

Using a table, you can more easily:

Manage and analyze data independently of data outside of one of the many table formats to make it easier to view data and scanAdd calculated columns to calculate instantly valuesUse a total row to calculate and display data from totalsFilter in the columns of the table to display only the data that you want to quickly parse the tableApply

If you're trying to extract meaningful information from data, for example, to find out which products are selling best over time, you can use a PivotTable report instead of an Excel table. A pivot table is an interactive table that summarizes large amounts of data quickly, that can then be analyzed in detail.

By using a PivotTable, you can more easily:

Display the exact data that you want to analyzePivot data for display by different data anglesFocus on specific details by expanding or collapsing the data or by applying templates to filtersMake data comparisonsDetect data, reports and trends data

For more information about Excel tables or PivotTables, see the video or the following articles:

Video: create an Excel table

Create or delete an Excel table in a worksheet

Video: Create a PivotTable report

Create or delete a PivotTable or PivotChart view

--Frederique Klitgaard

View the original article here

Adding a table of content for the workbook – it's easy, I promise!

Sometimes workbooks can be very large and difficult to navigate. Only so many tabs fit at the bottom of the screen, and it is difficult to know how long is each worksheet. Excel does not provide a built-in way to add a table of content in a workbook; However, there is a way! In this post, I'll show you how to add a new worksheet to the workbook called "TOC" (TOC). This sample uses Excel 2010.

On the sheet of TOC, column lists the name of each sheet, and includes a hyperlink to the appropriate worksheet. Column b lists (which is the workbook) the number of the worksheet and the number of pages contained in this worksheet (how many printed pages would be). You must use Visual Basic for Applications (VBA), but do not be frightened by the fact that you need to code — is actually very simple, and I'll walk you through it step-by-step.

First, you must add code to the workbook and to do that you need Developer card. If you do not usually work with code in Excel, you probably will not see the development tab in the Ribbon. To view the development: tab

Click the File tab. The Guide below, click Options on. Click to customize the Ribbon. In Customizes the Ribbon, select the check box Developer.

Now you can create a macro:

development tab, in the code group, click Visual Basic.

In the Visual Basic Editor, Enter menu, click form on. In the code window of the module, type or copy the following macro code:

4. In the Visual Basic Editor, Files menu, click close and return to Microsoft Excel.

5. to save the workbook with macro code, select Save File menu and save the file to a macro-enabled workbook (.xlsm).

You are almost ready to run the code. You may need to change the macro security settings to enable the macros. To set the security level temporarily to enable all macros, do the following:

You are almost ready to run the code. You may need to change the macro security settings to enable the macros. To set the security level temporarily to enable all macros, do the following:

development tab, in the code group, click the Macro Security.
In Macro Settings, click enable all macros (not recommended, potentially dangerous code can run) and then click OK on.
Note    To help prevent, it is recommended that you return to potentially malicious code any of the settings that disable all macros after you finish working with macros.

And now you can run the code that creates the table of contents worksheet!

development tab, in the code group, click macro.
In the Macro window, select the Create_TOC macro and click Run on.

View the original article here