Finding a crucial data or metric for a report amid a sea of Raw data can be challenging, especially if your data is dispersed across several Spreadsheets and Tabs. Planning is necessary for effective project management, and Dynamic Dashboards may make this task simpler.
To make data-driven decisions, you can follow insights and receive a bird’s eye view of the data with the aid of a dashboard. An overview of your tasks and the ability to monitor the advancement of your Projects and Campaigns are provided through a Dashboard.
To provide users of your spreadsheet discretion over what data is presented, you can add your own dynamic charts. Any Google Sheets graphic and data validation can be used to accomplish this.
This section will walk you through building a dynamic chart for the first time in Google Sheets. In order to construct the data source for our interactive chart, you’ll discover how to configure data validation and a VLOOKUP column.
Creating The Drop-Down Menu
Let’s make a list of options the user can select from to control the chart. The chart will only display the data for the driver the user selects from the list of names in this case.
- Put the data validation drop-down menu next to the raw data table because it is in its own cell.
- Click the desired cell with your mouse, then select the Data > Validation menu option.
- Verify that the cell is chosen as the Cell Range. Then, as indicated in the following image, choose the name range A3:A11 as the criteria:
- Select “Save.” This will increase the right side of your input cell, G2, by one small gray triangle. When you click it, a user input menu for names appears:
You are halfway there now that the Google Sheets drop-down menu has been configured.
Dynamically Retrieving Data by Means of VLOOKUP
To chart the data matching to the name we’ve chosen, you should attach your table of data to this Google Sheets drop-down option. When it discovers a match, the remarkable VLOOKUP function looks for values and returns information. This works perfectly with a drop-down menu.
Using the value in the Google Sheets drop-down menu as the search criteria, create a table using VLOOKUPs to import the data from the raw data table.
Fill in cells H2, I2 and J2 using the following VLOOKUP formulas, respectively:
=vlookup($G2,$A$3:$B$11,2,false)
=vlookup($G2,$A$3:$C$11,3,false)
=vlookup($G2,$A$3:$D$11,4,false)
Create Dynamic Charts in Google Sheets
The next step is to make your Google Sheets Dashboard appealing and readable by customizing it. Your Dashboard is enjoyable to use since you can alter the font or add colors to charts and tables. A graph’s background can be colored, and titles can be displayed in several font families.
- Select the data, then select a chart from the Insert menu.
- Make sure to choose a column chart and to select Column G and Row 1 as headers and labels:
- Select insert and check your chart. Here’s mine below:
In my example, I made a dynamic chart to compare each store’s October and November sales. Every time you select an option from the drop-down menu we generated, you can view all the data.
Google Sheets Dynamic Drop Down
Before developing a dynamic dashboard in Google Sheets, you should be aware of a few things. The end result will boost the productivity and organization of your workforce. Google Sheets’ cells and algorithms make it simple to organize data. With the help of this data, you can create a dynamic dashboard in Google Sheets that looks polished.
If Data Integration is a challenge for your firm, be sure to look into FileDrop. With FileDrop, you may upload files directly from your Google Drive and they will be included with the name in the spreadsheet cell.
