Excel for Analytics
The last 10 weeks have been a fun journey learning basic and advanced concepts, practical tips, and tricks to work around challenges and getting things done in GA, GTM, Data Studio, BigQuery, and Excel.
It was a pleasant surprise to see the good old humble Excel featuring as a powerful tool for Analytics. The mini degree courses emphasize time and again that knowing the tools is as important as knowing the concepts.
Data operations using Excel
This week I am going to walk you through some Excel tips and tricks that are practical and can shave hours off your day-to-day work.
Sorting and Filtering
Excel allows advanced sorting such as sort by multiple columns and sort only a certain range of data. This comes in handy when arranging the data in a more meaningful way.
Sum
Sum is a simple operation of adding multiple numbers, but Excel offers multiple ways to sum the numbers. Example: you can do a conditional sum. You can tell Excel to sum only if a condition is true or false.
Sum function variations:
Count
COUNT function allows you to count instances of certain value’s occurrences. Similar to Sum, the COUNT function also allows multiple variations that help you to perform data analysis better:
Tables and Filters
Spreadsheets are rows and columns. But if you add filters, then they become tables. The table is a special type of marked area in Excel which can offer benefits. Such as adding more columns or rows to the table automatically expands the table. A sorted column will automatically sort the new values added to the table.
You can filter easily by using the Auto Filter option in Excel which adds a filter dropdown to each column and allows you to only select distinct values that makeup all the rows of that table. Example: selecting “London” in the City column of the customer information spreadsheet will filter and display rows that contain “London” in the city column.
Pivot Tables
Pivot tables are one of Excel’s most powerful features. They allow you to extract the significant information (insights) from a large, detailed data set.
The below example shows a spreadsheet with information about vegetables, their prices and the country which produces them.
Using a pivot table feature, you can extract the insight such as:
Recommended by LinkedIn
This is extremely helpful as you can find significant information from the raw data. Pivot tables are one of the most useful business features of Excel.
De-Duplication
Duplicate data can skew the metrics heavily and provide the wrong information for analysis. Excel provides a de-duplication feature which quickly allows you to remove duplicates from a data set. Select the range from which you want to remove duplicates. Make sure you do not select any columns or rows with totals or formulas. Then under “Data” select “Remove Duplicates” to remove the duplicate values from the data set.
Vlookup
The VLOOKUP function performs a vertical lookup by searching for a value in the first column of a table and returning the value in the same row in the index number position. The VLOOKUP function is a built-in function in Excel that is categorised as a Lookup or Reference Function.
There are many uses of VLookup function, such as use VLOOKUP when you need to find things in a table or a range by row. For example, look up the price of a machine part by the part number, or find an employee name based on their employee ID.
Setting the last parameter to true returns an approximate match while setting it to false returns an exact match. Some times this does not work to our liking, and we may get unwanted results using Vlookup. So you may need to experiment a bit till you are satisfied with this results using this parameter.
Xlookup
This is a newly introduced function of Excel. It appeared in the online version of Excel first and then rolled out to the other versions of the product. XLOOKUP was introduced to overcome some of the shortcomings with VLOOKUP function.
While it is required that values be sorted first for VLOOUP to work, XLOOKUP can find values in both directions so you do not need to sort them first.
XLOOKUP also requires lesser information to begin the analysis. While VLOOKUP requires the entire data set as a reference, XLOOKUP only needs the rows and columns it want to work with.
The biggest drawback of VLOOKUP is that if you insert a row or column in the data set which is used for VLOOKUP, then the formula breaks because it cannot self-adjust the reference cells. XLOOKUP overcomes this by self-adjusting the cell references when a column or row is added or removed.
Conditional Formatting
Sometimes we want some data to stand out from others due to their important significance. Example: Let’s say you are analysing the data set for the yearly revenue and would like to highlight all orders above a certain threshold in yellow to indicate significant wins. Or you want to highlight all numbers below a certain threshold to quickly see the comparative performance among their peers.
For this purpose, you will use Conditional Formatting feature of Excel. Just go to “Home” tab and choose “Conditional Formatting”.
Error Handling
When you encounter erroneous data, Excel will fail the calculation and throw up “#” codes such as a”#NAME?” or “#REF?” etc. These indicate different types of errors in Excel which have a specific meaning and can be solved with specific troubleshooting steps. However, you do not always control data and how it can appear — imagine you are asked to sift through the spreadsheet having 1 million rows. But you want your formulas to work without errors and whenever it encounters an error, you want to show a nice human-understandable message so the others who are providing you the data can take appropriate actions and send you the corrected data.
You use a function called “IFERROR”. IFERROR allows you to print a certain message on the cell if the data is erroneous — example: a text value is encountered in a cell when performing a SUM or a COUNT function.
Wrapping Up
Excel is an excellent tool to build your analytics prowess. It is a also an excellent playground to help you hone your data processing skills and interpretation of data for insights. If this post has excited you to take a deep dive into Excel — especially if you are a Digital Marketer then this is an indispensable tool, then I strongly recommend you to take up the course “Excel and Sheets for marketers” from CXL institute.
