Use the Microsoft Excel tips and tricks below to help you complete the Reading Manual Roster Import Teachers or Students spreadsheets.
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™, you can use the concatenate function 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.
The images below 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.
| Note: Any function or formula that uses a numeric value or date will automatically default to increasing the number or date as it is copied. For example, a cell containing the number 1 will copy into subsequent cells as 2, 3, 4, and so on. |
The image below shows an example of a copied formula or function. For cells that are next to each other, 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)
|
Click here to open a Microsoft Support article with directions on how to move or copy a formula. |
Left and Right Functions to Generate Username or Passwords
In Excel™, you can use the left and right functions 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.
The images below 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™, you can use the Find and Replace function to remove accented or disallowed characters by pressing Ctrl + F (or cmd on Apple iBook®, MacBook®, or Macintosh® computers) and selecting Replace. The images below 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.
| Note: 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. |
Updated