Excel - 120 Years of Billboard Hot 100 Data in a Win Loss Sparkline - Episode 1930
2,021 views · Published 18 October 2014 · 21:54 · Indexed 14 November 2026
Channel: MrExcel.com · 2014 · Education
Microsoft Excel Tutorial: Music Acts in the Billboard Hot 100 using a 122-year Win/Loss Sparkline. 🎙️ Dive into musical history with MrExcel Podcast Episode 1930! 📊 In this episode, Bill Jelen takes us on a journey through 120 years of Billboard Hot 100 data, showcasing the power of win-loss sparklines. Starting with a pivot table, Bill demonstrates how to create a lookup table for the win-loss line, offering insights into the staying power of top artists. Learn how to use Power Pivot, insert slicers based on decades, and filter for the top 10 artists. Discover the beauty of win-loss sparklines, highlighting artists who not only topped the charts but remained influential for years. 🔍 Explore the creation of a presentation layer showcasing the "staying power" of top artists in the Billboard Hot 100. Bill explains the process of building columns for artists, win-loss sparklines, and the years from 1890 onward. Witness the magic of index and match functions, replacing V lookup for efficient data calculation. Bill shares tips on sparkline customization, advocating for a wider column to visualize 120 years of data effectively. 📈 The video goes beyond standard Microsoft features as Bill labels sparklines for added insights. While acknowledging the tediousness of the process, he emphasizes the significance it adds to understanding the sparkline data. Discover how to identify one-hit wonders and explore details behind each artist's chart performance. 👀 Don't miss the chance to broaden your Excel skills and gain a unique perspective on data visualization with MrExcel. Join Bill Jelen in Episode 1930 as he uncovers the secrets behind win-loss sparklines and unveils the hidden stories within the Billboard Hot 100 data. 🎶📉 #MrExcel #DataVisualization #ExcelTips #BillboardHot100 #WinLossSparkline Table of Contents (0:00) Win-Loss Sparklines in Excel (0:50) Creating a static lookup table using a pivot table (1:50) Filling Pivot Table blank cells with -1 for Win/Loss functionality (2:45) Converting pivot table to static values (3:15) Data in Power Pivot (3:55) Killing Compact Layout in favor of Tabular Layout (4:10) Adding a decade slicer for filtering (4:20) Preserve column widths in a pivot table (4:40) Formatting a Slicer (5:00) Sorting top values to top of pivot table (5:55) Selecting multiple decades from the slicer (6:10) Filtering to top 10 items in a pivot table (6:40) Which artists had staying power? (7:05) Building the presentation layer above the pivot table (7:40) Building 120 columns of year data using a formula (8:35) Why VLOOKUP would be slow (9:00) Using MATCH instead of VLOOKUP (9:50) Using INDEX, with an explanation of $F4 and B$2 mixed references (11:45) Win/Loss is looking for positive or negative (12:00) Inserting Sparkline (12:15) Small sparkline versus big sparkline (12:55) Dragging slicer to a new position of the worksheet (13:20) Plug for Sirius 40's on 4 Channel (13:35) Why you should add labels to sparklines (13:55) MOD formula to assist in identifying decade locations (14:50) Using a Line from the Insert Shapes (15:20) Making the line a dashed line (15:35) Making copies of the first line using Ctrl+Shift+Drag (16:20) Using Textbox for Decade Labels (17:15) Using fonts smaller than 8 in Excel (18:15) Replacing the temporary MOD formulas (18:30) Changing the color in the sparkline (18:45) Using Title Style (19:00) Elvis Presley discussion (19:10) Adding a Year Slicer from the Lookup table (19:25) Why you should build Slicer from lookup table (20:10) Looking for One-Hit Wonders (20:20) Shaun Cassidy discussion (20:30) Drilling down to see detail on one artist (21:00) Clicking Like really helps the algorithm #excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial This video answers these common search terms: Alabama Billboard hot 100 data Episode 1930 Index formula Lookup table Miami, Florida Pivot table Power Excel seminar Power pivot Top 10 artists Win-loss sparkline This video answers these common search terms: top 10 artists by decade excel tutorial win-loss sparkline tutorial microsoft excel sparkline demonstration excel data analysis for music charts historical music chart analysis power pivot for excel charts billboard hot 100 data visualization top artists staying power analysis pivot table for music chart history billboard hot 100 historical data exploration how to add chart on excel sparkline how to insert line sparklines in excel what are sparklines in excel how to make sparklines in excel what is sparklinbes excel Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1155081/
More from this channel
-
2:13
Excel Calculate Decimal Hours from two Time Cells - Episode 902
-
3:57
Excel 2007 - Color of Selected Sheets is too much like unselected sheets - Episode 907
-
2:48
Excel - Adding Equals Icon to Excel QAT - Build Formula with Mouse - Episode 911
-
3:30
Excel Grouping Percentages into Buckets for a Pivot Table - Method 2 - Episode 913
-
2:04
Excel Change Color of Selected Cells - Episode 914
-
4:38
Excel - Auto Numbering a List with Frequent Inserts & Deletes - Episode 916
-
2:00
Excel Hiding Data in Plain Site - Episode 919
-
2:20
Excel - Bring Back The Full Excel 2003 Dialogs In Excel! - Episode 920