Digital Analytics Scholarship @ CXL Institute — 5th Week Review

I have always thought of google sheets and excel as tools for data processing. but I never thought that someday, I will need to use them for digital marketing purposes. Now thanks to Fred Pike and CXL Institute, I know that a marketer should also know how to use it like a pro. I am going to tell you in advance that I’ve learned a lot in this course.

5- Excel and Sheets for marketers by Fred Pike

• This is a combination of Excel and Google Sheets courses for marketers. It is actually awesome to learn how other marketers use these two great products in their daily lives and I’m excited to start it.
• Fred started by using filter and sort in both tools. As Fred described it, it is a little bit more difficult to do these jobs in google sheets. But in the end, both tools are able to do them.
• I would rather use google sheets over excel because I think google sheets is more dated and suits my needs better. But you have your own choice.
• The next part was focused on the sum functions and by functions, I mean SUM, SUMIF, and SUMIFS. They are so easy to use and so capable of doing the work that you want to be done.
• Fred talked about the absolute and relative referencing which was very well timed and became relevant during the lesson.
• The great thing about the sum functions is that they are identical between excel and google sheets. You simply learn how to use them and after that, both tools are yours to use.
• Guess what was the next lesson about? It was about count functions and as you might know, they are identical between excel and google sheets. Pretty much like sum functions, we have COUNT, COUNTA, COUNTIF, and COUNTIFS for different conditions.
• And after that, Fred went to his beloved excel only to work on simple styles that addressed table headers. After that, he made a table out of the data in the excel file. To tell you the truth, I didn’t know the difference between tables and raw data. I always thought that every excel data in excel is always formatted as tables!
• Then we dived into pivot tables both in excel and google sheets. Fred constantly talks about the weaknesses of google sheets which I don’t like to hear about. But he likes to talk about it.
• I tried to create a pivot table that was introduced in the lesson in google sheets. It was damn easy and I did almost nothing to create it. The idea about rows and columns was very easy and I populated the cells with the data that I wanted.
• But after that, I tried to do the same job in excel on my mac and I failed to do so. I don’t know whether it was just bad luck or Excel had a worse user interface. Fred showed in the video that you can use “analyze data” to expand or contract the rows based on your will. He also used some table variations that he evaluated as great, but I didn’t like them that much.
• Believe me or not, Fred talked about calculated fields in google sheets and excel in the next lesson. The whole idea was to make fields with accurate data to use them in the pivot table.
• Luckily, this time both excel and google sheet had the feature to add calculated fields and I think google’s approach was much cleaner to this specific topic.
• Data for itself doesn’t convey any meanings, so we have to segment it to be able to have a more specific look at it. Excel gives us do this in its pivot tables and as it seems, google sheets can’t do that for now.
• Microsoft excel has three segmentation tools for pivot tables. One of them is filters that let you have a drop-down and segment data based on whatever you want. After that, we have a great thing in excel called Slicer. By using a slicer, you can have a simple visualized representation of your filters or controls. It is very much like a dashboard tool that is right in front of your eyes and you can mess with it visually.
• Finally, we have a timeline feature in the segmentation tools which only accepts time as input. By using a timeline, you can limit your data by a specific time. This too is like the things I’ve seen in the google data studio and reminds me of the features that are available in that google tool.
• Oh, I didn’t know that we have slicers in google sheets as well. They work much better than their equivalents in excel. You can use them along with filters to segment your data.
• And you know what? You can save your changes in the saved view to call it later. This feature reminded me of the saved reports in google analytics. They work in quite the same way.
• So finally Fred got the chance to brag about his beloved excel. He said that pivot tables in excel have some features that google sheets currently does not. You can make named tables in excel and when you update your columns, the data in your pivot table responds to those changes.
• There are also pivot charts in excel and google sheets doesn’t have it. And oh, I almost forgot to say that you can assign a specific tab in excel to filters that you apply to your pivot tables.
• Do you have a dataset that you don’t know if is clean or not? Then you would need to use the deduplicate feature of excel and sheets. It is rather easy and can remove duplicate entries.

And after that, you can use text to column features. Using this feature, you can make long strings of text into smaller ones. I was very excited once I learned what they can do for me.

• Do you know how vlookup works? I didn’t know so far and now I think I know. It takes an element as its input, searches for it in a column, and wherever it finds it, looks in another column for something that corresponds with it.
• It was actually fun playing with this function in google sheets and I think there might be another lesson specifically for excel.
• I learned two good functions that help you format error occurrences in a better way. They are IFERROR and IFNA. you can specify whenever an error occurs, a custom message is displayed.
• And after that, I was acquainted with INDEX and MATCH functions. Fred told us that these two functions kinda complement each other. They are with us to fix the inherent flaw that is there with the VLOOKUP function. The problem is that vlookup only searches for the query in the first column of the referenced document. But we might want it to look in another column.
• The match function only searches for a specific query in a column and returns the row number in which that query resides. This alone might not be that big of a work. But we have another function named Index which is hungry for rows and columns. We might be able to tell it about our desired column, but we definitely can’t tell about the row. This is exactly where we might want to use the Match function. Isn’t this great? They work hand in hand.
• The next thing that Fred excitedly talked about was a new function called XLOOKUP. He said that it may not be available in some versions of excel and I didn’t have it sadly. Xlookup is designed to fix the flaws of vlookup function.
• Vlookup works only if the column you are querying is the first column of the sheet. If you change the targeted column number by adding or removing columns, your vlookup won’t work anymore. And it doesn’t support error handling by itself. Xlookup tries to fix all of those problems.
• Wow! Sparklines were damn easy to implement. I thought they were very hard. You just specify the range that you want to be visualized and bam! It’s done. I personally can’t believe that.
• The only difference between google sheets and excel is that when you are in excel, you have to use an option in the menu to do the job. But when you are in sheets, a formula will do the job. It might not be very intuitive, but I like the formula method more.
• Conditional formatting was the next topic covered. It was very easy to apply in excel and rather difficult in google sheets. I’m a google sheet person, so it is obvious for me to compliment it. I think that google sheets and its custom formula section have lots of power and once you learn how to use it, it will serve you much much better.
• Playing with text was very fun in both excel and sheets. By using functions like substitute, find and search, mid, left, right, and len, I learned how to filter data based on my needs.
• We are getting closer to the end and this is a bit sad to know. But in one of the last lessons, I learned about error trapping. It wasn’t something that I was totally unaware of, but I learned some new and noteworthy things as well. For example, I learned about how to cross-check vertical and horizontal sums and validate their truth. Now I know the does not equal is equivalent to <> in google sheets and excel.
• This was kinda the end of everything. Chris talked about some useful resources and influential people in the field. He mentioned Ben Collins as a google sheet master which I will endorse as well.
• I don’t know what else to talk about. So, bye for now.