For most finance professionals, Microsoft Excel is the technology you turn to if you want to do your job right. And for good reason. It's tried and true, meaning you're already familiar with it and can make the most of the functionality it offers.
But that doesn't mean you can't build on your skills or learn how to get even more out of Excel.
That's where Purna "Chandoo" Duggirala comes in. Founder and CEO (that is, Chief Excel Officer) of Chandoo.org, he's a Data Analytics and Visualization expert, specializing in Microsoft Excel and Power BI. Chandoo joined up with Vena in September for Excel Nerds: How To Build a Dynamic Finance Dashboard in Excel, a two-day workshop, now available on demand, that showed participants how to create a dynamic financial performance dashboard.
Using a mock data set for a fictional company--Awesome Chocolates--Chandoo took the audience through the dashboard creation process. And as he did, he offered some tips on how to build a finance dashboard that tells the story--and gets the results you want.
Read on for five of Chandoo's top tips. But let's dive a little more into Excel dashboards first.
Key Takeaways:
-
For your dashboard to be effective, it has to answer all of the questions you're trying to answer. To ensure that it does, start with a mockup and determine the best level of detail to get those answers across without bogging your audience down.
-
Data is integral to any dashboard, so having the right data in place is key. Collect the data you need from across sources to answer the questions at hand. Pivot tables can help you easily put that data into action and ensure your dashboard refreshes without any added effort on your part.
-
Your dashboard also has to look good if you want people to pay attention and clearly understand the points you're trying to make. A strong but minimalist design that fits your audience and message can help you better communicate the story you're trying to tell.
What Is an Excel Dashboard?
A financial dashboard is a data visualization tool used to track KPIs across the financial spectrum. This can include, but is not limited to, departments such as accounting, sales, marketing, customer service and human resources.
By sharing data with different stakeholders across your organisation in an easily digestible format, such as charts and graphs, it's simple to track the ongoing health of your company's finances--regardless of the amount of data or where it's being pulled from.
How To Create an Excel Dashboard
The steps that you take to build a KPI dashboard are unique to your company, its financial circumstances and its many financial needs. On the plus side, there's no shortage of options available to you. Conversely, you have to narrow your options to make an informed and confident decision.
A growing number of companies are opting to create financial dashboards in Excel, as it allows them to work toward their goals while using a familiar application.
What You Should Know About Building an Excel Dashboard
No two companies are identical, but the majority of them share similar reasons for using a financial dashboard. Here are five of the most common reasons why dashboards are important for performance:
- They save time
- They reduce the risk of errors
- They allow teams to more efficiently share data
- They empower you to better track performance and KPIs
- They allow you to keep your data all in one place
There's no shortage of the types of financial dashboards you can create. If you need it and if you can dream it up, you can create it.
Getting the Most From Your Dashboard: 5 Tips From Chandoo
In his recent Excel Nerds workshop, Chandoo offered several tips designed to help organisations get the most out of their Excel dashboards. To build dynamic dashboards that align with your business goals, keep these five best practices in mind:
Tip 1: Always Start With a Mockup (To Get Your Story Straight From the Start)
What exactly do you want your dashboard to display? What questions do you want it to answer? A mockup will help you make those decisions and ensure you're focused on exactly the right things.
"If I'm building these kinds of dashboards for a client, I wouldn't directly jump into the dashboard page," Chandoo shared during the workshop. "I would do a mockup exercise to understand what it is that they need and how they want it, what level of information needs to be presented and what KPIs are important."
If you listen to Chandoo's podcast, you'll also discover that he's an advocate for talking to your end users as a way to help inform that process. In doing so, you can better determine exactly what questions your dashboard needs to answer. This, he suggests, is the best first step to designing your dashboard and should happen long before you start choosing font colours or make any other kind of graphic decisions. Through those conversations, the narrative you want to share will emerge--fueling your mockup process.
The mockup doesn't need to be sophisticated, though. Chandoo has suggested using a pen and paper to put together a rough sketch, but PowerPoint would also work. In his Vena workshop, he suggests a two- or three-step dashboard narrative--maybe starting with a quick summary of what's happening in the business. The mockup can help you fine tune the story you want your dashboard to tell and the details you need in place to help visualise that story, before you even begin the process of building it.
Tip 2: Make Your Data Work For You (Not the Other Way Around)
Of course, data drives every dashboard. Would you even have a dashboard without it?
In his workshop, Chandoo used fabricated data from his fictional business, Awesome Chocolates, but if you're creating a dashboard of your own, sourcing the right data will be paramount. That might mean collecting it from different sources, cleaning it up before you bring it into Excel, then using a tool like PowerQuery to merge or synchronise the data.
When it comes time to put that data to work, as Chandoo pointed out in his workshop, pivot tables are also ideal. Besides letting you capture large amounts of data, they're easy to set up and don't require formulas--allowing for faster analysis. Pivot tables also mean any changes to the original data will automatically be applied to your dashboard--reducing the amount of manual labour needed later on. "This is a great time saver because every time you calculate something in a pivot table, it just shows up in the numbers," Chandoo said.
Tip 3: Determine the Level of Detail You Need To Tell Your Story (But Don't Bog Anyone Down)
Part of building any dashboard is deciding what level of detail is most appropriate for the story you're trying to tell. Or so says Chandoo, anyway.
"Do I need to really show the amount up to the last dollar or should we round it up to millions or thousands or something like that?" he asked. The answer will depend on the dashboard you're creating and who you're creating it for--and the value that kind of detail will add to your overall story.
"You can debate this, but I find that there is no additional value between 10.7 million or 10,744,335 at this level of reporting," Chandoo said. "So I don't want to see those numbers here. I want to see them rounded up to millions with one or two decimal points."
Tip 4: Don't Forget To Add Context (and Build Emotion Too)
Your dashboard isn't telling a complete story if you don't share the context that will make the numbers make sense. As Chandoo told audiences: "A simple number like 10.7 million has no meaning. You don't know whether that number is good or bad or better or worse or anything like that. There is no emotion attached to that number."
Context adds meaning to your numbers. But creating an effective story means eliciting emotion too. And for that to happen, you need a narrative arc--whether that means you show a triumphant win, a soul-crushing downturn or an underdog on the rise.
"A good thing to think about is instead of just saying 10.7 million, maybe provide a historical comparison," Chandoo said. "So last year, we did 8.2 million and this year we did 10.7 million. So 10.7 is highlighted, but right next to it or somewhere underneath you have 8.2 highlighted to provide better context. Now you start to have a feeling towards that where you feel like, 'Oh, we did better.'"
Tip 5: Add Some Oomph Through Design (But Not Too Much Oomph Either)
"If you're interested, there are a lot of interesting and fun ways you can design [your dashboards]," Chandoo told his Excel Nerds audience. By adding strong design elements, you can take your dashboard from bland and boring to visually head turning. But it's also true that when it comes to dashboards, a little can go a long way.
Among other things, after all, your dashboard is a communication tool--and for it to communicate effectively, it needs to be visual enough to draw people in. But at the same time, it can't scare them away either. That's why the right design can be the difference between getting your end users to look at what you've done or ignoring it completely.
Chandoo suggests adding icons, colours, shape variations and fonts to create a dashboard that pops. But a simple, clean look that doesn't go overboard with too many colours or too many fonts will go a long way. "If you're printing, you may want to go with black or white because that's how most printers work," he added.
Final Thoughts
Dashboards are powerful tools, and Excel allows you to create financial dashboards that stand out. But to do that, you need to approach each dashboard with thought and foresight--and with an eye toward the story you're telling and how it's being told.
Because ultimately, if done right, your dashboard will be used to drive decision making at your business. But to ensure the right decisions are being made, you'll need a dashboard that sings.
