Data can be a powerful tool for driving business decisions, but a ‘sea of totally unrelated data’, when presented, can confuse your audience. However, you can make a huge impact by presenting that data clearly, using captivating visuals that tell a compelling story.

This guide will equip you with the skills to leverage the data visualisation tools readily available in Excel, turning your presentations from forgettable to phenomenal. By the end, you’ll be confidently creating stunning charts that impress your clients and leave a lasting impact.

Create Stunning Charts in Excel and Impress Your Clients

From Bland to Boardroom-Ready: The Power of Storytelling with Charts

Have you ever endured a presentation where the data resembled a scrambled mess on a spreadsheet? Confusing your audience with an avalanche of numbers is a recipe for lost attention and diluted messages.

The good news is that clear and impactful communication doesn’t require complex solutions. By utilising Excel’s data visualisation tools, you can transform your presentations into engaging narratives that inform, persuade and inspire your clients.

Think of your data as the building blocks of a story, and charts as your visuals. These visuals guide your audience through a logical narrative, making complex information easier to digest.

For instance, a marketing team presenting social media engagement metrics could ditch the bullet points and pie charts. Instead, a well-designed line graph could highlight follower growth across various platforms over time. This not only presents the data but also allows the team to delve into specific strategies that drove successful campaigns – see below:

Line Chart

Tailoring Charts for Maximum Impact

Understanding your audience is crucial for effective data visualisation. Consider their level of data literacy and tailor your charts accordingly.

For audiences unfamiliar with complex charts, bar graphs and pie charts offer a clear and concise way to grasp key information. For example, if you are presenting sales figures to regional managers, a bar chart comparing sales growth across different territories would be an excellent choice, allowing for easy comparison and quick identification of top performers or areas requiring improvement. See Bar and Pie charts below – Note the same data is used but each chart type presents a different perspective:

Bar Chart

Pie Chart

For more experienced viewers, consider using scatter plots or heat maps to reveal deeper relationships within the data. A financial analyst presenting risk assessments might utilise a scatter plot to demonstrate the correlation between stock prices and market volatility.

Scatter Chart

Always prioritise clarity over complexity. An overloaded chart can be confusing and counterproductive.

Excel: Your Gateway to Charting Mastery

Excel boasts a comprehensive selection of chart types, allowing you to present your data in the most impactful way possible. Whether you need to display trends over time, highlight comparisons between categories, or reveal correlations, there’s a chart in Excel suited to your needs.

Sunburst Chart

Histogram Chart

Waterfall Chart

Beyond choosing the right chart type, Excel empowers you to customise every aspect of your visuals. From colour palettes and fonts to data labels and gridlines, you can ensure your charts are not only informative but also visually appealing.

A strategic use of colour can significantly enhance the readability and impact of your charts. Imagine a sales manager presenting monthly revenue figures. Instead of a bland line chart, they could use a colour gradient to represent different sales targets. This would allow viewers to instantly see which sale at a particular point in time exceeded expectations and which fell short – see below:

Conclusion: From Dull to Dazzling: The Final Brushstrokes

By harnessing the power of data visualisation in Excel, you can transform dry data points into a captivating story that informs, engages, and inspires your clients. Craft a clear narrative, tailor your charts to your audience, and leverage Excel’s customisation features. Remember, a well-designed chart is worth a thousand spreadsheets.

The next time you’re preparing a presentation, ditch the text-heavy slides and embrace the magic of data visualisation. Leave your clients with a clear understanding and a lasting impression.

Example 1 – SEQUENCE

The SEQUENCE function allows you to generate a list of sequential numbers in an array, such as 1, 2, 3, 4.

=SEQUENCE (rows, [columns], [start], [step])

=SEQUENCE(5) will return this array:

=SEQUENCE(5,5) will return this array:

Start number and incremental steps can be entered as function arguments but in the above examples only number of rows and columns have been entered in the function.

Task: Build a dynamic Excel list which always shows expenses for the last two years starting from last day previous month and two years backward.

If the following is typed in the first cell (done 31/1/2024)

=TEXT(DATE(YEAR(TODAY())-2,SEQUENCE(24,1),1),”mmm-yyyy”)

It will result in:

To make this example easier to understand will it be broken down in a couple of steps:

DATE(YEAR(TODAY())-2,SEQUENCE(24,1),1)

The DATE functions arguments are DATE(YEAR,MONTH,DAY).

In the DATE function’s year argument, the year has been extracted from the TODAY() (current date) minus 2 to go two years backward.

In the DATE function’s month argument, the SEQUENCE function has been told to generate and array with 24 rows and 1 column. In the DATE function’s day argument is just entered 1 to start from the first of the month.

=TEXT(DATE(YEAR(TODAY())-2,SEQUENCE(24,1),1),”mmm-yyyy”)

A TEXT function has been put around to tell Excel to return the date format “mmm-yyyy”.

Example 2 – UNIQUE

The UNIQUE function returns a list of unique values in a list or range.

=UNIQUE (array, [by_col], [exactly_once])

=UNIQUE(G5:G12) will return this array:

=UNIQUE(G5:G12,,TRUE) will return this array (only distinct names):

Example 3 – Best practice designing a worksheet for data analysis by using dynamic array formulas.

Dynamic array formulas can make you able to design Excel worksheets which are fully automated and self-cleaning. No more time spend when the next month data are available. No need to delete old data. No need to update calculations or formulas to include newly added data. The dynamic array formulas can take you to a completely new level of efficiency as an Excel user.

Task: Build a dynamic Excel list which always shows advertising stats, number of sales calls, and sales figures for the last three years starting from last day previous month and three years backward.

This company generates a list with monthly advertising expenses and number of sales calls their sales team has done. The sales records are broken down on day, product, and sales reps.

The Excel analysis should provide the company with information about the correlation between sales and advertising expenses and sales calls.

In this example 3 dynamic array formulas are used.

The TAKE function returns a specified number of contiguous rows or columns from the start or end of an array.

=TAKE(array, rows,[columns])

The SORT function sorts the contents of a range or array.

=SORT(array,[sort_index],[sort_order],[by_col])

The SORTBY function sorts the contents of a range or array based on the values in a corresponding range or array.

=SORTBY(array,by_array,[sort_order],[array/order],…)

Step 1 – get the data from the source to the worksheet.

Both source lists are in tables. The list with advertising and sales calls is in a table named tblMarketing and the sales records in a table named tblSales.

To get the last 36-month dates from the tblMarketing table the TAKE function has been used.

=TAKE(tblMarketing[Date],-36)

The TAKE function has been told to create an array from the last 36 entries from the source table’s date column.

To make sure that it is always the dates from the last 36 month a SORT function has been nested inside the TAKE function.

SORT(tblMarketing[Date],,1)

The last argument in the SORT function is 1 to sort ascending.

All together the functions look like this:

=TAKE(SORT(tblMarketing[Date],,1),-36)

Step 2 is to get the advertising and sales calls from the source to the destination list.

=TAKE(tblMarketing[[Advertising]:[Sales Calls]],-36,2)

Here the TAKE function has both columns in the array argument tblMarketing[[Advertising]:[Sales Calls]] and the last argument 2 tells the TAKE function to return a two column array and again the last 36 rows.

To take sure to get the last 36 month a SORTBY function is nested. The two columns need to be sorted by the Date column.

=TAKE(SORTBY(tblMarketing[[Advertising]:[Sales Calls]],tblMarketing[Date],1),-36,2)

To be able to calculate the correlation the sales numbers also need to be brought in. Here is a SUMIFS function used.

Summary

Dynamic Arrays are a huge change to Excel formulas and maybe the biggest change ever. This is a game changer for all Excel users from industries such as finance, healthcare, and retail, well from all industries. This can dramatically reduce time heavy tasks and make Excel users much more efficient. This blog post just scratches the surface of how this impact the way we can work with dynamic arrays in Excel.

Improve Communication of Data Using Power BI Dashboards

How to Use Sparklines in Excel to Visualise Data Trends