Manage your spreadsheets with Data Validation in the Excel Web App (aka an awesome tool to prevent your friends from messing up your data)

This post is brought to you by Cathy Harley, a Program Manager in the Excel Team.

I moved out to the West (best) Coast about a year ago to join the Excel team, and I’ve absolutely fallen in love with Seattle.  I’ve been nagging all of my East Coast college friends to come out here for a few months now to check out how awesome Washington State is (and maybe even convince a few of them to consider moving out here as well). 

My East Coast friends are mostly part of the dainty, educated, socialite crew, so I’ve spent quite some time pondering the right Seattle event for all of them to visit.  I finally found the perfect match: the Extreme Warrior Mud Massacre Run! 

Once everyone was on board, it was time to order our Extreme Warrior Mud Massacre Run team battle suits—our V-neck cotton T-shirts.  Since everyone was taking the time to come to visit me, I volunteered to take care of the shirt order.  This wasn’t my first time around the T-shirt ordering block, so I knew about some of the common annoyances that come with the task:

1. A bombardment of emails asking about the available sizing (is there an XS? What about an XXS? I’m a split between a Medium and a Large, is there a M/L size?)
2. The process of manually aggregating everyone’s size information to make the final order.  

Luckily, with Data Validation and Excel, I can avoid both of those problems!

Part 1: Setting up your spreadsheet

For this first step, you’ll need to use Excel 2013 Desktop. Let’s start with just the basic information that I’ll need to collect from everyone: first name, last name, email, and size. 

The data will be easier to work with if it’s formatted as a table, so I’ll do that by highlighting the header row and clicking Format as Table in the Ribbon.  Formatting your data into a table allows formatting and formulas to be copied down the entire column, and it allows easier analysis of the data later with easy sorts and filters.

Make sure to check the box for My table has headers.

Voila!

Now for the fun part where we’ll tackle our first problem: A bombardment of emails asking about the available sizing.  Whenever you shop for clothing online, you’re given a drop-down list of all the available sizing for a product.  It’s obvious right away what sizes the product is available in.  I’m going to do the same thing here and give a drop-down list of available sizes so that my friends have to choose from a list. 

I know the sizes that I can order from the T-shirt company are XS through XL. So, I’m going to enter these off to the side of my spreadsheet. 

Now the trick is to get these choices to show up in a drop-down selection for the size column of my table!

Start with selecting the first cell under the Size Header in your table.  Now you’ll want to click the Data tab in the Ribbon.

Click Data Validation and then select Data Validation.

Under Allow, select List.

For Source, select the range of cells where you’ve listed your sizes.  Simply highlighting the appropriate cells in the grid with your mouse will do this automatically!

Now click OK and you’ll be set.

When I go in and enter my information, there is an awesome drop-down menu under the Size column for me to choose my size from!

Part 2: Saving and sharing your spreadsheet on the web

Now I can save my spreadsheet onto SkyDrive to share with my friends.  This way, everyone can edit it simultaneously and everyone’s changes will be saved automatically!

If you already have a SkyDrive account set up, this is really simple.  Simply go to File > Save As and select your SkyDrive. 

If you don’t have a SkyDrive Account, you can register for an email address at www.outlook.com and you’ll get one with 7GB of free storage.  You can also upload your file directly from SkyDrive, rather than from inside the app, if you’d rather do it that way.

Once I save to SkyDrive, I can click Share and simply enter the email addresses of my friends. 

Done! All of my friends will get the email and be able to edit the spreadsheet with their information from wherever—even their phones!

I can see my Spreadsheet on the web app here:

Part 3: Aggregating everyone’s information to place an order

Now all of my friends have had enough time to fill out the spreadsheet and it’s time for me to place an order. 

I tend to make a lot of mistakes, and since my friends are VERY particular, I want to make sure I don’t mess up the order when I add up everyone’s sizing information. Therefore, I’ll let Excel do the trick for me. What better way than a PivotTable?

First, I’ll select my data.  Then, I’ll go to the Insert tab on the Ribbon and select Recommended PivotTables.

I’m given a few good suggestions:

The first one looks like exactly what I want, so I can select it, and now I know how many of each shirt to order.

Perfect.

Now that I’ve finished my T-shirt order, I can get to training for the Extreme Warrior Mud Massacre Run. Too bad Excel won’t be automating push-ups for me anytime soon. 

–Cathy Harley, Excel Program Manager