Excel & VBA Skills for Freelancing

Learn basic Excel VBA concepts, including recording macros, viewing code, and using the object browser, with emphasis on variables, data types, and end user input.

Record and review macros in Excel using the developer tab to automate tasks, explore the Visual Basic editor, and understand formula and formatting actions with r1c1 references.

Limit end-user errors with drop-down menus via data validation and a submit button that runs VBA input validation using if statements to check numeric values and non-empty names.

Learn to use Excel VBA loops to capture end-user form data into a separate sheet with the cells property, and prevent duplicates with do while and for loops.

Explore Excel VBA forms, import/export code, and cleanup while building a survey input form with labels, text boxes, a combo box, and radio buttons.

Explore enforcing ACID transactions—atomicity, consistency, isolation, durability—in Excel with Power Query and VBA, enabling multiple users to record data and consolidate into a central database.

Explore atomicity in the central Excel database by enforcing one-row transactions with validated inputs and a unique business key using username and timestamp via VBA.

Consolidate data into a central Excel database by appending three workbooks with Power Query and refreshing updates via an audit log.

Conclude section 3 by showing how to maintain a central, de-duplicated Excel database from multiple users with ACID principles, Power Query, and an audit trail.

Explore formulas and tables in Excel, including the valuable If formula, table functionality, and formatting techniques for freelance projects, using the net promoter score as a practical example.

Learn to calculate net promoter score in Excel using nested if formulas, classify responses as detractors, passives, or promoters, and implement this logic with VBA macros.

Create and format tables in Excel to enable automatic filtering, resizing, and formula propagation, then apply VLOOKUP with absolute references and conditional formatting to highlight high likelihood rows.

Rebuild the database with Power Query to prepare for formulas and future charts and pivot tables. Create unique data, rename connection, and include NPS groups, high, medium, and low groups.

Learn how to use count, counta, countif, sumif, and average in Excel to calculate the NPS score, determine promoter and detractor counts, compute percentages, and average ratings.

Master Excel basics by validating averages with the sum formula on the ratings column. Use and, or, and if to model promoter and high likelihood scenarios for repeat purchases.

Master formulas and table formatting, including adding formulas through VBA, to improve the appearance and utility of your work. Prepare for pivot tables and charts in the next section.

Explore pivot tables and charts, learn VBA commands to enhance analysis, and build a simple Excel dashboard for easy, reusable calculations.

Create and analyze pivot tables in Excel by selecting data ranges, adding to the data model, and choosing averages, distinct count, or totals. Explore filters and design options for insights.

Learn to automate data refreshes in Excel with a simple VBA button that refreshes all connections, including Power Query, and customize pivot table filters with macros.

Document where your logic resides across buttons, macros, formulas, and VBA implementations to ensure maintainability, clear handoffs, and understanding of data validation, table formatting, and conditional formatting.

The lecture demonstrates building an Excel dashboard around an NPS KPI, using a bar chart for promoters, detractors, and passives, and a pie chart to show percentages.

Create a line chart of daily submissions linked to a pivot table with a day-level distinct count, and build a scatterplot of purchase likelihood by rating with a trend line.

Create interactive Excel visualizations with pivot charts and pivot tables, using the data model to count distinctly, analyze NPS Group axis, and apply filters.

Master pivot tables, charts, and interactive pivot charts to summarize and visualize data in excel, extending your capabilities for future freelance projects.

Summarizes the foundation for freelance Excel and VBA projects, outlining three methods to extend your capabilities and solve issues, delivering value to learners.