Definition: An excel programming company can be hired to organize workbooks, create custom formulas, automate repetitive tasks using VBA, and can consult on how to streamline processes. A good excel programmer should have general programming skills, familiarity with SQL, and knowledge of BI tools.
This article will cover what skills you should consider when hiring an Excel programmer, the benefits an excel programmer can provide, and a list of sample projects that a programmer can complete. The sample projects include workbooks you can download to help get you started on your projects for free.
Skills an Excel Programmer Should Have
This list of skills will help ensure the programmer you can hire can complete all project requirements. Many business projects in Excel require more than Excel skills. Before building the core Excel functionality, data often needs to be imported, exported, cleaned, or otherwise manipulated.
VBA Development (Macros)
VBA stands for Visual Basic for Applications. It is the programming language used in Microsoft Excel and other office applications. Written in C++, VBA allows a programmer to extend the native functionality of Excel and create truly interactive workbooks. A macro is the term used when you call a VBA script.
If your project requires saving a copy of your workbook with a click, sending emails, uploading data to SharePoint, or even creating a custom formula, VBA will help you accomplish this. Often, just using simple VBA scripts can increase the efficiency of an Excel workbook.
Databases (SQL)
A relational database is how computer programs store and access information. Like Excel, data is stored in tables, rows, and columns. However, relational databases are highly optimized and can handle large amounts of data much more efficiently than Excel. When you want to access or manipulate data inside of relational data, you will use the SQL programming language.
Accounting, ERP, and HR systems all use a database on their backend. Excel programmers that are well versed in SQL can help pull source data from systems and use them in an Excel workbook. Simplifying source data into the workbook can streamline many business projects by reducing the need for formulas to clean/format the data.
SharePoint
Many organizations use SharePoint to create websites and share files. In addition, sharePoint makes it easy to set permissions inside of a document library, making it possible to build processes like budgets where only certain employees have access to certain regions.
An Excel programmer could upload files to SharePoint, read data, and even dynamically create folders for additional projects using VBA. For most organizations, SharePoint can eliminate the need for custom-built websites.
APIs (application programming interface)
An API is how computer programs can share data. QuickBooks, for example, has a standard web API that uses HTTP requests to complete specific tasks. One of these tasks can be to pull in a list of vendors. This could be useful for creating a dynamic receivables report; instead of downloading a piece of information and pasting it into the data, an Excel programmer could use the API to retrieve it with a button click.
General Programming
An Excel programmer with experience in various languages will likely think of general ways to increase the efficiency of a workbook. In many of our Excel consultations, some processes use pivot tables or native excel tables to store and read data. A more efficient way to temporarily store and access data is using arrays. An array will allow you to perform lots of manipulation on the data in a split second.
The Benefits of Hiring an Excel Programmer
An Excel programmer will help streamline an existing workbook or create a custom workbook. Depending on the project, there could be a few different benefits.
Increased Efficiency
Because an Excel programmer will have expert Excel skills, increased efficiency can be obtained through advanced formulas. In addition, these formulas will often simplify a workbook. For example, instead of adding lots of SUM formulas to compute account amounts by period, you could use on simple SUMPRODUCT formula with the account number and period.
Some workbooks can contain messy formulas because of business logic requirements. For example, paying commissions might be based on many nested IF statements. The longer the if statement, the harder it is to maintain. A good programmer would instead create a custom VBA formula to use. You call the custom function =comission_amount() right inside an Excel cell, then add your parameters like any other formula. The logic would be easier to maintain.
Reduced Costs
Because programming will help automate repetitive tasks, an Excel programmer will reduce costs in future projects. Consider an example where monthly payroll reports need to be created. Regional managers might need to make adjustments to finalize the report. Instead of manually sending reports to each manager, a programmer could create one master file, pull data from an API, and then dynamically upload copies of the workbook to SharePoint.
Improved Processes
When a programmer starts a project, the project scope will usually detail the required inputs and the required outputs. A new process may be suggested if the inputs or outputs can be simplified.
A new process could mean eliminating the need for separate workbooks, using an API to receive data, and suggesting unique SQL views for cleaner source data. In addition, outputs could include automated emails with PDF attachments. These new processes will increase the utility of the existing approach.
Faster Decision Making
By building an interactive workbook, company stakeholders can plan for scenarios by just changing a few cells. For example, suppose a shipping company wants to understand how the overall profit margin would change if fuel increased by 5% during the year; easy with a well-programmed Excel workbook.
Excel Programming Rates
Depending on the firm, rates for Excel programmers can range from $50 to $150 an hour. SurveyKing consulting services charge a simple flat fee of $50 an hour. Freelancers generally do not go above $100 an hour. Financial modeling firms are usually the companies that charge $100 or more per hou
Sample Excel Programming Projects
Here are some of the projects our consulting team has worked on in the past. This list of projects will give you an idea of what is possible in Excel. In addition, some projects contain sample workbooks that can be downloaded.
Financial Statements
Putting together financial statements is one of the most common Excel programming projects. This usually involves formulas to summarize data from a trial balance. Error checks using conditional formatting to highlight when a balance sheet is not in balance or when reported cash does not equal balance sheet cash.
One of the most significant benefits of making financial statements in Excel is being able to forecast performance by just changing a few input cells (assumptions). The majority of our financial statement workbooks include a forecast trial balance tab. This tab captures data from a few inputs that can be quickly changed. The attached has a formatted input tab for the forecast.
Commission Report
Another typical consulting project is using Excel to track commissions. The source data often comes from a sales report from an accounting platform. The data is then used to create workbooks for each sales rep and uploaded to SharePoint for review. Some processes required manager approval and audit logs; perfect uses for VBA.
Operation Metrics
Shipping and logistics companies often send reports to managers summarizing delivery metrics. These metrics often need to be compared against targets and to prior periods. These projects include VBA to pull data and save a copy for each region to a SharePoint site.
The attached sample file has a dashboard that can be used to loop over locations and save a copy of the book. In addition, the VBA code is included. You will also notice the bottom of the metrics tab has a nice SUMIF inside of a SUM function to do a unique lookup type and summarize data simultaneously.
Financial Modeling
Financial modeling in Excel can refer to various things, but two of the most common projects are order or estimate forms and product sales projections.
Order forms and estimates help a company determine the optimal selling price based on various inputs. Projection workbooks usually use high-level assumptions to predict revenue and net income for a company or business unit. SaaS projections usually include dynamic tables to help model attraction and recurring revenue statistics.
ABOUT THE AUTOR
Allen is the founder of SurveyKing. A former CPA and government auditor, he understands how important quality data is in decision making. He continues to help SurveyKing accomplish their main goal: providing organizations around the world with low-cost high-quality feedback tools.
Ready To Start?
Create your own survey now. Get started for free and collect actionable data.
