Excel to SharePoint

In these days where we are all told we have to recycle I am going to talk about an under used part of SharePoint (both MOSS and WSS) that allows you to convert your old Excel spreadsheets into new super SharePoint lists. But first I am going to moan about refuge (sorry ‘recycling’) collections in the West Midlands. Friday in my road is bin day. Well, it used to be ‘bin day’ now it is called ‘two large wheelie bins (one green, one black), a green box and a large square bag day’. Yes, for any pedestrian on a Friday in our street it is an obstacle race similar to the Grand National especially for old Reg with his zimmer frame. Okay enough moaning I know we need to recycle to help save the planet and I am doing my bit for the planet (don’t forget you can still buy my Car Sharing SharePoint Template at Buy It Here). Oh, and I must have watched every episode ever of ‘The Wombles’. It just gets me annoyed when you hear that ninety-two year old Mrs. Batty has been sentenced to twenty years in jail for putting her old copy of ‘Woman’s Weekly’ in the wrong bin. Okay exaggeration she only really got fifteen years. Surely in this technical age somebody could invent a machine that can sort our waste out for us? Back to SharePoint then after that politically incorrect rant. Many organisations have got Excel spreadsheets that contain useful information, maybe going back years. You might have a list of all your Purchase Orders, a customer details list or a catalogue all stored on Excel spreadsheets. Now you are using SharePoint you don’t need to throw out these spreadsheets or even upload them into a Document Library. Instead you can import them straight into SharePoint. Why would you want them in SharePoint? By having the data from the spreadsheet in a SharePoint list it makes it easier to filter the data, display the data to the right audiences, search the data, add new records to the list and most importantly it is easier for people to have access to the data. So how do you import the spreadsheet into SharePoint? If the spreadsheet is in Excel (any version since 97 to the current 2007) it is a very quick and painless process. It is worth checking first that the Excel columns are vertical going from left to right with the column names in Row 1. Open up the SharePoint site where you want to create the new SharePoint list that will contain the data from your spreadsheet. Click ‘Site Actions’ – Create. Then from the column ‘Custom List’ you will find ‘Import Spreadsheet’. Enter a ‘Name’ for the List and a ‘Description’, if you wish. Then in the ‘Import from Spreadsheet’ section browse to your spreadsheet. Oh, perhaps I should have mentioned earlier that you need to know where the spreadsheet is. Next click the ‘Import’ button. You will now get the ‘Import Windows SharePoint List’ screen as well as your Excel spreadsheet. This shows that it is working though WSS so you are not just restricted to MOSS. Cell A1 is highlighted but you can’t select all the cells until you change the dropdown ‘Range of Cells’ and then click in the bottom box ‘Select Range’. Now you can click on cell A1 hold the mouse button down and drag to the last cell in your spreadsheet so that all cells including the column titles are selected. Now click the ‘Import’ button and after a few seconds you will have a neatly presented SharePoint list with all the columns and data from your spreadsheet now displaying. You can add views and filters now to your heart’s content. Don’t forget to dispose of your excel file in the correct Recycling Bin after you have finished. SharePoint has many hidden gems like this and whilst I try and cover these in my blog I would always recommended attending a one day SharePoint Taster course with a company like Officetalk to really see what SharePoint can do. It might help you decide if you really need to invest in full MOSS or Windows SharePoint Services will do. Just hope my large recycling bag hasn’t blown away again after it has been emptied and that my empty wheelie bins aren’t blocking my drive when I get home.