Definition for update spreadsheet

For example, in the case of currency format, the cell data could have several decimal places. But when formatting for currency, a dollar symbol will display and the cell will only show the hundredths place 2 decimal points , even if the actual data in the cell has is more exact 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 raw data. The cell format is generally used to make thing more human-readable.

But sometimes this can be the cause of consternation, especially when using formulas. This could especially be tricky when using dates. This next section will describe how to calculate a percent change between two numbers. A percent change is calculated by finding the difference between the two numbers, and comparing that difference by the first number. In our spreadsheet on murder weapons, we can calculate how much each weapon increased or decreased between to This formula tells the spreadsheet to find the difference of homicides by subtracting the total homicides in from After that, divides the results to the original value.

Note: The parentheses in this formula are also important to define the correct order of operations. The total number of homicides by all types of weapons declined by 9. But to make it into a more human-readable format, we can change the data format of the cell to a percentage. Click on cell:. 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:. The percent changes for all the different types of weapons used in homicides will appear on your screen. In the formula for percent change we used in the previous section, parentheses were included in the formula:.

The parentheses in this formula are very important. These tell the spreadsheet to subtract the number of homicides in B5 from the number of homicides in F5 first , and then divide that amount by the number of homicides in B5. Finally it would subtract the result from F5, resulting in an incorrect number.

So if you are doing a calculation involving several steps, it is important to include parentheses so you can group the numbers properly and the spreadsheet thus knows the order in which to do the calculations. In our spreadsheet, for example, we might want to know what percentage of homicides involved each different type of weapon compared to a specific year. This formula tells the spreadsheet to divide the number of homicides involving firearms in F6 by the total number of homicides that year F5.

So firearm related homicides were about two thirds of the total number of homicides in Good… so far. But, you might then try to apply this same formula to the cells for the other types of weapons by dragging the crosshairs, as we did in the previous example. The problem is that when the spreadsheet copies a formula using this method, it shifts the letters for both cells in the original formula 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 always divide the numbers for each type of weapon used by a constant number — the total 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. The dollar signs tell Excel to always keep anchored on cell F5 and the data in it when applying this formula to other cells. For example, in our example spreadsheet on weapons used in homicides, what if you wanted to know the total number 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. To use the SUM formula to calculate the number of non-firearm-related homicides in rows 12 to 21, first click on cell:. A range has two cell references separated by a colon. Ranges can even span multiple row or multiple columns, and can be used in numerous formulas.

You also can add up select numbers in a column, rather than a span of them, using the SUM formula. To do that, in the SUM formula you replace the colon with commas to separate the specific cells you want to total up. Thus if you wanted to total up only the number of homicides in in which either poison cell B15 or narcotics cell B18 was involved, you would type this formula.

This would mean adding cells B6 through F6. Now you can click the cells you want to be referenced, and they will be auto-populated into the formula. You can click-and-drag to specify a range, or click and hold down the shift key and click another cell.

To specify specific cells to add without making it a range, you should hold down the command key Mac or Control key PC and click all the cells you want. In our spreadsheet on the types of weapons used in homicides, for example, what if we wanted to know the average number of firearm-related homicides each year between and cells B6 to F6. One advantage to Google spreadsheets is that it is designed to work with the Web.

Specific functions allow you to load data dynamically directly from a website. CSV files comma separated values can be imported directly into a spreadsheet from anywhere on the Web. CSV is one of the most common data formats and can be found with a simple Google search. For sample data, we will use a piece of crime data from UC Berkeley in hosted on Github.

Click the small plus button at the bottom of our workbook document:. Many files will not be this clean and may require cleanup.

Governments regularly update CSV files on their servers. This may happen frequently with certain files such as election results. In the previous example, you might have noticed the date and time columns display these strange numbers which should be dates and times of each crime. Raw cell data for a time value is the number of days since Jan 1, and may even be different when using Microsoft Excel.

We can easily adjust this by changing the cell format. Tables can frequently be imported directly from a Web page into a spreadsheet. Note: This example will tie into the next section on charts, so we use it for convenience. However, we do not advocate using data from Wikipedia in any production sense.

Always vet and corroborate data directly from the source when used in journalism. The third parameter is which table element we should find, in case there are multiple.

Live data from Google finance can be imported into your spreadsheet. The data updates automatically every time the spreadsheet is loaded. Quotes can have up to a 20 minute delay, which is common for financial data. The initials at the beginning of the parentheses are stock ticker symbols.

You can find the symbol for any stock at Google Finance. The same function can be used to load historic data. With the spreadsheet on types of weapons used in homicides, for example, 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 first need to highlight the area of the spreadsheet that you want to sort. The highlighted area now includes the headings for the types of weapons used and then the numbers for each type of weapon for each year. You also can select whether to sort that data in ascending order A — Z so the smallest number appears at the top of the sorted data, or descending order Z — A so the largest number appears at the top.

A spreadsheet provides a lot of options for re-formatting the information being displayed. These are similar to the options in a word processing program like Microsoft Word or many other applications. They include:. Some of these options are available by selecting Format in the menu at the top and then picking one of the choices in the drop-down menu. This tutorial was originally written by Paul Grabowicz for students in his Computer Assisted Reporting class, and later modified by Jeremy Rue for public use.

I first learned how to use the Microsoft Excel spreadsheet program many years ago using that book, which took a step by step approach based on how a journalist uses a spreadsheet.

I tried to use the same approach with this tutorial. This content may not be republished in print or digital form without express written permission from Berkeley Advanced Media Institute. Please see our Content Redistribution Policy.

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. In the image below, for example, cell D9 is highlighted. What makes a spreadsheet software program unique is its ability to calculate values using mathematical formulas and the data in cells.

An example of how a spreadsheet may be utilized is creating an overview of your bank's balance. Below is a basic example of what a Microsoft Excel spreadsheet looks like, with all the important features of a spreadsheet highlighted. In the above example, this spreadsheet is listing three different checks, the date, their description, and the value of each check.

Because the terms spreadsheet, workbook, and worksheet are so similar, there often is confusion when trying to understand their differences. When you open Microsoft Excel a spreadsheet program , you're opening a workbook. A workbook can contain one or more different worksheets that are accessed through the tabs at the bottom of the worksheet your currently viewing.

What's often most confusing is that a worksheet is synonymous with a spreadsheet. In other words, a spreadsheet and worksheet mean the same thing. However, most people only refer to the program as a spreadsheet program and the files it creates as spreadsheet files or worksheets. Today, Microsoft Excel is the most popular and widely used spreadsheet program, but there are also many alternatives. Below is a list of spreadsheet programs used to create a spreadsheet. Although spreadsheets are most often used with anything containing numbers, the uses of a spreadsheet are almost endless.

Below are some other popular uses of spreadsheets. Spreadsheets are ideal for financial data, such as your checking account information, budgets, taxes, transactions, billing, invoices, receipts, forecasts, and any payment system. Form templates can be created to handle inventory, evaluations, performance reviews, quizzes, time sheets, patient information, and surveys. Teachers can use spreadsheets to track students, calculate grades, and identify relevant data, such as high and low scores, missing tests, and students who are struggling.

Managing a list in a spreadsheet is a great example of data that does not contain numbers, but still can be used in a spreadsheet. Great examples of spreadsheet lists include telephone, to-do, and grocery lists. Spreadsheets can keep track of your favorite player stats or stats on the whole team.

With the collected data, you can also find averages, high scores, and statistical data. Spreadsheets can even be used to create tournament brackets. In a spreadsheet, data is entered in one or more cells. To enter data in a cell, follow the steps below. If you want to add additional data to a cell that already has data entered in it, follow the steps below. Although some uses above could be done in a word processor , spreadsheets have a considerable advantage over word processors when using numbers.

It would be impossible to calculate multiple numbers in a word processor and have the value of the calculation immediately appear. Spreadsheets are also more dynamic with the data and can hide, show, and sort information to make processing lots of information easier.

An active worksheet is the worksheet that is currently open. For example, in the earlier Excel picture, the sheet tabs at the bottom show "Sheet1," "Sheet2," and "Sheet3," with Sheet1 being the active worksheet.

The active tab usually has a white background behind the tab name. In Google Sheets, your spreadsheets starts with one sheet Sheet1. In Microsoft Excel , by default, there is only one sheet tab that opens Sheet1.