Getting VLOOKUP to return multiple values

This post is for the somewhat more advanced Excel user.

You may already be familiar with the VLOOKUP function, what you may not know is that you can get a single VLOOKUP formula to return multiple values by using an array.

1. Select each cell that you want to contain a result from the VLOOKUP.  In the screenshot below, the cells I4:K4 were selected
2. Switch to the Formulas tab, select Lookup&Reference, VLOOKUP
3. Specify the Lookup_value and Table_array as normal
4. Enter a column number for each column that you want data returned from.  Separate the column numbers by commas and enclose it all in braces / curly brackets {  }.  The column numbers can be specified in any order.  In the example below, data will be returned from columns {5,6,3}
5. Specify your Range_lookup requirements
6. Press CTRL, SHIFT, ENTER

Hope this is a time saver for you!  Please share if you think anyone might find this useful too.

Restrict values using Data Validation lists

Using the Data Validation feature in Excel you are able to restrict entry to a predefined list of values.  

In many cases spreadsheets are completed by more than one user which often results in different styles of inputting data.  By having a drop down list to select from ensures consistency which not only makes for a neater appearance, but also significantly improves the ease of use of tools such as Pivot Tables and Filters.

• Somewhere in your spreadsheet, type out the list of values that you would like to use as the source for your drop down list 
• Select the cells to which you would like to add Data Validation drop down list
• On the Data tab, select Data Validation. (A drop-down menu will appear.)
• Select Data Validation.
• Select List from the Allow drop down.
• In the Source field, select the range of cells that will act as the source data for the drop down list

• Click OK and begin using the drop down list to aid data entry

Happy spread-sheeting! Please share if you found this useful.

Easy way to wrap text in MS Excel

When you want to split your heading over more than one line but within the same cell, you may be familiar with the Wrap Text button on the ribbon.  However there is a quicker method that can be implemented as you are typing: ALT Enter.

Using the example below, type Stock then press ALT ENTER to move your cursor into the next line, then type MVMT and press ALT ENTER to move down again and then type Boxes.  Press ENTER when you are done.

You can even edit a cell afterwards to add a line break if necessary, you'd just have to press ALT ENTER at the appropriate spot in the formula bar (or in the cell itself if you're in edit mode).

Please share this using one of the buttons below if you think others will find this useful, or leave me a comment if there is something else you'd like to learn about.

Non breaking spaces in MS Word

Perhaps you've heard of non breaking spaces, perhaps not, but you certainly should know how to use them.

On occasion you may want to override the way MS Word wraps text at the end of a line.  For example, you may want a person's name and surname to always appear on the same line or perhaps the date and year are separating and you'd prefer they didn't.

Looking at this example the name (which I've highlighted to make it easier to spot) has wrapped onto two lines.

In order to force MS Word to keep the name and surname together, I've replaced a normal space between the name and surname with a non breaking space.  Easily done.  Simple replace the existing space with CTRL SHIFT SPACEBAR

Whilst this is not something you're likely to use on a very frequent basis, it's a great little trick to know.

Formula Auditing in Excel

When you're viewing a formula, you would typically read the formula bar to determine which cells the formula uses, and this is where the distinct blue arrows of Formula Auditing make your life a lot easier.  

First, select a cell that contains a formula, then switch to the Formulas tab on the ribbon.   In the Formula Auditing group, click the Trace Precedents button.

Trace Precedents will send a blue arrow to all of the cells which are used in the creation of the formula that is currently selected.

If the cell that is selected does not contain a formula the Trace Precedents error alert will appear.

If you'd like to find out if a cell is required elsewhere on the spreadsheet, click the Trace Dependents button.  If the cell is used by any other calculation, a blue arrow will direct you to that value.

At any stage you can use the Remove Arrows button to clear the arrows on your screen.

This really is any easy way to figure out how your spreadsheets are inter linked, I certainly use it often when faced with assisting someone else with their Excel files - it helps me get to grips with their spreadsheets in half the time.

Using Quick Calculations

The status bar appears at the bottom of the Excel window and you are most likely familiar with finding settings like your zoom control here.  Naturally, there is far more use to the status bar than just zooming, and one of those things is activating the quick calculations.

These quick calculations are useful when you want to spot check values but don't necessarily need the resulting value to be placed as an independent value on the spreadsheet.

Right click onto the status bar.  This will display all the content that you could have displayed if you wish.  There is a section that is specifically for the quick calculations - typically Sum is already selected.  You may select any calculation types you like.

When you next select a range of cells containing values, the status bar will display the quick calculations that you have requested.

Calculating a loan repayment with the PMT function

Here's an Excel function we could all make use of.  Calculating the repayment on a loan.

In order for this calculation to work, you require 3 items:  the amount being borrowed; the interest rate that will be charged; and the term over which the loan will be re-payed.

First layout your spreadsheet with the required information, and click into the cell that you would like the answer (i.e. the monthly installment) to appear.  Now switch to the Formulas tab on the ribbon, and from the Financial list scroll down to find the PMT calculation.

The Function Arguments window will appear with 3 required fields (for the information that you have ready) and 2 further optional fields that I'll explain a little further down.  A clue to indicate which fields are required vs which are optional, is that the required fields have a bold title.

Place your cursor into the first required field, Rate, and click once on the cell that contains your interest rate value.  Typically the interest rate that you are quoted is the APR (Annual Percentage Rate) and as such the amount is what you will be charged per year.  Because we are attempting to calculate the monthly installment, we need to give Excel the monthly interest rate.  You are able to do so by dividing this APR value by 12.

Now place your cursor into the second required field, Nper.  This field represents the total number of installments you will be paying.  So if your loan is taken out over a 10 year period, you won't be making 10 payments in total, but rather 10 years of 12 months each, so a total of 120 payments.   Click once onto the cell that contains your term value, and then type *12

The final required field is PV.  This part of the calculation is where you indicate the PresentValue i.e. the loan amount that needs to be re-payed.  Ensure that your cursor is in the PV field, type a minus sign and then click once onto the cell on your spreadsheet that contains the loan amount.  Typing a minus sign is not required, but it's a clever way to ensure that the calculated monthly repayment does not appear as a negative value.
At this point you can click OK to see installment that will be required for this loan.  Please remember that many loan companies will have their own additional costs that will mean this will not be an exact amount but it will at least give you a good idea of what to expect.

There are however, two additional fields are not required in order for the calculation to work correctly, but they can have an impact on the answer.

The first of these fields is FV.  This is the FutureValue - the amount that the loan needs to reach by the end of the loan period.  This would typically be zero - you want to pay the loan off in full over the course of the loan period and if you leave this field blank, Excel will assume you wish to pay off the entire loan.  Of course this isn't always the case, and sometimes your loan may allow for what is known as a balloon payment / residual payment.  If you have such a balloon payment, you still enter the full loan amount into the PV field, and the balloon amount into the FV field.

The second optional field is Type.  This is used to indicate when payment is required - if you are required to pay at the end or beginning of the period.  Simply put, is your payment due at the end of the month or beginning.  If you leave this field blank, it is assumed you pay at the end of the month.  You can enter a 1 to indicate payment is due at the beginning of the period, whilst a 0 indicates payment at the end of the period.

I'm wondering if this post was heavy reading for you?  Would you prefer to have seen this tutorial as a 2 or 3 minute video clip?  Let me know you're thoughts