Why I’m Using an Excel 2016 Spreadsheet to Manage Data

As I have only blogged about Excel seven times since I created this blog in 2013, I felt the need to give you some context around why I am using it more. Also, I’m going to be using the same spreadsheet in my screenshots, and some of you savvy readers may, be shaking your head wondering why I’ve reverted back to using an Excel spreadsheet to manage my data. Let me explain:

I’ve been using a spreadsheet in Excel 2016 to keep track of some corporate information. I’m sure this may cause some readers to wonder if I’ve lost my mind, as a Microsoft SharePoint list or Microsoft Dynamics CRM would be two obvious locations to store this type of information.

1. First (and most important), this spreadsheet is a temporary data repository that is being used as part of a planning exercise for something greater and more awesome.
2. Second, I’m using Excel as an opportunity to learn and strengthen my skillset. (I am not ashamed to admit that Excel is my weak point when it comes to my overall my Microsoft Office suite competency.)
3. Third, I’m the only one updating the file so there’s no need for co-authoring or other SharePoint benefits.
4. Fourth, the Excel file lives in a SharePoint library so I get to keep enjoying the benefits of check out/in and version history.

The data in the spreadsheet is an export from a SharePoint list that was used to manage information about our active and inactive clients. I had configured the list from its original intent to also track information related to sales, marketing, and customer experience. Some examples include:

• Can we talk publically about this client on social media? (Some of our projects are 100% NDA. I cannot confirm or deny their existence.)
• What industry vertical is this client in?
• What version of SharePoint is this client using?
• Is this client allowed to receive gifts?
• Is this client interested in being offered Victoria Royals hockey vouchers and/or Suite tickets?
• Do we sent this client a Christmas Card?

When our new Billing and Time Entry System went live (IT’S AWESOME), it eliminated the need to store a Client list in our SharePoint Portal.
(Why? #reasons. Joking aside, as Sean says “not everything should live in SharePoint,” a point I will address in another blog post.)

When said list was retired, it was no longer updated with new information as it was set to read-only. I made the decision to export the data out of the SharePoint list into an Excel spreadsheet.

Why?  The new system (built on Azure, SQL Server, and JavaScript if you are curious) is specifically designed for managing Billing and Time Entry information. I wholeheartedly agreed with the team’s decision to keep its focus narrow (cleaner interface, less data to enter, etc.)

So far the only “downside” in this exercise is having to keep the Excel spreadsheet up-to-date manually by adding a new row when a new client comes onboard. To be honest, in my Business Development role, I already know who’s in the queue to be onboarded, and the new Billing System auto-posts to our Yammer Network when a new client is added, so it’s just a matter of adding a new row.

The biggest part that I miss about using SharePoint is being able to quickly create and switch between views. With Excel, I am using a Table to allow me to sort and filter rows by a variable, and using the Hide/Unhide Column to eliminate the ‘noise’. I find the process of resetting the data to switch to the next view more labour intensive that I’d like, as I’d much rather have a dropdown with a list of pre-set views to switch between. (If I come across it, I will certainly blog about it.)

With that explanation out of the way, stand by for some of the Excel 2016 tips and tricks that I discover while on my journey.