by Formulas are the backbone of most advanced calculations within Excel. Think of a formula as an equation; it’s a series of steps that lead to a final output. You can tell Excel that you’re entering a formula in Excel by starting your input with the equals sign (=). For example, try entering the following into a cell:
Formulas in Excel use the standard mathematical order of operations to evaluate. As such, operations within formulas are processed in this order:
- Parentheses
- Exponents (^)
- Multiplication (*)
- Division (/)
- Addition (+)
- Subtraction (-)
All Excel formulas begin with an equal sign (=).
After the equal symbol, you enter either a calculation or function. For example, to add up values in cells B1 through B5, you can either:
- Type the entire equation: =B1+B2+B3+B4+B5
- Use the SUM function: =SUM(B1:B5)
Press the Enter key to complete the formula. Done.
Elements of Microsoft Excel formulas
When you make a formula in Excel, you can use different elements to supply the source data to the formula and indicate what operators should be performed on those data. Depending on the formula type that you create, it can include any or all of the following parts:
Constants: numbers or text values that you enter directly in a formula, like =2*3.
Cell references: reference to a cell containing the value you want to use in your Excel formula, e.g. =SUM(A1, A2, B5).
To refer to data in two or more contiguous cells, use a range reference like A1:A5. For example, to sum values in all cell between A1 and A5, inclusive, use this formula:
=SUM(A1:A5).
Names: defined name for a cell range, constant, table, or function, for example =SUM(my_name).
Functions: predefined formulas in Excel that perform calculations using the values supplied in their arguments.
Operators: special symbols that specify the type of operation or calculation to be performed.
Special paste
Paste menu options
- Select the cells that contain the data or other attributes that you want to copy.
- On the Home tab, click Copy Copy icon.
- Click the first cell in the area where you want to paste what you copied.
- On the Home tab, click the arrow next to Paste, and then do any of the following.
| Select | To paste |
| Paste | All cell contents and formatting, including linked data. |
| Formulas | Only the formulas. |
| Formulas & Number Formatting | Only formulas and number formatting options. |
| Keep Source Formatting | All cell contents and formatting. |
| No Borders | All cell contents and formatting except cell borders. |
| Keep Source Column Widths | Only column widths. |
| Transpose | Reorients the content of copied cells when pasting. Data in rows is pasted into columns and vice versa. |
| Paste Values | Only the values as displayed in the cells. |
| Values & Number Formatting | Only the values and number formatting. |
| Values & Source Formatting | Only the values and number color and font size formatting. |
| Formatting | All cell formatting, including number and source formatting. |
| Paste Link | Link the pasted data to the original data. When you paste a link to the data that you copied, Excel enters an absolute reference to the copied cell or range of cells in the new location. |
| Paste as Picture | A copy of the image. |
| Linked Picture | A copy of the image with a link to the original cells (if you make any changes to the original cells those changes are reflected in the pasted image). |
| Column widths | Paste the width of one column or range of columns to another column or range of columns. |
| Merge conditional formatting | Combine conditional formatting from the copied cells with conditional formatting present in the paste area. |
Paste options
| Select | To paste |
| All | All cell contents and formatting, including linked data. |
| Formulas | Only the formulas. |
| Values | Only the values as displayed in the cells. |
| Formats | Cell contents and formatting. |
| Comments | Only comments attached to the cell. |
| Validation | Only data validation rules. |
| All using Source theme | All cell contents and formatting using the theme that was applied to the source data. |
| All except borders | Cell contents and formatting except cell borders. |
| Column widths | The width of one column or range of columns to another column or range of columns. |
| Formulas and number formats | Only formulas and number formatting. |
| Values and number formats | Only values and number formatting options from the selected cells. |
| All, merge conditional formats | Combine conditional formatting from the copied cells with conditional formatting present in the paste area. |
Operation options
The Operation options mathematically combine values between the copy and paste areas.
| Click | To |
| None | Paste the contents of the copy area without a mathematical operation. |
| Add | Add the values in the copy area to the values in the paste area. |
| Subtract | Subtract the values in the copy area from the values in the paste area. |
| Multiply | Multiply the values in the paste area by the values in the copy area. |
| Divide | Divide the values in the paste area by the values in the copy area. |
Freeze pane
To keep an area of a worksheet visible while you scroll to another area of the worksheet, go to the View tab, where you can Freeze Panes to lock specific rows and columns in place, or you can Split panes to create separate windows of the same worksheet.
Freeze the first column
- Select View > Freeze Panes > Freeze First Column.
The faint line that appears between Column A and B shows that the first column is frozen.
Freeze the first two columns
- Select the third column.
- Select View >Freeze Panes > Freeze Panes.
Freeze columns and rows
- Select the cell below the rows and to the right of the columns you want to keep visible when you scroll.
- Select View > Freeze Panes> Freeze Panes.
Unfreeze rows or columns
- On the View tab > Window > Unfreeze Panes.
