Microsoft Excel Programming

Paths

Microsoft Excel Programming

Authors: Pratheerth Padman, Craig Golightly, Amruta Mahajan, Mike McQuillan

Microsoft Excel offers several powerful programming features you can use to automate mundane tasks and enrich your spreadsheets with custom logic and behavior. This skill will... Read more

What you will learn

  • Visual Basic for Applications
  • Excel Macros
  • Built-in Excel Functions
  • Troubleshooting Formulas

Pre-requisites

Basic Excel use

Beginner

Learn to record, playback, and edit your Excel activities using macros.

Recording and Managing Excel Macros

by Pratheerth Padman

Sep 23, 2019 / 31m

31m

Start Course
Description

Unbeknownst to most people, a whole lot of our daily tasks performed in Excel can be automated. In this course, Recording and Managing Excel Macros, you will dip into the world of Excel Macros and VBA, which will help you do exactly that.

First, we'll see what macros are in Excel and how to go about creating and editing them. Next, you'll learn how to manage these newly created macros and their configurations, which include enabling and disabling macros, copying macros between workbooks, and renaming and deleting macros.

When you're finished with this course, you'll have fundamental knowledge about macros in Excel and the ability to create simple macros.

Table of contents
  1. Course Overview
  2. Creating Simple Macros in Excel
  3. Managing Macros and Configurations in Excel

Intermediate

Learn to apply logical, statistical, data, and lookup operations inside of Excel functions.

Excel Logic Function Playbook

by Craig Golightly

Sep 12, 2019 / 36m

36m

Start Course
Description

Excel is often the hammer that is applied to many jobs. Knowing the built-in functions can save you time and frustration. In this course, Excel Logic Function Playbook, you will get an in-depth look at the logic functions in Excel. First, you will learn the AND, OR, and NOT functions. Next, you will discover the IF, IFS, and IFERROR functions. Finally, you will explore how to combine them all so you can write and debug these logic functions in Excel. When you’re finished with this course, you will know how to leverage logic functions to save more time. Software required: Excel.

Table of contents
  1. Course Overview
  2. Checking Conditions with Boolean Logic
  3. Making Decisions with IF, IFERROR

Excel Statistics and Finance Function Playbook

by Craig Golightly

Oct 10, 2019 / 1h 26m

1h 26m

Start Course
Description

Excel is used for many jobs, and while you can invent all of your own functions, knowing how to use the built-in functions can save you time and frustration. In this course, Excel Statistics and Finance Function Playbook, you will get an in-depth look at common statistics and finance functions in Excel. First, you will learn the SUMIFS, AVERAGEIFS, and COUNTIFS functions. Next, you will discover the NPV, FV, and PV functions. Finally, you will explore how to apply these functions in various real-life scenarios. When you’re finished with this course, you will have the skills and knowledge of excel statistics and finance functions needed to create accurate reports and compare financial scenarios. Software required: Excel

Table of contents
  1. Course Overview
  2. Using the SUMIFS Function
  3. Using the AVERAGEIFS Function
  4. Using the COUNTIFS Function
  5. Using the NPV Function
  6. Using the FV Function
  7. Using the PV Function

Excel Data Lookup Function Playbook

by Pratheerth Padman

Oct 21, 2019 / 33m

33m

Start Course
Description

Imagine that you’ve been given a Microsoft Excel worksheet with tons of data - hundreds or even thousands of rows, and thousands of columns. The task you’ve been given is to extract maybe 10 or 20 values from 2 or 3 columns and use that to create a much smaller worksheet to fulfill some need. If you’ve been doing that manually so far, this course is exactly what you need. In this course, Excel Data Lookup Function Playbook, you'll explore 5 different functions that’ll help you to look up data quickly and efficiently. In the first module, you'll go through 3 different functions: LOOKUP, VLOOKUP, and HLOOKUP. Next, you'll examine the behavior of the INDEX and MATCH functions, both individually and when combined. When you're finished with this course, you'll know how to quickly and efficiently look up data in Microsoft Excel.

Table of contents
  1. Course Overview
  2. Looking up Data with the LOOKUP, HLOOKUP, and VLOOKUP Functions
  3. Looking up Data with the MATCH and INDEX Functions

Excel Time and Date Function Playbook

by Pratheerth Padman

Oct 9, 2019 / 53m

53m

Start Course
Description

Microsoft Excel workbooks are seldom found without several cells filled with relevant dates or timestamps. As such, it is important to know how to deal with them, and in this course, Excel Time and Date Function Playbook, that's exactly what you'll be doing. To effectively compartmentalize all these functions, the course is divided into 5 modules. In the first module, you’ll learn how to reference time and date in excel using the NOW and TODAY functions. Then, you’ll look at comparing date and time in a cell with the current date and time using the NOW and TODAY functions. Next, you'll learn to serialize dates and times before moving on to use several functions such as TIMEVALUE, DATEDIF, and DAYS360, to find the difference between dates and times. Finally, you'll explore how to parse dates and times in Excel with further useful functions.

Table of contents
  1. Course Overview
  2. Referencing Current Date and Time
  3. Serializing Dates and Times in Excel
  4. Calculating Differences Between Dates and Times
  5. Finding the Day, Month, Weekday, or Year of a Date
  6. Finding the Hour, Minute, or Second of a Time

Advanced

Troubleshoot your formulas, and build rich applications around your data using Excel's Visual Basic for Applications.

Troubleshooting Excel Formulas

by Amruta Mahajan

Dec 4, 2019 / 1h 25m

1h 25m

Start Course
Description

Sometimes your Excel spreadsheet doesn't work as expected and it has so much data that you don't even know where to begin. In this course, Troubleshooting Excel Formulas, you will learn to tackle the most complicated of spreadsheets. First, you will learn the different error types in Excel, what each of them mean, and how to correct them. Next, you will discover some monitoring and debugging tools provided by Excel to help isolate issues in large formulas, and understand the relationships between various cells. Finally, you will explore how to use best practices to avoid errors and develop your own optimized troubleshooting workflow. When you are finished with this course, you will have the skills and knowledge of common issues needed to fix any Excel spreadsheet.

Table of contents
  1. Course Overview
  2. Understanding and Correcting Errors When Entering Formulas
  3. Understanding and Correcting Problems with Existing Formulas
  4. Tools to Audit Formulas and Cell Relationships
  5. Tools to Monitor and Debug Formula
  6. Best Practices to Speed up Your Troubleshooting Workflow

Building Your First VBA Module

by Mike McQuillan

Nov 6, 2019 / 1h 57m

1h 57m

Start Course
Description

There are lots of Excel VBA developers out there, and they’re all creating great Excel applications, but are they all structuring their code correctly? If you want to know about good code structure and how to make your code more reusable, this is the course to watch! In this course, Building Your First VBA Module, you'll use a pre-built application to learn about the three types of module VBA supports – Standard, Object, and Class. First, you’ll learn about the differences between them and when you might use each type of module. Next, the course takes a deep dive into class modules, outlining how to design and build a class module, how to use module events, and how to expose properties with getters, setters, and letters. You’ll also see how to test your code and how to extend your class module’s capabilities with external references. Finally, once you know how to build class modules, the course shows you how to use them, integrating the class module with a user form to build a completely custom user interface. You’ll find out how to add controls to user forms, as well as how to manipulate them via VBA. When you're finished with this course, you'll be able to take your VBA skills to the next level! Software required: Microsoft Excel, SQL Server Express, SQL Server Management Studio.

Table of contents
  1. Course Overview
  2. Introducing Modules and Classes
  3. Building the Code and Adding References
  4. Creating User Forms

Developing for Excel with VBA

by Mike McQuillan

Oct 29, 2019 / 2h 38m

2h 38m

Start Course
Description

Microsoft Excel. It’s one of the most popular and used applications in the world, especially in business. Most people can use it to manage data and run simple calculations. But did you know Excel has a programming language built into it, and it can do so much more? No? Then this is the course for you! You will learn all about Visual Basic for Applications (VBA), the programming language built directly into Excel. You’ll find out what VBA is and how it became part of the Microsoft Office suite. More importantly, you’ll discover how VBA can be used to create user applications, querying a database to perform searches. You’ll also see how user interfaces can be built using Excel worksheets, and how buttons can be used to call your code. Whilst all this is going on, you’ll learn how to structure and debug your code. Once you’ve built your first application, you’ll discover how to manipulate other Excel workbooks, inserting values and saving the file as a PDF! By the time this course is through, you’ll have an excellent knowledge of Excel VBA programming, and will have all the skills you need to start creating your own custom spreadsheet applications.

Table of contents
  1. Course Overview
  2. Introducing VBA
  3. Connecting Excel to a Database
  4. Building a VBA Application in Excel
  5. Enhancing and Debugging a VBA Application
  6. Sending Data to Different Workbooks with VBA
Offer Code *
Email * First name * Last name *
Company
Title
Phone
Country *

* Required field

Opt in for the latest promotions and events. You may unsubscribe at any time. Privacy Policy

By providing my phone number to Pluralsight and toggling this feature on, I agree and acknowledge that Pluralsight may use that number to contact me for marketing purposes, including using autodialed or pre-recorded calls and text messages. I understand that consent is not required as a condition of purchase from Pluralsight.

By activating this benefit, you agree to abide by Pluralsight's terms of use and privacy policy.

I agree, activate benefit