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: norwalk.edu/extended-studies/.

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: Get Data with a Power Tool:

In this project, we’ll use various techniques for getting data into an Excel 2016 spreadsheet. First, we’ll do a review of the VLOOKUP function, which is a very popular way of importing data from a Lookup table. Then we’ll look at the amazing Get & Transform tool (formerly called Power Query). With this tool we will import data from a website and from tables with non-standard formats.  Next we’ll import data from several files at once, just by referring the to the folder that contains them. And finally we will merge data in a way very similar to the VLOOKUP function.


Session 3: Analyze Data with a Power Tool

In this session, we’ll take a look at the amazing Power Pivot feature in the most recent versions of Excel. This industrial-strength tool will enable us to analyze mountains of data. But first, we’ll look at how Macros can supercharge the Subtotals tool, and how you can replace the VLOOKUP function with the INDEX/MATCH combination.

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 an amazing dashboard based on the new Power View feature of Excel 2016. Also in this session, we will learn how to use the intriguing and mysterious Array formulas, and various controls such as scroll bars.