How to: Sort, Filter, Merge Cells and Freeze Rows or Columns in Google Sheets

If you think Google Sheets doesn’t offer you the features and functions necessary to create, maintain and update your elaborate spreadsheets and dashboards, think again. Google Sheets provides many of the same basic and advanced functions that you may be familiar with using in Microsoft Excel. However, the processes differ to achieve similar results.

Throughout this series, we will introduce the basic, intermediate and advanced features of Google Sheets. In turn, you can begin utilizing the application to track and manipulate data for your company, or for your own personal use. Whether the task at hand is maintaining your daughter’s softball team’s roster and schedule, or creating visual representations of your quarterly sales data, Google Sheets makes it easy.

In this post, we will discuss the following basic features available to you in Google Sheets:

• Sort Range (Multi-level Sort)
• Merge Cells
• Freeze rows/columns
• Filter

Sort Range:

Sorting data is an excellent way to arrange your data into categories in order to prepare for analysis, or quickly transform a jumbled spreadsheet into a conducive tool for your organization.

Example: The HR, IT and Sales department leads would each like a contact list to help them quickly identify their team’s phone numbers and office locations.

Below is the master contact list, organized alphabetically by employee last name, or, Column A:

The following are steps for sorting a range of data in Google Sheets:

1. Highlight the range of data in which you would like to sort.
2. Select Data from the menu.
3. Choose Sort Range from the drop down list.

4. Add a check mark in the box next to “Data has header row” if your highlighted data includes headings. In the example, the Last Name, Dept., Phone and Location cells are the column headings, which we do not want to sort along with the other data. Adding the check mark omits the header row in the Sort Range's actions.
5. Determine the level, or levels at which you would like to sort your data (Note: If the example did not have a header row, the selections indicated below would be “Sort by Column B, then by Column D, then by Column A.”).
6. At each level, choose to sort in Ascending (A-Z) or Descending (Z-A) order.  

Now, the contact list is sorted first by Department, then by Location and then alphabetically by Last Name. The department leads can quickly identify their team members, which are sorted by location and then alphabetically by their last names. 

There are various functions available in Google Sheets that help make your spreadsheets easier to comprehend. Merging cells is a simple way to create a heading across multiple columns, and freezing rows allows you to keep information locked in place as you scroll through the spreadsheet.

Merge Cells:

In the example above, new rows were inserted at each change in department to accommodate headings for each department/location. Adding headings allows us to delete or hide the columns specifying which department and which location corresponds to each employee. Merge cells allows us to stretch the headings across multiple columns.

The following are steps for merging cells in Google Sheets:

1. Highlight the cells in which you would like to combine as one cell.
2. Select the Merge cells icon.

3. From the drop-down list, choose Merge All, Merge Horizontally, Merge Vertically or Unmerge. In this case, Merge Horizontally should be selected from the drop down menu in order to merge two cells side by side, and those specifically located in row numbers 2, 6, 10, 13, 16 and 19.

Freeze Rows or Freeze Columns:

Freezing rows or columns in Google Sheets helps make your spreadsheet more reader-friendly. For example, as you scroll down through a large data set, it is helpful when the header row remains displayed at the top of the columns.

Google Sheets allows users to freeze up to five columns, and up to ten rows.

The following are steps for freezing rows or columns in Google Sheets:

1. Highlight the row(s) or column(s) you would like to freeze. In the example, row 1 was highlighted by clicking on the row number displayed on the left side of the screen.
2. Select View from the menu items.
3. To choose the number of rows or columns you wish to freeze, hover over Freeze rows or Freeze columns and select accordingly from the drop-down list. In the example, Freeze 1 row - the header row - was selected.

Filter

Applying a filter to a range of data in Google Sheets is a quick way to choose exactly what criteria to display in each column.

In the example below, the Sales Department employees are filtered in Column B, and then the Philadelphia location is filtered in Column D. As a result, only the Philadelphia Sales Department employees are listed, and the remainder of the contact list is hidden.

The following are steps for filtering data in Google Sheets:

1. Highlight the data in which you would like to apply the filter.
2. Select Data from the menu.
3. From the drop-down list, choose Filter.

After a filter is applied in Google Sheets, the row numbers and column letters displayed in Green indicate which data will be available for filtering action.

To take action, refer to the drop down arrow that is now shown on the right side of each column heading.

1. Select the drop-down arrow.

2. Specify which criteria you would like to filter by removing/adding a check mark next to the items listed. In the example below, “Sales” is selected to be filtered in Column B.

A green filter icon is now displayed where the drop-down arrow was located next to “Dept.” The icon is a reminder that the data in Column B has been filtered.

To narrow down the list of Sales Department employees even further, filter a specific location in Column D.

1. Select the drop-down arrow in the Location column heading.

2. Add/Remove a checkmark next to the item(s) you would like displayed. In the example, “Philadelphia” is selected to be filtered in Column D.

Filters are a simple way to segment a master list of data without making formatting changes to the spreadsheet.

In the example below, our end result is a filtered list of Sales department employees working in the Philadelphia office, as well as their contact details.

In two clicks, you can easily revert back to the full employee list.

Simply:

1. Select Data from the menu items.
2. Choose Turn off filter from the drop-down list.

Google Sheets makes it easy to create a masterpiece on your own, with the bonus of also being able to collaborate with others simultaneously in a spreadsheet. You can choose to share your spreadsheet with others and grant them permission to view only or make edits to the document. Additionally, Google Sheets allows multiple people to make changes to a document at the same time. The revisions are tracked so you can easily view which collaborator made each change. If there are questions or comments along the way, Google Sheets also tracks comments and instant messages that take place in the document.

Below, we’ve outlined how real-time collaboration in Google Sheets can remedy a few circumstances that you may have encountered in the past.

Scenario: I wonder which one of these four versions of the budget report is accurate, or the most up-to-date?

Google Sheets quick fix: Save the master budget report in Google Drive and share the report with the appropriate individuals to ensure only one copy circulating to your team.

Scenario: Please let me know when you are done in the budget report, so I know when I can access it to update my information.

Google Sheets to the rescue: Team members can make edits and add comments in a spreadsheet simultaneously.

Scenario: I am not so sure about my teammate’s changes to the budget report this week.

Google Sheets saves the day: Add a comment to start a discussion regarding a particular cell, where other users will have the ability to reply to the comment, mark it as resolved or clear it when it is not longer legitimate.

As you can see, Google Spreadsheets offer numerous features and functions necessary to create, maintain and update your elaborate spreadsheets and dashboards. We’ve covered a few of those basic functions in this post. If you have any questions about how to use these functionalities, we invite you to chat with one of our experts during Blog Office Hours, every Thursday from 11-12 est. Additionally, if you would like to schedule a Google Spreadsheets training for your organization, please visit the Training section of our website. Stay tuned for the next installment of our Google Spreadsheet Series, “How to: Create Visual Representations of your Sales & Marketing Data with a Dashboard in Google Sheets”.

How to: Add events to your calendar directly from email & use the new chat experience in Google Drive

As usual Google has released a number of impressive updates this week to the Google Apps for Business platform. In this post we would like to highlight the two updates we are the most excited about!

• Adding Events to Your Calendar Directly From an Email
• The New Chat Experience in Google Drive

Adding Events to Your Calendar Directly From an Email

The wait is finally over! This feature is now available to domains on the Rapid release track, meaning your organization can experience the newest updates and features immediately. Now you can quickly add events to your calendar from an email message by simply clicking on the lightly underlined event date and time.

Once you've clicked on the event details, you will click the 'Add to Calendar' button.

Next, you will see the event details calendar box. From here you can edit the title, date, and time of the event, as well as see what other activities you have going on that day in the right side pannel. Simply click 'Add to Calendar' and the event will be automatically populated to your calendar. Additionally, you will notice that the calendar event will include a link back to the original email.  They thought of everything!

Utilizing this new feature will help you easily organize your calendar events to work more efficiently!

Note: You have to have English designated as your preferred language to use this feature. 

Using the New Chat Experience in Google Drive

The second new feature we are very jazzed about is the new chat experience for Google Docs & Slides. The new layout looks similar to the Gmail experience and allows users to provide direction to document collaborators quickly which streamlines the creation process. This update is available to those users on the Scheduled release track.

To initiate a chat, simply click on the icon in the top right corner in between the images of the people in the document and the 'Comments' button. 

You will see the chat box pop-up in the bottom right corner of the document. This makes it very easy for you to quickly communicate with all collaborators working in the doc. 

In addition to this update, you will notice that you will now see the Google+ profile image of individuals accessing the document instead of their name. If someone has not imported a Google+ profile photo, their image will be that of a random animal.

Take advantage of these new features to stay organized and increase your productivity! If you have any questions on this feature or any other Google Apps features, we invite you to ask our experts during our weekly Blog Chat, every Thursday from 11-12 est. Visit our website www.ditoweb.com to discover the Google Apps for Business training opportunities available to you and your organization.

Pro Tip: Create Tasks Directly from Email Messages

Gmail provides us with many different tools to optimize our organization and productivity. Last month, for our Pro Tip we looked at how to to keep your chat messages organized within email threads by replying via chat. This month we are going to build off of this organization theme. Gmail’s Tasks help us keep track of the things we need to do. We use Tasks to create lists of items, set due dates, and notes. One feature of Tasks that is often overlooked is the ability to add Gmail messages directly to Tasks. If someone sends you an email with a list of things to do, or important information, this shortcut will save time as you can transform the message into a task item.

Create a task from an email message using the following steps:

• Open the email you would like to turn into a task.
• Click the More drop-down and select Add to Tasks. Alternatively, you can use the keyboard shortcut Shift + T.

• Your Tasks should automatically open in the bottom right, but if it does not, then select Mail and then Tasks.

• The subject of your message is now a task item. You can view the Related email directly from the task. Clicking on this will open the email Next Weeks Meeting. No more copying and pasting emails into Task!

 

• Select the arrow to edit details. From here you can set a due date, add notes, view the related email, or move it to a different task list. All of your favorite features in one spot!

Tip:  Keeping a detailed task list will help boost your productivity, and setting the due date will automatically sync with your Google Calendar.

• Open your task list in a new browser tab by selecting the pop-out arrow. Now you can drag the corner of the task list to make it larger, or move it to a different part of your screen.

Use this Pro Tip to help save time when creating Tasks. If you have any questions on this feature or any other Google Apps features, we invite you to ask our experts during our weekly Blog Chat, every Thursday from 11-12 est. Visit our website www.ditoweb.com to discover the Google Apps for Business training opportunities available to you and your organization.

How Marketing Professionals Use Google Apps

Google Apps for Business features can be used to streamline many processes across an organization, from the Operations department to Sales & Marketing. However, after a number of discussions with our customers about how Google Apps features are being used in Marketing departments, it has become clear that many marketing professionals have not implemented these features to their full potential.  To help with this implementation, we have outline three ways you can quickly apply Google Apps features to your marketing processes.

• Use Google Drive folders as brand, product, & messaging directories
• Use Google Forms -- for EVERYTHING!
• Event Registrations
• Customer Quotes
• Surveys
• Upload Your Current Spreadsheets

Use Google Drive Folders as brand, product, & messaging directories

One of the many challenges for Marketing Professionals is maintaining consistent branding and messaging for the company as a whole as well as for all of its products and services. Typically, as updates and changes are made to branding or messaging, marketing will notify staff and distribute the most current version of the assets, flyers, white papers, etc.  To ensure the continuity of this information throughout the company, we suggest creating branding, product, and messaging directories in Google Drive to store all this information. That way, staff will know where they can locate necessary information without having to contact marketing or hunt through emails. Using Drive Folders will significantly reduce the possibility that your reps will use outdated or incorrect branding, messaging, or assets. That never happens right? A Google Drive Folder is a simple and clean way to share the most current information with your team.

One key piece of content that should be included in each of your directory folders is a “Directory” spreadsheet. This spreadsheet should list all pieces of content in the folder with fields for: links to the piece, version information, owner, audience, and product or service. You may like to ask your staff to subscribe to any changes to the spreadsheet. This would trigger an email notification to staff when a change has been made. To do this, simply follow these instructions.

Tips for Your Shared Folder

• Share with anyone in your Domain
• Include a “Directory” document as seen above
• List folder link within your company intranet
• Link to folder in all branding/message update notifications
  
  

The same idea can be applied to your event management process. Simply create a folder containing all relevant documents and flyers and share it with the staff members working the event. This way, if they run out of a particular flyer while onsite at a show, they can quickly print replacements from Google Drive. And, if you didn’t know already, you can now print directly from Google Drive at any Fedex Office location. Say goodbye to your thumb drive!

Use Google Forms

Google Forms are a powerful and simple tool that anyone can use to easily create custom marketing documents. Send forms both internally (colleagues) and externally (prospects/customers) to instantly collect data, regardless of whether the recipients have a Google account. The major benefit of forms is how quickly you can gather information and have it automatically populate in a spreadsheet. Not only will you have a timestamp of when the info was entered in the spreadsheet, but you can view a summary of the responses to see your data broken down into charts and graphs. This makes it very easy to see trending information. You can also subscribe to the spreadsheet corresponding with the form to be notified of any changes such as a new entrants.  To do this, simply follow these instructions.

There are many different kinds of forms you can create and use for marketing purposes. Here at Dito, our Marketing department leverages forms for all of our event registrations, work requests, and customer surveys. See the examples below. For more information on how to setup Google Forms, read our post How to get the most our of the new Google Forms.

Event Registration Form

Work Requests

Surveys

Upload Your Current Spreadsheets

As marketers we use spreadsheet all day, everyday to show trends and ROI, or to track action items, etc. With Google Apps you can continue using your existing spreadsheets by simply uploading them to Drive. For instructions on how to do this, read our post How to Manually Upload Files and Folders into Google Drive. The benefits to storing spreadsheets in Drive is that you can access the file at anytime, from any device, and you can easily share the spreadsheet with coworkers. No more emailing spreadsheets back and forth or asking if your coworker has “closed it out”. Ugh, how many times did I yell that over a cubicle?! All the data is the most current and your entire team can be editing the same spreadsheet at the same time.

Examples of helpful spreadsheets for Marketers:

• Collection of customer quotes
• Editorial calendar for company blog
• Speaker directory
• Directory Content List - as discussed above
  
  

These three suggestions are just a chip off the iceberg of possibilities and provide clear examples of how to apply Google Apps in your marketing department. How are you using these tools? Please share your suggestions in the comments section below. As always if you have any questions on how to use any of the features of Google Apps for Business, please chat with an expert during our blog office hours, every Thursday from 11-12est.

The Top 5 ways to stay connected while on the road.

So far this year, our team of Change Management Specialists (AKA Google Apps Trainers) have been traveling non stop. While they’re on the road delivering Google Apps training, they are still expected to stay connected so they can reply to customer emails and help out fellow staff members. In today’s world, everyone is on the go and our Trainers are no exception. Their graceful management of responsibilities and deliverables while traveling inspired this post. In this post, we will explore the top 5 Google solutions that help you stay productive and connected while on the go.

1. Multiple Chrome Profiles
2. Chrome Sync
3. Google Offline Services
4. Mobile Apps
5. Chromebooks

1. Multiple Chrome Profiles

Creating Multiple Chrome profiles allows you to manage your work and personal life with ease while in the office or from the road. If you haven’t already set up multiple profiles, we strongly recommend doing so. Once you’ve signed into the Chrome browser, your preferences, bookmarks, apps, extensions and themes will be automatically updated and saved to that profile. This becomes especially helpful when you set up Chrome Sync on your devices (see #2). Multiple profiles also ensure that your personal browsing history is recorded under your personal profile instead of your work profile.

To begin, we will first establish a personal Chrome profile. To do this, open your Chrome browser, click the ‘Customize and control Google Chrome’ button on the top right of your browser toolbar, and click on ‘Settings.'

Next, find the ‘User’ section and select ‘Add new user.'

You will need to select an icon and you will have the option to adjust the profile name. We suggest renaming the profile ‘personal’ to make it easy to distinguish. Once you have done this, click ‘Create’.

You will now be prompted to sign in. Sign in using your personal Gmail address and password.

You will notice that the icon you selected is now visible in the top right corner.

Repeat this process to create each of you profiles. For example, I have three Chrome profiles: one for my personal email, one for my Dito email, and one for my training account. To switch between accounts, simply click on the icon image at the top of your browser and select which account you would like to use.

You can also add new users from here as well. Mac users can switch between users profiles by right-clicking on the Chrome icon in the Dock.

Note: When you first open your browser, you may notice your email in the top right corner. This indicates which profile you are logged in to. To add another profile, simply click on the ‘Customize and control Google Chrome’ button and select ‘Signed in as ‘your email’’. This will bring you right back to the settings menu where you can add an user.

Note: You can not create multiple users on a Chrome device using the steps above. Please refer to Google Support’s post ‘Sign in to Chrome device - Adding accounts to the device.’

2. Chrome Sync

The second way you can stay connected while on the go is by setting up Chrome Sync on all your devices. Once enabled, Chrome Sync captures your browser preferences, bookmarks, apps, extensions, and themes automatically and syncs them instantly to your Google Account. This is an especially powerful feature for those of us who are constantly accessing different devices throughout the day or when traveling. 

For example, when I travel I bring my smartphone, MacBook and Chromebook. All my essential docs, apps, and bookmarks are automatically updated to each. Being able to transition seamlessly from any of those devices is very often the difference between meeting a deadline and not.  To learn how to set up Chrome sync on your devices, read our post Pro Tip: Utilizing Chrome Sync.

3. Google's Offline Services

One of the improvements to Google’s offerings that has really impacted productivity is the addition of offline availability for Google Drive. You can now access Google Documents, Spreadsheets, and Presentations while offline. Great for finishing up that presentation or proposal while flying! Once your internet connection is reestablished, your edits will be updated automatically. To learn how to enable offline access, read our post How to Enable Slides for Offline Editing. The instructions apply to Documents and Spreadsheets as well.

You also have the opportunity to access your Gmail and Calendar while offline. To access these, you will need to add the apps to Chrome from the Web Store. You can also add the apps through the settings menu of Gmail and via the Calendar’s ‘Gear’ drop down menu. 

The image below shows where to add Offline access for Gmail.

After installed, you will need to ‘Allow offline mail’. Once you click ‘Continue’, you will be able to manage and compose email when not connected to the internet.

As for Calendar, once Offline is enabled you will be able to view and respond to events.

Note: Do not enable Calendar Offline if you are using a shared computer as your information will be saved to the Chrome browser on that computer.

4. Mobile Apps

The fourth way you can stay connected while on the go is by installing Google apps to your mobile device. These apps allow you to communicate with your team, edit and produce documents, and manage your schedule all from your mobile device. To learn how to install Mobile apps to your smartphones or tablets, please refer to one of these expert posts.

Setting Up Google Apps on Android

Setting up Google Drive on an iPhone or iPad

Chrome for your iPad/iPod/iPhone

5. Chromebooks

If you haven’t heard of or used a Chromebook you are in for a real treat! Chromebooks have replaced the traditional computer. These fast and intuitive devices give users secure access to the applications, documents, and tools they need to work from wherever they are; in the office or on the road. Each Chrome device includes three years of Google Enterprise Support and a 1-year warranty on hardware directly from the manufacturer and with automatic updates your device is always current. As a Chromebook reseller, we will help you leverage these tools within your organization with guaranteed deployment packages paired with our industry leading change management and training.

Chromebooks are smaller, lighter, and faster than traditional laptops, making them not only a great travel companion but a powerful device for the work day. In fact, our CEO uses his Chromebook exclusively. Additionally, they are resilient. One of our developers accidentally left his Chromebook on the roof of his car and drove away. After retracing his steps, he found it leaning against a curb. It had a few scratches but still worked just as well as it did before. Evaluate a Chromebook today!

Thanks for reading our top 5 ways to stay connected while on the road! How do you stay connected? Please share your tips and tricks in the comments below. If you have questions about any of these features, ask an expert during our blog office hours every Thursday, from 11-12pm est.