Category Archives: Tutorials

VLOOKUP Excel

VLOOKUP Tutorial

The explanation for how VLOOKUP works in Excel is far more complicated than actually using it. The best thing to do is to get a rough idea of the arguments that VLOOKUP requires, and what it returns to you, and then quickly go on to look at a few VLOOKUP examples.

Imagine you have the following spreadsheet:

In this example, you need Excel to give you the country code if you give Excel the country. It’s helpful to break down what you as a human would do to complete this task. Let’s get the country code for Angola now.

State what we are searching for, and that is the text “Angola”
Then we identify what table contains the data we need, and that’s A2:B11.
Then we determine what column holds the information we need Excel to return to us, and that’s column 2 (country code).

These three pieces of information are precisely what Excel needs to perform the VLOOKUP, and they are provided in the following format:

VLOOKUP(search_value, table_array, column_to_return)

So, to get the country code for Angola, we would use the following VLOOKUP:

=VLOOKUP(“Angola”, A2:B11, 2)

Don’t forget the “=” at the beginning of the formula.

The above example shows the most basic way of using VLOOKUP, and it returns an exact match on the country code. If “Angola” couldn’t be found in the table, Excel would return #N/A. We can, however, provide an additional parameter to Excel’s VLOOKUP: not_exact_match. The format then looks like:

VLOOKUP(search_value, table_array, column_to_return, not_exact_match)

not_exact_match takes the value TRUE or FALSE. If set to FALSE, Excel tries to find an exact match. If set to TRUE, Excel finds the largest value in column 1 that is less than or equal to the search value. Imagine we were trying to get the country code for the fictional country “Amporo”, which doesn’t exist in the table. Here, it only makes sense to look for an exact match, so we would use:

=VLOOKUP(“Amporo”, A2:B11, 2, FALSE)

Excel would return #N/A because “Amporo” doesn’t exist in the table. If, instead we used:

=VLOOKUP(“Amporo”, A2:B11, 2, TRUE)

then Excel would return AS. American Samoa is the largest value less than or equal to Amporo, so AS gets returned.

Now that we’ve seen VLOOKUP in action, let’s visit the official definition:

VLOOKUP searches for a value in the first column of a table array and returns a value in the same row from another column in the table array.

The V in VLOOKUP stands for vertical. Use VLOOKUP instead of HLOOKUP when your comparison values are located in a column to the left of the data that you want to find.

Have a look at this illustrative VLOOKUP example.

Remove Duplicates From Excel

The ability to remove duplicate entries from a list is a useful one, and one that can be used in a variety of every day situations. Imagine, for example, that you are writing a shopping list and are copying and pasting ingredients from several recipes into an excel spreadsheet. Some ingredients will be repeated. Garlic is an ingredient in spaghetti bolognese, chicken and ginger, and so on. In your spreadsheet, many ingredients may be duplicated several times. How do you remove these duplicates?

It’s easier than you think. The hardest part is finding the command to do it!

That very command is found on the Data tab in the Data Tools group.

Here is a sample spreadsheet with ingredients in it. Open that in Excel and then click the Remove Duplicates button. The Remove Duplicates window opens and allows you to confirm the column that you will perform the operation on. In our simple spreadsheet, Excel rightly selects Column A.

Click OK and a confirmation dialogue informs us that 4 duplicate values were found and removed (18 unique values remain).

If you tend to do a lot of removing duplicates, why not add the command to the Quick Access Toolbar? That way you won’t have to jump to the Data tab every tim you want to use it. Right click on the Remove Duplicates command and select Add to Quick Access Toolbar.

Now the command will appear in the Quick Access Toolbar (in top left hand corner of the screen). If you later decide to remove it again, it’s a simple case of right clicking on it and selecting Remove from Quick Access Toolbar.

Count The Number Of Cells With Text In Excel

If you ever need to count the number of cells that contain text in Excel, there is a very easy way to do it. You will need two basic functions:

COUNT – this Excel function returns the number of cells in a range that contain numbers
COUNTA – this function returns the number of cells that are not empty

Intuitively, we know that the number of cells that contain text (not numbers!) is equal to the number of non blank cells – the number of cells containing numbers. In other words: COUNTA – COUNT.

Let’s look at a simple example to illustrate. Suppose we have the following data in our spreadsheet:

Place the cursor in the cell that you want to hold the results and type

=COUNTA(D1:D12)-COUNT(D1:D12)

Alternatively, if you type out
=COUNTA(

you can then drag your cursor over the cell range to select it and Excel will insert D1:D12 into the formula. You can do the same with the COUNT argument, too. When you press Enter, Excel resolves the equation and displays 5.

Make A 2011 Calendar In Excel

Making a 2011 calendar in Excel 2010 is probably easier than you think. You don’t have to spend time getting the cell formatting right and you don’t have to figure out what Excel formulas to use. All you need to do is use an Excel Template.

To create a calendar in Excel, click the File tab > New. In the workspace that opens, we should see some template categories.

Click to enlarge

The category we need is Calendars, so click on that one. Now we get to see the different calendar categories we can use.

As we want a calendar for 2011, click 2011 calendars. There are quite a few different Excel calendars to choose from here. You can get an idea of what each one looks like by clicking on it to select it and then looking at its preview on the right.

When you’ve found one that you like, and have selected it, click on the download button beneath the preview. The calendar will open in Excel for you to amend or just print out.

Some of the calendar templates look pretty good, and they are all different, but to illustrate the quality, here is one month from the 2011 Yearly View Calendar:

You should take a look around the different templates. They all have different styling, and some have all the significant dates marked in already. The calendar templates come directly from office.com so, as they aren’t already a pat of Excel, they need to be downloaded. This will explain any small delay you experience when you view and download the templates.

And, of course, you can change the styling of your calendar to your own tastes.

Excel Drop Down Lists

A drop down list in Excel presents a number of options for the user to select from. The advantages of eliciting a response from the user in this way are that:

the entries in the list give the user a clue about what values are allowed or expected
it’s easier for the user to select an option than to type one into a cell
it reduces typos that the user makes
you can make the list dynamic by populating it with entries from different cells elsewhere in the workbook

To indicate that a drop down list is present, Excel displays a down arrow to the right of the cell.

Create A Drop Down List From A Range Of Cells

To create a drop down list from the contents of a range of cells, first of all type in the values you want to appear in the list in a single row or single column in your workbook. Make sure that there are no blank entries in the list.

Be aware that the order that you type in the data is the order that the values will appear in the drop down list so perform any sorting you need before you go any further.

Select the cell where you want the list to go and then click the Data tab and go to the Data Tools group. Click the Data Validation button and in the Data Validation window ensure that you’re working on the Settings tab.

In the Allow box select List and then enter the range of cells in the Source box. To get the range into the box, you can also click on the cell selector button and then drag over the cell range you need. Make sure that the range of cells is preceded by an equals sign like this: =$F$8:$F$13

Make sure that the In-cell drop down box is checked and if you want the user to be able to make a blank selection (i.e. leave it blank) then check the Ignore blank box.

If you want to display an informational message for the user when they make the drop down list cell active, go to the Input Message tab. Check the Show input message when cell is selected box. Then enter values for the title an input message itself.

If you want to display an error message after invalid data has been entered, click on the Error Alert tab. Ensure that the Show error alert after invalid data is entered box is checked and select the most appropriate Style:

To display an information message that does not prevent entry of invalid data, select Information.
To display an information message that does not prevent entry of invalid data, select Warning.
To prevent the entry of invalid data, select Stop.

Type in a title for the error message and also some descriptive text.

To remove a drop down list from a cell, make the cell active and then click to the Data tab. Go to the Data group and then click the Data Validation button. On the Settings tab, click the Clear All button at the bottom.

Create A Drop Down List Using Static Values

If you want a drop down list of static values that don’t change, and aren’t dependent on the contents of other cells, you can type in the values directly. Select the cell where you want the list to go and then click the Data tab and go to the Data Tools group. Click the Data Validation button and in the Data Validation window ensure that you’re working on the Settings tab. In the Allow box select List and then type in the value you want to display in the list into the Source box. Separate the values with a comma. Review the steps above if you need to add any informational or error messages. Click OK when finished.

If you typed in an input message, it will be displayed when the cell becomes active, like this:

The user will need to click on the down arrow to display the drop down list, and then they can click on the entry they want to select it.

The Quick Access Toolbar In Excel 2010

The quick access toolbar in Excel provides a selection of the more commonly used commands in one convenient place – the top left corner of the workspace (above the file tab).

The beauty of the quick access toolbar is that it’s available whichever tab you’re using. By default, the toolbar contains the following commands, but you can add more:

Save
Undo
Redo

Add Commands To The Quick Access Toolbar

There are two different ways to add commands to the quick access toolbar. The first way involves using the Customize Quick Access Toolbar button, to the right of the toolbar. When we click that, we get this menu:

In this panel, we can see that the commands that already appear in the toolbar have a tick next to them. To add others, just select them one at a time by clicking on them. To remove one, click on it again to remove the tick. This menu displays only a small selection of the commands that are available for display on the quick access toolbar, though.

Any command you see on the ribbon can be added to the quick access toolbar, which brings us to the second method. Find the command on the ribbon that you want to add, right click on it and select Add to Quick Access Toolbar.

For example, many people find that they use the Merge & Center command all the time and it makes sense to have quick and easy access to it from any tab. Just add it to the quick access toolbar!

To remove it again, you can right click on the command in the toolbar and select Remove from Quick Access Toolbar.

There are other controls you can use on the quick access toolbar. For example, if you click on the Customize Quick Access Toolbar button (see above), you can choose to display it below the ribbon. But why on earth would you want to do that?? Another example of Microsoft providing a “feature” that has no benefit whatsoever, but hey we digress.

Number Formatting In Excel

Number formatting in Excel is the process of controlling the appearance of numbers in your spreadsheets. Formatting numbers appropriately will help your readers to read and understand them better. Questions you need to ask yourself when dealing with numbers in Excel include:

Do I need to include a currency symbol for amounts?
How many decimal places shall I display? That is, how accurate do the numbers need to be?
What format should dates be in, for example dd/mm/yy, or dd/mm/yyyy or mm/dd/yy etc

All the number formatting commands are to be found on the Home tab in the Number group. Let’s format some numbers right now! Suppose we have a column of sales figures like this:

We can make these sales figures much more readable in just a few clicks. Select the column by clicking on its letter heading. Now click Home > Number > Number Format drop down selector. You should see the following number format options:

Select the Currency option and you should see the currency symbol that your region uses appear to the left of each number in the column. You should also see 2 decimal places appear.

You can tweak the number formatting, too, by clicking Home > Number > Number Format drop down selector, and then select More Number Formats at the bottom. The formatting of the currently selected cell(s) is initially shown in the Format Cells window, but you can change any aspect of it. Let’s change the number of decimal places to 0 for our sales figures.

There are lots and lots of different formatting options available in this window.

Note that any number formatting you apply affects only the value in the cell. The value displayed in the formula bar is unformatted.

Formatting Dates In Excel

Let’s have a quick look at how we can format dates in Excel. If you type in a value of “14/03/2011″, Excel will be able to recognise it as a date, and Date will be displayed in the Number Format selector. Note that dd/mm/yyyy is a typical date format used in the UK. If you want to change the date format to mm/dd/yy, for example, click Home > Number > Number Format (drop down selector) > More Number Formats, and make the following changes:

As you can see, you can also have the name of the month displayed too: “14 March 2011″

Excel Save As PDF

Many people prefer the way that an Adobe PDF (Portable Document Format) document presents information to the way that a standard spreadsheet looks. Whereas in the past you needed to download and install a special add in to save a spreadsheet as a PDF (for example, the Microsoft Office 2007 Save as PDF Add-in), Excel 2010 comes with this facility by default.

To save as a PDF, click the File tab > Save As, and then give the document a name. Change the Save as type selection to be PDF (*.pdf).

Click to enlarge

When you do this, additional options appear on the Save As window.

Here is an explanation of what each one does:

Optimise For – Standard (publishing online and printing) is the option that you would usually use, but if you needed to reduce the amount of space the PDF took up on disc, then you could select Minimum size (publishing online). As with most options that reduce file size, an accompanying degradation in quality should be expected.
Options – when you click on the options button, a window is displayed that looks a little like the one you see when adjusting printing options (see below). Here, you can select what pages and what worksheets you want to be included in the PDF.

To illustrate, here is a worksheet that contains data about which countries visitors to this site live in. This is what the document looks like when saved as a PDF.

Excel DATEDIF

The DATEDIF function in Microsoft Excel calculates the difference, or interval, between two dates. This difference can be expressed in a variety of ways. The function takes the form

=DATEDIF(Date1, Date2, Interval)

where Date1 and Date2 are the two dates and Interval defines how the date difference should be returned. Interval must be enclosed in quotes like this:

=DATEDIF(Date1, Date2, “d”)

These are the errors that DATEDIF may return if you get something wrong:

#NUM error if Date1 is later than Date2
#VALUE error if either Date1 or Date2 are invalid dates
#NUM error if the Interval specified is not one of the allowed values

The Interval must be one of the following values:

Interval	Use
m	Months: the number of whole calendar months between the two dates
d	Days: the number of days between the dates
y	Years: the number of whole calendar years between the dates
ym	Months In Same Year: the number of months between the two dates if they were in the same year
yd	Days In Same Year: the number of days between the two dates if they were in the same year
md	Days In Same Month And Year: the number of days between the two dates if they were in the same month and year

DATEDIF Examples

Try and predict what the result of the following examples will be:

DATEDIF	Result
=DATEDIF(01/01/2010, 01/06/2010, “d”)	151
=DATEDIF(01/01/2010, 15/01/2010, “d”)	14
=DATEDIF(01/01/2010, 01/06/2010, “m”)	5
=DATEDIF(01/01/2010, 15/01/2010, “m”)	0
=DATEDIF(01/01/2009, 01/06/2010, “m”)	17
=DATEDIF(18/08/2008, 01/03/2010, “y”)	2
=DATEDIF(01/01/2010, 31/12/2010, “y”)	0
=DATEDIF(01/01/2008, 01/06/2010, “ym”)	5
=DATEDIF(23/04/2003, 31/05/2005, “yd”)	38
=DATEDIF(17/02/1974, 28/01/1998, “md”)	11

Excel Offset

The Excel offset function is designed to return the contents of a range of cells that is offset from an initial range. The function takes the following arguments:

the starting range to which the offset is to be applied – this can be one cell or a range
the number of rows to offset the starting range by – this can be negative
the number of columns to offset the starting range by – this can be negative
the height of the new range in rows
the width of the new range in columns

The syntax for the Excel offset function is as follows:

=Offset(range, rows, columns, height, width )

Let’s consider the following data in a worksheet:

Performing the offset

=Offset(B3, 2, 1, 1, 1 )

gives the contents of cell C5, i.e. 7.95.