5 Tips to Automate Excel for Reporting and Budgeting
We’re obsessed with helping our customers take the pain out of processes that people currently battle with Excel to achieve. We try to get Finance teams using Excel effectively and in a way that is low-risk, highly productive and leads to great outcomes for their business. This usually means less reliance on lots of complicated spreadsheets.
Sadly, I see Excel workbooks all the time from potential clients that are very manually driven. Formulas are not utilized when they can be. Less than 1% use automation options like ODBC or macros meaning their spreadsheets are complicated in ways they just don’t need to be.
It’s awful seeing great people battle with Excel. This post gives you my top 5 tips for improving your productivity and accuracy with Excel. In each of the 5 tips, I offer links to sites that will help you get moving. Enjoy!
1. Use Excel Formulas, a lot!
The more formulas you can use in Excel, the better. This means fewer mistakes and less copy and paste. Copy and paste is your enemy! if you reduce repetition; you reduce the potential for error. There are hundreds of formulas to lean on. Check out this page from Microsoft to get started on more powerful, yet simple formula to make your Excel reporting and budgeting life easier.
My favorite formula for the more complex data manipulation is VLOOKUP() and SUMIF().
I use VLOOKUP all the time! Any time I am matching data or looking for data on other sheets to present on a report, VLOOKUP is my go-to function.
SUMIF is a very cool function too. It helps me create report templates in Excel that are driven with a drop down on the spreadsheet. Using the SUMIF function means I can have one template for each report type, and users can select different months, cost centers, etc. and the template updates with data based on the user’s selection. You do need to have your data loaded into the Excel workbook too, which can be limiting and make the workbook rather large on disk and slow to load. But it’s far better than having the same report on several sheet tabs for all your cost centers and periods. It will save you many hours each month maintaining and updating reports.
2. ODBC connection
ODBC stands for Open Database Connectivity. I promise – no more jargon. ODBC is a way to get data into Excel to use with your Excel formulas to better automate your reporting and budgeting. Ideally, when you need to produce that report or your monthly GL reporting, you open your Excel workbook containing your reports, hit a button and shazam! All your reports update with the latest data. The dangerous alternative is downloading or receiving a text or data file and loading it. That’s not as good as ODBC because you’re back to copy and paste.
ODBC will enable you to connect to your source systems directly and import your data into Excel whenever you need. This means no more waiting for IT to send you data, no more merging data into existing reports, and no more pain. You can learn more about ODBC for Excel here and here for a step by step guide.
3. Macros
Yes, macros. Don’t be afraid!
“I don’t like programming – coding is for programmers!” I hear you say. Imagine if you did know how to write code or Excel macros. Do you think if you could create just a few lines of code in Excel that saves you 8 hours every week, it would be worth learning?
I met an accountant at a CPA conference recently, and I asked him about his financial reporting at the $1B company he worked at. He said it’s fully automated in Excel. My eyes lit up! “How?!” I asked. “Macros, ODBC, and formula,” he said. Could this work for you? Check out Trump Excel, a cool site with lots of Excel tips and some beginner macros to get you moving.
4. Third party tools and add-ins
There are a lot of third-party tools, but only a few are excellent. CALUMO is my choice (obviously ?). CALUMO gets me and my clients around the limitations of Excel, doesn’t require macros, and focuses on your strengths in Excel, formulas and formatting. All of the good parts of Excel and none of the bad. I can also easily distribute my Excel workbooks as interactive web pages for the non-Excel savvy report consumers and budgeters so they have immediate access to what they need without finding files or even starting Excel.
5. Take a course or buy a book on Excel
If you use Excel every day but you’ve never taken a training course, you don’t regularly read tips, and haven’t downloaded a manual for a while then this link is for you! it’s a Google search for available online Excel training courses. They are low cost, fast and easy to do, and it should be an easy sell to your boss. From there, you will save time and lower the risk of errors.
Learn more about budgeting, planning and forecasting for your business here.