Microsoft-EXCEL

Excel specifications and limits

Worksheet and workbook specifications

Feature	Maximum limit
Open workbooks	Limited by available memory and system resources
Worksheet size	65,536 rows by 256 columns
Column width	255 characters
Row height	409 points
Page breaks	1000 horizontal and vertical
Length of cell contents (text)	32,767 characters. Only 1,024 display in a cell; all 32,767 display in the formula bar.
Sheets in a workbook	Limited by available memory (default is 3 sheets)

About cell and range references

A reference identifies a cell or a range of cells on a worksheet and tells Microsoft Excel where to look for the values or data you want to use in a formula. With references, you can use data contained in different parts of a worksheet in one formula or use the value from one cell in several formulas. You can also refer to cells on other sheets in the same workbook, and to other workbooks. References to cells in other workbooks are called links.
The A1 reference style
By default, Excel uses the A1 reference style, which refers to columns with letters (A through IV, for a total of 256 columns) and refers to rows with numbers (1 through 65536). These letters and numbers are called row and column headings. To refer to a cell, enter the column letter followed by the row number. For example, B2 refers to the cell at the intersection of column B and row 2.

To refer to	Use
The cell in column A and row 10	A10
The range of cells in column A and rows 10 through 20	A10:A20
The range of cells in row 15 and columns B through E	B15:E15
All cells in row 5	5:5
All cells in rows 5 through 10	5:10
All cells in column H	H:H
All cells in columns H through J	H:J
The range of cells in columns A through E and rows 10 through 20	A10:E20

Reference to another worksheet    In the following example, the AVERAGE worksheet function calculates the average value for the range B1:B10 on the worksheet named Marketing in the same workbook.

Link to another worksheet in the same workbook

Note that the name of the worksheet and an exclamation point (!) precede the range reference.
The difference between relative and absolute references

Relative references   A relative cell reference in a formula, such as A1, is based on the relative position of the cell that contains the formula and the cell the reference refers to. If the position of the cell that contains the formula changes, the reference is changed. If you copy the formula across rows or down columns, the reference automatically adjusts. By default, new formulas use relative references. For example, if you copy a relative reference in cell B2 to cell B3, it automatically adjusts from =A1 to =A2.

About formulas

Formulas are equations that perform calculations on values in your worksheet. A formula starts with an equal sign (=). For example, the following formula multiplies 2 by 3 and then adds 5 to the result.

=5+2*3

A formula can also contain any or all of the following: functions, references, operators, and constants.
Operator precedence
If you combine several operators in a single formula, Excel performs the operations in the order shown in the following table. If a formula contains operators with the same precedence— for example, if a formula contains both a multiplication and division operator— Excel evaluates the operators from left to right.

Operator	Description
: (colon) (single space) , (comma)	Reference operators
–	Negation (as in –1)
%	Percent
^	Exponentiation
* and /	Multiplication and division
+ and –	Addition and subtraction
&	Connects two strings of text (concatenation)
= < > <= >= <>	Comparison

Use of parentheses
To change the order of evaluation, enclose in parentheses the part of the formula to be calculated first. For example, the following formula produces 11 because Excel calculates multiplication before addition. The formula multiplies 2 by 3 and then adds 5 to the result.
=5+2*3
In contrast, if you use parentheses to change the syntax, Excel adds 5 and 2 together and then multiplies the result by 3 to produce 21.
=(5+2)*3
In the example below, the parentheses around the first part of the formula force Excel to calculate B4+25 first and then divide the result by the sum of the values in cells D5, E5, and F5.
=(B4+25)/SUM(D5:F5)
About constants in formulas
A constant is a value that is not calculated. For example, the date 10/9/2008, the number 210, and the text "Quarterly Earnings" are all constants. An expression, or a value resulting from an expression, is not a constant. If you use constant values in the formula instead of references to the cells (for example, =30+70+110), the result changes only if you modify the formula yourself.

Enter data in worksheet cells

Enter numbers, text, a date, or a time

1.      Click the cell where you want to enter data.

2.      Type the data and press ENTER or TAB.

Numbers and text in a list

1.      Enter data in a cell in the first column, and then press TAB to move to the next cell.

2.      At the end of the row, press ENTER to move to the beginning of the next row.

Enter the same data in other worksheets

If you've already entered data on one worksheet, you can quickly copy the data to corresponding cells on other sheets.

1. Select the sheet that contains the data and the sheets to which you want to copy the data.
2. Select the cells that contain the data you want to copy.
3. On the Edit menu, point to Fill, and then click Across Worksheets.

Fill in a series of numbers, dates, or other items

1. Select the first cell in the range you want to fill.
2. Enter the starting value for the series.
3. Enter a value in the next cell to establish a pattern.

How?

o    If you want the series 2, 3, 4, 5..., enter 2 and 3 in the first two cells. If you want the series 2, 4, 6, 8..., enter 2 and 4. If you want the series 2, 2, 2, 2..., you can leave the second cell blank.

o    To specify the type of series, use the right mouse button to drag the fill handle over the range, and then click the appropriate command on the shortcut menu.

o    For example, if the starting value is the date JAN-2002, click Fill Months for the series FEB-2002, MAR-2002, and so on; or click Fill Years for the series JAN-2003, JAN-2004, and so on.

o    To manually control how the series is created, or use the keyboard to fill in a series, use the Series command on the shortcut menu.

4. Select the cell or cells that contain the starting values.
5. Drag the fill handle over the range you want to fill.

To fill in increasing order, drag down or to the right.

To fill in decreasing order, drag up or to the left.

Clear cell formats or contents

When you clear cells, you remove the cell contents (formulas and data), formats (including number formats, conditional formats, and borders), or comments, but you leave the blank cells on the worksheet. When you delete cells by clicking Delete on the Edit menu, Microsoft Excel removes them from the worksheet and shifts the surrounding cells to fill the space.

1. Select the cells, rows, or columns you want to clear of formats or contents.
2. On the Edit menu, point to Clear, and then click Formats or Contents.

You can clear formats and contents by clicking All. This also removes any cell comments and data validation.

Delete cells, rows, or columns

1. Select the cells, rows, or columns you want to delete.
2. On the Edit menu, click Delete.
3. If you are deleting a range of cells, click Shift cells left, Shift cells up, Entire row, or Entire column in the Delete dialog box.

Undo or redo an action

1. Do one or more of the following:

o    To undo recent actions one at a time, click Undo.

o    To undo several actions at once, click the arrow next to Undo and select from the list. Microsoft Excel reverses the selected action and all actions above it.

o    To cancel an entry in a cell or the formula bar before you press ENTER, press ESC.

2. If you change your mind, click Redo or click the arrow next to Redo and select from the list.

Delete sheets

1. Select the sheets you want to delete.

How?

When you enter or change data, the changes affect all selected sheets. These changes may replace data on the active sheet and other selected sheets.

Two or more adjacent sheets	Click the tab for the first sheet, and then hold down SHIFT and click the tab for the last sheet.
Two or more nonadjacent sheets	Click the tab for the first sheet, and then hold down CTRL and click the tabs for the other sheets.
All sheets in a workbook	Right-click a sheet tab, and then click Select All Sheets on the shortcut menu.

2.     On the Edit menu, click Delete Sheet.

Move or copy sheets

1. Caution  Be careful when you move or copy sheets. Calculations or charts based on worksheet data might become inaccurate if you move the worksheet. Similarly, if you insert a worksheet between sheets On the Edit menu, click Move or Copy Sheet.
2. In the To book box, click the workbook to receive the sheets.

To move or copy the selected sheets to a new workbook, click new book.

3. In the Before sheet box, click the sheet before which you want to insert the moved or copied sheets.
4. To copy the sheets instead of moving them, select the Create a copy check box

Find text or numbers

1. Select the range of cells you want to search.

If you want to search the entire worksheet, click any cell.

2. On the Edit menu, click Find.
3. In the Find what box, enter the text or numbers you want to search for or choose a recent search from the Find what drop down box.

Note  You can use wildcard characters in your search criteria.

4.      If you want to specify a format for your search, click Format and make your selections in the Find Format dialog box.

If Format is not available in the Find and Replace dialog box, click Options and then click Format.

5. Click Options to further define your search. For example, you can search for all of the cells that contain the same kind of data, such as formulas.

In the Within box, you can select Sheet or Workbook to search a worksheet or an entire workbook.

6. Click Find All or Find Next.

Find All lists every occurrence of the item you are searching for and allows you to make a cell active by selecting a specific occurrence. You can sort the results of a Find All search by clicking a header.

Add, delete, or move page breaks

1.      On the View menu, click Page Break Preview.

2.      Do one of the following:

View page breaks    Manually inserted page breaks appear as solid lines. Dashed lines indicate where Microsoft Excel will break pages automatically.

Move a page break    Drag the page break to a new location. Moving an automatic page break changes it to a manual page break.

Insert vertical or horizontal page breaks    Select a row oOn the Edit menu, click Delete Sheet. r column below or to the right of where you want to insert a horizontal or vertical page break, right-click, and then click Insert Page Break.

Remove page breaks    Drag the page break outside of the print area. To remove all manual page breaks, right-click any cell on the worksheet, and then click Reset All Page Breaks.

Insert blank cells, rows, or columns

1. Do one of the following:

Insert new blank cells Select a range of cells where you want to insert the new blank cells. Select the same number of cells as you want to insert.

Insert a single row Click a cell in the row immediately below where you want the new row. For example, to insert a new row above row 5, click a cell in row 5.

Insert multiple rows Select rows immediately below where you want the new rows. Select the same number of rows as you want to insert.

Insert a single column Click a cell in the column immediately to the right of where you want to insert the new column. For example, to insert a new column to the left of column B, click a cell in column B.

Insert multiple columns Select columns immediately to the right of where you want to insert the new columns. Select the same number of columns as you want to insert.

2. On the Insert menu, click Cells, Rows, or Columns.

Insert a new worksheet

Add a single worksheet

• Click Worksheet on the Insert menu.

Add multiple worksheets

Determine the number or worksheets you want to add.

1.      Hold down SHIFT, and then select the same number of existing worksheet tabs that you want to add in the open workbook.

Example: If you want to add three new worksheets, select three existing worksheet tabs.

2.      Click Worksheet on the Insert menu.

Name cells on more than one worksheet

This is also called a 3-D reference.

1. On the Insert menu, point to Name, and then click Define.
2. In the Names in workbook box, type the name.

Position data in a cell & Formatting:

1. Select the cells you want to reposition. To center or align data that spans several columns or rows, such as column and row labels, first merge a selected range (range: Two or more cells on a sheet. The cells in a range can be adjacent or nonadjacent.) of cells and then select the merged cell (merged cell: A single cell that is created by combining two or more selected cells. The cell reference for a merged cell is the upper-left cell in the original selected range.) for repositioning.

2.      Align data at the top, center, or bottom of a cell

1.      On the Format menu, click Cells, and then click the Alignment tab.

2.      In the Vertical box, click the option you want.

Rotate text in a cell

3.      On the Format menu, click Cells, and then click the Alignment tab.

4.      In the Orientation box, click a degree point, or drag the indicator to the angle you want.

 

To display text vertically from top to bottom, click the vertical Text box under Orientation.

Merge or split cells or data

Spread the content of one cell over many cells

Text spread and centered over multiple cells

Warning  Microsoft Excel places only the upper-leftmost data in the selected range into the resulting merged cell. If there is data in other cells, the data is deleted.

Define the default column width

1. Right-click a sheet tab, and then click Select All Sheets on the shortcut menu
2. On the Format menu, point to Column, and then click Standard Width.
3. Type a new measurement.
4. Arrange automatically column content cell in the “auto Fit selection”

The number that appears in the Standard column width box is the average number of digits 0-9 of the standard font that fit in a cell.

Select the column, point to Row on the Format menu, click Height, and then enter a number.

Display or hide rows or columns

Display a hidden row or column

1. Select a row or column on each side of the hidden rows or columns you want to display.
2. On the Format menu, point to Row or Column, and then click Unhide.

If the first row or column of a worksheet is hidden, click Go To on the Edit menu. Type A1 in the Reference box, and click OK. Point to Row or Column on the Format menu, and then click Unhide.

Also, the row or column may have had the height or width set to zero. Point to the border of Select All until the cursor changes to or, and drag to widen the row or column.

Hide a row or column

1. Select the rows or columns you want to hide.
2. On the Format menu, point to Row or Column, and then click Hide.

Password protect a worksheet or workbook

Protect worksheet elements

Protect worksheet elements from all users.

1.      Switch to the worksheet you want to protect.

2.      Unlock any cells you want users to be able to change: select each cell or range, click Cells on the Format menu, click the Protection tab, and then clear the Locked check box.

3.      Hide any formulas that you don't want to be visible: select the cells with the formulas, click Cells on the Format menu, click the Protection tab, and then select the Hidden check box.

4.      Unlock any graphic objects you want users to be able to change.

How?

You don't need to unlock buttons or controls for users to be able to click and use them. You can unlock embedded charts, text boxes, and other objects created with the drawing tools that you want users to be able to modify. To see which elements on a worksheet are graphic objects, click Go To on the Edit menu, click Special, and then click Objects.

1.      Click the Protection tab.

2.      Clear the Locked check box, and if present, clear the Lock text check box.

5.      On the Tools menu, point to Protection, and then click Protect Sheet.

6.      Type a password (password: A way to restrict access to a workbook, worksheet, or part of a worksheet. Excel passwords can be up to 255 letters, numbers, spaces, and symbols. You must type uppercase and lowercase letters correctly when you set and enter passwords.) for the sheet.

Remove protection and passwords

Remove user permissions for a range

1. Switch to the protected worksheet.
2. On the Tools menu, point to Protection, and then click Unprotect Sheet.
3. Click Delete.

Note  The password is optional; however, if you don't supply a password, any user will be able to unprotect the sheet and change the protected elements. Make sure you choose a password you can remember, because if you lose the password, you cannot gain access to the protected elements on the worksheet.

Sort a range

Sort rows in ascending order (A to Z, or 0 to 9) or descending order (Z to A, or 9 to 0)

1. Click a cell in the column you would like to sort by.
2. Click Sort Ascending or Sort Descending.

Note   In a PivotTable report, Microsoft Excel uses the selected field to sort.

Sort rows by 2 or 3 columns

For best results, the range you sort should have column labels.

1. Click a cell in the range you want to sort.
2. On the Data menu, click Sort.
3. In the Sort by and Then by boxes, click the columns you want to sort.
4. Select any other sort options you want, and then click OK.

Sort by 4 columns

1. Click a cell in the range you want to sort.
2. On the Data menu, click Sort.
3. In the first Sort by box click the column of least importance.
4. Click OK.
5. On the Data menu, click Sort.
6. In the Sort by and Then by boxes, click the other 3 columns you want to sort, starting with the most important.
7. Select any other sort options you want, and then click OK.

Sort rows by months or weekdays

1. Select a cell or range you want to sort.
2. On the Data menu, click Sort.
3. In the Sort by box, click the column you want to sort.
4. Click Options.
5. Under First key sort order, click the custom sort order you want, and then click OK.
6. Select any other sort options you want, and then click OK.Show All