Excel-PivotTables

A deep dive into Excel Pivot Tables, specializing on Data Analytics, using an IMDb dataset from a Udemy led course.

Section 1: Excel Pivot Tables 101

• 1. For step one I need to show the Budget of each movie Title in rows, with filters, for Country and Language that are set to Japan and English respectively. Out of all of the movies produced in English, how many of the movies were Japanese movies? !
• 2. Using the pivot tables from step one, I needed to remove the language filter and set the Country filter to Denmark. As well, I had to include Gross Revenue as a second columned metric. What was "The Celebration's" gross revenue? !
• 3. For the third part in section one I needed to create a new view. In this new view I must now show each Countries (rows) Gross Revenue across all of the Genres (columns). How much revenue was generated by Comedy films in Finland? !
• 4. Using the same table as in section three, remove the Country field and move Genre to rows. As a secondary row label, include Rating. What was the total revenue, that PG-rated family films, generated? Double click this cell to see the source data. What was the movie title that drove the most revenue? !
• 5. Add a fake row of data in the "IMDb Movie Database" tab, beneath the existing rows, and use the Change Data Source option to update the pivot. Create a title-level view and confirm that the new data is included, then delete the entire row in the raw data sheet and refresh the Pivot Table to remove it.

Japanese Movies Produced in English	"The Celebrations's" Gross Revenue
Gross Revenue for Finlands Comedy Films	Most Prosperous Family Film

Add a row and a column to the raw dataset

Section 2: Formatting Excel Pivot Tables

• 1. Show Budget and Gross Revenue by Title, and change the number format to currency, with a dollar sign and no decimal places. What was the budget for "A Passage to India"?
• 2. Remove Budget and Title, and show Gross Revenue by Genre (rows) and Rating (columns). Update the PivotTable options to show "$0" instead of blank values
• 3. Move Rating to the row labels (beneath Genre), change your table layout to Outline View, and Update your column headers from "Rating" to "Film Rating", and from "Sum of Gross Revenue" to "Gross Revenue" (hint: you may need a trailing space)
• 4. Remove Film Rating from the view, so that you're just viewing Gross Revenue by Genre. Turn Grand Totals off, select the Gross Revenue values, format as currency (if they aren't already) and add a Color Scale from Green (high) to Red (low). Which Genre produced the most Gross Revenue?
• 5. Add a second instance of Gross Revenue, and format the new column with Data Bars. Update the number format to make the text invisible, so that only the bars appear. Which Genre produced the second-highest Gross Revenue total in the sample?

A Passage To India GR	Replace Blank Values w/ $0
Horror Films GR by Country	USA B&W Films GR by Genre and Rating

Visualizing Gross Revenue with Color Values and Data Bars

Section 3: Use Pivot Tables to Sort, Filter and Group Data

• 1. Create a view showing Gross Revenue by Title, with a filter for Year to only include films released in 2005, 2006, 2007 or 2008, then sort the titles descending by Gross Revenue. What's the top-grossing film released during that 4-year sample? (Note: if the Release Dates don't auto-group, you will need to use the "Group" tools in the Analyze tab or create a new column in your raw data to extract the year from the Release Date column)
• 2. Add a Label Filter to only include titles that end in "2". How many sequels were released during these years? Which earned the most Gross Revenue?
• 3. Clear your label filter, and add a Value Filter to only show titles that earned between $1,000,000 and $3,000,000 in Gross Revenue. How many titles fell into this range?
• 4. Adjust your PivotTable Options to allow multiple filters, then add a label filter to only show movies that start with the letter "M". How many titles are now listed?
• 5. Add a wildcard to your label filter to only show titles that start with the letter "M" and also contain the letter "s", separated by any number of characters. Which titles are returned?

Top Grossing Title from 2005-2008	Top Grossing Title ending in "2"
Which Titles earned from 1-3mm	Which Titles start with the letter "M"

Titles that start with "M" and contain "s"

Section 4: How to Enrich Data with Calculated Values and Fields

• 1. Create a view to show IMDb Score by Title. What happens when you replace Title with Genre? How can you fix this issue? (hint: look at the summarization type...)
• 2. Update your view to show Average IMDb Score by Genre (primary row labels) and Year (secondary row labels), for 2011-2014. Drag in a second instance of IMDb Score, change the summarization to Average, and show the values as a Rank (large to small) based on the year. Which year in the 4-year sample saw the highest-rated Biography films on average? The lowest?
• 3. Add in a column for Gross Revenue, and show the values as the % Difference From the previous year. By what percentage did Action movie revenue grow in 2014?
• 4. Create two new calculated fields named "Profit" (Gross Revenue - Budget), and "Profit Margin" (Profit / Gross Revenue). Update the view to show both new fields by Title. Which Title generated the strongest Profit Margin in the entire sample (across all years)?
• 5. Create a new calculated field for "Cast + Director Likes" (Cast FB Likes + Director FB Likes), and update the view to show Cast + Director Likes by Genre. If you wanted to show this field as an average across titles, rather than a sum, how could you accomplish this?

Show IMDb Scores as "Avgerage"	View Genre Average by Rank between 2011-2014
Show % Difference from Previous Year	Create "Profit" and "Profit Margin" Calculated Field

Create calculated field for "Cast + Director Likes" for combined Cast and Director FB Likes.

Section 5: Visualizing Data with Excel Pivot Charts

• 1. Create a view to show # of Titles by Country, excluding the USA, for the entire sample. Name the PivotTable "Titles by Country", then use a PivotChart to visualize this view as a Clustered Column Chart.
• 2. Hide the Field Buttons from the PivotChart, then apply a value filter to only show the top 10 countries by # of Titles (hint: you may need to enable multiple filters). Which country is #2?
• 3. Change the chart type to a Clustered Bar, and change the PivotTable sorting to ascending by # of Titles.
• 4. Pull in IMDb Score as a second series, and summarize values by Average. Change your PivotChart type to Combo, with # of Titles as a Clustered Column and IMDb Score as a Line with Markers, on the Secondary Axis. Which of the 10 countries generated the lowest average IMDb scores? (Bonus: Format the IMDb series in the chart to only show the markers, with no line)
• 5. Copy the existing pivot and create a second view below the combo chart to show Budget by Genre, with a Top 5 filter applied. Name the table "Budget by Genre", then visualize this view with a Pie chart, with hidden field buttons.
• 6. Insert a Slicer for Genre, enable multi-select, then connect it to both PivotTables. Create a simple dashboard by hiding the columns of your raw PivotTable views, disabling gridlines, and aligning/formatting the PivotCharts and Slicer as you see fit. Practice adjusting slicer selections to see how the dashboard updates!

"Titles by Country" Clustered Column Chart	Top 10 Countries by Number of Titles
Clustered Bar Chart Ascending by # of Titles	# of Titles and IMDb Score Combo Chart

Top 5 "Budget by Genre" Pie Chart. Enable slicers multi-select and connect it to both combo and pie charts.

Section 6: Case Studies

Analysis of U.S. Voter Demographics

--

Analyzing San Francisco Salary Data

--

Exploring Shark Attack Records

--

Analyzing Stock Market Data

--

Analyzing Major League Baseball Teams

--

Exploring San Diego Burrito Ratings

--

Tracking Daily Weather Conditions

--

Analyzing Spartan Race Facebook Posts

--

Analyzing Apple App Store Data

--

Analyzing Wine Tasting Scores