Create a drop-down list in Excel

Save time by avoiding repetition and errors

Excel is great for lists; sales figures, staff rota’s, stock control, to name a few. But an easy trap to fall into is repetitive error prone data-entry that leads to inaccurate business reporting and lost time in troubleshooting.

If you create a drop-down list in Excel, you can avoid all of this. Imagine the time saved short and long term especially if multiple people are using the same spreadsheet.

How to create a drop-down list in Excel


Here’s one we made earlier

Step 1 Assign the values for your drop-down list. In a new worksheet, just start your list and order it if you wish (better now than later!)


Step 2 Now select the data and right click, select Define Name.

Step 3 In the New Name dialogue box you need to give your data name (this is a named range), making sure not to have any spaces in the name. Example, Commute

Name the range

Step 4 Now go to the worksheet where you wish create a drop-down list in Excel, and click a cell. Go to the Data tab and select Data Validation

select data validation

Step 5 In Settings tab we need to do the following:

Select List from the Allow box.
Ensure In-cell dropdown is ticked. If you are okay for blank entries to be made just leave the Ignore blank ticked.
In the Source box we need to type in the name of our list making sure to start with an =. In this case, =Commute

data validation options

Now click OK, your drop-down list is ready to go. You may have noticed two other tabs within the Data Validation box. The Input Message and Error Alert give you even more options to control how data is entered and also what messages appear to users when they have not entered data correctly.

To create a drop-down list in Excel is pretty straight forward giving us some major advantages in saving time from data entry as well as data error. Data validation in it’s own right can really help businesses adopt more consistent and efficient use of Excel spreadsheets.

More related information:

Excel data validation in business

A real world example of assigning values to a drop-down list in Excel

A further look at Input Message & Error Alert



How to share files in Excel

Improve collaboration and save time

It is not uncommon with Excel to have multiple people requiring access to the same file. Whether the file in question is a sales report, marketing budget, or time-sheet for example. This is where the Share Workbook feature in Excel comes in handy, here we’ll look at how to share files in Excel and things to watch out for.

The big incentive for sharing an Excel file amongst people is that there is just a single version in use. Not taking this approach entails all sorts of challenges, from having to manage multiple (and ever changing) versions of the same file through to organising how to merge all the data into a single file. By sharing, everyone is on the same page/workbook!

Step 1 Create or open an existing spreadsheet that you wish to share.

Step 2 Once open, go to the Review tab and select the Share Workbook.

how to share files in Excel

In the Review tab select the Share Workbook

Step 3 Now the Share Workbook dialogue box will be open. Make sure the Editing tab is open and then click on the box to “Allow changes by more than one user at the same time” (this is where you will also be able to view who else is using the shared file). Finally click OK to save the changes.

Select to share a workbook

Step 4 The file then needs to be saved to a location that others will be able to access. This could be a shared folder, network drive, or even a OneDrive (for Excel 2013 users).

About advanced sharing features

You may have noticed the Advanced tab on the above Share Workbook dialogue box. Here is a quick overview on what these advanced features do:

Advanced options for sharing a workbook

Track changes: If you wish to track revisions then select the Keep Change History option. The fewer days that you select the smaller the size of the change log.

Update changes: If the Excel file in question is in regular use it makes sense to set up an automatic save. You can even be notified of what changes are being saved.

Conflicting changes between users: Take an instance where the same cell has been changed by two users, you can either have the option to decide which change gets saved or simply set it so that only saved changes have final say.

Include in personal view: An option to include any set filter and print views that other users may have applied.

Sharing does limit Excel features:

When sharing you can: When sharing you can’t:
Insert columns/rows Create a table
View existing charts Can’t create new charts
Use existing conditional formating but not edit Merge cells or split merged cells
View existing Macros with limitations Group or outline data


The Share Workbook feature in Excel is a really useful way to easily collaborate between different users and not have the headache of managing multiple file versions. Yes it does need some consideration in terms of certain limitations of Excel features and ensuring everyone can access the file. It may not be a bad idea to actually keep a spare sheet in the workbook with some sharing guidelines for all users.
With Excel 2013 the options to share are even more varied considering that you can share a file for access across multiple platforms and devices (such as iPads).

More resources on how to share files in Excel

How to share a file in Excel 2010.
Read more

How Getting Together Is Now Even Easier With Excel.
Read more

Use a shared workbook to collaborate.
Read more

How To Split A Stacked Chart In Excel

How to create AND split a stacked chart in Excel

Amongst the many charts available in Excel, some of the most popular are column charts, and the main variants being clustered and stacked. We’ll look at how to split a stacked chart in Excel, and to do this let’s start by creating a basic column chart.

Creating a column chart

In this example we are looking at regional sales data.

Sample sales data

Let’s create a clustered column chart of the above data.

Step 1 Select the range of data (as above, which will be A1:B4).

Step 2 Now select chart type, and “Clustered Column” from Charts options on the Insert ribbon.

a basic clustered column in Excel

Step 3 The following chart is now created.

regional sales in an Excel clustered column

This is all well and good we can compare regional sales performance, but it’s not so easy to compare how well each region performed against total sales. That’s where stacked column charts come into their own, let’s see how to build one.

Creating a stacked column chart

Step 1 Simply select the above chart then choose “Change Chart Type” from the Design ribbon.

change an Excel chart tyoe

Step 2 In the “Change Chart Type” dialogue box just choose the “Stacked Column” option as below.

changing an Excel chart into clustered chart

We now have a single stacked column chart for regional sales.

Excel stacked chart for sales regions

How to split a stacked chart in Excel

Now supposing we want to compare North & South as a stacked column against West and East as a stacked column. This can be achieved by splitting the above stacked chart.

Step 1 We need to change the layout of our data. So in this case we are going to select the two cells containing the “West” and “East” sales figures, then move them one column to the right. See below.

preparing Excel data for split stacked chart

Step 2 Now select the new range of data, in this case A1:C4, and as before when creating our original column chart but this time selecting the “stacked column” option. We end up with the following.

how to split a stacked chart in Excel

We now have our regional sales in a split stacked column chart.

By learning how to split a stacked chart in Excel you can now visualise data in a new and useful way.

More Excel chart related information:

How do you add a piechart and a bar / column chart on one worksheet?
Read more

How do I create a Floating Column Chart in Excel?
Read more

Excel 2013: 3 new ways to customise your charts

Present your data in a column chart

Using the Total cell style in Excel

Trying to review a spreadsheet with unformatted data can be a real eye sore, but by using the Total cell style in Excel you can quickly give your data more context.

An example of using the Total cell style in Excel

Below we have a list of sales figures for several sales reps in a travel company. In just a few steps by applying the Total style helps the “Total” column of sales figures stand out.

Total cell style in Excel

Using a couple of Excel styles can make all the difference

1. Select the range of cells you wish to format. Goto the Home tab, then the Styles group. Then click on the “More” button the more button in Excel to expand the group. (for Excel 2013 & 2010 click on Cell Styles button, see the second screenshot below)

Using the Total cell style in Excel 2007

How to select Total option from style section

Using the Total cell style in Excel 2010

Selecting Cell Styles in Excel 2013 and 2010

2. Now within the Style options, under the section, “Titles and Headings” just select the Total option. The selected range of cells will now display the Total formatting.

In the example above, we also repeated these two steps for the row with “John”, and with one further twist, also included an additional style option under “Themed Cell Styles” which was to select a colour.

Tip: If you are experimenting with different styles you may wish to remove a cell style, to do this simply select the range of cells you wish to “reset”, then go to the Style group (within the Home tab) and select Normal from the “Normal, Bad, Good, Neutral” section.

how to remove a style in Excel

To remove a style, just hit the Normal button

What we have covered here is a simple demonstration of how using styles can lead to more engaging and professional-looking spreadsheets. You can even create your own custom cell styles which may contain multiple formatting options and can be a real time saver when dealing with similar spreadsheets.

Found this useful? Learn more about Excel cell styles:-

  • How To Use The Cell Styles Functionality In Excel 2010. Read more
  • How do you group a selection of styles on a workbook? Read more
  • A comprehensive guide to applying, creating and removing cell styles



How to calculate age from date of birth using Excel

An easy way on how to calculate the age from date of birth using Excel formula

Here we’ll look at how using the Today() function along with a less well known function, Datedif(), calculates the age from a date of birth. Even if this isn’t a pressing need of yours the example below is a handy demonstration of the usefulness of these two functions in an Excel formula to calculate age from a date of birth.

The following formula uses both these functions to achieve the desired result.

where “B4” is the actual cell containing the date of birth.

Here’s the formula in action.

How to calculate age from date of birth using Excel

Formula using the Datedif() & Today() functions

Tip: You can also express the age as months, or even days by simply changing the “y” in the above formula to “m” or “d” respectively.

There are many ways to achieve the same result and the example above is one such way on how to calculate age from date of birth using Excel.

How Excel stores dates:

Dates and time are some of the most common types of data that people use in Excel and the way that Excel stores dates is quite different to how we would imagine.

Instead of storing a date as day, month, year (01/01/1900, for example) Excel actually allocates a serial number (which is generated from working out how many days have elapsed since the year 1900 to that date).  And yes, any date before 1900, as far as Excel is concerned, just doesn’t exist! This is why if you don’t have the correct cell formatting for date data you get a number bearing no relation to the date!

Additional resources:

You can view answers to actual Excel users’ questions related to time and date on the following links:

For a more in-depth look at how to use dates and times in Excel:


UK government pays £5.5m penalty for not listening to Microsoft

UK Government pays £5.5m to Microsoft

£5.5m says UK government should have taken Microsoft seriously about XP

Imagine you’re at an important hospital appointment and you’re kept waiting because the scanner, which potentially cost £100K, needs a re-boot. And then you discover it’s driven by Windows XP which is no longer supported by Microsoft. That would be insane wouldn’t it?

Sadly not. It’s a true story and it’s probably just the tip of the iceberg across the NHS. Just how big a problem it is we don’t know because, as The Register discovered, the Department of Health doesn’t actually know the penetration of XP amongst its million PCs spread across GP surgeries, trusts, hospitals and other organisations. An estimate from healthcare specialists EHI Intelligence back in September 2013 put the penetration of NHS PCs running XP at 85%. Its survey suggests that NHS managers were keeping their fingers crossed that either Microsoft would change its mind or some sort of deal would be done. Fortunately for them, the government was able to finalise a deal with Microsoft for its public sector users – but only for a year, only for security updates for XP, Office 2003 and Exchange 2003 and at a cost of around £5.5m. As people are wont to ask in such situations, how many nurses could that have paid for?

For many bodies such as the Metropolitan Police and HM Revenue and Customs, migrating away from XP will be completed by the end of the year. But it’s all a bit late and reeks of panic. The government’s central purchasing agency, the Crown Commercial Service, is putting on a brave face and claiming that its deal with Microsoft is saving the government £20m by centralising negotiations – but why has it had to happen in the first place?

“We see it as a serious case of hiding behind the sofa and hoping it will all go away,” says Rich. “Windows XP was born 13 years ago – that makes it venerable in technology terms. It can’t last forever.”

Microsoft has been flagging up the end of life for years and offering assistance in moving on. Yet it seems to have come as a surprise to many that’s it’s been true to its word. “Either they thought that the sheer numbers of XP users still out there would be persuasive in getting XP’s life extended or it’s an example of poor management”, says Rich Talbot, Trainer at Best STL. The NHS story suggests the latter. “We can’t even sigh and say that this is what happens in large public bodies, because organisations in every commercial space from small to large have missed the deadline too,” adds Rich.

Best STL hears grumbles about the greedy face of business forcing businesses to spend money when they can least afford it. Is Microsoft being fair in withdrawing support? Let’s look at its arguments.

  • Security issues leading to compliance risks. Security is definitely one of the biggest issues facing organisations today and Microsoft claims that an ageing OS can’t be protected sufficiently well. If that’s true, it’s important. And if organisations continue to use an operating system without updating security they will suffer. It’s quite likely that there’s a huge amount of malware silently sitting on XP systems today waiting to be initiated by attackers to steal data and devastate businesses. Even if the worst doesn’t happen, failing to comply with ever-tightening financial regulations is asking for huge financial penalties and loss of face.
  • Lack of ISV and hardware manufacturers’ support. Microsoft quotes Gartner research on its web site, arguing that a growing number of ISVs won’t support XP with new versions of their software and that manufacturers won’t support XP on their new PCs. On the other hand, one of the reasons that organisations may be holding back from upgrading is that they don’t know if their current legacy applications will work on Windows 7 or 8 or Linux or something else. Where there are specialised applications like the driver for that scanner, this could well be true – but, as Rich says, it’s time that someone found out.
  • Greater productivity with newer versions of Windows and Office. “Yes, it’s possibly true but it’s a very general claim and depends on your needs,” comments Rich. “We can all recognise this as marketing puffery.”

Is there an alternative argument that Microsoft is being harsh on government and businesses in times of economic difficulty? “We don’t think so,” says Rich. “The global financial crisis began over six years ago. How long can we keep claiming poverty? In any case, if the likes of Microsoft stopped driving for profitability, where would be the development that we all really need?”

So here’s the quandary. Microsoft is insisting that there will be no help beyond this final year for anyone. That’s hardly surprising and managers really need to avoid the temptation to sign up for the year and then promptly hide their heads again. So what’s the bottom line? Says Rich “If you’re running any applications on XP that are business-critical you need to take action now because lack of security and compliance are a real threat. If you’re driving important apps – such as that scanner – with XP then while security might not be your priority, lack of day-to-day support that puts the equipment out of action could be.”

Are you concerned about upgrading to a new version of Windows – we can help with migration and training for individuals and organisations - contact us for more information.

How to: Excel VBA clear clipboard

In Excel there’s a lot of data being moved around, perhaps via copy and paste. If you leave a lot of stuff lying around on in memory or cache you’ll get a message along the lines of “you’ve left a lot of information on the clipboard…”. Generally in VBA you can avoid this with other methods but sometimes copying and pasting is the only way. How do you clear the clipboard in Excel VBA?

excel vba clear clipboard

It’s done with a simple line of code which you place after the paste operation:

Application.CutCopyMode = False

You might see this in macro recorded code – it is created when you press ‘Esc’ on the keyboard to stop copying, or if you’ve simply pasted. This line of code empties the clipboard and clears the memory cache.

Smarter version

You can avoid these altogether by bypassing the clipboard entirely. This is one of those things you can only do with VBA. There’s a silent Destination argument of the Copy command, which isn’t obvious unless you press a space immediately after a Copy instruction. You certainly won’t get it from the macro recorder.

Here’s a chunk of code I just recorded, copying and pasting a block from one sheet to another:

    Application.CutCopyMode = False

Lots of typically superfluous code there. Lines that end “…Activate” are almost always useless, indicating which cell has been selected – why this is useful in this context I don’t know. Here’s a truncated version:

Range("A1:J4292").Copy Sheets("Sheet2").Range("A1")

With this command it doesn’t use the clipboard so no Excel VBA clear clipboard command required. And of course, we have one line of code instead of seven. Generally speaking less code means quicker execution, but there’s lots of exceptions to that.

If you want to explore this further, type up the .Copy bit then press space. Examine the arguments for further insight into how you can use this command.

Bonus marks – work with any range of data, from anywhere in the workbook

By prefixing the command with the source sheet (“Sheet1″ in this case), and adding CurrentRegion off the starting cell will continue selecting until it has captured all of the data (it will stop at completely empty rows and completely empty columns):

Sheets("Sheet1").Range("A1").CurrentRegion.Copy Sheets("Sheet2").Range("A1")

Excel VBA Uppercase, Lowercase and more

Ever received a spreadsheet and all the data on it is in the wrong case? You might received a dodgy dump of data from your website or database, or maybe the user-inputted data is poorly or inconsistently formatted. Here I’ll guide you through how to do Excel VBA Uppercase, lowercase and capitalising the first letter of a string. All these tasks are invaluable when you have to manipulate your data using VBA.

wrong kind of case. we want excel vba uppercase

Not this kind of case! (image copyright iconarchive)

Excel VBA Uppercase

First, converting strings to upper case through Excel VBA: you need the Ucase function. Let’s say you want to loop through column A, replacing the current cell with an upper case version:

For i = 1 to cells(Rows.Count, 1)

     Cells(i,1) = Ucase(Cells(i,1))

Next i

And that’s how you do Excel VBA Uppercase.

Excel VBA Lowercase

Lower case couldn’t be simpler: the Lcase function. Here’s an example of converting a variable to lower case:

Dim empStatus As String

empStatus = "CONTRACT"

empStatus = Lcase(empStatus)

MsgBox empStatus

How do I capitalise the first letter in a cell (or string) ?

Excel VBA does not have a native way to do this; you could use some clumsy string manipulation methods but let’s lean on the Excel functionality: the Excel Proper function. You can summon any function from Excel using the Application.WorksheetFunction method.

Here’s an example, capitalising the first letter of every cell in column C:

For i = 1 to cells(Rows.Count, 3)

     Cells(i,3) = Application.WorksheetFunction.Proper(Cells(i,3))

Next i

Three quick ways to manipulate text strings using Excel VBA,

How do I sort in Excel VBA?

Excel VBA is used for all sorts (!) of data manipulation tasks. One of the most common is sorting your data. Performing an Excel VBA sort seems trivial but some parts of it may misbehave if you’re not diligent.

I’ve seen some people try to write their own sort routine in Excel VBA, but frankly Microsoft has spent thousands (millions?) of dollars refining their Excel Sort technique, so why not just record a macro where you use that?

It starts simply enough. View > Macros > Record new macro, fill in the details, hit Sort then Stop Recording.

Play it back – seems fine.

Add some new rows, play it again – still fine.

But it isn’t.

The problem comes when you look at what has been sorted. If you notice, your macro has only worked on the same range as your original recorded macro. Extra data outside of that first selection will not be included.

A quick peek in the Visual Basic Editor (ALT + F11) will demonstrate this clearly.

excel vba sort

Boom, there it is. A reference to a fixed range of cells (A2:H31). Aside from the other typically overcautious lines from the macro recorder (thank goodness the xlPinYin method was set!) these cell references are the bits that will cause you problems. It’s the worst kind of error too, the one that doesn’t cause an error but silently causes havoc in the background.

The key is then to create a dynamic range. This is the source of many issues with beginner code in VBA. As with most VBA problems, there are many ways to solve it. Try this out:

Sub MySortMacro()

    Dim LastRow As Long
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    Range("A2:H" & LastRow).Sort Key1:=Range("C3:C" & LastRow), _
       Order1:=xlAscending, Header:=xlNo

End Sub

Let’s take a look at that.

    Dim LastRow As Long

Create a new variable called LastRow as a Long datatype (an integer that goes roughly up to 2 billion). Why a Long and not an Integer? An Integer goes up to ~36,000. If your data is 36,000+ rows, you will run into the overflow error. 2 billion is plenty!

    LastRow = Cells(Rows.Count, 1).End(xlUp).Row

Count how many rows are in the first column (,1). Go to that last cell and do a CTRL + up arrow motion. In most sets of data this will hit the last item of data in that column. Get the row number then store that in the LastRow variable.

    Range("A2:H" & LastRow).Sort Key1:=Range("C3:C" & LastRow), _
       Order1:=xlAscending, Header:=xlNo

This heavily truncated command concatenates (or ‘joins’) the LastRow variable to the ranges required. The “A2:H” bit is the full range of data, “C3:C” is which column you want to sort by.

There you go. Simpler code, easier to read and will work with any range of data.

You can get lots of great VBA tips like this on our Excel VBA Introduction / Intermediate courses, or Excel VBA Advanced training course.

Read about our Excel VBA training solutions

How to use a VLOOKUP function in Excel VBA

VLOOKUP is one of the most useful and versatile functions in Excel. As you work further with macros it’s not uncommon to make your create an Excel VBA VLOOKUP macro. With this you get the ability to reference your tables of data, but automated.

Wait, what’s a VLOOKUP function?

The Vertical Lookup is one of Excel’s most popular commands. It’s most common use allows you retrieve data from another table of data based on a key value. For example, in one Excel sheet you may have a list of one customer’s invoice numbers, and in another sheet a list of all your invoice numbers plus other columns, such as amount, customer and invoice date. A VLOOKUP function can use the invoice number as a reference point to extract one or more other related columns of data. This avoids sloppy copy-and-paste and ensures the data remains up to date.

excel vlookup function

An example of a simple VLOOKUP retrieving a ticket price for a given country.

There are other smart uses of the VLOOKUP, such as being able to search for duplicates, group values into buckets and check to see if items exists but this is enough detail for now.

For a more thorough discussion of the VLOOKUP function, check out our article here. Even better, come on one of our Excel Advanced courses!

So what about using Excel VBA VLOOKUPs?

You can retrieve data from sheet to sheet programmatically using VBA alone, usually with nested FOR NEXT loops and variables to track your current cell position. These can be a bit fiddly and the learning curve can be a little steep (if you want to learn how to do this check out our Excel VBA Introduction / Intermediate course).

an example of excel vba vlookup style functionality

The CopyRecords macro is simulating VLOOKUP-style functionality.

Luckily, VBA provides you with the Application.WorksheetFunction method which allows you to implement any Excel function from within your macro code.

So if your original VLOOKUP in cell B2 was something like this:

=VLOOKUP(Input!A2, Data!A1:X200, 5, FALSE)

The VBA version would look like this:

Range("B2") = Application.WorksheetFunction.VLookup(Sheets("Input").Range("A2"), Sheets("Data").Range("A1:X200"), 5, False)

Notice a couple of things:  I had to insert the Sheets and Range objects so VBA could properly interpret it.

I want a sneakier version!

Don’t want to do that Sheets and Ranges business? You can adopt a little-known punctuation trick in VBA that converts things into cell references: the square brackets. Did you know you can turn this:

Range("A1") = "Fred"


[A1] = "Fred"

With a little lateral thinking we can do the same to our VLOOKUP:

[B2] = [VLOOKUP(Input!A2, Data!A1:X200, 5, FALSE)]

Make it more robust

The code lines above will do the bare minimum. They’ll get the job done. What if you grab the result of the VLOOKUP and store it in a variable?

result = [VLOOKUP(Input!A2, Data!A1:X200, 5, FALSE)]

And then throw an unexpected value at it?

vba error 1004

Of course! Error 1004! Why didn’t I see that coming?

Let’s add a little error-handling so your code doesn’t come to a screeching halt. There’s a number of ways you can test this, and various things you can do with the error, so here’s only one suggestion:

On Error GoTo MyErrorHandler:

  result = [VLOOKUP(Input!A2, Data!A1:X200, 5, FALSE)]

  If Err.Number = 1004 Then
    MsgBox "Value not found"
  End If

Or possibly

On Error GoTo MyErrorHandler:

  result = [VLOOKUP(Input!A2, Data!A1:X200, 5, FALSE)]

  If Err.Number = 1004 Then
    result = ""
  End If

The first example throws the error right up in your face. The second one is a ‘silent’ error that pushes a null string into the result variable. Not usually advisable as you can’t actually spot the problem but on some occasions you just want to move past the code issue. You could replace the empty string with your own custom error message.

Make it dynamic

This is all well and good but what if your data grows and shrinks? You should be on the lookout for dynamic methods. You can consider things such as dynamic range names and similar, but here’s a VBA option you can consider:

    Dim ws As Worksheet
    Dim LastRow As Long
    Dim TargetRange As Range

    On Error GoTo MyErrorHandler:

    Set ws = Sheets("Data")

    LastRow = ws.Cells(Rows.Count, "X").End(xlUp).Row
    Set TargetRange = ws.Range("A1:X" & LastRow)

    result = Application.WorksheetFunction.VLookup(Sheets("Input").Range("A2"), TargetRange, 5, False)

    MsgBox result

    If Err.Number = 1004 Then
      MsgBox "Value not found"
    End If

Note I went back to standard sheet and range references; they’re tricky to mix and match with square bracket notation.

What’s happening here? We set up three variables, to house the sheet name, last row and final range. Then we calculate what the last used row is with the “xlUp” method. This is equivalent to pressing CTRL + up on the keyboard when working in Excel. This finds the last row on the worksheet, and finally we set this as the range used.

There’s lots of variations on this, depending on whether you need dynamic columns too and how regular your data is, but this is a great method for getting you started.

So there it is, from soup to nuts, ways to implement VLOOKUP functions in Excel VBA.

Looking for help on your VBA projects? We offer the UK’s largest schedule of VBA training events, with all versions and levels trained. The Introduction and Intermediate levels will give you all the tools you need to get started, while the Advanced course will allow you to hook up other Office applications and communicate with databases. We can also visit your offices to deliver training, or consult on your projects. We can also offer Access VBA and Word VBA too.

Read about our Excel VBA training solutions