Как сделать чек лист в гугл таблицах

Курс Таблицы Google → Разбор примера. Чеклисты и эмоции в Таблицах Google

На Facebook хорошо разошлась таблица со связкой Чек-лист + emoji.
Было отправлено много ссылок на исходник, но также были вопросы как это работает.

В этой таблице не все используемые фишки видны невооруженным глазом, поэтому сейчас мы разберем каждый из шагов.

Фиксирование колонок и строк

Видим широкие границы. В случае, если эта таблица выйдет по контенту за рамки экрана вправо или вниз — первая строка и первая колонка, всё рано будут на видимом экране. Есть видео как зафиксировать колонки и строки.

Если коротко:

Добавление чекбокса в ячейку

Основные элементы управления в примере — ячейки с чекбоком(галочкой). Подробно разбираем эту опцию в видео Как сделать ячейки: календарь, список, чекбокс.

Если коротко:

Выбираем ячейку — Клик правой кнопкой — Валидация данных.

В выпадающем списке выбираем тип поля Checkbox.

Задать чекбоксу пользовательские значения

По-умолчанию, чекбокс будет иметь значения TRUE / FALSE.
Но есть возможность, переопределить их.

Забегая вперед, для вывода emoji будем использовать функцию IF, где удобнее будет считать сумму «выполненных шагов».

Выбираем ячейку — Клик правой кнопкой — Валидация данных.

В попапе ставим галочку «Произвольные значения» и задаем их.

Выводим emoji в зависимости от прогресса

Разбираем на примере ячейки E2.

В ячейке формула:

Используется 2 функции:

IF(условие, если да, если нет)
То есть, в первом параметре мы передаем условие, например B2 = 1.
Во втором — результат, если условие будет верным: B2 будет равняться единице.
В третьем — результат, если условие будет неверным: B2 не будет равняться единице (больше, меньше, текста — неважно, главное что не равно 1).

SUM(ячейки/значения/диапазон)
В функцию можно вносить ссылки на ячейки, просто значения и диапазоны, разделяя запятыми, если хотим просуммировать несколько элементов.
Можно чередовать, например суммировать значения ячеек, с суммой значений диапазона. Тут ограничений нет.

Разбираем наш случай:

Тут основная мысль — IF можно вкладывать внутрь IF.
Если условие сработало или не сработало — внутри можно делать еще проверки через IF(условие, если да, если нет).

Больше эмоций!

Точнее, больше размер эмоций. В ячейках с emoji размер шрифта больше, чем в других ячейках. ��

Выделяем строку фоном, если процесс завершен

В нашем примере, если все 3 галочки проставлены — то строка заливается зеленым фоном.

Тут срабатывает Условное форматирование.

Выделяем все ячейки документа.

Правой кнопкой — Условное форматирование.

Задано такое условие.

То есть, выбран пункт Форматировать ячейку, если Пользовательская формула =SUM($B1:$D1)=3.
Видим проверку на сумму значений диапазона с чекбоксами, если сумма равна 3, то форматирование включается.

Важный момент, чтобы выделилась вся строка, нужно использовать $ перед ссылкой на ячейку.
В нашем случае — диапазон, поэтому добавляем $ перед ссылками, которые обозначают размер диапазона.

Надеюсь этот разбор был полезен для вас!
Поделитесь с коллегами и подписывайтесь на YouTube-канал. ��

How to Create a Checklist in Google Docs

Checklists are a very handy way of keeping track of items, tasks, or steps that require completion. They provide a simple visual reminder of whether or not everything that needed to be done has been done. Combine this with the convenience of Google Docs that can be accessed at any time (as long as you have internet access), and you’ve got a great management tool.

In this article, we’ll show you how to create a checklist in Google Docs, along with a few insights into making a functional checklist.

How to Make an Interactive Checklist in Google Docs

Creating a checklist using Google Docs is a very simple process. As long as you already have an idea of the things you want to include in the list, then the steps are quite easy. If you want to make an interactive checklist using Google Docs, then follow these steps:

1. Open the Google Docs app. Click on + on the upper tab to make a new document.
   
2. On the top menu click on Format.
   
3. Hover over Bullets & Numbering on the drop-down menu.
   
4. Hover over Bulleted List.
   
5. Several choices will appear, click on the top rightmost option. This is the checkbox bullets feature.
   
6. You will notice that your list now has a clear checkbox in front. You can now type in the first item on your list.
   
7. Pressing enter will automatically create a new blank checkbox. Proceed to populate your checklist.
   
8. Save the document once you’re done.

You have now created an interactive checklist. You can print it out and use it as a normal checklist or you can open it digitally and tick these boxes by doing the following:

1. Highlight the blank checkbox on the item you wish to tick.
   
2. Right-click on your mouse if you’re using a PC. On a Mac, use ctrl + click.
   
3. A popup window will appear. Click on the checkmark. This will change the checkbox to a checkmark.
   
4. If you wish to check more than one box at a time, you can highlight multiple checkboxes at once.
   
5. Pressing ctrl + z will undo the change.

Limitations of Google Docs Mobile

There is a mobile version of Google Docs available for both Android and iOS. Although this version does have many features, it doesn’t have many of the formatting options of the desktop version. There is a way to get around this though, as Google Docs can be accessed via a web browser. Just use your mobile web browser and open Google Docs from there. This will be the better option, especially when using Android tablets or iPads.

Using Google Sheets

Another app to keep in mind when creating checklists is Google Sheets. It has a built-in option to make actual checkboxes that can be toggled on and off with a simple click. Using Google Sheets to create a checklist can be done by following these steps:

1. Open the Google Sheets app.
2. Highlight cells that you want to add a checkbox too. You can do this by either clicking and dragging your mouse or by selecting individual cells while holding down the ctrl key.
   
3. Click on Insert in the top menu,
   
4. Click on Checkbox on the dropdown menu.
   
5. A checkbox should now appear on the cells that you’ve highlighted.
   
6. You can toggle the checkmark on or off by clicking on the checkbox.
   
7. Complete your list by typing in the items to the right of each checkbox.
   

Google Sheets for Mobile

Unlike Google Docs, the checkbox functionality can still be used with the mobile version of Google Sheets. This can be done by following these instructions:

1. Open Google Sheets for Mobile.
   
2. On the lower right of the screen, click on the + icon.
   
3. Tap on New spreadsheet.
   
4. As with the desktop version, highlight the cells that you want to add checkboxes to.
   
5. On the upper right corner of the screen, tap on the three dots icon.
   
6. Tap on Data Validation on the popup menu.
   
7. Tap on the down arrow beside Criteria.
   
8. Tap on Checkbox.
   
9. On the upper right, tap on Save.
   
10. Your selected cells should now have interactive checkboxes.
    

What to Keep in Mind When Creating a Checklist?

When creating a checklist, there are a few things that you should keep in mind to make sure they’re effective. Below are some of the things you should take note of when making your checklist:

1. If there are chronological steps to follow, make sure that they are listed in order. Also, make sure to mention that they should be followed in that particular order.
2. Ensure that everything that should be included is included, otherwise, the checklist is meaningless.
3. As opposed to the previous tip, make sure that everything included in the list needs to be there.
4. Check for redundancies. This is especially important for checklists. Having an item listed twice will create confusion when one of them is checked and the other isn’t.
5. If a list doesn’t have a particular order, try to group things logically. This is especially useful in shopping lists. Listing items like bread and cheese separately when they’re usually in the same areas in a grocery store will just result in needless backtracking.

Additional FAQs

How Do You Add Templates to Google Docs?

If you wish to add a template to the Google Docs Template Gallery, you will need to have a G Suite account. If you have a personal Google Account, you can still technically create one for free. Just create your document on Google Docs as normal, then save it under the name Template. Afterward, if you wish to create a new document using the same format, just open the Template document then edit it as necessary. The same template method can be used for Google Sheets, Google Slides, and Google Forms.

How Do You Set Up a New Google Doc?

Whenever you start Google Docs, you’re given the option to create a new blank document by clicking on the + icon on the upper tab. You can also use a pre-formatted document by clicking on Template Gallery on the upper right side of the upper tab. If you don’t see this menu, then you may have templates hidden. You can bring them back by doing the following:

• On the Google Docs home menu, click on the Main Menu icon on the upper left corner. This is the three lines icon.

• Click on Settings from the dropdown menu.

• Under Templates on the popup window, toggle ‘Display recent templates on home screens’ back on.

How Can I Add a List in Google Docs?

Adding another item to your checklist is as simple as clicking on the last item of the list, then pressing the enter key. If you’re using checkboxes, Google Docs should create a blank box automatically for you. You can then fill in the list as usual. If you wish to insert a new item in the middle of the list, just click on the end of the item just before the step you wish to insert it in. Clicking enter will create a new checkbox as well.

How Do You Add Checkboxes in Google Docs?

If you’re already using a checkbox then simply pressing enter every time you finish one item of your list will create a new checkbox automatically. Otherwise, just follow the steps provided above to format a new checklist.

If you’ve already created a list and only want to add checkboxes, then highlight your entire list. Click on Format on the top menu, hover over Bullets & numbering, and then over Bulleted List. If you click on the checkbox format on the upper right the numbers on the list will become checkboxes. Do note that this will delete all the numbering on your list. You may have to type the numbers back in one by one if you need them.

How Do You Create a Checklist?

Checklists are only ever needed if you have to keep track of important steps or items that you can’t remember on your own. If the number of items is small, or the steps are entirely optional, using a checklist isn’t necessary.

Thus, when creating a checklist, it’s already a given that there are steps that must be included in the list itself. Refer to the tips and tricks on creating a checklist as given above to know the important thing to keep in mind when creating one.

Do I Print Out My Checklist or Keep It Digital?

This depends on what is more convenient for you. If you can keep the checklist on a mobile device, then do so. Most people never leave the house without a phone nowadays, anyway. If using pen and paper would seem more practical, then there’s no reason not to print one out. Doing both at the same time isn’t recommended though, as two identical lists will just cause confusion and defeat the point of the checklist.

A Handy Management Tool

Knowing how to create a checklist in Google Docs adds to your arsenal of handy management tools. The convenience of Google Docs does lend a whole lot of functionality to the traditional list-making process. It never hurts to have as many useful means at your disposal when dealing with important tasks.

Do you know of other ways on how to create a checklist in Google Docs? Share your thoughts in the comments section below.

Как сделать чек лист в гугл таблицах

Чекбоксы (или флажки) в гугл таблицах это специальный элемент графического интерфейса, позволяющий возвращать 2 состояния — включено или выключено.

Вставка чекбокса

Есть 2 способа вставить флажок

Через «Вставку»

Через «Данные»

Что возвращает чекбокс

Если сослаться на ячейку с флажком, то можно увидеть следующее:

При выключенном флажке

При включенном флажке

При этом есть один нюанс такого «возвращения»: если писать формулу, завязанную на чекбокс, то в формуле указывается true как boolean значение, а не «TRUE» как текст. В скрипте он так же возвращается как boolean.

Однако есть и другой способ задать пользовательские значения, которые возвращает флажок. Через проверку данных можно указать нужные :

и тогда при нажатии на флажок будет возвращаться «Кошка», при отжатии — «Собака»

Нажатие на флажок

Есть два способа нажать на флажок:

• Клик мышкой
• Пробел

Групповое нажатие на флажки

К примеру есть список фамилий сдающих что-либо:

И они все сдали. Чтобы не кликать по каждому чекбоксу, можно сделать следующее:

• Выделяю все флажки

• Нажимаю «пробел»

Условное форматирование и разноцветные чекбоксы

Чекбоксы можно так же использовать при подсветке и условном форматировании, через свою формулу и привязку к ячейке, зафиксированной по столбцу:

а если нажать, то:

Google apps script + checkbox

Ну и конечно же, как без нашего любимого гугл скрипта, благо дело чекбоксы им совершенно спокойно обрабатываются.

Скрипт изменяет состояние флажков в диапазоне на «отмечено». Игнорирует ячейки в диапазоне, которые не содержат настроенного значения.

Логично что если есть check, есть и uncheck

Так же состояние чекбокса можно получить в скрипте через getValue()

и, как уже говорил выше, консоль вернет следующее:

из Checkbox в Radiobutton

Допустим у меня есть некий лист с вопросами в виде тестов:

И нужно выбрать только один правильный ответ. Если нажимать в обычном режиме, то вариантов выбора может быть несколько. Пользуясь возможностью uncheck это легко исправить:

Checklist Template In Google Sheets

In this tutorial, we’ll create a checklist template in Google Sheets.

We’ll use checkboxes, conditional formatting and a sparkline to build a checklist template like this:

Checklist Template Use Case

There are many situations when a checklist comes in handy.

From simple to-do lists to project planners, from teaching lessons to tracking physical goods.

And although this simple spreadsheet checklist isn’t suitable for large, complex projects, or projects that require more robust data trails, it’s a quick and easy way to add some useful flair to your spreadsheet projects.

When I teach live workshops, I often include a front sheet in my Google Sheet that I use as a checklist for the exercise steps.

It ensures I don’t forget anything and gives the audience a visual clue as to where we’re up to in the workshop. And I get almost as many questions about how I built these checklists as for the topic of the actual workshops.

How To Create A Google Sheets Checklist Template

Checklist Template

Feel free to download the checklist template and make your own copy:

Feel free to copy this sheet: File > Make a copy

I’ve set my file sharings to allow anyone with the link to view this file. You may not be able to open this file because it’s from an outside organization, and my Google Workspace domain is not whitelisted at your organization. You may be able to ask your Google Workspace administrator about this.

In the meantime, feel free to open it in an incognito window and you should be able to view it.

1. Sheet Set up

We start with the checklist title on row 1, center aligned.

Then leave a blank row.

On row 3, we put the word “Progress” in column 1. Leave the other column blank for now.

On row 5, put the headers for the checklist table: Status and Step

On rows 6 onwards, we put a checkbox in column 1 and the corresponding step in column 2 of that row.

To add a checkbox, go to: Insert > Checkbox

2. Add Conditional Formatting

Next, we’ll apply conditional formatting across an entire row to turn the row red when it’s marked as done.

Highlight all the rows with your checkboxes and steps, e.g. A6 to C15 in this example.

Select Conditional Formatting and add this Custom Formula Is rule:

Set the formatting rules to light red background, dark red text, and strikethrough.

Now, whenever you check the checkboxes, the entire row will be formatted red to show it’s complete (see the GIF image at the start of this post).

We also set the table to have alternating colors, from the menu: Format > Alternating colors

3. Use A Sparkline To Create A Status Bar

Here’s the sparkline function in cell B3 that creates that dynamic status bar:

Let’s break it down to see what’s going on.

Checkbox Count

counts how many of the checkboxes in column A have been checked (i.e. have a TRUE value).

The output of this is a single number, between 0 and 10 in this example.

We pass that value into the SPARKLINE function.

Sparkline Bar Chart

Then we set the sparkline to be a bar chat, with the first option:

Max Value

Next, we need to specify a maximum value for the bar chart, so that it can compare the count of checked checkboxes (e.g. 4) against the maximum possible number (10 in this example) to get the percentage completion.

We could simply type in the max value of 10 as an option, but it’s better practice to set it with a formula so that it will update automatically if your data changes.

To do this we count the number of “steps” in the column next to the checkboxes:

Custom Color

Finally, we set a custom color for the sparkline with the final custom option:

Notes

In some European countries, sparkline formulas have a slightly different syntax and use “\” instead of “;” (read more here about syntax differences based on Google Sheets location).

Pro tip: to select or un-select multiple checkboxes at once, highlight them all and press the space bar!

4. Add A Progress Percentage

The final formula goes in cell C3 next to the sparkline to show the actual percentage complete value:

This counts the checkboxes in column 1 and divides them by the total count of steps in column 2. Format it as a “%”.

17 thoughts on “Checklist Template In Google Sheets”

Thank you for sharing this tip. I have always wondered about how to create a progress bar for projects and for letting people know how much was left to complete on a project or report. This will come in very handy moving forward. I also appreciate the way you have explained it very clearly. It is easy to understand and follow along.

Nice and simple template! Good start for a more complex version where it would be great to add:
– Transaction log sheet that shows who and when an item was checked or unchecked (for traceability purposes working with a team). This sheet should be “read only”
– Add a “category” column and show progress per “category”

A little app script magic can make this happen!

Thank you. This is very good simple template and just trying to use it to increase productivity. It takes time to learned how to edit the script but I enjoy in trial and error

Wonderful. I was looking for a way to help my students document their progress in their independent learning process, so that they can know where they stand, but I can also track them and see if they need help. this is perfect. Thanks a lot for posting

thanks for sharing this Ben! much appreciated!

You’re welcome! Hope you find it useful. Cheers!

Thank you so much for sharing this, Ben! This is amazing.
I made a copy to edit and create my own checklist, and then added more rows to the list.

Now the progress bar does not fill up even I kept COUNTIF(A6:A, TRUE) and “max”,COUNTA(B6:B) in the formula. Column C shows =COUNTIF(A6:A,TRUE)/COUNTA(B6:B) so it should be 100% when all the items are checked, but it isn’t. I was wondering what I did wrong?

I just discovered this template. Love it. But wondering the same thing. Did you ever figure this out?

Thank you very much for this clear and structured explanation! It helped me a lot!

Thanks for the ready document!

Wow this is SO helpful for me and exactly what I’ve been looking for. Thank you! I’m running into a problem though: when I have 5 items and check one, it’s displaying 17% complete instead of 20%. Not sure where that extra 3% is missing from. Have you seen this before? Thanks!

Hi Emma, just change =COUNTIF(A6:A,TRUE)/COUNTA(B6:B)
to =COUNTIF(A6:A,TRUE)/COUNTA(A6:A) at C3

Credits to Max Makhrov
and many thanks to Ben

…and for B3, do the same modification
change B6:B to A6:A

hi there! So glad I found this! I’m a wedding photographer and officiant and I need to remember to bring the same things each event. I’m hoping to use it multiple times a week. Do you think that could work? Do I just keep a clean version as a template and copy it over and over again each time I want to use it?/

I would make a clean version an actual template in Google Sheets. That way you don’t have to copy it every time – you just open the template gallery and pick your template and it will make a new one for you.

Great Ben,
So Glad.
This helps a lot and gives a new vision.
Thanks a lot.

oh man this is SO cool! So much more informative than I expected when I was looking for a template. I followed steps and made my own. This is great and I Love the spacebar check boxes tip! Thank you