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".

Excel Tip: Calculating elapsed time between British Men's Wimbledon Champions

I hope all of my readers in the UK are enjoying our current heatwave.

The focus here in the UK for weeks now has been whether Andy Murray could finally win the Wimbledon Men's Singles title, 77 years after the last Brit (Fred Perry) won it on 3rd July 1936. A feat Murray achieved magnificently on Sunday (7th July 2013) - well done Andy.

In a blatant attempt to be topical, I thought I would explain this week, how to calculate the elapsed time between two dates in Excel.

NB: To avoid any confusion, all dates are shown using the format most commonly used in the UK, i.e. dd/mm/yyyy.

Dates in Excel are stored as numbers, starting with 1 being 1/1/1900, 2 being 2/1/1900, etc.

The date of Sunday's final (7/7/2013) is stored in Excel as 41,462. You can see this by typing the date in Excel and changing the number format back to General.

[I don't intend to cover times in this post, but it is worth noting that where 1 is a whole day, decimals mark the time in that day. Technically 41,462 is midnight on the morning of the 7/7/2013 - 41,462.5 would be 12 noon on that day.]

Given that the date is held in Excel as a number (representing the number of days since 1/1/1900), then if cell A1 holds the date of Fred Perry's Wimbledon win (3/7/1936) and A2 holds the date of Andy Murray's win (7/7/2013), to calculate the elapsed number of days between the two we simply need to subtract one from the other, so the following formula would work:

=A2-A1

...the answer being 28,128 days.

In many applications this is what is required, e.g. days an invoice has been outstanding, however in this example years might be a more appropriate unit of measure. We can do this in one of two ways, which are subtly different:

We can deduct the year of the first date from the year of the second date, using the YEAR function to pull out the year:

=YEAR(A2)-YEAR(A1)

...returning 77, i.e. 2013-1936

This is probably the most appropriate approach for our purposes as Wimbledon is an annual competition, and we would not be concerned with the timing during the year.

An alternative approach would be to use the DATEDIF function which can calculate the total COMPLETE years that have elapsed between the two dates.

DATEDIF has the following Syntax:

=DATEDIF(Start date,End date,Interval)

where Interval, can be "d" (complete days), "m" (complete months) or "y" (complete years).

Interval can also be "ym", "yd" or "md" being complete months excluding years, complete days excluding years, or complete days excluding months (and years) respectively.

In our example we simply need:

=DATEDIF(A1,A2,"y")

...this also returns 77, however as this measures COMPLETE years, the answer would have been 76 if this year's final had been, say, 1st July. This is the reason that, for our example, I would recommend the previous approach.

Here's to hoping that the gap until the next British champion is more appropriately measured in days!

Click here for our our exclusive offer on Online 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".

Excel Tip: Multiple rows of text in one cell

Just a quick post this week.

Excel is great at handling numbers and text data fields, but what about when you need to wax lyrical and enter and hold larger sections of text.

In the data behind an invoice spreadsheet, for example, we might want to be able to enter significantly more text in the description column than anywhere else.

There are a couple of little tips worth knowing when entering larger chunks of text like this, and I thought I'd share these with you.

First of all, it's worth knowing how much text you can enter in one cell. Excel is restricted to 32,767 in a cell, which should be more than you need for most practical purposes. So how do we make the most of this?

Wrap text
The first feature worth knowing about is "Wrap text". This is a tick box accessed on the Alignment Tab when you select Format Cells (accessible (among other ways) by right-clicking on the cell and choosing Format Cells).

When this box is ticked, the text in the cell "wraps" similar to a word processor, i.e. when a word will not fit onto a line, it moves onto the next line.

The row height also flexes to fit the multiple rows of text.

Alt + Enter
Less people seem to know about this one (as is usually the case with features that are not selected from a menu). While typing within a cell, pressing Enter will finish editing that cell and move onto the next one. However, pressing Alt+Enter stays in the same cell but moves onto the next line (again like a word processor). This is great for typing a list in one cell, or entering data in paragraphs.

Once again, the row height flexes to fit the multiple rows of text.

That's it for this week, now you can go off and write a  novel in Excel!

Click here for our our exclusive offer on Online 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".