Preparing the data

1. Preparing the data

We have now made it to the home stretch. In this chapter, we will apply everything we have learned throughout the course to build an engaging dashboard that can be used in key decision making. We will start by preparing the data for the dashboard using data validation and regular expressions.

2. Digital marketing data set

Before we dive into the tasks, let's revisit the digital marketing data set. We will be analyzing impressions, clicks, and cost. Recall, impressions are the number of views for all the ads in a campaign, clicks are the total number of times an ad is clicked on in a specific campaign, and cost is the total amount of money spent by each campaign.

3. Add a source selection dropdown

Our first task is to add a source selection dropdown. This will be important, as the value selected in the cell will help to dynamically filter the charts. Filtering is important for any dashboard, as it is not very likely that a user will want to make an important decision based on all the data. The first type of data validation we will want to review is the list of items. As you can see from this example, the items have been manually entered into the data validation settings - making this an ideal candidate for any dropdown with a small number of items. The list of items is also best to use when the list will remain unchanged or constant, and it provides a simple dropdown menu, which makes filtering easy.

4. Add another source selection dropdown

List of items is good for a few items, but if we want a dropdown menu that can add items to the dropdown as we add them to the table, then the list from a range dropdown is the better choice. The list from a range data validation works well for dropdowns with a large number of items. It is the best choice when the list changes often and, just like the list of items, it provides an easy-to-use dropdown menu.

5. Filter data based on source selection

Now that we have the dropdown to determine what to filter, let us revisit how to go about filtering the data. Our goal is to filter the original data into a new filtered table, which we can accomplish using this formula. The first argument in an IF statement takes the cell that contains the dropdown menu value and makes sure that something exists. If a value does exist, then we will filter the original table, where the original table is in the range A5 through E10. We then define how to filter the original table by including a REGEXMATCH function for the values in the source column A and return any values that match the value in the dropdown, A1. Lastly, if the dropdown has no value, we simply return the entire original table.

6. Building a regex table

The last part of the preparation is to create a regex-driven aggregate table for some of the charts we will be creating in subsequent lessons. Recall the basic filtering formula. To aggregate the data, all we have to do is wrap the filtering with a REGEXMATCH formula in a sum or average function. Remember that we will be writing regular expressions in separate cells for the aggregate table, as it allows us to easily change the regular expressions without the risk of breaking the entire formula.

7. Let's prep the data!

Let's begin prepping the data for the charts!

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.