Hi Richard,
I work with a CRM (Customer Relationship management software) called Capsule. I also use Mailchimp for mail shots. When I add a new prospective customer to Capsule I assign one or more market sectors using a tag. Eg if the prospect sells plastic trim into the Automotive industry I will probably assign it two sectors: "Sector:Automotive" and "Sector:Materials". I then upload the entry from Capsule into Mailchimp (the two programmes are compatible) and these tags will map into the various sector groups in Mailchimp. The point is that when I send mailshots I can target particular sectors.

The problem is that often I bulk upload entries into Capsule. If for example I have just been to a trade show, I will have 100 business cards which I will enter into an excel template (that has around a dozen columns like name, email address, location, sector etc) , save it as a csv file and then import into Capsule.

Until now I thought I only needed to assign one sector per prospect so there was no problem: i.e if I had entered just "Sector:Automotive" in the "Sector" column, then that would map fine into Capsule when doing the upload. But now as I said we want to be able assign two or more sectors to a prospect.. The question is how can we make the excel template so that the "Sector" column allows you to choose two or more sectors.

According to Capsule support, if you put in two or more sectors into a cell in the csv file, it will recognise them as separate tags if separated by commas, so in theory we could just write in the excel, for example: "Sector:Automotive, Sector:Materials" but this would be a nightname to fill in as there are at least a dozen sectors. What I think I need is some sort of checkbox list that includes all the available sectors, but I cant really understand Excel's help page about making checkbox lists or even if it is the right solution for me.

Is this clear, and can you help?!

Many thanks!


It sounds as though a formula that combines two cells with a comma between the data will work for you

Here is what the formula might look like--


What this formula says is take the value that is in cell J2 and add a comma and then add to that the value in cell K2.  This is done with the & sign.
So if the value in cell J2 is Vector 1 and the value in cell K2 is Vector 5 then the result will be Vector 1,Vector 5.

So if you put your two selected vectors in the column J and K, the formula will transfer them to the cell with the formula in it and put a comma in between.

Another suggestion---in column J and K you can use data validation in the cells.  By using this you can type in your list of vectors one time and use the data validation drop down list to make your selection.  To do this  first type your list of vectors on some part of the worksheet. Then go to the first cell where you want your vector selection to appear in my example above it would be cell J1. Then go to The toolbar and select the data tab then select the data validation icon.

A box will pop up and in the allow box, you should pick "list"  then go down to source box and click in that box,  then go to your list and paint the list. Then click ok. Now you can copy the data validation both down and across to the cells where you want it to apply simply by copying the cells.  Now when you want to select a vector you can click on the cell and then click on the drop down box symbol and a drop down will appear that will list all of the vectors in your vector list. Select the one that you want and it will appear in the cell.

Hope this all works for you.

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Richard Roberts


Can assist you in most areas of Excel, have been working with it for about 15 years in many types of applications, but primarily in financial and accounting applications. I am a CPA and many client or client problems have necessitated the use of excel. I am not an expert in charting, macros, or pivot tables.


Have been working with Excel for about 20 years primarily in accounting and financial areas.

BA, Certified Public Accountant

©2016 About.com. All rights reserved.