Remember what it was like when you first discovered spreadsheets?

This week, I thought I'd do something a little more light-hearted than the usual Excel tips and share a video I discovered on YouTube that made me smile.

Do you remember the feeling the first time you realised what you could do with a decent spreadsheet package?

Well I think this 1983 advertisement for Lotus1-2-3 captures it nicely!




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 Tips - 5 of the best

Not Just Numbers has been going almost 5 years now and I thought it might be a good time to link to some of the most popular posts on the site. These are posts that are consistently in the top five to ten every month, although some of them were written years ago.

By far the most popular post of all time on the blog is my post on the use of the dollar sign to fix rows and columns in Excel:

EXCEL TIP: The dollar sign ($) in a formula - Fixing cell references

Another much read post is my explanation of the IF statement which is one of the most useful functions to grasp in Excel:

EXCEL TIP: The IF Statement made simple

A more recent post that is proving popular is my assorted tips on tidying up text:

EXCEL TIP: Simple tips for tidying up text in Excel

When you are dealing with imperfect data, it is often handy to be able to deal with errors that make your reports look messy, this post deals with an approach to never needing to see an error message on your reports again:

EXCEL TIP: Eliminating #DIV/0! and other errors automatically using ISERROR

Finally, this post gives a simple tip for learning any new function that you want:

EXCEL TIP: Use any Excel function in seconds

I hope there's something new to learn there for you.



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: COUNT, COUNTA and COUNTBLANK

Before we get into this week's post I'd just like to comment on how popular Mynda Treacy's Excel Dashboards course proved with readers. Unfortunately, registration for the course is now finished. Given its success, I will certainly be making it available to readers next time Mynda runs the course - just make sure that you are subscribed to the blog so that I can keep you posted.

In the meantime, you might want to take a look at Mynda's Excel Expert course which is of the same high standard.

OK, on with today's post...

I have covered COUNTIF in a previous post, but today I want to look at three much simpler functions that do a similar task in specific circumstances.

All of the functions have one argument, the range they are to apply to, i.e.

=FUNCTION(range)

NB: there can be multiple arguments, e.g.

=FUNCTION(range1,range2,range3)

or even a list of numbers, although this has less practical use.

So, let's look at each one in turn:

COUNT
This function returns the number of cells that contain numbers within the range or ranges. This does include zeroes. and the results of formulae.

COUNTA
This function returns the number of cells that are not empty within the range or ranges. This does mean anything, including a formula that returns null.

COUNTBLANK
This is essentially the reverse of COUNTA, i.e. it returns the number of empty cells. Again, a formula will not be a blank, irrespective of its result.

Example:

If we apply each of the functions to the range A1:B5 above, we get the following:

=COUNT(A1:B5), returns 4, as the range contains 4 numbers

=COUNTA(A1:B5), returns 8, as there are 8 non-empty cells

=COUNTBLANK(A1:B5), returns 2, as there are 2 blank cells

Not as flexible as COUNTIF but, I'm sure you will agree, much simpler for these specific scenarios.



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

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