A lot of the data you enter into your Google Sheets tables may be repetitive, like tracking whether an influencer you’ve reached out to for a partnership has agreed to working with you or not.
It can get tedious to go in and type each yes or no as time goes on, which is where a critical tool, the drop-down list, becomes your best friend.
In this post, we’ll go over how to add a drop-down list to your own Google Sheets data set to help save time.
How to Add a Drop-down List in Google Sheets
As mentioned above, a drop-down list can help you easily change elements of a cell when the content is repetitive.
The example data set for this walkthrough (as shown in the image below) is tracking the progress of marketing campaigns on different channels and the stage they’re in; not yet started, in progress, or completed. I want to create a drop-down menu so I can easily go in and change the status of the campaign as time goes on.
Before going through the steps, it might be helpful to see what a drop-down menu looks like so you can contextually understand each instruction. The gif below shows a final drop-down menu and how it applies to the sample data set.
Let’s go over how to add a drop-down list to your Sheet.
1. In the toolbar header, click Data.
2. In the drop-down menu, as shown in the image below, select Data validation.
3. In the Data validation dialog box, enter the range of cells you want to have a drop-down menu in Cell range. For this example, I’m entering B2:B10 for cells 2-10 in column B.
4. The next step is to enter the data range that you want to be included in the drop-down menu. Select List of items, and add in your menu values. For this example, this is where I would input Not yet started, In progress, and Complete.
Once you’ve entered your values, click save.
5. Each of your cells should now have a clickable down arrow, as shown in the image below.
For the example table, I can click on each down arrow and change the status of my campaigns as time goes on.
How to Edit a Drop-down List in Google Sheets
If you need to make changes to your drop-down menu, the process is rather simple.
1. In the toolbar header, click Data and then Data validation.
2. In the Data validation dialogue box, simply input the changes you want to make. For example,
- If you want to change the items in your drop-down menu, navigate to Criteria, and make your desired changes.
- If you want to change the column the drop-down menu is in, change the cell numbers in Cell range.
- If you want to delete your drop-down menu altogether, select the column the menu is currently in and click Remove validation.
Always click save after making all changes.
Color Code a Drop-down List in Google Sheets
Color coding is helpful when it comes to interpreting results at a glance. You can do this with your drop-down list by creating conditional formatting rules, and below we’ll explain how.
1. Select the cells your drop-down menu is in and click Format.
2. Select Conditional formatting from the dialogue box, as shown in the image below.
3. In the Conditional formatting rules sidebar on the right-hand side of your screen, navigate to the Format rules section.
4. In the Format cells if menu, select Text contains…
5. Enter the first element in your drop-down list that you want color-coded. In the image below, I’ve entered Completed as my value and set the color to Green.
6. To set a color for each of your list items, select + Add another rule and repeat step 5 for each value. For my chart, I’ve set In progress to Blue, and Not started to gray.
7. After you set each of your rules, changing the drop-down menu item to a different value will automatically change it to the correct color. For example, if I change Not yet started to In progress, it turns from gray to blue.
Once you’ve created your drop-down menu and color-coded it for easy interpretation, you can continue to track the progress of your different marketing activities and save time while doing so.
How We Increased a Client’s Leads by 384% in Six Months by Focusing on One Topic Cluster [Case Study]
The author’s views are entirely his or her own (excluding the unlikely event of hypnosis) and may not always reflect the views of Moz.
Content marketing is an essential part of any SEO strategy. Without it, how are you going to attract customers looking for answers to their questions, and who are potentially in the market for your products or services?
At Tao Digital Marketing, we’ve recently generated some great results for one of our clients operating in the business financial space, The Insolvency Experts, mainly by focusing on just one “cluster topic” that was a huge money maker for them.
When looking at six month comparison stats (August 2021-January 2022 to February-July 2022), we’ve achieved the following:
Leads: 95 to 460 (384%)
Clicks: 4,503 to 23,013 (411%)
Impressions: 856,683 to 2,033,355 (137%)
Average position: 33.4 to 23.6 (increased almost 10 spots)
This was mostly achieved by absolutely hammering one topic area: company liquidation. In this case study, we’re going to explain how we did this step by step, so that hopefully you can generate similar results for your own business!
If you really break it down, the objective of all SEO consultancy work is essentially the same: increase the number of leads for a business. This was our ultimate goal.
It’s not just as simple as that, though. We all know you can’t get to number one on Google overnight. So, like other SEO geeks out there, we tracked our successes through additional factors such as clicks, impressions, and average position, to show our efforts were worthwhile.
In January this year (2022) our goals for the next six months were as follows:
Leads: Just over double from 95 to 200 (110%)
Clicks: 4,503 to 13,500 (around 200%)
Impressions: 856,683 to 1,700,000 (around 100%)
Average position: 33.4 to 25 (around eight spots)
Insolvency Experts’ audience is primarily directors of UK businesses that are going insolvent, closely followed by business owners looking for financial advice. The majority of Insolvency Expert’s cash flow comes from formal insolvency processes, such as liquidation, administration, and CVAs (Company Voluntary Agreements), so it was really important for us to push these areas.
1. Research “company liquidation” search volume and related queries
We first picked this client up in November 2020. Initially, our focus was on the basics: updating all the top level pages (such as service pages and guides) to make sure they fit the intention of the user and clearly explained the services that Insolvency Experts offer.
Researching what works well at present
One of the pages that our content team updated was their company liquidation guide. After updating, the page started to perform very well in the SERP, and ranked at position #4 for “company liquidation”. Clearly, this sort of content was working, and we wanted to hit it even more.
After pulling some research together, one of our strategists proposed the idea of a “Company Liquidation Content Hub”, as the company liquidation guide was ranking for a lot of long tail questions:
After cross referencing with the monthly search volume for these questions, she added some of these as H3s within the guide to see how they would perform. They resulted in so much more traffic that she decided they warranted their own individual guides, hence the idea for the hub. This would mean we weren’t putting all of our eggs into one basket, and that we could also internally link all of them together for users wanting to read more.
Users that are further down the marketing funnel don’t want to scroll down a huge guide to find the answer to their specific question, and we were certain that this would positively affect bounce rate. We therefore made sure that nine times out of 10, the H1 contained the question that was being answered.
In order to further target those at the bottom of the marketing funnel who want to speak to someone quickly, we placed regular “Contact Us” CTAs throughout the content so that they don’t have to scroll right to the bottom of the page to get in touch with Insolvency Experts.
Undertaking a competitor analysis
We also conducted a competitor analysis on this topic, focusing on three key players in the industry that were all ranking well for the phrase “company liquidation”. We found that the key competitors had the following:
Competitor A – 38 indexed articles on liquidation
Competitor B – 23 indexed articles on liquidation
Competitor C – 47 indexed articles on liquidation
Insolvency Experts only had six indexed articles on liquidation at the time, so it was clear we needed to be on their level – this was an obvious content gap.
Pitching the content hub to the client
We suggested this idea to the client alongside a forecasting spreadsheet created by our founder, in order to justify the resource that was needed to push the client as high as possible in the rankings for company liquidation.
This spreadsheet broke down a huge list of keywords alongside monthly search volume, average click through rate for positions 1-10 on the SERP, domain authority of competitors who are currently ranking for these keywords, and average conversion rate on the site at the moment.
This unique formula would then allow us to explain to the client that for X amount of work, we predict we can get you to position X in X timeframe, and this would result in approximately X annual revenue. After pitching this to the client alongside infographics and current performance statistics, they told us they loved our ideas and agreed to let us go ahead.
2. Plan the content after client approval
After the client gave us the go-ahead, the next step was to plan all of this work based on search volume, and therefore priority order.
It’s easy to get lost in all the data within SEO, so it was incredibly important for us to have a solid plan and timeline for these changes. Topics were going to range from How to Liquidate a Company with No Money through to Administration vs Liquidation.
How we communicate planned works to our clients
In order to orchestrate clear communication between ourselves and our clients, we create a Traffic Light Report, which is a live Google Sheets document detailing all work to be undertaken for the current and next quarter. This is split into sections for technical SEO, content, and digital PR/link building (the three pillars of SEO).
This includes justification for each change we make, as well as a link to any live changes or documents. It also details when this will be done and if the action is with us or the client. The tasks are coloured in green for live changes, yellow for action needed, orange for in progress, red for anything on hold and clear for not started.
Here’s an example of what the content section of Insolvency Expert’s traffic light report looks like for their current quarter (July-September 2022):
Scheduling the tasks
We then scheduled these topics for our various content writers to work on using our project management software, ClickUp. Within each task we placed a link to a skeleton document consisting of H1s, H2s, and H3s, as well as a title, meta description and keywords to include.
3. Write the content while implementing technical SEO
By this time it was around April 2022, and it was time for us to fully attack the content portion of our task list. Since then, we’ve written 18 pieces of content around company liquidation, and still have quite a few left to go before we consider this area of focus complete.
Analyzing as we go along
Once we covered the big topics in the first couple of months of writing, we started to use Low Fruits to find smaller queries which are estimated at around 10 or fewer monthly searches. We’ve had a lot of success targeting lower search volume phrases, as these users seem to be more focused and lower down the sales funnel, so are more likely to be better engaged and convert better. A lot of the time they are pleased that you have answered their very niche question!
The below is a screenshot from a keyword analysis. We trawled through hundreds of keywords to pull out the ones relevant to the client.
We then used Low Fruit’s Keyword Extraction and SERP Analysis tool to give us further details on a select few key terms.
These terms are shown as having a search volume of either 10, less than 10 or 0. Of course, we know that this is still hugely important to cover, and targeting these will bring in a very niche reader who is much more likely to convert due to the nature of the long-tail queries.
Finalizing the hub
Our plan is to finalize the hub this fall, and ensure that everything is internally linked. There will also be a menu change to make the addition of the hub very clear. See screenshots below for the current hub vs. how it will be presented once all content is ready (screenshot taken from their staging site in Kinsta, our hosting platform where we make design changes so that the client can approve them before they go live).
Current ‘hub’ in the menu:
How the hub will look once all content is complete:
As part of our content process within ClickUp, we have a recurring task to check a new URL in Google Search Console two weeks after upload. This allows us to see if we have the “Google Spike of Acceptance”, which is a sharp incline of impressions/traffic indicating that the content will do well, before it falls then slowly rises again.
If we don’t see this spike, we carry out multiple checks, including: Is it an orphan page? Are there any technical errors? Is it indexed? If it is not indexed, we push the URL through Index Me Now.
If the issue is just that the piece isn’t getting picked up, we will take another look at the content to see if there is something else we can do to improve it, e.g. tweak the H1 or expand the content.
4. Build links to the relevant pages and homepage
We wanted to really hone in on generating links for our company liquidation page. The page has 36 backlinks, many of which were built through link building efforts. This was largely done by working with business site publications and creating natural anchor text that would help with certain keyword rankings.
As well as building links specifically to the company liquidation page, we also built links to the main URL in order to boost overall domain authority. This was done through answering queries through platforms such as HARO and Response Source, as well as working with the client to create relevant, time-specific thought leadership pieces. Here’s an example of a HARO request we responded to, the topic being “Recession-proofing tips for small businesses”:
Although the site’s domain authority tends to fluctuate between 30-33 depending on links lost and general algorithm updates, the links to specific pages have still resulted in an increase in rankings, detailed further below.
Results compared to objectives
Although we knew that our strategy was going to work well based on our experience with our other clients, we were very pleasantly surprised by the huge positive effect our work has made, which enabled us to smash the targets we set!
Goal: Increase from 95 to 200 (110%)
Result: Increased from 95 to 460 (384%)
As a result of creating incredibly useful, lengthy content and placing regular CTAs throughout the content, we managed to almost quadruple the amount of leads coming through to the client in the space of just six months.
In the six months before our liquidation project began, our Leads Dashboard within WhatConverts shows that Insolvency Experts had five liquidation leads via phone call and 10 leads via their contact form on a liquidation-focused page.
In the six-month period since we’ve been working on the content hub, they have had 38 liquidation leads via phone call and 52 leads via contact form on a liquidation-focused page.
Result: 660% increase in phone call leads and 420% increase in contact form leads.
Previous six months:
Goal: Increase from 4,503 to 13,500 (around 200%)
Result: Increased from 4,503 to 23,013 (411%)
By creating highly relevant content that matched the user’s search intent, we managed to almost quadruple the clicks over the space of six months, doubling our original 200% goal.
The site has received 29,400 clicks overall across the past 12 months. Below, you can see the huge spike in clicks and impressions from January onwards when we really started to focus on the liquidation content.
Goal: Increase from 856,683 to 1,700,000 (around 100%)
Result: Increased from 856,683 to 2,033,355 (137%)
Again, by creating highly relevant blogs, Google started to understand the relevancy of our content, so the number of impressions hugely increased. Along with the 137% increase above, over the past 12 months (August 2021-August 2022) the site has received 485,000 impressions for the query ‘liquidation’ alone.
The main company liquidation guide that we updated had a total of 732K impressions over the past 12 months, too, with a huge spike from February onwards, when we updated the guide.
Goal: Increase from 33.4 to 25 (around 8 spots)
Result: Increased from 33.4 to 23.6 (increased 10 spots)
This increase is due to the relevancy of our content and the amount of keywords each piece ranked for. As mentioned, the main company liquidation guide has worked incredibly well, ranking for 181 keywords, 67 of which are page one (37%). It now has the number one spot for the term “company liquidation”. See below for an example of queries the page is showing up for.
The page also shows up for six featured snippets as a result of us implementing FAQ schema.
335 clicks and 93,663 impressions have come from the FAQ rich results alone.
In the six months before we updated the guide, it pulled in around 650 clicks and 227K impressions. In the six months following, it brought in around 1,180 clicks and 382K impressions. We’ve practically doubled clicks on one single guide.
As mentioned, this particular piece of content has 36 backlinks, and actually ranks ABOVE the official UK government company liquidation guide, which has a domain authority of 93 (about 60 higher than ours). Clearly, we’re meeting the searcher’s intent and giving them what they are looking for.
In the six month period before we started work on liquidation, Insolvency Experts had an average click through rate of 0.5%. Over a six month period of us working with them, this more than doubled to 1.2%.
Another success worth noting is that 3 out of 6 of our latest articles have an average page view duration of between 9 and 10 minutes! The other half are averaging around 5 to 6 minutes, which is still very good. Clearly, users are wanting in-depth information on this topic.
The “What happens to a director of a company in liquidation?” guide, which went live in May, is now the fifth most clicked page on the site. when filtered on GSC by the term “liquidation”.
Overall, we’re extremely pleased with the results we generated, and so are Insolvency Experts — the company liquidation department is now inundated with queries and they are rushed off their feet!
How We Increased a Client’s Leads by 384% in Six Months by Focusing on One Topic Cluster [Case Study]
Google Core & Product Review Updates Finish Rolling Out
Update for Capcom Fighting Collection with More Features Available Now
UK eyes big TikTok fine over child privacy lapse
Why B2B Companies are Entering the Editorial Space [& What You Can Learn From Them]
Daily Search Forum Recap: September 26, 2022
Fact Checking: Get Your Facts Right
A Gummy’s Life Celebrates a Year on Xbox
In-house SEO vs outsourced agency talent: Who wins the debate?
14 Things That Can Hurt Your Site’s SEO Rankings [Infographic]
How to Create UTM Tracking URLs on Google Analytics
Google Is Not Yet Done Rolling Out The Helpful Content Update
How to Target Keywords With Blog Posts
Google On Why Helpful Content Update Seems Quiet
If You Love Escape Rooms, You’ll Love the Elaborate Puzzles of Zero Escape: Zero Time Dilemma
Why & How Machine Learning Took Over Paid Advertising
Google Updates Documentation On Meta Descriptions
The Ultimate SEO Checklist For Boosting Organic Traffic: 6 Highlights
Google Learning Video Structured Data Docs Breaks Out educationalLevel
How to limit your reliance on canonicals and boost crawl efficiency
SEO6 days ago
YouTube Is Monetizing Shorts With 45% Revenue Split
GAMES7 days ago
Coming to Xbox Game Pass: Deathloop, Slime Rancher 2, Valheim, Grounded’s Full Release, and More
TECHNOLOGY6 days ago
Benefits of Blockchain To Enhance Data Security
SEO6 days ago
5 Common Mistakes and Tips to Avoid Them