Google Sheets: Getting Started with Creating Spreadsheets & Formatting Data
The human brain can easily process and memorize images, make strong connections between concepts and words, and remember trivial, everyday things. However, we are much worse in understanding and memorizing accurate, firm data sets. We better leave that kind of stuff for the computers to store and figure out.
Data Tables or Spreadsheets were invented to take advantage of software computing capabilities and organize information for us, so we don’t need to scratch our head to recall them. When you use a Spreadsheet, it helps you in sorting and labeling your data in a way that makes sense, making it easier to perform calculations on them later.
VisiCalc is the first digital spreadsheet ever, and when it was released back in the late ‘70s, it completely changed the way information is stored. There was no need to physically calculate values, write data and store piles of paper in desks anymore. As the tools evolved, they started creating programs like Excel (the most popular of them all in the last 30 years), and digital spreadsheets became popular worldwide.
The only problem with them was that it was difficult to share them with anyone because the spreadsheets were tied to a single machine. Not to mention that whenever you have accidentally erased it or a computer failure occurred, the data was gone forever.
In 2006, Google finally brought spreadsheets online as a part of Google Docs suite. With Google Sheets, you can work together with your friends and colleagues on the same spreadsheet online. The appearance and functionality are roughly the same as with any other spreadsheet tool, but now it’s functioning as an online app, so it opened the door to new opportunities. Just to mention a few key improvements:
- The spreadsheet is web-based, which means that you can use it anywhere, so you don’t need to remember where you put them
- You can access your sheets from any iOS or Android-based device through the application
- Google Sheets, Drive, Docs and Slides are free and you can share presentations, files and documents with them online
- The most popular spreadsheet options are available, so if you know how to use Excel, you will definitely get along with Google Sheets
- You can download add-ons, create new ones or write custom code
- Since it’s online, you can set your spreadsheet to gather data automatically while you do something else
Creating a Spreadsheet and Filling It With Data
Google Sheets is free and works on any device, all you need is a web browser (or the app version on your Android or iOS) and your Gmail account to start it. Just head over to sheets.google.com and you can follow along with the tutorial right away.
You can create a new spreadsheet from the Google Drive dashboard, the Google Sheets homepage and within a spreadsheet as well. There are even pre-populated spreadsheets available on the Google Sheets homepage, but you should start with a blank one for the sake of the tutorial. If you used a spreadsheet app before, the interface will be quite familiar for you with the usual text editing icons and tabs for additional sheets.
However, there are not as many displayed elements on the interface as with other spreadsheet apps, so Google reduced the clutter a little bit. Now, your first task will be to add some data to your chart.
Add Data to Your Spreadsheet
In the new sheet, there is white-and-grey grid all over your sheet, forming individual squares called cells. Just try clicking on one of them and a blue outline around the selected cell will appear. These are all distinguishable by their letter and column IDs. Actually, if you create a new spreadsheet and start writing right away, your text will populate the very first cell. You don’t need to double-click a cell to start adding information, and there is not much need to use your mouse either.
Once you selected an arbitrary cell, go ahead and start typing. When you are finished, you can press Enter and the data will be instantly saved in the cell, moving the selector to the beginning of the very next row. If you want to move to the right in the same row, just press Tab after typing.
Try using the Arrow Keys on the keyboard to move 1 cell up, down, right and left. This is a pretty fast way to get to certain cells you want to continue with. You can click on them as well, but as you become more experienced, it can easily interrupt you in your workflow.
There is no need to type everything in manually. You can add massive amounts of data just by copying or importing it to your sheet. The Copy & Paste method is the same as with any simple text, but be careful, because you can’t copy just any set of data from a PDF or website into your spreadsheet. It has to be in HTML format; otherwise, the whole data set will appear in one single cell or it spreads out in its original form when you paste it. You can avoid this by simply searching for data that is in a HTML table format (such as any movie data on IMDB).
If you click on a cell before pasting the data, Google Sheets will treat it as a list and puts each item in a separate cell. Whenever you double click a cell, the full text will be pasted into that cell, and you probably don’t want that to happen. In the next section, we will show you how to fix oddly formatted data.
Importing files is quite straightforward as well. You can import directly into your current spreadsheet, create a new one, or replace one of your sheets with the imported content. The most commonly imported files are XLS, XLSX (Microsoft Excel files), and CSV (comma separated values). With the File > Import > Upload option, you can import files that are not on your Google Drive. On the other hand, if the particular Google Sheet is on your account, you can import it directly with the same process by searching the Drive via the import window.
You can always apply a cell value to other cells with an easy dragging method which may come in handy when you start working with formulas. Just click on a cell, and once the small blue dot appears in the corner, click and drag it down or across an array of cells to perform certain functions. This feature can be used in three different ways:
- Copying cell data with formatting into numerous neighboring cells
- Copying the “formula” into neighboring cells (we will cover this advanced feature later)
- Arranging the text data in an ordered list form
For example, to create an ordered list, add the text “member 1” to the first cell (A1), and then, as the cell stays highlighted, click and drag the blue dot in the bottom-right corner across a few neighboring cells in any direction you want. The dragging action will copy the text to the other cells and because you added a number to the text, every next cell will increment that number by +1.
Now practice these skills a little bit and try to put together a healthy spreadsheet by yourself with a good chunk of data. We will discuss how to use this data below in the next part.
Format Your Data for Easy Viewing
Whether you’re tracking sold products, recording competition results or keeping track of customers, your data can be formatted and manipulated in many different ways. You can discover the basic formatting options above your first cell in Google Sheets. Just hover over each icon, and it will immediately display a short description and a shortcut key. Pretty much all of them can be treated like in any other software for document editing. Everything else will be covered in great detail in this chapter.
Data sets are often times too broad or too complex to simply memorize them, but Google Sheets makes it very simple to capture information. You can quickly create a simple list and share it with your friends or use it as a neatly structured note-taking tool. Besides copying and writing texts, there is also an option to copy links into the cells.
However, there are really large data sets and it’s quite painful to skim through them. Not to mention if you have to work with several spreadsheets per day and share them back and forth, it can be really tedious. So it’s time to learn how to make them more user-friendly with a few formatting tools.
At first, there is an option to keep the first row visible at all times, which may come in handy with long lists. Just “Freeze” the first row in its place and you can scroll down as much as you want while keeping track of the annotations for each column. You can freeze a row by clicking View > Freeze > 1 Row in the menu or by dragging the dark grey bar on the top left (it becomes a hand if you hover over it) between the first two row.
This should be the first thing to do whenever you start a new sheet. You can format any text by using the tools right above the first row. Select a text and try them all just to get more familiar with the toolbar. If you want to display item prices, there is an option to add a dollar sign to each of the selected cell values to display them as a price amount instead of a number. Keep in mind that if you highlight a whole row or column while formatting, it will apply to future values as well.
That’s all for this article, but in the next article we’ll continue with using data calculation and data filtering methods.