TOC
Excel is a popular, easy-to-use, and powerful tool. Most importantly, most people already have Excel, so the cost of an Excel tool is extremely low! If you are a small business or have limited resources to commit to a data solution, an Excel based tool is a great option to consider. As one of my professors in grad school used to say: “we went to the moon using a slide rule, so Excel can solve most problems that exist on earth!”
In this post, we will review a few solutions I have used Excel to develop and the core functions that drive these tools.
Dashboards
An excel dashboard is great option for teams with limited budgets. If you need simple summary statistics, Excel can give you a great looking dashboard that you can slice and dice to answer your questions. Some of the benefits of Excel Dashboard include:
- Easy data entry (most people have excel experience)
- Click button updates
- No hosting or licensing costs
- Easy to format
- Filter data using buttons
- Leverage all the powerful features available in excel PivotTables
The example below highlights a planning dashbaord that supports managing tasks and resources.
Automation
If you need to create standard reports from your data, Excel and its programming language, VBA, should be able to solve many of your automation problems.
In a recent example, I created a workbook where a user can generate standardized, professional reports, which have built-in formulas to lookup values and calculate totals. A user can also generate summary reports that aggregate results from generated reports.
#
Core Features that Drive Useful Excel Tools
PivotTables
My favorite tool in in Excel is PivotTables. If your data is structured correctly, you can slice and dice your data to quickly summarize large amount of data without writing any code. Below are a few of the key features:
- Count, Sum, or Average data.
- Aggregate data by categories and subcategories.
- Create custom calculations and formulas.
- Quickly filter data to analyze different segments.
- Sort data by value or custom ordering.
- Double click to see underlying data and review details.
Below is a simple PivotTable example from Microsoft.
Once you create a PivotTable to calculate the metrics you are interested in, it is easy to incorporate the results into dashboards and link to charts.
Formulas
Excel has many powerful built in formulas that allow you to lookup values, compute future values of investments, conduct statistical tests, and determine the number of working days between two dates, just to name a few.
- vlookup (the formula I use most often) - append data from one table to another using an index
- if - return a value based on conditions
- networkdays - compute the number of work days between two date, with an option to exclude custom holidays
- FV - determine the future value of a current principal, future payments, with a given interest rate over specified intervals
These are few I use most often, but this is just a small introduction to the built-in power of Excel.
VBA
VBA is a useful tool when you need to automate repetitive tasks in excel. It is also useful when the logic required to return a value is very complicated or your workbook is very slow due to an abundance of formulas living in your worksheet. In these situations, VBA is great option to pursue.
Below are some common problems I use VBA to solve:
- Update data with a click of a button without the user having to follow a long list of steps.
- Create reports from templates and update values from reference data.
- Compute values based on a complex logic.
When a formula or PivotTable cannot solve your problem, chances are we can use VBA to produce the solution we need.
Next Steps
The best way to see the power of an Excel-Tool is to see one in use. What are you waiting for, let’s get started today.