Data Science 101: Python Plus Excel

Explore the structure of Excel sheets, including the ribbon and its tabs, the work area with sheets and cells, and the name box and formula bar for editing.

Explore the Excel ribbon’s seven main tabs—home, insert, page layout, formulas, data, review, and view—and use the Tell Me search to quickly access pivot tables, sparklines, and charts.

Select a cell, edit in the formula bar or the cell, enter days and numbers, and note that text aligns left while numbers align right.

Learn to create Excel formulas for arithmetic operations, using the equals sign or plus, with numbers or cell references, and master plus, minus, star, forward slash, and parentheses.

Learn how Excel formats cells with general, number, currency, and percentage styles, including red negatives, using the home tab or Ctrl+1. Ensure data types match formulas.

Format a professional Excel worksheet from the start with a white background, Arial font size 9, and adjusted column width and row height; title in dark blue bold 12.

Learn fast navigation and range selection in Excel using Ctrl and arrow keys to move to sheet ends, and Ctrl+Shift+Arrow to select blocks, plus Ctrl+A to select all.

Learn how to fix cell references in Excel using dollar signs to lock specific cells when copying formulas, ensuring consistent total cost calculations like volume times unit cost.

Apply custom formatting in Excel to make numbers stored as text behave as numbers. Use format cells to create a custom format like 0.0 X so values stay numeric.

Learn how to record and apply macros in Excel to automate repetitive tasks. Enable the developer tab, record actions, and reuse macros across sheets to save time.

Learn to use freeze panes in Excel to keep the table header visible while scrolling, by selecting the rows below the title and applying freeze panes.

Learn how to use count, countif, and countifs in Excel to count numbers and text, apply single or multiple criteria, and handle ranges with correct syntax.

Explore essential text functions in Excel to edit and manipulate strings. Learn left, right, mid extraction, case conversion with upper, lower, proper, and joining text with concatenate or ampersand.

Learn how to use max and min functions in spreadsheets to find the highest and lowest values. Build skills with range notation and examples like Bayern's 90 and hamburger's 27.

Explore the round function in Excel to round a number to a specific number of digits, with zero or one decimal place, for modeling.

Explore how to use the vlookup function to transfer data between tables in Excel, including lookup value, table array, leftmost column, column index, and exact versus closest matches.

Learn to insert and customize charts in Excel to visualize data, using the insert tab, selecting data, and the recommended charts feature to try line, bar, or pie charts.

Format the chart by setting the year to Arial, size eight, fill the selected bar series, bold axis labels, and 75% transparent grid lines for a professional legend-ready look.

Learn to create bridge charts, also called waterfall charts, by selecting data and inserting a waterfall chart in Excel 2016, then format the title for clear corporate visuals.

Identify data sources through a case study, examine a 2016–18 Excel sheet with account names, partner companies, totals, and P&L references, and ensure data are homogeneous across years.

Apply Excel's sumif to calculate yearly revenues in the database by selecting year ranges and codes, then adjust negative revenue to positive by negating the sum for FY 2016–2018.

Format the PNL statements in Excel by styling headers, applying thick borders, and showing euro amounts in millions, then sum totals and prepare for database population.

Apply sum formulas across years 16–18, paste formatting and borders to gross margin and total, then populate data with sumif from the database to calculate net income.

Explore how to use the vlookup function in excel and implement a similar lookup with Python to populate mean income by matching postal codes across order and zip code sheets.

Learn to build pivot tables in python using pandas by loading excel data, selecting region, segment, category, subcategory, and profit, then grouping and unstacking before exporting to excel or html.

Compute profit after tax in Excel using a nested if statement that applies 20% tax to furniture, 30% to office supplies, and 40% to technology, with corresponding remaining percentages.

Master Excel text manipulation with practical techniques using mid, left, right, trim, upper, lower, proper, and concatenate, and learn error resilient extraction with find and iferror.

Learn text manipulation in Python using pandas to load Excel sales data, slice order IDs, split and strip strings, concatenate, and apply upper, lower, and find with negative indexing.

learn to visualize regional sales and profit using pandas, group by region, aggregate sums, and create bar plots, stacked plots, and subplots with layout options.

Explore Matplotlib basics for visualization, including creating a blank figure canvas, adding axes with rect coordinates, and plotting data using NumPy to generate x values.

Learn to enhance matplotlib visuals with themes and ggplot styling, add axis labels and legends, and choose between subplots and subplot for multi-plot layouts.

Combine pandas and matplotlib to read Excel sales data, group by city, and plot the top 15 cities with a green horizontal bar chart integrated into a matplotlib axis.