In this guide, I’ll show you, step by step, how to make your Google Sheets experience way better by using different types of automations.
Let’s get started and make your spreadsheet do the hard work for you!
Simplifying Tasks with Macros
Macros are like little recorded actions that Google Sheets can repeat on command.
You show Sheets the steps you want it to take, and then it’s all about playback.
Let me show you how to do it.
Step 1: Open your Google Sheet
Here’s the scenario:
You’re tracking down employees’ information, such as their ID number, first and last name, the department where they belong, their yearly salary, and the date they joined the company.
Step 2: Navigate to “Extensions” in the menu
You want to make the sheet presentable, so you go to the “Extensions” tab and proceed with the “Macros” tab so that you can record how you want the sheet to look like every time you input new data.
Step 3: Select “Record Macros” to start recording your desired format
Once you’ve clicked that button, you’ll see in the middle part of your screen that it is recording the steps you are doing. You can opt to use absolute references or relative references.
Step 4: Save New Macro
You can press the Save button once you have fulfilled the formatting that you wish. You can name it for easier access, as well as put a number for shortcut purposes. It might require some users to give access to Macros to manage the sheet.
Step 5: Save and Run the Macro
You’ve now successfully encoded the desired format. Now, every time a new information is given, you can just go to the “Extensions” tab and then to the “Macros” tab where you can see your saved Macro. You can also do it by pressing Ctrl+Alt+Shift along with the number you’ve assigned to your Macro.
Easy, right? It’s like having a mini-robot that does the work for you.
Video Tutorial
In this video, I recorded a macro that would automatically format the data in a way that I wanted it to be presented. I made the 3-number ID into 6, had the date into a specific format, placed all input to the center, and had alternating colors as a background. Once I encode a new data, I just need to type in the shortcut or go to Macros tab and tada, it will appear what I wanted it to appear.
2. Advanced Automation with Scripts
It’s a bit more advanced but super powerful.
With scripting, you can create custom functions or actions tailor-made for your needs.
From automatic data entry to pulling specific information from other sheets or even different sources, scripts give you a whole new level of control.
Here’s a breakdown of the procedures involved:
Step 1: Open your Google Sheet
Here’s the scenario:
You’re managing a budget tracker for a fundraising event in Google Sheets. You want to automate the process of updating the total funds collected whenever a new donation is added.
Step 2: Navigate to “Extensions” in the menu
You’ve decided to enhance the functionality of your budget tracker by incorporating a script. To access the Script Editor, go to the “Extensions” menu.
Step 3: Select “Apps Script” to open the Script Editor
You’re now ready to create a custom script that will automatically update the total funds whenever a new donation entry is made in your budget tracker.
Step 4: Start Creating Custom Functions
In your budget tracker, you have columns for ‘Donor Name,’ ‘Donation Amount,’ and ‘Total Funds.’ You want to write a custom function that calculates the sum of all donation amounts and updates the ‘Total Funds’ cell.
Step 5: Save and Run Your Script
You’ve successfully written your custom function. Save the script, and run it to see the magic happen. Now, every time a new donation is added, the ‘Total Funds’ will automatically update.
3. Enhancing Data Accuracy with Data Validation Rules
Ever found yourself dealing with messy data entries or constantly fixing errors in your spreadsheet?
Data Validation Rules allow you to set specific criteria for the data entered into your cells, ensuring accuracy and consistency.
Imagine you have a column for the due dates of tasks, and you want to avoid any entries that fall on weekends. With Data Validation Rules, you can create a rule that restricts users from inputting weekend dates, saving you the hassle of manually double-checking each entry.
Here’s a quick guide on implementing Data Validation Rules:
Select the Range:
Highlight the cells or range where you want to apply the data validation.
Step 1: Go to “Data” in the Menu:
Navigate to the “Data” menu and choose “Data validation.”
Step 21: Set Criteria:
Specify the criteria for your data. In our example, you’d choose “Date” as the criteria and set the range to exclude weekends.
Step 3: Customize Error Messages:
Create custom error messages to guide users when their input doesn’t meet the validation criteria.
Step 4: Save and Apply:
Once you’ve configured the rules, save your settings, and voila! Your spreadsheet now enforces the specified data validation, reducing errors and enhancing data reliability.
Data Validation Rules not only maintain the integrity of your data but also contribute to a more organized and error-free spreadsheet.
Video Tutorial
Conclusion
Automating tasks in Google Sheets isn’t just a time-saver; it’s a game-changer for your productivity.
AI automations liberate you from the constraints of rigid task orders and the need to focus on one activity at a time.
After experiencing the transformative power of automation, you’ll reflect on how you navigated tasks before this technological leap.
So, grab that spreadsheet, and enjoy the efficient workflow that empowers you to accomplish more in less time.
