10 Excel Tips for Financial and Project Reporting

Microsoft Excel has reigned supreme as the clear leader in spreadsheet software for decades because it is a powerful, flexible, and user-friendly tool for creating dynamic reports. Most ERP systems provide built-in reporting tools that meet the requirements of companies with basic financial and project reporting needs, but integrating your ERP with Excel provides robust functionality that significantly enhances your reporting capabilities.

While Excel is popular and widely used, we thought it would be beneficial to highlight some Excel features that the average Excel user (and perhaps even some spreadsheet aficionados) might not currently use. This blog will cover ten tips for visual improvement, sharing and collaboration, and productivity enhancements that will improve your ERP reporting experience.

1. Conditional Formatting

While nobody likes to be “in the red,” financial data stands out on your spreadsheet when you add conditional formatting, such as red or green fill, to your cells. In the screenshot below, you will see a percentage change column that is automatically highlighted either red or green depending on the calculation in those cells.

Curious about how to do this in Excel? Check out our video below for a quick tutorial.

2. Sparklines

Sparklines are great visual tools that you can easily add to your spreadsheets when you wish to communicate a trend, such as revenue, cost, or gross profit over time (sparkline for a 12-month period pictured below). Click here to travel directly to the sparkline section of our Visual Tips video above to learn how to add sparklines to your cells.

3. Grouping

The Excel grouping functionality allows you to show a summary and then also expand a set of rows or columns to view more details. It could be a “Sales” line that expands into dozens of sales accounts, or a year-to-date column that you can expand to see a month-by-month amount. It’s very simple to use, as you can see in this video:

Click here to travel directly to the grouping section of our Visual Tips video above to learn how to create row and column groups on your spreadsheets.

4. Auto-Hide Zero Rows

Do you ever encounter a large report that includes several rows of data that only have zeros? If so, Velixo can help with the Auto-Hide Zero Rows feature, which cleans up your spreadsheet, so you are only viewing essential figures. If you change a filter, such as period or branch, Velixo will automatically unhide any rows that no longer include zeros and hide those that do. For a short tutorial on this feature, please watch the video below.

To be clear, this is a feature that is only provided in Excel to Velixo users. Please get in touch with us, so you don’t miss out on the fun!

5. OneDrive and SharePoint

Many organizations and businesses are already using cloud storage options such as OneDrive and SharePoint to allow team members to store and share documents with others. While you can upload your files straight to your cloud storage folders, Velixo’s Distribution List feature can help you upload to your shared locations without leaving your workbook! Watch our video below to see how.

Click here if you would like to see our in-depth tutorial on Velixo’s Distribution List functionality.

6. Protect Workbook/Worksheet

If you wish to share workbooks with other users who will have editing privileges, we highly recommend using the Protect Workbook and Protect Worksheet features. When you enable Protect Workbook, other users will not be able to change the structure of your workbook (adding, deleting, hiding, unhiding sheets). When Protect Worksheet is enabled, users will either be restricted from editing the sheet(s) you specify or only be able to edit specific cells. See below for a great tutorial video on these Excel features.

7. Commenting

It is important to keep the lines of communication open when you are working collaboratively on a spreadsheet. To speed up your communication, why not use Excel’s commenting abilities? Click here to view our video on Collaborative Budgeting, which includes a tutorial on the commenting features offered in Excel.

8. Hyperlinking

Most folks know how to create a hyperlink in their emails and documents, but we find hyperlinking to be an underutilized feature in Excel. You can easily embed a link to a website in a specific cell on your sheet, link to another sheet in your workbook, and even link to another file.

9. Pivot Tables

Do you have large tables with a lot of data that you need to summarize and sort? If you are a Velixo user, and you are bringing in data from a Generic Inquiry in Acumatica, the answer is probably yes. In this case, you might want to use a Pivot Table. See below for a tutorial video that shows how to build a pivot table from a generic inquiry.

10. Freeze Rows and Columns

Little in life can be as frustrating as scrolling through a spreadsheet to review figures and constantly navigating back to the top row to see the column headings. To save yourself the pain, click on the View tab of the Excel ribbon and select “Freeze Panes.” You will have the option to freeze the top row, the first column, or your current selection. Once you enable this setting, you will never lose track of your headings!

Closing:

We hope that you can improve your ERP reporting experience with these Excel tips by creating eye-catching, collaborative spreadsheets that boost your productivity! If you are ready to join over 600 customers using Velixo to bring their ERP data to life in Excel, please Click here to schedule a demo.

This content was prepared by Presales Leader. Our team creates product marketing content including blog posts, videos, and click-through demonstrations that are tailored to your specific vertical market. We also offer subscription based presales support for the entire presales process. We work closely with your sales team to provide maximum results and support throughout the entire sales cycle. If you would like to explore how we can help your business grow with high quality product marketing content and/or strong end-to-end presales support, visit our web site at Presalesleader.com.