ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

Configuring and Using Combo boxes in Excel 2007

Updated on August 22, 2012

Introduction

Welcome to part two of my series on Excel. In part one I looked at how to set up checkboxes (http://robbiecwilson.hubpages.com/hub/Configuring-and-Using-Check-boxes-in-Excel-2007). These are very useful for recording situations where a yes / no answer or condition is required. In part two, I will examine Combo boxes. Combo boxes have a number of advantages over check boxes and other methods of gathering input from other users of your Excel spreadsheets.

  • Combo boxes give you the option of a configurable list in a drop down menu as a posed to a check box which is binary (on / off, yes / no etc).
  • You have more flexibility of response compared with check boxes and by linking the output of the combo box to another cell, the selected answer from the combo boxes drop down list can be stored as a number for further manipulation. You could for example, set up combo boxes so that people can select how often each day do a particular task and then take those responses and manipulate this data directly from the combo boxes.
  • By defining the drop down lists you have the added advantage of controlling a person’s responses to a question to those options available which enables your data to be more robust and more clearly defined.

As with creating check boxes, the developer tab will have to be enabled to access them. Enabling them is also illustrated in part one (http://robbiecwilson.hubpages.com/hub/Configuring-and-Using-Check-boxes-in-Excel-2007).

Creating Combo boxes

The creation of a combo box in Excel 2007 is very straightforward. Click on the Developer Tab, then select Insert and click on Combo Box in the Forms Control section as shown below. As with a checkbox, Excel will allow you to specify the size and location of your combo box using the cursor. To resize and move the combo box, right click on the combo box you want to alter. You can move it when the cursor is the cross formed by two arrows or by selecting the circles you can resize it. Don’t copy the combo boxes just yet; there is one more step we have to do before we can duplicate our combo boxes.

Configuring Combo boxes

The main advantage of a combo box over a check box is the ability to create drop down lists. You can have anything appear in your list that your heart desires which gives you far more flexibility over checkboxes. To create the list, type the list into a series of cells in one column. I have used Yes, N/A and No in mine. To allow your combo box to use this list, right click on your combo box and select Format Control and then the Control Tab. This screen has two fields we will configure, firstly let’s create our drop down list.

Click on the small red arrow to the right of Input range and select your column of values. It is important that you do not erase these cells, so it may be a good idea to hide the column or the sheet they are on as changing these will change your drop down lists (select the column and then right click and select hide).

The second field we are configuring will be the cell link field. This field is used to create the data we will use for conditional formatting. If you do not intend to do so, you can ignore this field. If you wish to use conditional formatting, click the red arrow to the right of cell link as shown below and select a cell. Once completed, this column will be hidden as well. How this linking works is quite clever. Each item on your drop down list is given a number. So in my example, Yes = 1, N/A = 2 and No = 3. So I now have a cell that contains a numerical representation of my selection that I can manipulate for conditional formatting.

Creating more combo boxes

To create more combo boxes, right click on your first combo box and select copy and then paste. Combo boxes tend to snap into existing cells much more accurately which makes placing them with your Excel spreadsheets much simpler than check boxes which tend to end up in a bit of a mess requiring them to be moved around more.

One thing to note when copying and pasting combo boxes is that they will all be linked to the cell used in the first combo box. To tidy this up for conditional formatting later, right click each newly created combo box and select Format Control / Control and update the cell link to the correct cells.

Combo boxes in action

So now we have configured and created some combo boxes and you will have something along the lines of my spreadsheet below. The drop boxes in column C are linked to the cells in column A which now display the results of your selection from the combo box in a numerical format which can easily be manipulated.

Conclusion

Thanks for reading my hub on configuring combo boxes. Please join me for my hub on conditional formatting for combo boxes and check boxes in Excel 2007. I hope you have enjoyed reading my hub as much as I have enjoyed writing it and that you found it useful and informative. Please feel free to leave a comment below.

I also have a number of other hubs on aspects of Excel 2007, covering everything from Conditional Formatting to creating charts and graphs. I have an Index hub which also covers how I successfully transitioned from Excel 2003 to 2007 as well as outlining my other Excel 2007 hubs which can be found here

http://robbiecwilson.hubpages.com/hub/How-to-adjust-to-Excel-2007-from-previous-versions-as-well-as-step-by-step-guides-to-many-functions-in-Excel-2007


Comments

    0 of 8192 characters used
    Post Comment

    No comments yet.

    working

    This website uses cookies

    As a user in the EEA, your approval is needed on a few things. To provide a better website experience, hubpages.com uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

    For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at: https://hubpages.com/privacy-policy#gdpr

    Show Details
    Necessary
    HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
    LoginThis is necessary to sign in to the HubPages Service.
    Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
    AkismetThis is used to detect comment spam. (Privacy Policy)
    HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
    HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
    Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
    CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
    Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
    Features
    Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
    Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
    Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
    Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
    Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
    VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
    PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
    Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
    MavenThis supports the Maven widget and search functionality. (Privacy Policy)
    Marketing
    Google AdSenseThis is an ad network. (Privacy Policy)
    Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
    Index ExchangeThis is an ad network. (Privacy Policy)
    SovrnThis is an ad network. (Privacy Policy)
    Facebook AdsThis is an ad network. (Privacy Policy)
    Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
    AppNexusThis is an ad network. (Privacy Policy)
    OpenxThis is an ad network. (Privacy Policy)
    Rubicon ProjectThis is an ad network. (Privacy Policy)
    TripleLiftThis is an ad network. (Privacy Policy)
    Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
    Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
    Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
    Statistics
    Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
    ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
    Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)