Use the Data Validation feature for Clean, Detailed Client Reports
Published: March 26, 2014
Author: Spencer Fair
The Data Validation feature in Excel can be a very useful tool, particularly with client-facing reports. One of its features allows you to create a drop-down menu of choices that you can then have formulas use as a reference cell. This allows a client or user to view different data by selecting different choices from the Data Validation menus.
You can reference these drop-down menus in conjunction with other formulas, etc., to give the client-facing sheets a lot of choices and data without making the sheet too cluttered. It also helps to consolidate data into smaller tables that can be adjusted based on the drop-down menu choices.
You could also use these sheets to make spec sheets or product sheets where you can drill down using a group of drop-downs to a specific version of software that will then give you the options of drivers specific to that software.
These drop-downs allow you to build a sheet that has a ton of options but does not show all the options all at once, making it look cleaner and more refined.
The Data Validation option can be found in the data ribbon under data tools:
We are going to focus on the list option in the Data Validation menu:
You then enter in a cell range on the source that contains the list of options you want to offer. I usually create a separate sheet with all my lists on it; this keeps client-facing sheets cleaner and less prone to breaking.
The Data Validation drop-down menu will look like a normal cell of data unless you click on it; then it will show a drop-down arrow that you can click on and see your list of choices:
What I normally do is add in a fill color and a border around the Data Validation drop-down to make it a bit more noticeable. It is also a good idea to always have an “All” option at the top of your list to allow the data to be viewed as broadly as possible.
Instead of just selecting the data from the list menu, you can use a formula in its place so that you will no longer have to update the cell range the Data Validation drop-down menu looks at. This allows you to add in more drop-down options without having to modify the actual Data Validation cell.
Use this formula in the “Source:” field of the Data Validation menu (modifying it, of course, for your needs):
=OFFSET(Lists!$C$2,0,0,COUNTA(Lists!$C:$C)-1,1)
With this formula you can set the data range to adjust based on whether the formula finds data in the columns’ cells.
The Offset formula can be used set the range of data dynamically by adding a Counta formula into the height section of the offset formula.
The Counta formula will count the number of cells in the set range that have data in them. Counta combined into the offset formula allows for it to auto-adjust as you add more options to the list.
=Offset(start of your list,0,0,counta(column your list is in)-1,1)
The -1 after the counta formula is to compensate if you have a header in the column. If you have more than one cell of data that should not be counted, add it onto the subtracted number; if you have no header/extra cells with data, you will not need the -1 at all.
Once this formula is set up for the Data Validation, you should not need to adjust it again.
What if you want your Data Validation drop-down lists to change based on the choices you made in the drop-down lists above it?
It is fairly easy to do once you have all the different lists set up for each of the different choices.
In the Data Validation source field, you would still use the same offset formula but wrap it with if statements that link to each of the different lists you want to show based on your other Data Validation drop-down menu choices.
Here is an example:
=IF($D$10=”Google”,OFFSET(Lists!$L$2,0,0,COUNTA(Lists!$L:$L)-1,1),IF($D$10=”Bing”,OFFSET(Lists!$M$2,0,0,COUNTA(Lists!$M:$M)-1,1), OFFSET(Lists!$N$2,0,0,COUNTA(Lists!$N:$N)-1,1)))
This formula checks the Channel drop-down for either Google or Bing. It first checks if the drop-down menu has Google selected; if it does, it will show the list created for Google-only campaigns. If the channel drop-down is not Google, it will then check for Bing; if it is Bing, it will show the list created for Bing-only campaigns. If the formula does not find Google or Bing (meaning All was selected), it will default to a list with both Google and Bing campaigns in the list.
This makes it so that you don’t have extra choices in your lists that don’t apply with the choices made in the earlier drop-downs.
Give it a shot, enjoy your clean new reports, and drop a comment if you have any questions come up!
