COURSES
Formulas
Dynamic formulas improve quality and development
Throughout the first of the Nodewise Excel courses, the Excel core module, we have focused on Excel built-in functionality found in the menus. We have explored ways of working dynamically with those functions together with Excel Tables. Tables has also given us the prerequisite to work with the next step in our journey, to create dynamic formulas in Excel, which is the foundation in the second Nodewise Excel course: the Formulas module.
Structured References make dynamic formulas possible
Most people who use Excel in their daily work knows how to create an Excel formula. It starts with the equal sign followed by a mathematical formula. To add complexity, Excel functions are used (e.g. SUM or VLOOKUP). It is also most common that cell references (e.g. A1) or range references (e.g. A:E) are added to the formula, so called A1 References. But not only are A1 References hard to read and understand in long complex formulas, they are also very static which is a problem when the workbook changes in form of additional data or design changes.
Instead of using A1 References we will in this course use Structured References, one of the many benefits of using Excel Tables. Structured References refers dynamically to a defined Table range rather than a static cell or range. When the Table is populated with additional data, all the Structured Reference formulas in a workbook will take this data into account.
Tables are the foundation for all dynamic content in Excel and Structured References are the next level of functionality that makes formulas dynamic!
How to study the course
Start by reading the sections (see content below) to get a good understanding of the functionality, why it is important and how it can be applied. The sections are presented in a certain order so that you gradually get the ability to combine them.
The next step is to hands-on try out your new Excel skills. Nodewise course modules recommend two learning techniques that you can also mix:
- Learning by doing: Take a look at the Quick guides that contain step-by-step instructions on how to use a certain functionality. Try out the functionality in a simple spreadsheet just to get a basic understanding. Once you have completed all the sections you can move on to the case studies. Here you can download the data file and start adding functionality. If you don’t know what functionality to use you get hints in the form of links to the sections. Afterwards you can evaluate your solution against our solution by downloading the solutions file.
- Learn by examples: Another approach is to download the solutions file of the case and check how we have solved it, to understand how the functionality works and how you can work using best practice. Then you can download the data file and start trying out the functionality yourself. Afterwards you compare your solutions against the solutions file.
TIP: PRACTICE EVERY DAY IN YOUR DAILY WORK
Once you have completed the course, practice the techniques on real data in your daily work. Force yourself to use the techniques even though it can sometimes be tempting to fall back to old habits!
As you practice over and over again the new way of using Excel will feel as natural as walking!
The best of luck!
