K-Drama Analytics Dashboard in Excel

Clean, analyze, and visualize 250 K-dramas in an interactive Excel dashboard.

Introduction

30 Second Summary

You have a favorite K-drama streaming network. Maybe it's tvN, maybe it's Netflix. But which one actually produces the highest-rated dramas? Real data can answer that question, and knowing how to pull insights from data is one of the most in-demand skills in the job market.

In this project, you will build an interactive analytics dashboard in Microsoft Excel using a real dataset of the top 250 Korean dramas. You will clean the data, calculate key metrics with formulas, and create charts that update with a single click.

What You'll Build

An interactive Excel dashboard with bar charts, line charts, pie charts, and slicers that let you explore K-drama ratings, genres, and network trends across 250 dramas.

By the end of this project, you'll have:

  • A cleaned and structured Excel Table with 250 K-dramas and their metadata ready for analysis.
  • A summary sheet with formulas (COUNTIF, AVERAGEIF, IF) that calculate key metrics like average ratings and genre counts.
  • An interactive dashboard with PivotCharts and Slicers that filter all charts simultaneously with a single click.
  • Secret Mission: Build a K-Drama Recommender tool that uses XLOOKUP to instantly display details for any drama you select.

Are there any prerequisites?

You need Microsoft Excel (Microsoft 365 or Excel 2021+) installed on a Windows PC. No prior Excel experience is required.

You will also create a free Kaggle account to download the dataset. Step 1 walks you through the full setup.

Set Up Your Workspace

Every data analytics project starts with a dataset. Before you can build charts and dashboards, you need real data to work with.

In this step, you will download a dataset of 250 top-rated Korean dramas from Kaggle, open it in Microsoft Excel, and save it as a workbook ready for analysis.

In this step, get ready to:
  • Create a free Kaggle account and download the K-drama dataset.
  • Open the data in Excel and save it as an .xlsx workbook.
  • Explore the dataset structure and get familiar with the columns.
Create a Kaggle account and download the dataset

Kaggle is a platform where data scientists share datasets and compete in machine learning challenges. You need a free account to download the K-drama dataset you will analyze in this project.

  • Open your web browser and go to kaggle.com.
  • Click Register in the top right corner.
  • Choose Register with Google (quickest option) or Register with Email.
  • Complete the registration form with your name and email.
  • If prompted, enter the six-character verification code sent to your email.
  • Agree to the Terms of Use when prompted and click Next to finish creating your account.

What is Kaggle?

Kaggle is a free platform with over 300,000 public datasets covering everything from stock prices to movie ratings. Data analysts use it to find real-world data for practice and portfolio projects.

  • Once signed in, navigate to the dataset page by pasting this URL into your browser's address bar:
https://www.kaggle.com/datasets/ahbab911/top-250-korean-dramas-kdrama-dataset
  • Click the Download button near the top of the dataset page.
  • Kaggle will download a ZIP file to your computer. Open the ZIP and extract the kdrama.csv file to a location you can easily find, such as your Desktop or Downloads folder.

What is a CSV file?

A CSV (Comma-Separated Values) file stores tabular data as plain text. Each line is a row, and commas separate the columns. Excel can open CSV files directly, but they do not support formatting or formulas until you save them as an Excel workbook.

Can't find the Download button?

Make sure you are signed in to your Kaggle account. Kaggle requires sign-in before allowing dataset downloads. If the page looks broken or unstyled, try refreshing it.

help me find and download the K-drama dataset from Kaggle

Open the CSV in Excel and save as a workbook

Now that you have the raw data file, you need to open it in Excel and save it as an .xlsx workbook. This ensures you do not lose any formatting or formulas you add later.

  • Press the Windows key to open your search bar.
  • Type Excel and press Enter to open Microsoft Excel.
  • Click File in the top left corner.
  • Click Open, then click Browse.
  • Navigate to the folder where you saved kdrama.csv and select it.
  • Click Open. Excel imports the CSV data into a new workbook.

You should see rows of K-drama data filling the spreadsheet, with column headers in row 1.

  • Save the file as an Excel workbook by clicking File in the top left corner.
  • Click Save As, then click Browse.
  • In the Save as type dropdown, select Excel Workbook (*.xlsx).
  • Keep the filename as kdrama and click Save.

Why save as .xlsx?

CSV files are plain text. They cannot store cell formatting, formulas, multiple sheets, or Excel features like Tables and PivotTables. Saving as .xlsx converts it to a full Excel workbook that supports everything you will build in this project.

Excel opened the data as one column?

If all the data appears in a single column separated by commas, Excel may not have parsed the CSV correctly. Close the file, then re-open it using File > Open > Browse and make sure you select the file with the .csv extension. Excel should split it into columns automatically.

help me fix my CSV import in Excel where all data appears in one column

Take a quick tour of the data

Before jumping into analysis, it helps to understand what information you are working with. Your dataset contains 250 rows (one per K-drama) and 16 columns of metadata.

  • Scroll right across the columns in row 1. You should see these 16 headers: Name, Aired Date, Year of release, Original Network, Aired On, Number of Episodes, Duration, Content Rating, Rating, Synopsis, Genre, Tags, Director, Screenwriter, Cast, Production Companies.
  • Scroll down to confirm you have 250 rows of data (plus the header row, so row 251 is the last).
  • Browse through a few entries and look for dramas you recognize. Notice how the Genre column contains multiple genres separated by pipes (for example, Romance|Youth|Drama|Fantasy).
  • Check the Rating column. Values are numeric scores roughly between 8.0 and 9.2 for these top-rated dramas.

Where does this data come from?

This dataset contains the top-ranked 250 Korean dramas from MyDramaList, a community site where fans rate and review shows. Networks like tvN, Netflix, JTBC, and SBS are all represented.

Your workspace is ready. Next up, you will clean this data and format it as an Excel Table to unlock powerful analysis features.

Clean and Structure the Data

Your workbook is open and your raw data is loaded. Now it's time to turn that raw CSV import into something you can actually analyze.

Real-world data is messy. Before running any analysis, you need to check for duplicates, handle missing values, and make sure Excel recognizes your numbers as numbers. This process is called data cleaning, and it's the first skill every data analyst reaches for.

In this step, get ready to:
  • Remove duplicate rows and handle blank cells in key columns.
  • Format your data as a structured Excel Table.
  • Convert the Rating column from text to proper numeric values.
Remove duplicates and handle blank cells

Duplicate rows and missing values can skew your analysis. You'll use Excel's built-in tools to catch both before going any further.

  • Select all your data by pressing Ctrl+A.
  • Click the Data tab in the ribbon at the top.
  • Click Remove Duplicates in the Data Tools group.
  • In the dialog that appears, make sure all columns are checked.
  • Click OK.

Excel displays a message confirming how many duplicates were found and removed. You should see "0 duplicate values found" for this dataset.

What does Remove Duplicates do?

Excel compares every row across all selected columns. If two rows match in every single column, one is deleted. Running it early prevents inflated counts in your analysis later.

Next, check for blank cells in the two columns that matter most for your analysis: Rating and Genre.

  • Click the column header for the Rating column to select the entire column.
  • Press Ctrl+G to open the Go To dialog.
  • Click Special.
  • Select Blanks and click OK.

If Excel highlights any blank cells, type Unknown in each blank cell. If no blanks are found, Excel tells you no cells were found. Repeat this same process for the Genre column.

Why fill blanks with Unknown

Blank cells cause formulas like COUNTIF and AVERAGEIF to skip rows silently. Filling blanks with a placeholder like "Unknown" keeps those rows visible in your analysis so you know they exist.

Go To Special not highlighting anything?

Make sure you selected the correct column by clicking directly on the column letter header (e.g., the letter above the Rating data). If you selected a range that has no blanks, Excel displays a "No cells were found" message. That means you're good to move on.

Help me find blank cells in my Rating column.

Format the data as an Excel Table

An Excel Table is a structured data range that gives you automatic filtering, consistent formatting, and named references you can use in formulas. Converting your data into a Table now makes everything you build in later steps easier.

  • Click any cell inside your data (for example, cell A1).
  • Go to the Insert tab in the ribbon.
  • Click Table (or press Ctrl+T).
  • In the Create Table dialog, confirm the range covers all 250 rows and 16 columns of your data.
  • Check the box labeled My table has headers.
  • Click OK.

Your data now has banded rows (alternating colors) and filter arrows on each column header. The Table Design tab appears in the ribbon.

  • Click the Table Design tab.
  • On the left side of the ribbon, find the Table Name field.
  • Replace the default name with KDramaData and press Enter.

Why name the table?

Naming your table KDramaData lets you write formulas like =COUNTIF(KDramaData[Genre],"*Romance*") instead of referencing raw cell ranges like F2:F251. It also makes creating PivotTables faster because Excel can auto-detect the table as a data source.

Table Design tab not appearing?

The Table Design tab only shows when your cursor is inside the table. Click any cell within your data to make it appear. If you still don't see it, your data may not have converted to a table. Try pressing Ctrl+T again.

Help me format my data as an Excel Table.

Clean the Rating column

When CSV files are imported, Excel sometimes stores numbers as text. A rating like "8.9" looks correct, but Excel can't calculate averages or find maximums on text values. You need to force Excel to recognize these as real numbers.

  • Click the column header for the Rating column to select the entire column.
  • Go to the Data tab in the ribbon.
  • Click Text to Columns in the Data Tools group.
  • In the wizard that appears, click Finish immediately (accept all defaults).

Why does Text to Columns fix this?

Text to Columns forces Excel to re-evaluate each cell's content. When it re-reads "8.9", it recognizes the value as a number and converts it. This is a quick trick that avoids manually editing hundreds of cells.

Verify the conversion worked by clicking any cell in the Rating column. Numbers align to the right side of the cell. Text aligns to the left.

  • Click a cell in the Rating column (for example, the first rating value).
  • Confirm the value is right-aligned in its cell.
  • Save your workbook by pressing Ctrl+S.

Rating values still left-aligned?

If the values are still left-aligned after Text to Columns, look for a small green triangle in the top-left corner of the cells. Click one of those cells, then click the yellow warning icon that appears. Select Convert to Number. You can select the entire Rating column first to convert them all at once.

Help me convert my Rating column to numbers.

Your data is now clean, structured, and ready for real analysis. Next up, you'll build a Summary sheet using formulas to pull insights directly from your KDramaData table.

Analyze with Formulas

Your data is clean, structured, and sitting inside a proper Excel Table. That means you can now reference columns by name in formulas instead of fiddling with cell ranges.

In this step, you will use Excel formulas to pull insights from the dataset. You will calculate averages, count dramas by network and genre, and classify each drama into a rating tier.

In this step, get ready to:
  • Build a Summary sheet with key metrics using COUNTA, AVERAGE, MAX, and MIN.
  • Count dramas by network and genre using COUNTIF and AVERAGEIF.
  • Add a Rating Tier column that classifies each drama using a nested IF formula.
Create the Summary sheet with key metrics

A dedicated Summary sheet gives you a single place to see your dataset's most important numbers at a glance. You will use structured references (like KDramaData[Rating]) so the formulas automatically cover all 250 rows.

  • Right-click any sheet tab at the bottom of Excel and select Insert.
  • Double-click the new sheet tab and rename it to Summary.
  • Click cell A1 and type K-Drama Summary Metrics as the sheet title.
  • Enter the following labels and formulas for your basic metrics:
B3: Total Dramas
C3: =COUNTA(KDramaData[Name])

B4: Average Rating
C4: =AVERAGE(KDramaData[Rating])

B5: Highest Rating
C5: =MAX(KDramaData[Rating])

B6: Lowest Rating
C6: =MIN(KDramaData[Rating])

What do these formulas do?

Each formula uses a structured reference like KDramaData[Name] to point at the entire column inside your table. This means if rows are added or removed, the formulas update automatically.

  • COUNTA counts all non-empty cells. It gives you the total number of dramas.
  • AVERAGE calculates the mean rating across all 250 dramas.
  • MAX and MIN find the highest and lowest ratings in the dataset.

You should see numbers populate in column C. The total count should be 250, and the average rating should be somewhere around 8.6.

Seeing a #REF! or #NAME? error?

Make sure you named your table KDramaData exactly (case-sensitive) in Step 2. You can check by clicking any cell in the data sheet table and looking at the Table Design tab for the table name.

Also confirm the column header is exactly Rating (not "Ratings" or "rating"). Structured references must match column headers character for character.

help me fix a #REF! or #NAME? error in my Summary sheet formulas

Now add the network-specific metrics below your basic stats. These formulas use COUNTIF and AVERAGEIF to filter results by network name.

  • Enter the following labels and formulas for network metrics:
B8: Dramas on tvN
C8: =COUNTIF(KDramaData[Original Network],"*tvN*")

B9: Dramas on Netflix
C9: =COUNTIF(KDramaData[Original Network],"*Netflix*")

B10: Avg Rating on tvN
C10: =AVERAGEIF(KDramaData[Original Network],"*tvN*",KDramaData[Rating])

B11: Avg Rating on Netflix
C11: =AVERAGEIF(KDramaData[Original Network],"*Netflix*",KDramaData[Rating])

Why the asterisks around network names?

The asterisks (*) are wildcards. They match any text before or after the network name. Some entries in the Original Network column may list multiple networks (like "tvN, Netflix"), so wildcards ensure you catch every drama that aired on that network.

COUNTIF counts how many rows match the criteria. AVERAGEIF averages the Rating column only for rows where the network matches.

  • Save your workbook by pressing Ctrl+S.
  • Verify that the tvN count is a number greater than zero. tvN produces more top-rated K-dramas than any other network in this dataset.

Seeing 0 for the network counts?

Double-check that you included the asterisks inside the quotation marks: "*tvN*" not *"tvN"*. The wildcards must be inside the quotes to work.

Also confirm the column header on your data sheet is exactly Original Network (with a space, capital O and N).

help me debug why my COUNTIF formula returns 0

Add genre analysis

The Genre column in the dataset uses pipe-separated values (like "Romance|Thriller|Drama"). Using COUNTIF with wildcards lets you count how many dramas include each genre without splitting the column.

  • In your Summary sheet, enter the following labels and formulas for genre counts:
B13: Romance Dramas
C13: =COUNTIF(KDramaData[Genre],"*Romance*")

B14: Thriller Dramas
C14: =COUNTIF(KDramaData[Genre],"*Thriller*")

B15: Comedy Dramas
C15: =COUNTIF(KDramaData[Genre],"*Comedy*")

B16: Fantasy Dramas
C16: =COUNTIF(KDramaData[Genre],"*Fantasy*")

What does this tell us?

Each formula scans the Genre column for a keyword. Because genres are pipe-separated, a single drama can appear in multiple counts. For example, a "Romance|Fantasy" drama counts toward both Romance and Fantasy totals.

This is intentional. It shows you how popular each genre is across the entire dataset, even when dramas blend multiple genres.

  • Save your workbook by pressing Ctrl+S.
  • Verify that Romance shows the highest count among the four genres. Romance is the most common genre tag in the top 250 K-dramas.

Getting unexpected genre counts?

Make sure the column header on your data sheet is exactly Genre (capital G, singular). If you accidentally renamed it or have extra spaces, the structured reference will break.

help me troubleshoot why my genre COUNTIF formulas show unexpected numbers

Add a Rating Tier column to classify each drama

Raw numbers are useful, but categories make analysis easier. A Rating Tier column will label each drama as "Elite", "Excellent", "Great", or "Good" based on its rating. This categorical column will be useful when you build PivotTables in the next step.

  • Switch back to your data sheet (the one with the KDramaData table).
  • Scroll right to find the first empty column after Production Companies.
  • Click the header cell of that empty column and type Rating Tier as the column name. Press Enter. The table automatically expands to include the new column.
  • In the first data cell of the Rating Tier column (directly below the header), enter this formula:
=IF([@Rating]>=9,"Elite",IF([@Rating]>=8.5,"Excellent",IF([@Rating]>=8,"Great","Good")))

What does this nested IF do?

  • The formula checks the Rating value of the current row ([@Rating] means "the Rating in this row").
  • If the rating is 9 or above, it assigns "Elite".
  • If not, it checks for 8.5 or above and assigns "Excellent".
  • If not, it checks for 8 or above and assigns "Great".
  • Anything below 8 gets "Good".

The [@Rating] syntax is a structured reference that always points to the Rating column in the same row. This is why formatting your data as an Excel Table in Step 2 pays off.

  • Press Enter. Because your data is in an Excel Table, the formula should automatically fill down to all 250 rows.
  • If the formula does not auto-fill, click the cell with the formula, press Ctrl+C to copy, then select the remaining empty cells in the Rating Tier column and press Ctrl+V to paste.
  • Save your workbook by pressing Ctrl+S.
  • Scroll through the Rating Tier column. You should see a mix of "Elite", "Excellent", "Great", and "Good" labels. Since these are the top 250 dramas, most should be "Great" or above.

Seeing the formula text instead of results?

If the cell displays the formula as plain text instead of calculating a result, the cell might be formatted as Text. Select the Rating Tier column, go to the Home tab, and change the Number Format dropdown from "Text" to "General". Then re-enter the formula.

help me fix my Rating Tier formula that shows as text instead of calculating

✔️ Awesome, I've got everything!

Great. Make sure you have saved your workbook with both the Summary sheet and the new Rating Tier column.

ⓧ I'd like to double check the full formulas

Here is the complete state of your Summary sheet and the Rating Tier formula for reference.

=== SUMMARY SHEET ===

A1: K-Drama Summary Metrics

B3: Total Dramas
C3: =COUNTA(KDramaData[Name])

B4: Average Rating
C4: =AVERAGE(KDramaData[Rating])

B5: Highest Rating
C5: =MAX(KDramaData[Rating])

B6: Lowest Rating
C6: =MIN(KDramaData[Rating])

B8: Dramas on tvN
C8: =COUNTIF(KDramaData[Original Network],"*tvN*")

B9: Dramas on Netflix
C9: =COUNTIF(KDramaData[Original Network],"*Netflix*")

B10: Avg Rating on tvN
C10: =AVERAGEIF(KDramaData[Original Network],"*tvN*",KDramaData[Rating])

B11: Avg Rating on Netflix
C11: =AVERAGEIF(KDramaData[Original Network],"*Netflix*",KDramaData[Rating])

B13: Romance Dramas
C13: =COUNTIF(KDramaData[Genre],"*Romance*")

B14: Thriller Dramas
C14: =COUNTIF(KDramaData[Genre],"*Thriller*")

B15: Comedy Dramas
C15: =COUNTIF(KDramaData[Genre],"*Comedy*")

B16: Fantasy Dramas
C16: =COUNTIF(KDramaData[Genre],"*Fantasy*")

=== DATA SHEET - Rating Tier Column ===

Column header: Rating Tier
Formula (all rows): =IF([@Rating]>=9,"Elite",IF([@Rating]>=8.5,"Excellent",IF([@Rating]>=8,"Great","Good")))

Your Summary sheet now tells a clear story about the dataset. Next up, you will turn these numbers into visual charts using PivotTables and PivotCharts.

Build PivotTables and Charts

Your Summary sheet proves you can calculate metrics from raw data. But numbers in cells only tell part of the story.

In this step, you will build PivotTables that automatically group and summarize your K-drama data by network and year. Then you will turn those PivotTables into visual charts that make trends immediately obvious.

In this step, get ready to:
  • Create a PivotTable showing average ratings by network.
  • Create a second PivotTable showing K-drama production counts by year.
  • Generate PivotCharts from each PivotTable to visualize your findings.
Create a PivotTable for network ratings

A PivotTable lets you drag and drop column names to instantly group, count, and average your data without writing any formulas. You will use one to answer the question: which networks produce the highest-rated K-dramas?

  • Switch back to the sheet containing your KDramaData table.
  • Click any cell inside the table.
  • Go to the Insert tab in the ribbon.
  • Click PivotTable, then select From Table/Range.
  • In the dialog that appears, select New Worksheet and click OK.
  • Right-click the new sheet tab at the bottom and rename it to Analysis.

You should see an empty PivotTable placeholder on the left and the PivotTable Fields pane on the right listing all your column names.

  • In the PivotTable Fields pane, drag Original Network to the Rows area at the bottom of the pane.
  • Drag Rating to the Values area.

Excel defaults to Sum of Rating, but you want the average rating per network.

  • Click the dropdown arrow on Sum of Rating in the Values area.
  • Select Value Field Settings.
  • Choose Average from the list and click OK.

Your PivotTable now shows the average rating for each network. You should see networks like tvN, Netflix, SBS, and others listed with their average scores.

What is a PivotTable?

A PivotTable is Excel's most powerful analysis tool. It takes a flat list of data and instantly groups it into a summary view based on whichever columns you drag into the Rows, Columns, and Values areas.

Instead of writing a separate AVERAGEIF formula for every network, the PivotTable calculates all of them at once. When your source data changes, you can refresh the PivotTable with a single click.

PivotTable Fields pane not showing?

Click directly inside the PivotTable area on the worksheet. The Fields pane only appears when a PivotTable cell is selected. If you accidentally clicked outside it, the pane disappears.

Also check that you started from a cell inside the KDramaData table before inserting. If the PivotTable is completely empty with no field names, delete the sheet and try again from a cell inside your table.

Help me fix my PivotTable Fields pane in Excel.

Create a second PivotTable for yearly production

Your second PivotTable answers a different question: how has K-drama production grown over time? You will place it on the same Analysis sheet below the first one.

  • Click an empty cell several rows below your first PivotTable on the Analysis sheet.
  • Go to the Insert tab and click PivotTable > From Table/Range.
  • This time, select Existing Worksheet and confirm the location points to the cell you clicked.
  • Click OK.
  • In the PivotTable Fields pane, drag Year of release to the Rows area.
  • Drag Name to the Values area. Excel defaults to Count of Name, which is what you want.

Your PivotTable now shows how many dramas from the top 250 were produced in each year. You should see a clear growth trend toward recent years.

  • Right-click any year value in the PivotTable and select Sort > Sort Smallest to Largest to arrange years in ascending order.

Why Count of Name instead of Count of Year?

When you drag a text field like Name into Values, Excel automatically counts entries. Each drama has a unique name, so counting names gives you the total number of dramas per year. You could use any text column here and get the same count.

Seeing years grouped into ranges?

Excel sometimes auto-groups numeric years into ranges like 2000-2005. Right-click any grouped year in the PivotTable and select Ungroup. This restores individual years.

Help me ungroup years in my PivotTable.

Create PivotCharts from each PivotTable

A PivotChart is a chart linked directly to a PivotTable. When your PivotTable data updates, the chart updates automatically. You will create a bar chart for network ratings and a line chart for yearly trends.

  • Click inside the first PivotTable (the one showing Average of Rating by network).
  • Go to the PivotTable Analyze tab in the ribbon.
  • Click PivotChart.
  • In the chart type dialog, select Bar Chart (the horizontal bars option) and click OK.

A bar chart appears showing each network's average rating as a horizontal bar. Now create the second chart.

  • Click inside the second PivotTable (the one showing Count of Name by year).
  • Go to the PivotTable Analyze tab and click PivotChart.
  • Select Line Chart and click OK.

You now have two charts on your Analysis sheet. Time to give them clear titles.

  • Double-click the title on the bar chart and type Average Rating by Network.
  • Double-click the title on the line chart and type K-Drama Production by Year.
  • Save your workbook.

Your bar chart should clearly show which networks have higher average ratings. The line chart should show K-drama production increasing over time, with a spike in recent years.

Why a bar chart for networks and a line chart for years?

Bar charts are ideal for comparing categories side by side. You can instantly see which network has the longest bar. Line charts are ideal for showing trends over time because the connecting line reveals growth patterns at a glance.

Don't see PivotTable Analyze in the ribbon?

The PivotTable Analyze tab only appears when a PivotTable cell is actively selected. Click directly inside a PivotTable cell (not on the chart or an empty cell) and the tab will appear in the ribbon.

Help me find the PivotTable Analyze tab in Excel.

Your PivotTables and charts are ready. Next up, you will assemble these charts into a polished, interactive dashboard with slicers that filter everything with a single click.

Assemble the Interactive Dashboard

Your PivotTables and PivotCharts are generating real insights about K-drama ratings and production trends. Now it is time to bring everything together into a single interactive dashboard that tells the full story at a glance.

In this step, you will create a dedicated Dashboard sheet, move your charts onto it, add a third chart for rating tiers, and wire up Slicers that filter all three charts simultaneously with a single click.

In this step, get ready to:
  • Create a Dashboard sheet and move all three PivotCharts onto it.
  • Add Slicers that filter every chart at once.
  • Polish the layout with a title, color palette, and hidden gridlines.
Create the Dashboard sheet and move your charts

A dashboard works best when all the visuals live on one clean sheet, separate from the raw PivotTables that power them.

  • Right-click any sheet tab at the bottom of Excel and select Insert, then choose Worksheet.
  • Double-click the new tab and rename it to Dashboard.
  • Switch to the Analysis sheet.
  • Right-click your Bar Chart ("Average Rating by Network") and select Move Chart.
  • In the Move Chart dialog, select Object in and choose Dashboard from the dropdown.
  • Click OK.
  • Repeat the same process for your Line Chart ("K-Drama Production by Year"): right-click it, select Move Chart, and move it to the Dashboard sheet.

Now you will create a third PivotTable and chart for the Rating Tier breakdown.

  • Go back to the Analysis sheet.
  • Click an empty cell below or to the right of your existing PivotTables.
  • Go to the Insert tab and click PivotTable > From Table/Range.
  • Select Existing Worksheet and click the empty cell you selected, then click OK.
  • In the PivotTable Fields pane, drag Rating Tier to Rows.
  • Drag Name to Values (it defaults to Count).
  • Click inside this new PivotTable, then go to the PivotTable Analyze tab and click PivotChart.
  • Choose Pie as the chart type and click OK.
  • Right-click the new Pie Chart and select Move Chart. Move it to the Dashboard sheet.

Switch to your Dashboard sheet. You should see all three charts: the Bar Chart, Line Chart, and Pie Chart.

Why a separate Dashboard sheet?

Keeping charts on their own sheet means viewers only see the visual story, not the underlying PivotTable data. This is exactly how professional Excel dashboards are structured in workplaces.

Don't see all three charts on Dashboard?

Charts may be stacked on top of each other. Click and drag each chart to a different area of the sheet so they are all visible. If a chart is missing, go back to the Analysis sheet and confirm you selected Dashboard (not "New sheet") in the Move Chart dialog.

Help me move my PivotCharts to the Dashboard sheet

Add Slicers for interactivity

Slicers are visual filter buttons. When you click a value in a Slicer, it instantly filters the connected PivotTables and their charts.

  • On the Dashboard sheet, click any of your PivotCharts to select it.
  • Go to the PivotTable Analyze tab in the ribbon.
  • Click Insert Slicer.
  • In the Insert Slicers dialog, check Original Network and Content Rating.
  • Click OK. Two slicer panels appear on the Dashboard.
  • Drag both slicers to the top of your Dashboard, above the charts.

The slicers are currently connected to only one PivotTable. You need to connect them to all three so every chart updates together.

  • Right-click the Original Network slicer.
  • Select Report Connections.
  • Check the boxes next to all three PivotTables in the list, then click OK.
  • Repeat for the Content Rating slicer: right-click it, select Report Connections, check all three PivotTables, and click OK.

Test your slicers by clicking a network name (like tvN) in the Original Network slicer. All three charts should update to show only tvN data.

Clear a slicer filter

Click the small funnel icon with an X in the top-right corner of any slicer to clear the filter and show all data again.

Slicer only filters one chart?

This means the slicer is not connected to all three PivotTables. Right-click the slicer, select Report Connections, and make sure every PivotTable in the list is checked.

Help me connect my slicers to multiple PivotTables

Polish the dashboard layout

A clean layout makes the difference between a spreadsheet and a professional dashboard. A few formatting touches will bring everything together.

  • Click cell A1 on the Dashboard sheet and type K-Drama Analytics Dashboard.
  • Select the title text. In the Home tab, increase the font size to 18 or larger and click Bold.
  • Go to the View tab and uncheck Gridlines to remove the cell grid from the background.
  • Arrange your three charts so they fill the space evenly. Place the Bar Chart and Line Chart side by side below the slicers, and the Pie Chart below or beside them.
  • To set a cohesive color palette, click each chart and use the Chart Design tab > Change Colors to pick a matching color scheme across all three charts.
  • Resize all charts to similar dimensions by dragging their corner handles.

Save your workbook. Your interactive dashboard is complete.

What makes this a real dashboard?

The combination of connected Slicers and PivotCharts is the same pattern used in professional Excel dashboards at companies. When someone clicks "tvN" in the slicer, every chart instantly shows only tvN data. That interactivity is what separates a dashboard from a static collection of charts.

Charts look cluttered or overlapping?

Click a chart to select it, then use the corner handles to resize. Hold Alt while dragging to snap charts to cell boundaries, which helps you align them neatly.

Help me arrange my Excel dashboard charts neatly

Secret mission

Build a K-Drama Recommender with XLOOKUP

Your dashboard shows big-picture trends across all 250 dramas. But what if someone wants to look up a specific drama and instantly see its details? In this secret mission, you will build a mini recommender tool using XLOOKUP, one of Excel's most powerful modern functions.

Clean Up Your Resources

Clean Up Your Resources

This project runs entirely locally in Microsoft Excel, so there are no ongoing costs or cloud resources to worry about.

Decide whether to keep your files for your portfolio, or delete them if you no longer need them.

Resources you used:

  • kdrama.csv file downloaded from Kaggle.
  • kdrama.xlsx workbook with all your sheets (data, Summary, Analysis, Dashboard, Recommender).

Keep everything running

No action needed. Choose this if you want to keep your dashboard as a portfolio piece or continue exploring the data.

  • Your kdrama.xlsx file stays wherever you saved it on your computer.
  • Your free Kaggle account remains active with no charges.
  • Consider moving the workbook to a dedicated portfolio folder so you can find it later.

Pause - I'll come back to this later

Since this is a local Excel file, there is nothing to pause. Your files sit on disk and use no resources while closed.

  • Close Excel if you have it open.
  • Your workbook and all sheets will be exactly as you left them when you reopen the file.

Delete - I don't want to use this again

Remove all project files from your computer to start fresh.

  • Close Excel if you have it open.
  • Open File Explorer and navigate to the folder where you saved kdrama.xlsx.
  • Delete kdrama.xlsx (your workbook with all sheets).
  • Delete kdrama.csv (the original file you downloaded from Kaggle).
  • Empty your Recycle Bin to permanently remove both files.

Nice Work!

Nice Work!

You just built a fully interactive K-Drama Analytics Dashboard from scratch using real-world data. You took a raw CSV of 250 Korean dramas, cleaned it, analyzed it with formulas and PivotTables, and assembled a polished dashboard that filters with a single click.

Here's what you learned:

  • Cleaned and structured a real-world dataset of 250 K-dramas into a formatted Excel Table ready for analysis.
  • Built formula-driven analysis using COUNTIF, AVERAGEIF, and IF to calculate key metrics across networks and genres.
  • Assembled an interactive dashboard with PivotCharts and Slicers that filter all charts simultaneously.
  • Secret Mission: Built a K-Drama Recommender tool using XLOOKUP and Data Validation to instantly look up any drama's details.

Ready to quiz yourself?