Microsoft Excel Evening Class: “The Best of Excel”
(10 classes x 2 hours)
This course is guaranteed to increase your proficiency in Excel.
It takes place in the Ballinasloe Enterprise Centre (Training Room) on Wednesday evenings (7 – 9 p.m.) on the following 10 dates:
21/02/18, 28/02/18, 07/03/18, 14/03/18, 21/03/18, 28/03/18, 04/04/18, 11/04/18, 18/04/18, 02/05/18
Cell referencing – relative, absolute & structured
In Excel you copy formulas a lot. It’s therefore crucial to know the 3 basic types of reference to a cell present in formulas. Ensure that you learn this basic Excel skill so that you know in advance what your copied formula will look like in a new location once pasted.
Formulas & Functions I – SUM, AVERAGE, COUNT, MAX, MIN & IF
These are the basic Excel functions used in formulas. In addition, we review the full list of functions to classify them into functions which you know already, functions which are not particularly relevant and then, most importantly, the functions which you will / would like to learn on the course.
Formulas & Functions II – VLOOKUP & SUMIFS
Two of the most popular functions in Excel. Use VLOOKUP to automatically bring in matching rows from columns which are part of other lists or even create your own classifications with this function in a brand new column. SUMIFS enables you to add up (or “filter in”) particular numbers into your total whilst intentionally excluding others, but in a dynamic way.
Charts I – embedded chart versus chart sheet, change chart type & data series
Learn the fundamentals of graph structure in Excel, especially the all-important concept of data series and how the numbers are read into your graph.
Charts II – chart elements & more chart types
Build on your first chart session by adding additional elements to your graph or chart such as Data Labels and familiarise yourself with the different ways in which Excel can visualise your numbers. On the last 10-week course, as homework, I asked the students to design a line graph similar to the one below. The message communicated is that there is, using a season’s results in the English Premier League as an example, almost always an advantage in playing your matches at home as against playing away – no matter what your final league position might be!
Pivot Tables I – purpose & set up area
The number 1 use of Excel in data analysis is Excel’s fully integrated summary tables tool called Pivot Tables. Every Excel user should learn how to create and modify them. No formulas or functions here but with just a few clicks you can get full summary insights from your table of data.
Pivot Tables II – slicers, drilldowns & Pivot Charts
Pivot Tables are multifaceted. Loads of different hidden features. Let’s discover them together to take full advantage of Pivot Tables.
Power BI Desktop – download this free Microsoft software – introducing business intelligence
Power BI is regarded as one of the best data visualisation tools on the market and it’s made by Microsoft too. It’s perfect for the Excel user as there are so many similarities and potential interactions. Bridge the skills gap by learning Excel tables as well.
Vba Macros – If, Loop & interaction
Within Excel there is another screen acting behind the scenes called the VBA Macro Editor. This screen allows you to automate Excel tasks by saving the corresponding Vba Macro code. Learn how to code for conditions, for looping many times through a repetitive procedure and for creating dynamic interactions between yourself and Excel.
Formulas & Functions III – INDEX MATCH MATCH, OFFSET COUNTA & SUMPRODUCT
The course finishes off with Excel’s 3 best function (sets): INDEX MATCH MATCH to return the contents of a dynamically chosen cell. OFFSET COUNTA to access changing cell ranges. The final Excel function you will learn on the course is SUMPRODUCT. Renowned Excel expert Leila Gharani calls this “a very elegant array function” which when used in the correct way “saves you from doing a lot of in-between calculations and unnecessary workarounds in your existing Excel files”. See Leila’s excellent SUMPRODUCT video on YouTube here: SUMPRODUCT
Note 1: Please bring your own laptop with Excel to follow along and also to do the exercises.
Note 2: One of the great things about the course is that you get to learn the very best keyboard shortcuts in Excel. Learning these enables you to continually save time as you interact with your spreadsheet.
Note 3: I love watching Excel videos on the web. The best available ones form part of the homework for this course. In this way you get to learn Excel both online + offline.
Martin Loughman is the owner of Spreadsheet Interaction Limited. He has >25 years of spreadsheet experience and personally delivers all the company’s Excel courses. In 2016 he attained Microsoft Office Specialist Expert (MOS) for Excel certification and also received a Merit for Level 6 Training Delivery & Evaluation (QQI).
Interested in learning some Microsoft Excel spreadsheet tips & tricks ? Let me show you how !