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 treat Excel as a miniature database, using formulas and pivot tables to query and summarize data, and unify two datasets with a platform column for YouTube and Facebook.

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.

Master essential Excel hotkeys, especially Ctrl+1, to open the format cells box and quickly tailor numbers, currency, and dates for faster data analysis.

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 essential Excel functions, including the core 20, learn about arguments and optional arguments, and use the formula builder to build reliable formulas such as quotient and match.

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.

Compute the difference between two dates by subtracting their serial numbers to reveal days between, then format dates; months or years require a different function.

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.

Learn to extract day, month, and weekday from dates using the day and weekday functions, then apply sumifs and filters to analyze March and Friday Facebook and YouTube data.

Use the TEXT function to reformat dates as days of the week, choosing 3D for abbreviated and 4D for full names, and explore custom date-time formatting in Excel.

Learn the sum function in Excel, including range totals and keyboard shortcuts. See how sum and sum if apply to metrics like engagements across a period.

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.

Learn how to use VLOOKUP to merge two data sets by pulling uploads and watch time percentage, locking ranges to prevent errors when autofilling.

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

Learn how to use the index function with an array, row, and optional column to retrieve values, and see how index and match together offer a flexible alternative to vlookup.

Explore how the match function finds an exact value and returns the row or column number, using a date example and header row search to locate data in Excel.

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.

Use index and match to pull uploads and watch time by date across worksheets, similar to vlookup; derive row numbers from dates and auto fill all days.

Explore Excel's logical functions, including or, and, and the if function, to test conditions and return true or false using comparison operators like equals, not equal, greater than.

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.

Walk through project 2 to build a helper column for full weekday spelling, pull revenue with vlookup, and use index–match and if statements for weekend vs weekday totals with sumifs.

Place views in the rows of a pivot table and group by platform and year to compare Facebook and YouTube. Ignore the likes and group date by year.

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.

Explore the values box in pivot tables to compute metrics like sum, count, or average by month and year, demonstrated with January 2017 data counts and average views.

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.

Learn to create calculated fields as an on-the-fly extra column in a dataset or pivot table, summing likes, comments, and shares to form engagements and calculating engagement rate from views.

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.