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: 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.
- Instructions: How to Set up a Charity Database
- Project spreadsheet file: Charity_Database_Project.xlsm
- Finished version: Charity_Database_Final.xlsm
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.
- VLOOKUP Exercises
- How to Set up a Macro
- Invoice Project File (revised): Invoice Simple with Macros.xlsm
- Finished Invoice Template: Invoice Simple with Macros – Complete.xlsx
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 www.chandoo.org. 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.
- Loan Payment Calculator with Scrollbars: Loan Payment Calculator
- Explore the INDEX/MATCH combination: INDEX and MATCH
- Learn about Array Formulas: Array Formulas
- Interactive Dashboard with Timeline: Dashboard with Timeline