From the Not Just Numbers blog:

I have said this many times before, but one of the things I like most about working with Excel is that there is always something new to learn.

I don’t just mean learning new features as they are added. I mean learning things that have always been there that you didn’t know about!

A blog post on another Excel expert’s blog taught me a really simple one the other day, that I’m not sure how I’ve missed!

Reading a post on Charley Kyd’s ExelUser blog on Excel’s Five Annuity Functions, he revealed a simple feature that I had never come across, but could be useful when entering any function in Excel.

Did you know that you can start typing any function and then press Ctrl-Shift-A for excel to fill it in with the names of the arguments, which you can then simply replace with the actual arguments.

For example, if you type:

=VLOOKUP

and press Ctrl-Shift-A, Excel puts the following into the formula bar:

=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

You can then replace these argument names with the arguments for your particular scenario.

Alternatively, you could even leave it as is and name the cells and ranges that hold the arguments with these argument names as range names.

i.e. name the cell that contains the value you want to look up, lookup_value and the range that contains your lookup table table_array, etc.

I’m not sure I would use the range naming approach, not least because you could then only use the function once in your spreadsheet!

Using Ctrl-Shift-A to give me a skeleton to enter the function arguments does sound useful though.

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

From the Not Just Numbers blog:

There has been much talk for years now among IT experts, about getting away from Excel for business reporting, while the reality has tended to move the other way.

In my view, much of the case for specialist Business Intelligence Software is more a case for structure and control in business reporting, rather than proving that we are all using the wrong software.

You can see more discussion on this in an earlier post:

http://www.notjustnumbers.co.uk/2015/07/is-excel-error-prone.html

I thought this week, that I would share with you a recent article on the website of Investech.com that gathers the views of 27 Excel experts on the subject, including a few names you might recognise, such as Chandoo, Mynda Treacy and myself. Unfortunately, you can tell by the brevity of my comments that they caught me at a busy time!
27 MICROSOFT EXCEL EXPERTS PREDICT THE FUTURE OF EXCEL IN BUSINESS INTELLIGENCE

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

From the Not Just Numbers blog:

Today, I am going to cover a quite simple function that has been touched on in previous posts but that I have never covered on its own.

Sometimes, you want to know how many items in a list meet certain criteria. As usual, Excel has a function (or two) to help.

If you simply want to count items in one column (the same column that you want to apply the criteria to), you can use the COUNTIF function.

The syntax is as follows:

=COUNTIF(Range,Criteria)

So, for example, if you had a list of sales and column A contained the sales region, you could count how many sales were in the North region, by using:

=COUNTIF(A:A,”North”)

This assumes an “equals” criteria, but you can use other operators. Say that column B contained the value of the sale, then you could return how many sales exceed £5,000, by using:

=COUNTIF(B:B,”>5000″)

But what if you want to return how many sales exceeded £5,000 in the “North” region?

Here, you can use a more recent Excel function that allows you to apply multiple criteria, COUNTIFS. This works just like COUNTIFS but allows as many pairs of Range and Criteria as you want, so:

=COUNTIFS(A:A,”North”,B:B,”>5000″)

would provide our answer.

And that’s it!

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

From the Not Just Numbers blog:

One of the things I love about Excel is that I continue to learn and find new quirks all the time.

An issue that arose while working on a spreadsheet for a client last week, caused me to find one of these such quirks.

Read on about the Curious Case of FIND and SEARCH…

I regularly use the FIND function to find text within other text, for various reasons.

The FIND function works as follows:

=FIND(text,within text,[start character])

This function returns the position (character number) of the first occurrence of “text” within “within text” – reading left to right, starting at the start character, or the beginning if omitted.

So,

=FIND(“U”,”NOTJUSTNUMBERS”) returns 5

whereas,

=FIND(“U”,”NOTJUSTNUMBERS”,7) returns 9

I had used this function as part of a larger formula in a client spreadsheet, but it needed to be case-insensitive. After a little Googling, I discovered the SEARCH function which is exactly the same as the FIND function, the only difference being that it is not case sensitive!

I was even able to use Find and Replace to swap all of my FIND functions for SEARCH functions, to stop them being case sensitive.

I might have come across this earlier if it had been called something like FINDCASEINSENSITIVE!

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

From the Not Just Numbers blog:

In an earlier post, I showed a method for pulling the text of the weekday out of a date. This is all very well if you are just wanting to pull the name of the weekday out of the date, but if you want to do any calculations using this information, then working with the day name is a bit “clunky”.

As usual, Excel offers a different approach that is more appropriate.

The WEEKDAY function pulls the day out of the date as a number from 1 to 7 or 0 to 6, with options of which weekday to start on.

The syntax of the WEEKDAY function is as follows:

=WEEKDAY(Date,[Option])

Without the optional second argument, this will return a number 1 to 7 for the weekday of the date in the first argument, with Sunday being 1. You can, however, change how it works by using any of the following as the second argument:

1 or omitted – Numbers 1 (Sunday) to 7 (Saturday).

2 – Numbers 1 (Monday) to 7 (Sunday).

3 – Numbers 0 (Monday) to 6 (Sunday).

11 – Numbers 1 (Monday) to 7 (Sunday).

12 – Numbers 1 (Tuesday) to 7 (Monday).

13 – Numbers 1 (Wednesday) to 7 (Tuesday).

14 – Numbers 1 (Thursday) to 7 (Wednesday).

15 – Numbers 1 (Friday) to 7 (Thursday).

16 – Numbers 1 (Saturday) to 7 (Friday).

17 – Numbers 1 (Sunday) to 7 (Saturday).

So, for example, we can determine whether the date in cell A1 is a weekend with a formula such as:

=IF(WEEKDAY(A1,2)>5,”Weekend”,”Work”)

If you need a refresher on the IF function, take a look at this earlier post.

By entering 2 as the second argument, Saturday and Sunday will be 6 and 7 respectively, so we can apply the criteria >5 to identify a weekend.

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

From the Not Just Numbers blog:

Apologies for the the lack of posts over the last few weeks. I intentionally took two weeks break while I was on holiday on the Isle of Skye with the family, then I have involuntarily missed the last two weeks while catching up since I got back!

I’m back now with a simple post that I have been asked about on numerous occasions.

“How can you show a number as millions or thousands? e.g. show, 3,000,000 as 3 million.”

Well, the good news is that you can do it simply with a custom number format.

There is a full description of how number formats work available here, if you wish to know a lot more about this subject, but we don’t need to understand all of that to format millions or thousands as required here.

If you right-click the cell or range that you wish to apply the number format to, and select “Custom”, you will be presented with a box to allow you to create the format. This will be pre-populated with the current number format of the cell, and you can choose to start from any other.

We’ll work from scratch with a simple format in this case, then you should be able to apply the same principles to more complicated formats.

Let’s start with a number format of

#,##0.00

This is a commonly used format, to show numbers with two decimal places and a comma separator for the thousands.

The characters mean the following:

#  –  show this character only if required (e.g. if the number was 12, it would show as 12.00, not 0,012,00)

0  –  always show this character, even if it is not significant (this example will always display two decimal places, and a zero before the decimal place if it is less than 1)

,  –  use a thousands comma separator (this works for all thousands)

In this format, 15,450,324 would appear as 15,450,324.00, but we might want it to appear as any of the following for example:

15 million

15.45 million

15,450 thousand

We can do this by use of the comma and the insertion of text. Taking the last example, we can remove everything after the comma, swap the hash for a 0 (so that we still get a zero, if there is less than a thousand) and add the text, i.e.

0,” thousand”

This, however gives us

15450 thousand

We can re-introduce the comma as a thousands separator as follows:

#,##0,” thousand” giving us 15,450 thousand

If we want to do millions, we can add a second comma on the end, i.e.

#,##0,,” million” giving us 15 million

We can also put decimal places before the commas, e.g.

#,##0.00,,” million” giving us 15.45 million.

Have a play, and a read of the Office Support article on the subject, if you want to see what else you can do.

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

From the Not Just Numbers blog:

As you may be aware from one of my earlier posts, I was attending an ICAEW event last week, marking one year since the launch of the Twenty Principles for Good Spreadsheet Practice, as I was privileged to be part of the team that worked on devising the principles.

One of the age-old accusations against Excel was addressed by Professor Ray Panko, the keynote speaker at the event. Namely that it is error-prone.

I thought it might be good to highlight his research into this accusation, as you might find it interesting reading (and a useful defence when senior managers say that “we must get rid of these spreadsheets”).

The research paper behind Professor Panko’s talk is available to read here:

What We Know About Spreadsheet Errors

If you want to see some UK-based research into Spreadsheet errors, take a look at F1F9’s paper:

Capitalism’s Dirty Secret

Professor Panko is possibly the world’s leading academic when it comes to research into spreadsheet use and it was very interesting to hear his take on this subject.

A telling quote from his talk was the wonderful:

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

From the Not Just Numbers blog:

Before I get into today’s post, just a quick reminder for those of you in London about the Twenty Principles event I mentioned in my last post.

I believe that there are still some places available for Wednesday evening, and it would be great to see you there.

I am currently working on a large project trawling through some incredibly complex spreadsheets built by someone else, and I thought that I would share with you a simple, but very valuable Excel feature that I am using a lot on this project.

Before I can make decisions about any particular cell in the spreadsheet, I need to know how it is linked to other cells.

These links go in both directions. If the cell contains a formula, I might want to know what other cells this cell is dependent on (referring to the formula will help in this case).

The contents of the cell will be of no help with the other direction, and this is the one I need to know most often, i.e. what cells are dependent on this one. This is crucial, because I can’t delete it or change it, until I know if there are any implications, and if so, what they are.

Fortunately Excel has a tool for both of these.

On the Formulas ribbon, there is a section called Formula Auditing which includes, among other things, the following three buttons:

• Trace Precedents
• Trace Dependents
• Remove Arrows

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

From the Not Just Numbers blog:

This week’s tip is a really simple one, but can make using your spreadsheet so much easier.

It is something we have used with paper files for decades, if not centuries, but is sadly not used anywhere near as much in spreadsheets. This is a shame, because it is really simple and very effective.

I’m talking about colour-coding your worksheet tabs, i.e. the little tabs along the bottom of the screen for switching between your different worksheets.

We can change the colour of a tab simply by right-clicking on the tab name and selecting Tab Color (the menus have the US spelling)., we can then select our chosen colour from the palette.

Despite how easy it is to do, used well it can make a huge difference to the usability of the spreadsheet.

By ‘used well’ I mean the following:

• Use a small number of colours to identify particular types of worksheets. For example I often use red to identify sheets that contain external data that must be refreshed, green for output reports and orange for data entry.
• Order the tabs sensibly. You can move tabs simply by clicking and dragging them. I find ordering them in typical sequence of use is useful (left to right), so in a reporting spreadsheet we might have external data sheets that need refreshing first, followed by the reports that are generated from them. If there are also data entry sheets, I would tend to include these last if they are just standing data that it is not always necessary to update, or if they need editing every time, include them at the appropriate place in the workflow. Combined with the colours and well-chosen tab names, this can make the spreadsheet quite intuitive to use.
• Keep tab names short but meaningful. The shorter they are, the more that will fit on the screen without scrolling (thanks to Jim’s comment on last week’s post for this simple tip).

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

From the Not Just Numbers blog:

This is a slightly different kind of post this week. I thought I’d share a few simple tips to help when you’re a little short of screen space.

This may be because you are working on a laptop screen, or just dealing with a huge spreadsheet that you want to see as much of as possible without scrolling.

There are a few little things you can do to make the most of what you’ve got.

Firstly, since Excel 2007, the Ribbon can take up quite a sizable percentage of the screen, particularly if you are working on a laptop.

You don’t need to have it visible all of the time.

Just click the ^ at the bottom right of the ribbon (see image below)

and it will fold away until you click on one of the menu headings. You can get it back more permanently (when you’re on a larger screen) by clicking the pin symbol that has now replaced the arrow. This setting is a personal setting that does not affect any particular spreadsheet, just how you choose to view Excel in general.

You can also zoom out to see more of the screen by using the slider at the bottom right of your screen:

By combining all of the above, you can make a lot better use of a small screen, when you need to.

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