Tracy van der Schyff

#Office365Challenge – Data Validation in Excel: As much as I love SharePoint, some things still need to happen in Excel. Should you require users to complete certain fields – then Data Validation is for you. This allows you to validate the data users enter into certain fields on your spreadsheet. We all know the saying “Garbage in – garbage out” – so your output is based on the quality of the input.

In this example, we’ll be using the List option (dropdown). In your Excel spreadsheet you need to create the list (1)- I always do this on a separate sheet and then hide it. Type the question you would like to add (2) and click on the field where the user will enter (select) the answer (3). Now click on the Data Tab (4) > Data Validation (5) and then on Data Validation (6) again.

The Data Validation settings box will open, select List (7) under Allow. Click in the Source field (8), now navigate to where your list is (with your mouse), and select the full list (9). This could be on a separate tab, you can still navigate there to select the data. Click on OK after you’ve added the Source. This is how your dropdown will now appear on your spreadsheet (10).

You can also change the settings regarding Input Message and Error Alert:

And that’s it for today;

Overview of my challenge: As an absolute lover of all things Microsoft, I’ve decided to undertake the challenge, of writing a blog every single day, for the next 365 days. Crazy, I know. And I’ll try my best, but if I cannot find something good to say about Office 365 and the Tools it includes for 365 days, I’m changing my profession. So let’s write this epic tale of “Around the Office in 365 Days”. My ode to Microsoft Office 365.

Keep in mind that these tips and tricks do not only apply to Office 365 – but to the overall Microsoft Office Suite as well as where applicable, SharePoint.