How to use Google Sheets – A Detailed Guide for Getting Started

Last Updated: April 05, 2024
puneet-gogia-excel-champs

- Written by Puneet

What is Google Sheets?

Google Sheets is an online (Web-only) spreadsheet application part of Google’s suite of online productivity tools, Google Workspace. Like Microsoft Excel, it allows users to create, format, and work on spreadsheets. It supports complex formulas, pivot tables, conditional formatting, and charting.

use-google-sheets

One of its significant benefits is its seamless integration with other Google services, real-time collaboration, and the ability to access the files across all devices.

Google Sheets Components and Terms

Google Sheets interface has various components, each with a specific one that enables users to create, manage, and analyze data effectively.

google-sheets-components-and-terms

Below are some of the most important:

  • Cell: A small box in which you have every spreadsheet application to store a single data value. Each cell is identified by its unique address, a combination of the column letter and row number.
  • Rows and Columns: A row is a horizontal set of cells identified by numbers, while columns run vertically and are identified by letters. Together, rows and columns create a grid, and their intersections create cells where data can be entered.
  • Range: A range refers to a group of cells that have been highlighted or selected. Ranges are useful when performing operations on multiple cells at once, such as summarizing a list of numbers or applying a format to a group of cells.
  • Sheet: A sheet is a single page or tab within a Google Sheets file. Each sheet can contain its data, charts, and other elements. A single Google Sheets file can contain multiple sheets, allowing you to organize related data within a single file.
  • Workbook (File): A workbook is a Google Sheets file that contains one or more sheets. Each workbook can be saved to your Google Drive and shared with others for collaborative editing.
  • Formulas: Formulas are mathematical expressions that perform calculations, extract values, or test conditions. They always start with an equals sign (=). For example, to add numbers in cells A1 and B1, you would write the formula =A1+B1.
  • Functions: Functions are predefined formulas in Google Sheets. They perform specific calculations, which can range from simple operations like addition (SUM) and average (AVERAGE) to more complex tasks like finding the length of a text string (LEN) or extracting a specific day from a date (DAY).
  • Pivot Tables: Pivot Tables are a powerful data summarization tool. They allow you to analyze large data sets by automatically sorting, counting, totaling, or averaging the data stored in one table or spreadsheet. It lets you extract meaningful information from large, detailed data sets.
  • Conditional Formatting: This feature allows you to format cells based on specific conditions. For example, you can use conditional formatting to automatically change the background color of cells that contain values above a certain threshold, or to highlight rows that contain specific text.
  • Charts: Charts create graphical representations of data. Google Sheets provides various chart types, including line, bar, and pie charts, which can help you visualize patterns, trends, and relationships in your data.

Google Sheets Vs. Excel (Which One is Better)

Google Sheets and Excel, developed by Google and Microsoft respectively, are both powerful spreadsheet programs widely used for data management, analysis, and visualization.

Each has its strengths and the choice between the two primarily depends on the specific needs and circumstances.

google-sheets-vs-excel
Difference
Google Sheets
Microsoft Excel
Accessibility and Collaboration
Google Sheets is cloud-based and allows real-time collaboration. Multiple users can view and edit a workbook simultaneously from any device with an internet connection.
Excel is primarily a desktop-based application. Collaboration is possible via OneDrive or SharePoint, but it’s not as seamless as Google Sheets.
Complexity and Power
There are numerous categories of functions in Google Sheets, such as math and trigonometry, statistical, text, date and time, financial, and logical.
Excel is known for its advanced features and functions, such as PivotTables, Power Query, and VBA macros. It is often the tool for heavy-duty data analysis and complex financial modeling.
Integration
Google Sheets integrates seamlessly with other Google applications, such as Docs, Slides, and Drive, making it convenient for users who are already using these tools.
Excel integrates well with other Microsoft Office products like Word and PowerPoint, providing a cohesive workspace for users invested in the Microsoft ecosystem.
Cost
Google Sheets is free for personal use, making it an accessible tool for individual users and small teams.
Excel is part of the Microsoft Office Suite, which requires a subscription. This might be a consideration for budget-conscious users.
 Visualization
Google Sheets offers basic to intermediate data visualization options like charts and graphs, which are sufficient for most common uses.
Excel has more advanced charts options, providing greater flexibility for specialized and complex representations.
Updates and Version Control
With Google Sheets, updates are automatic, and version control is straightforward with its revision history feature. It makes it easy to track changes and revert to previous versions if necessary.
In Excel, users must manually update the software. Version control can be more complex, especially when collaborating, requiring more careful management of workbook versions.

In summary, if real-time collaboration, easy access, and integration you need, Google Sheets is the way to go.

Create a New Google Sheets File

To create a new Google Sheets file:

  • Go to the link (https://sheets.google.com/ and make sure to login first to your Gmail account.
open-sheetsgoogle-link
  • Once you’re signed in, you will see the Google Sheets interface with a “+ Start a new spreadsheet” button. Click on this button.
create-new-spreadsheet
  • A new, blank Google Sheets workbook will open in a new tab. You can start editing this workbook immediately.
blank-google-sheets

Note – All changes in Google Sheets are saved automatically, so you don’t need to worry about saving your work manually.

NoteGo to this link and sign in with your Gmail ID. Enter the username and password (if not login already). Otherwise, this link will open a new Google Sheet instantly.

From Google Drive

  • Open “drive.google.com”, if you’re not already signed in, sign in to your Google account.
google-sheets-from-google-drive
  • Once you’re in Google Drive, click on the “+ New” button on the left-hand side of your screen.
click-new-button-in-drive
  • Dropdown menu will appear. Hover over the “Google Sheets” option. A secondary menu will appear to the right. You can choose to create a new blank spreadsheet or use a template. Click on the option you prefer.
click-on-blank-spreadsheet
  • A new Google Sheets workbook will open in a new tab.

Understanding Google Sheets Toolbar

All the options available in Google Sheets are categorized into menus.

google-sheets-toolbar
  • File: This includes options such as New, which allows you to create a new spreadsheet; Open, which lets you open existing spreadsheets; Save, to save your current work.
  • Edit: This provides options like Undo to revert your last action, Redo to repeat your last action, and Cut-Copy-Paste.
  • View: This includes options like Zoom to magnify or shrink your view of the spreadsheet; Freeze to keep a row or column visible while scrolling.
  • Insert: This offers options like to add a new sheet, to insert a chart, to add an image, to insert a drawing, and more.
  • Format: This has options to change font setting like font, size, color, borders, fill color, etc. Conditional formatting, to automatically format cells based on their values, and other options.
  • Data: This includes options like Sort the data, apply Filter, create a Pivot table, Data validation, and more.
  • Tools: This provides options like Spelling check, Script editor, to write and run scripts; Macros, to automate repetitive tasks, and other options.
  • Add-ons: This consists of options related to additional features or extensions that you can add to Google Sheets to enhance its functionality.
  • Help: This includes options like Sheets Help, to access Google’s help center for Sheets; Training, to find educational resources about using Sheets; Updates, to see the latest changes to Sheets, etc.

To search for an option within the menus, you can use the keyboard shortcut Alt + / to open the search bar.

Naming / Renaming a Google Sheets File

Here are the steps on how to do it:

  • Once your workbook is open, look to the top-left corner of the screen. Here, you’ll see the current title of the workbook. Click on this title.
naming-google-sheets
  • Edit the filename: A text box with the current name highlighted will appear upon clicking the title. At this point, you can either delete the current name entirely or add to it.
  • Save your Changes: After typing the new name, press the Enter key on your keyboard to save it. Remember to name your file clearly and accurately for easy identification in the future.

Apart from this, you can rename a file form the list of files in Google Sheets main page. Click on the three dots to open the menu and the click on the “Rename” option.

rename-a-file

Create Table in Google Sheets

  • Select a starting cell for your table: Click on an empty cell where you want the top-left corner of your table to be. This will be the starting point of your table.
create-a-table
  • Enter the Data: Begin entering your data into the cells, moving from left to right to fill out your rows and top to bottom to create new columns. Each row and column of cells you fill in will form the structure of your table.
  • Format your table: After you’ve entered your data, you can start to format your table to suit your needs.
    • Cell Alignment
    • Alternate Row Shading
    • Format Each Column with right Data Type
format-table

Functions and Formulas in Google Sheets

Google Sheets has many functions easy to complex calculations. To use a function or a formula, you begin with an equal sign (=), followed by the name of the function.

For example, to add two numbers in cells A1 and B1, you would use the function “=SUM(A1:B1)”.

functions-and-formulas

There are numerous categories of functions in Google Sheets, such as math and trigonometry, statistical, text, date & time, financial, and logical.

categories-of-functions

Some commonly used functions:

  • SUM: Adds up all the numbers in a range of cells.
  • AVERAGE: Computes the average of a range of cells.
  • COUNT: Counts the number of cells in a range that contain numbers.
  • MIN and MAX: Finds the minimum and maximum number in a range of cells, respectively.
  • IF: Evaluates whether a condition is met and returns one value if it is true, and another value if it is false.

Share a Google Sheets File

To share a Google Sheets file, follow these steps:

  • Click on the Share button in the upper-right corner of the screen.
share-google-sheets-file
  • In the pop-up window, you can either type the email addresses of the people you want to share the workbook with or create a shareable link and send it to them.
add-users-to-share-file
  • You can also select whether the people you share the workbook with can edit, comment on, or only view the workbook.
  • Click on Done to save your changes and close the window.

Download Google Sheets File as Different Format

Downloading Google Sheets in a different format can help you in several ways, such as offline Access, Data Backup, and Sharing and Collaboration.

download-google-sheets-in-another-format

Here are the steps to save a Google Sheets file:

  • Once your file is open, click on the “File” option in the top left corner of the Window.
  • After clicking on “File”, scroll down through the options until you find “Download”.
  • A dropdown will appear with a list of available file formats. (xlsx for Excel, ods for OpenOffice.pdf for Adobe Acrobat, csv for comma-separated values, tsv for tab-separated values, html for a web page).
  • After selecting your preferred file format, the file will automatically download in that format.

Adding New Tabs (New Worksheet)

  • Look for the “+” Button: At the bottom of the Google Sheets interface, you’ll see a “+” button towards the right side.
add-new-tab
  • Add a New Sheet: Click the “+” button to add a new sheet. A new sheet (tab) will be created immediately and will be named with the sequential number by default (e.g., “Sheet1”, “Sheet2”, etc.).
add-new-sheet

Formatting in Google Sheets

In Google Sheet, you can format your data the way you want. Here are some basic tips for formatting:

  • Font Style and Size: You can change the font style and size from the toolbar at the top. Select the cells you want to format, then choose a new font style and size.
  • Text Color and Cell Background: Use the toolbar button color of your text and the background color of your cells.
  • Bold and Italic: Use the B (bold) and I (italic) buttons in the toolbar to format your text accordingly.
formatting-in-google-sheets
  • Align Text: You can align text vertically and horizontally within a cell. Select your cells, then use the alignment tools in the toolbar to align text to the left, center, or correct, as well as the top, middle, or bottom.
align-text
  • Format as Currency: Select your cells, then go to Format > Number to choose a specific format for your data, such as currency, percentage, date, and more.
format-as-currency
  • Adding Borders: Select your cells and then use the borders icon to add or customize borders around and between your selected cells.
adding-borders
  • Merge Cells: You can merge multiple cells to create a larger cell. Select the cells you want to merge, then click on the merge icon and choose how you want to merge them.
merge-cells

Create a Chart in Google Sheets

Here is a step-by-step guide to creating a basic chart:

  • Select the cells or range of cells for which you want to create a chart. If you have column titles and row labels, include them, as these can be used to label the axes of your chart.
create-a-chart
  • Go to the menu bar, click the Insert menu, and then choose the Chart. This will insert a simple line chart, which is the default chart. Or you can also use the insert chart button on the toolbar.
choose-chart-option
  • Google Sheets will try to choose the most appropriate chart type based on your data, but you can change this.
chart-type-based-on-data
  • Once the chart is inserted, a Chart editor will appear on the right side. In the Chart editor, click on the Setup tab. Click on the dropdown menu under Chart type to see all available options.
chart-editor

In the Chart editor, you can switch to the Customize tab to access various options to tweak your chart’s appearance, including Chart style, Chart & axis titles, Series colors, Legend position, and Font styles and sizes.

switch-to-customize-tab

Sorting in Google Sheets

In Google Sheets, you can sort data alphabetically, numerically, or even by date in ascending and descending order.

  • Select the range of cells you want to sort, including any header row, and if you’re going to sort the entire sheet, select any cell within your data range.
  • Go to the Data menu and click on the “Sort range”. Now choose “Sort range,” then “Advanced range sorting options” to open the sort dialog.
sorting-in-google-sheets
  • Check “Data has header row” in the sort dialog box if your selected range includes headers. Select the column you want to sort by and specify the sort order.
check-data-has-header-row
  • In the end, click sort to apply the sorting.

You can also sort the data from the “Sort range by” options.

sort-range-by-options

But there’s one thing which I feel Google Sheets need to improve that, it doesn’t show me the option to according to the data I have in the columns. Even though I have dates in the columns which I have selected, but the it still show me options for text values.

Sort Data in Google Sheets

Filter Data in Google Sheets

Filtering data in Google Sheets can help you to narrow down the data. You can display only the rows that meet certain criteria while temporarily hiding the rest.

  • First, select the columns you want to filter. You can click on any cell within your data. Go to the Data menu and select Create a filter. You can also click the filter icon (it looks like a funnel) in the toolbar to apply filters to your selection.
filter-data-in-google-sheets
  • Click on the dropdown arrow in the column’s header cell you want to filter. To filter by specific values, uncheck the boxes next to the values you want to hide and leave the boxes checked for the values you want to display.
click-on-dropdown-arrow
  • In the end, click Ok to apply the filter to the column.
filter-button-applied

Using Comments in Google Sheets

Adding comments in Google Sheets is a fantastic way to collaborate with others or make notes for yourself within the sheet.

using-comments-in-google-sheets
  • Adding a Comment – Select a cell and then from the right click menu, select Comment, or from the Google Sheets menu, choose Insert > Comment.
  • Type Your Comment: A comment box will appear. Type your comment in the box. Once done, click the Comment button or press Ctrl + Enter (Cmd + Enter on Mac).

Mentioning Someone in a Comment – In the comment box, type @ followed by the email address of the person you want to mention. Suggestions pop up as you type if they have access to the sheet.

mention-someone-in-comment

Integration with Other Applications

Integrating Google Sheets with other applications can enhance productivity, allowing automated data transfers, real-time updates, and more efficient workflows.

  • Built-in Google Sheets Integrations – Google Sheets comes with built-in integrations that allow you to connect with other Google Workspace applications and selected third-party services, like, Google Forms, Google Data Studio, Add-ons.
  • Google Apps Script – Google Apps Script is a cloud-based scripting language for light-weight application development in the Google Workspace platform.

Some Quick Google Sheets Tips

Here are some tips to help you get the most out of Google Sheets:

  1. Learn Keyboard Shortcuts – Use keyboard shortcuts to speed up your workflow. For example, Ctrl + C to copy, Ctrl + V to paste, Ctrl + Z to undo, and Ctrl + Shift + L to toggle filters.
  2. Explore Built-in Functions and Formulas – Google Sheets supports a wide range of functions that can help you in calculations. Functions like SUM(), AVERAGE(), and VLOOKUP() are highly useful.
  3. Collaborate in Real Time – Share your workbook with teammates and collaborate in real-time. You can set different permission levels (view, comment, edit) and even chat within the spreadsheet if others view it.
  4. Link Data Between Sheets – You can link data between sheets within the same workbook or between different workbooks. You can use functions like, =IMPORTRANGE, =QUERY, or =VLOOKUP across sheets.
  5. Keep Your Data Clean – Use TRIM() to remove extra spaces from text except for single spaces between words and CLEAN() to remove non-printable characters from text.