Advanced Excel Class

Our Advanced Excel class is taught by Rich Malloy at Norwalk Community College. The four-session course is generally taught in the spring, summer, and fall. For information on registering for the course, click:

Session 1: The Advanced Excel Budget

How to Set up and Manage a Budget

In this Advanced Budget Project for Excel 2016 running in Windows, we’ll set up and manage a budget using some the more powerful and advanced tools in Excel. The purpose of this project is two-fold: to teach people about some exciting tools in Excel, and to get people to start using budgets so that they can save money and get rich.

The project is broken into four parts: the Budget, the Ledger, the Pivot Table, and the Report. The same Excel file used for all four parts. If you have trouble with any part, the file includes completed worksheets to help you out.

Click here for the Advanced Budget Excel File: advbudget.xlsx

Part A: Setting up the Budget

In this part, we will format a budget so that it can be used later with a Ledger, a Pivot Table, and a Budget Report. Use the Advanced Budget File listed above.

Instructions for Part A: Advanced Budget Instructions – Part A.pdf

Part B: Setting up the Ledger

In this part, we will format a Ledger to keep track of expenses using Data Validation and Slicers. The Ledger table will later be used in a Pivot Table and a Budget Report. Be sure to use the Advanced Budget spreadsheet file listed above.

Instructions for Part B: Advanced Budget Part B – The Ledger.pdf

Part C: Summarize Expenditures with a PivotTable

Now that we have our expenditures detailed so nicely in our Ledger table, it would be nice to see a summary of all expenditures grouped by category and month. This can quickly be done by using the amazing PivotTable feature of Excel. We can also use two other features of Excel, Sparklines and PivotCharts to get a visual summary of expenditures. Be sure to use the Advanced Budget spreadsheet file listed above.

Instructions for Part C – The PivotTable: Advanced Budget Part C – The PivotTable.pdf

Part D: Create a Budget Report with the Amazing SUMIFS Function

So, are we on-budget, or over-budget? With the wrong answer, you could lose your job. To find out, you need a Budget Report. The Pivot Table we created in Part C was astonishingly easy and powerful, but not very flexible. A better way to summarize expenditures for a Budget Report is to use the amazingly powerful SUMIFS. It’s like the SUMIF function, only much, much more powerful. Again, be sure to use the Advanced Budget spreadsheet file listed above.

Instructions for Part D – The Budget Report: Advanced Budget Part D — Budget Report.pdf

The Complete Advanced Budget Project – Parts A-D

OK, OK. So you don’t want to do this project piecemeal. You would rather do the whole project at once. Well, here are the complete instructions. Again, be sure to use the Advanced Budget File listed above.

Complete Instructions for Parts A-D: How to Set up a Budget–Advanced.pdf

Session 2: Set up a Database for a Charity:

In this project, we’ll set up and manage a small database for a charity. We will keep track of donations and generate thank you letters easily. This database can be used for any typ of member organization. Two very powerful Excel tools are used in this project: VLOOKUP and Macros. I’ve included extra files to help you with these topics.


Session 3: Set up a Invoice with Macros

One of the most important forms a business has to deal with is an invoice. Let’s face it, without an invoice, it is a lot harder to get your customers to pay you. In this project, we’ll set up an Excel workbook that we can use to generate invoices. This workbook will also use macros to print invoices—and store invoice data. To begin we will review the powerful VLOOKUP function and explore the basics of creating macros.


Session 4: Set up a Dashboard

One of the most popular applications currently for Excel is a Dashboard, which allows business executives to monitor various aspects of their business from a single screen. In this session we’ll build a variation of a dashboard introduced by Purna “Chandoo” Duggirala at his amazing Excel website We enhance Chandoo’s dashboard by adding a Timeline. Also in this session, we will learn how to use the powerful INDEX/MATCH combo, the intriguing and mysterious Array formulas, and various controls such as scroll bars.