CXL Growth Marketing Mini-Degree Review Week 8

This week, we explored excel for growth-hackers

Let’s explore some fundamentals of Excel:

1. Sort and Filter: The basics of making sense from data starts with sorting and filtering data. Sort can be done in an ascending and descending manner. With filters, we can reduce the view of the overall data to see the exact variables we want to.
2. Sum variations:
   a) SUMIF: sum based on one condition
   b) SUMIFS: sum based on multiple conditions with the use of relative and fixed values.
3. Count Variations: Count variations were very useful as well
   a) Counta: It will return the values of text count (Add -1 if the text has header), count returns the number of count of numbers.
   b) Countif: Count based on one condition
   c) Countifs: count based on multiple conditions (similar to sumifs command)
4. Table headers: We then talked about the general rule of keeping the headers containing numbers aligned to the right and the headers containing text aligned to the left. These rules can be changed in case a filter is applied in which case the headers containing numbers can be aligned to centre. Furthermore, if we showcase the data into excel tables then any change or addition gets implemented throughout the table in the new formatting, this is an easy way to add formula throughout the table.
5. Pivot Tables: I then got a refresher in pivot tables which can be created by data>pivot tables. We can then add rows and columns and filters to trim down our view. There are some really amazing features for segmenting the data in the pivot tables:

5a) Slicer: Analyse>Insert slicer. The slicer works well with limited category fields.

With slicers, we can see the filter applied. We can add slicer by going to data>slicer and then adding the column list we want to use.

Slicer in sheets gives a great view of understanding what the filters are being added.

5b) Filters: Filters can work great with a long list of category fields.

Filter command in excel can be added through data>filter, in sheets it is down below once we are in the pivot table view. Filters in sheets work together.

5c) Timeline: Timeline works with the date field and you can specify months, quarters and years. Analyse > timeline. Timeline is not available for sheets.

5d) Calculated fields: For some of the calculations in the pivot table the sum or the average might not make sense.

So you can add a new calculated field as a part of the pivot table.

You can do this by Analyze>fields,forms and sets>insert calculated field>create formula>set up the new field (in excel).

In sheets, this can be done right from where we add the columns, choose the last option> calculated fields.

5e) Filter view in sheets: We can create a filter view to easily navigate between different important filters.

We can now easily switch and also share this by using the FVID and using the =Hyperlink(“URL”, “range_name”). This can be then pasted anywhere and the link will take to that particular view.

5f) Power tips in Pivot table in excel: Sheets for pivot is a little less powerful as compared to the excel version.

Some power tools that I learnt is:

— Start the pivot table after converting the data in an excel table. This makes it easier to get the new data fields which will automatically show up when refreshed in the pivot table.

— The detailed view of a number in the pivot table can be learnt by clicking on the number that will open the composition of that number, if any.

This does open a new sheet which can be controlled through Pivot table options. (Analyse>Options>data)

— You can create individual spreadsheets based on the filters. This can only be done when you have a small set of filters.

— The show value can be used to correlate the data in many different ways. This is really useful as well.

— Turning off auto fit columns: Analyse>Options>turn off auto fit option. This makes the experience smooth for everyone viewing the data when flipping through the filters.

— Pivot chart: Though this is a cool feature, I think this leads to more cleaning of the chart.

However, pivot chart is tied to the pivot table which means all the filters and slicers are attached to the chart.

We then moved to different other features for excel and sheets including vlookup, let’s look at some of the cool points learned:

1. I learned about removing duplicates, splitting the data into text to columns and also that there are always some other ways to achieve a result in excel.
2. Vlookup is such a powerful formula that can be used in so many different situations.

We looked at how to reduce the impact of those error messages through the use of IFNA or IFERROR.

This command helps to avoid #N/A returns and rather return a controlled message.
=VLOOKUP(search_key, range, index, [is_sorted])

search_key — The value to search for. For example, 42, “Cats”, or I24.

range — The range to consider for the search. The first column in the range is searched for the key specified in search_key.

index — The column index of the value to be returned, where the first column in range is numbered 1.

If index is not between 1 and the number of columns in range, #VALUE! is returned.

is_sorted — [TRUE by default] — Indicates whether the column to be searched (the first column of the specified range) is sorted. FALSE is recommended in most cases.

3. Index and Match: Sometimes the information you are looking for is not in the first column from the range.

We can then use the combination of index and match to overcome this problem. With the “match” command we can locate where the range is. The index then uses this information and returns the value that we are looking for.

=INDEX(reference, [row], [column])

reference — The array of cells to be offset into.

row — [OPTIONAL — 0 by default] — The number of offset rows.

column — [OPTIONAL — 0 by default] — The number of offset columns.

INDEX and MATCH gives us more flexibility when what we’re matching on is not in the very first column that we are doing the VLOOKUP on.

4. Xlookup: This is one up from Vlookup and takes into consideration the shortcomings of Vlookup.

Although, Xlookup is not available everywhere but it was fascinating to learn about this command. It can take care of the challenge of:

a) The data not being in the left-hand side column

b) The challenge of specifying exact match

c) Vlookup cannot handle column insertions

d) Vlookup can’t do vertical lookups

e) Vlookup has weird approximate values

f) Vlookup requires the complete reference cells

5. Sparklines: A one cell chart that can pictorially represent the data. This is easy to set up but tricky to delete, so use it carefully. In excel it is added through chart on the toolbar but in sheets it is done through the command:

=SPARKLINE(data, [options])

data — The range or array containing the data to plot.

options — [ OPTIONAL ] — A range or array of optional settings and associated values used to customise the chart.

6. Conditional formatting: This helps to highlight some of the data on the basis of the formatting options available with excel. Conditional formatting is very easy to use with excel but not so much with sheets. One pro tip I found was to define named ranges that will make it easier to select and apply the formatting. In sheets, we can apply multiple rules and use filters to analyze data.

Understanding string (text) functions

1. LEN: Throws the length of the text
2. SUBSTITUTE: Replaces a substitute value for the part defined
3. FIND and SEARCH: The find is case sensitive and search is case insensitive. So you can search what you are looking for, within where and how many letters in you wish to start the search.
4. MID: Extract some text in whatever string you are looking for
5. LEFT and RIGHT: Extract leftmost and rightmost characters from the string

Understanding Error Trapping

1. Data validation: Reducing error and reporting error is a great feature of excel. This can be done through Data Validation.
2. Pull down list
3. Whole number specification
4. Summing with brackets
5. IF function to reduce the cross footing
6. Protected cells to protect the sheet and keep a designated data entering space