• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Top 5 Mistakes With Reports in Excel

You are here: Home / Excel Charts / Top 5 Mistakes With Reports in Excel
top 5 mistakes with excel reports
October 25, 2011 by Mynda Treacy

There's a handful of mistakes almost everyone makes when they first use Excel. I know I did. In fact some of us are still making these mistakes.

Are you?

The good news is they're easily fixed.

Mistake 1. Create reports that take a long time to update.

Excel DashboardsI’ve done it before.  I’ve I imported the data from another source but it’s not in the right format so I go about fixing it, moving columns, inserting rows, deleting data I don’t need, sorting, adding sub-totals etc.

Then I feed it into my report using formulas that refer to a fixed range, you know the ones; =SUM($C$2:$C$500) then the boss loves it and wants it updated every month.

My heart sinks ‘cause I know it’s taken me the best part of a day to prepare that report and now I have to do it every month. I’m already over worked and I don’t have a day to spare to update this report.

Solution:

  • Lay your data out in a tabular format so that you can use functions like INDEX, MATCH, OFFSET, and VLOOKUP to name a few.
  •  
  • Set up your data in a Table format so that new data is automatically included in any formulas referencing the table.

  • Or if you prefer you can use PivotTables instead of Functions – Note: PivotTables only work with data in a tabular format so you still have to do this part.

  • Set up dynamic named ranges that automatically update charts each time new data is added.

  • Optional: Link your Excel file to your external data source (Access, web, Oracle database etc.) so you can update it with the press of a button each week/month.

Mistake 2. Create reports that are inflexible often resulting in having to start from scratch if changes are requested.

Excel DashboardsYou’ve spent hours creating your report based on the brief given, but when you show your boss they realise it would be better if you changed a few things.

You get back to your desk and start to make the changes but the original report was so complex that you decide it would be quicker and less error prone to start from scratch.

Solution:

As per point 1. If you’d set up your data model using a tabular format your report would be relatively quick and easy to modify.

Likewise adding new analysis from the same data source is easy when your data is in a tabular format.

Bonus solution:

Build in interactive elements to your reports that allow your recipients to customise the view themselves.

Built in features that automatically filter data and update charts and tables according to their selections like:

  • drop down lists,
  • option buttons,
  • and check boxes

allow the user to customise the view without you having to press a key, which is just as well because you’ve left early for the day cause your work is already done :).

Mistake 3. Create reports that are easy for you to understand but not the recipient.

Excel DashboardsHave you ever handed out a report only to be asked to explain it?

I know I have, and whilst at the time I put it down to people just not being versed in the language of Finance, if I’m honest I probably didn’t present the data in a way that was quick and easy for them to interpret.

Let’s face it, we’re all busy, our time is valuable and everyone is looking for quick answers.

If you can present information in this way you will earn much adoration, appreciation, and accolades….I can’t think of any more words beginning with ‘a’ but you get the picture :).

Solution:

  • Ask the right questions of the right people when planning your report so you understand their objective and expectations before you spend hours preparing something you think they want.

  • Use placement to draw attention to the key information you want to convey.

  • Make sure the report isn’t cluttered and full of superfluous information (even if it does look pretty).

Mistake 4. Use the fancy Chart Styles and formatting from the built in Chart features in Excel.

Using some of the Photoshop-like formatting available in the Excel Charts menu usually results in garish reports that detract from the message.

Excel DashboardsIt’s natural to look at the chart layouts and fancy formatting available in Excel, especially the new graphics in Excel 2007 & 2010, and think they look great.

Especially since we’re not all born with artistic talents (my 5 year old is more artistic than me).

But the problem with a lot of the Excel chart styles is they are cluttered with unnecessary formatting, and this clutter detracts from the message.

Solution:

Less is more. Get rid of the garish colours and formatting and back away from the 3-D charts.

Mistake 5. Use the wrong chart types for the data and subsequently hide key information.

A pie chart is only good if you have 4 or 5 categories at the most….and even then there’s probably a better chart.

Just take a look at the two charts below. Both plot the same data but one is much easier to make comparisons in the data than the other. You be the judge.

Excel Pie Chart

Choosing the right chart for your data is a key factor in making your message compelling.

Solution:

Know what chart will convey your message quickly and clearly. And use small charts and tables that show comparisons or trends in data.

How You Can Avoid These Mistakes

The solutions I’ve described above, albeit briefly, are all integral parts of setting up your reports as Excel dashboards.

Once you understand some fundamental rules about data visualisation and best practices for modelling data in Excel you will be amazed at the amount of time saved; not only your time to create and update the reports but also your report recipients' time to interpret the report.

So, what is an Excel Dashboard?

An Excel Dashboard is simply a report like the one above that:

  • Usually fits on one page
  • Displays key trends, comparisons and data graphically or in small tables
  • Provides the reader with conclusions to their objective
  • Is often interactive allowing the user to filter data and switch views themselves
  • Employs best practices that enable the report to be updated quickly and easily (often at the click of just one button)
Excel Dashboard Example

Dashboards can be complex or simple, fancy or plain. They can contain financial or non-financial data, or both. They can work with any data in any organisation.

In fact you’ve probably created a dashboard before and not realised that‘s what it’s called.

Over the past few months I've been working on a comprehensive Excel Dashboard Course that will teach you the simple techniques you can apply in Excel to make killer dashboards and save loads of time.

Click here to learn more about the dashboard course.
top 5 mistakes with excel reports

More Excel Charts Posts

picture fill excel charts

Picture Fill Excel Charts

Using a stylish picture fill in your Excel Charts is a simple way to make your data visualizations more captivating and memorable
excel speedometer charts

Excel Speedometer Charts

How to build Excel Speedometer Charts or Gauge Charts as they're also know, why they are BAD and what to use instead.
burn up burn down charts

Excel Project Management Burn Down and Burn Up Charts

Excel Burn Down and Burn Up Charts are easy to make with line or scatter charts. They are useful for monitoring the progress of a project.
wee people font charts

Excel WeePeople Font Charts

Excel WeePeople Font Charts are a nice change from generic shapes for waffle charts, bar/column charts and more.
excel dot map charts

Excel Dot Map Charts

Interactive Excel dot map charts are not built-in, but with some creative use of Excel’s built-in tools we can create something unique.
Excel S Curve Charts

Excel S-Curve Charts

Easy Excel S-curve Charts made with PivotTables for project management. Track progress by including budget amounts.
chart axis switch

Excel Chart Axis Switch

Use radio button form controls to create an Excel chart axis switch enabling you to toggle pannel charts between same axis and own axis.

Excel Charts with Shapes for Infographics

Excel Charts with Shapes for Infographic styling and increased interest in your charts. Easy to insert but there are a few tricks required.
excel pyramid chart

Excel Pyramid Charts

Excel Pyramid charts are useful for visualising demographic data across multiple categories. Let’s look at 3 ways we can build them in Excel.

Highlighting Periods in Excel Charts

Highlighting Periods in Excel Charts helps your users interpret them more quickly and or focus their attention on a point or area.


Category: Excel Charts
Previous Post:excel dynamic text formula trickExcel Dynamic Text Formulas Trick [FREE VIDEO]
Next Post:Excel Conditional Formatting Zebra StripesExcel Conditional Formatting Zebra Stripes

Reader Interactions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

launch excel macros course excel vba course

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

239 Excel Keyboard Shortcuts

Download Free PDF

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.