Staff often need to maintain a spreadsheet of families and enrolments. Common tasks like adjusting or reorganizing such lists can be repetitive. Here are the first of some time-saving methods suggested from our Excel course:
- Inserting blank columns
- Re-ordering columns
- Combining two columns such as first and last name.
Inserting blank columns or rows
To insert a blank column into the spreadsheet, select a cell to the immediate right of where you want to insert the column.
On the Home Ribbon select Insert->Insert Sheet Columns. The new column will be inserted to the left of the selection. Use the same method to insert rows.
Re-ordering columns
Select the entire column you want to move by clicking on the column letter.
From the Home ribbon select Cut.
Find the column immediately to the right of where you want the insert. Select that entire column by clicking on the column letter.
On the Home ribbon find the Cells tool group. Select Insert -> Insert Cut Cells.
Note if the data has been converted to a table, excel may prevent changes to any cells that may break formulas.
Combining two columns.
This method is good for combining two cells i.e. first name and surname.
Step 1. Insert a blank column.
In our example we want to combine the first and last names in columns C and D.
- Insert a new blank column.
Step 2. Create a formula.
- Find the first row of names you want to combine. Select the cell in the new column on that first row.
- Press the Equals key (=) to start creating a formula.
- Click on the cell with the first name in column C. The cell’s reference name will appear in the formula. In our example it is C2.
- Immediately type the following: &” “&
The characters to type are: Ampersand, then Quotation, then Space bar, then another Quotation and then another Ampersand.
- Now click the Surname in column D.
Our example will look like this: =C2&” “&D2.
If you get an error, check that your ampersands, space and quotation marks are in the right order etc. - Press Enter to complete the formula. The combined name should appear in column E.
Step 3. Fill the column with the formula
Locate the little green square in the lower right corner of the selected cell. This is the Fill Handle, your cursor changes to a black cross when you hover over it.
- Drag the fill handle down the rest of the column.
- The formula will fill the column with the first and last names.
Step 4. Convert formulas to values.
- Select all of column E. Select Copy.
- Select Paste-> Paste Values.
If your version of Excel does not display the same copy/paste buttons, select Paste Special and select Paste Values from the options.
The last step converts the formulas in the filled cells to simple text values. This will allow you to reposition, copy/paste the names without unexpected results.
