Search Not Just Numbers

Loading...

Tuesday, 12 August 2014

Excel Tip: Add a simple tick box to your spreadsheet - and use its result!

Do you ever wonder how you get those professional looking tick boxes (or check boxes) in your spreadsheet?

They're easier to add than you think, and it's pretty straight-forward to have calculations depend on the result of the tick box.

In this post, I'll show you how to do both.

Before you can add a tick box, you need the Developer Ribbon enabled. If you don't see it as one of the named Ribbons at the top of Excel, then you can add it by selecting File - Options - Customize Ribbon and then ticking Developer in the list of Main Tabs on the right-hand side.

To insert a tick box, go to the Developer ribbon and click Insert in the drop-down menu that appears, click the Check Box (Form Control) icon. This is the tick box under the heading Form Controls - if you hover over it you will see the name "Check Box (Form Control)" appear.

Your cursor will become a cross and you can now click where you want the tick box to appear.

The box will appear with some default text to the right of it (this will usually be Check Box 1 if this is the first one that you have inserted into the spreadsheet. This text can be edited (or deleted) by double-clicking on the text and editing or deleting as required.

This is your tick box created and you can now click it to toggle between ticked and unticked.

However, I did say that I would show you how to use the result of the tick box.

If you right-click on the tick box you will see the menu option "Format Control". Click on this and go to the Control tab.

You will see a box entitled Cell Link. Enter a cell reference here, where you wish to store the result of the tick box (let's say C3). Now when you click the tick box, cell C3 will switch to show TRUE, and when you untick the tick box, C3 will show FALSE.

You can then use cell C3 in a formula. The most common way to use it would be as the criteria argument of an IF function. As C3 contains the logical value TRUE or FALSE, then this is all you need as your criteria argument, so:

=IF(C3,100,0)

will return the value 100 if the tick box is ticked, or 0 if not.

It's as simple as that!

Excel Expert Course

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

10 comments:

  1. Really clever, thank youl

    ReplyDelete
  2. Lastly,be acquainted of gucci replica the item's affairs prices.A accepted replica backpack should alone amount you about $150 to $250.Be alert if a website sells you handbags for over $300 because it is over-priced.On the added hand,yield a afterpiece attending at the architecture and superior of architecture of the backpack abnormally if it the amount is beneath $100.Always accumulate in apperception that if the amount is absolutely arrangement again the superior will be mostly arrangement also.So you should accept chanel replica a acceptable compassionate of the accepted costs of replica handbags or at atomic ask a acquaintance to advice you acquisition superior handbags.Women adulation appearance and they chase the appearance trends of the celebrities.They wish to accept the handbags the celebs carry.Well celebrities do not pay for louis vuitton replica the handbags they carry.The appearance designers present the handbags to them.The almsman will never apperceive that you accept able them with Swiss replica watches.In actuality they will be captivated to accept these Swiss replica watches back they see it as the original.Go out today and acquirement a brace of cartier replica these Swiss replica watches.Do not absolute yourself to just one and let your fantasy run agrarian while you are purchasing these Swiss replica watches.

    ReplyDelete
  3. Article useful too, I did try and succeed

    ReplyDelete
  4. I also want to use excel to create the box marked it very quickly and beautifully. Under the guidance of the articles I have done, thanks

    ReplyDelete
  5. feel very good guide, thank offline kizi

    ReplyDelete