Ad hoc reporting in Excel can take on many forms. One of the benefits of using a spreadsheet-based software application is that it is relatively flexible in how it can be used. Presenting data in excel can range from simple to complex, and as a result, it is often the best way to quickly field ad hoc requests from various departments.
Finance professionals are continuing to find themselves in the crosshairs of data requests from multiple areas across their respective businesses. Moreover, the data requests are sometimes dynamic and require a method of reporting that is not common or has never been used before.
This is where Excel, and spreadsheet programs like it, can be both good and bad. Here are the basics of creating ad hoc excel reports.
Start With Raw Data
In order to respond to any request, you first need data. Almost any system will export to Excel these days or .csv file format. Using these spreadsheet exports will save time and energy.
Always be sure to create a tab labeled “raw data” that you do not manipulate. This will allow you to link back to it or quickly reference the raw data for quality checks later on.
Maintaining the integrity of raw data is important when fielding requests, and sometimes it is wise to password protect the raw data tab to prevent unwanted changes. It might be the case that a request will require the use of several different data tables. In this case, combining the data tables might present difficulties.
If it is at all possible, combine like data sets. This will create the maximum benefit when utilizing features like filters and pivots.
Understand The Request
If the request is too ambiguous, responding using Excel can be difficult. This is because Excel will only do what it is told.
The output in Excel is dependent upon the demands that are made. This means that, if the request is unclear, time can be wasted pulling irrelevant data into the workbook and can inundate the end-user with more than what is required.
At the basis of any good response to an ad hoc request is a clear request. For example, sales demographics, financial performance, and picking times are all somewhat vague and generic requests.
Clarifying the requests might yield responses that are more useful. For example, average zip code of shoppers on a certain date, gross sales over the past month, time to pick and ship a certain product, and more.
The more clear the requests are, the more an answer will be relevant and concise.
Filtering Data And Its Limitations
Most requests will be for some level of information that requires the use of filtering. Filtering is a basic function in Excel and is easy to do. Columns can be sorted and filtered, and even advanced filtering is available.
Filtering is a basic approach in handling data sets when an ad hoc request is specific and somewhat easy to deal with. For instance, a request might come in for the price of a product, and a table of data might exist with all product pricing. Simply filtering by the name of the product might yield the required response.
More advanced filtering techniques exist in excel that allows users to specify a range of criteria and a range of data to apply that criteria too. This is a more complex approach to dealing with data sets when a request might be for several pieces of information.
Data Links And Queries
All requests, at some level, are for data. The ease at which a response can be made to an ad hoc request in Excel depends on the level of data available. Excel has the ability to link to external databases, which allows users to run custom queries that will quickly pull data into a spreadsheet.
This method of accessing data is usually the fastest and provides the most complete datasets to work with. One benefit of linking Excel to a database is that the information will be updated as the database is updated.
This allows for accurate and timely data sets to be referenced. Excel will also link to the internet to pull in various types of information; for example, market data can be linked directly to Excel and will refresh on-demand.
One drawback to accessing databases in Excel is that they often require end-users to manually refresh data, which can lead to incomplete or outdated data sets.
Ad Hoc Reports on Excel — Using Pivot Tables
When it comes to ad hoc reports, Excel has a feature known as pivot tables, which are a unique way to respond to ad hoc requests in Excel. They allow for data to be filtered and presented in a summarized way.
A pivot table is dynamic in that the inputs can be altered and changed depending on what the user is requesting. It will summarize all data in a given data set and will allow users to group columns and rows of data in unique ways.
Because the data is summarized, it makes using a pivot table for analytical purposes much easier. Even if the ad hoc request cannot be responded to with an actual pivot table, creating one might allow the user to more easily identify the answer to a request.
Once created, pivot tables can also “drill down” into specific data depending on what the user wants to do. This is done intuitively by simply double-clicking on the data, and because of that, pivot tables are often a default way to sort and summarize large amounts of data.
One drawback is that the data must be grouped into columns to be able to use a pivot table. Pivot tables also rely exclusively on the data sets they reference, meaning that if the data is not complete, the pivot tables’ summaries are not complete either.
Another downside is that pivot tables do not update automatically. This sometimes creates problems when data sets are linked or autonomous.
Presenting Data With Tables and Graphs
Certain ad hoc requests might require the use of tables and/or graphs. Excel has a wide variety of chart types and graphs to work with. Graphs are good at illustrating trends and directional movements with data. For example, sales volume over time, or gross revenue over time.
The challenge with responding in graphs is that there needs to be context given with the graph, and the appropriate graph needs to be selected. Line and dot graphs are good at illustrating trends, pie charts are good for illustrating proportionate data, and bar graphs are good for comparing categorical data.
Using DataRails To Respond To Ad-Hoc Request
DataRails is a unique software application that allows you to respond to ad hoc requests in dynamic ways. DataRails is an enhanced data management tool that can help your team create financial reports as well as monitor budgets, plans, and other KPIs faster and more accurately than ever before.
It has the ability to manage data sets in a user-friendly environment, cutting down on the time spent fielding ad hoc requests.
Big data must be useful. DataRails helps you use it to the maximum without having to learn a new proprietary user interface. Using Excel as a backdrop provides a familiar dynamic framework that makes adopting DataRails easy for any finance professional.
By replacing spreadsheets with real-time data and integrating fragmented workbooks and data sources into one centralized location, you can work in the comfort of Excel with the support of a much more sophisticated data management system behind you.
Build beautiful budgets, track and monitor business performance, and give users stunning and easy-to-use dashboards with DataRails.