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.
- VLOOKUP Exercises
- Instructions: How to Use Get & Transform in Excel.pdf
- Data files: Get-and-Transform-Data-Files.zip
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.
- How to Set up a Macro
- Supercharge the Subtotals tool with Macros: Subtotals Exercise.xlsx
- Explore the INDEX/MATCH combination: INDEX and MATCH
- Mike Girvin’s YouTube Video on Introduction to PowerPivot: Click here
- Slide Show on Data Models: AE02-Parents and Children Slides
- Linking Tables in a Data Model: AE03-Data Model Exercise.xlsx
- 10,000 Row Excel File: AE03-Large 10K-Row Workbook
- Power Pivot Exercise.xlsx
- Data File for Power Pivot Exercise: AE03-Medium Data File
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.
- Loan Payment Calculator with Scrollbars: Loan Payment Calculator
- Learn about Array Formulas: Array Formulas
- Create a Maps Chart: Maps Chart Project
- Create a 3D Map Chart: AE04-3D Maps Project
- Finished 3D Map Chart: AE04-3D Maps Project Final
- Create an Interactive Dashboard with Power View: AE04-Power View Project.xlsx