Microsoft Excel Data Analysis: Formulas & Pivot Tables

Master metrics and dimensions and how context guides data, with views, likes, comments, and shares, then structure data down rather than across with six columns for per-row insights on YouTube.

Learn to sort data with multiple levels and apply filters to reveal dates, platforms, and top values in Excel, and avoid filtering a single column to prevent data from scrambling.

Learn how to create and use named ranges in Excel with Name Manager, reference them in sum and average formulas, and build a pivot table from a named data range.

Master quick navigation and data selection in Excel with control or command arrow shortcuts to move to data ends, and shift-control arrows to select ranges for pivot tables and formulas.

Learn essential Excel shortcuts for find, replace, copy, paste, cut, and undo using Ctrl F, Ctrl H, Ctrl C, Ctrl V, Ctrl X, and Ctrl Z.

Explore comparison operators in part 2 of the introduction to formulas and learn how they compare numbers, text, and dates to yield true or false for use in helper columns.

Explore how Excel handles dates using serial numbers to perform date calculations, compute distances between dates, and sum values across date ranges for scheduling and time-based reports.

Explore how to use the datedif function to calculate days, months, or years between two dates, leveraging Excel’s date cataloging for effective data analysis.

Explore the sumif function to sum values by criteria, using day, month, and year helper columns to filter YouTube and Facebook views and verify totals by date.

Learn to use the sumifs function to aggregate data with multiple criteria in Excel, using filters and helper columns to analyze monthly and yearly social media metrics.

Learn countif variations in Excel to count matches using a range and a criteria, including text, numbers, and dates. Apply it to YouTube and Facebook data across an entire column.

Learn to use countifs to apply multiple criteria in Excel, filtering YouTube days with over 100,000 views and 6,000+ likes, and combining conditions across platforms.

Use the averageifs function to calculate average views and likes by platform (Facebook and YouTube), then apply int to round results to whole numbers.

Create a month-year helper column and an engagement helper column in your data set to feed the six project problems. The next video shows the answers if you need help.

Walk through project #1 using helper columns to build formulas in Excel, counting views and engagements across platforms by month and year, and calculating average views and engagements by quarter.

Explore vlookup and index match to look up data in vertical tables using a lookup value, table array, and exact-match, with the value on the left and a specific column.

Master using named ranges in vlookup to replace the data array and speed up lookups, reducing mistakes and ensuring exact matches.

Combine the match and index functions to look up a date in column a and return the corresponding value from column b, using evaluate formula to show the lookup.

Apply an if statement with the and condition to classify days as great day or good day based on benchmark results, and use pivot tables for YouTube and Facebook benchmarks.

Learn how name errors from misspelled vlookup and value errors from wrong data types, and fix by supplying correct lookup value and column index number.

Understand how #REF errors arise when you delete data used by formulas across tabs, and apply the if error function to convert these errors to zero in Excel.

Examine the columns box in pivot tables, using dimensions like year and platform to organize data on top and in rows, and learn to repeat dimensions for clearer displays.

Learn to display pivot table values as a percentage of the grand total or column total, with raw numbers shown alongside percentages.

Discover how pivot table filters customize views, letting you filter by platform and date, compare quarters across years, and give end users flexible, transparent insights.

Convert your data into a table and power your pivot table with that table to automatically refresh when you add new data. Automatically recognize new data on paste.

Master pivot tables to group dates by year and month, adjust group levels, and compare metrics like views, likes, and comments across platforms and years.

Clean up pivot tables by removing sums, turning off grand totals, using a trailing space to show views and engagement rate, and moving dates to rows for rollups.

Build a pivot table with years and months, add platform as columns, calculate engagements and engagement rate, apply filters for Q2 2018, and refresh with new data.