A little high a little low, but how do you know? A little conditional formatting can help determine who falls where.
Example: Suppose you were assigned to test employees on computer skills and you wanted to know who received above-average scores. If you listed the scores next to the employee number in range H2:H36 then this is how we do it:
1. Select L2:L24.
2. Go to Format: Conditional Formatting.
3. Click the drop-down arrow and select Formula Is.
4. Enter the following formula: =L2>Average($L$2:$L$24)
5. Click the Format button.
6. Select Red in the drop-down Color menu.
7. Click OK twice.
All above-average scores will be red.
Note: When entering the formula in step 4, ensure that the cell references in the Average function are absolute.
While Excel's AVERAGE function ignores blank cells, it doesn't ignore cells that contain 0 (zero). This can result in inaccurate analysis of the data.
Example, let's say you want to calculate the average score for all students who took the final exam. Using the AVERAGE function, you might enter: =AVERAGE(B3:B23).
But if two students were absent and received a grade of 0, the result of the formula wouldn't give a true picture of the average grade. However, you can calculate the average and exclude the absentee students' grades, and this is how:
The entered formula first creates an array that includes only nonzero values in the range. The AVERAGE function then uses this array as its argument.
A chart is supposed to take all of your important information and put it into one, easy-to-read form. If you have an overwhelming amount of hard-hitting information, but no way to show it effectively, your point can be lost on your audience....
If your business' profit just skyrocketed in the last year and your chart doesn't show this at a glance, it may not be effective at all, and ultimately, all the time you spent preparing for and giving your presentation has been wasted. When creating a chart, there are three important questions you must ask yourself.
Is the chart type appropriate for my data?
Are all elements clearly labeled?
Does the design distract or confuse my audience?
Here, we'll explore the different chart types and answer these questions so you can create what's considered a good chart to get your point and data across effectively.
Did I use the right chart?
Most likely, if you don't understand the data on your chart or how to read it, your audience will be equally confused. Don't get carried away with fancy charts just because you think they might look better. A chart that is clear, and accurate, will always do the job better than a chart that looks great, but is confusing or misleading.
In order to guarantee that your chart is effective, you first need to check if the format you chose is appropriate for your data.
Columns, bars, or pie charts are generally a safe bet. They're easy to decipher and people are familiar with reading them. They are great for showing differences in size or growth.
However, when using a pie chart, be careful. Microsoft® Office Excel® will allow for functions that are irrelevant with that chart type, including plotting negative values. Pie charts are useful for showing how a total value, such as budget forecasts, breaks down into categories, but not for showing changes or comparisons.
Did I clearly label all my elements?
For your audience to be able to read your chart at a glance, all the parts need to be labeled clearly and concisely. The three main parts of a chart are the
Headline: should be descriptive and should not cause the audience to guess what the chart is going to be about.
Example: if you're talking about XYZ Company's profits for the year 2007, you wouldn't just write “Profits†as the headline. You would write "XYZ Company Annual Profits for 2007".
Data: should be accurate so as to not mislead the audience.
Legend: should either be color coded or use symbols to differentiate between the data being shown.
Is my design or color palette distracting?
Data can be enhanced with data graphics and a pleasing yet still readable color palette. A data graphic presents your data as a combination of textual and visual elements, such as flags and progress bars and enhances your data by applying shapes to show data that the shape contains.
To ensure that your color palette doesn't take away from the overall information of your data, stay away from bright colors that distract the eye and are hard to read. Typical colors that are successful and easy to read are shades of blue, orange, green, black, or red. Learn how you can print your graphs clearly in color or black and white helping you ensure even more success.
The good the bad and the just okay
Some graphs can be disguised as good, but they are actually either misleading or just plain nonsense.
Misleading Chart: Even if data is accurate, a chart that misleads can cause data too look much more or less impressive than it really is.
Tip: Take a step back and see if the chart is explaining the exact point you are trying to make. For example, if your sales figures are showing only an increase of 1% (which to you is not a lot) and your y-axis is set at a default that looks zoomed in too closely, you could be creating a visual story that your sales figures are higher than they actually are. View an example of a misleading visual based on y-axis figures not set to show the bigger picture. To update this view (in Excel 2007) use axes tools on Chart tools / Layout tab. On the y-axis, set the minimum value to a more appropriate one.
Nonsense Chart: Even if a chart was easy to create, if it doesn't make sense or is irrelevant, your chart is classified as a nonsense chart.
Remember what makes a successful chart
Good Chart: A chart is classified as good if it is easy to read and understand. It has a descriptive title, a data table that shows the data plotted on the chart, and the legend.
Complex yet Clear Chart: This chart may seem daunting to create because of all the data included but you can easily create a clear chart with complex data just by adding a second axis.
Paying attention to factors that affect the effectiveness of your chart makes it easy to create a visually pleasing addition to your presentation.
Excel includes a number of functions that allow you to remove printable characters, like spaces, from imported data. But what if your data contains nonprintable characters, like the ones shown in Figure A?
To clean up this data, Excel provides the CLEAN function. Follow these steps:
Excel displays the contents of the cells without the nonprintable characters. You can now copy the results to another part of the workbook using the Paste Special option to copy the values without the formulas (Figure C).
Sometimes, you may need to replace a formula with its results - either to preserve a static value or to optimize your sheet by reducing calculations. There’s a pretty simple trick for this, but a word of warning: Be sure you really want to wipe out a formula before you do it. (There could be undesirable consequences.)
In fact, a good practice is to create a backup copy of the workbook as a safety net in case things go awry.
To convert a formula, click in its cell and press [F2] to enable in-cell editing. Next, press [F9] to calculate the formula and display its results. Then, hit [Enter], and your formula will be replaced by the value it produced.
You can also copy the formula and use Paste Special | Values to paste the results someplace else, leaving the formula intact in its original location.
When you copy a formula from one cell to another, Excel will, by default, readjust the formula cell references to reflect the change. However, if you want the formulas to stay the same, this is how we do it:
1. Click on the cell containing the formula you want to copy.
2. Press [F2].
3. Click and drag to select the entire formula.
4. Click the Copy button in the Standard toolbar.
5. Press [Enter].
6. Select the cell into which you want to paste the formula.
7. Click the Paste button in the Standard toolbar.
When you copy an Excel formula in this manner, the formula copies as text and will not adjust its cell reference. You can also copy part of a formula this way. For example, you may want to include the formula as part of an If statement in another part of the worksheet.
'When concatenating date and text cell data in an Excel formula, you need to convert date cells to text to avoid unexpected results. If not, you end up with an unexpected result. Example, suppose cell H11 contained the text Due Date: and cell H12 contained a formula that calculated the date. H12 is correctly formatted for the date data type, m/d/yyyy. If you use the formula =H11&H12 to concatenate these cells, the result comes back with the serial date (such as, Due Date: 39054).
Because Excel ignores the formatting of H12, Excel returns the serial date unless the contents of H12 are converted to text before concatenating, as shown in the following formula:
=H11&TEXT(H12,\" mmmm d, yyyy\")
The correct result of this formula is Due Date: December 3, 2006.
For some strange reason, Excel likes to import fractions as dates. Example: let’s say you are want to import a column of data from an Access database table that contains fractions such as 21/25, 1/25, 2/35, and 30/61. You will need to format the column of cells containing the fractional data to an appropriate fraction format before importing, and this is how we do it:
When you import the data to that column, Excel will format each entry as a fraction. Keep in mind that if the data contained fractions such as 30/750, you would select Up To Three Digits (312/943) in Step 5. If you wanted to display your data as fractions over 100 (for example, 30/65 displayed as 46/100), you would select As Hundreds (30/100) in Step 5.
If you won’t be using the fractions in calculations, you can prevent Excel from changing your entries to dates by applying the Text format to the cell before you enter the data. In that case, in Step 4 select Text under the Category list and then click OK.
To me this is kind of lame, but it’s the best Excel can do!
Tired of typing the same list of items across a number of Excel cells for each worksheet you add
to your workbook? Then learn how to create a named formula that will enter the data for you.
Example, suppose you need to type the names of your Sales Regions in row 2 of every
worksheet. First, create the named formula by following these steps:
1. Press [Ctrl][F3].
2. Enter SalesRegions in the Names In Workbook Text box.
3. In the Refers To text box, enter the following array formula:
={\"North West\",\"NorthEast\",\"SouthEast\",\"South West\"}
4. Click Add and then click OK.
Then, in order to add this list to row 2 of your worksheet, select the range B2:E2, type
=SalesRegions in the formula bar, and then press [Ctrl][Shift][Enter].
You can also use this method to enter labels down a column. However, instead of using a comma
to separate the regions, you would use a semicolon, as shown below:
={\"North West\";\"North East\";\"South East\";\"South West\"}
Many people have the idea that building a pivot table is complicated and time-consuming, but it's simply not true. Compared to the time it would take you to build an equivalent report manually, pivot tables are incredibly fast. If you have well-organized source data, you can create a pivot table less than a minute. Here's how:
Select any cell in the data to start.
A very simple pivot table in about 30 seconds
To minimize problems down the road, always use good quality source data, organized in a tabular layout. "Perfect" source data will have no blank rows or columns, and no subtotals. Each column will have a unique name (on one row only), every field will have a value in every row, and columns will not hold repeated groups of data (i.e. month names, location names, region names, etc.).
Video: How to quickly fill in missing data
Perfect data for a pivot table!
When you first create a pivot table, use it to generate a simple count first to make sure the pivot table is processing the data as you expect. To do this, simply add any text field as a Value field. You'll see a very small pivot table that displays the total record count, that is, the total number of rows in your data. If this number makes sense to you, you're good to go. If the number doesn't make sense to you, it's possible the pivot table is not reading the data correctly or that the data has not been defined correctly.
300 first names means we have 300 employees. Check.
Although it's a lot of fun dragging fields around a pivot table, and watching Excel churn out yet another unusual representation of the data, you can find yourself going down a lot of unproductive rabbit holes very easily. An hour later, it's not so fun anymore. Before you start building, jot down what you are trying to measure or understand, and sketch out a few simple reports on a notepad. These simple notes will help guide you through the huge number of choices you have at your disposal. Keep things simple, and focus on the questions you need to answer.
If you use an Excel Table for the source data of your pivot table, you get a very nice benefit: your data range becomes "dynamic". A dynamic range will automatically expand and shrink the table as you add or remove data, so won't have to worry that the pivot table is missing the latest data. When you use a Table for your pivot table, the pivot table will always be in sync with your data.
To use a Table for your pivot table:
Video: Use a table for your next pivot table
Creating a simple Table from the data using (Ctrl-T)
Now that we have a table, we can use Summarize with a Pivot Table
6. Use a pivot table to count things
By default, a Pivot Table will count any text field. This can be a really handy feature in a lot of general business situations. For example, suppose you have a list of employees and want to get a count by department? To get a breakdown by department, follow these steps:
Employee breakdown by department
In many pivot tables, you'll want to show a percentage rather than a count. For example, perhaps you want to show a breakdown of sales by product. But, rather than show the total sales for each product, you want to show sales as a percentage of the total sales. Assuming you have a field called Sales in your data, just follow these steps:
See the tip below "Add a field more than once to a pivot table" to learn how to show total sales and sales as a percent of total at the same time.
Changing value display to % of total
Sum of employees displayed as % of total
Because pivot tables summarize data, they can be used to find unique values in a field. This is a good way to quickly see all the values that appear in a field and also find typos, and other inconsistencies. For example, suppose you have sales data and you want to see a list of every product that was sold. To create a product list:
Every product that appears in the data is listed (including a typo)
9. Create a self-contained pivot table
When you've created a pivot table from data in the same worksheet, you can remove the data if you like and the pivot table will continue to operate normally. This is because a pivot table has a pivot cache that contains an exact duplicate of the data used to create the pivot table.
See: How to make a self-contained pivot table
Although pivot tables automatically group data in many ways, you can also group items manually into your own custom groups. For example, assume you have a pivot table that shows a breakdown of employees by department. Suppose you want to further group the Engineering, Fulfillment, and Support departments into Group 1, and Sales and Marketing into Group 2. Group 1 and Group 2 don't appear in the data, they are your own custom groups. To group the pivot table into the ad hoc groups, Group 1 and Group 2:
Starting to group manually
Half way through manual grouping - Group 1 is done
Finished grouping manually
One of the most interesting and powerful features that every pivot table has is the ability to group numeric data into ranges or buckets. For example, assume you have a list of voting results that includes voter age, and you want to summarize the results by age group:
The source data for voting results
Grouping the age field into 10 year buckets
Done grouping voting results by age range
When you add fields to a pivot table, the pivot table will display the name that appears in the source data. Value field names will appear with "Sum of " or "Count of" when they are added to a pivot table. For example, you'll see Sum of Sales, Count of Region, and so on. However, you can simply overwrite this name with your own. Just select the cell that contains the field you want to rename and type a new name.
Rename a field by typing over the original name
When you try to rename fields, you might run into a problem if you try to use exactly the same field name that appears in the data. For example, suppose you have a field called Sales in your source data. As a value field, it appears as Sum of Sales, but (sensibly) you want it to say Sales. However, when you try to use Sales, Excel complains that the field already exists, and throws a "PivotTable field name already exists" error message.
Excel doesn't like your new field name
As a simple workaround, just add a space to the end of your new field name. You can't see a difference, and Excel won't complain.
Adding a space to the name avoids the problem
There are many situations when it makes sense to add the same field to a pivot table more than once. It may seem odd, but you can indeed add the same field to a pivot table more than once. For example suppose you have a pivot table that shows a count of employees by department.
The count works fine, but you also want to show the count as a percentage of total employees. In this case, the simplest solution is to add the same field twice as a Value field:
Setting a field to show percent of total
The Name field has been added twice
Any time you add a numeric field as a Value in a pivot table, you should set the number format directly on the field. You may be tempted to format the values you see in the pivot table directly, but this is not a good idea, because it's not reliable as the pivot table changes. Setting the format directly on the field will ensure that the field is displayed using the format you want, no matter how big or small the pivot table becomes.
For example, assume a pivot table that shows a breakdown of sales by Region. When you first add the Sales field to the pivot table, it will be displayed in General number format, since it's a numeric field. To apply the Accounting number format to the field itself:
Setting format directly on a value field
Whenever you see a total displayed in a pivot table, you can easily see and extract the data that makes up the total by "drilling down". For example, assume you are looking at a pivot table that shows employee count by department. You can see that there are 50 employees in the Engineering department, but you want to see the actual names. To see the 50 people that make up this number, double-click directly on the number 50 and Excel will add a new sheet to your workbook that contains the exact data used to calculate 50 engineers. You can use this same approach to see and extract data behind totals wherever you see them in a pivot table.
Double click a total to "drill down"
The 50 Enginners, extracted into a new sheet automatically
Once you have one pivot table set up, you might want to see a different view of the same data. You could of course just rearrange your existing pivot table to create the new view. But if you're building a report that you plan to use and update on an on-going basis, the easiest thing to do is clone an existing pivot table, so that both views of the data are always available.
There are two easy ways to clone a pivot table. The first way involved duplicating the worksheet that holds the pivot table. If you have a pivot table set up in worksheet with a title, etc., you can just right click the worksheet tab to copy the worksheet into the same workbook. Another way to clone a pivot table is to copy the pivot table, and paste it somewhere else. Using these approaches, you can make as many copies as you like.
When you clone a pivot table this way, both pivot tables share the same pivot cache. This means that when you refresh any one of the clones (or the original) all of the related pivot tables will be refreshed.
Video: How to clone a pivot table
After you've cloned a pivot table, you might run into a situation where you really don't want the clone to be linked to the same pivot cache as the original. A common example is after you've grouped a date field in one pivot table, refresh, and discover that you've also accidentally grouped the same date field in another pivot table that you didn't intend to change. When pivot tables share the same pivot cache, they also share field grouping as well.
Here's one way to unclone a pivot table, that is, unlink it from the pivot cache it shares with other pivot tables in the same worksheet:
Your pivot table will now use it's own pivot cache and will not refresh with the other pivot table(s) in the workbook, or share the same field grouping.
The default layout for new pivot tables is the Compact layout. This layout will display "Row Labels" and "Column Labels" as headings in the pivot table. These aren't the most intuitive headings, especially for people that don't often use pivot tables. An easy way to get rid of these odd headings is to switch the pivot table layout from Compact to Outline or Tabular layout. This will cause the pivot table to display the actual field names as headings in the pivot table, which is much more sensible. To get rid of these labels altogether, look for a button called Field Headers on the Analyze tab of the Pivot Table Tools ribbon. Clicking this button will disable headings completely.
Note the useless and confusing field headings
Switching the layout from Compact to Outline
Field headings in Outline layout are much more sensible
This is just a simple design tip. All good designers know that a pleasing design requires a little white space. White space just means empty space set aside to give the layout breathing room. After you create a pivot table, insert an extra column to the left and an extra row or two at the top. This will give your pivot table some breathing room and create a better looking layout. In most cases, I also recommend that you turn off gridlines on the worksheet. The pivot table itself will present a strong visual grid, so the gridlines outside the pivot table are unnecessary, and will simply create visual noise.
A little white space makes your pivot tables look more polished
Inspiration: 5 pivot tables you haven't seen before.
By default, pivot tables show totals for both rows and columns, but you can easily disable one or both of these totals if you don't want them. On the Pivot Table tab of the ribbon, just click the Totals button and choose the options you want.
You can remove grand totals for both rows and columns
If you have a pivot table that has a lot of blank cells, you can control the character that is displayed in each blank cell. By default, empty cells will display nothing at all. To set your own character, right-click inside the pivot table and select Pivot Table options. Then make sure that "Empty cells as:" is checked, and enter the character you want to see. Keep in mind that this setting respects the applied number format. For example. if you are using the accounting number format for a numeric value field, and enter a zero, you'll see a hyphen "-" displayed in the pivot table, since that's how zero values are displayed with the Accounting format.
Empty cells set to display 0 (zero) and Accounting number format gives you hyphens
By default, when you refresh a pivot table, the columns that contain data are adjusted automatically to best fit the data. Normally, this is a good thing, but it can drive you crazy if you have other things on worksheet along with the pivot table, or if you have carefully adjusted the column widths manually and don't want them changed. To disable this feature, right-click inside the pivot table and choose PivotTable Options. In the first tab of the options (or the layout tab on a Mac), uncheck "AutoFit Column Widths on Update".
Pivot table column autofit option for Windows
Pivot table column autofit option for Mac
A pivot table is a program tool that allows you to reorganize and summarize selected columns and rows of data in a spreadsheet or database table to obtain a desired report. A pivot table doesn't actually change the spreadsheet or database itself. A pivot table allows you to extract the significance from a large, detailed data set, thus turning "data overflow" into meaningful reports.
Use a pivot table to build a list of unique values. Because pivot tables summarize data, they can be used to find unique values in a field. This is a good way to quickly see all the values that appear in a field and also find typos, and other inconsistencies.
Pivot tables accomplish this task by using "Values Areas". The Values area displays the data (values) that we want to summarize in our pivot table report. When you drag a field into the Values area, the pivot table will automatically sum or count the data in that field. If the data in the field contains numbers, then the sum will be calculated.
Pivot Table Example:
Insert a Pivot Table | Drag fields | Sort | Filter | Change Summary Calculation | Two-dimensional Pivot Table
Pivot tables are one of Excel's most powerful features. A pivot table allows you to extract the significance from a large, detailed data set.
Our data set consists of 213 records and 6 fields. Order ID, Product, Category, Amount, Date and Country.
To insert a pivot table, execute the following steps.
1. Click any single cell inside the data set.
2. On the Insert tab, in the Tables group, click PivotTable.
The following dialog box appears. Excel automatically selects the data for you. The default location for a new pivot table is New Worksheet.
3. Click OK.
The PivotTable Fields pane appears. To get the total amount exported of each product, drag the following fields to the different areas.
1. Product field to the Rows area.
2. Amount field to the Values area.
3. Country field to the Filters area.
Below you can find the pivot table. Bananas are our main export product. That's how easy pivot tables can be!
To get Banana at the top of the list, sort the pivot table.
1. Click any cell inside the Sum of Amount column.
2. Right click and click on Sort, Sort Largest to Smallest.
Result.
Because we added the Country field to the Filters area, we can filter this pivot table by Country. For example, which products do we export the most to France?
1. Click the filter drop-down and select France.
Result. Apples are our main export product to France.
Note: you can use the standard filter (triangle next to Row Labels) to only show the amounts of specific products.
By default, Excel summarizes your data by either summing or counting the items. To change the type of calculation that you want to use, execute the following steps.
1. Click any cell inside the Sum of Amount column.
2. Right click and click on Value Field Settings.
3. Choose the type of calculation you want to use. For example, click Count.
4. Click OK.
Result. 16 out of the 28 orders to France were 'Apple' orders.
If you drag a field to the Rows area and Columns area, you can create a two-dimensional pivot table. First, insert a pivot table. Next, to get the total amount exported to each country, of each product, drag the following fields to the different areas.
1. Country field to the Rows area.
2. Product field to the Columns area.
3. Amount field to the Values area.
4. Category field to the Filters area.
Below you can find the two-dimensional pivot table.
To easily compare these numbers, create a pivot chart and apply a filter. Maybe this is one step too far for you at this stage, but it shows you one of the many other powerful pivot table features Excel has to offer.
At a minimum, what you need to record is a date, the time you spent, the client name, and the project. So, after you enter the data consistently, you might end up with source data that looks something like this:
Note that there are no blank lines - you just need to enter the data as you go.
Now the summaries. First, you might want an overview of your time by week. Here we are using the week numbers provided by Excel's WEEKNUM function (see column C of the source data):
You might also want to arrange the pivot table to show a more traditional timesheet layout, with days of the week across the top:
Each time you filter on a different week number, your pivot table will build a new time sheet that displays the dates that belong to the week.
Note that by adding a column for Name to the data, you could track and report time for multiple people. You could also add a rate column to the data and use a pivot table to summarize the cost or billing rate of time logged.
If you do not apply a fraction format to a cell before you enter a fraction into it, Excel will convert the fraction into a date. But that’s not what I want; I want a fraction...
For example, if you enter 1/50 into a cell, Excel immediately converts it to Jan-50 . If you then try to reformat the same cell to a fraction, Excel converts it to 18264 , the internal number that represents Jan-50. In that case, if you want Excel to recognize your cell entries as fractions, you must apply the appropriate fraction format to the cell before you enter the data.
For example, say you are importing a column of data from an Access database table that contains fractions such as 1/50, 2/70, and 30/65. You will need to format the column of cells containing the data to an appropriate fraction format before importing. Follow these steps:
Now, when you import the data to that column, Excel will format each entry as a fraction. Keep in mind that if the data contained fractions such as 30/750, you would select Up To Three Digits (312/943) in Step 5. If you wanted to display your data as fractions over 100 (for example, 30/65 displayed as 46/100), you would select As Hundreds (30/100) in Step 5.
Alternately, if you won’t be using the fractions in calculations, you can prevent Excel from changing your entries to dates by applying the Text format to the cell before you enter the data. In that case, in Step 4 select Text under the Category list, and then click OK.
If you do not apply a fraction format to a cell before you enter a fraction into it, Excel will convert the fraction into a date. But that’s not what I want. I want a fraction...
Have you ever set up a worksheet using one structure that seems to make sense, only to realize it would make a whole lot more sense if the rows were columns and the columns were rows. Microsoft makes it easy with the Transpose option to facilitate the necessary flipping of data from Rows to Columns OR from Columns to Rows, and this is how we do it:
VLOOKUP will work with a list where the table arguments are sorted, and you will get the closest match to a table argument that does not exceed your lookup value.
(for sorted lists use TRUE or default for a *close* match)
Syntax: (As always look in HELP for more information)
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
range_lookup can be TRUE or FALSE, if omitted the default is TRUE
VLOOKUP will work with a list where the arguments are unordered, and you either get an exact match or fail with #N/A!.
(Whether sorted or not when an *exact* match is required so is the use of FALSE)
To suppress N/A errors:
=IF(ISNA(VLOOKUP(...,...,...,False)),"Item not found",VLOOKUP(...,...,...,False))
A cause of #VALUE! error is a zero value for col_index_num value in the function.
Do not mix cells defined as numbers with cells defined as text in the argument column of your table. Some tips on determining data type and actual content of your data. Your table must be consistent, but your lookup value can be forced to look like the table by using one or the other of these tricks (Peo Sjoblom 3003-01-15).
=VLOOKUP(TEXT(A1,"00000"),Table,2,FALSE)
This is the simplest example that I can come up with. Note the use of TRUE in the formulas indicating that the value found in the table does not have to be an exact match but must be less than or equal to the lookup_value used. For VLOOKUP the first column of the range is the used to match the argument, the 2 used in the example indicates to return the second column of the table. Since TRUE is used an exact match is not required, but because an exact match is not equired, the table must be in ordered in ascending order to obtain the correct result. If VLOOKUP can’t find lookup_value, and range_lookup is TRUE, it uses the largest table argument value that is less than or equal to lookup_value.
VLOOKUP To Find Your Perfect Match
Using the FALSE argument in Excel can help you find a true match in a lookup table, while eliminating a lot of the confusion that is common with such comparisons. However, you can use the FALSE argument to your advantage.
Sometimes lookup tables do not require an exact match. Example, to find shipping costs, the cost of the item must fall within a certain range. However, some situations, such as one that matches up an ID number with a particular person's name, require an exact match. The lookup formula in this instance requires the FALSE argument at the end of the statement.
Suppose you have a list of employee ID numbers in A1:A100; the list of their corresponding names is in B1:B100. To create a lookup formula that looks for an exact match to an employee's ID number, follow these steps:
When you enter an employee ID number from Employee_List in E1, the name of the employee will appear in E2. If no exact match appears, Excel will display "Notfound" in E2. Note: By placing the FALSE argument in the VLOOKUP formula, you do not need to sort the list of employee ID numbers.