<img height="1" width="1" style="display:none" src="https://www.facebook.com/tr?id=371557583225568&amp;ev=PageView&amp;noscript=1">

Excel Tip & Trick: Data Validation

Excel Tip & Trick: Data Validation

Steps to Create a Validation List:

Excel Tip of the Week; Data Validation is used to add data pick lists to your report design. For example, users can add a valid list of Fiscal Years and Periods for users to choose from. All formulas within the report can be anchored so the data will reflect the changes of years and periods.

Step 1: Create a list of the data you want in your pick list. Select from an Analysis Set such as departments, locations or company. In this example, we have dragged and dropped the Segment 3 List Analysis Set to create a pick list of Departments.


validation list 1

Step 2: Select the cell in the worksheet where you want the pick list to be displayed and then choose Data > Data Validation > Data Validation from the menu.


validation list 2

Step 3: In the Data Validation dialog, select "List" from the Allow drop down list. In the Source field, select the range of values you want displayed in the pick list. Click OK.


validation list 3

*NOTE: If you would like the source data to be on a different tab of your workbook, you will need to assign an Excel range name to the range of values and set the Source to the Excel range name.

validation list 4

Step 4: Click on the drop down arrow next to the cell and confirm the values are properly displaying.


validation list 4


About BizNet Software

BizNet Software, Inc. is a market leader for Microsoft Excel in-memory, cloud-enabled reporting and business intelligence. Founded in 1996 to take the mystery out of databases, BizNet focuses on Microsoft Excel reporting solutions guided by financial and business professionals with a real-world understanding of corporate financial, operational and technical requirements. The BizNet Excel Suite is the most innovative and intuitive reporting solution on the market. It creates a user-friendly, drag and drop environment with real time access to the most accurate and current data. Built upon fundamental business disciplines and controls, BizNet solutions make it easy for Microsoft Excel users to connect, analyze, and share real time information. BizNet Software is endorsed by more than 200 partners, 15 software publishers, and currently has 20,000+ users in more than 2,100 companies in 30 different countries.

Microsoft® and Excel® are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. For more information, visit http://www.biznetsoftware.com or call 888-803-5227.