What do spreadsheets look like

Releasing the mouse button will then fill the content. Filter: Filtering will allow you to quickly find the information that you are looking for in a spreadsheet. When you apply a filter, you control the data that is displayed on the screen by setting criteria. Data contained in rows that don't meet your criteria will temporarily disappear from view when the filter is applied. When the filter is cleared, all of the data will once again appear in the spreadsheet.

Formula: A formula is a spreadsheet data type that will calculate a result and display it in the active cell. After typing the formula and pressing the Enter key, the resulting value will be displayed. Formula Bar: The formula bar appears directly above the column headings of a spreadsheet and will display what has been typed into the active cell.

If a spreadsheet contains many rows, you can freeze the rows containing your heading labels so that as you scroll down in the sheet the headings stay at the top and line up with the appropriate data. Likewise, if your spreadsheet contains many columns, the leftmost columns may be frozen so that they stay with the data as you scroll to the right. Function: Functions are built-in formulas that are used to enter either commonly used or very complex formulas.

One commonly used function is the Sum function, which will add up the values in a range. Gridlines typically do not print unless the option is set in the layout options of the spreadsheet.

Labels have no numeric value and cannot be used in a formula or function.. Unless you define a cell or range of cells with a specific name, the name box will display the cell reference of the active cell.

Print Area: The print area is used to specify a range of cells that will be printed, rather than printing an entire worksheet. This is particularly useful for very large worksheets with multiple columns and rows.

Print Titles : Print titles are used to repeat column or row titles on each page. That way, if a spreadsheet prints on multiple pages, each page will contain the appropriate headings to identify the data. If the cells are all together in a rectangular or square shape, it is an adjacent range. An adjacent range is identified by the cell reference in the upper left and lower right corners of the selection separated by a colon. Example: A3:B5. In this example, the range would include all cells in the rectangular area formed by beginning the highlighting in cell A3 and dragging down to B5.

You can consider the colon as the word "through". In this case, the range would include cells A3 through B5. 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. Setting the View Options 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.

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 rows Formulas — 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. Text Headings To enter text headings for the various columns and rows to identify them, follow the same procedure as you would with entering numbers. 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.

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 to in five columns, with the columns labeled by year in cells B4 to F4. 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.

Deleting or Adding Columns or Rows You can get rid of unwanted data or other information by deleting rows or columns. Use the same procedure for deleting a column. 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. This would require totaling up for each year 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 To do this we need to insert a formula for adding a series of numbers in a column. Click on cell: B23 Which is in the column that shows the numbers for weapons used in So for example 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 , other guns B10 , and firearms, type not stated B11 for the year Applying a Formula to Multiple Cells If we now wanted to calculate the total number of gun related homicides for the other four years, we could repeat the process of typing an addition formula into each cell in the rest of row This could be a tab, semi-colon, comma, space, or something else.

In our example, let's choose the space. Excel will then show you a preview of what your new columns will look like. When you're happy with the preview, press "Next. When you're done, click "Finish. In addition to doing pretty complex calculations, Excel can help you do simple arithmetic like adding, subtracting, multiplying, or dividing any of your data.

You can also use parentheses to ensure certain calculations are done first. Conditional formatting allows you to change a cell's color based on the information within the cell. If you want to color code commonalities between different rows in Excel, you can do that. This will help you quickly see information that is important to you.

To get started, highlight the group of cells you want to use conditional formatting on. Then, choose "Conditional Formatting" from the Home menu and select your logic from the dropdown. You can also create your own rule if you want something different. A window will pop up that prompts you to provide more information about your formatting rule.

Select "OK" when you're done, and you should see your results automatically appear. Sometimes, we don't want to count the number of times a value appears. Instead, we want to input different information into a cell if there is a corresponding cell with that information. For example, in the situation below, I want to award ten points to everyone who belongs in the Gryffindor house.

Instead of manually typing in 10's next to each Gryffindor student's name, I can use the IF Excel formula to say that if the student is in Gryffindor, then they should get ten points. In general terms, the formula would be IF Logical Test, value of true, value of false. Let's dig into each of these variables. Note : In the example above, I awarded 10 points to everyone in Gryffindor. If I later wanted to sum the total number of points, I wouldn't be able to because the 10's are in quotes, thus making them text and not a number that Excel can sum.

Have you ever seen a dollar sign in an Excel formula? When used in a formula, it isn't representing an American dollar; instead, it makes sure that the exact column and row are held the same even if you copy the same formula in adjacent rows. You see, a cell reference — when you refer to cell A5 from cell C5, for example — is relative by default. In that case, you're actually referring to a cell that's five columns to the left C minus A and in the same row 5. This is called a relative formula.

When you copy a relative formula from one cell to another, it'll adjust the values in the formula based on where it's moved. But sometimes, we want those values to stay the same no matter whether they're moved around or not — and we can do that by turning the formula into an absolute formula.

Learn more on Microsoft Office's support page here. Have you ever had two sets of data on two different spreadsheets that you want to combine into a single spreadsheet?

For example, you might have a list of people's names next to their email addresses in one spreadsheet, and a list of those same people's email addresses next to their company names in the other — but you want the names, email addresses, and company names of those people to appear in one place.

Before you use the formula, though, be absolutely sure that you have at least one column that appears identically in both places. Scour your data sets to make sure the column of data you're using to combine your information is exactly the same, including no extra spaces.

In this formula, there are several variables. The following is true when you want to combine information in Sheet 1 and Sheet 2 onto Sheet 1. In the example below, Sheet 1 and Sheet 2 contain lists describing different information about the same people, and the common thread between the two is their email addresses.

Let's say we want to combine both datasets so that all the house information from Sheet 2 translates over to Sheet 1. Keep in mind that VLOOKUP will only pull back values from the second sheet that are to the right of the column containing your identical data.

Here are the main differences:. Let's look at an example. Let's say Sheet 1 contains a list of people's names and their Hogwarts email addresses, and Sheet 2 contains a list of people's email addresses and the Patronus that each student has. For the non-Harry Potter fans out there, every witch or wizard has an animal guardian called a "Patronus" associated with him or her.

The information that lives in both sheets is the column containing email addresses, but this email address column is in different column numbers on each sheet.

So what's the formula, then? Instead of manually counting how often a certain value or number appears, let Excel do the work for you. For example, let's say I want to count the number of times the word "Gryffindor" appears in my data set. Databases tend to split out data to make it as exact as possible. For example, instead of having a column that shows a person's full name, a database might have the data as a first name and then a last name in separate columns.

Or, it may have a person's location separated by city, state, and zip code. Let's go through the formula together using an example. Pretend we want to combine first names and last names into full names in a single column. To do this, we'd first put our cursor in the blank cell where we want the full name to appear. The quotation marks around the space tell Excel to put a space in between the first and last name. To make this true for multiple rows, simply drag the corner of that first cell downward as shown in the example.

If you're using an Excel sheet to track customer data and want to oversee something that isn't quantifiable, you could insert checkboxes into a column. For example, if you're using an Excel sheet to manage your sales prospects and want to track whether you called them in the last quarter, you could have a "Called this quarter?

Highlight a cell you'd like to add checkboxes to in your spreadsheet. Once the box appears in the cell, copy it, highlight the cells you also want it to appear in, and then paste it. If you're using your sheet to track social media or website metrics, it can be helpful to have a reference column with the links each row is tracking. If you add a URL directly into Excel, it should automatically be clickable.

But, if you have to hyperlink words, such as a page title or the headline of a post you're tracking, here's how. Highlight the words you want to hyperlink, then press Shift K. From there a box will pop up allowing you to place the hyperlink URL. Copy and paste the URL into this box and hit or click Enter. Sometimes, you'll be using your spreadsheet to track processes or other qualitative things.

Rather than writing words into your sheet repetitively, such as "Yes", "No", "Customer Stage", "Sales Lead", or "Prospect", you can use dropdown menus to quickly mark descriptive things about your contacts or whatever you're tracking. Highlight the cells you want the drop-downs to be in, then click the Data menu in the top navigation and press Validation. From there, you'll see a Data Validation Settings box open.