This article is covering my Tenth week of studying the Digital Analytics Minidegree from CXL Institute. In the previous post I have covered Attribution Model for marketing technology and this week I will be covering the presenting data using Excel and Sheets.
Talking about the trainer Fred Pike, Managing director at Northwoods. He has covered complete details on Excel and Google sheets for SEOI and Digital Marketers report. He clearly explains with the live work using Google Search Console Data.
The Lessons Covers in Excel and Google Sheets
- Sort and Filter
- SUM – Variations
- COUNT – Variations
- Tables and Calculated Columns
- Pivot Tables – Set Up – Excel & Sheets
- Pivot Tables – Calculated Fields
- Pivot Tables – Filters, Slicers, and Timeline – Excel & Sheets
- Power Tips for Pivot Tables – Excel
- De-Dup & Text To Columns
- Index and Match
- XLookup – Excel
- Conditional Formatting
- String Functions
- Error Trapping
Sort and Filter
Sort means to sort data or value in Ascending or Descending order. But Filter means to filter any unnecessary data (according to requirement). For example in your case if you sort data by Ascending order it will show up like this..1,3,3,5,…and so on.
How to Sort in Excel:
- Right click to open the menu
- Go down to the Sort option – when hovering over Sort the sub-menu will appear
- Click on Largest to Smallest
- Select Expand the selection
- Click OK
How to Filter in Excel:
- Go to the Data tab on Excel ribbon
- Select the Filter tool
- Select Eastern Company from the dropdown menu
- Select Dylan Rogers from the Salesperson dropdown menu
SUM – Variations
- SUM (sums everything)
- SUMIF (sums based on one condition)
- SUMIFS (sums based on multiple conditions)
- Most of the time, you’ll use the SUM function in Excel to sum a range of cells.
Example: =Sum (A1:B1)
- Use AutoSum or press ALT + = to quickly sum a column or row of numbers.
If you want, you can apply the criteria to one range and sum the corresponding values in a different range. For example, the formula =SUMIF(B2:B5, “John”, C2:C5) sums only the values in the range C2:C5, where the corresponding cells in the range B2:B5 equal “John.”
The SUMIFS function:
The SUMIFS function, one of the math and trig functions, adds all of its arguments that meet multiple criteria. For example, you would use SUMIFS to sum the number of retailers in the country who (1) reside in a single zip code and (2) whose profits exceed a specific dollar value.
COUNT – Variations
The Excel COUNTA function returns the count of cells that contain numbers, text, logical values, error values, and empty text (“”). COUNTA does not count empty cells.
Use COUNTIF, one of the statistical functions, to count the number of cells that meet a criterion; for example, to count the number of times a particular city appears in a customer list.
Counts the number of cells with apples in cells A2 through A5. The result is 2.
The COUNTIFS function applies criteria to cells across multiple ranges and counts the number of times all criteria are met.
A table is a powerful feature to group your data together in Excel. Think of a table as a specific set of rows and columns in a spreadsheet. You can have multiple tables on the same sheet.
Creating a Table within Excel
- Open the Excel spreadsheet.
- Use your mouse to select the cells that contain the information for the table.
- Click the “Insert” tab > Locate the “Tables” group.
- Click “Table”. …
- If you have column headings, check the box “My table has headers”.
- Verify that the range is correct > Click [OK].
A pivot table is a table of statistics that summarizes the data of a more extensive table. This summary might include sums, averages, or other statistics, which the pivot table groups together in a meaningful way. Pivot tables are a technique in data processing.
Pivot Tables – Slicers
Slicers are visual filters. Using a slicer, you can filter your data (or pivot table, pivot chart) by clicking on the type of data you want.
Create a slicer to filter data
- Click anywhere in the table or PivotTable.
- On the Home tab, go to Insert > Slicer.
- In the Insert Slicers dialog box, select the check boxes for the fields you want to display, then select OK.
- A slicer will be created for every field that you selected
Text to Columns
Text to Columns is a feature in Excel that parses the text in one cell/column into many columns using a delimiter than can be adjusted.
How to Use Text-to-Columns in Excel
- Open Excel and start a new Blank workbook.
- Add entries to the first column and select them all.
- Choose the Data tab atop the ribbon.
- Select Text to Columns.
- Ensure Delimited is selected and click Next.
- Clear each box in the Delimiters section and instead choose Comma and Space.
- Click Finish.
VLOOKUP is an Excel function to look up data in a table organized vertically. VLOOKUP supports approximate and exact matching, and wildcards (* ?) for partial matches. Lookup values must appear in the first column of the table passed into VLOOKUP. The matched value from a table.
- In the Formula Bar, type =VLOOKUP().
- In the parentheses, enter your lookup value, followed by a comma. …
- Enter your table array or lookup table, the range of data you want to search, and a comma: (H2,B3:F25,
- Enter column index number. …
- Enter the range lookup value, either TRUE or FALSE.
A sparkline is a tiny chart in a worksheet cell that provides a visual representation of data. Use sparklines to show trends in a series of values, such as seasonal increases or decreases, economic cycles, or to highlight maximum and minimum values. Use sparklines to show data trends
A sparkline is a tiny chart in a worksheet cell that provides a visual representation of data. Use sparklines to show trends in a series of values, such as seasonal increases or decreases, economic cycles, or to highlight maximum and minimum values. Position a sparkline near its data for greatest impact.
How to Add a Sparkline
- Select a blank cell at the end of a row of data.
- Select Insert and pick Sparkline type, like Line, or Column.
- Select cells in the row and OK in menu.
- More rows of data? Drag handle to add a Sparkline for each row.
Conditional formatting is a feature in many spreadsheet applications that allows you to apply specific formatting to cells that meet certain criteria. It is most often used as color-based formatting to highlight, emphasize, or differentiate among data and information stored in a spreadsheet.
Conditional formatting enables spreadsheet users to do a number of things. First and foremost, it calls attention to important data points such as deadlines, at-risk tasks, or budget items. It can also make large data sets more digestible by breaking up the wall of numbers with a visual organizational component. Finally, conditional formatting can transform your spreadsheet (that previously only stored data) into a dependable “alert” system that highlights key information and keeps you on top of your workload.
Originally a powerful feature of Excel, other spreadsheet applications have also adopted this functionality.
Few rules are listed here:
- Apply Highlight Rules to Your Excel Spreadsheet
- Create Top/Bottom Rules
- Apply Data Bars
- Apply Color Scales
- Apply Icon Sets
- Edit and Delete Conditional Formatting Rules
- Apply Multiple Conditions to a Rule with AND Formula
- Conditional Formatting Based on Another Cell
- Data Validation and Dropdown Lists
Now that you have an idea of the scope of the presenting data using Excel and Sheets. I am sure that you have realized how much of a value it can add to your report and business. Excel and Google sheets are the places place where you can present the marketing data for your analysis. In the next week post I will be covering the Google Data Studio where you will learn to create impressive, time-saving reports and save the time in the process with Data Studio. Feel free to come back on every Monday’s and read about CXL Digital Analytics mini degree.