In this recent age of obsession with ‘big data’, Power BI, and other data analysis tools, it’s easy to overlook the power of Excel. Keeping things simple with familiar software is an attractive prospect if you want to present your datasets better with an Excel dashboard.
And yet with just a little extra learning in Excel, you’ll realise that you need not splash out on expensive new software and suffer the associated steep learning curves. You can create an attractive and interactive Excel dashboard, with a little know-how.
“Design is intelligence made visible.” – Alina Wheeler
In this blog, I let you know how exactly! I’ll take you through the 7 key steps of my Excel dashboard YouTube video series, where I create an HR Dashboard based on fictional (yet funny) people data from the TV series, The Office. I even share the Dunder Mifflin Paper Company HR data I compiled, so that you can follow along if you wish. Plus, I give the low-down of the pros and cons of using Excel over Power BI and other such ‘big data’ analytics software for dashboards.
Whether you need to present metrics from your project, sales data, product portfolio, or even customer surveys, these exact concepts and learning can be applied to any dataset. Before we begin on the pros and cons of Excel for dashboards, here’s the video where I demo the dashboard, how it works, and generally give an overview of what we will create in this video mini-series:
Dashboards: Excel vs. Power BI, Qlik and Others…
In an age of Power BI and other data analysis tools, Excel still holds firm. Many don’t want the new app learning curve, and there’s so much possible in Excel you probably don’t even need the others. But what exactly are the pros and cons of using Excel vs Power BI and other ‘Big Data’ analytical software?
Here’s the 6 main benefits of using Excel for your dashboard reporting from my experience:
- Minimal learning curve: To create interactive dashboards, you only need to spend a few hours watching my explainer series and you’ll have your own dashboard set up within the day. Conversely, learning PowerBI or Qlik will take weeks before you can create as aesthetically pleasing reports.
- Low cost: With Excel already in place for you and your report audience, there’s no big new software or licensing costs to consider. The only ‘cost’ is your learning and development time. Conversely, getting Power BI, Tableau, or QlikSense involves significant costs (£thousands) to your organisation, with licences enabling others to actually see and use what you’ve produced.
- Accessibility: If you buy or use another software platform to analyse your data and create dashboards, others won’t be able to see what you’ve produced! For example, Power BI requires expensive licensing, with headache-inducing complexity, to enable others in your organisation to see what you produced, or to publish reports online so anyone can see them. Conversely, almost everyone has Excel, so can see reports.
- Quicker and less hassle: If your company doesn’t already have Power BI, Qlik, or similar, there will be a complex procurement, installation, and testing process to get things up and running. Whether that’s by you as the ‘admin’ or involving an IT department in bigger organisations, it means there’s lots of time-consuming hoops to jump through before you can even begin creating (or learning to create!) reports.
- Suits most situations: If your dataset is under 100,000 records or so, Excel can handle it perfectly well and with manageable file sizes. So if you’re dealing with a few hundred or thousand employee records, or even tens of thousands of sales records, Excel can handle it. Within Excel, Power Query can be used to handle bigger datasets more efficiently.
- Flexibility with visuals: In Excel, you have greater flexibility and precision in creating all your visualisations and their surrounds. Ironically, working with ‘big data’ analytical tools is more fiddly to get your graphs exactly as you want, with all the associated formatting of labels, text, and shapes to complete the report experience for the viewer.
Conversely however, there are some downsides to consider as Excel isn’t perfect! There are good reasons why the likes of Power BI, Qlik, and other ‘big data’ analytics tools exist. Here’s the main issues, but also how to mitigate them…
- Unsuitable for ‘big data’: While an Excel spreadsheet can theoretically hold around 1 million records, if you’re dealing with datasets with over 100k records, or are linking in significant (>20k) datasets from multiple places (databases), the powerhouse data warehousing/management tools behind Power BI or Qlik will be undoubtably better. An intermediate workaround is learning Power Query as a bolt-on to your Excel spreadsheets, which basically makes Excel handle data more efficiently. But if your company has millions of sales transactions to analyse, it can probably afford to invest the approx. £50k + for such enterprise software!
- Stability: Excel is renowned for ‘freezing’ and getting stuck, having to ‘force quit’ and potentially losing your work; especially when working with far larger datasets. File corruption can also happen, a potential disaster for your reporting aims! To mitigate this, saving regularly and creating/maintaining a monthly backup file is good practice.
- File Sharing: With Excel files (like any files), complications can occur when sharing your reports with others. Emailing a copy is poor practice, especially with larger file sizes and you end up with people saving their own copies, confused what the latest version is. Meanwhile, sharing in a folder and sending your audience a link might create access issues or problems with multiple people trying to open at the same time, or even folk saving their selections before closing (grrr!). A good alternative is creating a sharable ‘latest version’ copy on a SharePoint or similar site, so you can link people to it while giving ‘read only’ permissions.
- Data security: Creating dynamic dashboards in Excel relies on Pivot Tables and a dataset. A user can extract data from a pivot table and ‘unhide’ any data sheets you’ve tried to ‘hide’. So you must be careful to not disclose any sensitive or personal information/data in the report you publish to your intended audience. This can be mitigated by simply deleting any sensitive
- Excel skills of audience: Another thing to consider of course is the Excel skills (or lack of!) of your intended audience. There’s nothing worse than spending ages creating an analytical masterpiece, but your audience has no clue on how to navigate, interact with, or even understand your report! This is overcome with honing your ‘style over substance’ skills, presenting beautiful and intuitive graphics, while considering ergonomics for your dashboards.
- ‘Clickability’ of visuals: A strong benefit of QlikSense, Power BI and such is the responsive nature built into visuals. I.e. clicking part of a chart is another way to filter data for the report, other than bespoke filter lists. With Excel, graphs aren’t ‘clickable’ in this way, your viewer must use the slicers you set up to manipulate the data.
So assuming you want to make your dashboard in Excel, let’s now go through the steps…
7 Steps to Create Dynamic Excel Dashboard from Data
I recently began a YouTube series on how to create an HR dashboard in Excel from your raw data. I did this using ‘dummy data’ I created as an example employee database. To make it more interesting, I based this upon the paper company Dunder Mifflin from the hit TV series The Office. If you’d like to follow along with this exact dataset, you can download this simple set of personnel data below…
Whether you’re structured data is derived from your sales database, customers, employee records, or something else, the concepts I convey can apply throughout. I embed my relevant videos along the way too, so you can follow along real time as I myself work and talk through each step in more detail. But here’s the outline of the 7 core steps to creating Excel dashboards…
Step 1: Set out your worksheets and colour scheme
The first task is to turn your blank new Excel workbook and default single ‘Sheet1’ into a multi-page workbook with logical flow. Plus, you might want your own colour scheme to match your company’s branding for added professionalism later, instead of Excel’s limited colour palette. This step is quick and non-technical and I summarise as follows:
- a) Create new sheets for INTRO, DASHBOARD, RAW DATA, INSTRUCTIONS (plus COLOURS, and LOOKUPS if you’ll find useful for colour consistency and advanced categories respectively).
- b) Format sheet tabs with a logical colour scheme to aid navigation among your viewers.
- c) Create bespoke colour scheme aligned to your branding on COLOURS sheet using ‘hex’ numbers.
Here’s my video where I cover the detail of both Step 1 and Step 2:
Step 2: Clean up and ready your dataset, add formulas
No dashboard can exist without some structured data to refer to, so get this in order first. You’ll need your raw data in its own sheet, plus any extra categories you create. Here’s how:
- a) Import your data into a single worksheet (or download my Dunder Mifflin people data as above).
- b) Add any important calculations, lookups, and categorisations to create useful, analysable fields.
- c) Format cells and the aesthetic appearance of the data.
- d) Format data as Table and rename, then refit column widths and correct for any formats Excel just imposed when doing this!
- e) Consider how new data will be added to regularly refresh report.
I run through important additional tips in my video above on this matter, but it’s important to mention Power Query here. In essence, I’m showing you how to create dashboards without needing to learn Microsoft Power Query. But if you’ve got a lot (100k) of data to process, you’ll probably want to import your data using Power Query, to make your Excel workbook more streamlined and data efficient.
Step 3: Create your pivot tables
Once your data is in place, it’s time to get some pivot tables set up to look at it. That’s because your nice ‘front end’ charts and associated filters on your dashboard will be referencing these pivots…
- a) Note the layout and categories you want to summarise.
- b) Create first pivot table of interest, referencing whole range of data (the Table you create in Step 2d).
- c) Format and ensure pivot doesn’t ‘Autofit columns on update’.
- d) Copy that first pivot table to then create other data summaries of interest, using pivot options.
- e) Give each pivot table a clear name (useful later!) and title.
Below is my associated walk-through video using my bespoke Dunder Mifflin dataset. You can also see my video explaining pivot tables generally for more info about pivots.
On pivot tables, I strongly recommend copying and pasting from the first one you created, then amend that to display further alternative representations of your dataset. This minimises file size, because otherwise Excel creates new datasets behind each brand new pivot table you create, even if it’s referencing the same data source.
In case you weren’t aware, every pivot table creates its own ‘cache’ of data (unless you switch this off in the pivot table options), which can soon accumulate. Further, when it comes to refreshing your pivots as you add new data to your data source, you only need to refresh one pivot table to refresh them all.
Step 4: Ready your dashboard space
OK so we’ve gone from data analysis noob (raw data table) to intermediate (pivot table summaries). It’s surprisingly easy to now go PRO, by presenting information more visually on a pleasing dashboard.
- a) Select columns A:AZ and narrow, to create square cell space.
- b) Roughly lay out sections with formatting (title, filters area, charts area).
- c) Make a pleasing header area with key info (title, branding, contact, updated date).
- d) Sort page margins (shade light grey, fit columns to one page then snip to just page 1).
- e) Hide all other rows / columns.
- f) Create shapes, titles, and arrangement to place charts on.
- g) Create titles and icons in broad shapes ready for associated charts (or do as you build Dashboard in Step 5).
Remember, a dashboard is all about ‘style over substance’, so the design is imperative. How you lay out your dashboard space is an important basis to the overall appearance, and therefore engagement and usability, of your dashboard report. Below I share a more detailed walk-through of this aspect…
Step 5: Create charts for your dashboard
You’ve readied the space, but an Excel dashboard is nothing without the data visualisations. I’ve covered how to create awesome Excel graphics elsewhere, but here’s the important aspects of this step.
- a) For simple total counts, insert textbox and reference cell value (not pivot ref!), format.
- b) Bar charts: Select a Pivot Table, insert first suitable bar graph.
- c) Improve graph appearance: Hide all field buttons; change font to Arial; delete title and legend; reduce bar gap width; add data labels; recolour bars and fonts; ‘no fill’ for background and graph border.
- d) Move chart to desired place on Dashboard, resize to fit area.
- e) When inserting other similar bar charts, copy and paste formatting of existing one to save time.
- f) Adjust textbox titles and icons to best use space around your charts.
- g) Use pie charts too for variety (try donut style and using text boxes as legend).
I’ve embedded the video walkthrough of this important step below. But I’d also like to highlight a massive timesaver I cover! Let’s say you’ve just created a beautifully formatted chart to present data on a specific dimension/theme. Now you want to create a similar chart, but slicing the data on a different theme and you don’t want to go through all those formatting settings again. Instead of recreating all the colours, formats, text styles, and so on (Excel always creates boring new charts!), you can simply copy the good chart and paste it over the new chart! This at the very least gives you a massive, time-saving head start on refining the next chart.
6. Create and link up your slicers
With your charts in place, what you’ve created so far is simply a nicely presented static report. It’s now time for the magical filters which make your dashboard dynamic and interactive! These filters / selectors are technically called slicers. So let’s create some and make them look nice, while linking them up properly…
- a) Note down on Pivots tab the fields you’ll want as slicers, to help keep track of things.
- b) Select first pivot table and Insert > Slicer, choose for all fields you want to filter.
- c) Create custom slicer style under Slicer > Styles, choose ‘New Slicer Style’.
- d) Select all slicers and apply your new slicer style (Ctrl + click to select multiple).
- e) Cut slicers, paste roughly in desired area of Dashboard, then resize and position (Alt!).
- f) Connect each slicer to the pivot tables through its ‘Report Connections’.
- g) For each slicer, right click > Size and Properties > Disable Resizing and Moving (under Position and Layout section).
- h) Test all slicers are working properly.
The video walkthrough is below, but for now a quick tip on slicers. Excel calls a filter a ‘Slicer‘. It’s akin to having a drop-down menu field placed above a pivot table. However, you can direct it to manipulate multiple pivot tables at once, and so they also then manipulate any charts you’ve created which derive from those helpful-yet-boring pivot tables.
Formatting can be customised, as can the columns and choice whether to display headings, for a ‘cleaner’ look. For slicer formats, you could even give groups of slicers a different format style, to signify themes.
Step 7: Intro page, tidy and save
You’ve done all the hard work. Why not now polish your report and help your audience navigate it, with an introduction page, tidying up the tabs, and then of course save it.
- a) Create an INTRO page to introduce your report, giving pointers and contact details.
- b) Sort out all the page margins, for static PDFs and/or printing.
- c) Hide technical sheets you don’t want end users to be confused by.
- d) If handing over the future updating of this report, create an instructions sheet.
- e) Save and share your professional-looking dashboard report with your audience!
See the finale video of this series outlining these finishing touches below:
I’d also just like to mention about the value of an introduction page and handover instructions.
Instructions are very important to communicate exactly how anyone can update the report you set up. This also allows you to hand it over to admin support (or to a customer to give a value-added complete product, as I do) for ongoing maintenance. In turn, this allows you to spend more time creating fantastic new reports and improving things, rather than maintaining them. I often create them anyway, just to remind myself of what to do if the report is more complex or I refresh it infrequently. There’s really no need to have this in a separate document, just put as one of the worksheets.
Remember to be precise and clear with your instructions, so that anyone can update, whether skilled in Excel or not. I recommend running through it yourself as you refresh the report before handing over; there’s always something you might not have thought of!
Introduction sheet: Most people overlook the need to have the first sheet in a workbook, introducing the rest of the Excel dashboard report. It should explain what’s included, how to interact to get the most from it (e.g. filter with the slicers), any important contextual information, when it was last updated (so people know they’re looking at the latest version), and who to contact for support.
To disguise the Excel look and feel, buttons can be used to help people navigate. You can either create bespoke images to use as buttons, or a simpler option is to just add hyperlinks to shapes or icons.
Try Your Own Excel Dashboard
Below is my completed file for comparison. Now it’s your turn to try making your own Excel dashboard. Let me know how you get on following my broad steps and detailed follow-along video tutorials. Let me know in the comments…
- Were you able to create something successfully?
- Was it to convey sales, customer, survey, HR, or some other data type?
- What hurdles did you encounter and how did you surmount them?
- Did I miss anything important in my explanations?
Of course, if you don’t have the time nor inclination for this sort of thing, I can create an Excel dashboard for you from a CSV or even text file of your company data. Get in touch if you’d like to commission me to create such a report for your company, handed back to you as a polished Excel report that is easy for you to maintain in future.
Kind Regards, Adrian
I hope this guidance helps you ‘excel at the office’! Subscribe to my blog and EATO YouTube channel for more useful info. Liking the content? Please like and share others who may also find it useful. If so inclined and able, buying me a coffee helps keep free content flowing and I greatly appreciate it. Want fantastic, time-saving templates or dashboards tailored to you and your business? Then please get in touch to arrange a bespoke quote.
Support my work
If you can and would like to support my work, buying me a coffee goes a long way to inspiring more free content and pay my web fees.
£4.00