Advanced Tips and Tricks for Productivity COMPILATION
we have this to-do list we want to gamify this a little bit grab a random task and put it in C2 use Rand to get a random number times by the number of things here which is 10 and round this we're not going to get the task we're just going to get a bare number use index and reference C5 colon C15 row is going to be this round Rand time 10 but this 10 might change maybe we insert more items to do count C C15 and now we get inbox zero but once we click that it's going to come back so what we need to do is one more step we need to filter C5 to C15 where B5 colon B15 equals false because boxes are a visual representation of true or false take our Celebrate Your Completed Checklists How to Create a Random Motivational Quote Checklist Select Random From List in Google Sheets
filter and count the remaining ones in the filter now we should not get any that are repeated and as we check off we'll get less and less options the cool thing here is that every time we check off it gives us another random task to do create a road trip packing list as we check things off our car gets closer and closer to our destination I'm show you how to create this we're going to use concatenate repeat and count if let's start use catenate first things first is we want to destination do control command spacebar we'll put a mountain in quotes R PT count if is going to be B5 call B for the entire checkbox range we're going to count all of the truth what are we going to repeat the backwards Arrow blue car in quotes we'll Celebrate Your Completed Checklists
repeat exactly the same arrow and how many times we'll do count if B and count the falses I made an error here we need to do false first because that's what we want to minimize and we want to increase the true here our car will move from right to left and that's how we we're creating a habit tracker and we want to create that Viral progress bar up here if we are doing daily habits tracking them with some check boxes we can add a progress bar the trick here is to use spark line formula we're going to do equals Spark line spark line needs some data the data is going to be a count if of B5 Co make sure our count if is actually counting all the TRS here now the options this is where it gets tricky we going to have to do curly brackets here we want to first name our chart type our chart type is going to be a column chart because we want a vertical Hide Completed Bars - Make Progress Bars with Sparkline: Part 2 How To Make That Viral Growing Plant Progress Chart Power Progress Bar
column semicolon and then we set a y Max and our y Max is going to be in this case 31 because we have 31 days we also need a y Min our y Min is going to be zero now we have a bar chart if we want to change the color green let's say or yellow or purple and now we can copy paste this all the way across don't do this equals cuz it's not going to work you can't put an equal sign you can put uh quotation equals but we don't want to do this manually we want to do this automatically so what we can do is equals if if this is equal to this then if it's true we'll put an equal sign if it's false if this is greater than D3 and if that value is true we'll put a greater sign if it's false we'll do another if and we'll do B3 is less than Add Cool Responsive Graphs to Your Stock Tracking Sheets EVERYTHING you need to know about Conditional Formatting
D3 and if that's true we'll put a less than we'll finish up all these quotes and now we do this automatically for all of these you don't have to copy and paste in order to filter your list in order to do this we can just enter a formula equals filter select your range you want to filter the condition is going to be the header here equals and select the range where your header is we hit enter but that's not all if we copy paste we get an NA what we need to do is go into our filter and lock with a dollar sign not the B15 but B and B here lock those now copy and paste now we have dynamically filtered data how would you count to 1 Make Your Headers Useful Find New Additions to a Main List in Google Sheets Highlight Duplicates of Two Columns
million in Google Sheets might start like 1 two three drag it down and make a million rows you can't make a million rows you're going to have 26 million cells that's pass the limit we need to delete the columns drag this down to a million that's going to take a while we could do equals row that gives us the row number all we need to do is Pace this down to a million let's see if we can do it in one formula sequence how many rows let's do one that's a million start one step one what we're going to get is an error sequence parameter we need one column please insert more rows aut automatically it has 50,000 rows we add more let's see if we can add another 100,000 we need now 9500 more rows 95 there we go now it's calculating there How to Insert Multiple Rows in Google Sheets (Easy to Hard) How To Add Columns in Google Sheets How to Insert Multiple Rows in Google Sheets (Easy to Hard) VIBE CODE GOOGLE SHEETS
it is counting to a million in Google Sheets randomize a to-do list every time we check off a to-do item a new one shows up that's a random one that hasn't been checked off yet how do we do this I'll show you how use index inside of index we're going to do filter filter C5 to C15 only showing the falses meaning the unchecked boxes B5 B15 equals false for the row of the index X round Rand we get a random number times we need the number of items in the list let's count all filter C5 colon C15 filter by B5 colon B15 equals false and now we have we created a checklist that can change per every single weekday let me show you how to do this first off we are going to Celebrate Your Completed Checklists How to Create a Random Motivational Quote Checklist One Million Checkboxes Select Random From List in Google Sheets
delete our entire checklist over here created a checklist for each day and we need to insert it into our checklist here we have a drop- down menu with each day of the week and we want to match the day of the week with the column here so the first thing we're going to do is equal match search key is going to be C2 B2 we want to use a absolute reference we going to use E4 colon I4 and search type zero so it'll look along E2 I2 and zero search type so it'll find an exact match in the case of Monday it'll be the First Column then we want to take the index of E4 colon e 14 and we need to find the first row because we're in the first row here the output of the match is going to be the column we're looking at but this row we want to actually move Automatically Uncheck A Daily Checklist The Simplest Bestest Checklist in Google Sheets Simple To Do List Template with built in Calendar
as it goes down from 4 to 14 so we're going to do row minus three this gets us the exact Row for the first item don't need E4 we need E4 to i14 and we're going to lock this as we copy paste down we don't want this to change we also don't want the E to change so we'll put some dollar signs there and now we can autofill and each one of these is exactly the same from Monday and if we change to Tuesday it's Chang let's make a self-grading test we have our questions and our answers the actual answers over here in column e these could be hidden with the same color for the text in the background or you can put them on another sheet but once we put an answer here like eight we want this to say thumbs up good job and if they did something else we want it to Study Better With Google Sheets Run 100 ChatGPTs at One Time Visicalc 2023 Design Walkthrough EZ Grader in Google Sheets
say something else we're going do the IF function our logical expression is going to be C3 equal 3 and this E3 could be anywhere where the actual answer is now if it's true meaning those are correct those are the same things we're going to put a little thumbs up if it is not true if it's false maybe we put a little hand pray for us and it's like oh no we did not get that correct but if we did get it correct oh yeah we get a thumbs up but what happens if it's blank we don't want this saying false so we're going to wrap this with if is blank you see3 again and do two comma there and now if it's blank there's nothing if we put an answer here we oh pray for us we need to get a better answer if we get the correct answer yay thumbs up we got the correct answer we can copy that all the way down and now we have self-grading 5 Ways to use IF() Formula to Create Magic in Your Sheets 5 Magical Ways To Use the IF() Formula in Google Sheets IF All Above Are Filled Conditional Formatting
questions every checklist you've ever seen has looked like this with a header at the top and checklist under it let's put that to the side I'll show you how to do that in a nice way insert a column to the left all these check boox delete them and then merge all of these cells right Monday that doesn't fit right go to text rotation rotate this up change size delete this row do Tuesday copy again wind day is a really fun way to differentiate your checklist if you want to get follower Counts from Tik Tok URLs I'm going to show you how to do it really easily you might see someone else do something like this try to copy paste that into here this is so much easier let's delete that go to our Tik Tok URL right click inspect over on the right side here right click copy full X path back to our sheet equals import XML for Ultimate Checklist in Google Sheets The Simplest Bestest Checklist in Google Sheets Celebrate Your Completed Checklists One Million Checkboxes
URL we're going to use C3 which is the URL we already have the X path we're going to put in quotes what we just copied and now it'll be loading and we'll get the exact count copy paste down we'll get the follower account for every single account we have here with the URL so you have a to-do list here and you want to add a date at which you did that to-do item just like this check and have a date show up here I'll show you how to do that we're going to go up to extensions appscript first thing we're going to do is use the edit function we're going to use an vent here to see event will be anytime there's an edit going to do variable range is the E want to get that row so range . column of the edit event is going to be range . getet column we also need to know what sheet we're on so we're going to use sheet equals range . source. getet active sheet we want to another sheet name Magical Checklists That Archive Done Items How To Create a Stop Watch in Google Sheets Add Click Tracking To Your Google Sheets | Bitly in a Google Sheet URL Link to Last Row
sheet. getet name now we want to make sure that we only create the Tim stamp whenever something is checked off in the checkbox column I'm going to create this if here make sure the sheet name is equal to timestamp you might have a different name in this maybe to-do list or sheet one we also want an row to be greater than one and we want the column to be equal to the second column once that happens we want to get the the variable active sheet want to make sure there a time sheet get sheet by name going to grab that time stamp sheet make sure that we have this time sheet. G range so we have the correct Row from up here we are going to do we're going to edit the fourth column and we're only going to edit one cell take this time range and we're going to set value new date command s this we're editing the B column adding the check box here make Timestamp When Done (With Code) How To Create a Stop Watch in Google Sheets Timestamp When Done on Certain Sheet Automatically Timestamp New Tasks When Entered
sure our event is actually here and now we can check off the done task messed up a little bit here this range is supposed to be Event Source get active sheet going to save the project and now when we select the done task our date will show up here in the fourth column the D column what if we wanted a checkbox here that turned this into a dark mode all we need is conditional formatting for this so we just need to enter a checkbox we're going to select the entire entire sheet and then we're going to go up to format conditional formatting over in our conditional formatting we're going to apply to the entire range we're going to change our background to Black our text to White and our formatting rules we're going to change this to a custom formula equals equals true and then we're going to change the A1 to dollar Add a Checkbox to Turn on Dark Mode The Simplest Bestest Checklist in Google Sheets Brighten Up Your Checklist Automatically Conditional Formatting With Checkboxes in Google Sheets
sign a dollar sign one click done and now when we click our checkbox it changes to a dark mode or a dark background with light text create a drop- down menu with star ratings from one to five we want to convert this into a number can we find an average can we report this you might enter by looking at it counting three we cannot use count or if there's no counting of stars here there's a much easier way to do this but we have to do one thing beforehand to count the Stars we set up a v lookup we're going to call it vlookup here three stars five stars on the second column we're going to put the number we can put this vlookup somewhere else maybe on another tab V the search key is going to be this C3 what's our range wherever this V look up is wherever you put it our index is going to be because we want to look up the second column is it sorted I always put false and now we Ken Asks: Can We Add Star Ratings? Ken Asks Can We Add Star Ratings in Google Sheets? Counting Stars QUICKLY
have our rating what we could do is if we put a dollar sign in front of the B and the 14 in front of the C and the eight we could click and drag this or copy and paste it all the way down if we change our rating here to four stars rating here is four now we can get an average of all of these this is pretty cool way to count how many stars we have Ken Asks: Can We Add Star Ratings? Ken Asks Can We Add Star Ratings in Google Sheets? Counting Stars QUICKLY