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.

Master selecting, inserting, deleting, and resizing rows and columns in Excel, using ctrl+space for column and shift+space for row, and adjusting column width and row height.

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.

Explore how to format Excel cells using the home tab and format cells menu, applying font styles, sizes, colors, bold, italic, underline, borders, and fill colors.

Learn how to align text and numbers in Excel by applying left, center, and right alignment to headers, text, and numeric cells.

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.

Master Excel formulas by inserting functions from the formula bar, exploring basic to advanced options, and testing a three-argument 'if' function with a logical test, true, and false outcomes.

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.

Use Alt+Enter to insert line breaks within a single Excel cell, improving readability and organizing long content without adding extra cells.

Explore Excel's text to column tool to split data in a single cell into separate columns using delimited or fixed width options, preparing data for a machine learning algorithm.

Learn how to use Excel's wrap text feature from the home tab to keep text inside a cell by automatically wrapping and adjusting the row height to fit content.

Master excel's select special to identify blanks with go to special, then perform imputation by filling empty cells with a chosen value for data science workflows.

Learn to create dynamic names for cells by concatenating fixed text with a changing cell value, using an equals sign and the ampersand, so names update as company names change.

Discover how to apply Excel custom formatting to display numbers legibly: control positives, negatives (bracketed and red), zeros as dashes, and use thousand separators and decimals.

Learn to create and apply custom formatting in Excel, using semicolon-based rules for positive, negative, and zero values with thousand separators and color cues.

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 sort an entire table by a chosen criterion using custom sort, from largest to smallest by volume, so data stay aligned and top products stand out.

Learn to add hyperlinks in Excel to navigate from one sheet to another, using the hyperlink feature and formatting the link text.

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 to locate and use the freeze panes feature in Excel using the tell me what you want to do search, even after months away from the app.

Master essential Excel keyboard shortcuts, from Ctrl+C and Ctrl+V to Alt shortcuts for formulas and filters, and customize the quick access toolbar to speed up data tasks.

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 sum, sum if, and sumifs through hands-on examples that total points and sum by country, including Germany and England, with champion league criteria.

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 how hlookup mirrors lookup but searches by rows, using a table array and a row index number with an exact match (false), and apply dollar signs to fix references.

Explore using index and match together in Excel to look up values, offering a powerful alternative to vlookup, with exact-match lookups and nested indexing.

Learn how to use the iferror function in Excel to calculate percentage shares, handle missing values, and display a custom message when data is unavailable.

Discover how pivot tables in Excel turn large data into dynamic, interactive summaries by summing volumes, counting entries, and analyzing volumes by year and product group.

Explore how data tables in Excel perform a sensitivity analysis on loan repayment, adjusting interest rate and term to reveal the total amount due.

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.

Learn to customize excel charts using the design tab, add axis titles, data labels, and legends, switch row/column, and adjust data series for a polished cluster bar chart.

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.

Create a treemap chart in Excel 2016 from two categories, country and city, and set a chart title to show which destinations attract the most tourists.

Learn how spark lines create tiny in-cell charts to visualize trends within a single cell, using line or column spark lines to reveal patterns in data across rows and columns.

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.

Left align the data and apply a filter in Excel to organize the dataset. Remove the total from rows using shortcuts to produce clean data.

Copy unique codes into a single database, convert formulas to values, remove duplicates, and use vlookup to build a searchable dataset for profit and loss visualization.

Populate a master table by using vlookup to pull account, partner company, and external names from source tables for 2016–2018, after repositioning codes to the left and applying exact-match formulas.

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.

Map each account row to predefined or custom categories to build profit and loss statements, aligning net sales, direct cost, personnel cost, and other operating expenses using Excel mapping.

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.

Populate the income statement using sumif with fixed mapping, pull net sales for FY16, convert to millions, and round; copy formulas across periods and create charts.

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 replicate Excel's vlookup in Python using pandas merge to join sales data with zip code income by postal code, extract mean income, handle duplicates, and save the results.

Learn how to create pivot tables in Excel to analyze sales data by product category and subcategory, view profits by region with filters, and use a classic layout.

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.

Create pivot tables with pandas by passing data, index, and columns, using np.sum to sum profit by region across segment and subcategory, as an alternative to group by.

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.

Use numpy.where in python to apply 20%, 30%, and 40% taxes by category (furniture, office supplies, technology), creating a profit net tax column with pandas.

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 how to use count, countif, countifs, sumif, and sumifs to count and sum with conditions, using category and quantity examples, and preview implementing the same in Python.

Learn to implement count, countif, countifs, sum, sumif, sumifs in Python with pandas, using boolean indexing to count rows and sum profits by category and conditions.

Learn how to create pivot charts in Excel and compare Excel's limitations with huge data to Python visualization, using matplotlib, seaborn, and Plotly for advanced visuals.

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 format charts in matplotlib by adding major and axis titles, labels, and customizable ticks. apply set_title, set_x_label, set_y_label, and tick ranges to refine the figure.

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.