学习如何在Excel中创建清单对许多人来说是一个改变游戏规则的方法。制作清单将帮助您跟踪许多日常事务。例如,清单可以帮助您记住旅行时要带什么或烹饪食谱时可用的成分。
然而,并不是每个人都精通电子表格,并且可能会发现在Excel中创建清单很困难。这篇文章将讨论如何在Excel中制作清单,以及将其与其他电子表格功能集成的一些提示。
如何在 Excel 中创建清单(How to Create a Checklist in Excel)
创建清单的第一步是列出需要确认的项目或活动。以下是您应该在Excel 电子表格(Excel spreadsheet)上执行的操作:
- 要打开Excel,请单击“开始”菜单(Start menu)旁边的搜索按钮(search button)。键入“ Excel ”并单击第一个结果。
- 在一个新的电子表格上,在其中一个单元格中输入清单的名称,最好在 A1 中,这样您就可以轻松了解清单的内容。
- 选择您将在其中列出项目的列(例如:A 列)。然后,单击要放置第一个项目或活动(item or activity)的单元格并键入它。
- 在列的后续单元格中键入其余条目。接下来,选择要放置复选框的列。在本例中,我们将其放在 B 列中,因为它位于我们列出的条目旁边。然后,按照以下说明进行操作。
如何在 Excel 中使用复选框(How to Use Checkboxes in Excel)
以下是如何在Excel中制作一个复选框,您可以勾选该复选框以指示输入已完成:
- 检查Microsoft Excel 工具栏(Microsoft Excel toolbar)上是否有“开发人员(Developer)”选项卡。如果没有,请单击文件(File)。
- 向下滚动并单击选项(Options)。然后,单击自定义功能区(Customize Ribbon)。
- 前往面板右侧并勾选Developer 框(Developer box),然后单击OK。
- 单击开发人员选项卡。
- 单击“插入(Insert )”图标,然后在“表单控件(Form Controls)”下选择“复选框”(Checkbox)。
- 选择要放置复选框的单元格,最好在列表中的条目旁边。
- 您可以通过删除默认复选框文本(default checkbox text)并键入单词或短语(word or phrase)或完全删除文本以仅保留复选框来更改复选框上的文本(tick box)。
- 如果要移动复选框,请将其拖到所需位置。
- 要复制后续单元格上的确切复选框,请单击它。然后,将鼠标悬停在框的右下角并将其向下拖动(或拖动到要放置复选框的任何方向)。
重要提示:(Important tip: )格式化单元格上的复选框时,按住Ctrl键并按左键单击(left-click)以修改或调整复选框的形状,而不是勾选它。
如何在 Excel 清单中链接单元格(How to Link Cells in an Excel Checklist)
1. 如果您想计算选中的框,请将复选框链接到另一个单元格。
2. 右键单击复选框并选择(checkbox and select) 格式化控件(Format Control)。
3. 转到控制(Control)选项卡。前往Cell Link选项,输入要链接的Cell 名称。(Cell name)
您会注意到,如果您勾选复选框,链接单元格将显示TRUE。
取消选中它将产生FALSE。
4.对列中必须链接的其他单元格 重复步骤 1 和 2。(Repeat)
5. 要获得勾选了多少框的摘要,请单击要显示结果的单元格。然后,type =COUNTIF( C4:C10 ,TRUE)。将C4:C10替换为(C4:C10)cell range的开始和结束单元格。
6. 如果要隐藏显示TRUE/FALSE值的列,请单击该列(例如:C 列)。接下来,按右键单击(right-click)并选择弹出菜单(popup menu)底部的隐藏(Hide)。
使用条件格式修改 Excel 清单 (Modify Your Excel Checklist Using Conditional Formatting )
电子表格的条件格式可以帮助您的数据脱颖而出并使其具有视觉吸引力。
1. 选择您要修改的单元格,然后单击主页(Home)选项卡。转到工具栏的右侧,然后单击(toolbar and click) 条件格式(Conditional Formatting)。
2. 选择新规则(New Rule)。它将显示几种规则类型,您可以使用它们来编辑选定的单元格。现在,选择使用公式来确定要格式化的单元格(Use a formula to determine which cells to format)。
3. 在下面的文本框中,Edit the Rule Description type =$C4。接下来,选择格式(Format),然后选择填充(Fill)。转到颜色(Color)并更改单元格的字体颜色(例如:绿色)。选择确定(OK)。
注意:(Note: )不要忘记修改单元格名称(cell name)。我们在这里使用的(C4)只是一个例子。
4. 请注意,当您勾选单元格旁边的链接复选框时,文本将变为绿色。
5. 对其余条目重复步骤 1-4。
重要提示:(Important Tip:)按Ctrl + Shift key并将单元格拖动到要复制复选框的位置,以产生与复制粘贴相同的结果。此方法还将使复选框保持对齐。
如何制作交互式 Excel 清单(How to Make an Interactive Excel Checklist)
如果您想跟踪您的食谱待办事项清单的进度,请将清单整合到任务完成的百分比中。这是你如何做到的:
- 使用我们当前拥有的示例,单击要显示任务进度(task progress)的任何单元格(示例:单元格 E7(Cell E7))
- 键入以下 COUNTIF 公式=COUNTIF($C$4:$C$10,TRUE)。此公式将计算复选框被勾选的条目总数。
- 要跟踪所有条目的完成率(completion rate),请在要显示的单元格中输入此内容(例如:单元格 F4(Cell F4))=COUNTIF($C$4:$C$10,TRUE)/7*100。注意:不要忘记(t forget)将“7”替换为列表中的条目数。
您的清单中有什么? (What’s in Your Checklist? )
在Microsoft Excel(Microsoft Excel)中创建清单很有趣,并且可以帮助您对要跟踪的活动或项目(activity or items)感到兴奋。无论您是制作杂货清单还是为商店创建库存,清单都是避免丢失任何物品的最佳方法之一。接下来,在(Next)Excel中创建一个下拉列表,以便更轻松地在电子表格中输入常用数据。
How to Make a Checklist in Excel
Learning how to create a checklist in Excel is a game-changer for many people. Making a checklist will help уou keep track of many everyday things. For instance, a checklist can help you remember what to bring in your travels or the ingredients available when cooking a recipe.
However, not everyone is well-versed in spreadsheets and may find it tough to create a checklist in Excel. This post will discuss how to make a checklist in Excel, plus some pointers on integrating it with other spreadsheet features.
How to Create a Checklist in Excel
The first step in creating a checklist is coming up with a list of items or activity that needs to be confirmed. Here’s what you should do on the Excel spreadsheet:
- To open Excel, click on the search button next to the Start menu. Type “Excel” and click on the first result.
- On a new spreadsheet, type the name of the checklist in one of the cells, preferably in A1, so that it will be easy for you to know what the list is about.
- Select a column where you will list the items (example: A column). Then, click a cell where you want to put the first item or activity and type it.
- Type in the rest of the entries on the succeeding cells of the column. Next, select a column where you are going to put the checkbox. We’ll put it in column B in this example since it’s next to our listed entries. Then, proceed with the instructions below.
How to Use Checkboxes in Excel
Here’s how to make a checkbox in Excel which you can tick to indicate that the entry is completed:
- Check if you have the Developer tab on the Microsoft Excel toolbar. If not, click File.
- Scroll down and click Options. Then, click Customize Ribbon.
- Head to the right side of the panel and tick the Developer box before clicking OK.
- Click the Developer tab.
- Click the Insert icon, and under Form Controls, select Checkbox.
- Select the cell where you want to place the checkbox, preferably next to an entry on your list.
- You can change the text on the checkbox by deleting the default checkbox text and typing a word or phrase or removing the text entirely so that only the tick box remains.
- If you want to move the checkbox, drag it to the position you want.
- To copy the exact checkbox on the succeeding cells, click on it. Then, hover your mouse to the lower right corner of the box and drag it downwards (or to any direction where you want to place the checkboxes).
Important tip: When formatting a checkbox on a cell, hold the Ctrl key and press left-click to modify or resize the shape of the checkbox instead of ticking it.
How to Link Cells in an Excel Checklist
1. If you want to tally the boxes ticked, link the checkbox to another cell.
2. Right-click a checkbox and select Format Control.
3. Go to the Control tab. Head over to the Cell Link option, type the Cell name you want to link.
You’ll notice that if you tick the checkbox, the linked cell will display TRUE.
Unchecking it will produce FALSE.
4. Repeat steps 1 and 2 to the other cells in the column that must be linked.
5. To get a summary of how many boxes were ticked, click a cell where you want the results displayed. Then, type =COUNTIF(C4:C10,TRUE). Replace C4:C10 with the beginning and ending cells of cell range.
6. If you want to hide the column where the TRUE/FALSE values are displayed, click on the column (example: column C). Next, press right-click and select Hide at the bottom of the popup menu.
Modify Your Excel Checklist Using Conditional Formatting
Conditional formatting of your spreadsheet can help your data stand out and make it visually appealing.
1. Select the cell you want to modify, then click the Home tab. Go to the right side of the toolbar and click Conditional Formatting.
2. Select New Rule. It will show several rule types which you can use to edit the selected cell. For now, choose Use a formula to determine which cells to format.
3. In the text box below, Edit the Rule Description type =$C4. Next, select Format and then select Fill. Go to Color and change the font color of the cell (example: Green). Select OK.
Note: Don’t forget to modify the cell name. What we used here (C4) is just an example.
4. Notice that when you tick the linked checkbox next to the cell, the text will turn green.
5. Repeat steps 1-4 for the rest of the entries.
Important Tip: Press Ctrl + Shift key and drag the cell to where you want to duplicate the checkbox to produce the same result of copy-pasting. This method will also keep the checkboxes aligned.
How to Make an Interactive Excel Checklist
If you want to track your progress on your recipe to-do checklist, integrate the checklist into a percent of tasks completion. Here’s how you do it:
- Using the example we currently have, click into any cell where you want to have the task progress to be displayed (Example: Cell E7)
- Type the following COUNTIF formula =COUNTIF($C$4:$C$10,TRUE). This formula will count the total numbers of entries whose checkboxes were ticked.
- To track the completion rate of all the entries, type this in the cell where you want it displayed (Example: Cell F4) =COUNTIF($C$4:$C$10,TRUE)/7*100. Note: don’t forget to replace “7” with the number of entries on the list.
What’s in Your Checklist?
Creating a checklist in Microsoft Excel can be fun and help you be excited about the activity or items you want to track. Whether you are making a grocery list or creating an inventory for your store, a checklist is one of the best ways to avoid missing any item. Next, create a dropdown list in Excel and make it easier to enter frequently used data in a spreadsheet.