Patti Condon Research Data Services Librarian University Library (603) 862-1524 patricia.condon@unh.edu
Excel is a program for creating, processing, analyzing, and visualizing tables of data. Spreadsheet programs like Excel make it easy to enter your data and see all of it at once, which can be more difficult in other data analysis programs. On the other hand, other data analysis programs (such as Python, R, SPSS, Stata) may offer more powerful options and may be more reliable for advanced statistical analysis.
This guide offers some basic concepts that are important for getting started with Excel. All University of New Hampshire students, faculty, and staff have licensing for Microsoft’s Office suite. If you do not have access to Microsoft Excel you can try other Spreadsheet programs from LibreOffice or OpenOffice - these are free to download and have similar features as Excel.
You may see these terms in various training documents.
Formulas are calculations that Excel can perform on your data. This includes everything from basic arithmetic to more complicated, built-in functions that operate over a large number of cells. After you click inside a cell to make it the active cell, you can type a formula into the formula bar. Formulas begin with an equals sign ("=").
After you enter a formula into the formula bar, the result of the calculation (or the "value") will appear in the active cell. The formula, however, will still appear in the formula bar. This is great for keeping track of how the value was calculated, but it can be a bit risky, too. Sometimes, you'll do something like delete or move a column, or re-sort the data, and all of a sudden the formula will break and you will lose the value.
To keep just the values, you can copy the cells that contain the formulas, and then "Paste Values" into another set of cells.
There are plenty of ways to copy formulas and values in Excel. One especially useful way is the "fill handle." This is a black "plus sign" that appears when you hover your mouse over the bottom-right corner of the active cell.
A formula can either contain just numbers and arithmetic operators (+, -, etc.), or it can include built-in Excel functions and references to other cells. References can be a bit tricky, so you might want to look through this article on references. This information is especially helpful if you're going to be copying formulas from one column to another.
Sometimes you'll be open files that are already in Excel format (.xls or .xlsx). Sometimes, though, you may be opening a data file that is in an alternative format, like .txt or .csv. These formats can easily be read by Excel, but sometimes Excel doesn't load everything correctly using the defaults.
In a nutshell, Excel will try to guess what kind of variable you have in each column - text or number. If something looks like a number, Excel will apply rules that it applies to all numbers. One of these rules is that numbers can't start with "0." If you have data that look like numbers but actually have zeroes at the beginning - for example, zip codes - you may accidentally lose those zeroes when you open the data file.
To specify the data type of some of your columns when you open the data file, you want to use the "Get External Data from Text" option.This Text Import Wizard will allow you to specify other things, like where the data should be imported and whether you want to skip some of the columns.
