Whether you’re new to this program or a long-time user, there are some hacks that you can use with Google Sheets to help propel your business forward. Google Sheets is an excellent tool to record, store, and organize all the things related to your business. Let’s not waste anymore time and dive into some shortcuts/ functions that will help keep you and your business organized!
Google Sheets Shortcuts!
While you are working on a spreadsheet, you can save time by using shortcut keys. If you are using google sheets, be sure to enable the keyboard shortcuts feature. You can access this option in the Help menu, choose keyboard shortcuts, and there will be a toggle on the bottom of the pop up window. Below are just a few helpful shortcuts available:
|Select Whole Row||Shift + Spacebar||Shift + Spacebar|
|Select Whole Column||Ctrl + Spacebar||Ctrl + Spacebar|
|Paste Values Only||Control + Shift + V||Command + Shift + V|
|Paste Format Only||Control + ALT + V||Command + ALT + V|
|Find Values||Ctrl + F||Command + F|
|Insert Current Time||Ctrl + Shift + ;||Command + Shift + ;|
|Insert Current Date||Ctrl + ;||Command + ;|
|Insert note||Shift + F2||Shift + F2|
|Format as Percentage||Ctrl + Shift + 5||Ctrl + Shift + 5|
|Repeat Last Action||F4||Command + y|
Google Sheets Hacks for Formatting
Freeze panes for easy viewing
If you have a long list of data, it is very helpful to keep the column titles visible at the top of your page as you scroll down.
- View tab → Freeze→ choose how many rows
Conditional formatting to spot errors
This is a good trick if you want to highlight data that is missing, or perhaps highlight cells with a certain status. For example, if you want to make sure all required fields are filled out, you can have the cell automatically highlighted in yellow if the cell is empty. Once it is filled out, the highlighting format will disappear. This allows you to easily spot errors in your data.
Another example would be if you were tracking tasks, you may include a column to indicate the status of the task (i.e. in-progress, complete, on hold). You could then add formatting rules in order to colour code each status.
- Select data section → Format → Conditional Formatting
Sort and filter to organize sets of data
If you are keeping track of all money in and out for your business, you can use a simple sort and filter feature to isolate income from expenses. Another example is if you are keeping track of sales for each of your services or products, you can sort and filter to have a deeper look into each item.
- Select data section → Data tab → Filter
Data validation to create organized lists
You may have a column in your spreadsheet that you’d like to have a drop down menu to choose from. This feature allows you to create a list (i.e. expense categories) and choose from that list during data entry. Dedicate your first sheet for your categories. For example, list all your expense categories in one table. Within your data entry sheet, when you want to have a drop-down list:
- Select column → Data tab→ Data Validation → Cell range: where the drop down list will appear → Criteria: list from range: choose cell range of your list
Google Sheets Functions
- =UNIQUE([select range])
This function will return a list of unique values from a range of data. For example, product names, or expenses.
- =SUM([select range])
The sum function will automatically total your selects range
- =DAYS(SELECT CELL, SELECT CELL)
This function calculates the number of days between two dates.
- =NETWORKDAYS(SELECT CELL, SELECT CELL,[numberofholidays])
This function will calculate the number of weekdays between two dates, and has the option to exclude holidays.
Keeping your business organized can seem like a daunting and mundane task. The reality is, being well-organized is critical to business success. Use these google sheets hacks to create systems that work for your business!
Don’t hesitate to reach out to us if you need a hand setting up spreadsheet systems that will work for your business!