Excel is likely the most comprehensive, and widely used application software. In more than 20 years in the BI industry I still have yet to see a more versatile solution. All of us are still likely only using a fraction of what this tool can do and often -particular with all the game changing features added in the last few years- not up to its current capabilities. Here are my 5 favorite tips that help many of our clients to use Excel more effectively for financial reporting purposes:
Separate Data & presentation
This is the essential one, on which I have also written dedicated blog: Excel A BI toy?. If you are using larger volumes of data from other sources available in a structured format, absolutely avoid copy & pasting it directly to Excel. Excel was never meant to be a database. You will only create a lot of maintenance and performance issues. This is 2018 there are fantastic options to establish a live connection to your data in Excel particularly supported by Power Query which allows you to integrate nearly any source under the sun super easily and transform it to your requirements. As opposed to copy and pasting, this approach will give you a dynamic link to your respective data sources and the spreadsheet will on refresh automatically reflect the current state of the data. When using a flat file import it will either update based on the latest version of the file or with multiple flat files you configure Power Query to link all files in source folder where the data of every new file will be automatically added to your data model.
Formula Views
This one requires the tip before: formula reports require an “OLAP” data source for example Power Pivot (that can be link to nearly any source e.g. relational tables, etc.) when you want to store data locally in the sheet or sources like SQL server Analysis Services (Tabular or Multi-Dimensional) as well as a variety of Third Party options.
Formula view can be started from scratch or by converting a pivot table (keep in mind it is to best duplicate the Pivot Table beforehand as otherwise it will be lost). The benefit here is you have a report dynamically linked to your source data with complete flexibility in regards to the layout as well as great options to update report structures based on formulas:
Pivot Tables
I am still amazed how many finance professionals are not making use of the unparalleled power of the Pivot Table which provides tremendous flexibility for reporting (and with using Acterys equally for planning!). My not so well-known tips here: Make use of drill through (double click on a cell) to get to the underlying records of a cell (unfortunately not supported with DirectQuery yet), use “Quick Explore” to drill into details for another dimension attribute and combine Pivot Tables with Pivot Charts. Here are a few examples:
Split Screen to work with two Pivot Tables
Quick Explore in Pivot Table
Conditional formatting
A great helper particular in conjunction with the Pivot table is conditional formatting (that can even be configured to apply only to particular measure tuples if you are using an OLAP source like Acterys). To help your audience understand data “at a glance and putting it in perspective for example using a bar conditional format will immediately show the user the significance of a cell before you have to consciously look at the number as well as pointing out outliers. My recommendation here: be subtle use colors not too hard on the eye and consider using the conditional format to point out the type of cell e.g. by data scenario: using a white background color for normal cells and a shading for a different scenario like “budget” (particularly important when you are using Acterys where the users can enter data in the pivot table. This will immediately show them where they should enter)
General Layout tips
The thousands of client spreadsheets that I have seen often contain absolutely crucial insights but more often than not presented in a way that is not necessarily wanting someone to spend more than the absolutely necessary time with them because they are so horribly ugly. Too often I see brash colored, grid nightmares. Here are a few tips:
With the titling my tip: use a clear text box click on it and add a dynamic link in the formula bar e.g. =”Financial Results ”&$C$6 to make your title independent of all changes on the sheet and dynamically update according to details selected e.g. in this case by pointing to the cell that contains the time and the company name.