Spreadsheets and the data life cycle

To better understand the benefits of using spreadsheets in data analytics, let’s explore how they relate to each phase of the data life cycle: plan, capture, manage, analyze, archive, and destroy.

• Plan for the users who will work within a spreadsheet by developing organizational standards. This can mean formatting your cells, the headings you choose to highlight, the color scheme, and the way you order your data points. When you take the time to set these standards, you will improve communication, ensure consistency, and help people be more efficient with their time.
• Capture data by the source by connecting spreadsheets to other data sources, such as an online survey application or a database. This data will automatically be updated in the spreadsheet. That way, the information is always as current and accurate as possible.
• Manage different kinds of data with a spreadsheet. This can involve storing, organizing, filtering, and updating information. Spreadsheets also let you decide who can access the data, how the information is shared, and how to keep your data safe and secure.
• Analyze data in a spreadsheet to help make better decisions. Some of the most common spreadsheet analysis tools include formulas to aggregate data or create reports, and pivot tables for clear, easy-to-understand visuals.
• Archive any spreadsheet that you don’t use often, but might need to reference later with built-in tools. This is especially useful if you want to store historical data before it gets updated.
• Destroy your spreadsheet when you are certain that you will never need it again, if you have better backup copies, or for legal or security reasons. Keep in mind, lots of businesses are required to follow certain rules or have measures in place to make sure data is destroyed properly.

Resources

Spreadsheet shortcuts can help you become more efficient with spreadsheets. If you’d like to learn more, you can explore the collection of Google Sheets shortcuts, or visit the Microsoft Excel shortcuts page if you are using Excel. Both of these resources contain a list of spreadsheet shortcuts you can save and reference as you work more with spreadsheets on your own.

Hands-On Activity: Introduction to Google Sheets

Activity overview

By now, you have been introduced to spreadsheets and their role in data analysis. In this activity, you will work with a spreadsheet in Google Sheets. You will create and edit a spreadsheet, share the sheet with others, and add comments to the sheet. Google Sheets is a cloud-based spreadsheet application. You can use Sheets to organize and analyze data from any online device. All the changes you make are automatically saved in the cloud.

By the time you complete this activity, you will be more familiar with some of the key features of Google Sheets. Knowing how to work with spreadsheet applications is an essential skill for any data analyst. Spreadsheets are powerful tools because they let you store, organize, analyze, and share data.

What you will need

There are many excellent spreadsheet applications available to data analysts, such as Google Sheets, Microsoft Excel, and more. This activity uses Google Sheets. If you want to follow along with a different spreadsheet application, the steps will be similar.

If you want to use Google Sheets, you will need a Google account. If you don’t yet have a Google account, you can follow the instructions from the Google account support page. Once you have your Google account set up, you can start working with Google Sheets!

Create and edit a Google Sheet

As you progress in the program, you will become more familiar with spreadsheets. You’ll learn how to use functions, formulas, pivot tables, and more to organize and analyze data. For now, you’ll begin with the basics: how to create, edit, and format your own spreadsheet.

Create a new spreadsheet

1. To start, go to www.google.com.
2. Click the Google apps icon.
3. Then, click the Sheets icon.

4. In the Start a new spreadsheet section, click Blank to create a new blank spreadsheet.

Now you’ve got a new spreadsheet that you can use to enter your data!

Edit and format your spreadsheet

For this activity, you want to create a spreadsheet that contains sales data for a local bakery. Here’s an example of the spreadsheet you will create:

1. After you create your sheet, give it a title. In the upper left corner of your sheet, click Untitled spreadsheet and enter a title. Make your title clear and concise. It should describe what the data in the spreadsheet is about. For example, you could use the title Bakery Sales March 2020 or something similar.
2. Next, enter your column headers. The first row of the spreadsheet is for data attributes, which is another name for column headers. An attribute is a characteristic or quality of data used to label a column in a table. It's basically labeling the type of data in each column. In this case, you want to enter data for transaction date, product name, price, and quantity. Click in cell A1 and enter your first header: Date. Click in cell B1 and enter your next header: Name. Repeat this process for the rest of the attributes.
3. Next, format your column headings to make them stand out clearly. For example, you can make the headings stand out from the rest of the rows by using bold and center align. Click on cell A1 and drag the handle across to cell D1 (you have now selected the range A1:D1). Next, click the bold icon on the toolbar. Then, click the Center align icon on the toolbar.

4. Now, enter relevant data for each column (Date, Name, Price, Quantity). Feel free to use the data contained in the example above, or create your own.
5. If you want to add another column between two existing columns, you can insert a new column. First, click on a column to select it. Then, click Insert on the menu bar and choose where to add the column. You can do the same thing to insert rows.
6. If you want to move an existing row or column, click the row number or column letter to select it. Then, drag it to a new location.
7. If you want to delete an existing row or column, right-click on the row number or column letter that you want to delete. Then, select Delete in the popup menu.
8. (Optional) Feel free to explore some of the other features in Google Sheets. Later in the course, we’ll cover many of these features in detail. For example, you can click Insert on the menu bar and experiment with charts, images, drawings, and more.
9. As mentioned earlier, your data is saved automatically as you are working.

Now that you’ve set up your spreadsheet, share it with others.

Share your spreadsheet

Collaborating with team members is an important part of being a data analyst. When you collaborate, people can make changes at the same time, and you can see their changes as they happen.

Share a spreadsheet

In Google Sheets, you can only share files that you own or have edit access to. Because you created your spreadsheet, you are the default owner.

1. To start, click the Share icon.

2. Under Share with people and groups in the pop-up window, enter the email address of your collaborator.

3. In Google Sheets, users have different access privileges. As owner of the sheet, you can choose the access level for your collaborators. Click Editor in the dropdown and choose the access level:

• Can edit — collaborators can add and edit content or comments. Choose this access level if you want them to be able to make changes to your spreadsheet.
• Can comment — collaborators can add comments, but can't edit content. Choose this level if you just want their feedback.
• Can view — collaborators can view the file, but cannot edit or add comments. Choose this level if you want to share a spreadsheet as an optional resource and you don’t need feedback.

4. (Optional) By default, Google Sheets will send an email notification to the person or group you share your spreadsheet with to let them know they now have access. You can add a note in that email by entering your text in the Message box. For example, you may want to include a description of what data your spreadsheet contains and why you want that person to check it out. If you don’t want to send an email notification, uncheck the Notify people box. This is particularly useful during documentation, when you don’t need people to review your work right away, but may want them to in the future.

5. Click Send.

Share a link to a file or folder

You can send other people a link to a file or folder so that anyone with the link can open it. This is useful when you want to share your file or folder with a large group and do not want to type in everyone’s individual email addresses. You can share files that you own or have edit access to.

1. To start, click the Share icon.
2. In the Get link section of the pop-up window, you’ll notice that the default is Restricted so that only users with whom you’ve shared the file or folder via email can access it.
3. But, if you want to allow others to access your file or folder without having to add their email addresses, click Change.

• By default, your organization is selected. Now choose an access level.

4. Click Copy link.
5. Click Done.
6. Paste the link in an email or any place you want to share it. If the recipient is in your organization, they will be able to access your file or folder without you having to grant them individual access.

Unshare spreadsheets

You can also stop sharing a spreadsheet that you own at any time. You may want to do this if someone switched jobs or teams and should no longer be looking at your data.

1. To start, click the Share icon.
2. Click on the dropdown menu that shows the access level for the person you want to stop sharing the file with, then click Remove.
3. Finally, click Save.

Comment on your spreadsheet

Google Sheets lets you and your collaborators add comments to your sheet and reply to those comments. As a data analyst, this is a great way to share feedback with your teammates.

1. In your sheet, select the cell or cells you'd like to comment on.
2. Do one of the following:

• Right click on the cell and click on Comment in the pop-up menu.
• Or click the Comment icon in the menu bar at the top of your sheet.

3. Next, enter your comment in the box.
4. (Optional) To direct your comment to a specific person, enter an at sign (@) followed by their email address. You can add as many people as you want. Each person will get an email with your comment and a link to the spreadsheet file. If that person does not currently have access to your file, Google Sheets will notify you in a pop-up window and ask if you want to grant that user access..
5. (Optional) To assign the comment to a specific person, check the Assign to box.
6. Finally, click Comment or Assign.

Confirmation and reflection

Question 1

In Google Sheets, what access level should you select if you want your collaborator to comment on your spreadsheet, but not edit the content?

A. Can comment

B. Can edit

C. Can view

D. Can delete

The correct answer is A. Can comment. Explain: In Google Sheets, you should select the access level “Can comment” If you want your collaborator to comment on your spreadsheet, but not edit the content. When you share a Google sheet with collaborators, you can assign them the following access levels: can edit, can comment, can view. As a data analyst, you will often share your work with others to get feedback or to communicate your ideas.

Question 2

In this activity, you had the opportunity to learn about some of the basic features in Google Sheets. In the text box below, write 2-3 sentences (40-60 words) in response to each of the following questions:

• What do you think are the main advantages of using Google Sheets to organize, analyze, and share your data?
• How do you think the comment feature in Google Sheets can increase collaboration among teammates?

Explain:

Congratulations on completing this hands-on activity! In this activity, you learned Google Sheet basics. You created, edited, and formatted a spreadsheet; shared the sheet with others; and added comments to the sheet.

Spreadsheets are an important tool in every data analyst’s toolkit. They help you organize, clean, and analyze data. The sharing and commenting features in Google Sheets let you communicate and collaborate with teammates — a key part of your role as a data analyst. In upcoming activities, you will continue to explore the many ways spreadsheets can help you analyze your data.

Learn more about spreadsheet basics

Below, you will find a list that covers two types of spreadsheet programs: Microsoft Excel and Google Sheets. The list includes quick-start guides, tutorials, and more. The examples in this course use Google Sheets, but you can follow along using Excel or any other spreadsheet application. The user interface might be a little different, but it should look and work similarly.

Microsoft Excel

• Office Quick Starts: Scroll down to the Downloadable guides section to download the Excel Quick Start Guide: This PDF guide begins with a labeled map of Excel that can guide you through the basic tasks you can accomplish in Excel. For tips on starting and opening Excel, this Microsoft Support page will show you how to begin a new workbook.
• Excel video training: This is a collection of step-by-step videos to use all sorts of Excel features, including adding and working within rows, columns, and cells; formatting; using formulas and functions; and adding charts and pivot tables.
• Sort data in a range or table: This page guides you through all of the steps you will need to sort data by number, text, and color. You’ll also have the option to sort by custom list so that you can customize exactly what you want to sort.
• Filter data in a range or table: This article has step-by-step instructions on how to filter an Excel spreadsheet to show only the data you want to see. You can also use built-in comparison operators, such as “greater than” and “top 10” to reveal only the most relevant data.
• Format a worksheet: The guide will help you select and format your Excel spreadsheet, then change the borders, shading, colors, and text. This can help improve your spreadsheet’s readability.

Pro tip: If you’re searching for information about using customizable options, check out Microsoft’s Guidelines for organizing and formatting data on a worksheet. This article provides clear methods for creating easy-to-read spreadsheets.

Google Sheets

• Google Sheets cheat sheet: The cheat sheet puts all the basics of Sheets on a single page for easy reference. Here, you can learn about customizing your spreadsheet and the data inside; working with rows, columns, and cells; sharing your spreadsheet with others; creating different versions and copies of a spreadsheet; and more.
• Get started with Sheets: Create and import files: This guide is a step-by-step guide for working with Sheets. You start by learning how to open a spreadsheet, then move on to adding data.
• Sort and filter your data: This resource can help you organize data in Sheets. Use this guide to sort part or all of a spreadsheet. You can sort by text, number, and color. Then, learn how to create filters to show only certain data while hiding the rest. Finally, the article includes information on creating, saving, and removing a filter view.
• Edit and format a spreadsheet: This will help you make easy-to-read spreadsheets. You will learn how to assign a color, customize borders around cells, and change the appearance of text. If you’d like to give your spreadsheet a theme, you can scroll to the bottom of the page and find how to apply it to parts of your spreadsheet.

Tip: Microsoft Excel and Google Sheets are very similar in terms of calculations, formulas, functions, and many other features. But there are some differences, which can make it tricky to switch from one to the other. If you are moving between Excel and Google Sheets, find a quick list of the differences between the two kinds of spreadsheet applications in Overview: Differences between Sheets and Excel.

Test your knowledge on working with spreadsheets

Question 1

When giving a spreadsheet a title, what are some best practices to follow? Select all that apply.

• Titles should be clear

Explain: Spreadsheet titles should be short, clear, and state exactly what the data in the spreadsheet is about.

• Titles should state what the data in the spreadsheet is about

Explain: Spreadsheet titles should be short, clear, and state exactly what the data in the spreadsheet is about.

• Titles should be in ALL CAPS
• Titles should be short

Explain: Spreadsheet titles should be short, clear, and state exactly what the data in the spreadsheet is about.

Question 2

Fill in the blank: Data analysts can use _____ to highlight the area around cells in order to see spreadsheet data more clearly.

A. italics

B. borders

C. filters

D. pivot tables

Data analysts can use borders to highlight the area around cells in order to see spreadsheet data more clearly.

Question 3

Within a spreadsheet, data analysts use which tools to save time and effort by automating commands? Select all that apply.

• Tables
• Filters
• Functions

Explain: Data analysts use formulas and functions to save time and effort by automating commands.

• Formulas

Explain: Data analysts use formulas and functions to save time and effort by automating commands.