Week 8 Review

Hey everyone! Welcome to week 8. Almost there! Been a tough week to get into the lessons because a lot of clients had due dates to hit past week alone. I understand that Thanksgiving is coming up and the holiday season as well. I know everyone’s been trying to prepare their online stores and sites for the coming rush of orders and services. This week I wanted to focus on excel and google sheets. I didn’t realize that it is hands down the most powerful microsoft office software out of all of them combined. Excel and google sheet alone can run a fully functioning business and more! There’s so many more functions and tools i’ve learned from this lesson. It really makes me want to dig into it a little more and explore what else I can do with excel and google sheets. Let’s get started.

The first lesson we talked about is sorting and filtering. Not much there except getting data into the sheets and doing exactly that…sorting and filtering. Using Google My Business, you can use excel and google sheets to properly sort and filter your insights according to whatever category you want using a special add-on google browser. It’s called search analytics for sheets. Change the dimensions and limit the rows and columns to see the proper insights. Continuing in Google My Business, we go over the different sum variations. Even though both programs are fairly different, they’re ultimately the same thing when it comes down to functions or being used as a tool. We went over the different sum commands whether its just summing a range, sumif or ifs depending on multiple conditions. It also goes over absolute and relative cells when putting your formulas in which is definitely a great concept to know. After the sum commands, it continues to go over the count commands. it’s pretty similar to sum commands also running with countif and countif with multiple conditions. There’s not much difference there but useful nonetheless. The next lesson went to tables and calculated columns. It starts with formatting headers. Not difficult at all. Main purpose is just to keep things more organized and easier to read ultimately.

The lessons continue to go over pivot tables. It’s pretty basic and not difficult at all. Another way to stay organized and get the most of your data from analytics. Being able to move metrics around and get them exactly where you want it. Very useful! It continues to go over using timelines and filtering power to help you better read the data. It shows you segmenting throughout using the data and also slicers to visually help them with the filtering.

In the next lesson, we continue to learn about excel and google sheets but in the frame of google analytics. No more google search console. In this section, we find different ways to tie data together. The main focus for now is google analytics. We’re getting the google analytics data and getting it into excel and google sheets. One thing they go over is getting rid of duplicates. Another easy task to do. It’s as simple as choosing remove duplicates from a menu bar and selecting the column you want to check. Very simple. Another tool that was on the lesson was using text to column. The main purpose of text to column is to split a cell in to different columns to make it more organized and see that single cell to be separated. If you had long URLs on one column and you wanted to break them up, this would be a great feature to use to better organize and see them all split up.

In the next lesson, we go over the use of vlookup. Vlookup stands for vertical look up and it’s when you use it as a function to look up a certain value within some column and return a different value in the same row. It will make your life a lot easier when you use excel or google sheets. especially during keyword research. I’ve found it very useful when using vlookup when you’re doing the column technique for keyword research. You pretty much use your core keyword and then use the different modifiers with the core word and use vlookup to help fill in the remaining cells which will in turn get you more keywords with using the same core keywords with different modifiers. The next functions are called index and match. They’re pretty much the weakness of using vlookup. It’s a matter of just using the right formulas and find exactly what you’re looking for. Great thing about excel and google sheets, you’ll be able to see what is needed to be in the formula. So if some of these topics aren’t clear, it’s very easy to look them up. It also goes over xlookup but it’s not available in google sheets, just excel. It cover everything from vlookup to index and matching.

The next section is about sparklines. They just add a little zing to your spreadsheet. All it really does is shows you a visual representation of some data on your excel or google sheets in graph from in one single cell. Very cool to have and also easy to see visually how the range of data is doing. It could be a line graph or a bar graph. You can show the points on the line as well. Very cool! The next section is conditional formatting. The best thing about this is it shows even more style and zing in your excel or google sheet. You can pull up heat maps and see the different data in value format by using spectrum colors. I love how it can be used to see visually how you’re doing with a particular set of data. One thing we used this function on is for a restaurant’s cost / sale sheet. They had to keep a certain margin percentage for the whole month. So every day when they input the proper cost and the end of the night sale value, the conditional formatting feature would change colors depending if they hit the margin for that day. Green for yes and red for now and it also shows the exact value of that margin. We also took it further and added the variance of how far they are from the margin as well.

The lesson continues with string functions. Functions after functions that relate to text strings. The 7 major functions the lesson went over is len, substitute, find, search, mid, left, and right. The len functions (or length) is great use for seo for example. Will show you the length in characters of a particular metatag for example. The substitute function is simply replacing data or text from something what it was to what you want it to be. The find and search function are practically the same but the find is case sensitive. Search is just more for identical characters. The next function is the mid function. This function is used for pulling out text or data somewhere within whatever string you’re looking at. Left and right function just gives you the most left and the most right texts in a string. Very easy functions to remember and to execute as long as you follow the proper formulas.

Error trapping is the next section of the lesson. Watching out for errors is very important. Having a lot of errors in your excel or google sheet may cause you to make the wrong decisions. Very not good! Iferror function is very helpful! Checking the ranges and adding validations will efficiently help you protect yourself from making mistakes. Adding these boundaries and locks will help prevent you from even accidentally inputing the wrong data. I love that you can add error messages to an excel or google sheet. Something that we have also implemented to help some of our guys stay on point with the data they input. Using certain resources online that you can export in to a csv file is the best way to collect data and manipulate what categories you’re looking for. Most seo tools should be able to export data into excel or google sheets.

I love this lesson most of all. Very useful with a lot of the things we’re doing as an agency. It’s really great. Till next week everyone and thank you all! Stay tuned for week 9!