The ability to track and understand every click that a user on your website makes is only a blessing if you can quickly turn that data into actionable information. Drowning in a sea of data or spending hours processing it isn’t going to make anyone’s life better. This is where a set of useful excel tricks come in handy.
Let’s take a look at some quick excel tricks and tips that people find useful when processing their SEO data. If you want more tips it’s worth looking at this guide to Excel.
1. Using Filters
Most people know about sorting their data. That’s useful for smaller datasets. For larger datasets (like lists of URLs or traffic sources) sorting doesn’t really work.
What you need to do is only show the specific data that you’re looking for which is where FILTER comes in. You can find the FILTER function in the drop down sort menu in Excel.
The image above shows the steps to use a “Greater Than Or Equal To” filter although as you can see there are lots of other possibilities. One caveat is that each time you set up a filter you need to be sure that you have removed the previous one.
Typical uses for a filter are only showing data after a certain date or only showing data where a figure is either above (for looking at the most important items) or below (for looking at the least important items) a certain threshold.
If you want to extend your Excel tricks then you can find information on FILTER functions here.
2. Trim
There is nothing more frustrating than working with text if the spacing of the text isn’t correct or worse if it contains spaces before the first letter. This means that sort and filter functions won’t work properly.
The TRIM function will sort this out for you very quickly.
You simply use the formula =TRIM(TARGET CELL) and the result will be the original text but without spaces before or after the text and with only one space between each of the letters.
TRIM is very useful when working with lists of search queries as they are often mistyped.
Again, if you want to extend your Excel tricks then you can find more information on using the TRIM function here.
3. COUNTIF
This final tip is again a lifesaver when working with large volumes of data. What would these Excel tricks be worth if we didn’t make sure you eliminate the headaches from your data processing, right?
It allows you to very quickly count items in large lists according to criteria. So for example, if you want to see how many of your pages have received more than (say) 50 visits, or how many of your title tags are longer than a certain number of characters.
The formula that you use is =COUNTIF(range, criteria) where the range is the list that you want to count in and the criteria give you what you want to count.
The image above shows someone using a COUNTIF formula to see how many search queries they rank in positions 1 – 4 for. The sheet has 1,000 rows and the answer is 98 and so even if you sorted the data is would take a long time to get the answer.
If you want to deepen your Excel tricks then you can find more information on using COUNTIF in this article.
3 Genius Excel Tricks For Faster Website SEO Results
COMMENTS