Other 'Smart' Buttons in Excel
Excel has some similar buttons of its own which have options menu. The exact options may vary depending on exactly what you just did.
- AutoCorrect Options
When Excel applies an AutoCorrection, the AutoCorrect Options button appears. Its menu offers to undo the autocorrection. You can even make Excel stop doing this type of autocorrection.
The choices that appear for pasting depend on what you have copied and whether it was already inside your spreadsheet or not. Office 2007 and 2010 use icons to illustrate the choices. Hovering over an icon makes a screen tip appear about the icon.
The examples below are for pasting from inside Excel, from a web page, and a picture from a Word document.
Copy and paste: from inside Excel, from a web page, a picture from a Word document
- Paste the formula
- Paste the formula and number formatting
- Keep source formatting
- Match destination formatting
- No borders
- Keep source column widths
- Transpose - rows and columns
- Values only
- Values and number formatting
- Values and source formatting
- Paste formatting only
- Paste as a link
- Paste as a picture
- Paste as a linked picture
- Auto Fill Options
When you drag the Fill handle (the small blank square in the bottom right corner of the selected cells), Excel looks to see if there is a pattern to the values you selected. If it sees one, it will can extend the series into the new cells.
The AutoFill Options menu gives you the choice of filling the series (which also includes the formatting) or just the formatting or filling the series without the formatting. The example at the right also includes Fill Months because Excel thinks that the text is months of the year.
When you insert a cell or a row or a column, how do you want the inserted part formatted? Menu items vary depending on what you are inserting.
The illustrations are for a cell, a row, and a column.
- Trace Error Options
A green triangle in upper left of cell means that Excel thinks your formula has an error. Click in the cell and then on the Trace Error icon. The menu should give you a hint about what the problem is.
Typing a range name that does not exist
Leaving off the quotation marks around a text string in a formula
|=SUMN instead of =SUM
= Total is&B44 instead of ="Total is "&B44
|#DIV/0!||Tried to divide by zero. Either the cell's value was zero, it was blank, or it contained text instead of a number.
Numbers that are formatted as text can cause this, too.
|#VALUE!||Tried to do something to data of the wrong type, like trying to add values when at least one is text instead of a number.||= 4 + "total"
="Total = "& 6
|#REF!||Formula refers to a cell that you deleted or pasted over.|
|#NUM!||Problem with a number in the formula.
An argument is invalid.
A calculated value is too large or too small to display.
|=DATE(-72, 3, 15) which has as the year 72 BC. The DATE function cannot handle a negative number for the year.
a value less than
-1*10307 or greater than 1*10307
|#NULL!||The formula includes multiple ranges with a space in between instead of a comma, but the ranges do not overlap. When the ranges do overlap, the formula uses only the values from the intersection of the ranges.||=SUM(B7:B13 C7:D8)|
|Formula Omits Adjacent Cells||This one may not actually be an error! It depends on what your formula is supposed to do. The warning helps when your dragging missed a cell or two.|