Skip to content
Tutorials Center
My learning

Topics

Lesson 71 of 93 ยท Data Cleaning and Import

Checkboxes, Checkmarks, Hyperlinks, Pictures and Data Entry Forms

Add Excel checkboxes linked to cells, insert checkmark symbols, create or remove hyperlinks, use HYPERLINK and IMAGE, enter line breaks, use the built-in Data Form and sign a workbook.

Spreadsheets are not only for numbers: people tick tasks off, click links to jump to other sheets, add logos and fill in records one at a time. Excel has dedicated tools for each of these. This lesson covers checkboxes that drive formulas, checkmark symbols, hyperlinks (and removing them in bulk), the HYPERLINK and IMAGE functions, pictures in cells, line breaks and bullets, the built-in Data Form for entering records, and digital signatures.

Excel cannot run on this site, so the menus and formulas are real Excel but read-only. The TryIts are JavaScript stand-ins that model the cell logic: a to-do list whose progress follows linked checkbox cells, and building and cleaning up hyperlink formulas. They are teaching models, not Excel's code.

Checkboxes linked to cells

The new in-cell checkbox (Microsoft 365)

Recent Microsoft 365 builds have a simple Insert > Checkbox that puts a real checkbox inside a cell. The cell then contains TRUE when checked and FALSE when not, so formulas read it directly. Select a range, choose Insert > Checkbox, and it appears in every cell. Delete the box by selecting the cell and pressing Delete (this clears the value too), or format as General to see the TRUE/FALSE behind it. Because the value is a Boolean, =COUNTIF(B2:B10, TRUE) counts ticked items.

Developer-tab checkboxes (all versions)

  1. Enable File > Options > Customize Ribbon > Developer.
  2. Developer > Insert > Form Controls > Check Box, then click on the sheet and drag.
  3. Right-click the box, choose Format Control > Control and set Cell link to a cell such as $D2. The linked cell shows TRUE or FALSE.
  4. Edit the label by right-clicking and choosing Edit Text (or delete it).
  5. Copy and paste the box down the column; each copy needs its own cell link (the link does not shift automatically, so set each one).

Form control checkboxes float above the cells, so set Format Control > Properties > Move and size with cells to keep them aligned when rows are sorted or resized. Sorting a list does not move the boxes, so sorting is a bad fit for checkbox lists; the in-cell version sorts correctly.

Using the TRUE/FALSE results

TEXT
Done count    =COUNTIF(D2:D11, TRUE)
Progress      =COUNTIF(D2:D11, TRUE) / COUNTA(A2:A11)
Status text   =IF(D2, "Done", "Open")
Strikethrough =conditional formatting rule  =$D2=TRUE   (Format: Strikethrough, grey font)

For the formatting rule see Conditional Formatting. Show progress with a data bar or a doughnut chart linked to the percentage cell (Dashboards).

JS A checklist whose progress follows the linked cells

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Checkmark symbols

A checkmark symbol is just text, so it works in any cell and prints well.

  • Insert > Symbols > Symbol, font Wingdings or Segoe UI Symbol, and pick a tick (โœ“) or cross (โœ—). In Windows, Alt+251 / Alt+252 in a Wingdings-formatted cell also produce marks.
  • Copy โœ“ or โœ” from anywhere and paste them as text; or use =UNICHAR(10003) (โœ“), =UNICHAR(10004) (โœ”) and =UNICHAR(10007) (โœ—).
  • Make them data driven: =IF(B2>=C2, UNICHAR(10004), UNICHAR(10007)).
  • Or use a custom number format so a 1 shows a tick and a 0 shows a cross: [=1]"โœ”";[=0]"โœ—";General. The cell still holds the number.

Icon sets in conditional formatting (Sparklines, Icons and In-Cell Visuals) give colored check and cross icons with no formulas.

Insert, edit and remove

Press Ctrl+K on a cell (Insert > Link). You can link to an Existing File or Web Page, to a Place in This Document (a sheet and cell, or a named range), to a new document or to an E-mail Address. Edit with Ctrl+K again; follow with a click. To select a link cell without opening it, click and hold, or click the cell next to it and use the arrow key.

To remove a link: right-click > Remove Hyperlink (keeps the text). To remove all hyperlinks on a sheet at once: select the data (Ctrl+A), right-click, Remove Hyperlinks. Excel also adds links automatically when you type a web address; to stop that, File > Options > Proofing > AutoCorrect Options > AutoFormat As You Type and untick "Internet and network paths with hyperlinks". Pasting text with Paste Special > Values or Keep Text Only avoids bringing links along.

=HYPERLINK(link_location, [friendly_name]) builds a link with a formula, so it can be generated from data.

TEXT
=HYPERLINK("https://example.com/orders/" & A2, "Open order " & A2)
=HYPERLINK("#'Sales Data'!A1", "Go to Sales")            ' jump inside the workbook (note the #)
=HYPERLINK("mailto:" & B2 & "?subject=Invoice " & A2, "Email " & B2)
=HYPERLINK("C:\Reports\" & C2 & ".pdf", "Open PDF")

Because it is a formula, you can copy it down a column and build a table of contents sheet: =HYPERLINK("#'" & sheetname & "'!A1", sheetname). Remember that to click an HYPERLINK cell you click the text; there is no separate editing dialog, you edit the formula.

JS Building and removing hyperlinks as text

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Pictures, IMAGE and logos

  • Insert > Pictures places an image that floats over the cells. Choose Place in Cell (Microsoft 365) to put it inside a cell so it sorts, filters and resizes with the cell.
  • =IMAGE(source, [alt_text], [sizing], [height], [width]) (Microsoft 365) loads a picture from an https:// address into the cell, which suits product catalogs where column A holds a picture URL: =IMAGE(B2, A2, 1) fits the image to the cell. Use sizing 0 to fit, 1 to fill, 2 for the original size and 3 for a custom size. Only HTTPS sources work, and the picture must be reachable by the viewer.
  • To keep a picture with its cell on a floating image, use Format Picture > Properties > Move and size with cells.
  • To remove a picture background, Picture Format > Remove Background.

Line breaks and bullets in a cell

  • Alt+Enter inserts a line break inside a cell (turn on Wrap Text to see it). In formulas the line-break character is CHAR(10): =A2 & CHAR(10) & B2.
  • To remove line breaks from imported text: =SUBSTITUTE(A2, CHAR(10), " ") (Cleaning Messy Data).
  • A bullet is just a symbol. Press Alt+7 (numeric keypad on Windows) for โ€ข, or use =UNICHAR(8226), and combine with Alt+Enter for a bulleted list inside one cell. A custom number format โ€ข @ adds a bullet before any text without changing it.

The built-in Data Form

For a list with many columns, a form is easier than typing across a row. The Data Form is built in but hidden from the ribbon:

  1. Click a cell in a list with headers, or format it as a Table.
  2. File > Options > Quick Access Toolbar, choose Commands Not in the Ribbon, add Form, and click OK.
  3. Click the Form button. A dialog shows one record at a time with a box for each column.
  4. Use New to add a row, Find Prev/Find Next to scroll, Criteria to search, Delete to remove a record. Columns holding formulas are shown as read-only labels and calculate on their own.

The Data Form gives a simple entry screen without code. For more design control, build a UserForm in VBA (VBA Events, UserForms and Controls) or use Microsoft Forms for web-based entry. Data Validation keeps what people type within limits.

Digital signatures

A digital signature certifies that a workbook came from you and has not changed since you signed it. In Microsoft 365: File > Info > Protect Workbook > Add a Digital Signature. You need a certificate: one from your organization or a trusted authority, or a self-signed certificate (valid on your own computer only). Excel records a signature that is invalidated if anyone edits the file, which is the point. A signed workbook is marked Final and read-only. Do not confuse this with a Signature Line (Insert > Text > Signature Line), which is a visual placeholder for a handwritten-style signature. Macro projects can be signed separately in the VBE (Tools > Digital Signature) so users can enable trusted macros (Collaboration and Protection).

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does the cell linked to a checkbox contain when the box is ticked?

    The linked cell holds the Boolean TRUE when ticked and FALSE otherwise, so formulas such as COUNTIF(range, TRUE) can use it.
  2. 2. Which formula creates a link that jumps to cell A1 of a sheet called Costs in the same workbook?

    The # prefix tells HYPERLINK to look inside the current workbook. Without it Excel treats Costs!A1 as a file path.
  3. 3. How do you put a line break inside a cell?

    Alt+Enter inserts a line break (CHAR(10)). Enter alone moves to the next cell. Turn on Wrap Text to see the break.
  4. 4. Why does the Data Form need to be added to the Quick Access Toolbar?

    Form is listed under Commands Not in the Ribbon. Add it to the Quick Access Toolbar to use it.

Summary

  • Checkboxes (in-cell, or Developer form controls with a Cell link) store TRUE/FALSE that formulas can count and format on.
  • Checkmarks are symbols: UNICHAR(10004), Wingdings, or a custom number format.
  • Ctrl+K inserts links; HYPERLINK("#'Sheet'!A1", name) builds internal links; right-click > Remove Hyperlinks clears them in bulk.
  • IMAGE(url) and Place in Cell put pictures inside cells; Alt+Enter makes line breaks (CHAR(10)).
  • Add the Form command to the Quick Access Toolbar for the built-in Data Form; digital signatures detect later edits.

Next: Macros and the Macro Recorder.