• Data Validation in Excel Services

    I’m a huge consumer of Data Validation in Excel. At this point, however, Excel services (the Excel webApp) is still so new that there are very few of the techniques that we normally use in Excel which are web compliant. This article looks at ways that we can implement data validation into an Excel services solution.

    Data Validation techniques I use when building workbooks 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. This is frustrating since, 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:

    Quote Originally Posted by Claire
    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
    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.
    Quote Originally Posted by Christopher Fennell
    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,

    Hi Cristopher,

    What issues are you having? And what browser do you use?
    Quote Originally Posted by Homer Shannon
    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.)
    Ken Puls said,

    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?


    I'm afraid that you must be logged in to comment or leave a testimonial. I wish it could be otherwise, but I'm trying to keep my site spam free for everyone's benefit. If you don't yet have an account it's completely free to sign up, and a very quick process. Simply click here to Register. Not only can you post a comment here, but it gives you full access to posts questions in our forum as well!


    If you already have an account, and just haven't logged in yet, what are you waiting for? Login Now!