Spreadsheet Tips and Tricks
To ensure reading success, all students need to perform to high standards.
Referencing Other Cells
In Microsoft Excel™ one cell can point to another using the letters and numbers of the cited cell. Cell A1 refers to the cell found in the first column (A), first row (1); cell B4 refers to the cell found in the second column (B), fourth row (4), and so on.
Concatenate Function: Combining Cells to Generate Username or Password
In Excel™, the concatenate function is used to combine the contents of two or more cells, and is a useful way to generate a username or a password. To combine the contents of two or more cells, type the concatenate command followed by the cells to be combined, using their column (A,B,C) and row (1,2,3) values.
These images show how the First Name (column A) and Last Name (column C) may be combined to create the Username (column E). In this example, the username is created by clicking on the appropriate Username cell (i.e. E2), typing the command “=Concatenate(A2,C2)” and hitting enter.
Copying a Function or a Formula
In Excel™, a function or a formula in one cell may be copied and used in another cell.
This image shows an example of a copied formula or function. For cells that are adjacent, click the dark black dot in the bottom right corner of the cell containing the formula and drag it to a connecting cell. For cells that are not adjacent, click on the cell containing the formula, use the copy function (Ctrl+C) to copy it and the paste function (Ctrl+V) to place it in any other cell.(Replace CTRL with CMD on Apple iBook®, MacBook®, or Macintosh® computers)
Please note that any function or formula which uses a numeric value or date will automatically default to the use of an increasing sequence in whole numbers. For example, a cell containing the number 1 will copy into subsequent cells as 2, 3, 4, and so on.
Left and Right Functions to Generate Username or Passwords
In Excel™, the left and right functions are used to extract characters from the left or right side of a selected cell. This is done by using the command “=LEFT” or “=RIGHT” and indicating the cell and number of characters to be included in the extraction. The command “=Left(E2,5)” seen below would look at cell E2 and extract the first five characters on the left.
These images show the creation of a password using this command. The command “=LEFT(E2, 5)” looks at cell E2 and extracts the 5 characters on the left.
Find and Replace to Remove Disallowed Characters
In Excel™, the Find and Replace function can be used to remove accented or disallowed characters by pressing Ctrl + F (or cmd on Apple iBook®, MacBook®, or Macintosh® computers) and selecting Replace.
These images show the steps of the Find and Replace function. In the Editing section of the toolbar, select the Find and Replace option. In the Find text space, type the characters to be replaced. In the Replace text space, type the characters to appear (or leave blank to delete characters listed in the Find text space). Click Find All and Replace All to make the change.
Please note that the Find and Replace function finds ALL instances of a character. Using a single character, such as “a,” finds all instances of a, whether used singularly or in a word. We recommend checking all changes made using the Find and Replace function to avoid unintentional changes.
