# A Quick and Easy Guide to Spreadsheets

A spreadsheet is a software course of study you use to easily perform numerical calculations on statistical data and totaling farseeing column of numbers or determining percentages and averages .
And if any of the bare-assed numbers you put into your spreadsheet should change – like if you obtain final figures to substitute for preliminary ones for case – the spreadsheet will update all the calculations you ’ ve performed based on the newfangled numbers .
You besides can use a spreadsheet to generate data visualizations like charts to display the statistical information you ’ ve compiled on a web site.

On your riddle will appear a basic spreadsheet, divided into number rows and letter column .

The rows and columns intersect to create little boxes, which are called cells .
Each cell is identified by its column letter and row number.
thus the very inaugural cell in the upper berth left corner is called A1 .
just below A1 is A2. Just to the properly of A1 is B1. Just below B1 is B2, and therefore on .
In the image below, for example, cellular telephone D9 is highlighted .

## Setting the View Options

You can select some settings to change the position 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 chink on View and make indisputable there ’ second a check mark next to Show Formula Bar ( to display a box to enter formulas ) .

## Entering Information in a Cell

You enter data into a spreadsheet plan by typing it into each of the cells .
You can enter three unlike 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 rows
• Formulas – to perform calculations on the numbers in a column or a row of cells.

To enter information into a cell, plainly 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 cellular telephone to the veracious .
Each meter you type information into a cell, you ’ ll notice the data besides appears in the Formula barricade, the box just above the column and rows .
For case, if you click on cell :
B3
And type in the count :
100
You ’ ll see the number 100 displayed in the recipe banish above .

To enter text headings for the diverse columns and rows to identify them, follow the lapp routine as you would with entering numbers. Click on the cell, type in the name of a head and press the enter/return key .
You can besides “ freeze ” this heading course, so it stays in the same invest, 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 politics agencies and secret organizations provide data on their websites in a spreadsheet or other format that you can download onto your computer .

## Importing Sample Data

Let ’ s download some data to demonstrate how to import it into a Google Docs spreadsheet, and besides 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 datum is in an Excel spreadsheet ( .xls ) file that can be downloaded from the FBI web site and then imported into a Google Doc spreadsheet .
Expanded Homicide Data Table 8 ( 2010-2014 )

Click on the link at the top for :
To import the file into a Google Docs spreadsheet, create a new spreadsheet and in the menu at the acme snap on :

## File…Import

Click on the Browse button and navigate to the download FBI file which is named expanded_homicide_data_table_8_murder_victims_by_weapon_2010-2014.xls. Google Spreadsheet besides allows you to import data from your Google Drive. It may give you an option to replace existing data, or to create a modern 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 mangle victims in each class from 2004 to 2008 in five columns, with the column 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 altitude of a rowing .
To change a column ’ south width, in the grey browning automatic rifle at the top of the spreadsheet where the letters of the column are displayed, move your mouse cursor to the frame between any two columns .

Note for Excel: if you narrow the width of a column displaying a phone number besides much, you will see a series of lumber signs displayed in the cell :
###
This doesn ’ deoxythymidine monophosphate mean you ’ ve lost any data – you just made the column width besides narrow to fit some of the numbers in the cells in that column .
You can besides speed up the resize of column and avoid making them besides narrow-minded by moving your shiner cursor to the molding separating two column in the grey cake at the top and double-clicking on the bound. This will automatically resize the column to the leave, making it just wide adequate to fit the longest entrance on any row in that column .

## Deleting or Adding Columns or Rows

You can get rid of undesirable data or other information by deleting rows or column .
For model, in our sample spreadsheet of weapons used in homicides, we might want to get rid of course 23, which is fair 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 shiner cursor over a quarrel number in the grey area to the bequeath, in this event row 23. Right click and in the pop fly menu choice Delete row .
Use the lapp procedure for deleting a column .
Hover your sneak cursor over a column letter in the grey sphere at the top, justly click and in the pop-up book menu choose Delete column ( you besides can click on the bantam downward-pointing arrow to get this pop-up book menu ) .
If you want to add a column or course, again hover your shiner cursor over the allow column or row in the grey area above or to the leave, right chink and in the pop fly menu select one of the Insert options .

### Formulas – Adding, Subtracting, Multiplying and Dividing

With a spreadsheet you can insert a convention that will immediately add, subtract, multiply or divide numbers in column or rows .
To do this you select a cell in a new column or quarrel and then type in a convention .
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 another
• minus sign (-) for subtracting one number from another
• asterisk (*) for multiplying one number by another
• backslash (/) 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 .

Let ’ s write a convention for adding together a series of numbers .
In the spreadsheet for types of weapons used in murders that we downloaded from the FBI web site, the spreadsheet already included the total number of homicides in which any kind of firearm was used each year from 2004 to 2008. Those numbers are in row 6.
But what if these totals hadn ’ metric ton been included in the original data and you needed to calculate them yourself using the spreadsheet ( or if you wanted to use the spreadsheet to double-check the FBI ’ s calculations ) .
This would require totaling up for each class the column of numbers for the five weapon types in the spreadsheet :

• Handguns – row 7
• Rifles – row 8
• Shotguns – row 9
• Other guns – row 10
• Firearms, type not stated – row 11

To do this we need to insert a formula for adding a series of numbers in a column .
Let ’ s start by doing this for the year 2004. Click on cell :
B23
Which is in the column that shows the numbers for weapons used in 2004 .
In that cell, type :
=B7+B8+B9+B10+B11
( bill : the letters are not case sensible. therefore for exemplar so you could type in either B7 or b7 )
This tells the spreadsheet to add up the number of murders committed with handguns ( B7 ), rifles ( B8 ), shotguns ( B9 ), early guns ( B10 ), and firearms, type not stated ( B11 ) for the year 2004 .

You should type cell letters/numbers into a convention quite than the actual numbers .
That way if the numbers always change ( for exercise, if the FBI released update murder weapon statistics for 2008 ), you won ’ thymine have to re-enter the new numbers in the formula. alternatively you ’ d just type the update numbers into the allow cells and the spreadsheet will apply the existing formula to the new numbers in those cells .

## Applying a Formula to Multiple Cells

If we nowadays wanted to calculate the total number of gun related homicides for the other four years, we could repeat the serve of typing an addition formula into each cell in the remainder of quarrel 23. But a spreadsheet has a much faster direction of accomplishing this – by letting you merely copy the formula to one or more of the early cells in the same row .
To do this, cluck on cell :
B23
Where we typed in our addition formula
=B7+B8+B9+B10+B11
Pass your mouse cursor over the bottom right corner of cell B23 and notice your cursor changes from an arrow cursor to a thin crosshairs .

Click on that crosshairs, hold down your mouse button and drag your mouse to the right over the rest of the cells in row 23 .

An outline will appear around the cells you ’ ve selected .
Continue dragging your sneak until you get to cell :
F23
Release your mouse push button and the sum number of homicides involving firearms for each class from 2010 to 2014 will appear in course 23 .

Which again confirms the totals in the original FBI spreadsheet in quarrel 6 .
The spreadsheet has calculated these totals for you by applying the formula you first typed in cell B23 to the perch of the cells in course 23 .
The spreadsheet keeps the formula ( addition ) the same, but shifts the cell numbers as it applies the recipe to the early cells to the right ( so the recipe in cell C23 is =C7+C8+C9+C10+C11, the recipe in cell D23 is =D7+D8+D9+D10+D11, and so on ) .

## Editing a Formula

When you type a convention into a cell and then hit the enter/return key, the convention will disappear, replaced by a number that ’ s the result of the calculation .
So how can you edit the formula ?
There are two ways :
You can double click on the cell to display the formula in the cell and then edit or retype it there .
Or you can click once on a cell and use the Formula bar above to edit it .
If you click once on a cellular telephone that has a rule hidden in it ( replaced by a number that ’ s the resultant role of the calculation ), the convention you primitively typed will appear in the Formula bar above the column and rows .

To edit the formula you can click in the formula legal profession where the formula for this cell is displayed. then change the existing formula or type a new one into the Formula bar, press the enter/return key and the fresh rule will be applied and the numbers will be recalculated in the cellular telephone .

## Understanding Cell Formats

Cells can display their data in many unlike ways. For case, you can format a cellular telephone to display data as currentness, as a date, scientific notation, or several other formats. You can adjust this by highlighting a cell, and changing its format under the Format -> Number  menu .

This can sometimes be counter-intuitive because the cell can appear differently than the data that’s actually in the cell. For model, in the shell of currency format, the cell data could have several decimal places. But when formatting for currentness, a dollar symbol will display and the cell will only show the hundredth set ( 2 decimal points ), even if the actual data in the cell has is more demand and has more decimal points .

The way to understand what the actual data is in a cell is to look at the formula bar. This will sometimes show you the natural datum. The cell format is generally used to make thing more human-readable. But sometimes this can be the cause of alarm, specially when using formula. This could particularly be catchy when using dates .

## Percent Changes and Multiplying and Dividing

This following section will describe how to calculate a percent change between two numbers. A percentage change is calculated by finding the dispute between the two numbers, and comparing that dispute by the beginning phone number .
In our spreadsheet on murder weapons, we can calculate how much each weapon increased or decreased between 2010 to 2014 .
First click on cell G5 to the correct of our existing data .
type in the succeed formula :
+ ( F5-B5 ) /B5

nowadays let ’ s do the percentage calculation, starting with the percentage change in the full count of homicides ( row 5 ) .
First chatter on cellular telephone H5 to the good .
And type in the follow formula :
=(F5-B5)/B5*100

This is the formula for calculating the percentage change between two numbers .
This formula tells the spreadsheet to find the remainder of homicides by subtracting the total homicides in 2014 from 2010. After that, divides the results to the original prize .
The backslash ( / ) is the symbol for dividing, while the asterisk ( * ) is the symbol for multiplying .
( note : The parentheses in this formula are besides important to define the compensate regulate of operations. )
nowadays hit the enter/return key to see the final solution of the percentage formula in cell G5 :
-0.09138559708
The full phone number of homicides by all types of weapons declined by 9.1 percentage from 2010 to 2014. But to make it into a more human-readable format, we can change the data format of the cell to a percentage .

now it will display as :
-9.14%

### Apply to the rest of the cells

nowadays let ’ s apply this percentage switch rule to the rest of the murder-by-weapon numbers. Click on cell :
G5
Pass your mouse over the bottom right corner of the cell until the cursor changes to thin crosshairs .
Click and drag the mouse cursor down over the rest of the cells in the H column. Release your mouse button when you get to cell :
G22

The percentage changes for all the unlike types of weapons used in homicides will appear on your riddle .

## Parentheses in a Formula

In the recipe for percentage variety we used in the previous section, parentheses ( ) were included in the recipe :
=(F5-B5)/B5
The parentheses in this formula are identical crucial. These tell the spreadsheet to subtract the number of homicides in 2010 ( B5 ) from the number of homicides in 2014 ( F5 ) first, and then divide that amount by the count of homicides in 2010 ( B5 ) .
If you didn ’ thyroxine include the parentheses and had just typed in =F5-B5/B5, the spreadsheet first would divide B5 by B5 ( yielding 1 ). Finally it would subtract the resultant role from F5, resulting in an incorrect number .
therefore if you are doing a calculation involving several steps, it is authoritative to include parentheses so you can group the numbers properly and the spreadsheet therefore knows the order in which to do the calculations .

## Using Formulas with a Fixed Cell

Another have you can do with a spreadsheet is building a formula with a fixed cell, so that when you drag your recipe to apply them to other cells, it doesn ’ deoxythymidine monophosphate mechanically switch its reference to a newfangled cell .
In our spreadsheet, for example, we might want to know what share of homicides involved each different type of weapon compared to a specific year. We would compare each cell to the total number of homicides for alone that year, so we don ’ deoxythymidine monophosphate want the reference to that year ’ second sum to change .
Let ’ s beginning with 2014. To create our percentage formula snap on cellular telephone :
H6
And type in this rule :
=F6/F5

This formula tells the spreadsheet to divide the number of homicides involving firearms in 2010 ( F6 ) by the total number of homicides that year ( F5 ) .
Press the enter/return key and swith the cell format to percentage. You ’ ll see the total is :
67.92%
thus firearm refer homicides were about two thirds of the full number of homicides in 2014. Good… so far .
But, you might then try to apply this like formula to the cells for the other types of weapons by dragging the crosshairs, as we did in the former exemplar. But if you tried this, it would produce bizarre numbers in the G column, including that some weapons-related homicides are more than 100 % of the total .

What went wrong ?
The trouble is that when the spreadsheet copies a formula using this method, it shifts the letters for both cells in the original recipe ( F6 and F5 ) as it applies that formula to other cells ( resulting in F7 divided by F6 in the next cell down ) .
To fix this, we need to force the spreadsheet to constantly divide the numbers for each type of weapon used by a constant number – the entire number of homicides in cell F5. This is called anchoring the cell in our formula, and force the spreadsheet always to use one cell each time .
You accomplish this by adding some `\$` signs to the formula that instruct the spreadsheet not to change cell F5 when applying the formula to other cells .
therefore go back and click on cell :
F6
Delete that formula ( press edit key ), and rather type in this :
=F6/\$F\$5

The dollar signs tell Excel to always keep anchored on cellular telephone F5 and the datum in it when applying this formula to early cells .
now we can drag the formula down through the column of cells and get the correct results .
therefore levitate your mouse over cell :
F6
then pawl on the crosshairs in the penetrate mighty corner of the cell and drag down to cellular telephone :
F22

The chastise share number for each weapon type will nowadays appear in the spreadsheet .

## Adding Numbers Using the SUM Formula

If you want to add a large group of numbers in a row or column, there ’ s another manner to do that quickly in a spreadsheet by using the SUM formula .
For exemplar, in our example spreadsheet on weapons used in homicides, what if you wanted to know the total act of homicides in which did not include a firearm ?
To calculate that, you could add up the numbers in rows 12 to 21 for each year using the SUM formula
( note : row 22 – “ other weapons or weapons not stated ” – may or may not involve a non-firearm-related homicide, so we ’ rhenium leaving that out of this calculation )
To use the SUM rule to calculate the count of non-firearm-related homicides in rows 12 to 21, first suction stop on cell :
B23
In that cell type this formula

``````= summarize( B12: B21)
``````

You ’ ll see there were 3,418 non-firearm-related homicides in 2014. In our convention, `=SUM()` is shorthand for telling a spreadsheet to add up a series of numbers .
After typing =SUM, you type a fixed of parentheses, and inside the digression you will include something called a range .
A range has two cellular telephone references separated by a colon. `B12:B21`. Ranges can even span multiple row or multiple columns, and can be used in numerous formulas .

#### Adding selected cells with the SUM formula instead of a range

You besides can add up choice numbers in a column, preferably than a straddle of them, using the SUM convention .
To do that, in the SUM rule you replace the colon with commas to separate the specific cells you want to total up .
therefore if you wanted to total up only the total of homicides in 2014 in which either poison ( cell B15 ) or narcotics ( cell B18 ) was involved, you would type this convention .

``````= sum( B15, B18)
``````

## Shortcuts to Writing Formulas

There are a number of shortcuts for writing formulas in a spreadsheet .
To illustrate these, in our spreadsheet on types of weapons used in homicides, let ’ s add up the total number of firearm-related homicides from 2010 to 2014. This would mean adding cells B6 through F6. We could manually type in the `=SUM(B6:F6)` convention, but there is a more user-friendly creature for doing this without having to remember formulas .
To do this, first click on cellular telephone :
I6
then use the spreadsheet ’ south Formulas tool that will shorten what you have to type .

Click on it and you ’ ll see a series of formulas you can select to insert into your spreadsheet .

In this case pick SUM and the recipe =SUM() will be inserted into cellular telephone G6 .
now you can click the cells you want to be referenced, and they will be auto-populated into the convention. You can click-and-drag to specify a range, or chink and hold down the chemise key and snap another cell. To specify specific cells to add without making it a range, you should hold down the instruction winder ( Mac ) or Control key ( personal computer ) and click all the cells you want .

## Averaging Numbers

Another park calculation is averaging a series of numbers .
In our spreadsheet on the types of weapons used in homicides, for example, what if we wanted to know the average phone number of firearm-related homicides each year between 2010 and 2014 ( cells B6 to F6 ) .
To do this, pawl on cellular telephone :
J6
And in that cell type :
=AVERAGE(B6:F6)

This same process can be used to besides calculate the `MEDIAN()`, `MODE()`, `STDEV()` ( standard deviation ) and other statistical functions for a series of data points .

## Using Functions to Import Website Data

One advantage to Google spreadsheets is that it is designed to work with the Web. particular functions allow you to load data dynamically directly from a web site .

### Import a data file published on the Web into your spreadsheet

CSV files ( comma separated values ) can be imported directly into a spreadsheet from anywhere on the Web. CSV is one of the most coarse data formats and can be found with a childlike Google search .
For sample data, we will use a patch of crime data from UC Berkeley in 2015 hosted on Github. The url is `https://raw.githubusercontent.com/jrue/ucpd-crime/master/data/ucpd/ucpd_data_6.csv` .
Let ’ s import this data into a newly sheet. Click the small plus button at the bottom of our workbook document :

Click in cell A1 and type ( or copy-and-paste ) the watch :

``````=ImportData( `` hypertext transfer protocol : //raw.githubusercontent.com/jrue/ucpd-crime/master/data/ucpd/ucpd_data_6.csv '')
``````

After a moment the data will load and should look like this :

many files will not be this cleanse and may require cleaning. But if you can use the file as is, it ’ s specially useful. Governments regularly update CSV files on their servers. This may happen frequently with certain files such as election results .

## Adjusting Data Display by Changing Cell Formats

In the former exemplar, you might have noticed the date and fourth dimension columns display these strange numbers which should be dates and times of each crime. Raw cell data for a fourth dimension value is the number of days since Jan 1, 1900 ( and may even be different when using Microsoft Excel ) .

We can easily adjust this by changing the cell format. Click on the column ’ randomness heading, then under the Format menu, blue-ribbon Date for the foremost column, and a Time for the second column .

## Import a table or list directly from a web page

Tables can frequently be imported directly from a Web page into a spreadsheet. Let ’ s consequence the lapp data from the Wikipedia ’ south page on Gun Violence by State .
note : This model will tie into the following section on charts, so we use it for appliance. however, we do not advocate using data from Wikipedia in any production common sense. Always vet and corroborate data directly from the source when used in journalism .
Open a new sheet and pawl in cellular telephone A1. type :

``````=IMPORTHTML( `` hypertext transfer protocol : //en.wikipedia.org/wiki/Gun_violence_in_the_United_States_by_state '',  `` board '',  1)
``````

The first gear parameter is the web page Google will scan ( make sure it ’ randomness in quotes ). The second parameter is the HTML element it ’ randomness looking for. In our case, we want it to find a

element. The third parameter is which table element we should find, in case there are multiple. You may need to change the third parameter through trial-and-error, or look at the source code of the webpage you’re scrapping.

Hit record and the spreadsheet should look like this :

The “table” parameter can be replaced with “list” so that it will look for the contents of

and

tags.

Live data from Google finance can be imported into your spreadsheet. The datum updates automatically every prison term the spreadsheet is loaded. Quotes can have up to a 20 moment stay, which is coarse for fiscal data .
Create a new spreadsheet that looks like this :

Type `=GoogleFinance(".DJI", "price")` in cell B2
Type `=GoogleFinance(".INX", "price")` in cell B3
Type `=GoogleFinance(".IXIC", "price")` in cell B4
The initials at the begin of the parentheses are stock ticker symbols. You can find the symbol for any stock at Google Finance .
The cells should update in a few moments and your spreadsheet should look like this :

The same function can be used to load historic data. Let ’ s pull in the daily close price of Google stock for 2009 .
In cell A1, type :

``````=GoogleFinance( `` GOOG '',  `` close '',  `` 01/01/2009 '',  `` 12/31/2009 '',  `` daily '')
``````

The wax documentation on all of the unlike parameters for the GoogleFinance function are listed on Google ’ s help pages .

## Sorting Results

After you ’ ve enroll numbers or done calculations in a spreadsheet, you may want to sort the results from highest to lowest or lowest to highest .
With the spreadsheet on types of weapons used in homicides, for model, you could more easily see which weapons are most frequently used by ranking them from the highest number to the lowest number for any given year .
To do this, you inaugural need to highlight the area of the spreadsheet that you want to sort .
Don’t just highlight a whole column of numbers to sort because the spreadsheet then will sort only the cells in that column and not change the order of the represent cells in other column ( such as the headings that tell you which type of weapon corresponds with the numbers of homicides ) .

The highlight area nowadays includes the headings for the types of weapons used and then the numbers for each character of weapon for each year .
To sort the datum, in the menu at the top, click on Data … Sort Range

In the box that appears, you ’ ll see the image of selected cells displayed at the top ( in this case, cells A5 to F22 ) .

You immediately can select the column by which you want to sort the data .
You besides can select whether to sort that data in ascending ordering ( A – Z ) so the smallest issue appears at the top of the sorted data, or descending holy order ( Z – A ) so the largest issue appears at the top .

## Formatting Cells

A spreadsheet provides a lot of options for re-formatting the information being displayed. These are exchangeable to the options in a bible serve course of study like Microsoft Word or many other applications. They include :

• Changing the font size or style
• Defining the format for the kind of data in a cell, such as dates, times, currency or percents
• Changing the number of decimal places displayed in a number
• Changing the text color or the background color
• Adding borders around the cells

Some of these options are available by selecting Format in the menu at the clear and then picking one of the choices in the drop-down menu .
Or you can click on the icons in the middle of the toolbar for early options .