For many of us, Microsoft Excel has been a go-to tool since childhood, making it a natural choice for any project involving numbers and formulas. While Microsoft’s online version of Excel has gained popularity, many modern estimators prefer to use Google Sheets. Besides being free, Google Sheets is designed with the cloud in mind, providing a slightly more user-friendly experience. Although it may lack some advanced features for managing millions of cells, for basic to intermediate users, it is a solid and free solution.
Whether you have always been a Google Sheets enthusiast or recently made the switch, learning a few formatting tips can significantly boost your efficiency.
1. Conditional Formatting in Google Sheets
Conditional formatting might sound like a complex concept, but it has practical use cases, such as:
- Changing the color of a cell based on a specific condition (e.g., turning a cell green to indicate task completion when the response is “YES”).
- Notifying you if you have entered the same value twice (catching duplicate data entry errors).
- Alerting you if a cell is empty (highlighting cells that should be filled).
To set up conditional formatting in Google Sheets, follow these steps:
- Select your cells: Highlight the cells you want to include in the rule. In this example, we will use all cells in the “F” column of our estimate sheet.
- Go to “Format” and click on “Conditional formatting” in the top bar.
- Choose your trigger: Select the “if this, then that” trigger that will change the cell’s formatting. In our case, we want the cell to turn red if it is empty (to catch any omissions in the estimate). In the “Formatting rules,” select “Format cells if empty” as the trigger.
- Choose the formatting style: Select the formatting style you want. You can choose from pre-defined styles (we use a red background as an alert message) or create your own custom format.
- Click “Done”: Once you have set up the formatting, click “Done,” and you will automatically see the empty cells in the estimate sheet turn red. This helps identify where information was omitted and allows you to revisit the estimate and fill in the missing information or reintegrate it using your takeoff tool to submit a revised estimate.
Once you learn how to use conditional formatting, applying it to other scenarios is just as easy. Modify the trigger and formatting options, and you can automate various checks and formatting in your sheet without manual work.
2. Formatting Shortcuts in Google Sheets
At Crawlan.com, we are big fans of keyboard shortcuts. We even have dedicated pages in our company wiki to help our team learn shortcuts that save time in the programs we use. Google Sheets is no exception. Shortcuts help you work faster and navigate without relying heavily on your mouse. Here are some commonly used shortcuts in Google Sheets:
- Select a column: Ctrl + Space (Mac & PC)
- Select a row: Shift + Space (Mac) or Shift + Space (PC)
- Insert a hyperlink: Command + K (Mac) or Ctrl + K (PC)
- Format as decimal: Ctrl + Shift + 1 (Mac & PC)
- Go to the beginning of the line: Fn + Left arrow (Mac) or Home (PC)
- Go to the end of the line: Fn + Right arrow (Mac) or End (PC)
If you have already memorized these basic shortcuts, there are many more to learn. Check out this guide for a complete list.
3. Copy and Paste Formulas
There are many reasons why you might want to copy and paste formulas from one cell to another. It saves you from formatting each cell individually and ensures consistency. For example, if one cell contains a decimal number with two decimal places, but the rest only have one decimal place, it might not look consistent or easily readable. Here’s how to improve formatting:
- Copy using the format painter tool: Begin by selecting the cell containing the correct formula. Then, select “Format” and click on the paint roller icon labeled “Apply format.”
- Select the cell where you want to paste the formula: Now, click on the cell where you want to paste the formula. The cell will automatically adopt the same formatting as the previous one. You can also use the format painter tool on multiple cells at once, and it will detect outliers and apply the same format accordingly.
4. Text Formatting
Sometimes, your Google Sheets spreadsheet may require some cleaning up to be client-ready. If one of the issues is inconsistent text formatting (e.g., some in all caps, some in lowercase, or what Sheets calls “Proper Case” with the first letter of each word capitalized), you can rectify it using the following method:
- Find an empty cell and create your formula: Place your cursor in an empty cell and write one of the following formulas, followed by the cell you want to convert to uppercase, lowercase, or proper case:
- =upper(c1)
- =lower(c1)
- =proper(c1)
- Press “Enter” after writing the formula, and you will see that the original cell is copied but now in the desired format.
- Copy your formula (values only): Select the cell where your copy is now written in the correct format, then click “Copy.” Next, go to the original cell where you want to change the text, right-click, and choose “Paste Special > Paste values only.” Your cell should now be copied in the desired format.
Although this method works for one or two cells, changing the format for a list of cells can be time-consuming. In such cases, you may want to install a Google Sheets add-on to streamline the process.
To access add-ons, select the “Add-ons” tab in the top bar and choose “Get add-ons.” If you don’t see the “Add-ons” tab, it is likely that you don’t have permission to modify the Google Sheets or that this option has been disabled by your administrator.
Once you have the ability to install an add-on, you can use tools like ChangeCase, which enables mass formatting changes for multiple cells at once, saving you a significant amount of time.
Final Thoughts
While formatting in Google Sheets may seem complex at first, the short-term effort is worth the long-term efficiency and productivity gains. By learning a few simple tips and tricks, you can work smarter and submit more estimates on time.
Discover more tips and tricks on Crawlan.com to unlock the full potential of Google Sheets and enhance your user experience.
