Data Analytics & Story-Telling Techniques in Excel

Learn a hands-on 4-step framework to turn data into insights using Excel, including pivot tables, pivot charts, and slicers, with real life enrollments and ratings trends.

Explore step 1 of the data to insights framework: understand and comprehend a real-life dataset of student enrollment and course ratings across five anonymized courses, with 2012–2017 monthly data.

Download the Excel template provided with this module to follow along with the instructor and gain hands-on practice with the course illustrations.

Explore the data set for five online courses from 2012–2017, detailing enrollments and average ratings by course name, year, and month.

Analyze the dataset to answer questions about student enrollments and course ratings, using the data to insights framework to explore year-on-year trends, best selling courses, and yearly performance.

Examine year-over-year trends in course ratings and break them down by course, identifying the best rated and best performing courses over the last six years across five courses.

Analyze year-on-year student enrollment trends using pivot tables and pivot charts in Excel, selecting the data range and placing the PivotTable on the worksheet to reveal answers to key questions.

Use the PivotChart fields to place years on the axis and enrollments on the values, producing a PivotChart that shows year-on-year enrollments from 2012 to 2017.

Format the pivot chart by resizing, add data labels outside end, remove horizontal grid lines, and update the heading to 'year on year student enrollments' to show trends.

Analyze year-on-year enrollment trends in Excel with pivot charts and slicers, filtering by course name to reveal course-wise patterns and visualize total enrollments across years.

Explore year over year enrollment by using a slicer with pivot tables and pivot charts to view single or multiple courses, updating visuals to reveal course-wise trends and insights.

Utilize data analysis step 2 to identify the best selling course over the last six years with a pivot table and pivot chart in Excel.

Analyze course wise enrollments over six years by building a pivot chart, switching to a doughnut chart to reveal the most and least selling courses.

Add data labels to the pivot chart, convert enrollments to a percentage of the grand total, and interpret the doughnut chart to reveal top and bottom courses over six years.

Edit the chart title textbox to 'Course wise enrollment' and analyze which course was the best selling over the last 6 years by comparing enrollment percentages across five courses.

Explore analyzing course enrollments across years using pivot charts and slicers in Excel, filtering by year or multiple years to identify best selling courses.

Create a pivot table and pivot chart to display the percentage enrollments for all courses across all years in a single stacked column chart, enabling year-by-year comparison.

Demonstrates building a pivot chart and pivot table in Excel to show year-on-year enrollments by course, including selecting the data range and configuring the axis and legend.

Convert the absolute enrollments by course and year into a 100% stacked column chart to reveal proportional shares from 2012 to 2017, using the pivot chart method.

Convert absolute enrollments to percentages by setting the pivot chart values to show as % of row total, then add data labels to create a stacked column chart.

Add a chart title to a pivot chart in Excel and show the proportion of enrollments by course across 2012–2017, guiding insights in the fourth step of the framework.

Analyze year-over-year trends in student ratings from 2012 to 2017 using pivot tables and a pivot chart in Excel, calculating average ratings and formatting decimals.

Convert a pivot chart from a column to a plain line chart to reveal year-over-year ratings, then add data labels above the line to show 2012–2017 ratings.

Add a course name slicer to a pivot chart to view year-over-year ratings by course, toggle or multi-select courses, and analyze course-wise trends from 2012 to 2017.

Analyze course ratings over six years using pivot tables and pivot charts in Excel to identify the course with the maximum ratings, guiding data-driven storytelling.

Rearrange the pivot chart to show course-wise average ratings over the last six years by dragging the course name to the axis and selecting average in the values.

Format the pivot chart to a clustered bar chart to compare course ratings, add data labels outside end, remove vertical grid lines, and title it 'course wise students ratings'.

Sort the course names in a pivot chart by the average of ratings to display from bottom to top, with the lowest rated at bottom and the highest at top.

Learn how to use an Excel slicer to filter course ratings by year and compare average ratings across years to identify top courses.

Apply the data to insights framework to derive actionable observations from enrolments and ratings, using a rankings approach and color indicators to guide corrective actions.

Derive insights from the six-year y-o-y enrolment trend for all courses in the Excel data analysis tab, using slicers to show a steady increase and a very good overall performance.

Analyze year-over-year trends for course number 1, noting a strong rise from 2012 to 2016 and a muted 2017, with an orange circle highlighting the six-enrolment change.

Analyze year-on-year enrolment trends for course 2, noting a 2017 drop from 900 to 860 and prompting management to consider corrective actions.

Compare year-over-year enrolments to identify trends across courses 1–5. Flag course 2 for decline, mark courses 1, 3, and 4 as cautious, and celebrate course 5’s strong growth.

Analyze the second analysis in tab 2 of the excel spreadsheet to identify the best selling course over six years and highlight diversification across five courses with a green circle.

Identify the best and least selling of five courses over the last six years, and use color-coded red, orange, and green markers to reveal insights.

Assess YoY ratings from 2012 to 2017, noting a healthy trend around 4.4–4.5 with a 2016 dip, 2017 rebound, and a green circle indicator.

Analyze the trend of course 1 ratings over six years, noting a dip in 2015–2016 and a strong rebound by 2017, indicating very good year-over-year performance.

Examine course number 2's mixed trend in year-over-year ratings, from a decline to a rise and a slight 2017 dip, signaling cautious but solid overall performance with orange circle.

Analyze Y-o-Y rating trends for multiple courses, identify declines in courses 3 and 4, apply corrective actions, and highlight course 5's strong improvement as the key insight.

Derive six years of course ratings from the data analysis 4 tab, identifying top performers (courses 2, 4, 5) and average performers (1, 3) with green and orange indicators.

Assess performance across five courses using a four analysis ranking framework to extract course insights. Identify corrective actions to boost enrolment and stabilize ratings, highlighting course 5 as top performer.

Apply a four-step excel framework—from raw data to insights—using pivot tables, charts, and slicers to analyze enrolments and ratings, and rank course performance for actionable management steps.