Please enable JavaScript.
Coggle requires JavaScript to display documents.
Data analysis with Excel Pivot Tables - Coggle Diagram
Data analysis with Excel Pivot Tables
Module 2: Pivot Table 101
Why Pivot Table ?
Looking at a raw data set like one here, how would you answer the following ?
(2.1) Which state had the highest population in 2002 ?
(2.2) In which year was overal US population the highest ?
(2.3) Which states saw a decline in student population rate between 2002 and 2003 ?
Pivot allows you to easily organize, filter, summarize and analyze raw data.
Key benefits
(4.1) Powerful : Uncover insights and answer key questions about your data.
(4.2) Beautiful : Apply custom styles and conditional formating rules to bring your Pivot to life.
(4.3) Fast : Create custom views, filters and calculated fields on the fly.
(4.4) Automate calculations to minimize human error
Structuring your source data
Data structure : good
Rectangular ( variables as columns, observations as rows )
No extra formatting.
Contains only dimensions & measures.
Clear column headers.
No extra headers, footers, sub-totals, or calculated fields.
Data structure : bad
Transposed ( variables as rows, observations as columns )
Unnecessary formatting.
Contains calculated fields
Extra header rows.
Inserting your first pivot table
(*) From the "insert" menu, select Pivottable to create a blank Pivot, or use the Recommended PivotTables option to browse pre-populated starting points.
Navigating the field list
The field list shows all variables in your dataset, and which one currently included in the Pivot.
If there are fields that you want to use to filter the whole data set, drag them to the Filters box.
Variables included in the Rows field will appear as individual rows within the Pivot.
Layout options allow you to adjust the look and feel of the field list.
Variable included in the Columns field appear as individual columns within the Pivot.
Numerical variables are almost always included in the Values field.
Practice
Khám phá tổng doanh thu : Sum of Gross Avenue -> Values.
Khám phá doanh thu theo tên phim : Title -> Rows, Sum of Gross Avenue ->Values.
Khám phá doanh thu theo từng phim thuộc khu vực Canada : Title -> Rows, Sum of Gross Avenue -> Values, Country -> Filter; tick Canada.
Khám phá doanh thu theo từng phim thuộc khu vực Canada, thuộc Ngôn Ngữ, thuộc màu sắc : Title -> Rows, Sum of Gross Avenue -> Values, Country -> Filter, Filter -> Language + Color, B&M.
Khám phá doanh thu theo từng thể loại theo từng bộ phim, thuộc khu vực Ustralia, thuộc 1 ngôn ngữ, thuộc 1 nhóm màu sắc : Title -> Rows, Genre-> Rows, Sum of Gross Avenue -> Values, Country -> Filter, Filter -> Language + Color, B&M.
Khám phá chỉ doanh thu theo thể loại : Genre -> Rows, Sum of Gross Avenue -> Values.Tất cả bộ lọc set về -> All.
1 more item...
Pivot table options: analyze & design
Analyze
Tools : options, Expand field, Collapse field, group selection, insert slicer, insert timeline, refresh, change data source, clear, select, move pivottable, fields, items & sets, pivotchart, fieldlist.
Design
Tools : Subtotals, Grand totals, report layout, blank rows, pivot table styles.
Selecting, clearing, moving & copying pivots.
Clear options allow you to clear all fields and values from a table, or just any filters that have been applied.
For example 1: Filter country-> France, Filter Language -> French, Color / B&M -> Color. You use Clear Filters to delete Filters or use Clear all to delete pivot table.
Select options ( allow ) you to select entire sections of the Pivot Table) , ( or the entire table itself )
For example 2: Select entire pivot table
Move option allows you to relocate an existing PivotTable to a new worksheet or a new location within the existing one.
For example 4: Click Right Mouse -> Show Field List// Hide Field List.
Pro tip : Select -> Entire pivot table, then copy and paste to duplicate an entire pivot.
For example 3: Copy and past duplicate an entire pivot
Refreshing & updating pivots.
Refresh updates the Pivot Table based on changes made within the defined source data range or table.
For example 1: , at row 3327, Example title. Choose Change data source.
For example 2 : format change data source : $A:$G, just only colum range reference.
For example 3: Example title 2, example title 2-> Choose refresh
Change data source allows you refresh the Pivot Table to reflect changes outside of the defined source data range or table ( i.e. new columns or rows )
For example 4: Click all table -> Format as Table -> Name for Table ( Movie )
For example 4: Change data source-> Movie
For example 5; Copy data into movies table
For example 6: Convert to Range.
Format your source data as a table to dynamically adjust as new columns or rows are added, or use a column-only range reference ( i.e. $A:$G)
Pro tip : dealing with growing source data
Change PivotTable Data Source. Table/ range : 'IMDb Movie Database' !$A$1:$R$3727
Change PivotTable Data Source. Table/ range : 'IMDb Movie Database'!$A:$R.
Format as Table =$A$1:$R$3726
Design. Table -> Movie
Change source data.
Table Tools -> Convert to Range.
How pivot table actually work ?
Step 1: Detect/ evaluate coordinates [ State = Arizona, Measure = Total population, Filter = All ages. ] Excel isolates relevant source data.
Step 2: Apply arithmetic. [ Summarize values by : average , vs ( SUM, COUNT, MAX, MIN, etc ].
Step 3: Display result. [ 586 + 850 + 870 + 1656 + 892 ] / 5 = 973
(*) Note : You can double-click any specific value in a Pivot to generate a new tab showing the exact source data used to calculated it.
Module 3: PivotTable Formatting.
Number formatting
Formatting "Revenue Field"
Step 1: Click -> Right Mouse -> Choose "Number Format"
Step 2: Choose "Currency" and Adjust "Decimal places = 0 "
Pro tip : Automatically formatting Empty Cells
Example : Look movie revenue by country by genre ?
Step 1: Rows -> Title; Values -> Revenues; Columns -> Genre.
Step 2: Choose "PivotTable Tools" go to "Analyze tab"
Step 3: Click "Tab options" -> Open "PivotTable options -> Click " Layout & Formatting" -> Fill "For empty cells show =0"
Table layouts & styles
Example : Look at "Movie revenue by country by genre".
Step 1: Choose "PivotTable styles"
Step 2: Choose "PivotTable Style Options"
Step 3: Choose "Layout" with 4 adjust options
Step 4: Choose with "Report Layout" with 4 options [ Show In Compact Form , Show In Outline Form, Show In Tabular Form, Repeat all item labels, Do Not Repeat Item Labels" ]
Step 5: Choose with "Blank Rows" with 2 options [ Insert Blank Line After Each Item" and [ Remove Blank Line After Each Item ]
Step 6: Choose with Grand Totals with 4 options [ Off For Rows and Columns, On for Rows and Columns On for Rows Only, Off for Column only ]
1 more item...
Pro tip : Using tabular tables to create new source data
Example : Look at "Movie revenues by country by genre"
Step 1: Choose Report Layout with "Show In Tabular Form" and "Repeat All Items Labels"
Step 2: Choose Grand Total with "Off For Rows And Columns"
Step 3: Choose Subtotals "Do Not Show Subtotals"
Customizing headers & labels
Example : Look at "Movie revenues by country by genre"
Step 1: I want change Field "Sum of Gross Revenue" into "Gross Revenue"
Step 2: To do such, just click "Gross Revenue" into "Extra space"
Conditional Formatting
Formula : Conditonal formatting rules can be applied to PivotTable just like normal data ranges. ( Home -> Conditional formatting ). Option include : Text and value-based format, Data bars, Color Scales, Icon Sets, Formula-Based Rules.
Example 1: I Want to look at "Which of country with genre have movie revenue greater than 100,000,000".
Syntax : Hightlight the revenue data column -> Home -> Conditional formatting -> Highlight Cells Rules -> Greater than -> Set value = 100,000,000.
Example 2: I want to deleted "conditional formatting cells"
Syntax : Select revenue column -> Home -> Conditional formatting -> Manage Rules -> Select "rule" -> Select "Delete".
Example 3: I want to look at "Which Top 5 countries with genre have movie sales the highest ?
Syntax : Select revenue column -> Home -> Conditional formatting -> Top/ Bottom Rules -> Top 10 Items -> Adjust "values =5" and "Green fill with Dark Red Text".
Example 5: I want to look at "How the revenue distribution in data table happens ?
Syntax : Selected revenue column -> Home -> Conditional formatting -> Data Bars -> Select "color frame"
Example 4: I want to delete "conditional formatting" according to another way.
Syntax : Select revenue column -> Home -> Conditional formatting -> Clear Rules -> Clear Rules from Selected Cells.
Example 6: I want to look at "How the revenue distribution in data table with color scales happens ?
Syntax : Selected revenue column -> Home -> Conditional formatting -> Color Scales -> Choose "which color".
Example 7: I want to look at "How the revenue distribution in data table with icon sets happens ?
Syntax : Selected revenue column -> Home -> Conditional formatting -> Icon sets -> Choose item "Indicators".
Pro tip : Data Bars with invisible text
Example : I want to see "How the distributions of the each country's movie revenue by genre as a data bar displayed 2 column happens"
Synax 1: Select "sale data column" -> Clear Rules -> Clear Rule from Selected Cells. Add "gross revenue column field" into "values box".
Syntax 2: Select "Revenue data column" -> Home -> Conditional formatting -> Data Bars -> Choose "Gradient Fills"with blue.
Syntax 2.1: Choose "revenue data column" -> Home -> Adjust "font size = 1" -> Choose "Font Color by White".
Syntax 2.2: Choose "revenue data column" -> Click "right mouse"-> Choose "Format number"-> Choose "Custom" -> Add by ";;;".
Module 4: Sorting, Filtering & Grouping
Sorting options
Hit this button ( or right-click one of the values ) to drill into Sorting & Filtering options.
Syntax 1: Manual selections.
Syntax 2: More soft options
Syntax 3: Label filters
Syntax 4: Value filters.
Context : I am viewing data table by movie revenue by genre with data bar format.
For example 1: I want to look at "movie revenue to sort according to A-Z order.
Step 1: Click "genre column" -> Sort A-Z.
For example 2: I want to look at "movie revenue to sort according to many different sorting modes with Desceding (A to Z with values set )"
Step 1: Click "genre column" -> More Sort Options -> Descending ( Z to A) by Gross Revenue.
For example 3: I want to look at "movie revenue to sort according to many different sorting modes with Ascending (A to Z with values set )".
Step 1: Click "genre column" -> More Sort Options -> Ascending (A to Z ) by Gross Revenue.
For example 4: I want to look at "the sum of the budget film has being sorted according to descending ( A - Z with values set ).
Step 1: Choose field "Sum of budget" into "value box"
Step 2: Adjust -> "Format number" of "sum of budget column"
Step 3: Choose -> Sort more options -> Descending (A to Z ) by sum of budget.
For example 5: I want to look at "sum of the film budget" has being sorted according to descending ( A to Z with values set sum of budget ) and descending by country.
Step 1: Add "country field" into "Row box"
Step 2: Change "Report layout" into "Show in Outline form" And choose "Do not repeat all items"
Step 3: Click "country column"-> Choose "Sort A-Z"
Step 4: Click "genre column" -> more sort options -> Choose descending ( Z to A with Sum of budget )
Pro tip: Why is my Alphabetical Sorting Wrong ?
For example : I want to look at "data table according country field" and remove "sum of gross revenue field"
Syntax 1: Remove "genre field" & "Add "country field into "Rows".
Syntax 2: Choose "country column" -> A to Z -> Analyze -> Options -> Tick on "Use custom lists when sorting".
Label Filters & Manual Selections ?
For example 1 : I wan to look "movie revenue that contain 10 days in, 10 things hate and 102 dalhatemats".
Syntax : Choose "title column" -> Label filters -> Choose which title contains "10 days in, 10 things, 102 dallhatemats.
For example 2: I want to look at "movie revenue that of all film except these film : 10 day in, 10 things, 102 dallhatemats
Syntax : Choose "title column" -> Label filters -> Tick "All" -> Tick no selection "10 days, 10 things, 102".
For example 3: I want to look at "movie revenue that contains word "snow".
Syntax : Choose "title column" -> Label filters -> Search word "snow"-> Selection all them.
For example 4: I want to look at "movie revenue of all films that except "snow dogs".
Syntax : Choose "title column" -> Label filters -> Selection tick all -> Search "snow dogs" -> Tick " Add current filter selection" -> Selections "Snow dogs".
For example 5: I want to look at "movie revenue of just only snow dogs".
Syntax : Choose "title column" -> Label filters -> Search "snow dogs"-> Selection them -> No selection "all".
For example 6: I want to delete "all label filters"
Syntax : Choose "title column" -> Choose "clear filters"
For example 7: I want to look at "movie revenue that begin characters with "T".
Syntax : Choose "title column" -> Label filters -> Selections " Begin with" -> Type T.
For example 8: I want to look at "movie revenue that characters greater than R.
Syntax : Choose "title column" -> Label filters -> Selection " Greater than" -> Type R.
Pro tip : Using Label Filters with Wildcards ?
For example : I want to look at "movie revenue with the starting character begin with A and Look at "movie revenue with the second character is A.
Syntax : Choose "title column" -> Label Filters -> Selections "Begin with" -> Type A. And Do the same but change Type A -> Type : ?A.
Syntax 2: Choose "tilte column" -> Label Filters -> Selections "begin with" -> Type : S?n.
Syntax 3: Choose "tilte column" -> Label filters -> Selections "begin with"-> Type : S??n.
Syntax 4: Choose "tilte column" -> Label filters -> Selections "begin with" -> Type : S*n.
Syntax 5: Choose "title column" -> Label filters -> Selections -> Begin with -> Type : A*night.
Value Filters
For example 1 : I want to filter "sum of budget" equal = 12.000.000
Syntax 1: Choose "title column"-> Value Filter -> Equals-> Set value : 12.000.000
For example 2: I want to look at "gross revenue with values between 1,000,0000 and 100,000,000.
Syntax 2: Choose "title column" -> Value Filter -> Clear Filters -> Between -> Type values : 1,000,000 and 100,000,000
For example 3: I want to look at "top 5 of gross revenue".
Syntax 3: Choose "title column"-> Value filter -> Clear Filter -> Top 10 -> Adjust values : top 5-> Selections choose item: gross revenue.
For example 4: I want to look at "Top 5 of gross revenue according to item: percent"
Syntax 4: Choose "title column"-> Value filter -> Top 10 ( adjust top 5) -> Selection Choose item: percent - gross revenue.
Pro tip : Automatic Date Grouping
For example 1: I want to look at "data table with the name of film begin S and I want to look at "revenue is greater than 100,000,000
Syntax 1: Choose "title column"-> Label Filters -> Choose begin with -> Type : S.
Syntax 2: Choose "title column"-> Values filters -> Is Greater than-> Type value : 100,000,000.
Syntax 3: Choose analyze -> Options -> Tick "Allow mutiple filters per field"
7.Grouping data
For example 1: I want to look at "data table according to grouping of the film name.
Syntax 1: Choose "title column"-> Manual filter -> Search "Star" -> Choose Star Wars -> Selection "right mouse" -> Grouping -> Change name group : Star Wars -> Change title field : Group Title.
Syntax 2: Do the same -> Change "Grouping Title" -> Star trek.
For example 2: I want to look at "data table" with just only "Title Grouping" column
Syntax 3: Move "title column" into "filter box" and now just only "group title column"
Syntax 4: You also can take break group connect. Choose Star Trek -> ungrouping and Star Wars -> UnGrouping.
Pro tip: Automatic date grouping
For example : I want to look at "revenu and budget according 'release date field" and look at each private field such as "years, quarters, month"
Syntax 1: Choose "release date" into "rows box" -> Analyze -> Group selections -> Adjust Add "release date into box"
Syntax 2: Selection move "year field" to Filter box, "quarter field" to Filter box, "month" to filter box, 'release date' to filter box.
Syntax 3: Selection "release date" ungroup.
Using slicers & Timelines to filter data
Formula : Insert slicers or timelines.
Basically a prettier version of a filter
A filters designed specifically for dates.
Pro tip : Slicers and timelines work just like regular report filters, but with user-friendly interfaces.
For example 1 : I want to look at "gross revenue & sum of budget of the film" according to the country that in order that Descending Z-A with gross revenue
Syntax 1: Move "release date field" into "filter box". Move "country field" into "rows box".
Syntax 2: Selection -> More sort options -> Descending Z-A by -> type : gross revenue.
For example 2: I want to look at"data table" according to filter the films belong wester country.
Syntax 3: Choose filter with 'genre box" -> Choose "Western" to see.
Syntax 2: Choose filter -> Choose "Comedy".
Syntax 3: [ Pro tip ] Selections -> Analyze -> Insert Slicers -> Tick : Genre. Choose remove "genre" from "filter box".
Syntax 4: Choose "slicer table" -> Selections -> Options-> You see more options -> To adjust-> To fit the table.
For example 3: I want to look at "data table according with using timelines to illustrate".
Syntax 1: Choose pivot table -> Analyze -> Insert timelines.
For example 4: I want to look at " gross revenue and sum of budget according the country with datte from June to December in 2015"
Syntax 1: Choose "see mode" : Jun - December of 2015.
Syntax 2: Choose "year from 2011-2013.
Breaking out report filter pages
Formula : Use "Show Report Filter Pages" option to create new tabs for each value that a given filter, can take.
For example 1 : I want to remove 'all slicer & timelines in table"
Syntax 1: Choose "slicer & timelines" -> Selection : delete.
For example 2: I want to look at "gross revenue & sum of budget according the country by movie title".
Syntax 1: Choose "title column" into "row box" under "country column"
For example 3: I want to look at "gross revenue & sum of budget according to the title". See filter with USA and Descending (A to Z )
Syntax 1: Move "the country field" to "filter box". Choose filter : USA. Choose descending ( A to Z ).
Syntax 2: Do the same -> Choose : Thailand.
For example 4 - Pro tip : Pivot Table -> Analyze -> Show Report Filter Pages ( Show filter that allow filter )
Syntax 1: Table Pivot -> Analyze -> Show Report Filter Pages -> Choose: country.
Module 5: Calculated Values, Fields & Items
"Summarize values by" options
Formula
"Summarize values by" determines how numbers should be treated when they are rolled up or aggregated.
Pro tip : Excel will default to "Count of" if a data column contains a blanks or non numerical values. Typically you will want to change this field setting to "Sum of".
For example 1: I want to look at "sum of revenue and budget" according genre that summarize by count.
Syntax 1: Selection "genre column" into "row box".
Syntax 2: Choose "summarize values by" -> selections "count".
Syntax 3: Do the same -> Choose "Average"
Syntax 4: Selections : Summarize values by -> Choose : more options.
Pro tip : Avoiding the 'Count of" Trap.
For example 1: I want to look at "sum of revenue of budget, and sum of tatal views according that summarize by sum".
Syntax 1: Choose "total view column" into "Row box".
Syntax 2: Selections : Summarize values by -> Choose: sum
For example 2: I want to look at the change "sum of revenue after delete one datac column from revenue column".
Syntax 1: Move into "revenue colum and total column" from "row box".
Syntax 2: Choose one value from revenue column -> Seletion : delete that column.
Syntax 3: Choose analyze -> selections : refresh. Add "revenue column" into "row box".
For example : I want to look at "sum of budget and total views according that sum as have data column contains blanks"
Syntax 1: Move "total views" into "row box" -> type "blank column from box" : add 0.
Syntax 2: Selection : pivot table -> selection : summarize values by "sum" instead of "count".
"Show values as: calculations"
Formula : Show values as options allow you to apply additional calculations to change the way values are shown, such as percent of a total or subtotal, running value, rank, etc.
For example 0: In this case, we're showing order quantity values as of % of column total, rather than whole numbers.
For example (*) : In this example, we're summarizing the same Revenue field 6 different ways.
Syntax 1: Choose value -> % of total column -> % of parent ( genre) -> % different year -> Running total ( by year ) -> Rank ( large-> small)
Show values as: % of column/ row.
For example 1: I want to look at 'sum of revenue as genre that have total".
Syntax 1: Selection " revenue column" into "row box".
Syntax 2: Selection : Design -> On For Rows and Columns.
Syntax 3: Selection : Show values as -> Click : % of total column.
For example 2: I want to look at " sum of revenue as genre that have % of total column with Spain country and with color.
Syntax 1: Selection "summarize values as : -> % of total column -> Filter_country : Spain -> Filter_color/B&M : Color.
For example 3: I want to look at 'data table as horizontal line".
Syntax 1: Move " genre column" -> "column box"
Syntax 2: Selection " values by as" -> % of total row.
For example 4: I want to look at "sum of revenue by the country by genre with % of parent row total"
Syntax 1: Choose "country column" into " row box" & "genre column" into "column box".
Syntax 2: Selections : Show values as -> Choose " % of parent row total".
Show values as: % of parent.
For example 1: I want to look at " The distribution of sum of revenue by genre by country as summarize values as % of parent total ..".
Syntax 1: Choose "country column" above 'genre column" into "row box".
Syntax 2: Choose " Show values as" -> % of parent total -> Type base fields : country.
Show values as: difference from.
For example 1: I want to look at Sum of Revenue by years within each genre, Look at "from 2012 to 2015".
Syntax 1: Remove "country field" -> Add "Release Date" into "Row Box".
Syntax 2: Choose "Year Field"-> Selections : Tick 2012, 2013,2014,2015; -> Remove "Summarize values as : % of parent total"; -> Selections: Manual options " Release Date" -> Type : 2012, 2013, 2014, 2015
Syntax 3: Choose "Number Format Field" -> Format : Currency.
For example 2: I want to see the changes in the number of revenue over 4 years within each category.
Syntax 1: Add " revenue field" into "Row box" -> Formatting : Number Format.
Syntax 2: Selection "Show values as" -> Choose "difference from" -> Choose "Base field : Release Date" -> Choose "Base Item" : 2012 Or Choose "previous"
Syntax 3: Selections "Show values as " -> Choose "% Difference From" -> Choose "Base Field : Release Date" -> Choose "Base Item: Previous".
Show values as : running total
For example 1: I want to see accrued revenue during four years within each genre.
Syntax 1: Choose " 2 revenue column" -> Selections "Show values as" -> Choose "Running total in". Add "Base Field" : Year.
For example 2: I want to see " the difference about film revenue" that compares the first year during four years within each genre
Syntax 1 : Insert "revenue field" into "Pivot table" -> Selection "the 3 revenue column" -> Selections "Show values as" -> Selection "Difference from" -> Choose " Add Base Item" : 2012.
Syntax 2: Selection "the third revenue column" -> Selection "Show values as" -> Selection "Difference from" -> Choose "Add Base Item" : Previous years.
Syntax 3: Selection "The third revenue column" -> Selection "Show values as" -> Choose "% difference from" -> Choose "Add Base Item: Previous year".
Show values as : rank.
For example 1: I want to see 'the revenue of film by years by genre" during four years.
Syntax 1: Remove the ' third revenue column' -> Choose " Second column" -> Selection "Show values as" -> Choose " Rank Largest to Smallest" -> Choose "Base field : Years".
Syntax 2: Choose "second column" -> Selection "Show values as" -> Choose "Rank Largest to Smallest "-> Choose "Base Field : Genre".
Show values as: index.
For example 1 : I want to see "The revenue of film related by genre by 2011,2012,2013,2014".
Syntax 1: Remove "second column and thrid column" -> Choose "year filter" -> Tick " 2011,2012,2013,2014".
For example 2: I want to see " The revenue of film" related by genre from 2011 to 2015 years." And filter top 5 countries "
Syntax 1: Selection " Second column" -> Choose " year filters" -> Tick " from 2011 to 2015" -> Selection " country field into 'column box'".
Syntax 2: Choose " country column" -> Selections : Values filters -> Add Item : 5" -> Choose " Show values as " -> Selections "Index" -> Choose " Number Format".
Inserting calculated fields.
For example 1: Want to see "the percent of each film's budget during about 2011-2015 years".
Syntax 1: View see "table included "title film column" and "revenue column" and "budget revenue".
Syntax 2: Go selection "Analyze -> Fields, Items -> Calculated Items"
Syntax 3: Add name: profit -> formula : Gross revenue - Budget".
Syntax 3: Calculate Profit, ROI and use Sort -> to know What title of film make more money ?
Calculations in Pivots vs. raw data.
For example 1: Want to see a summary table included calculated fields : ROI and Profit under two fields, the country field and the title field".
Pro tip: Calculations Using counts ( part 1)
For example : I want to see revenue of all of films that judged rank with G, GP, GP-13, R.
Syntax 1: View table with "rank included G, PG, PG-13, R with revenue.
Syntax 2: Add "title column in table" -> Selection "revenue" -> choose "values by count" -> Add "revenue into box" -> Choose "values by average " -> Add "calculated item"
Syntax 3: Add "calculated items" -> Name: revenue per title -> Add formula "Revenue per title = Revenue / COUNT (title) Note* Not Work -> Or Add "Number of Title in raw data".
Pro tip: Calculating Using Counts. ( part 2)
For example : I want to see FB Likes per tilte.
Syntax 1: See summary table with Cast FB likes, Movies FB likes.
Syntax 2: Add "Cast Likes column and Movies FB Likes column" into "box" -> Add 'calculated item" -> Name : FB Likes per Title = Total FB Likes / Number of Title".
Inserting a calculated Item. ( Not recommended )
The Solve Order & List Formulas Tools.
Module 6 : PivotCharts
Intro to PivotCharts
Definition : A Pivot Chart is simply chart that tied to a specific PivotTable, as you adjust filters and fields in your Pivot, the Pivot Chart updates dynamically.
Syntax 1: Select your Pivot and choose PivotChart from either the "Insert" tab or the "Analyze" tab.
Syntax 2: Select a chart type.
Syntax 3: The Pivot Chart will be inserted, and dynamically tied to the Pivot ( note : you can filter the view using either the pivot table or the chart itself )
Syntax 4 : 'Analyze, Design, Format".
PivotChart Demo : Column Chart
For example : I want to see Top 5 the most film genres that published from previous to now.
Syntax 1: Add column "number of title" into values and add column "genre" into axis column.
Syntax 2: Analyze -> PivotChart -> Column.
Syntax 3 : Use Filters to display data that you want to observe and view.
Syntax 4 : Genre -> Choose "Sort A to Z " -> Choose "Descending ( Z to A )by -> Choose "Sum of Title"
Syntax 5: Select "Genre column" -> Chooose "Value Filters" -> Choose "Top 10". Or Use Filter from Chart -> Choose "Genre" -> Select "Value filters" -> Select "Top 5".
PivotChart Demo : Pie / Donus Chart
For example : I want to see "movies trends by rating".
Syntax 1 : Select all "Pivot table above" -> Choose "Analyze " -> Choose " Entire Pivottable" -> Choose -> Copy -> Past into a blank space.
Syntax 2: Choose " rating colum" into "row box" and -> Move "genre" into "filter box"
Syntax 3 : Choose "entire pivot table below" -> Choose "analyze" -> Choose 'rename : title for rating"
Syntax 4: Choose "Entire Pivot Table Above" -> Choose "Analyze" -> Choose "Rename" : Title for genre".
Syntax 5: Choose "Rating colum" -> Select "More Sort Option" -> Choose "Descending ( Z to A by)" -> Choose "Sum of Number Title".
PivotChart Demo : Clustered Bar Chart
For example : I want to see " The number of films distributed rating according by genre is how ?
Syntax 1: Copy Pivot table -> Add 'genre column" into "colum box"
Syntax 2: Choose rating category included "R, PG, PG-13, G"
Syntax 3: Choose "genre" -> Select "More Sort Options" -> Select Descending Z-A ( sum number of title)
Syntax 4: Choose "Value filters" -> Choose "Top 10" -> Adjust "Top 5"
Pro tip : Prevent Charts from Resizing with Cells
For example : I want to see " Charts don't change when I move cells".
Syntax 1: Choose "All
Changing Chart Types on the Fly
For example 1: I want to change Pivot Chart that rating data of column move rating data of notion
Syntax 1: Choose "Pivot Chart" -> Select " Design -> Switch Row/ Column"
Syntax 2: Choose "Pivot Chart" -> Select "Change Chart Type" -> Choose "Stacked Bar"
Syntax 3: We can choose " more Sort Options" to filter by what I want to see data view.
PivotChart Demo : Stacked Area Chart
For example : I want to see "Pivot Chart show Film's revenue by realease"
Syntax 1: Choose : Pivot Table Before -> Select "Select to Entire Pivot Tabe" -> Choose : Copy-> Choose : Paste.
Syntax 2: Add : Revenu field to Values -> Add : Release to Axis.
Syntax 3: Choose : Pivot Table -> Select : Design -> Select : Pivot Chart -> Choose : Line Chart.
Syntax 4: Add : Rating move Axis -> Choose : Pivot Chart -> Select : Change Chart Type -> Select : Area -> Choose : Stacked Area.
PivotChart Layout & Styles
For example : I want to edit :Pivot table with layouts & Styles.
Syntax 1: Choose : Pivot Table 1 -> Select : Design -> Choose : properties. -> Add : Add Chart Element : Axis Title, Chart Title, Data Labels -> Adjust : Format Chart Area.
Moving PivotCharts to New Sheets
For example : I want to Move : Pivot Chart separate Pivot Table Control.
Syntax 1: Choose : Genre's Chart -> Choose : Design -> Select : Move Chart -> Select : Chart from Box.
Applying Slicers & Timeline to Multiple Charts.
For example : I want to apply : Rating and Timeline filter to see how data change on Pivot Chart.
Syntax 1: Choose : Title Chart by Genre -> Select : Analyze -> Select : Insert Slicers -> Tick / Add : Rating.
Syntax 2: Choose : Filter box -> Select : Options -> Choose : Report Connections -> Add : Pivot Table ( that see how the data change )
Syntax 3: Choose : R, GP, GP+13 -> To see the data change in Pivot Chart.
Syntax 4: Choose : Pivot Chart -> Select : Analyze -> Select : Insert Timeline -> Choose : Options -> Select : Connect Pivot Table ( to see data change )
Building a Dynamic Dashboard.
For example : I want to create : Dashboard" for IMDb Movie Data.
Syntax 1: Select : Move and don't size -> Choose : View - no tick gridlines -> Select : Entire Pivot - hide all list -> Insert : Title-name : IMDb Dashboard -> Format & Styles : Each Pivot Chart.
Syntax 2 : Choose : All Pivot Chart -> Format : Align
Module 7: Pivot Table Case Studies
Setting Expectations
Step 1: The goal of these case studies is to demonstrate how the concepts covered in this course can be applied to a range of different data sets.
Step 2: Some data may have been altered or fabricated to facilitate the demonstrations
Step 3: Homework exercise will be provided after each case study.
U.S Voters ( 2012 )
Step 1: Dataset summary : 2012 population and voter registration data from U.S Census Bureau, by state and age group.
Step 2: Dimensions and measures: - Dimensions ( stage, age) - Measures ( total population, citizen population, registered voters, confirmed voters )
Step 3: Concept covered : - calculated fields, - Value setting ( % of column, % of parent )
Syntax 1: Add : State field into row + Add : citizen population, confirmed voter fields into values.
Syntax 2: Analyze -> Calculated field : Voter population % = Confirmed voters / Citizen populations.
Syntax 3: Select : More Sort Option -> Select : Descending ( Z to A ) by : Sum of Voter Population %.
Syntax 4: Add : Age filed into Rows -> Move : State field into Filter -> Add : Confirmed Voter into Values -> Select : values as : % of Column Total -> Choose : View rate of voter population at top which age category ?
1 more item...
San Francisco Salaries
Step 1: Dataset summary : Salary information from San Francisco government employees, 2011 - 2013
Step 2: Dimensions & measures - Dimensions ( Employee name, year, employee ID, Job Title ) ; - Measures ( Base Pay, Overtime Pay, Other Pay )
Step 3: Concept covered : - Sorting & filtering ; - Grouping ; - Calculated fields ; - Table Layouts.
Syntax 1: Add : Employ Name field into Rows -> Add : Base Pay, Other Pay, Overtime Pay into Values -> Insert : Calculated Field - Total Pay = ( Base Pay + Overtime Pay + Other Pay ) -> Select : More Sort Option -> Choose : Descending - Sum of Total Pay -> View names who have top total pay.
Syntax 2: Add : Year field into filter -> Add : Job title into Rows -> View difference between 2 person who have top total pay.
Syntax 3: Insert : Calculated field - Overtime % = Overtime Pay/ Base Pay. -> More Sort Option : Descending - Sum of Overtime % -> Select : Value filter - Great than > 50.000 -> Label - Begin with : Steve ->To view data of people who have top overtime pay.
Syntax 4: Remove : Employee name Field from row -> Add : Job Title field into rox -> Select : More Sort Option - Descending (Sum of base pay ) -> Add : Base Pay Field into Row -> Choose & Format - Values as Average.
1 more item...
Shark Attack Records
Step 1 : Dataset summary - Shark attack records 1900 - 2016
Step 2: Dimensions & Measures : - Dimension ( case number, date, type, country, area, location, activity, name gender, age, injury, fatal ( Y/N), Species, Investigator of source ; - Measures : none.
Step 3: Concept covered : date grouping, value setting (% column, running total ), PivotCharts.
Syntax 1: Add : Case Number to Values -> Add : Years to Rows -> Keep Select 12 years ( 2005 - 2016) -> Add : Case Number to Values -> Add : Case Number to Values.
Syntax 2: Choose : Case Number 2 -> Select : Values as % of Column total -> Choose : Case Number 2 -> Select : Values as Running Total in.
Syntax 3: Add : gender to Row -> Remove : Case number 3.
Syntax 4: Add : Age to Row -> Remove : Unknow -> Add : Chart with Histogram -> Analyze : Field Buttons ( Hide All )
1 more item...
Stock Market Data
Step 1: Dataset summary - 3 month sample of stock market data for 500 publically-traded companies.
Step 2: Dimensions & measures : - Dimensions ( Date, symbol ) ; - Measures ( Open, High, Low, Close, Volume )
Step 3: Concept covered : - Sorting and filtering ; - Conditional formatting ( highlight cells, data bars ) ; - Value settings ( % of difference from )
Syntax 1: Raw Data develop over time : Insert -> Table
Syntax 2: Insert Pivot Table -> Name : Stock Market Pivot 2 -> Select : Date colum into Row Box -> Ungroup : date field -> Choose : only Date.
Syntax 3: Add : Open, High, Low, Close and Volume field into Value box -> Format all number -> Rename : column name.
Syntax 4: Add : Close field into value boxs -> Rename : Daily change -> Adjust : width column -> Select values as % different from -> Choose : Highlight cell rules into Daily change column -> Format : fill green for data >0 and fill red for data <0.
1 more item...
Baseball Team Stats
Step 1: Dataset summary - Major League Baseball Team Statistics by season, 1995 - 2015.
Step 2: Dimensions & Measures : - Dimensions : Season Start, Year, League, Team (Short), Team ( Full), Division, DivWin, WCWin, LgWin, WSWin ; - Measures : G, W, L, RS, AB, H, 2B, 3B, HR, BB, SO, SB, CS, RA, ER, ERA, CG, SHO, SV.
Step 3: Concept covered : - Sorting & Filtering ; - PivotCharts & Slicers ; - Calculated fields.
Syntax 1: Add : Year into row box -> Add : Win field into value box -> Add : Loss field into value box -> Add : Team ( full) into Filter box -> Rename : win, losses field
Syntax 2: Choose : Analyze -> Add : Calculated field -> Win % = W / G -> Choose : Sort Z - A by sum of win % -> Choose filter : Boston Red Sox -> Move : Year Field into filter -> Move : Team (Full) Field into row boxs.
Syntax 3: Add : League Field into row box -> Add : Division Field into row box -> Add : Team ( Full ) into row box -> Add : Year Field into filter box.
Syntax 4: Add : DivWin Field into Filter box -> Add : WCWin Field into Filter box -> Add : LgWin Field into Filter box -> Add : WsWin Field into Filter box -> Add : Year Field into Rows Box -> Design -> Select : Report Layout -> Choose : Show in Outline Form -> Choose : WSWin -Yes.
1 more item...
San Diego Burrito Ratings
Step 1: Dataset Summary - Burrito ratings and Yelp Reviews from San Diego restaurants in 2016.
Step 2: Dimensions & Measures : - Dimensions : Location, Burrito, Date, Recommendation, Reviewer, Notes ; - Measures : Yelp Ratings, Cost, Tortilla ( 0-5), Temp (0-5), Fillings (0-5), Meat Volume (0-5), Uniformity (0-5), Salsa (0-5), Synergy (0-5), Warp Quality (0-5)
Step 3: Concept covered : - Calculating with COUNT ; - Summarize values by ( SUM, AVERAGE ) ; - Show Values As ( Rank ) ; - Conditional Formatting.
Syntax 1: Add : Tortilla (0-5) Field, Temp (0-5) Field, Fillings (0-5) Field, Synergy (0-5) Field into Value box -> Select : Tortilla Temp, Fillings, Synergy -> Choose : Summarize values by : Average -> Click : Number Format : Decimal
Syntax 2: Add : Calculation field -> Add name : Average Score = tortilla (0-5) + temp (0-5) + fillings (0-5) + synergy (0-5) / 4 -> Add : #Reviews into Raw data -> Click : Change Source Data Pivot -> Change : Calculation : Average Score = Tortilla (0-5) + Temp (0-5) + Fillings (0-5) + Synergy (0-5) / 4 / ( #Reviews ).
Syntax 3: Add - Average Score 2 into Values -> Change - Average 2 - Show values as - Rank to largest to smallest -> Select - Rank -> Descending Z to A -> Select - Average Score - Conditional formatting - color scales -> Select - Average Tortilla (0-5) - conditional formatting - Hilight cells rules - greater than > 4 fill green -> Less than < fill red.
Syntax 4: Copy - Conditional formatting of Tortilla column -> Select - Format Painter -> Choose - Temp (0-5) -> Choose - Fillings (0-5) -> Choose - Synergy (0-5).
1 more item...
Daily Weather Conditions
Step 1: Dataset summary : - Daily weather conditions in Boston, Massachusetts from Jan-Dec 2016.
Step 2: Dimensions & Measures : - Dimensions : Date, Conditions ; - Measures : Max Temp (F), Mean Temp (F), Min Temp (F), Max Wind Speed ( MPH), Precipation (in)
Step 3: Concept covered : - Sorting & Filtering ; - Pivotcharts ; - Date Grouping ; - Calculating with COUNT.
Syntax 1: Add date into Row box -> Choose Months into Filter box -> Add Mean field into Row Box.
Syntax 2: Select : Mean Field - Choose summarize value by Average -> Select Sort - Choose Descending Z to A by Mean Field -> Choose Mean Field - Select conditional formatting - Choose : data scale.
Syntax 3: Choose Analyze -> Select Pivot Chart -> Choose : Line -> Add Format Data Label -> Choose Format Data Series -> Smooth Line -> Add Insert Timeline.
Syntax 4: Choose Analyze -> Select Entire Pivot -> Copy -> Paste.
1 more item...
Spartan Race Facebook Posts
Step 1: Dataset summary - Spartan Race Facebook posts from Aug to Oct 2016.
Step 2: Dimensions & Measures : - Dimension : Page, Date of Post, Post Copy, Link, Post Type, Hour of Post, Time of Day ; - Measures : Shares, Reactions, Likes, Comments.
Step 3: Concept Covered : - Show values as ( % of Column ) ; - Calculated Fields ; - Calculating with COUNT.
Syntax 1: Add : Copy Post into Value box -> Add : Post Type into Row box -> Choose : More Sort Option -> Select : Descending Z to A by Count Post Copy -> Select : Analyze -> Choose : Pie Chart -> Adjust : Formatting Chart.
Syntax 2: Add : Time of Day into Row box -> Choose : Analyze - Pivot Chart - Pie Chart -> Add : Hours of Post into Row Box -> Choose : More Sort Option -> Choose : descending A to Z.
Syntax 3: Add : Shares Field into Value -> Add : Reaction Field into Value -> Add : Like Field into Value -> Add : Comment Field into Value -> Choose : Analyze - Calculation - Total engagement = Shares + Reactions + Comments -> Choose - More Sort Options -> Choose - Descending Z to A by Total Engagement.
Syntax 4: Choose : Raw Data -> Add : # of Posts -> Choose : Refresh -> Choose - Analyze -> Add : Calculation - Engagement Per Post = ( Shares + Reactions + Comments ) / # of Posts -> Choose : More Sort Option -> Descending From Z to A by Engagement Total per Post -> Add : Copy Post Field into Row box.
1 more item...
Module 8: Resources & Next Step