Permissions is one of those areas of SharePoint which can take up a lot of the site owner’s time if they are not structured correctly in the first place.
In order to ensure permissions are easy to manage and are fully scalable going forward, I find it beneficial to group users by role, rather then what permissions they were assigned within a particular site. For example, you may create a “Sales Members” group within the Sales SharePoint site in order to assign contribute permissions to the sales team – this is what SharePoint suggests is the appropriate method.
The problem I see with this method is that if you then need to provide the sales team with a different permission level in another site, you may be tempted to create a new group containing the same set of users, when an appropriate group already exists. You may be asking yourself why someone would be tempted to create a new group in this scenario… one of the main reasons is that the term “members” is synonymous with the Contribute permission level, and therefore the site owner may not want to assign Read permissions to a group named “Sales Members“. The group name “Sales Members” suggests that it contains all of the people with contribute permissions to the Sales site – owners of other sites within the same site collection may not feel comfortable using a group which doesn’t describe its contents.
In this scenario, I would name this group “Sales Team”, as it describes the users it contains unambiguously, and it not directly related to a specific permission level, so you could easily use this group to assign read permissions to the entire Sales team without confusion.
Simply put, and contrary to popular belief, Excel is not designed for storing tables of data!
Excel is a spreadsheeting tool, intended for data analysis.
Do you know what IS designed for storing tables of data?
And SharePoint lists! Which are essentially database tables anyway.
Rather than store tables of data within Microsoft Excel workbooks, which you then store in your SharePoint document libraries, you may want to consider storing the data within a SharePoint list instead…
You may need to utilise Excel-specific functionality such as pivot tables, complex functions, and cross-worksheet calculations – if that’s the case, we can still use Excel to analyse data which we store within a SharePoint list… more on that later.
So let’s have a look at some of the key benefits of storing your data in a SharePoint list…
Only one user at a time is able to modify the contents of each worksheet within an Excel workbook at any one time. Therefore, if you’re storing a list of products within this workbook, while Fred is editing the details of an existing product, Janet is unable to edit another product’s details, or add a new product. Once a table is imported from an Excel workbook into SharePoint as a list, each product becomes an item in its own right, therefore items can be modified independently of each other.
SharePoint lists have the benefit of views. Although the contents of an Excel worksheet can be grouped, sorted and filtered, the interface is nowhere near as dynamic or intuitive as SharePoint lists.You can turn on version control within a library, but this will only tell you that the Excel workbook has been updated and by whom, but what it won’t tell you is what exactly has been updated. If the data is stored within a list with version control turned on, you can view the version history of each individual item, including who modified it, when it was modified, and which properties/metadata/columns were modified.
It is far easier to present the contents of a SharePoint list on a page within your site – by simply adding the web part for that list to the relevant page – than trying to resent the contents of an Excel worksheet. In situations where it is more appropriate to keep the data within Excel, you may consider using Excel Services to present the contents of an Excel workbook within a web part on a SharePoint page. There are many reasons why this is useful, such as allowing a user to dynamically modify the way the data is presented within a pivot table.
Accessing the data within an Excel workbook simply takes longer. For example, to view the information you would have to navigate to the site, then the navigate to the relevant document library, open the required Excel workbook (this can sometimes be a slow process!), then navigate to the tab containing the data. To view the equivalent data within a SharePoint list, you simply navigate to the site, then to the relevant list, and voila!
The point of this article is not to say that Excel is a redundant tool now that you can use SharePoint to store the data. Excel certainly still has its uses – SharePoint is a tool for viewing, adding and editing the data in a convenient and slick shared location, whereas Excel is a great data analysis tool which allows you to produce rich charts, pivot tables and complex formulas.
If you’re really feeling adventurous, you could try storing the data within SharePoint, exporting it to an Excel workbook which is then in turn stored within a document library within your SharePoint site! This allows the data to be managed and maintained within the SharePoint list, but you still have the ability to perform rich analysis – as mentioned above – on the up-to-date copy of the data which is sitting within the Excel workbook… then you get the best of both worlds!
The process described above – and much, much more! – is covered in our SharePoint 2013 Power End User course. Full details can be found on the 3grow website