Why use dashboards in Excel?

A couple of weeks ago, I featured a guest post from Mynda Treacy on how to create Excel Dashboard Reports. This post went down well with readers, as did Mynda's training course.

Although the early bird offer mentioned in the post has now expired, you can still access Mynda's Excel Dashboards online training course until 9th August. So do it now if you don't want to miss out - and you'll still get the my Introduction to Pivot Tables course free when you do sign up.

Mynda's blog post and her course have proved very popular with those who understand that employers are increasingly requiring this skill, but I thought it might be useful to write a post myself on the purpose of dashboards for the rest of us.

Sometimes it is easy, amid all the trials and tribulations of our day-to-day work, to forget why we are creating spreadsheets in the first place. In most cases, it boils down to one thing - taking all sorts of disparate data, and turning it into something that our business/organisation/household can use.

Dashboards are the ultimate expression of that purpose. Like a dashboard in a car, the idea is to have all of the information you need right in front of you, in a very visual format, so that you can immediately focus on what is important.

The selection of the dashboard contents, the methods of visual representation, the use of colours, font sizes and graphics, all should be serving this purpose.

Imagine driving a car, and having 30 screens to flick through to tell you the current state of your vehicle and journey. Do you think it's possible that you might run out of fuel, because the fuel level is mentioned on screen 7, and you are busy monitoring your speed on screen 26?

This sounds silly, but often we tolerate this kind of information overload in our organisations.

Dashboards can be used for anything, whether it be the current state of your business or department, or distilling all of the results of the Tour de France!

The key in every case, is to give the drivers of our organisational vehicle all of the important information they need to drive us forward as safely and quickly as possible.



If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get your free report "The 5 Excel features that you NEED to know".

Excel Tip - The NETWORKDAYS function

Before we get into today's post, just a little reminder that tomorrow (31st July) is your last chance to get the 20% discount on Mynda Treacy's Excel Dashboards course. Also, don't forget I'll be sending an absolutely free copy of my Introduction to Pivot Tables course to everyone who signs up to Mynda's course.

This week's post is a quick one on a useful function that allows you to calculate the total number of work days between two dates. This is particularly useful for budgeting, where production (or sales) is driven by the number of working days in each period.

The syntax of the NETWORKDAYS function is as follows:

=NETWORKDAYS(Start date,End date,Holidays)

The Holidays variable is optional, so at its most basic the function is as follows:

=NETWORKDAYS(Start date,End date)

The dates need to be either entered as their serial numbers (see my earlier post on elapsed time in Excel that explains how Excel dates work), or refer to a cell that includes the date (usually more what you would want, rather than hard-coding a date in the formula.

If you do need to hard-code the date in the formula, use the DATE function which returns the serial number for a date.

The DATE function is used in the format =DATE(Year,Month,Day), so =DATE(2013,7,30) returns the serial number for today's date (30th July 2013) which, incidentally, is 41485.

Taking the more useful approach where the dates are held in cells, let's assume that we have our start date in cell A1 and our end date in cell A2, then:

=NETWORKDAYS(A1,A2)

will give us the number of working days between the two days (INCLUDING the start and end dates).

So, if A1=1/7/2013 and A2=31/7/2013, then:

=NETWORKDAYS(A1,A2)

returns 23, which is 31 days less 4 Saturdays and 4 Sundays.

This is great, but what if we are in the US where 4th July is a holiday?

To allow for holidays, we allocate a range to enter holiday dates. So let's say we decide to use the range B1:B20 to enter the holiday dates for the year and put 4/7/2013 in that list (along with any other holidays we want to allow for).

Now if we use the formula:

=NETWORKDAYS(A1,A2,B1:B20)

this time we get 22 (as long as 4/7/2013 is the only July date in the range B1:B20).

I hope you find it useful...





If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get your free report "The 5 Excel features that you NEED to know".

Excel Dashboard Reports

We've got a bit of a change this week, with a guest post from the Queen of Dashboards, Mynda Treacy (of My Online Training Hub).

This is a hot topic for Excel users, as the ability to create dynamic dashboards is increasingly becoming a requirement of employers.

Mynda's article provides some  great information on the subject, but if you scroll down, you can take advantage of a brilliant offer on her excellent Excel Dashboards training course (but only if you act quickly). I really would recommend it.

Excel Dashboard Reports

Guest post by Mynda Treacy of My Online Training Hub

There’s a new craze around town and it’s called Excel Dashboard reports. Actually it’s not that new but it’s becoming a standard for reporting, and as a result it’s a must have skill for any Excel user who prepares reports.

Dashboards are a visual display of your data. They convey the most important information at a glance, and they often enable the user to interact with the report and choose how they view the data, like in this interactive dashboard below:

The principles for building dashboard reports can be applied to almost all of your Excel work.

As you read through the tips I’m about to share keep in mind other areas of your work you could apply these principles to.

Click here to learn how to build interactive Excel Dashboard reports in this online video course.

How to Build an Excel Dashboard

A dashboard is really just a compilation of charts and data tables that fit on one page. This is something Excel is ideal for and as a result you can create some amazing dashboards without the need for any other programs.

I recommend you keep your raw data, your analysis and your dashboard on separate worksheets so that the flow of data is clear and uncluttered.

Ensure your data is in a tabular format. That is; there is a separate row for each record. The column labels tell you what type of information is contained within, like this:

If your data isn’t in this format it will make your life difficult and create additional unnecessary work. Why? 

Because most formulas and tools in Excel are designed to be used with data in a tabular format.

That means if it’s not in a tabular format you possibly won’t be able to use some of the built in tools designed to analyse data quickly.

For example, if you want to use a Pivot Table for analysis then your data must be in a tabular format.

Plus there are loads of formulas that work with data in a tabular format, like these useful functions for building dashboard reports:

SUMIF, SUMIFS and SUMPRODUCT
AVERAGEIF and AVERAGEIFS
COUNTIF, COUNTIFS, COUNT and COUNTA
MIN, MAX, SMALL, LARGE and RANK
Database Functions like DSUM, DCOUNT, DMIN etc.
VLOOKUP, HLOOKUP, INDEX and MATCH
IF, Nested IF’s, IFERROR, OR and AND
OFFSET, INDIRECT and CHOOSE
GETPIVOTDATA

For free tutorials on the above functions go here: Excel Formulas

So, now you’ve got your data in a tabular format, on the Analysis sheet you can set up a table to feed each chart or table that will be on your dashboard.

Excel Dashboard Design Tips

Now, before you get carried away with those 3D pie charts here are a few design rules.

Your mantra should be KISS – Keep It Simple Stupid. That means:

1. Don’t use 3D effects, gradients, mutli-coloured charts or give each chart in your dashboard a different colour.
2. Don’t use pie charts unless you are only displaying 2 sets of data. Any more than that and a pie chart is virtually useless. Instead try a column or bar chart.
3. Let the data do the talking. The less legends, labels, gridlines the better. If you think you need these then maybe your chart is trying to do too much. Remember it should be quick for the reader to interpret. If they have to read every label and refer to legends then it’s not quick.

Let’s take the two charts below as examples. Both charts display the same information. The first chart is cluttered with ‘chart junk’; bevelled edges, redundant Y axis labels, dark colours etc. The linear trend line isn’t aiding interpretation since we automatically do this without even realising just by looking at the column heights, likewise the grid lines.

If we remove the trend line we can also get rid of the legend since the chart title tells us what the data is.

Now look at the second chart. It’s clean, the data does the talking and we get the key information without any distractions. The bottom line is it’s quicker to interpret, and that’s the goal.

Making Your Dashboard Interactive

We can build interactivity into our dashboards with an Excel Drop Down List a.k.a. Data Validation List.

We then link our formulas to the output of the drop down list.

For example, let’s say we have our regions we want the report to toggle through. We’ll set up our drop down list in cell A2:

Then on our analysis worksheet we have a SUMIF formula that sums all of the data for the chosen region.

So using the example data here from our Raw Data worksheet:

Our formula would be:

=SUMIF('Raw Data'!B2:B50, 'Analysis'!A2, 'Raw Data'!G2:G50)

In English the formula above reads:

Where the State in column B of the Raw Data sheet is the same as the State selected from the drop down list on the Dashboard sheet, sum the data in column G on the Raw Data sheet.

Rinse and repeat for other tables in your analysis sheet so they all link to the drop down list.

And like magic before your eyes your dashboard report will dynamically update upon selection in the drop down list like this:

Animated Charts

Animation isn’t always for every chart or table in your report but in some instances animating the data in your chart will help users recognise patterns in the data that might otherwise be difficult to see.

For example animation of how data moves over time is often helpful, but not always.

It requires VBA to control the animation. We then link formulas to the output of the VBA, similarly to how we did for a drop down list.

If VBA isn’t one of your strengths you can learn how to animate charts in Mynda Treacy’s hugely popular Excel dashboard course.

About the Author

Mynda Treacy is co-founder of My Online Training Hub, author of their comprehensive Excel Formulas list, and popular Excel Blog.

She started using Excel in 1995 and has been teaching and writing about Excel since 2010.

If you would like to learn from her you can. She shares her knowledge in her Advanced Excel course and periodically she also opens her Excel Dashboard course.


SPECIAL OFFER:
Just until the end of July, Mynda has offered Not Just Numbers readers a great deal on her Excel Dashboard course.

Receive 20% off Mynda's Excel Dashboard course (if you sign up by 31st July).

And, as an extra incentive for Not Just Numbers readers to take advantage of this offer (as if learning Excel Dashboards and getting 20% wasn't enough) I will also send you my Introduction to Pivot Tables Course absolutely free of charge (this may follow later as I need to wait until I get your email address from Mynda).

So, do it now while it's fresh in your head. If you don't, you know you'll not remember again until the offer's gone!

Click here to get the Excel Dashboard course with 20% off, as well as your free copy of An Introduction to Pivot Tables.

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get your free report "The 5 Excel features that you NEED to know".

Excel Tip: Rounding Numbers

Anyone who works with numbers for a living knows all too well the perils of rounding errors.

One of the positive aspects of Excel is that, by default, it doesn't round numbers. It stores (and uses) the full number, even if it is only displaying, say, two decimal places.

This is great for ensuring that calculations are accurate, but can lead to some apparent oddities when adding up calculated figures.

You can run the risk of displaying, say, three figures with a total and the total appears not to add up. This is because the numbers are displayed to, say, two decimal places, despite the fact that the numbers underneath might have four or five.The complete numbers do add up, but the displayed numbers don't.

This is where you need to know how to round in Excel.

Excel offers three rounding functions (ROUND, ROUNDDOWN and ROUNDUP) which all work in a similar way.

The ROUND function has the following syntax:

=ROUND(Number,number of decimal places)

This rounds "Number" to the number of decimal places specified using the rule I was taught at school - round down up to 4 and round up 5 and above. The number of decimal places can be negative to allow you to round to the nearest ten (-1), hundred (-2), etc.

The other two rounding functions work in exactly the same way, except ROUNDDOWN always rounds down and ROUNDUP always rounds up.

One other point to mention is the Excel setting (in Options -> Advanced) to "Set precision as displayed". This calculates formulae based upon the displayed number of decimal places, throughout the whole workbook. In my opinion, this is a very dangerous way to address the issue. Just imagine the damage if you format a section of a worksheet to display as a whole number - not realising that there is a percentage within that range that is used in calculations. Any percentage below 50% would be evaluated as zero, and any above 50% as 100%. Far better that any rounding used in the spreadsheet is done deliberately, using the functions above.

Why not treat yourself and take a look at our Excel Expert Advanced Excel Training?

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get your free report "The 5 Excel features that you NEED to know".