Most of the time, when you use the Excel VLOOKUP function, you want to get the information for an exact match.
For example, if you enter a product code in one cell, you want the product name in the next cell. You don't want the name of a product with a product code that is CLOSE to the one you entered.
Occasionally though, an approximate match is what you need with Excel VLOOKUP, so several values will return the same result.
For example, if you are working with student grades, you don't want to create a lookup table where every possible percentage is listed. Instead, your lookup table should have the starting percentage for each grade.
In this video shown below, you can see how the letter grades are shown on a report card, based on the student's percentage in that subject.
With a simple double-click, you can quickly minimize the Ribbon in Excel. Then, double-click to show the full Ribbon again. This tip works in both Excel 2007 and Excel 2010.
Minimizing the Ribbon is useful if you're working on a small monitor, or if you want to make more room for a large worksheet in Excel.
For a quick way to create a hyperlink in an Excel worksheet, you can use the mouse.
With this drag and drop technique, use the right button on the mouse to drag a cell to a different location.
When you stop dragging, as shown in this Excel video tutorial, use the popup menu command to insert a hyperlink.
This is an efficient way to create a table of contents in an Excel workbook, or to make a quick link from one sheet to another. These hyperlinks will make it easy to navigate through your Excel workbooks.
Here are the steps for creating a pivot table in Excel 2007. The source data contains information about insurance policies, and with a pivot table, it is easy to summarize in different ways.
With a few clicks, the thousands of rows of data can be totaled by Region, without using any formulas. Then, another couple of clicks, and the total value for different locations are shown.
In the following video, you can see how a complex Excel waterfall chart is set up, and how the waterfall chart add-in makes the task easier.
You can see how a simple Excel waterfall chart is build, and the formulas that are required, in the following video tutorial. The step by step instructions are shown below the video.
Instructions for Building an Excel Waterfall Chart
With an Excel waterfall chart, you can show a starting price, the rise and fall as other values are applied, and a final price.
Here is the original data, with a starting value, and a change in value for each month.
To prepare the data for the Excel waterfall chart, 5 blank columns are added, between the month names, and the values.
The headings, Base, End, Down, Up and Start are added to the blank columns.
Enter the Formulas
The Base column will not be visible in the completed chart, but this series provides a starting point for the Up and Down bars.
In the Base column, the following formulas are added.
B2: =“ “
B4: =SUM(B3,E3:F3)-D4
Copy B4 formula down to B15
B17: = “ “
In column F, the Start value is shown. It is the first visible bar in the chart
Start Formula:
F3: =G3
Next, the End value is calculated in column C, using this formula.
C16: =SUM(B15,E15:F15)-D16
In the Down column, formulas calculate the result for data that is a negative amount.
D4: = - MIN(G4,0)
Copy formula down to D15
The final set of formulas is in the Up column, using this formula:
E4: = MAX(G4,0)
Copy formula down to E15
Create the Excel Waterfall Chart
After the data is set up, select all the data and heading, but do not include the original data, in column G.
Insert a 2-D Column chart, using the Stacked Column chart.
To make the Base series invisible, right-click one of the Base columns, and format the series with no fill and no borders.
Next, right-click one of the Down columns, and format that series with red fill.
Then, right-click an Up column, and format that series with Green fill color.
Then, format the End and Start bars with gray fill color.
To make the columns closer together, right-click on any column, then click Format Data Series.
Reduce the Gap width to about 10%, so the columns are close together.
The Completed Excel Waterfall Chart
To tidy up the waterfall chart, you should remove the Legend, which isn't useful in this chart type.
The Excel waterfall chart is finished!
The Waterfall Chart Utility
If you don't have enough time or patience to build a waterfall chart and its formulas, you can quickly create the chart by using Jon Peltier's waterfall chart utility.
This Excel add-in is easy to use, and can work with complex data, such as data that drops below the zero line. The waterfall chart utility also adds join lines, to connect the tops or bottoms of the columns.
The add-in is quick and easy to install, and creates a command on the Excel Ribbon. After you enter your labels and original values, just click the Waterfall Plot command to create the chart.
With a waterfall chart in Excel you can see a list price, then discounts, and an invoice price. Off-invoice discounts can also be shown, and then the pocket price and pocket margin.
This video shows a couple of waterfall charts, and the table setup that is used to create the different columns and lines in the chart.
To save time and effort, you can use the PeltierTech Excel waterfall chart utility, which creates the formulas and chart in just a couple of clicks.
