Using Excel data validation list for dynamic graphs

Blog 5 - Media 1We have reviewed the SUMIFS formula that creates a summary of activities. In our latest example we used Vendor field to show the aging for each vendor on our Accounts Payable scenario. The only problem with that formula is the fact the user would have to actually enter each vendor on cell A2 for the formula to work. However, if we have a list of vendors, we can link them to the cell and allow the user to select their choice from a list.

Building on our last example, open or insert an empty sheet on the file you need to create the list.  For easy understanding during future reviews, we should always try to name the sheets; in this case, I named it Vendor List.
Blog 5 - Media 2
Go back to the sheet where you want the list inserted; I named it Dynamic Graph, for easy understanding. With the cursor on cell A2, click on the Data menu, then Data Validation. On the Data Validation box, choose List from the dropdown options. Note a Source box appears below the Dropdown. With the cursor on the Source box, simply click on the list you want to use (Vendor List sheet for this example). Press OK.
Blog 5 - Media 3

Return to cell A2 on the Dynamic Graph sheet and not there is now a list to choose from each of the vendors included in our list on the other sheet. Simply select any item from the list and watch the aging results change for each of them.
Blog 5 - Media 4

The Contextures website has some other useful examples for Data Validation or watch the video.

Graphing from summaries created with list is much easier than from Pivot Table. I will illustrate a simple graph creating with the intent to show the dynamic list effect on the graph. Keep tuned if you would like to make the graphs more attractive and presentable.
Select the data to be used on the graph. While the selection is active, click on the Insert menu, then Pie chart option. The graph is automatically inserted on the active sheet. This is Excel’s most simple default graph; thus, it is a good idea to personalize it a little bit. To do so the easy way, simply click on the graph and watch the Design tab on the menu.
Blog 5 - Media 5
Blog 5 - Media 6
Blog 5 - Media 7
Of course you can go much further with your graph customizations, but the idea is to highlight how simple it can be to review graphic images for several results, by selecting it from a list. This technique can be used in a variety of other ways. You may want to create a column chart to display your expenses for each month, and have a list for each expense you want to track such as dining out, telephone bill, groceries. You may also use this tool to track employee’s performance for different categories. Whatever the need is, just make it dynamic if you need to do it more than twice. Remember, there are always easy solutions with Excel. Thank you again for your time and hope you learned something new. Please remember to drop a comment and thank your following me.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s