Data Analyst's Toolbox: Excel, SQL, Power BI

Explore the data analyst role by analyzing four years of delivery orders in Excel, preparing reports, and offering actionable recommendations to boost profit.

Learn to use Excel's sort and filter tools to organize data, sort by values or color, and apply filters with many conditions for dates, client IDs, and costs.

Use the data validation tool in the data ribbon to restrict cell data, build a predefined answer list, and show input hints and error alerts for consistent survey data.

Utilize Excel's find, replace, go to, and go to special features to clean and prepare data, locate specific values, replace text like TV with television, and use formulas and filters.

Explore advanced Excel functions for calculating averages, using average, averageif, and averageifs with multiple conditions, such as age by gender and location, plus related tools like subtotal and filter.

Learn to create pivot tables in Excel to summarize data by year, revenue, and operational expenses. Use filters like gender and product category to reveal targeted insights.

Learn to build pivot table reports and create calculated fields to compute net profit as revenue minus OpEx, derive metrics like average net fee, and percent shares by year.

Group data via a pivot table to create frequency distributions of orders by product cost, then group costs into ranges and count orders.

Learn how to merge datasets in Excel using VLOOKUP with exact match, create columns for education and marital status, and analyze orders and net profit with pivot tables.

Learn to pair index and match in Excel to replace lookups, enabling left or right lookups and multi-criteria matching, with faster handling of large data sets.

Learn how to speed up Excel lookups by using vlookup with approximate match to merge large datasets quickly, comparing exact versus approximate match and the impact of sorting data.

Learn to build pivot tables with the data model to analyze data across multiple formatted tables from different worksheets, linking orders and customers to report orders and net profit.

Learn to use Excel conditional formatting to visually emphasize key results in pivot-table analyses, using rules, color scales, bars, and icon sets to highlight shares of profit and distinguish trends.

Explore essential Excel statistical functions—max, min, average, median, mode, variance, standard deviation, and correlation—to describe data and reveal relationships in cost and revenue.

Create a scatter plot in Excel to explore the relationship between property area and price, select data, insert scatter chart, and add a trend line to show a positive correlation.

Execute descriptive statistics in Excel to analyze property prices, obtaining mean, median, mode, standard deviation, standard error, and range to understand data at a glance.

Learn to build an Excel histogram with the Analysis ToolPak, creating bins, generating a price distribution chart, and using descriptive statistics to analyze property prices.

Learn how correlation measures how two variables fluctuate together, with examples of property area and price. Use Excel tools like the correlation matrix to interpret strong, zero, and negative relations.

Explore how linear regression models the relationship between property price and area, using correlation, the trend line, and the least-squares regression line to predict price.

Analyze regression outputs, interpret R, R-squared and adjusted R-squared, and assess F and P values for significance; use coefficients to predict property price from area and review residuals.

Build a multiple linear regression model to predict property price using area and bedrooms, assess coefficients, p-values, and R-squared, and compare models.

Explore the fundamentals of A/B testing as a randomized experiment that compares two web page versions to measure performance, conversion rates, and what design elements boost engagement.

Visualize an A/B test by comparing two page versions with a pivot table and a 100% stacked bar chart to assess conversion rates and orders, highlighting a 35 percent increase.

Explore statistical significance in A/B testing by interpreting p-values to decide whether version B outperforms A, using the G-squared (chi-square) test in Excel and a 95% confidence level.