How to visualise your data in Excel

Welcome to your weekly 3 tip Friday update, it’s just a short snippet of Excel help before you start the weekend. Each week we’ll cover a tip, a shortcut and a function - making it super easy for you to get better at Excel.

If you find this useful or have requests or ideas for future editions, send me a message or comment at the bottom of this post! Now, on with the tips. 

 

A quick win you can learn today -

One practice I see over and over, the use of pie charts - they’re the classic chart that everyone seems to use on their reports and dashboards. I’m going to fly against the grain here and recommend that you stop using Pie Charts, as they’re an extremely bad way to visualise data: 

 

  1. They take up an excessive amount of space to show a single metric

  2. For any more than 2 categories they are needlessly hard to read - it’s hard to visually measure proportions on a circle.

  3. Pie charts are hard to compare over time - if you have two side by side from different time periods it’s almost impossible to measure the change over time.

 

Instead, use simple bar charts or a well formatted simple table to display your data. 

 

A keyboard shortcut I'm adding to my list - 

Undo is a command we all use in all office applications, using the little blue arrow on the top menu bar.... there’s a simple keyboard shortcut to do this too, Ctrl + Z. I’m always surprised at the number of people who say ‘woah, what did you just do??’ When I use this shortcut. It’s an easy way to just quickly undo the last action you completed. 

 

This shortcut also works in PowerPoint, Word, Windows Explorer (e.g. when naming and moving files) and many other applications. 

 

On Excel for Mac, all of these shortcuts work with Command ⌘ instead of Ctrl. 

 

A function I've found super useful this week - 

This week I’ve been creating a resource planning tool for a client, and one thing I’ve needed to do is create a list of months extended for a year, based on a start date that the user enters - so if they enter May, the next row should be June and so on. 

 

There are a number of ways to do this, but I’ve been using the EOMONTH function with the date offset by a month to derive the next month. 

 

So if the start date was in A1, in A2 I’d type =EOMONTH(A1,1). So if A1 was 1st May, it would return 30th June. Format the dates as Custom mmm-yy to just display the month of year. 

Alternatively to always show the first day of the next month, you could use =EOMONTH(A1)+1.

Hope these were useful, until next time all the best. 

- Martyn

Edinburgh, Scotland

May 2018

Get More Exclusive Content

Sent Directly to your inbox with Excel 3 Tip Friday. A weekly dose of Functions shortcuts and tips for getting more out of Excel.

We won't send you spam. Unsubscribe at any time. Powered by ConvertKit