{"id":11498,"date":"2021-12-21T17:59:20","date_gmt":"2021-12-21T16:59:20","guid":{"rendered":"https:\/\/careerfoundry.inbearbeitung.de\/en\/?p=11498"},"modified":"2023-08-31T19:28:58","modified_gmt":"2023-08-31T17:28:58","slug":"how-to-create-a-checkbox-in-excel","status":"publish","type":"post","link":"https:\/\/careerfoundry.inbearbeitung.de\/en\/blog\/data-analytics\/how-to-create-a-checkbox-in-excel\/","title":{"rendered":"How to Create a Checkbox in Excel: A Step-by-Step Guide"},"content":{"rendered":"

Checkboxes in Excel are a very useful tool to make your spreadsheets more interactive for users. They are an instantly recognizable form control that users will immediately know how to use.<\/span><\/p>\n

In this article, we\u2019ll show you how to insert a checkbox in Excel. You will also learn how to edit the checkbox properties, and write formulas to work with the checkbox selections.<\/span><\/p>\n

The example used in this tutorial is to use checkboxes to make an interactive chart. This is a very useful scenario for using checkboxes. It takes the chart beyond the static boring charts created by many.<\/span><\/p>\n

You can learn even more Excel analysis skills with CareerFoundry\u2019s free 5-day data short course<\/strong><\/a>.<\/p>\n

In this checkbox tutorial, I\u2019ll cover:<\/span><\/p>\n

    \n
  1. What is a checkbox in Excel?<\/span><\/a><\/li>\n
  2. How to insert a checkbox in Excel<\/span><\/a><\/li>\n
  3. How to modify the properties of a checkbox<\/span><\/a><\/li>\n
  4. Using formulas with checkboxes<\/span><\/a><\/li>\n<\/ol>\n

    You can <\/span>download the Excel workbook<\/span><\/a> to follow along with the examples in this article.<\/span><\/p>\n

    So, let\u2019s dive in and learn how to use checkboxes in Excel.<\/span><\/p>\n

    1. What is a checkbox in Excel?<\/span><\/h2>\n

    A checkbox is a control in Excel that is most commonly used when creating forms and checklists. It is a simple and effective way of creating interaction for the users of your spreadsheets.<\/span><\/p>\n

    Checkboxes enable a user to make multiple selections. If you want a control to only allow a single selection, you want an option button control.<\/span><\/p>\n

    2. How to insert a checkbox in Excel<\/span><\/h2>\n

    To insert a checkbox, you first need to ensure that the Developer tab is visible on the Ribbon of Excel.<\/span><\/p>\n

    To show the Developer tab:<\/span><\/p>\n

      \n
    1. Right click anywhere on the Ribbon and click <\/span>Customize the Ribbon<\/b>.<\/span><\/span>\"A<\/li>\n
    2. Check the box for the <\/span>Developer<\/b> tab in the list on the right. Click <\/span>Ok<\/b>.<\/span><\/li>\n<\/ol>\n

       <\/p>\n

      \"The<\/p>\n

       <\/p>\n

      With the Developer tab visible, we can now insert a checkbox. But first, let\u2019s have a quick look at why we want a checkbox.<\/span><\/p>\n

      In the following image, we have sales data for the months of four years. 2021 is incomplete as the year is just finishing.<\/span><\/p>\n

      \"A<\/p>\n

      Having all four years in the line chart is a little messy and hard to read. So, let\u2019s insert checkboxes and enable the user to select the year(s) to compare 2021 against.<\/span><\/p>\n

      To insert a checkbox in Excel:<\/span><\/p>\n

        \n
      1. Click <\/span>Developer <\/b>on the Ribbon.<\/span><\/li>\n
      2. Click <\/span>Insert <\/b>and click the <\/span>Check Box (Form Control)<\/b> from the menu.<\/span><\/span>\"The<\/li>\n
      3. Click on the sheet where you want the check box to appear.<\/span><\/li>\n
      4. Click on the text of the checkbox to edit it. We will use the names of the years as labels, starting with \u201c2018\u201d.<\/span><\/li>\n<\/ol>\n

         <\/p>\n

        \"The<\/p>\n

         <\/p>\n

        I have moved the chart further down the worksheet from the previous image. This is to make room for some formulas that are coming soon.<\/span><\/p>\n

        If you click on the sheet off the checkbox, you will no longer be in edit mode and the checkbox is now clickable. If you click it, the box will check. This does not perform any purpose yet (we will need the formulas).<\/span><\/p>\n

        The following image shows the hand cursor indicating the checkbox is ready to be checked and unchecked.<\/span><\/p>\n

        \"A<\/p>\n

        Repeat this task to create two more checkboxes on the sheet, and label them \u201c2019\u201d and \u201c2020\u201d.<\/span><\/p>\n

        \"A<\/p>\n

        These checkboxes are quite disorganized. To align their left edges perfectly:<\/span><\/p>\n

          \n
        1. Right-click the first checkboxes to select it.<\/span><\/li>\n
        2. Press and hold <\/span>Ctrl <\/b>and left-click the other two checkboxes.<\/span><\/li>\n
        3. Click the <\/span>Shape Format<\/b> tab on the Ribbon, click <\/span>Align<\/b> and <\/span>Align Left<\/b>.<\/span><\/li>\n
        4. Click <\/span>Align <\/b>and <\/span>Distribute Vertically<\/b> to evenly space the checkboxes.<\/span><\/li>\n<\/ol>\n

           <\/p>\n

          \"The<\/p>\n

           <\/p>\n

          3. How to modify the properties of a checkbox<\/span><\/h2>\n

          To modify the properties of a checkbox:<\/span><\/p>\n

            \n
          1. Right-click on the checkbox and click <\/span>Format Control<\/b>.<\/span><\/li>\n<\/ol>\n

            \"The<\/p>\n

             <\/p>\n

            There are a few useful properties in this window. Let\u2019s begin with the most important property: the Cell link.<\/span><\/p>\n

            Each checkbox needs to be linked to a cell in the workbook. When a user interacts with a checkbox by either checking or unchecking it, the status of the checkbox will appear in the cell it is linked to.<\/span><\/p>\n

              \n
            1. Click on the <\/span>Control <\/b>tab, and click in the <\/span>Cell link<\/b> field.<\/span><\/li>\n
            2. Click the cell you want to link it to and click <\/span>Ok<\/b>.<\/span><\/span><\/span><\/span>In this example, the 2018 checkbox has been linked to cell H14. This will be just below the chart range we will create with our formulas.<\/span>\"The<\/li>\n
            3. \u00a0Repeat the cell link for the other two checkboxes. Link the 2019 checkbox to cell I14 and 2020 to cell J14.<\/span><\/li>\n<\/ol>\n

               <\/p>\n

              \"Cells<\/p>\n

               <\/p>\n

              Another property of the checkboxes which is not as important, but can be very useful, is to stop them moving or resizing when rows and columns are added, deleted, and resized.<\/span><\/p>\n

              This setting can be found on the <\/span>Properties <\/b>tab of the Format Control window for each checkbox.<\/span><\/p>\n

              \"Inserting<\/p>\n

              4. Using formulas with checkboxes<\/span><\/h2>\n

              The final step to get the checkboxes to control the chart, is to create a new chart source range and use formulas to display the year’s sales value only if the checkbox is checked.<\/span><\/p>\n

                \n
              1. Prepare the chart area as shown in the following image. The headers, month labels, and values for 2021 have all been copied over to the new chart range.<\/span><\/span>\"A<\/li>\n
              2. In cell H2, write the following formula to show the sales value if the 2018 checkbox is checked, otherwise display the NA() error (this prevents the chart plotting the data).
                \n<\/span>
                \n\"A
                \n<\/span><\/span><\/li>\n
              3. Fill the formula to range I2 and J2. Then fill the formula down to row 13 to complete the chart range.\"A<\/span><\/li>\n
              4. Adjust the chart’s data source to use the new calculated range, or re-create the chart (whatever you find easiest).\u00a0<\/span><\/span><\/span>Now, when you interact with the checkboxes the chart changes.<\/span>\"A<\/li>\n
              5. Hide the gridlines on the sheet, to improve the look of the checkboxes. To do this: click <\/span>View <\/b>and uncheck the <\/span>Gridlines <\/b>box.<\/span><\/li>\n
              6. Continue to make improvements to the chart and also the layout of the checkboxes to fit your needs.<\/span><\/li>\n<\/ol>\n

                This was a simple example with a chart that will hopefully give you ideas of where you could implement checkboxes in your Excel reports, forms, and models.<\/span><\/p>\n

                You can use any formula from the result of a checkbox and they provide a great way of creating interaction with the users of your spreadsheets.<\/span><\/p>\n

                For a hands-on introduction to the field of data analytics, try out <\/span>this free five-day short course<\/span><\/a>. And, for more Excel tutorials, check out the following:<\/span><\/p>\n