Excel: Useful Information


Freeze Panes

Freezing panes is useful if you have data that stretches beyond the viewing area. You are able to freeze rows, columns, or both to hold those cells in place while you scroll to other areas on the spreadsheet. To do this, click in the cell that is below and/or to the right of the rows/columns you wish to freeze. Go to Window > Freeze Panes > Freeze Panes (or, freeze row / column).

Image:Xls2 FreezePanes.jpg

Additional Windows

You can create additional viewing windows of your sheet – useful if you are working in different locations in the same file (same sheet or another sheet). Go to View > New Window. You will now have 2 windows titled “FileName1:1” and “FileName1:2.” Adjust the arrangement by View > Arrange Windows > Select Tiled, Horizontal, Vertical or Cascade.

Dollar Sign ($)

Using a dollar sign before a row and/or column reference will make that portion of the cell reference static wherever you copy the formula. For example, if you enter the formula =$A2+$D$4 into cell B2 and then drag the formula downward to populate the column, you will see a series of formulas that look like this: =$A3+$D$4, =$A4+$D$4, =$A5+$D$4, =$A6+$D$4. The A column remains static while the row changes and D4 is added to all. If you copy/paste, the formula adjusts to =$Ax+$D$4, “x” being the current row.

Quote Marks (" " and ')

Double quotes: Generally speaking, Excel assumes all content is in a numeric format. If you wish to enter text into formulas, you need to surround the text with quote marks. You will need to remember to add spaces. Example: =A2&"TX" or =A2&", TX"&B2.

Single quotes: Excel typically automatically formats the content as numbers. For example, Excel will not display zeros before a decimal point (unless it is the only number before a decimal). You may wish a series of numbers to read 00001, 00002, 00003 instead of 1, 2, 3. To force this display, place a single quote as the first character in the cell: '00001 will display 00001. You might also wish to display a formula: '=a+b+c will display =a+b+c.

Note about formatting: if large portions of your worksheet are formatted similarly, it is easier to change the cell formatting.

Paste Special

Once you’ve extracted information for a cell or range of cells, you may not wish to retain the formula. Replacing the formula with the data will prevent the information from being changed and will create a smaller-sized file that will not re-calculate every time it is opened. The easiest way is to use Paste Special.

  1. Copy the cell/range
  2. With the new location highlighted (best to select the top-left cell), go to the Edit menu OR right-click and select Paste Special…
  3. Select Paste - Values OR Values and number formats

Xls2 paste-special.jpg

Referencing locations on another tab or Excel file

Formulas do not need to be limited to the active spreadsheet tab. However, be aware that external files may be deleted, relocated, or require permissions.

  • Active spreadsheet: =A2
  • Different tab within file: =TabName!A2
  • Different file: =[FileName]TabName!$A$10