A Quick and Easy Guide to Spreadsheets
A spreadsheet is a software program you use to easily perform mathematical calculations on statistical data and totaling long columns of numbers or determining percentages and averages.
And if any of the raw numbers you put into your spreadsheet should change – like if you obtain final figures to substitute for preliminary ones for example – the spreadsheet will update all the calculations you’ve performed based on the new numbers.
You also can use a spreadsheet to generate data visualizations like charts to display the statistical information you’ve compiled on a website.
This tutorial will focus on the use of the free application Google Spreadsheets. To use Google Spreadsheets, you will need to sign up for a free Google account. There are other spreadsheet software you can purchase, like Microsoft Excel. While this tutorial will focus primarily on Google Spreadsheet, most of its lessons will be applicable to any spreadsheet software, including Excel.Spreadsheet Layout
To create a new spreadsheet in Google Spreadsheet, sign into your Google Drive account. Then click on the New button on the top left and select Google Sheets.
On your screen will appear a basic spreadsheet, divided into numbered rows and lettered columns.
The rows and columns intersect to create small boxes, which are called cells.
Each cell is identified by its column letter and row number.
Thus the very first cell in the upper left-hand corner is called A1.
Just below A1 is A2. Just to the right of A1 is B1. Just below B1 is B2, and so on.
In the image below, for example, cell D9 is highlighted.
You can select some settings to change the view of the spreadsheet or display toolbars you frequently use, such as the one for entering formulas to make calculations.
To do this, in the menu at the top click on View and make sure there’s a check mark next to Show Formula Bar (to display a box to enter formulas).Entering Information in a Cell
You enter information into a spreadsheet program by typing it into each of the cells.
You can enter three different types of information into a cell:Numbers – so you then can perform mathematical calculations on them.Text – to identify what the numbers in the columns and rows represent, usually by typing headings across the top of the columns or on the left edge of the rowsFormulas – to perform calculations on the numbers in a column or a row of cells.
To enter information into a cell, simply click on the cell and type in the information.
When you’re done, you can either press the enter/return key, which will take you down to the next cell, or the tab key, which will advanced to the cell to the right.
Each time you type information into a cell, you’ll notice the information also appears in the Formula bar, the box just above the columns and rows.
For example, if you click on cell:
You’ll see the number 100 displayed in the formula bar above.
To enter text headings for the various columns and rows to identify them, follow the same procedure as you would with entering numbers. Click on the cell, type in the name of a heading and press the enter/return key.
You can also “freeze” this header row, so it stays in the same place, even if you scroll down a long spreadsheet. To do this, grab the small bar in the corner of the spreadsheet area, and drag it down one row.
Importing Data Into a Spreadsheet
Many government agencies and private organizations provide data on their websites in a spreadsheet or other format that you can download onto your computer.
To import a spreadsheet, .csv or other file you’ve downloaded on your computer into a Google Spreadsheets, first create a new spreadsheet in Google Docs. Then in the menu at the top click on File … Import and then Browse and select the downloaded file.
Let’s download some data to demonstrate how to import it into a Google Docs spreadsheet, and also to give us some sample data to use to show how to do calculations and use other features of a spreadsheet.
The FBI compiles national crime statistics, including data on the types of weapons used in homicides.
This data is in an Excel spreadsheet (.xls) file that can be downloaded from the FBI website and then imported into a Google Doc spreadsheet.
To download the file go to this FBI web page:
Expanded Homicide Data Table 8 (2010-2014)
Click on the link at the top for:
The file will be downloaded onto your computer.
(if for some reason you have trouble downloading this file, you can click here to download the file from our website)
To import the file into a Google Docs spreadsheet, create a new spreadsheet and in the menu at the top click on:
Click on the Browse button and navigate to the downloaded FBI file which is named expanded_homicide_data_table_8_murder_victims_by_weapon_2010-2014.xls. Google Spreadsheet also allows you to import data from your Google Drive. It may give you an option to replace existing data, or to create a new sheet. Choose the best option for your situation.
After a few seconds you should see a Google Docs spreadsheet that looks like this:
This spreadsheet shows the number of murder victims in each year from 2004 to 2008 in five columns, with the columns labeled by year in cells B4 to F4.
Below that the spreadsheet shows the weapon used in the murders in 18 rows of data, with the rows labeled by type of weapon in cells A5 (which is the overall total for all weapons) to A22.Resizing Columns or Rows
You can improve the display of the data in a spreadsheet by increasing or decreasing the width of a column or the height of a row.
To change a column’s width, in the gray bar at the top of the spreadsheet where the letters of the columns are displayed, move your mouse cursor to the border between any two columns.
Note for Excel: if you narrow the width of a column displaying a number too much, you will see a series of pound signs displayed in the cell:
This doesn’t mean you’ve lost any data – you just made the column width too narrow to fit some of the numbers in the cells in that column.
You can also speed up the resizing of columns and avoid making them too narrow by moving your mouse cursor to the border separating two columns in the gray bar at the top and double-clicking on the border. This will automatically resize the column to the left, making it just wide enough to fit the longest entry on any row in that column.Deleting or Adding Columns or Rows
You can get rid of unwanted data or other information by deleting rows or columns.
For example, in our sample spreadsheet of weapons used in homicides, we might want to get rid of row 23, which is just a footnote stating that one murder in which the victim was pushed to his/her death has been included in the “Personal weapons” listing in row 14.
To delete a row, hover your mouse cursor over a row number in the gray area to the left, in this case row 23. Right click and in the pop-up menu select Delete row.
Use the same procedure for deleting a column.
Hover your mouse cursor over a column letter in the gray area at the top, right click and in the pop-up menu select Delete column (you also can click on the tiny downward-pointing arrow to get this pop-up menu).
If you want to add a column or row, again hover your mouse cursor over the appropriate column or row in the gray area above or to the left, right click and in the pop-up menu select one of the Insert options.Learn to work with data in a spreadsheet and to create engaging charts, maps and graphs in the Berkeley Advanced Media Institute Data Visualization for Storytellers Workshop.Formulas – Adding, Subtracting, Multiplying and Dividing
With a spreadsheet you can insert a formula that will instantly add, subtract, multiply or divide numbers in columns or rows.
To do this you select a cell in a new column or row and then type in a formula.
A formula starts with an equals sign (=) that tells the spreadsheet you want to do a calculation.
A formula then has a symbol for what kind of calculation you want to perform (add, subtract, multiply, divide, etc.). The symbols a spreadsheet uses for calculations are:plus sign (+) for adding one number to anotherminus sign (-) for subtracting one number from anotherasterisk (*) for multiplying one number by anotherbackslash (/) for dividing one number by another
Then you type in the letters/numbers for the cells (A1, A2, B1, B2, etc.) to which you want to apply the calculation, separated by the symbol for the type of calculation.
Adding Numbers in Columns
Let’s write a formula for adding together a series of numbers.