Excel Formulas, Functions, VBA & Automation
Microsoft Excel is a spreadsheet developed by Microsoft for Windows, macOS, Android and iOS. It features calculation, graphing tools, pivot tables, and a macro programming language called Visual Basic for Applications.
Excel, is the world’s most widely used spreadsheet program, and is part of the Microsoft Office suite. Other spreadsheet programs are available, but Excel is by far the most popular and has become the world standard.
Much of the appeal of Excel is due to the fact that it is so versatile. Excel is excellent for performing numerical calculations, but it is also very useful for non-numerical applications like:
- Number crunching
- Creating charts
- Organizing lists
- Accessing other data
- Creating graphical dashboards
- Creating graphics and diagrams
- Automating complex tasks
- Pivot Tables
Many Excel experts believe that pivot tables are the single most powerful tool in Excel. According to Bill Jelen (a.k.a. Mr. Excel) "No other tool in Excel gives you the flexibility and analytical power of a pivot table".
Excel formulas are useful in all types of spreadsheet applications. They may seem complicated, but they don't have to be. Formulas and functions are the building blocks of working with numeric data in Excel.
What is a formula?
It is the simplest form, a formula is an expression made up of cell addresses and arithmetic operators. Formulas can also be made up of discrete values i.e. = 5 * 2.99 * (1 + 0.065). Excel evaluates the formula to a value. An example of a formula looks as follows.
=B3 * C3 * (1 + D3)
In this second example, Excel formulas are used to calculate totals in rows and columns using ranges and functions. A calculation like this can save you a lot of time when you have to analyze or calculate complex spreadsheets.
VBA & Automation
Visual Basic for Applications (VBA) is an implementation of Microsoft's event-driven programming language Visual Basic 6. With Excel VBA you can automate tasks in Excel by writing so called macros.
VBA enables building user-defined functions (UDFs), automating processes and accessing Windows API and other low-level functionality through dynamic-link libraries (DLLs).
Example of user-defined function (UDFs)
Creating custom spreadsheet functions can be especially useful for advanced math, special text manipulation, simplification of long and complex formulas or date calculations.
Dim ProductName As Variant
Dim Price As Double
ProductName = InputBox("Enter the Product Name")
UnitPrice = WorksheetFunction.VLOOKUP(ProductName, Range("UnitPriceList"), 2, False)
MsgBox ProductName & " costs " & UnitPrice
Excel is a powerful tool but it can not work alone. It takes a savvy computer user to take advantage of everything Excel has to offer to provide the best results for your company.