Data analysis in Excel

Explore how data analytics draws insights from large data sets, enables objective, fact-based decision making, and presents metrics visually using Excel as an excellent data analytics tool.

Develop technical skills to analyze large data sets using Excel, SQL, and Hadoop through real-world practice, and cultivate logical synthesis plus effective data visualization for management-ready insights.

Compare the roles of data analyst and data scientist, focusing on differences in historical data patterns. Identify how analysts use Excel to explore historical data and how scientists forecast trends.

Explore Microsoft Excel as a comprehensive data analytics platform, covering data manipulation, visualization, and interactive modeling with features like pivot tables, slicers, and VBA.

Discover the Excel interface: workbooks with sheets, cells named by column and row (A1, I9), the name box, and navigation across sheets and large data capacity.

Learn basic Excel operations on a student marks dataset, including reading cells, summing across semesters, and copying formulas with Ctrl+C and Ctrl+V to replicate across rows and columns.

Format data in Excel with borders and grid lines, add thick borders to headers, apply color coding to student names and semester headers, and bold totals for emphasis.

Learn how to use the dollar sign to freeze references in Excel formulas, enabling reliable copying across rows and columns when calculating multiplication tables.

Recaps module 1 highlights: data analytics extracts insights from large data sets for bias-free decisions, outlines three key skills, and shows Excel as a data analytics tool.

Learn to clean and reformat data in Excel using text functions for analysis, addressing incorrect, incomplete, and duplicated data with LEN, FIND, LEFT, RIGHT, MID, TRIM, VALUE, PROPER, and SEARCH.

Download the Excel template provided with this module and open the file the instructor uses for illustrations. Practice hands-on by following along with the attached spreadsheet.

Learn to generate college-domain email addresses for hundreds of students by using Excel text functions and formulas to convert personal emails like student1@gmail.com to student1@collegedomain.edu.

Use LEN, FIND, LEFT, and CONCATENATE to transform a personal email into a college-domain email. Follow step-by-step instructions and apply a single formula to generate all students' college-domain addresses.

Clean and analyze data in Excel using trim to remove leading spaces and find to locate hyphens, then split records into date, product, price, and quantity.

Use left, mid, value, len, and find to extract date, product id, price, and quantity from hyphen-delimited text, with trim and formatting for clean data.

Explore illustration 3 in Excel showing how to use text functions to automate classifying qualitative feedback as positive or negative.

Learn how to use Excel's search function to detect positive and negative keywords in qualitative feedback, and how to lock references when copying formulas across cells.

Apply the iferror function in excel to replace error results with zero when using search to locate keywords, and copy the formula across cells.

Apply the Excel if function to classify qualitative feedback as positive or negative by summing positive keywords in E9 to L9 and returning 'positive' or 'negative' based on logical test.

Recap of module 2 highlights text functions such as len, find, left, concatenate, mid, trim, value, search, and iferror to clean data for analysis, with recommended practice exercises.

Learn essential Excel functions to extract and analyze large data sets, including sumif, vlookup, hlookup, index match, and countif, and master graphical charts and conditional formatting for insightful financial analysis.

Download the Excel template for this module and use the instructor's file to practice the module’s illustrations with hands-on data analysis in Excel.

Learn to use basic Excel functions to extract information from a GDP data template. Navigate the raw data tab to examine quarterly GDP by industry and totals in rupees billions.

Learn to use the sumif function in Excel to aggregate India's sector GDP by year from 2004-05 to 2013-14, applying range, criteria, and sum range.

Calculate the percentage growth rate of each sector’s GDP contribution from 2005-06 to 2014 in Excel, using base year 2004-05, the (C4 - C3) / C3 formula, and sumif to aggregate.

Master vlookup to extract country gdp figures by year from a dataset, using a lookup value, table array, and exact match; also retrieve gdp growth rates.

Explore how to use the HLOOKUP function in Excel to retrieve sector-specific GDP and year-over-year growth, highlighting its tie to VLOOKUP and horizontal data layouts.

Master index and match to dynamically retrieve GDP by year and sector, enabling data analysis in Excel while overcoming vlookup and hlookup limits.

Explore applying countifs with multiple criteria, alongside countif and sumif, to count years India's GDP growth and mining sector growth were positive (2005-14).

Visualize sector growth over years in Excel using the line chart, with data prep via transpose and VLOOKUP, and formatting via chart title, axis titles, legends, and data labels.

Learn to create a dynamic pie chart in Excel showing sectoral GDP contributions for a year. Use index and match to pull data and compute percentages, then format the legend.

Learn to create a column chart in Excel showing the absolute year-over-year GDP contribution of a sector in India and add a secondary-axis line for the growth rate.

Create a 100% stacked chart in Excel to show how sectors contribute to India's GDP over years, including absolute values and percentage shares.

Learn to build a heat map using conditional formatting to color code high, medium, and low growth sectors with green, orange, and red, based on 8%, 5–8%, and below 5%.

Review Excel functions—sumif, vlookup, hlookup, index-match, and countif—to analyze large data sets. Visualize results with line chart, pie chart, column chart, and stack chart plus conditional formatting, then complete practice.

Master advanced excel functions to analyze large data, including list, offset, sumproduct, goal seek, index and match, pivot tables, and indirect, with hands-on exercises using the provided template.

Download the Excel template provided with this module to practice along with the instructor. Use the template, the same file used for illustrations and hands-on activities.

Learn to use Excel's list function via data validation to create drop-down menus from four oil price scenarios, enabling dynamic, user-driven model choices.

Learn how the Excel offset function returns values from a reference cell by moving rows and columns to match a chosen scenario, enabling dynamic price models.

Explore the sumproduct function in Excel to multiply arrays and sum the products. Apply conditional sums with a double hyphen sign and analyze stock data like Accenture, Infosys, and Google.

Explore how Excel's goal seek uses a what-if analysis model to determine the required online advertisements to reach 100 million USD revenue, based on visitors, conversions, and course price.

Explore how index and match extract data from 3-dimensional Excel arrays. Select year, state, and month to retrieve rainfall increases from 2012–2014 data.

Explore how pivot tables in Excel summarize enrollment data across regions, courses, and years, turning large datasets into clear totals and averages for meaningful insights.

Learn to use the indirect function in Excel to pull margins from multiple sheets into one summary sheet, enabling cross-company, year-by-year comparison of EBITDA, EBIT, and WACC.

Review advanced Excel functions for analyzing large data and building flexible models, including List, Offset, Sumproduct, Goal Seek, Index-Match, Pivot Tables, and Indirect; practice is recommended.

Explore advanced visualization in Excel by mastering nine charts: combination, waterfall, dynamic line, area, index, radar, scatter, bubble, and Gantt, to present data to executives.

Download the excel template provided with this module and use it to follow along with the instructor's demonstrations for hands-on practice.

Create a combination chart in Excel that graphs absolute NPLs (USD billion) as columns and NPL ratio (percent) as a line on a secondary axis, with clear titles and labels.

Learn to build an Excel waterfall chart from scratch, using base, increase, and decrease columns to visualize product revenue and sales tax, with formatting and axis labels.

Create a dynamic line chart in Excel using check boxes to select sectors such as agriculture, manufacturing, financial services, and total GDP, based on a 2010–2014 data table.

Create and interpret area charts in Excel to visualize how four marketing campaigns contribute to revenue over time, using 2016 data and USD labeling for clear, actionable insights.

Learn to build an index chart in Excel by rebasing Google, Apple, and Microsoft prices to 100, then compare their performance over 2012–2017 using a line chart.

Create and interpret an Excel radar chart to compare actual versus planned completion across five workstreams, using a spider web visualization with 0% to 100% axes.

Learn to create a scatter chart in Excel to compare revenue growth and EBIT margins, add and link data labels to company names, and format axis titles and grid lines.

Learn to create a bubble chart in Excel, extending a scatter chart with a third size dimension by using revenue values to size bubbles, and label points with company names.

Learn to build a Gantt chart in Excel by extracting start and end dates, then apply conditional formatting to shade each activity's timeline across the project.

Review module five lessons on advanced visualization techniques in Excel, and apply combination, waterfall, dynamic line, and Gantt charts to present data.

Share your valuable feedback to help thousands of students learn data analysis in excel and to improve this course and Skillfin Learning.

Review five Excel-based data analysis modules, from text and basic functions to dynamic models and advanced visualizations, using real-life datasets and practice assignments.

Share your valuable feedback on this course to help students globally benefit from data analysis in Excel. Your insights will guide improvements and enhance the learning experience.