Data Validation in Excel Services
I’m a huge consumer of Data Validation in Excel. Techniques I use include:
Pro-active Data Validation:
- Excel’s Data Validation toolset
- Form controls
- ActiveX controls
- PivotTable filters
- Slicers
Re-active Data Validation:
- Conditional formats
- Contextual formulas (IF)
So naturally, as I was trying to convert one of my web pages to use Microsoft WebApp off SkyDrive, I ran into issues. Unfortunately at this point in time, the majority of the techniques that we use for data validation in the client are not yet supported in WebApp. And yet, if we’re trying to build a web application, sanitizing the data is really important to make sure it works correctly.
I decided to convert my “Automation Evaluation” worksheet, which was intended to display how much it cost someone to do repetitive tasks over time. The overall goal of this file was to convince someone that they should pay me to automate their work, back when I was still doing consulting projects. I don’t do them any more, but I think the exercise is still worth having on the site.
In this file, it was really important to me to control the options people can select to work with for how many minutes/hours/days they spend on a task. I want the right data in there to drive my chart, yet my options are VERY limited in WebApp.
I tried the slicers, but they aren’t really built for this scenario. Set horizontally or vertically they take up way too much space. You can set them to a single column then make your user scroll down, but if you only have one option on the screen (like a combo box) then it gets awkward to figure out which option(s) are selected, and involves extra clicks. And that doesn’t even touch the “how do I return the clicked value from a slicer” issue. (I haven’t figured out how to get a value from a non-PowerPivot slicer yet.)
So this time I reached to PivotTables. I ended up making some very small tables with the options I needed and created a PivotTable that uses only the Page Field. This give me the ability to get a drop-down with pre-defined options. It works, but it does have a couple of issues:
- In the client it defaults to a “Select one item” approach and you have to check a box to allow you to select multiple items. In WebApp, it allows you to select multiple items. So I had to add a contextual formula to feed back info to the users if this happens.
- There are actually hidden rows in the worksheet to allow the Page Field to be changed. Even though the options selected always end up in the page field line, it seems to need extra space to refresh the table.
At any rate, it’s not perfect, but it seems to work. Here’s a look at the file:
on March 9th, 2011 at 8:23 am
Looks good to me–pivot tables are genius. How do you like SkyDrive? I’ve heard nice things
on March 9th, 2011 at 10:25 pm
The biggest issue with using PivotTables for this is that in the web app you can’t restrict the user to only selecting one item. So they can quickly mess up the sheet by selecting two items from the list. Hence resorting to the IF function to tell the user if something goes wrong.
I think SkyDrive has lots of potential. For this kind of stuff it works quite well, although I do wish I had the ability to prevent a full download.
on August 4th, 2011 at 12:34 pm
Great post.
I have been ubale to get SkyDrive to work for me. I tried searching Goolge, but I cannot find a reason. This post helped, but I still have more to learn I guess.
Thank you,
Christopher
on August 4th, 2011 at 1:14 pm
Hi Cristopher,
What issues are you having? And what browser do you use?
on October 27th, 2011 at 11:25 am
Why is it that if you upload an Excel spread sheet to Skydrive, it only uploads the data, not the functions. This makes it pretty useless for spread sheets that have any sophistication. (Unless you re-write all the formulas after uploading.)
on October 27th, 2011 at 11:49 am
Uh… I can’t say that I follow… I’ve had very few functions stripped, and those only if they weren’t supported at all (so a re-write wouldn’t help.) What kind of functions are you losing?
on March 12th, 2012 at 9:44 am
Ken,
Drop down menus, data validation, conditional formatting, about 50% of my hyperlinks, buttons, user forms, macros, to name a few. The service is useless to anyone doing anything more complicated than simple data entry.