Search Not Just Numbers

Tuesday 31 December 2013

Excel Tip: Quickly reverse the sign of a block of numbers

This week's tip is a nice simple one that I only discovered a few weeks back when a client needed to reverse the sign on a large block of entered data.

For those of you that might not get to the tip this week (believe it or not, Excel is not always at the top of everyone's priority list over the Christmas break!), I hope you have all had a great Christmas, and I wish you all a Happy and Productive 2014!

If you want to make 2014 even more productive, why not treat yourself to a year of Excel Email support for just £75 (or $112.50 if you prefer).

Festivities and shameless plug over! On with today's tip...

The client in question had a range of numbers in Excel entered as positive figures that to be consistent with other reports should have been negatives (it was the expenses section of a budget spreadsheet and all other group companies had set their budgets up with costs entered as negatives).

I was going to enter a formula in a blank area of the spreadsheet (e.g. =-A1, where the range to be reversed starts in cell A1) and copy this over a range the same size as the range in question. I could then copy this and paste as values over the original range. I then stopped myself and wondered if it could be done in one step. A quick look around in the Paste Special dialogue box confirmed it could.

When you select Paste, Paste Special you are presented with the following dialogue box:


It is the section entitled "Operation" that caught my eye. This takes the pasted number and applies the selected operation to that and the existing contents of the cell.

So all we need to do is:
  • enter -1 in a blank cell
  • copy this cell to the clipboard
  • highlight the range of cells that we want to reverse
  • click Paste Special
  • Select Multiply under Operation and click OK
Nice and simple. It also has the added advantage that it will leave any formulae intact in the destination range (this would not have been the case in my first approach).

OK, back to the celebrations, see you in 2014!


Excel Expert Course

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Tuesday 24 December 2013

Partridges, Pear Trees and Merry Christmas!

Last week's post caused a bit of a stir - and distracted a lot of people from work in the run up to Christmas!

In case you missed it, here is the post:

A Christmas Excel Challenge

I asked readers to come up with ways of using Excel to calculate how many gifts are in the Christmas song, The Twelve Days of Christmas, and, boy, did you rise to the challenge!

You can read all of the comments on the post itself, but I have highlighted below some of my favourites.

Just for clarification, there was no trick, each days gift was considered as one gift, e.g. a Partridge in a Pear Tree is one gift (not one Partridge and one Pear Tree). However, each gift (apart from the last) is received on more than one day, the Partridge in a Pear Tree being received on each of the 12 days, the two Turtle Doves being received on the each of the last 11 days, etc.

For most people (myself included) the maths skills stopped at coming up with a formula for each day's presents:

=(B1+1)/2*B1

where B1 contains the day number in question. Therefore on the 12th day 78 gifts are received.

However, Simon Thacker was the first to come up with one formula for the total number of gifts:

=B1*((B1+1)*(B1+2))/6

returning 364 total gifts over the 12 days.

This is a Tetrahedral number from Pascal's Triangle, as described in this video from Mary Pat Campbell explaining the formula above.

No doubt that this is the most efficient solution, with just one formula to calculate the answer.

This was, however, an Excel problem rather than a maths problem, so I think it is also worth mentioning a more Excel-based solution from Ray Andrews:

"I just like the simplicity of numbering 1 to12 in row 1, 12 to1 in row 2, multiplying row 1 by row 2 in row 3 and summing the result of row three in the thirteenth column"

This uses Excel's capabilities while keeping the maths really simple.

Finally I did say that I would mention quirky solutions too and my favourite is this from Mike McCormick:

=COUNTIF(range,"*Partridge*")*1+COUNTIF(range,"*Turtle*")*2+COUNTIF(range,"*French*")*3+COUNTIF(range,"*Calling*")*4+COUNTIF(range,"*Golden*")*5+COUNTIF(range,"*Geese*")*6+COUNTIF(range,"*Swans*")*7+COUNTIF(range,"*Maids*")*8+COUNTIF(range,"*Ladies*")*9+COUNTIF(range,"*Lords*")*10+COUNTIF(range,"*Pipers*")*11+COUNTIF(range,"*Drummers*")*12

Where range contains the lyrics. of the song!

Thanks for all of your contributions.

Have a wonderful Christmas and a fantastic New Year!

Glen


Excel Expert Course

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Tuesday 17 December 2013

A Christmas Excel Challenge

Just a short, but seasonal, post this week - prompted by a quiz question my daughter was asked at school.

The question was, "How many gifts were received, in total, in the Christmas song The Twelve Days of Christmas?"

I thought that would make for an interesting Excel challenge.

So, the challenge is this:

Use Excel to answer the question, "How many gifts were received, in total, in the Christmas song The Twelve Days of Christmas?"

Explain in the comments how you would do this, remember though that it would be good practice to treat the 12 as a variable - so that you could enter any number of days of Christmas and the spreadsheet would calculate the total number of gifts.

In case you don't know the song, here are the lyrics:

On the first day of Christmas
my true love sent to me:
A Partridge in a Pear Tree

On the second day of Christmas
my true love sent to me:
Two Turtle Doves
and a Partridge in a Pear Tree

On the third day of Christmas
my true love sent to me:
Three French Hens
Two Turtle Doves
and a Partridge in a Pear Tree

On the fourth day of Christmas
my true love sent to me:
Four Calling Birds
Three French Hens
Two Turtle Doves
and a Partridge in a Pear Tree

On the fifth day of Christmas
my true love sent to me:
Five Golden Rings
Four Calling Birds
Three French Hens
Two Turtle Doves
and a Partridge in a Pear Tree

On the sixth day of Christmas
my true love sent to me:
Six Geese a Laying
Five Golden Rings
Four Calling Birds
Three French Hens
Two Turtle Doves
and a Partridge in a Pear Tree

On the seventh day of Christmas
my true love sent to me:
Seven Swans a Swimming
Six Geese a Laying
Five Golden Rings
Four Calling Birds
Three French Hens
Two Turtle Doves
and a Partridge in a Pear Tree

On the eighth day of Christmas
my true love sent to me:
Eight Maids a Milking
Seven Swans a Swimming
Six Geese a Laying
Five Golden Rings
Four Calling Birds
Three French Hens
Two Turtle Doves
and a Partridge in a Pear Tree

On the ninth day of Christmas
my true love sent to me:
Nine Ladies Dancing
Eight Maids a Milking
Seven Swans a Swimming
Six Geese a Laying
Five Golden Rings
Four Calling Birds
Three French Hens
Two Turtle Doves
and a Partridge in a Pear Tree

On the tenth day of Christmas
my true love sent to me:
Ten Lords a Leaping
Nine Ladies Dancing
Eight Maids a Milking
Seven Swans a Swimming
Six Geese a Laying
Five Golden Rings
Four Calling Birds
Three French Hens
Two Turtle Doves
and a Partridge in a Pear Tree

On the eleventh day of Christmas
my true love sent to me:
Eleven Pipers Piping
Ten Lords a Leaping
Nine Ladies Dancing
Eight Maids a Milking
Seven Swans a Swimming
Six Geese a Laying
Five Golden Rings
Four Calling Birds
Three French Hens
Two Turtle Doves
and a Partridge in a Pear Tree

On the twelfth day of Christmas
my true love sent to me:
12 Drummers Drumming
Eleven Pipers Piping
Ten Lords a Leaping
Nine Ladies Dancing
Eight Maids a Milking
Seven Swans a Swimming
Six Geese a Laying
Five Golden Rings
Four Calling Birds
Three French Hens
Two Turtle Doves
and a Partridge in a Pear Tree

Good luck. Post your approaches in the comments, and I will feature the slickest and/or the quirkiest solutions in next week's post, along with my own solution.


Excel Expert Course

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Tuesday 10 December 2013

Excel Tip: Show titles on every page of a report

Before we get into today's post, I just want to give you a quick update on an earlier post.

A couple of month's back I mentioned that I was writing a publication for the Institute of Chartered Accountants in England and Wales (ICAEW) on automating management accounts in Excel. A number of you asked to know when the publication would be available.

Well, it is now. Members of the ICAEW IT Faculty or Excel Community have already received a hard copy in the post.

At the following link you can either purchase a copy (from the ICAEW) or join the ICAEW Excel Community and then download it free if charge:

Automating management accounts production in Excel: a simple approach

OK, on with today's post. Many Excel spreadsheets are written with the intention of the output being printed. This sometimes causes a challenge when the spreadsheet spans multiple pages and we want the titles to appear on every page. Well, Excel has a solution (CLUE: It's not to break the sheet up into pages and enter the titles at the top of each page).

On the Page Layout ribbon, click on the Print Titles icon.


(In earlier versions of Excel, you can get to the same screen by choosing File, Page Setup and selecting the Sheet tab).

You will see the following window:


In the Print titles section, you can specify the rows you want to repeat at the top of every page by clicking in the "Rows to repeat at top:" box and selecting the rows that include your titles.

As you can see there is also a box to allow you to repeat columns if your print will straddle multiple pages horizontally. This is much less common, but can sometimes be useful.

Note that neither setting changes the spreadsheet that you see on the screen - just what is printed.

Excel Expert Course

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Tuesday 3 December 2013

Painting Yoda in Excel!

Obviously we all know how useful Excel is as a tool for work, but every now and again I come across a spectacular and unusual use of Excel, that I want to share with you.

Back in June, I posted some examples of the Excel art of Tatsuo Horiuchi.

This week I came across another amazing Excel artist by the name of Shukei. You can see some of Shukei's work on the YouTube channel Shukeiart.

In the video below, you can see the whole process of creating an Excel painting of Yoda from Star Wars condensed into under three minutes!




Now that makes using Excel to create next year's budget look pretty tame!

May the force be with you!

Excel Expert Course

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Tuesday 26 November 2013

Excel Tip: Making the same change to multiple worksheets

Sometimes we build an Excel spreadsheet that has a number of worksheets (or tabs) that are the same layout.

No matter how well we have designed our spreadsheet, there are times when we need to make updates. This can be further improvements or changes to reflect changing requirements.

This can be a time-taking task if the changes are to be made to multiple worksheets. Fortunately, Excel has a way to do this.

First of all, I think it is worth mentioning that some of the reasons we have multiple worksheets are due to bad spreadsheet design. For example, having a sheet for each month is not usually the best way to handle most applications. In most situations it is far better to have a sheet that contains all of the data and a report sheet that allows you to report that data for any month. For my advice on laying out data, take a look at this earlier post.

Assuming that you have decided that you do need multiple sheets (or that you already have them and are not going to spend your time changing a spreadsheet that works!), then you can make Excel apply the changes to each of the sheets that you want it to.

First of all, click on the tab name of the first of the sheets that you want to edit (as you normally would to make that the active sheet). Then, hold down the Ctrl key while you select the other sheets you want to edit too.

You should now see that the backgrounds of the tab names of the selected sheets are all white.

While these multiple sheets are selected in this way, any change you make to one of the sheets will be applied to them all.

The really important thing to remember though, is what you need to do when you've made the changes:

Right-click on the tab name of one of the selected sheets and click "Ungroup Sheets"

If you don't do this, then you can imagine the chaos you can cause when you type anything into one of the sheets and it changes them all!

This can be really useful, as long as you remember that last step!

Excel Expert Course

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Tuesday 19 November 2013

Twenty Principles for Excel Good Practice (from the ICAEW)

You may remember that back in May, I told you about some work I was doing as part of the ICAEW IT Faculty's Excel Community Advisory Committee. The other committee members and I have been working towards a set of Excel Principles to be published by the ICAEW (the Chartered Accountancy body for England and Wales),

In my post back in May, I posted my initial contribution. This was then merged with the contributions of the rest of the committee. Over the past few months these have been argued about, compromised, honed and, in no small part due to the diplomatic skills of Paul Booth of the IT Faculty, improved, to arrive at a set of Twenty Principles that the whole committee can get behind.

This process has now reached the stage where the principles can be released into the wild, to get the views of the wider Excel world.

I have reproduced the headline principles, as they currently stand, below, but I would strongly recommend that you visit the ICAEW's IT Counts website, where you can download the full document, including explanations and examples illustrating each principle, as well as join in the debate.

THE TWENTY PRINCIPLES IN BRIEF 

The spreadsheet’s business environment 

1. Determine what role spreadsheets play in your business, and plan your
spreadsheet standards and processes accordingly.

2. Adopt a standard for your organisation and stick to it.

3. Ensure that everyone involved in the creation or use of spreadsheets has an
appropriate level of knowledge and competence.

4. Work collaboratively, share ownership, peer review.

Designing and building your spreadsheet

5. Before starting, satisfy yourself that a spreadsheet is the appropriate tool for
the job.

6. Identify the audience. If a spreadsheet is intended to be understood and used
by others, the design should facilitate this.

7. Include an ‘About’ or ‘Welcome’ sheet to document the spreadsheet.

8. Design for longevity.

9. Focus on the required outputs.

10. Separate and clearly identify inputs, workings and outputs.

11. Be consistent in structure.

12. Be consistent in the use of formulae.

13. Keep formulae short and simple.

14. Never embed in a formula anything that might change or need to be changed.

15. Perform a calculation once and then refer back to that calculation.

16. Avoid using advanced features where simpler features could achieve the
same result.

Spreadsheet risks and controls 

17. Have a system of backup and version control, which should be applied
consistently within an organisation.

18. Rigorously test the workbook.

19. Build in checks, controls and alerts from the outset and during the course of
spreadsheet design.

20. Protect parts of the workbook that are not supposed to be changed by users.



Excel Expert Course

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Tuesday 12 November 2013

Excel Tip: Switch columns and rows

We often use Excel to analyse data from elsewhere. This might be from another Excel spreadsheet, or a text file exported from another software tool - maybe your accounts package.

We all know that Excel is brilliant at this, but the job can be made significantly more difficult when the data we want to analyse is not in the best format for us to work with.

Earlier this year, I wrote a post on tidying up text in Excel. This can help sort out the format of individual fields to get them the way you want them, but what if the layout of the data itself not what you require.

If you are not sure how you want your data laid out to make it easy to analyse, this post might help.

One frustrating layout issue is when you are presented with data that is in rows where columns would be more useful, or vice versa - fortunately Excel has a simple solution.

Say we have some data that we want to paste into our spreadsheet as follows:


This data would be easier to analyse if we had one column per field, i.e. two columns of data rather than two rows.

All we have to do is copy the data as normal but, when we paste it into our spreadsheet, click Paste Special, or Ctrl-Alt-V and we are presented with the following dialog box which allows us to make various changes to the data as we paste it (I will look at some of the others in future posts).



In this case we want to tick the Transpose box. This flips the data round, switching rows for columns.

When we then click OK, our pasted data looks like this:



Exactly as we wanted it.

Excel Expert Course

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Tuesday 5 November 2013

Excel Tip: Removing all spaces from text

Before we get into today's tip, I'm aware that we have a lot of new visitors and subscribers and I thought it might be a good time to remind you of my needaspreadsheet.com service.

If you are reading this blog, then you probably use Excel in your job, and my posts are intended to help you to get the most out of this excellent tool. However, sometimes you might not have the time or the skills to get it to do what you want.

When that happens, you just need to enter what you need your spreadsheet to do in the form on the site, and I will send you a fixed price quote to build (or fix) your spreadsheet for you. The whole service is handled by email and as a result, your location really doesn't matter - we have regular clients all over the globe.

Anyway, enough about me - on with today's tip...

In an earlier post on tidying up text, I introduced the TRIM function, that removes all leading and trailing spaces from text, as well as ensuring that any spaces in the text are reduced to single spaces.

Whereas this is useful for many applications to tidy up data, sometimes we need to remove all spaces from text. This happened to me the other day with some client data from their bank account. The only way to ensure consistency of the data was to remove all spaces.

Thankfully, Excel has a function that can help with this, as well as a number of other issues we might face when manipulating text data.

The function we can use is SUBSTITUTE. This replaces all references to one string of text in a cell with another string of text.

Its syntax is:

=SUBSTITUTE(Text to apply the formula to,Old text,New text,[Instance number])

Text to apply the formula to - this can be text in inverted commas, but is usually a cell reference containing the text

Old text - the text that you wish to see replaced, this can be text (in inverted commas) or a reference to text

New text - the text that you wish to see Old text replaced with, this can be text (in inverted commas) or a reference to text

Instance number - this argument is optional (as indicated by the square brackets) and specifies which instance of Old text you wish to replace (1 for the first instance, 2 for the second, etc.). If this argument is not entered, the function replaces all instances of Old text.

We can use this function to remove all spaces from the text in cell A1, by entering the following in cell B1:

=SUBSTITUTE(A1," ","")

Note that the second argument is a space in inverted commas, whereas the third argument has no space.

B1 will now be the same as A1, but we will have replaced all instances of a space with nothing - i.e. removing all of the spaces.

It's as simple as that.

Excel Expert Course

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Tuesday 29 October 2013

Excel Tip: Finding the maximum or minimum value in a list

Before we crack on with this week's post, just a quick little note about Mynda Treacy's Excel Dashboards course.

I know that many of you have purchased the course and taken advantage of the early bird discount. As when we offered the course last time, the course has been very popular and the feedback has been excellent.

If you haven't already done so, you can still get the course until 30th October and, although you have missed the discount, the course still represents excellent value and I will still throw in my Introduction to Pivot Tables course free of charge.

You can get the course here.

OK, on with this week's tip. We all know how to sum a list of numbers, but Excel offers a number of other functions for analysing a list, and today I want to introduce two of them that enable you to find the maximum or minimum value in a list.

This could be useful for all sorts of reasons - finding the maximum sales in any particular month, or the lowest score achieved in a test, for example.

Excel offers a simple way of doing this with the MAX and MIN functions.

Both functions have the following syntax:

=MAX(number1,number2,etc.) you can have as many numbers as you like in this list.

So, for example:

=MAX(23,45,12,65,21)  will return 65

and

=MIN(23,45,12,65,21)  will return 12

These numbers can be cell references, or ranges of cells - and this is usually of far more practical use.

So, say the cells A1 to A5 contain the numbers 23,45,12,65 and 21, then:

=MAX(A1:A5)  will return 65

and

=MIN(A1:A5)   will return 12

And that's really all you need to know.

Excel Expert Course

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Tuesday 22 October 2013

Excel Tip: Centre titles without merging

Before we get into today's post, just a little reminder that the 20% discount offer on Mynda Treacy's Excel Dashboards course expires on Thursday. So if you've been thinking about it, now's the time to do something about it, if you want to save some money.

Today's post is a follow-up to an earlier post, "Do you really need to merge those cells?" where I highlighted the dangers of merging cells.

It became clear in some of the comments on forums that I had highlighted the problems but had been less than clear on alternatives.

This post is intended to address that.

Just to recap, the problems highlighted in that earlier post were:

  • Data containing merged cells can not be treated like a normal data table - meaning that we can't use all of the tools that we might want to use for referring to a properly formatted data table, such as pivot tables, SUMIF, etc;
  • Copying and pasting ranges is restricted to those with cells merged in the same way;
  • Fill down doesn't work if any of the cells in the range to be filled are merged;
  • Even if we unmerge all of the cells, this rarely solves the problem, as this action will assume that the merged cell contents should be placed in the top left cell of the unmerged range - which may not be where you want them to be. Also, having done this, it is often not clear where your data is, as the labels may now be in a completely different place.
Now, by far the most common reason cells tend to be merged is when we are looking to centre titles across multiple columns.

This is usually done be selecting the range of cells in the title row that we wish to centre the text across and clicking Merge & Center.

Fortunately, Excel provides a very simple alternative to Merge & Center, but instead of placing a button for it prominently on the Home ribbon (Merge & Center is slap-bang in the middle of the Home ribbon), you need to go into the Alignment tab of the Format Cells dialogue box.

You can access this by either clicking the little expansion arrow of the alignment section, just under Merge & Center, or by right-clicking the selected cells, selecting Format Cells and then the Alignment tab.

However you get there - once you are on the Alignment tab, the first drop-down box under text alignment is labelled Horizontal. There is an option on this drop-down of Center Across Selection. Visually, this does exactly the same as Merge & Center but crucially, it does not merge the cells.

It still leaves the content in the leftmost cell, but all of the other cells remain intact.

One point to note, however, is that this is only possible horizontally, i.e. across the cells, unlike Merge & Center which will work vertically too. However the horizontal centring is far more common and this approach provides one less reason to merge cells.


Excel Expert Course

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Tuesday 15 October 2013

Excel Tip: Dashboards and Charts

Mynda Treacy's excellent Excel Dashboards online course is once again open for registration and once again there is an excellent offer for those of you who don't hang about - more about that later in this post.

Back in July, Mynda wrote a guest post for me on the subject of Excel Dashboards, which you can read here. This is becoming a very important skill and is often now required by employers. Mynda's post is a really good introduction and if, for whatever reason, you do not take up the offer on the training course, I would recommend that you at least read Mynda's guest post.

Charts

To celebrate the re-opening of Mynda's course, I have arranged to offer her eBook, 30 Chants for Better Charts, absolutely free to subscribers.

Many of us use charts regularly in our work, but few of us do it well, and Mynda's eBook provides some excellent tips on how best to format charts so that they look professional and convey the information that they are supposed to, clearly.

If you're not already a subscriber to Not Just Numbers, simply enter your name and email address in the box at the top right of the blog and you will receive Mynda's eBook, as well as other freebies, and updates on new posts to the blog.

Excel Dashboards

The Excel Dashboards course is video based and available online 24/7. It comes with comprehensive Excel workbooks and several sample dashboards to keep. There’s also an option to download the videos, plus Mynda personally provides support for the first 6 weeks of the 12 month membership.

Dashboards are an incredibly valuable tool in today's market for consultants, analysts and managers, but Excel doesn't make it straightforward to build highly professional and interactive dashboards. That's why this type of training is crucial.

What people are saying about the course:

The previous classes have been a huge hit with many people saying how they love the cool techniques and how they've been able to impress their colleagues and clients by using them in all sorts of reports, not just dashboards. 

Others have said the course has prompted them to take a whole new approach to producing their monthly reports. 

I highly recommend the  course but don't take my word for it. You can read comments from past students and find out more here.

Bonus 20% Off

If you join the class by 24th October you can get it for 20% off plus I'll include my Introduction to Pivot Tables course absolutely free, just email me your receipt and I'll send you my bonus.

So, do yourself a favour and check out the course . The price is incredibly fair, the course is awesome and it'll transform your Excel reports and possibly even your Excel career.

Learn Excel Dashboard Course

Disclosure: I make a small commission for students who join Mynda's course, but as you know I don't just recommend anything and everything. It has to be of outstanding quality and value, and something I can genuinely recommend. After all, if doesn't live up to what I've promised you'll think poorly of me too and I don't want that. Oh, and just watching the course videos won't transform your career, you have to actually put it into practice, but then you know that. 

Excel Expert Course

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

Tuesday 8 October 2013

Excel Tip: Don't do this with your table headings!

Another quick tip this week. This time it is a little bugbear of mine that I often see done by people not realising the problems it can cause them later.

It is really simple to get right if you know you need to!

See if you can guess what it is from the image. If you can't, I would recommend that you read on.

The problem is entering multi-row titles on any table of data.

I am not just talking about the table feature in Excel, but any table of data, such as the one in the picture.

In an earlier post I wrote about how to lay out data to make it useful.

The table format above fits all of these requirements, but falls down on the headings. Simply because you have used more than one line for the heading, it won't work properly if you decide you want to use AutoFilter, or a PivotTable!

This is really simple to avoid.

You can type the heading on one row and then apply word wrap to the cells on that row (select "Wrap Text" from the Home ribbon). If you want specific line breaks in the text, you can use Alt+Enter to insert a line break within the cell.

The data then fits all of the criteria to be used in a PivotTable, or to enable AutoFilter.

That's it - I'll get off my soap box now!

Don't forget that Mynda Treacy's Excel dashboards course will be available again soon for a short period of time. If you're not subscribed to the blog, do so now (at the top right of the blog) so that I can let you know when it's available - and to be notified of future Excel Tips.

Excel Expert Course

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

Tuesday 1 October 2013

Excel Tip: The power of NOW - Using the current time and date in Excel

Just a short post today because I have a very tight deadline to meet on a publication I am writing for the ICAEW (The Institute of Chartered Accountants here in England) on Automating Management Accounts in Excel.

Before we get into the post though, I have some exciting news for those of you who missed out on Mynda Treacy's Excel Dashboards course last time around. The course will be made available again for a limited time later this month. In the meantime, you can (re)read Mynda's guest post on the subject of Excel Dashboards here.

If you want to make sure you don't miss out this time, ensure that you are subscribed to the blog as I will keep subscribers updated. If you are not a subscriber, you can do so by leaving your email address in the form at the top right of the blog.

Today's post is a quick tip on how to use the current time and date in Excel.

There are two very similar functions that Excel provides for this - NOW and TODAY.

Both functions have no arguments but must still be followed by the (empty) brackets.

=NOW() returns the current date and time

=TODAY() returns the current date (actually it returns midnight at today's date)

I covered how Excel handles dates and time in a post a couple of weeks ago. Looking at the functions in those terms - NOW returns the full serial number for the current date and time and TODAY returns that serial number rounded down to the nearest whole number,

This functions can be used on their own, or as values in formulae, e.g using =TODAY()-A1 to calculate the elapsed time since the date in A1.

One caution to add to using these functions is that the values only update when Excel recalculates. Assuming you have the default calculation settings in Excel, recalculation will occur when any value changes in the spreadsheet, and when the spreadsheet is opened.

In most practical applications this is not a problem for the TODAY function unless the spreadsheet is open and unedited overnight. Obviously, it can be more of an issue with the NOW function.

You can, however, use the F9 key to force a recalculation at any point.

Right, back to Automating Management Accounts!

Excel Expert Course

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

Tuesday 24 September 2013

Excel Tip: Case Sensitive VLOOKUP

When I get stuck on Excel, I usually find myself Googling the forums for an answer, as was the case last week, when I needed a case-sensitive VLOOKUP for a job I was doing for a client.

The Microsoft site suggests a solution that doesn't really work! It only checks that the answer found is the right case. This is no use if your data has the same text in different cases (as my client's did).

However, I did find an ingenious solution, which I thought I would share with you - as well as explain how it works.

I found my answer by DonkeyOte on this Excel Forum discussion.

DonkeyOte's answer was:

"There are a few approaches - assume lookup values in A with values to be returned in B ... criteria is in C1 with result formula in D1:

D1: =INDEX(B1:B100,MATCH(TRUE,INDEX(EXACT(A1:A100,C1),0),0))"

If you are not interested in how it works, you can just use it as is and just replace as follows:

A1:A100 with the column (or row) range you want to lookup in
C1 with the lookup value
B1:B100 with the column (or row) range containing the results

(Use row ranges if you want to replicate an HLOOKUP)

If you want to know how it works, read on...

As you can see, this uses three separate functions - one of them twice.

I have covered using MATCH and INDEX together to create a VLOOKUP before.

Here is my introduction of these two functions from that post:

"INDEX is a function for returning a cell or range from within an array. At its simplest level this is done by referring to the cell by its row and column number (INDEX can do quite a bit more than this and also has another form which allows you to look at multiple ranges, however we only need to use its simple form here - I may do a post on some of its more advanced features at a later date). The simple form of INDEX is as follows:

=INDEX(range,row,column)

Column can be omitted and, if so, it is assumed to be 1 - unless range is just a single column in which case Excel will assume that the omitted argument is the row.

So for example:

=INDEX(A1:D5,2,3) returns the value in C2

MATCH finds the position of a value in a single row or column range. Its syntax is:

=MATCH(lookup value,range,match type)

match type is optional and has the following three possible values:

1 (or omitted)  - finds the position of the largest value that is less than or equal to lookup value and requires the range to be in ascending order (this works the same way as using TRUE for the 4th argument in a VLOOKUP).

-1  - finds the position of the smallest value that is greater than or equal to lookup value and requires the range to be in descending order.

0  - finds the position of the first value that is exactly equal to lookup value (this works the same way as using FALSE for the 4th argument of VLOOKUP). In this case, the range can be in any order."


DonkeyOte's formula uses the principles from my earlier post, but uses the EXACT funtion to deal with the case in a very clever way.

EXACT compares two values and returns TRUE if they are exactly the same (case as well), and FALSE if they are not. Our formula compares a range to our lookup value using EXACT.

EXACT(A1:A100,C1) will return a series of 100 TRUEs and FALSEs, the position of the first TRUE, being the position of the row that agrees to C1, our lookup value.

We can use INDEX to convert that to an array, as a column number of 0 will return the whole column as an array. So,

INDEX(EXACT(A1:A100,C1),0) returns the 100 TRUEs and FALSEs as an array

We can then use MATCH to determine the position of the first TRUE so,

MATCH(TRUE,INDEX(EXACT(A1:A100,C1),0),0) returns the position of the first TRUE

Finally, we use INDEX to return our result, being the contents of the same row in column B,

INDEX(B1:B100,MATCH(TRUE,INDEX(EXACT(A1:A100,C1),0),0))

Very clever, I thought!


Excel Expert Course

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

Tuesday 17 September 2013

Excel Tip: How Excel handles dates and time

My wife did the Great North Run half marathon on Sunday in 2 hrs 42 minutes and 23 seconds, almost the same time as she took last time she did it when she was 17, 25 years ago. Well done to everyone who did the Great North Run yesterday, I'm definitely doing it next year - once I've lost a few stone!

I mention this partly because I am one proud husband today, partly because she has raised nearly £500 for a cause very close to our hearts, but mostly because this gave me the inspiration for today's post.

I've never really done a post specifically on the interesting way that Excel handles time.

I've touched on it in other posts, but not specifically covered it, so let's do it now.

Let us start with the dawn of time. Now I realise that in parts of the US in particular this can be a contentious subject, but for Excel it is quite simple.

Time begins on 1st January 1900!

Excel cannot handle dates before this, and all subsequent dates are measured from this.

A date in Excel is stored as a number. 1 being the 1st January 1900, 2 being the 2nd January 1900, etc. Today's date in Excel terms (17th September 2013) is 41,534.

You may have noticed that zeros formatted as dates in Excel appear as 00/01/1900, obviously depending on the format chosen (I have used the conventional UK format dd/mm/yyyy). This is the day before the dawn of time, the 0th of January 1900.

Following this convention of 1 representing a day, times are represented as fractions of a day, so 0.5 is 12 noon and 41,534.5 is 12 noon today.

Every time you see a time or date in Excel, it is a serial number on this scale. What you see is determined by the format applied to the cell. If you change the cell format to Number, you can see the serial number behind the date or time.

I won't go into number formats in detail here (I'll cover that in a future post), but 41,534.5 could appear as any of these for example:

17/09/2013 (dd/mm/yyyy)
17 September 2013 (dd/mmmm/yyyy)
17-Sep-2013 (dd-mmm-yyyy)
12:00:00 (hh:mm:ss)
17/06/2013 12:00:00 (dd/mm/yyyy hh:mm:ss)

The benefit of this serial number approach is that you can apply normal formulae to dates and times. For example you can deduct one time or date from another to establish the elapsed time between them.

Hopefully that gives you an idea of what is underlying dates and times in Excel.

I hope you find it useful.

Excel Expert Course

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

Tuesday 10 September 2013

Excel Tip: Don't do that! How to avoid catastrophes

Stop
I received an email from a reader, Nikki, just the other day who has started her own blog.

Nikki blog covers her thoughts on many (non-Excel) subjects but the post she brought to my attention was a cautionary tale about an Excel mishap that recently befell her.

This got me to thinking that it might be useful to look at what things we should avoid in Excel.

I am not talking about good and bad practice here, I am talking about the things that can lead you to irretrievably screw up your spreadsheet! Those that throw all of your formulae out, or better still, cause that wrench in the pit of your stomach, the second you press the Enter key.

I'll start with Nikki's, throw in a few of my own, and then throw it open to the floor in the comments.

Nikki's nightmare was caused by forgetting that she had multiple sheets selected, causing her to overwrite everything she had been working on in the other sheets.

You can read Nikki's full nightmare here, it's not for the faint-hearted!

One of my nightmares that I have written about before is the dangers of cutting and pasting, or dragging and dropping.

For more detail on this one, have a look at my full post on it.

In short, cut and paste, or drag and drop, alter any formulae that refer to the cells being moved. This can mess up your whole spreadsheet, if that's not what you intended. It can be very difficult to pick out what has happened too.

Another one to be wary of is compatibility issues. If you have pivot tables in an Excel 2003 workbook and decide to save this as a .xlsx file in a later version of Excel - don't expect the pivot tables to work if you then try to save it as a .xls file again.

And the last one from me - don't add protection to a workbook or worksheet and apply a password, without making sure you have made a note of it somewhere you will find it next time you need it. It sounds obvious, but we've all done it!

I'm sure you've all got your own, so don't be shy. Please share them in the comments, so that your pain has not been in vain and it can help others to avoid the same fate.

Excel Expert Course

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

Tuesday 3 September 2013

Excel Tips - Quickly adding up numbers without formulae

This week, I've got one of those really simple tips, that are just that - if you already know them.

It is these kinds of tips that tend to get the most interesting reactions when someone sees them for the first time, and realise they have been doing something the long way round for so long.

A couple of other examples from the blog were:

A really quick way to copy down a formula

and

A quick way to add dollar signs in a formula

This week's quick tip tells you how to add up any numbers on the screen without having to type in a formula.

There are many times, when looking at numbers on a spreadsheet, that we want to quickly add a few of those numbers, just to check something (we may be trying to spot which numbers make up a difference that we are trying to find, for example). We could, of course, find an empty cell and enter a formula in the cell, using either SUM to add up a range of adjoining cells, or a simple formula in the form =A1+B3+A4+C7.

Excel, does however provide a simple tool for doing this that you might not have noticed (I've certainly worked with many experienced users that hadn't).

If you highlight any cells with numbers in them, Excel presents their sum on the status bar in the bottom right corner below the spreadsheet:

You can highlight them in any of the usual ways:
  • by using the mouse to select a range of adjacent cells, which can include multiple columns or rows;
  • clicking on the column letters or row numbers to highlight whole columns or rows;
  • highlighting multiple cells or ranges, by holding the Ctrl key while you click on individual cells or select ranges as described above.
In Excel 2007 onwards, you also see a count of the cells with values in, and an average. Note that the count also includes cells with text in - not just numbers. The average, however, only averages the cells with numbers in - as you would want.

This sum (and average and count) appears every time you highlight cells (that include numbers) - you don't need to do anything else.


Excel Expert Course

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

Tuesday 27 August 2013

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!


Excel Expert Course

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

Tuesday 20 August 2013

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.

Excel Expert Course

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

Tuesday 13 August 2013

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.

Excel Expert Course

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