Have you ever encountered an interesting dataset as you browse the web, and wanted to analyze it further in a spreadsheet? Perhaps you want to include this data into a custom report or use it to drive calculations in your spreadsheet model, but you weren’t sure how to make a table in google sheets? If so, you have come to the right place. In this article, I will walk you through a couple of approaches to quickly take a dataset found on the Internet and teach you how to make a table in google sheets to convert the data.
When learning how to make a table in google sheets, it is important to learn how to efficiently format the data after you have successfully loaded it into your table as well. While figuring out how to make a table in google sheets, it is important to focus on 5 basic steps when learning how to format your table.
- Enter Raw data
The first step in learning how to make a table in google sheets is learning how to enter the raw data into the table. Below I will discuss two efficient methods of data entry.
We will explore two public datasets:
- List of ISO country codes: [all.csv]
- Covid-19 dataset published by Our World In Data available here: [owid-covid-data.csv]
Method 1 — Import Directly Into Sheets (for small files)
The first step is to download `all.csv` and `owid-covid-data.csv` to your computer using the links above.
Google sheets has a built-in feature to import common file formats. Choose File → Import, navigate to your Downloads folder and choose `all.csv`.
While the above method works well for relatively small files, you run into issues if you try it on a larger file. In this case, `owid-covid-data.csv` is approximately 35 MB in size and has about 127,000 rows.
Method 2 — Create Table Using Dataprep and BigQuery (for large files)
To avoid the above issue, you can load the data into BigQuery using Dataprep, then use the BigQuery connector for Google Sheets to bring the data in.
The following video demonstrates how to accomplish that and how to make a table in google sheets. The high-level steps are the following:
- Create a Dataprep flow and upload the CSV file
- Create a recipe to filter and summarize the data (optional)
- Define the output as a BigQuery table
- Run a job to populate the BigQuery table
- Use the BigQuery connector to pull the results into Google Sheets
- Format the Header
Formatting your headers is important in learning how to make a table in google sheets. It allows your table to be organized and to see what each column of your table represents so it is easy to analyze. The best way to do this is to make the headers bold and centered.
- Aligning the Columns
Aligning your columns is the next step in organizing your table. It is a very basic step in learning how to make a table in google sheets. The best way to do this is to center column headings, ID numbers, or other standard entries. It is important to left-align text and to align dates or numbers to the center or right. Another important thing to do when learning how to make a table in google sheets is to make sure that each column is the correct width depending on if you want all of the data to be seen.
- Designing the Table
Design is vital because it makes your table look professional and neat. The easiest way to do this is to use alternating colors within your table. To do this, highlight all of your data and then click Format along the top ribbon. After this step click the option that says alternating colors. A screen will then pop up that gives you color options and design options where you can choose how you want your table to look.
- Add a Border and Finalize
When you are getting ready to finish your table, putting borders on your table makes it look complete. To put a border on each cell in your table, click the Border button and then click All borders.
In conclusion, it is possible to learn how to make a table in Google Sheets from many common file formats. Depending on the size of the file, you can perform a direct import, or use Dataprep and BigQuery to create a data pipeline into Sheets. After creating your table and entering your data, it is also easy to format and design your table to make it easy to read and analyze using 5 basic steps. Create an account and learn more tips and tricks on how to create and design tables in google sheets. Get started today!