Excel Data Visualization - Creating Effective Visuals When Using Excel

Proper data visualization is a game changer for any finance team. Professor Hans Rosling, a great statistician and data visualization popularizer, compared data analytics to music:

“Most of us need to listen to the music to understand how beautiful it is. But often that’s how we present statistics: we just show the notes, we don’t play the music”.

Effective visuals empower us to comprehend a large and complex dataset quickly. In a couple of seconds, you can grasp complex information when viewing well presented data. This understanding is the foundation of making better decisions.

Science proves the benefits of visual analytics. Applying IBSC-compliant reporting and data-rich presentations guarantee higher data literacy in your organization. A laboratory study with the Technical University of Munich proves the benefits of IBSC. The study found that good visual analytics resulted in 61% fewer errors in reading reports and 46% faster in decoding the data.

Excel is a particularly effective application for visualizing financial analytics. It offers a wide variety of visual tools, and is universally well understood by most finance professionals. That considered, it is critical to understand the importance of visuals in FP&A, how Excel users can best visualize their data, and what resources are available to address Excel’s shortcomings.

Consistent notation in FP&A

CFOs often don’t like colorful data visualization. There are three practices which are particularly helpful when improving business communication:

1. Reduce the noise and number of colors: Effective dashboard implementation needs a consistent style guide. Standardization is a way to make data analytics digestible: the fewer colors you have, the easier it is for the audience to focus on the key message.

2. Keeping consistent business communication is critical: Every expertise has its communication system to make work easier on the international level. Take a look at engineering sketches, music notes, road signs and even FP&A reporting with IBCS. A consistent method to present variances, actual, forecast and plan data reduces your time decoding the information and you spend more time taking actions.

3. Follow the IBCS recommendations:

4. Unification of the titles

5. Highlighting variances with green and red color

6. Using a consistent color and fill patterns, graphs read more easily this way

What Finance Professionals Should Consider When Creating Visuals

After defining standard notation to your reports, you can jump to the next level. The narrative in data visualization moves you to data storytelling in FP&A. These are important data visualization considerations for FP&A:

1. Understand the data:

Storytelling will always be a critical skill for FP&A. Data is hard evidence that covers current business scenarios. It is up to you what dataset you find the most insightful to FP&A leaders. As an FP&A storyteller, you enable a quick understanding of the business issues and call to action.

2. Develop the key message:

Effective data presentation happens when you are empathetic to your audience. Always think of the audience first. Formulate the story in your mind. If you have a significant variance in variable cost, it is worth adding an explanatory chart to answer the why question. Answer essential questions to develop the key message:

1. What is the purpose of my story?

2. Who is the target audience?

3. Why do I need to open this finance report?

4. What is the call to action?

5. What risks or opportunities do I want to convey?

3. Select the right charts: Visuals support the storyline. Proper graphs, charts, and comments are tools for the audience to tell a story with data quickly.

What Data Visualization Tools Excel Offers / Which Excel Data Visualization Tools You Should Use?

Excel is an excellent tool to use when creating visuals. It is well understood by many, and is capable of producing a variety of effective visualizations of a dataset. According to Microsoft’s documentation, you can use Excel to design at least 20 types of charts using data in spreadsheets. These include common options, such as bar charts, pie charts, and scatter plots, to more advanced ones like radar charts, histograms, and tree-maps.

There are 5 practices specific to Excel that finance professionals can use to maximize the effectiveness of their visuals:

1. Choose the chart appropriate to the story you’re telling

2. Distribute bar graphs evenly

3. Never use quirky effects like 3D with your visuals

4. Remove background lines

5. Keep your style simple

Data Visualization Tools - Beyond Excel’s Scope

More complex tasks often require tools more intricate than Excel. Data analytics and warehousing, which are, are better solved using tools other than Excel. Additionally, spreadsheets are also not ideal for a number of issues encountered in planning, forecasting, and budgeting.

A dataset can be further analyzed and consolidated by an FP&A software. The three main benefits of this tool (specifically with forecasting and budgeting) are as such:

1. Increased flexibility.

2. Improved data accuracy by directly integrating the data source to the analysis.

3. Better analytics and reporting capability through the use of tools like scenario analysis, data visualization and easy access to all stakeholders.

Companies like CashWeb Community, Pureshare Activemetrics, and DataRails offer software that can address the limitations of Excel’s visual tools, and even serve as a direct complement to Excel users.