Can an Excel spreadsheet automatically fill in a form, generate an invoice or prepare a report?

What follows are a number of document completion tasks we’ve been asked to undertake and reasons why you might use Excel. A summary of the considerations is shown in the table below:

When should you build quotes in Microsoft Excel?

Excel quote tools are most useful when pricing is dependent on many variables. Any quote that can be completed using a single price list is best incorporated into a website that doubles up as a sales portal.

When different customers get different discounts, different locations get different labour rates, and different products have different units, then a spreadsheet can be used to bring everything together. That plays to Excel’s strength as a calculator and the fact you can separate a quote’s components into an easily readable output. A spreadsheet can be configured to track margins and hide products not selected, all within a common framework.

Excel4Business is often asked to build quote tools for the construction industry where overall margins matter and services are variable. We tend to get asked for help by specialist firms, such as those fitting out commercial restrooms, that are underserved by specialist software. Excel cannot show a customer how solar panels would fit on their roof so we would not recommend a solar contractor use Excel to generate their quotes.

Note that building quotes in Excel is very different to supplying quotes in Excel. We would recommend all quotes get saved in PDF format before being sent to prospective customers. That’s to ensure they are able to see the quote, and to ensure they can’t see any commercially sensitive information that may have been used in preparing the quote. The steps hiding of sensitive information and printing a PDF can be automated using a macro.

When should you use Excel to make outbound enquiries to suppliers?

Excel is best used for supplier enquiries when those enquiries relate to another process being managed in Excel. If you’re building a quote in Excel, you may need to get prices for custom items at the same time. As you’ve already entered a description of the items, Excel can send off enquiries to your preferred suppliers.

Excel shouldn’t be used when there’s no need to keep a historical record of the enquiry, and a simple e-mail or phone call would be faster. Excel is very good at filling in document templates but, when you play the role of customer, there’s generally less need to standardise everything.

Our Excel consultants have found other uses for Excel in supplier enquiries. Pharmacies can purchase medicines from any number of suppliers. Each month, Excel can analyse inventory and sales to draw up a list of required medicines. This list can be issued to suppliers, returned prices compared, and final orders issued to the cheapest supplier of each product.

This is almost the opposite use case to getting prices for custom items. The items are standard off-the-shelf items available from many suppliers. However, the prices are constantly changing so fresh enquiries are constantly required. Excel works very well because suppliers can co-operate in providing the required information. All they need to do is open a spreadsheet, fill in some prices and send it back. If they find the process time consuming, they too have the option of automation.

How can I speed up filling in Excel sheets supplied by other organizations?

A one-off data request is best completed using a combination of copying, pasting, auto-filling, some simple formulas, and manual data entry. The biggest time cost is generally in interpret the form’s requirements. If you have to make regular submissions to a local authority or investor, then it makes sense to partially fill a version of the template with data that doesn’t change from month-to-month, whilst using code to populate the rest.

For most development projects, you can calculate the payback period in terms of monthly cost savings and compare that to the solution’s expected lifetime. Normally you have some control over the finished product’s useful life. Unfortunately when you’re filling in other people’s files, you no longer control the process. It can be worth checking the ‘last modified’ date of any template to see how often it gets changed. Experience suggests a form processor may only be used 5 or 6 times before requiring an upgrade.

Traditionally, that would limit the business case. However, the changes made to forms tend to be limited; sometimes an extra field is added, sometimes a typo is corrected. AI should be more than capable of identifying the changes made and correcting any existing code without having to re-hire an Excel developer. At Excel4Business, we would not recommend getting AI to build a form-filling macro to handle regulatory compliance, because an AI finds it difficult to safeguard against format changes.

We are regularly asked by clients to assist in the bulk upload data to their websites. This data generally needs to be supplied as a table of data saved in a text format such as a CSV file. To keep the process simple, database administrators like our clients to supply their data in an Excel spreadsheet. Although the file is often provided by a third party, it is a bespoke spreadsheet that exists purely for the client’s benefit. If the upload isn’t a one-off, it makes sense to automate the process because the format isn’t likely to change and these files typically contain thousands of rows of data.

When should I use Excel for invoicing?

There must be a good reason to use Excel over specialist accountancy software. Any business with multiple employees is going to have to keep track of accounts receivable for tax purposes and, if you invoice from Excel, you’re giving up a ready-made solution to that problem. One reason not to use accountancy software is because you want to build a picture of customer billings. If so, a CRM platform like Salesforce provides billing options and can integrate with most accounting software.

You might use Excel for invoicing when it’s awkward to create the invoices you want through standard platforms and you’re already using Excel to store the data used in the invoices. One of our clients runs a tutoring network. Their customers’ invoices contain a record of all lessons attended alongside a complete statement of accounts. The tutors record lesson attendance in Excel, alongside any cash or cheque payments made by the students. The system already has all the data required to generate PDF invoices, so it makes sense to generate them from Excel. Administratively, it is easier to handle customer queries when everything is kept in one place.

One feature of the above example is that the company’s accounting profit is effectively made when a student takes a class. The tutor performing that work finds it convenient to keep attendance records in Excel and they are far removed from the company’s back-office functions. A good reason to use Excel for invoicing is because you want to build processes around your frontline staff, and not the finance team. In that context, sending invoices from Excel should be seen as a small part of a bigger system.

Can I use Excel to prepare legal documents for potential litigants?

With the rise of class actions, law firms increasingly need to manage several individual claims within a single lawsuit. These claims are historic so involve an interest component that requires calculation. Where Excel is used to perform the calculation, it makes sense to push those figures from Excel into any reports. This works very well for class actions where you have a template report setting out the legal case, as it lets lawyers change their written arguments in the comfort of Microsoft Word.

Given how frequently Excel formulas get broken, it may seem strange to use Excel as part of a legal process. The benefit of Excel is that it is relatively straightforward to audit a calculation process because each step of a calculation can be displayed separately. At Excel4Business, we have been asked to perform those audits and serve as an expert witness. We have also been asked to build the calculators and, in doing so, provide an initial cross-examination of the proposed methodology.

There is a case for building an online calculator because it serves as a calling card for potential claimants. In any class action, the initial objective is to find suitable claimants and an online tool is a great way of showing people how much money they might be due. An administratively light way to run a class action is to take minimal details from claimants and to deliberately underclaim in an irrefutable way. That might encourage early settlement and might never require a more complex Excel calculator.

The big saving of that approach would be on processing documents released by the defendant but, as AI can now replace a lot of the manual work, it makes more sense to maximise each individual claim using a well considered Excel calculator.

Can I use Excel to send out targeted mail campaigns?

Most companies use specialist software to create e-mail campaigns to target new or existing clients. These tools create professional looking mailers and incorporate the unsubscribe options that are generally required by law. Excel is not a publishing tool and is not designed to comply with any data protection regulations. Excel can be useful in making decisions on who to target and when, but we would still recommend transferring any filtered list to third party software to send out marketing literature.

Excel has always been capable of creating personalised HTML e-mails within Outlook because Visual Basic was shared with all Office products. Microsoft are currently trying to migrate users to the new Outlook which is online and so no longer offers that integration. Assuming you have the old Outlook, Excel can also send e-mails from Outlook accounts on your behalf provided you stick to server limits.

Our Excel consultants could use this auto-mailing ability to send your clients e-mails containing their purchase history. If you wanted to use that information to re-market products to previous buyers then it could make sense to use Excel. We would still only recommend Excel for business-to-business sales because there is less regulation around marketing material and because professional buyers will be more interested in your offer than in the underlying aesthetics. It would also make most sense for a low volume campaign.