Advanced Excel Class

Shortcut: bit.ly/advan-excel

This Advanced Microsoft Excel 2016 course is comprised of six sessions.

  • Tips, Tricks, and Shortcuts
  • Pivot Tables & VLOOKUP
  • Advanced Formulas
  • Tables and Power Query
  • Power View, Dashboards, and Macros
  • Power Pivot

If you have any additional topics that you would like to cover, please let me know.

— Rich Malloy

Session 1: Tips, Tricks, and Shortcuts

This session covers over 50 tips in categories such as: Navigating a Workbook, Selecting Cells, Using Paste Options, Handling Absolute References, Entering Data, Displaying Data, Printing, and so forth.

Excel Shortcut Keys: Excel Keyboard Shortcuts

Workbook File: Workbook Session 1.xlsx

Slide Show (PDF): Slideshow–Tips & Tricks

Session 2: Pivot Tables & VLOOKUP

In this session we will review Pivot Tables and the VLOOKUP function and then explore some powerful advanced skills in both these topics.

Workbook File: Workbook Session 2.xlsx

Session 3: Advanced Formulas

Here we will examine some of the powerful advanced functions in Excel, such as: SUMIFS and AVERAGEIFS, INDEX and MATCH, and Array formulas.

Workbook File: Workbook-Session.xlsx

Session 4: Tables & Power Query

In this session we will explore the advanced features of Excel Tables and Slicers. We will also use the Get & Transform (“Power Query”) tools to bring data into a spreadsheet automatically.

Workbook File: Workbook Session 4.xlsx

Get & Transform – Directions: How to Use Get & Transform in Excel

Get & Transform – Files: Get and Transform Files (zip)

Individual Get & Transform files:

Giorgi’s Spreadsheet: DATA-NCC-RM2

Session 5: Power View, Dashboards, & Macros

The latest versions of Excel has some new powerful tools for displaying data. In this session, we will explore how these tools can be used in business applications. We will also look at how Macros can enable us to be more effective Excel users.

 

 

Session 6: Power Pivot

The Power Pivot feature of Excel can handle immense industrial-size databases. In this session we will review the powerful INDEX and MATCH function combination, examine Relational Databases, and explore some of the unique capabilities of Power Pivot.

Session 7: PowerPoint & Excel

The charts we create in Excel often end up either in Word documents or PowerPoint presentations. In this extra session we will look closely at PowerPoint and go over various tips and tricks.

Animated GIF background:

  • PP01A-Animated-Background-Water

Additional Files:

The Advanced Excel Budget

Slide Show: Adv Excel Intro–2018-07

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: AE01 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 — 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 — Pivot Table.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 (Complete)

Getting 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.

Analyzing 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 review the all-important VLOOKUP function and then see how you can replace the VLOOKUP function with the even more amazing INDEX/MATCH combination. Then we’ll have a change of pace and look at some workbook management and design skills. And finally, as if VLOOKUP doesn’t have enough problems, we will see how we can replace it with the amazing Power Pivot.

Power View: Setting 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.

Other Advanced Excel Topics

Advanced Excel: 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.

Miscellaneous Advanced Excel Files: