# Excel: Wikis

Note: Many of our articles have direct quotes from sources you can cite, within the Wikipedia article! This article doesn't yet, but we're working on it! See more info or our list of citable articles.

# Encyclopedia

Excel may refer to:

# Study guide

Up to date as of January 14, 2010
(Redirected to Microsoft Office/Excel article)

# Wiktionary

Up to date as of January 15, 2010

## English

Wikipedia has an article on:

Wikipedia

### Proper noun

 Singular Excel Plural -

Excel

1. (trademark) (properly Microsoft Excel) A spreadsheet application software program written and distributed by Microsoft.

# Wikibooks

Up to date as of January 23, 2010

## Definitions

• Workbook vs. Worksheet – when you open Excel, a new file is created called Book 1 (until you name it differently). It is called “Book” because it is a Workbook that is initially made up of three Worksheets. Think of a three ring binder with three sheets of paper in it. As with a binder, you can add sheets to your Workbook (Insert > Worksheet), delete them, and re-arrange them. Double click on the Worksheet title to rename it.
• Cell – cells are the basic rectangular building blocks of a spreadsheet. They are assigned an address, generally referred to as a cell reference, according to their column and row (e.g. the cell in column B at row 3 is referenced as cell B3).
• Row – rows travel horizontally and are numbered.
• Column – columns travel vertically and are assigned letters.
• Formula – a mathematical formula used to calculate a result based on data from one or more other cells. Often they consist of some combination of the standard mathematical operators ( +, -, *, /) (e.g.: =(A1+A5)/B13).but they may also include functions (see below). When you type a formula into a cell, that cell will generally display the result obtained by the formula, rather than the formula itself.
• Functions – pre-written formulae that perform common (and not so common) calculations, such as summation and averaging. You can combine many functions and operators in a single formula to obtain more complex results (e.g.: =SUM(A1:A13).

## Toolbars

Excel shows 3 toolbars by default: a Standard toolbar, a Formatting toolbar, and the Formula bar.

• Standard Toolbar – as in PowerPoint and Word, the standard toolbar has buttons that, when clicked, perform standard actions such as save, copy, sort.
• Formatting Toolbar – in Excel you can format cells, columns, and rows as well as text/numbers. Most of the formatting toolbar, however, is dedicated to the formatting of text and numbers.
• Formula Bar – used to enter in formulae and functions. When you type into the formula bar, the text you type is applied to the cell that is referenced on the far left of the formula bar.

If any of these toolbars is not visible, you can go to the View menu, select Toolbars and then click next to the name of the toolbar you would like to make visible.

When multiple toolbars share one row, each toolbar has a Toolbar Options button (see image below) that will display additional buttons or button options. The buttons that you use less frequently, but still have chosen to have on the toolbar, will be displayed here.

Click the Toolbar Options button to view and select from additional toolbar commands that you haven’t recently used.

## Navigating Cells

To select Do this (If you are left-handed use Right click in place of Left
a single cell Left click on it
a range of adjacent cells Drag from the first cell to the last cell, or click on the first cell, hold the Shift key and click on the last cell (scrolling if necessary)
non-adjacent cells or ranges Hold CTRL (Windows) or Command (Mac) key and click or drag
an entire row or column Left click on the row or column heading
all cells Left click on the blank header in the upper left corner
the next cell to the right Use the Tab key
the next cell down the column Use the Enter key
the A1 Home cell Press Ctrl + Home keys together
the last cell in a sheet Press Ctrl + End keys together
the cells around the active cell Press Ctrl + Shift + 8 keys together (Select Region)

## Data

### Editing, Moving, and Copying Data

To edit data, select the cell(s) to edit then either type data in the active cell or click the I-beam cursor in the cell to edit.

To move or copy data, select the cell or cells that you want to copy or move. From the toolbar or the Edit menu choose Copy or Paste.

To transfer the formatting from one cell to another cell, use Paste Special. For example, after using a formula, you may want to copy the numeric result rather than the formula. To do this, simply click on the cell that you want to copy and select Edit > Copy. Then, click on the cell where you would like the number pasted and click Edit > Paste Special. A box will pop up with a number of different options. Under the Paste section, click on the bubble that says Values and then click OK.

### Inserting, Deleting, and Clearing Data

In Excel, always select then do. Select the cell you want the data to go into and then enter the data (or the formula).

• To delete or clear data, select the cell or the row or column and press Delete on your keyboard.
• To delete a whole row or column, click on the row number or column letter and Edit > Delete.

### Using Autofill

Autofill allows you to quickly fill data in a series (e.g. months, days of the week, or a numeric series) into adjacent cells.

#### Using Autofill with a text series

To use the Autofill function with text, type in the first word of the series (e.g. January), rest your mouse on the bottom right corner of that cell, and you should see the Autofill cross (right).

Click and drag the series down or across the appropriate number of cells.

Excel knows how to Autofill months (January or Jan) and days of the week (Monday or Mon). You can teach Excel to Autofill other text series by going to the Tools menu, choosing Options, and then clicking on Custom Lists and defining your own text series.

#### Using Autofill with a numeric series

You can also use Autofill to quickly enter numeric patterns (e.g. 1 2 3 or 10 20 30) into adjacent cells.

To use Autofill with numeric patterns, enter the first two values in the series, one value in one cell and the next in the cell immediately below or to the right. Now select (highlight) both cells, release your mouse button, then rest your cursor on the bottom right corner of the selected area so you see the Autofill cross.

When you see the Autofill cross, click and drag down or across the cells you want to fill, then release the mouse button. Double-clicking the Autofill cross will result in an automatic filling of the cells below until it reaches a blank row.

Autofill becomes important again when constructing formulae.

### Controlling Your View of the Data

Auto Size: You may have noticed that sometimes data extends beyond the width of the column. In order to be able to see all of the data, you must widen the column. To do this, go to the header row and place your mouse in between the columns (e.g. on the line that separates the letters A and B). You will notice that your mouse becomes a black double arrow. If you double click on this arrow, the column will automatically size itself to fit the longest string of data in a cell. However, this does not adjust automatically so you may have to Auto Size again after adding more data.

Freeze Panes: Freeze Panes is a useful feature when you are working with a large document that has many rows. By freezing a certain row (usually the header), you make that row visible wherever you are in the document. For example, if you have a document with 100 rows, you can’t see the header row when you’re at cell A100. To solve that problem, click on the row below the one that you would like to freeze. To freeze the header row, click on the cell in the second row and first column. Then go to Window > Freeze Panes. [It’s important to note you can freeze columns as well as rows. If you just click at an arbitrary point in the second row, not only will you freeze the top row, you’ll also freeze all columns to the left of the cell you’ve selected.]

Reveal Formulae: If you want to see and/or print all of the formulae in a spreadsheet (as opposed to the values) there is a really handy shortcut. Simply press Ctrl + ~. To return the view to displaying values, press Ctrl + ~ once again.

### Sorting Data

You can order your data from the Sort window in ascending or descending order as well as based on multiple header values.

Sorting data is simply a way of automatically re-ordering rows on a spreadsheet to put them in a more useful order. For instance, you might sort an address book alphabetically by last name, or a list of items you’d like to buy from most expensive to least expensive.

Start by selecting the data you want to sort. It’s important to select all the columns in the data, not just the column you want to sort by. For instance, if you have a list of items in one column, with the prices in the next column, you would select both columns before running the sort. If your sheet has a “header row” at the top with labels for your columns (like “name,” or “price”) it’s a good idea to include that row in your selection as well, as you’ll see in a moment.

Once you have your data selected, click Data > Sort. You’ll see the sort window, pictured at right. If your data has a header row, be sure the “header row” bubble is filled in at the bottom. Excel will then use your labels in the “Sort by” boxes instead of the usual (unhelpful) “Column A,” “Column B,” etc. Now you can use the “Sort by” box to select the column you’d like to sort the data by (if that column contains text, it will sort alphabetically; if the data is numeric it is sorted in number order). The two “Then by” boxes select what column to use next if there is a tie in the “Sort by” column.

But what if you want to sort by more than three columns? Excel only allows sorting by (up to) three columns at a time, but fortunately preserves the logic of the previous sorts if a new sort is applied, with the most recent sort setting the precedent. Let's say that we have a spreadsheet which contains 20 columns and we want to sort it by 10 columns, with Sort Column 1 being the broadest sort and each succeeding Sort Column organizing the data strictly within the subsets defined by its predecessor. Here are our 10 Sort Columns, presented in logical order:

```    Apple
Banana
Clementine
Dangleberry
Elderberry
Fig
Grape
Hazelnut
Ichang
Jicama
```

The first step is to divide this set of 10 into groups of three, counting from the top:

```    Apple
Banana
Clementine
----------------
Dangleberry
Elderberry
Fig
----------------
Grape
Hazelnut
Ichang
----------------
Jicama
```

We're left with 4 groups. In order for the final sorting to reflect a logical flow from Apple through Jicama, we need to work backwards. The first time we select the Data > Sort option, we will enter the LAST group starting from its TOPMOST value:

```    Sort by: Jicama
Then by: leave blank
Then by: leave blank
---------------------------
Click ""OK""
```

Next, begin the with the 2nd-to-last group:

```    Sort by: Grape
Then by: Hazelnut
Then by: Ichang
```

Next, begin the with the 3rd-to-last group:

```    Sort by: Dangleberry
Then by: Elderberry
Then by: Fig
```

And finally:

```    Sort by: Apple
Then by: Banana
Then by: Clementine
```

This type of sort is frequently used to present detailed hierarchies in a comprehendable manner.

Alternatively, you can use formulas to CONCATENATE (join) your groups together into a single field. For example, if you had Index1 in column A, Index2 in column B, Index3 in column C, and Index4 in column D, you could create a field called "Sort" in column E using this formula: =A2&B2&C2&D2

### Filtering Data

Another way to organize data is to filter it. A filter only displays data that meet a certain criteria, such as all records for a certain day. To do this, go to Data > Filter > AutoFilter. A dropdown arrow will appear in the header cell of each column of your worksheet. Click on the arrow and select the criteria that you would like to use as a filter (e.g. April 10th). The worksheet will now display only the records from that day. You can also use the AutoFilter tool to display the top or bottom 10 numbers in a column, only records with blank spaces in that particular column, records that have a value greater than a certain number in the column that you are filtering, etc. These options are all available under the dropdown arrow that you will see after going to Data > Filter > AutoFilter.

An alternative to filtering is to use IF/THEN functions/formulas to select records or groups of records. For example, if column E contains information on "Country", you could write a formula such as =IF(E1="Mexico","Spanish","English") to fill column F with information on "Language".

## Formula

Select the cell that the formula result is going to be displayed in. The formula can be constructed in the formula bar. Always need to put the = sign before a formula. That is how Excel recognizes what you are entering as a formula.

Sign Operation Example
- Subtraction =A1-A2
* Multiplication =C4*C5
/ Division =C4/D4
(…) Combination =A1*(B1+C1)

Click on the green checkmark to enter the formula, the red x to cancel the formula.

### Autofill with Formulae

Autofill helps you fill in formulae quickly once you have constructed one in a cell. In order to Autofill, select the cell with the formula. Place your cursor so the small black cross appears in the lower right corner of the cell (+). Once that cursor is visible, simply drag your formula down the column (or across the row as the case may be). Autofill will change the cell references accordingly.

Note that cell references can also change automatically when you copy and paste a formula using the clipboard, unless you use an absolute cell reference (see below).

E.g.: If the formula in A3 is =A1+A2, when you drag that formula over to B3 then the formula becomes =B1+B2.

### Cell References

• Relative Cell References: cell references that change when the formula is autofilled into different cells, as in the example above.
• Absolute Cell References: if you don't want a certain part of your cell reference to change when you copy the formula to a new cell, you need to put a \$ in front of the row and/or column part of the reference (see example below).

E.g.: If the formula in A3 is =\$A\$1+A2, when you drag that formula over to B3 the formula becomes =\$A\$1+B2.

To quickly add the dollar symbols use the F4 key, which toggles four ways. First press gives both row and column absolute, \$A\$1, second press gives row absolute, A\$1, third press gives column absolute, \$A1 and finally fourth press returns to fully relative, A1.

### AutoSum

The AutoSum button on the standard toolbar allows you to quickly insert the SUM function. Select the cell where you want to put the total and then click on the AutoSum button ( ). Excel will insert the SUM function and take a guess as to what cell range you’d like to sum. Check to make sure the cell range is correct, then press enter to accept the function.

### Functions

Excel has created hundreds of functions that prevent you from having to write out complex or repetitive formulae yourself. The cells that you want to perform the function on are either listed, separated by commas, or included in a range of numbers, indicated by a colon in between the first number in the range and the last number in the range.

Functions can be inserted by using the Insert menu, by clicking on the Paste Function icon on the Standard toolbar, or by typing = in the formula bar and choosing the function from the drop down list on the left.

Functions can also be 'nested'; that is, inserted into larger functions, by using the appropriate amount of brackets. =AVERAGE(SUM(B2:F2), SUM(B3:F3))

### The Function Wizard

You can quickly generate valid functions using the function wizard. To open it, click the function wizard button in the formula toolbar ( ). It provides a description of the function you select (in this case AVERAGE), a space for you to enter in the range or numbers (in Number1) and previews the Formula Result. You can select a range from your sheet rather than typing it in manually by clicking on the little button with the red arrow to the right of the Value box.

The function wizard allows you to build custom functions to suit your specialized needs. It also offers many built in functions which you can utilize.

### AutoCalculate

Excel will automatically perform calculation on a set of cells that you select and display the results on the status bar. The default calculation is the SUM function, but you can change the calculation by right-clicking (Win) or Command + clicking (Mac) on the AutoCalculate result.

## Using Natural Language Labels

Excel has a convenient labeling feature which allows you to name your cells, columns and rows. You can then use your names to build formulae and functions in place of the cell references. For example, you can label one cell “length” and another cell “width,” and then the formula =length*width would find the area of whatever you’re measuring. You can also assign names to data ranges. For instance, you could select cells A1:C14, then go up to the Insert > Name > Define and create a Name for that range. You can then use that name in formulae and functions. Just keep in mind that the names you define cannot have spaces, so use an underscore if necessary. All names are defined using Insert > Name > Define.

For more details, see Microsoft Excel Programming Recipes#Creating a Named Range.

## 3-D References

“3-D” references allow formulae on one worksheet to access data on another worksheet. (You can even link a formula to a cell in a completely different workbook, although unless you’re working on a very large project this generally isn’t worth the hassle.) Using your mouse, you can easily use data and formula results from other worksheets in your formulae and functions. The fastest way to do this (when composing a formula) is to move to the Worksheet that has the information you want to use, and then click on the cell. The result will look something like this:

A cell reference within the current worksheet A cell reference to the Budget worksheet
B5 Budget!B5

## The Chart Wizard

A full discussion of Excel’s many charting options is beyond the scope of this handout. However, it is fairly straightforward to create a simple chart (and some complicated ones) using the chart wizard. Just highlight the data you wish to base your chart on (including header rows, if you have any) and click the chart wizard button on the toolbar (). The chart wizard will then take you through four steps:

1. Selecting the type of chart you want
2. Verifying the data range you selected
3. Setting options for labels, axes, legends, etc.
4. Choosing whether you’d like the chart to appear in the current working sheet, or in its own, new sheet.

A help button is available at each step in the wizard if you’re confused by any option. It looks like:

A quick way to get a chart started is to select the data and headers you wish to chart, and press F11 on your keyboard.

## Pivot Table

A pivot table is a great reporting tool that sorts and sums independent of the original data layout in the spreadsheet. It is an interactive representation of a data table. One can rearrange the data and choose what to display and what to hide. The best way to find out what a pivot table can do is to create one.

First, set up categories and create some data (See Table 1):

Table 1: Original Data
Who Week What Amount Spent
Joe 3 Activities \$18
Beth 4 Food \$17
Janet 5 Activities \$14
Joe 3 Food \$12
Joe 4 Activities \$19
Janet 5 Car \$12

Now choose any cell in this table and choose Data > Pivot Table wizard. Pivot Table should be selected. Click Next. Excel asks for the data source and suggests this table. Click Next. Finally Excel asks if the table should be placed in a new worksheet. Select new sheet. Click Finish.

### Figure 1: Arranging data on the table.

Drag the headers Who, Week and What into the ROW area, and the Amount header into the Data area. (Leave the Column area blank for now.) To change the placement of an item, drag the header title to the desired area. If the Amount tag does not show "Sum of Amount", double-click it and choose the Sum option. Double-clicking the headers gives options of showing/hiding specific data (like Empty and Activities, may come in handy) and removing subtotaling for this column. Right-clicking gives other options, among them Hide and Show Detail for reading totals only.

Here comes another useful pivot made from the same list. Select any item in the original data list and choose Pivot Table wizard again. This time, drag Who into the Row field, What into the Column field and Amount into the Data field. This table is also very useful for graphing.

Pivot tables are very flexible. The one being built here has four variables: Who, Week, What, and Amount, all of which can be dropped in header or data areas represented by the blue borders.

## Printing

The page breaks can be changed on an Excel spreadsheet so that it prints a bit nicer. This can also help to reduce the number of pages that get printed.

1. First, check out how it is currently set to print with File > Print Preview.
2. Second, if you want to change the page breaks, at the top of the print preview, click the “Page Break Preview” button.
3. Now you will see the spreadsheet broken up by dashed lines with a faint page number in each region.
4. You can move these dashed lines to change the page breaks.
5. Do File > Print Preview again and you will see how the change affects the printing.
6. You will also see “Page Break Preview” option has changed to “Normal View
7. You can also find “Page Break Preview” and “Normal View” under View in the menu bar.

### What if you want to print only part of the spreadsheet?

1. Highlight the area that you want to print.
2. From the menu bar, select File > Print Area > Set Print Area
3. Now when you go to File > Print Preview, you will see only the area that you highlighted.
4. To go back to printing the entire document, use File > Print Area > Clear Print Area

### Not able to highlight the region you wanted to print?

1. Go to File > Page Setup
2. Click the Sheet tab
3. In the box marked “Print Area” you can click the tiny spreadsheet icon.
4. This will reduce the box and allow you to highlight what you want (or something close to it).
5. You will see the cell range appear in the “Print Area” box.
6. Edit the string to get it to cover what you want, and click on the icon at the end of the box.
7. Now hit File > Print Preview
8. Remember to remove these settings if you want to print something else.

## Troubleshooting

Common sources of errors are parentheses that don’t match or missing arguments for functions. If your formula is free from those errors, here are some error values you might get:

Error Description
###### There is nothing wrong with your formula; the cell simply isn’t big enough to display the result. Widen the column. or set "Shrink to fit" under Alignment.
#DIV/0 You are trying to divide by zero
• Correct the divisor
• If the divisor is a cell reference, check to make sure the cell isn’t empty
#NAME? There is a name in the formula that Excel doesn’t recognize.
• If you used a natural language name, check the spelling
• If you typed in a function, check the spelling or verify that the function exists.
• If you are performing operations on text, enclose the text in double quotation marks
#REF! A cell reference is not valid. Reenter the formula.
#VALUE! The formula uses the wrong type of operand or argument. Check to see that you’re not performing math operations on labels or that arguments of functions that need to numeric are not referring to cells containing labels.

A handy function to avoid errors is ISERR. For example, to divide one column by another, where blanks and zeros are present, use this formula (in cell C1): =IF(ISERR(A1/B1),"",A1/B1)

The basic functionality can be extended using Addins. Some useful examples of this are shown below:

• ExcelCalcs - Equation display in Excel: Free software to display cell formulas as mathematical equations. Repository of sample equations and solved problems.
• XLXtrFun: XlXtrFun.xll is a collection of functions which extends the capabilities of Microsoft Excel; developed primarily to facilitate, interpolation of 2-dimensional and 3-dimensional data, and simplify 2-variable curve fitting.