Your spreadsheets don’t have to be boring. When you use Google spreadsheets, you have access to a variety of tools that can help make your workflow much easier. There are tons of functions and formulas in this free spreadsheet tool that allow you to edit, search, organize, and analyze content.
While there are a plethora of ways to improve your skill level in Google spreadsheets, let’s take the time to review a small sampling below.
Utilize Workbooks
Life can get crazy when you have several different files blowing up your computer or online storage space. Why not combine similar files in the same spot? When you use Google spreadsheets, in addition to saving your work as a single spreadsheet, you can easily combine spreadsheets into one workbook for better file management.
This is useful when having a topic that requires several spreadsheets. Create a new sheet as needed by clicking the plus symbol in the lower-left corner. You can give each sheet in your workbook a specific name, identifying color, duplicate, delete, and move their placement around as needed.
Functions
Like Excel, Google spreadsheets utilize several functions to increase your production speed. Many are quickly available in the Edit menu. The functions you will most often use will vary by user. Let’s check out a couple of useful ones.
Add Multiple Rows
If you have a long list of items, you may need to insert multiple rows in select spots. Using this list of world holidays, we can insert four new rows in the middle using these simple steps:
Highlight the number of rows you wish to insert at the location you want to insert them. We need four new rows after Halloween.
Now, we can right-click to access the pop screen below. Click “Insert # rows”.
Presto! We now have four new rows inserted. Use this step to insert as many rows as you wish.
Paste Special
Are you tired of carrying over unwanted formatting when you copy something to another part of your document?
After highlighting the cells you want to paste, go to the Edit menu. Then click “Paste special”.
The “Paste special” menu will give you several options to leave out when you paste your selection in a new location. You can opt to only paste the cell text/value, formatting, or formula while excluding everything else.
Formulas
Why spend excessive time calculating complicated formulas in your head or on a calculator, when you could utilize the formula system of Google spreadsheets? There are tons of formulas to help with your business, home budgeting ,etc. Let’s find out how to use SUMIFS such as seen on spreadsheetpoint.com
SUMIF
This formula allows us to deliver the sum of cells based on the condition in a given range.
After reviewing a list of personal budget items in categories like Entertainment, Food, and Transportation, I may only want to see what has been spent on entertainment this month. We can do so by using the following formula:
=SUMIF(B1:B10, “entertainment”, C1:C10)
Add or Edit Themes
As your Google spreadsheet becomes more detailed, the temptation to update the look and feel will be very enticing. After doing enough spreadsheets, you will probably get tired of seeing basic white pages. Some built-in themes come with pivot tables, inserted charts, etc., to help provide a consistent feel.
You can adjust your theme settings by going to the Format menu and clicking on “Theme”. An initial theme screen will pop up with a display of various standard themes to choose from. However, when you click “Customize”, you can change the selected theme’s font, hyperlink color, chart colors, etc.
Change the Default Background Color or Pattern
As long as the colors and patterns you use do not conflict with the layout of your spreadsheet, changing the background color or pattern of your sheet is extremely easy.
Simply go to the Format menu and select “Alternating colors”. Use this option for selected cells or the whole document.
Learn To Use Macros
Let’s say you’ve mastered all of these tips and are looking to really sink your teeth into something a little more difficult. Using macros to automate your processes will not only make you a more advanced user but will also cut down the time you spend on building and editing spreadsheets vastly.
Macros are basically little automation code snippets that allow you to complete simple tasks without having to re-enter each function or formatting requirements each time.
A common spreadsheet you may use macros in is the weekly budget. You could set a sheet that has macros in place for you to use without having to reenter the formatting or functions for each section of your business’s budget.
Macros work by using script functions which may be a little out of your league if you’re pretty fresh to using spreadsheets. However, you can also set them up by telling Google Sheets to record a set of tasks when you complete them for the first time.
Once it’s saved as a macro, you simply have to click on it to apply all of its functions and formatting to the desired cell, group of cells, or entire sheet.
All you have to do to get started is:
- Navigate to Tools > Macro > Record macro
- Then as you see “recording new macro” on your sheet, record all the actions you want to take
- Click save
Then the next time you perform the same tasks you can just use the macro instead of individual steps. You can also import macros to another sheet if you’d like to save time across multiple spreadsheets.
Additionally, you can check out how to use Google Sheets sum by category. This is very helpful especially if you only want to filter data by category and get its total figures.
Conclusion
Google Sheets is one of the best tools out there for organizing, processing, and analyzing information. To use this tool effectively, you need to be familiar with the available functions, search for formulas to implement, and familiarize yourself with the formatting.
Never worry about being overloaded with plain boring spreadsheets ever again as you continue to familiarize yourself with the power of this free software.
