1.4.1.Mastering spreadsheet basics - sj50179/Google-Data-Analytics-Professional-Certificate GitHub Wiki

Columns and rows and cells, oh my!

An attribute is a characteristic or quality of data used to label a column in a table.

An observation includes all of the attributes for something contained in a row of a data table.

A formula is a set of instructions that performs a specific calculation using the data in a spreadsheet.

Q. In a table, an attribute is a characteristic or quality of data used for what purpose?

  • To gather related data

  • To reference a cell

  • To perform a calculation

  • To label a column

Correct. In a table, an attribute is a characteristic or quality of data used to label a column.

Activity overview

So far, you have planned a project, identified the data you need, and collected the data. Earlier in this course, you completed a learning log where you recorded some data from your daily life, then took the practical step of organizing it. Now, you’re ready for the most satisfying step of the data analysis project: visualizing your data!

For this activity, you will move your data to a spreadsheet and bring it to life in a chart. By the time you complete this activity, you will understand how to create a simple graphical representation of information. This is a skill data analysts use to make data easy to understand and interesting to look at. It’s important for reports, presentations, infographics, and more.

What you will need

To get started, first determine what software you’d like to use. We suggest using Google Sheets or Microsoft Excel to create your chart.

Save the spreadsheet with your preferred file naming convention, and store it in a folder to help you stay organized.

To use the template for this course item, click the link below and select “Use Template.”

Link to template: Data Chart Template

Step 1: Open a spreadsheet

Open your spreadsheet in Google Sheets or Microsoft Excel.

Step 2: Familiarize yourself with the spreadsheet

If you are already familiar with spreadsheets, that’s great! If spreadsheets are new to you, don’t worry—they are just like the table you created in a previous activity.

To get familiar with spreadsheets, you should consider a spreadsheet’s format:

  • Each rectangular block is a cell.
  • Each cell is meant for one data point, just like in the table you created previously.

Now, consider the cells that run across the top of the spreadsheet (horizontally) and along the left side of the spreadsheet (vertically):

  • Cells are organized by columns and rows.
  • Each column has a distinct letter, and each row has a distinct number.
  • Each cell has a unique identifier composed of the column letter and row number. This identifier is like the cell’s address.

You also have a chart embedded in the spreadsheet. However, the chart is blank, because it doesn’t have any data yet. Next, you will add data!

Step 3: Add your data

Now, you can add your own data from previous learning logs to the spreadsheet. Notice that cell A1 contains the label “Date”, and cell B1 contains the label “Value”.

This lines up with the same structure you used in the table you created in a previous learning log entry, where you recorded data from your daily life. Just like your table, all the “date” parts of your data points go in the cells in column A, and the values you recorded on those dates go in the corresponding cells in column B. It should display like this:

10/19 - 2.5 10/20 - 2 10/21 - 1 10/22 - 1.5 10/23 - 1.5

Next, take all of the data that you previously recorded in your learning log and use it to populate the spreadsheet in the appropriate columns: Add the dates in column A, and the values in column B.

Displaying a chart in a spreadsheet

Next, you will update the chart in your spreadsheet based on the data you entered.

Step 4: Reviewing your chart

Now that you’ve entered your data into the Date and Value columns, your spreadsheet should display like this:

Did you notice what happened to the chart when you entered the data in the spreadsheet? The spreadsheet visualized the data for you by making it into a basic bar chart. There are many different ways to customize charts, but first you need to clean up your chart. Then, you can interpret the data visualization!

Step 5: Cleaning up your chart

Now that you have a chart, it’s time to clean it up. It’s generally a good idea to tidy your chart by making it descriptive and aesthetically pleasing. Keep in mind that good data analysts are data storytellers!

To change the title of your chart, double click on the chart. You should find that a “Chart Editor” menu displays on the side of your screen. Click on “Chart & axis titles”, and then enter the name of your chart in the “Title Text” box:

If you’re using Microsoft Excel, you can double click on the title in the chart to edit directly.

Don’t be afraid to play around with the options here: You can always download another copy of the template if you make a mistake you can’t fix!

Confirmation and reflection

Question 1

After you added data to the spreadsheet, you cleaned up the chart. What step did you take to make the chart descriptive?

  • Adding a title to the chart

  • Changing the color of the chart

  • Converting the bar chart to a pie chart

  • Removing the dates from the chart

Correct. To make your chart descriptive, you used the Chart Editor' menu and updated the chart title. Going forward, if you continue to collect data, you can use this spreadsheet to do so! The spreadsheet is a natural choice for storing your data, and practice with professional tools will help you develop your skills and continue to refine your data analyst’s toolkit.

Question 2

You have come a long way! You started recording your data manually, you loaded that data into a spreadsheet, and you visualized the data in a chart. Now, take a moment to examine your completed data visualization. In the text box below, write a 2-3 sentence (40-60 words) response to each of the following questions:

  • How did you use a spreadsheet to help prepare your data?
  • How did you format a chart to help you analyze your data?

A good response would include that spreadsheets help you organize data and charts make your data easier to understand. Beyond that, consider the following: A spreadsheet helps you structure data in rows and columns, prepare data for analysis, and create custom data visualizations. To better analyze your data, you clean up your chart to make it more visually appealing and to clarify what data means by making your chart more descriptive. To do that, it’s important to add chart titles and axis titles. Ultimately, this is an essential skill to master because clear, descriptive data visualizations help data analysts be great storytellers.

More spreadsheet resources

In the spirit of lifelong learning, it is good to have resources to turn to when you want to know more about using spreadsheets. Two of the most well known and used spreadsheet platforms are Google Sheets and Microsoft Excel. Both provide free online training resources that you can access anytime you need them. Bookmark these links if you want to access them later.

Google Sheets Training and Help

Learn even more ways to move, store, and analyze your data with the Google Sheets Training and Help page, located in the Google Workspace Learning Center.This hub offers an expanded list of tips, from beginner to advanced, along with cheat sheets, templates, guides, and tutorials.

Google Sheets Cheat Sheet

Want to learn more about Google Sheets? This online help article features a short list of the most important functions you will use, including rows, columns, cells, and functions.

Microsoft Excel for Windows Training

Get to know Excel spreadsheets a little better by visiting this free online training center. Offering everything from a quick-start guide and introduction to tutorials and templates, you will find everything you need to know, all in one place.

Test your knowledge on spreadsheet basics

Question 1

In a spreadsheet, what is text wrapping used for?

  • To allow text to overflow into an adjacent cell

  • To automatically change cell height in order to allow all of the text to fit inside

  • To clip text within a cell so it doesn’t overflow into an adjacent cell

  • To remove text that is too long to fit in a cell

Correct. In a spreadsheet, text wrapping is used to automatically change cell height in order to allow all of the text to fit inside.

Question 2

The columns in a spreadsheet are ordered by letter, and the rows are ordered by number.

  • True

Correct. In a spreadsheet, columns are ordered by letter and rows are ordered by number.

Question 3

Fill in the blank: In a dataset, a row is called an observation. An observation includes all of the _____ for something contained in the row.

  • names

  • attributes

  • diagnostics

  • commonalities

Correct. In a dataset, a row is called an observation. An observation includes all of the attributes for something contained in the row. An attribute is a quality or characteristic of data.