Ad hoc reporting in Excel can look very different for different people. One of the benefits of using a spreadsheet based software is that it’s fairly flexible in how it can be used. For example, presenting data in Excel can range from simple to complex, and as a result, it’s frequently the best way to hastily address ad hoc requests from multiple departments.
Since finance professionals oftentimes are asked to create different types of ad hoc reports from different departments, the data requests are occasionally dynamic and bear a system of reporting that isn’t common or hasn’t been used before.
This is where Excel, and spreadsheet programs like it, can have benefits and downsides. That being said, let’s begin with the basics of creating ad hoc reports in Excel.
Start With Raw Data
In order to respond to any request, you first need data. nearly any system will export to Excel or .csv format. Using these spreadsheet exports will save time and energy.
Always be sure to produce a tab labeled “raw data ” that you don’t manipulate. This will allow you to link back to it or hastily source the raw data for quality checks later on.
Maintaining the integrity of raw data is important when contending requests, and occasionally it’s wise to word-cover the raw data tab to help with unwanted changes. It might be the case that a request will bear the use of several different data tables. In this case, combining the data tables might present difficulties.
Always combine like data sets if it’s at all possible. This will produce the maximum benefit when exercising features like pivots.
Understand The Request
Responding using Excel can be delicate, especially if the request isn’t clear. This is because Excel will only do what it’s told.
Excel is dependent upon the demands that are made. This means that, if the request is unclear, time can be wasted pulling unnecessary data into the workbook.
At the base of any good response to an ad hoc request is a clear request. Vague and general requests are not a good place to start!
Clarifying the requests might yield responses that are more useful. For example specific requests based on time periods, demographics, etc. will produce better results for everyone.
Filtering Data And Its Limitations
Almost all requests will require filtering. Filtering is an introductory function in Excel and is easy to do. Columns can be sorted and filtered, and advanced filtering is available as well.
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; fFor 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.
Conclusion
Ad hoc reporting in Excel is an efficient way to quickly respond to report requests from different departments in the organization. Although there are benefits and drawbacks to creating spreadsheet based ad hoc reports, following these tips will allow for smooth and efficient ad hoc reports.
