Manipulating Member Details in Excel

Manipulating member details in Excel

First some basics

Excel is a spreadsheet package. Each file is called a workbook and contains one or more worksheets, each of which comprises thousands of cells, each referenced by the column letter and row number (the one in top left is A1). Usually, a blank new workbook contains 3 worksheets, but more can be added.

A word about file types

Standard Excel files have the extension .xls. An alternative you will often encounter is a .csv file, which stands for comma separated values. It is just a plain text file with all the values that would normally appear in columns, separated by commas. Sometimes, to avoid problems with compatibility of versions of Excel, files may be distributed as .csv files. These can be opened directly into Excel with no problem. But one thing to be careful of: beware values in a cell that contain commas (for example an address or site name, e.g. “Pit A, Baston Pits”). If you save this as a csv file, when it comes back in to Excel, Excel will treat that comma just as any other and split Pit A and Baston Pits into two adjacent columns.

1. Make a copy of worksheet

Worth making a copy of the whole worksheet so as not to corrupt the original. First, rename the original by double-clicking on the tab at the bottom, typing Original and pressing return. Now, make a copy. Right click on the tab and select “Move or Copy…”. Now you can choose a destination. Be sure to check “Create a copy” and a new worksheet containing all the data will appear called Original (2). Rename as Working version.

Note by right clicking you can also Insert new worksheets.

2. Resizing columns

The columns appear too narrow and half the information is hidden. Hover the mouse over the grey column identifiers (A, B etc). With the mouse positioned on the boundary between two columns it changes to two small arrows. Left click and drag the columns to widen or narrow as necessary. Or, to get it to auto fit to the right size. Double click left mouse button. You can do a whole load of columns at once. Click and hold the mouse on the column A identifier and move the mouse to the right until the column S identifier. All the columns are now selected. Now hover the mouse over any of the boundaries between two columns until you get the double arrow cursor. Now double-click the left mouse button, and all columns are auto fitted for width. Click anywhere on the cells to deselect the columns. The same approach can be used on rows.

Before:

And After:

3. Freeze Panes

On a big worksheet you scroll down and loose the headings. To prevent this you can ‘freeze’ panes to retain column headings, and even the first bits of a row. To the right, just above the up arrow of the scroll bar is a small grey bar. Left click and drag this down so that the thick grey line that appears is just below the column headers, and release the mouse button. The header row appears duplicated – its just a means of splitting the screen up. Now, go to Window menu and select Freeze Pane. The thick grey bar turns into a thin black line and now however far down the worksheet you scroll, the headers always remain in place. To unfreeze, go back to Window menu and select Unfreeze or Remove Split. You can add a vertical split, perhaps to maintain the observers name on the left. To do this, drag the screen split from the little grey bar in the bottom right, to the right of the scroll bar.

4. Sorting rows

You may wish to sort the records. They are currently in Surname order. Perhaps you want them by firstname or by 10-km square. Click the mouse somewhere within the main records, it doesn’t really matter where. Now select the Data à Sort… menu option. Automatically Excel selects all the relevant rows and columns. NOTE – this works only if you have no empty rows or columns. If you have a gap anywhere you will need to select the columns by hand by clicking and dragging on the column identifiers. Excel should also have automatically guessed that you have a Header Row with labels. If it has not, check the My list has header rows option at the bottom. You can now use the drop-down menus to select 10km grid square. Click OK.

5. “Filtering” out certain records

You may wish to pull out all the people who live in a particular 10km, or all those with a certain postal area or those with an email address. Auto Filtering is the easiest way to do this. First, select all the columns (click and drag on the grey column identifiers). Now from the Data menu select Filter and AutoFilter. If the option doesn’t initially appear, that’s is, if the menu is compressed, click the at the bottom of the menu to make it expand.

Small arrows now appear at the top of each column. These enable you to select, or filter, either individual values, blank cells, non blank cells or custom combinations of cells. For example, click the arrow for 10km grid square and select SS53 in the drop down that appears. The table automatically condenses to show just those rows with SS53. The other rows have not been lost, they are just hidden. This is a quick and easy way to see what is within a big data file.

To return to the full list, select (All) from the drop-down menu. To find just those people with email addresses, select the drop-down menu for email address and scroll down to the bottom and select (NonBlanks).

Whenever you have filtered the data, you can Copy and Paste these selected records to a new location. See the next example.

To turn off the filters go to menu Data à Filter and select AutoFilter.

6. Copy and Paste

Make a new worksheet called SS53. Go back to the Working sheet and filter on 10km grid square and select SS53. Beware – sometimes there could be rows hidden off the top of the screen – so always press Ctrl and Home keys together to ensure you are at the top. Now, to copy these particular records, with their headings, left click on cell A1 (the Record type header) and drag the mouse to the bottom right corner, in this case cell S32. Note that though there are only 9 rows from SS53 they still have their original row numbers indicating that many other non-SS53 rows are hidden. With all the rows selected press the Copy button , the area being copied is now surrounded by a dashed flashing line. Now switch to the SS53 worksheet, click in cell A1 and press the Paste button (). All the relevant rows are pasted. The only thing lost is the width of the columns, but that can be easily restored.

Other features

There is a variety of other features in Excel that can be very useful. Functions such as VLOOKUP enable merging of records together. Some of the character functions such as LEFT (to extract characters from the left of a cell), MID (extracts characters from within a cell) and CONCATENATE (sticks bits of text together again) are useful for chopping up text fields (e.g. to strip out the 10-km reference from a 1-km square). If such things are useful we can give more guidance as needed.

Simon Gillings

8 November 2006

Excel and Word for RRs

Simon Gillings 1 14/11/2006