Excel Tip: Merry Christmas with a quick macro to swap one colour for another

First of all I would like to wish all of my readers and subscribers, a very Merry Christmas and a Happy 2016.

I know we all work hard, and I hope, like me, you all get a chance to spend some time with those you love in the coming days, whether Christmas is a festival you celebrate or not.

One hassle I sometimes have when building spreadsheets for others is changing colours.

The spreadsheet may work perfectly, but the end user might not like the colours I have chosen. This isn't too much of a problem if we are talking about large blocks of colour, but it can be a time-taking process, if I have used yellow to denote input cells throughout the whole worksheet, and the client wants orange, for example!

When this happened recently on a particularly complex spreadsheet, I decided to write a little macro to help, which turned out to be pretty straightforward and does the job in seconds.

Here is the code of the macro.

Sub ColourSwap() 

Dim Source As Variant
Dim R As Integer
Dim G As Integer
Dim B As Integer
Dim NewColour As Variant
Source = ActiveCell.Interior.Color
If MsgBox("Switch to no colour?", vbYesNo) = vbYes Then
NewColour = 0
For Each cell In ActiveSheet.Cells
If cell.Interior.Color = Source Then cell.Interior.ColorIndex = NewColour
Next
Else
R = InputBox("R?")
G = InputBox("G?")
B = InputBox("B?")
NewColour = RGB(R, G, B)
For Each cell In ActiveSheet.UsedRange.Cells
If cell.Interior.Color = Source Then cell.Interior.Color = NewColour
Next
End If 

End Sub

The easiest way to include it in your spreadsheet is to hit Alt-F8 and type the name you want to call the macro in the box at the top of the dialog. I have called it ColourSwap. Click create and you will see the green sections above already showing in the window (with your chosen name replacing ColourSwap). Just paste the yellow section in between.

You can run the macro by hitting Alt-F8 again, selecting it and choosing Run. If you prefer, you can click options instead of Run, and assign a shortcut key to run it in future.

The macro works as follows, and is applied to a single worksheet at a time.

1. Click on one of the cells that contain the colour that you wish to swap;
2. Run the Macro
3. You will be asked if you wish to "Switch to no colour?", if you select "Yes", then all of the cells on that worksheet that are the same colour as the cell you selected, will have any Fill Colour removed. This is the same as choosing No Fill if you were colouring the cell manually.
4. If you select "No", you will be faced with 3 prompts, requesting the R, G and B values for the colour you wish to swap to. Once you enter these, all of the cells on the worksheet that are the same colour as the cell you selected, will be filled with this new colour.

NB: The macro only checks the Used Range of the worksheet, so will not change any coloured cells below, or to the right of the last populated cell on the worksheet. This is to save time, as it can take quite a while to loop through every cell, when this is not necessary. If (for some reason) you have coloured cells outside of the Used Range, then these should be very easy to change manually, as they will almost certainly be in a large block.

Note that if you wish to save the Macro with the spreadsheet, you will need to save the Workbook as Macro-enabled Workbook, however this will usually be unnecessary, as once the colours have been changed, the macro is no longer needed, so that you can let it save without the macro.

I hope you find this useful, and could maybe use it to make some tired old spreadsheets look a bit more festive!

Merry Christmas!

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

Excel Tip: How many calendar months does a date range affect?

This week I responded to a client request for a formula that seemed straight-forward, but it took a few emails backwards and forwards to establish exactly what was required.

My answers at each stage, provide a number of alternative versions of what appear to be the same thing - but aren't!

The question itself was essentially "How do I calculate the number of months between two dates?"

My first response to this question used the DATEDIF function as featured in this earlier post.

Assuming that the start date is in cell A1 and the end date is in cell A2, then:

=DATEDIF(A1,A2,"M")

will return the number of WHOLE months between the two dates.

My client then said that they wanted to always round up the number of months.

I then used the "MD" argument of a DATEDIF to identify the remaining days after calculating the whole months, so that I could use and IF statement to add 1 if this remainder was greater than zero:

=DATEDIF(A1,A2,"M")+IF(DATEDIF(A1,A2,"MD")>0,1,0)

Finally, it transpired that what was really required was the number of calendar months touched by the date range, e.g. if the start date was 31st January 2015 and the end date was 1st February 2015, the answer should be 2, as both January and February feature in the date range (with the same dates, our first example would return zero, as there are no whole months and our second would have returned 1, as we rounded the 2 days up to a whole month).

This required a completely different approach, by using the month function to pull out the month from each date, and the year function to pull out the year. The answer would then be the difference between the month numbers, plus 1, plus 12 * the difference in the years, i.e.

=(MONTH(A2)-MONTH(A1)+1)+((YEAR(A2)-YEAR(A1))*12)

Depending upon your specific needs, any one of these formulae might be correct for your requirement!

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

Excel Tip: An introduction to dynamic ranges

A few months ago I wrote a post  introducing the use of ranges in Excel:

Excel Tip: A short introduction to named ranges

In that post I alluded to dynamic ranges but didn't really say much about them, so I thought I would expand a little on the subject now.

As the name suggests, dynamic ranges are ranges that are not fixed. The range referred to varies based upon either the cell in which the reference is used or a variable (or variables) referred to in a formula, or both.

If we go to name a new range using the Name Manager on the Formulas ribbon (if you need a refresher on this, please visit my earlier post), and in the "Refers to" box, we highlight a range, then by default this range is absolute (Excel automatically inserts the dollar signs to fix the row and column references).

So, if we type "Test" in the Name field and delete the contents of the "Refers to" box, then click in cell A1 of Sheet1 (while still in the "Refers to" box), Excel populates the "Refers to" box with:

=Sheet1!$A$1

This means that if we refer to the range "Test" anywhere else in the workbook, it will look at the contents of cell A1 of Sheet1. This is a normal (fixed) range.

Now let's create a dynamic range.

We will follow the same process to create second range, but this time, ensure that cell B1 is selected before we start. Again create a new range in the Name Manager but call it Test2 this time. Delete the contents of the "Refers to" box again, then, again, click in cell A1. This will populate the "Refers to" box, as before, with:

=Sheet1!$A$1

This time though, before clicking OK, use the F4 key to remove the dollar signs.

The "Refers to" box should now read:

=Sheet1!A1

This is now a relative reference, relative to the cell we had selected prior to defining the range (B1). The range "Test2", will now refer to the contents of the cell immediately to the left of the cell that you use it in (as A1 is the cell immediately to the left of B1).

Try typing the formula =Test in multiple cells. This will always return the contents of cell A1. But type =Test2 in any cell, and it will return the contents of the cell to its left. "Test2" is therefore a dynamic range - the cell(s) it refers to change depending on where it is used.

Note that this is not just the case with a single cell, we can refer to a number of cells.

Let's take an example. Let's say that we have a 12 month budget, with the 12 monthly values in columns B to M. Click in cell N1 and name a new range in the Name Manager called "FullYear" and type the following into the "Refers to" box:

=Sheet1!$B1:$M1

We can either type this in, or as before, highlight the cells B1 to M1, but then use the F4 key on each of the references to remove the dollar from the row reference.

Note that I have left the dollars on the column references. This means that the row will change, but the columns will stay fixed. This means that we can use the formula =SUM(FullYear) and it will always sum columns B to M of the current row.

Sticking with this same example, we can use the OFFSET function to extend this further and allow us to have dynamic ranges for the current month's balance, and the year to date balance.

If we store the number of the current month (1 to 12) in cell P1, then we can name a range called "MonthBalance" by clicing in cell N1, and creating the range as before, but typing the following formula in the "Refers to" box:

=OFFSET($B1,0,$P$1-1)

Note the use of the dollars, The column of B1 is fixed but the row is left relative, whereas P1 is entirely fixed.

This will return the contents of the cell P1-1 cells to the right of column B, on the same row. So if the month number entered in P1 is 3, P1-1 =2. As column B is the month 1 balance, 2 columns to the right in column D is the month 3 balance!

So we can enter =MonthBalance in any cell and it will return the current month's balance from that row.

We can define the range "YTD" as:

=OFFSET($B1,0,0,1,$P$1)

This will return a range P1 wide and 1 row high starting in column B of the current row, i.e. all of the months to date. As this will usually be more than one cell, you will need to use SUM to total the range.

We can now use =SUM(FullYear), =MonthBalance and =SUM(YTD), to give us these totals on any row of the sheet. In addition, MonthBalance and SUM(YTD) will change depending on the month number entered in P1.





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

Excel Tip: Return the formula text from a cell, rather than its value

Just a short little tip this week, due to a hectic diary.

When you reference a cell in Excel, you will get its value, but what if you want to return the formula itself?

Excel, as usual, provides a way to do this.

NB: This function is only available in Excel 2013 onward. If you need this functionality in earlier versions of Excel, Bill Jelen has a simple video on how to create a User-defined function to do it.

Let us say that cell A1 contains:

=SUM(B1:B10)

If we enter the following into cell C1:

=A1

then cell C1 will show the same number that A1 shows, i.e. the sum of the numbers in cells B1 to B10.

What if we wanted to see the actual formula in cell A1?

We can use the FORMULATEXT function in C1:

=FORMULATEXT(A1)

This will return the text:

=SUM(B1:B10)

This may be useful for its own sake (for example, to show how numbers are calculated alongside the numbers themselves), or to return the text of the formula, so that you can manipulate it.

A couple of things to note:

• FORMULATEXT will return #N/A if:
• the cell does not contain a formula
• the formula is more than 8,192 characters!
• worksheet protection doesn't allow the formula to be shown
• it refers to an external workbook that is not open.
• If the range argument is more than one cell, it will return the formula in the top left cell of the range.

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

Excel Tip: Show negative numbers in brackets

Working with accountants, one of the requirements I often get asked for, is to show negative numbers in brackets.

Surprisingly, this is not one of the standard number formats in Excel, not even if you choose the Accounting format!

Fortunately, however, this can be remedied using a custom number format.

It is usually easier to start with a format that is close to what you want. We will start with the Accounting format, to 2 decimal places, and no currency symbol.

To select this, right-click the cell, choose Format Cells and, on the Number Format tab, selecting Accounting. In the information to the right, select 2 decimal places and None (for the currency symbol).

Click OK to store this as the number format for that cell (or the range of cells selected).

Now follow the same sequence again, but this time choose Custom as the Number Format. You will see the code for the existing format (as previously selected) in the box at the top of the section on the right:

_-* #,##0.00_-;-* #,##0.00_-;_-* "-"??_-;_-@_-

We will edit this code to give us our brackets.

The first thing that we need to be aware of is the different sections of the code. This format uses the maximum four sections, each section being separated by semicolons.

The sections are as follows:

1. Positive Numbers
2. Negative Numbers
3. Zero
4. Text

It is not necessary to have all of these.  Any sections not included will follow the formatting set in section 1.

In this case, however, we have all four sections, but we are only interested in changing the first three - in particular Section 2 for negative numbers.

From above, we can see that the current formatting for negative numbers as follows:

-* #,##0.00_-

The first character is simply the minus sign.

This is followed by an asterisk (*) and a space. The asterisk tells Excel to repeat the character that follows it, to fill the remainder of the cell. As this is followed by a space, this tells Excel to pad out the area between the minus sign and the number with spaces, so that the whole cell is occupied. This is why the minus sign is shown on the far left in the Accounting format.

The #,##0.00 tells Excel that we want to see the number to two decimal places, with commas to mark thousands.

Finally, the _- creates a space the width of the minus sign. The underscore (_) symbol, inserts a space the width of the character that follows it.

To edit this to show our brackets, we can do the following:

• Delete the leading - sign, as the brackets will denote the negative
• Place an opening bracket immediately before the number, assuming that we want the opening bracket to be placed after the leading spaces
• Replace the _- with a closing bracket. We no longer need the minus width space at the end, but we do want a closing bracket here

The new negative format should now look like this:

* (#,##0.00)

This is not the end of the story, however. We want to also slightly tweak the positive and zero sections, so that they line up correctly with the bracketed negatives.

The positive section currently looks like this:

_-* #,##0.00_-

We need to ensure that we have a space the width of a bracket in the same places that we have the brackets in the negative number. We also don't need the space the width of a - at the start and end, as these were removed from the negative.

The new positive format looks like this:

* _(#,##0.00_)

Finally, we need to make the same changes to the zero format which currently looks like this:

_-* "-"??_-

But, applying these changes, becomes:

* _("-"??_)

So, the complete new number format is:

* _(#,##0.00_);* (#,##0.00);* _("-"??_);_-@_-

We could trim this down even further if we like.

We don't really require the leading spaces, as there is nothing to the left of them, so we could remover the Asterisk and the space at the start of the positive, negative and zero sections.

We also, don't really need the text section, as this was only adding the leading space to coincide with the minus on the negative numbers, as well as the trailing space that was against all of the numbers.

Our streamlined format now looks like this:

_(#,##0.00_);(#,##0.00);_("-"??_)

And if you don't care how we got here, you can just select Custom Format and paste the above code in!

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

Excel Tip: Display current sheetname

Sometimes it can be very handy in a spreadsheet with multiple sheets, to show the sheet name in the sheet itself.

Say you have a sheet for each salesperson and new sheets are regularly being added and you name the sheets with the salesperson's names and want the same name to appear in the heading on the sheet. This is particularly important when printing the sheets as the worksheet name will not be visible.

You could, of course, type the same name in the header as the name of the sheet, but this has a few disadvantages:

1. It's twice as much work
2. You may forget, and have a different salesperson's name on the printed reports as is being used to pay them for example. This kind of inconsistency can lead to all sorts of problems!

Fortunately, it is relatively easy to get at the sheet name in a formula.

We can use the CELL command to do most of the work. This allows you to pull information about the current cell. In this case we are going to use it to pull the file path (including the sheet name). This is done by entering "filename" as the info type argument in the CELL command as follows:

=CELL("filename")

This will show something like:

C:\Users\Glen\Documents\[MySpreadsheet.xlsx]SheetName

where MySpreadsheet.xlsx is the name of the spreadsheet and SheetName is the name of the sheet that contains the CELL function above.

As we just want the sheet name, we can use the fact that the workbook name is enclosed in square brackets as follows:

=MID(CELL("filename"),FIND("]",CELL("filename"))+1,999)

This earlier post gives more information on both the FIND and MID functions.

Essentially, this returns the 999 characters starting at the position after it finds the "]" (the +1 ensures that we don't start until the character after the "]"). I have simply used 999 to ensure that all of the characters after the "]" are returned. It will not return any extra characters, so will just return all of the characters after the "]", i.e. the sheet name!

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

Excel Tip: Make it easy to enter the arguments for a function

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

Excel and Business Intelligence

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

Excel Tip: Counting items that meet certain criteria

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

Excel Tip: The Curious Case of FIND and SEARCH

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

Excel Tip: Using the weekday of a date in a function

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

Excel Tip - Formatting numbers for thousands or millions

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

Is Excel error-prone?

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:

"Spreadsheets aren't error-prone, people are!"

Professor Raymond R. Panko, University of Hawaii

According to his research, the error rates in spreadsheets are roughly the same as in other types of activities with a similar level of complexity (such as computer programming), i.e. around 3%-5%.

The big difference is that this is recognised in software companies, whose testing processes tend to reduce this rate considerably, whereas most spreadsheet development is carried out in a much less rigorous environment. It's not that spreadsheets are error-prone, it's that the errors aren't removed by rigorous testing - i.e. it is nothing to do with the platform, but the different approaches of those carrying out the work.

To reduce the rates of errors in the finished (post-testing) solution doesn't require choosing a different platform, just adopting some of the best practices used in other types of software development.

This means applying good practice as per the Twenty Principles, and in particular, rigorously testing the solution (Principle 18), preferably by more than just the original developer, who is much less likely to spot his/her own errors.

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

Excel Tip: Find out how a cell is linked to others

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 select a cell with a formula in it, and click Trace Precedents, a number of blue arrows will appear, pointing from the cells that are referred to in the formula to the cell itself. If the formula refers to cells on another sheet, a broken arrow will come from a small box. In this case you can double-click on the arrow itself, and the references on other sheets will be listed. You can even double click on a reference in the list to go to that cell.

Even more useful is the Trace Dependents button. This works in exactly the same way, but the arrows go from your cell to the cells whose results depend on that cell. Where the cell is referenced from other sheets, the same approach (with the little boxes and broken arrows is used).

Finally, the last button simply removes the arrows from the screen.

Have a play with this. It can be very useful when you are trying to understand how a particular spreadsheet works.

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

Excel Event: 20 Principles for Good Spreadsheet Practice from the ICAEW, one year on

You may remember that last year I told you about the 20 Principles for Good Spreadsheet Practice from the Institute of Charterered Accounts in England and Wales (ICAEW).

I was part of the committee that produced the 20 principles which were launched at an event at Chartered Accountants' Hall in Central London last June.

Well, the principles have been out "in the wild" for a year now and the ICAEW are holding another event to look at how it is being used.

The event is being held at the same location on 8th July 2015 and will offer the opportunity to not only hear about practical experience of applying the principles and Microsoft's plans for Excel, but also from world-renowned researcher into spreadsheet controls, Professor Ray Panko of the University of Hawaii.

There's also a drinks reception afterwards so maybe we could meet up too.

You can find out more, and book your place at the link below:

http://www.icaew.com/events/2015/july/titfsem150708-twenty-principles-for-good-spreadsheet-practice

I hope to see you there.

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

Excel Tip: Colour-coded tabs make life much easier

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

My suggestions above are just that, play with this and you will find what suits you and the particular spreadsheet, but don't ignore this ability to add very clear visual cues to your spreadsheet.

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

Excel Tip: Make the best use of your screen space

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:

Just click the minus on the left to reduce the zoom % and see more of your screen. You can usually get away with 80% reasonably easily. Note that this change only affects the current worksheet and is saved with it.

If you are really trying to get as every pixel of space, you can hide the formula bar and the row and column letters and numbers for a particular worksheet by unticking Formula Bar and Headings on the view ribbon:

Like the zoom, this affects the current worksheet, and is saved with it.

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

Excel Tip: Nested IF Statements - Decisions dependent on other decisions

In last week's post I discussed AND and OR, as ways of combining criteria in an IF statement. These are perfect if each test that you wish to apply is independent of the others.

What about, if you wish to apply additional tests based upon the results of other tests, resulting in more than to possible outcomes?

In real life this might be something like:

"If it is sunny today, then if all of the family agree we will go for a walk, but if they don't, we will go for a picnic - however, if it is not sunny, then if there is a football match on tv, we will watch that, otherwise we will watch a film."

This kind of thing is easier than you might think, if you keep your head!

If you need a refresher on the IF statement before we start then visit my earlier post:

EXCEL TIP: The IF Statement made simple

The answer to this kind of problem is the use of something called Nested IF statements. In Excel, we can 'nest' functions inside other functions. This means using a function as an argument inside another function. To address the kind of problem above, we can nest IF functions inside of other IF functions to produce the logic that we want.

These can lead to pretty complicated-looking formulae when you are finished, but need not be complicated to build if you take them step-by-step.

For our example let us say that we define the following cells as range names:

A1 as sunny
A2 as familyagreewalk
A3 footballontv

Each cell can contain either Y or N, for Yes or No.

We could just use the cell references but it will be easier to see the logic of the formula with the defined names.

I sometimes find it useful to do one IF statement at a time, placing dummy answers where I will later place a further if function. Taking this approach we can apply the first text as follows:

=IF(sunny="Y","AAA","BBB")

This will simply return AAA if it is sunny, or BBB if it is not. We can then replace "AAA" with the test that we wish to apply if it is sunny, which is IF(familyagreewalk="Y","Walk","Picnic"), this makes our formula:

=IF(sunny="Y",IF(familyagreewalk="Y","Walk","Picnic"),"BBB")

We can the replace "BBB" with the test that we want to apply if it is not sunny, i.e. IF(footballontv="Y","Football","Film"), so our formula becomes:

=IF(sunny="Y",IF(familyagreewalk="Y","Walk","Picnic"),IF(footballontv="Y","Football","Film"))

We can keep adding nested IFs in this way. We may want to replace "Film" with a rule that determines what kind of film we will watch, for example.

Since Excel 2007, you can actually have up to 64 Nested IFs in one function, although your formula would get pretty insane well before that! Excel 2003 only allowed 7, which is already starting to get a little too complex. Usually when you have more than around 4 or 5, there is usually a better way of doing it. Lookups can be helpful, for example.

You can comfortable build up 4 or 5 if you use the approach above. Trying to go straight into writing the whole function, you can very quickly get your brackets in a knot!

Good Luck!







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

Excel Tip: Applying multiple criteria in an IF formula

This week's post mostly applies to using the IF function, so if you don't already know how to use this incredibly powerful function, I would recommend that you read this post from 2010, which is still one of my most popular posts every month:

EXCEL TIP: The IF Statement made simple

If you already know how to use the IF function but want to apply multiple criteria, read on.

If you wish to apply more than one condition to your IF statement then you need to decide how you want to combine the multiple criteria. The two main options are:

1. All conditions must be true - this uses the AND function
2. Any of the conditions can be true - this uses the OR function

So, let's say the following cells are populated as below:

A1="Red"

A2=55

A3=400

So,

=AND(A1="Red",A2>30,A3<500)

will return TRUE as all conditions individually would return TRUE, whereas

=AND(A1="Red",A2<30)

will return FALSE, because one of the conditions would return FALSE.

However,

=OR(A1="Red",A2>30,A3<500)

will return TRUE as at least one condition individually would return TRUE, but

=OR(A1="Red",A2<30)

will also return TRUE, for the same reason.

You can create combinations of the two as well, by use an AND function as one of the arguments in an OR function, or vice versa.

So, for example:

=AND(A1="Red",OR(A2=55,A3=27))

works as follows, A1 must equal "Red" AND at least one of A2=55 or A3=27 must be true.

In this case it would return TRUE, whereas

=AND(A1="Blue",OR(A2=55,A3=400))

would return FALSE because A1="Blue" isn't true.

You can actually create quite complex rules using these two functions.

These do not, however, allow you to apply different criteria based on the results of other criteria. For that you need nested IF functions, which I will cover in a future post - maybe even next week.

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

Excel Tip: Highlighting a specific row or column (even after inserting and deleting)

I was asked this question in response to an earlier post and thought it was worth sharing my response as it has many applications.

The reader asked how to highlight a specific row number (in red, say), without the highlight moving when you insert or delete rows.

This might be to highlight when you have exceeded a target on a list for example.

Say, we want to highlight row 101, this would typically be the 100th record in a list with headers.

If we simply place a red fill on row 101, this will move to row 100 if we delete a row above it, or row 102 if we insert one.

How do we get it to stay on row 101?

The answer is to use Conditional Formatting.

First of all we need to highlight the range that we wish to apply the conditional formatting to. This could be the whole sheet, but this uses unnecessary resource that might be important in a bigger spreadsheet. A better approach would be to only apply the conditional formatting to the cells that we need to.

The width of our highlighted range should be how far across that we want the highlight to go (probably to the last column that we will be using), let's say column H.

The height needs to allow for the maximum amount of deleting rows that might happen as the range will reduce when we delete rows in it (but expand when we add them). Unless we expect to do a huge amount of deleting and very little inserting, then 1,000 rows should be more than adequate.

So, let's highlight the range A1:H1000, then click Conditional Formatting on the Home Ribbon, and New Rule.

From the list of options, select "Use a formula to determine which cells to format".

In the formula box, type:

=ROW()=101

Click the Format button and choose the format for the highlighted cells, e.g. a Red Fill.

Click OK and you're done. Row 101 will be highlighted in red up to column H, and this highlighting will stay on row 101 after inserting and deleting rows!

You can do exactly the same thing with columns, using =COLUMN()=x. Note that x is a column number rather than a column letter, so if we wanted to highlight column H, it would be =COLUMN()=8.





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

Excel Tip: Converting text to a number

Sometimes the information you need is not in the format you want it in.

In Excel, this problem often manifests itself as numbers formatted as text.

This is a problem, as when you try to do any kind of calculation with the number, you will get an error.

Excel's General number format will normally format a number that looks like a number, as a number, but this might not be the case for imported data, or if the number is part of a text string.

Fortunately, Excel (as usual) has an answer.

We can use the VALUE function to convert the text to a number.

So, for example, if cell A1 contains a troublesome number that is being read as text, that you wish to multiply by 2, but =A1*2 is returning an error, then:

=VALUE(A1)*2

should solve your problem.

A more common problem is where the number may be contained within a text string.

For example if A1 contained a number prefixed by a letter then we could use text manipulation to strip out the bit of text that contains the number - then use VALUE to convert it to a number.

So, if A1 contains text such as B230, and we wish to multiply the 230 by 2, we could use:

=VALUE(RIGHT(A1,LEN(A1)-1))*2

This earlier post explains the use of RIGHT and LEN. Essentially, RIGHT(text,x) returns the x rightmost characters from text, and len(text) returns the length of text (in characters). So, RIGHT(A1,LEN(A1)-1) returns all but one of the characters in A1, leaving out the leftmost character.

So, if A1=B230, then LEN(A1)-1=3, so RIGHT(A1,3)=230. This, however, is a text string, so we use the VALUE function to convert it to a number, before we multiply it by 2!

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

Excel Tip: Average with blanks

Just a quick post this week on a quite subtle issue.

If I can do a calculation using basic functions, I tend to do that rather than worry about finding a more advanced function to do the same thing.

For example, I might work out an average using something like:

=SUM(A1:A3)/3

instead of using Excel's AVERAGE function.

An emailed question I received last week prompted me to re-think that approach.

The questioner had a problem with an average calculation, and wanted a formula to calculate an average, taking into account how many numbers were actually in the range. i.e. ignoring text and empty cells in both the numerator and the denominator.

My first instinct was to expand my existing approach. The SUM function will ignore anything that isn't a number anyway, so my numerator was fine.

The denominator would also need to ignore text and blanks too, for the average calculation to be correct, so I came up with:

=SUM(A1:A3)/COUNT(A1:A3)

NB: The COUNT function counts the number of cells containing a numeric value in the range.

This works, but a little more digging and I discovered that the AVERAGE function does this by default!

So the far simpler answer is:

=AVERAGE(A1:A3)

I think that's what's called re-inventing the wheel!



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

Excel Tip: Analysing large datasets

Before I get into this week's short post, a quick reminder that Mynda Treacy's Excel Dashboards course is closing on Thursday, so this is your last chance to register.

I have a couple of large projects on the go at the moment which are taking up a chunk of my time, so when Alex Bankoff of Udemy emailed me offering some excellent free content that I could share with you, I jumped at the chance!

David Taylor has prepared an excellent tutorial on how to use Excel to start making sense of very large datasets. His tutorial uses baby names in California since 1880, from US Social Security data.

As well as helping you to get a feel for big data, it also introduces PivotTables and PivotCharts along the way.

You can do the tutorial here, no signup required.

Have fun!




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

Excel Tip: Multiple lines of text in one cell

Before we get into this week's post, just a quick reminder that you can still get 20% off Mynda Treacy's Excel Dashboards course until Thursday, so don't dither any longer if you want to partake!

This week I want to talk about entering multiple lines of text in one cell.

I'm not talking about wrapping text which simply runs the text over multiple lines when it doesn't fit in the width of the cell (this can be enabled for any cell by selecting the cell and clicking Wrap Text on the Home ribbon). I'm talking about choosing where the line breaks are to show different bits of information on different lines (in the same cell), or to break longer text into paragraphs,

This can be particularly useful in mail-merge data, for the body of an email, for example.

There are two different scenarios where you may want to do this which need addressing slightly differently.

The first, and simplest, scenario is when you are entering the text directly into the cell.

In this case, you can simple press Alt-Enter whenever you need a line break (in the same way that you might hit Enter in a Word document). When you do this, notice that Excel has automatically switched on Wrap Text for the cell (Wrap Text on the Home ribbon will be highlighted when the cell is selected).

The second scenario is when you are generating the text using a formula. Here you need to refer to the line feed character by its code.

We can use the CHAR function to refer to a character by its character code,

CHAR(10) is the Line Feed character.

We can build up the text in our cell using the & character to join the text together.

So, say that we have the text for paragraph 1 in cell A1, and the text for paragraph 2 in cell B1, then we can join them together using & as follows:

=A1&B1

However, the text from both cells will simply by joined together, without even a space to separate them, never mind a line break.

However, we can insert the Line Feed character as follows:

=A1&CHAR(10)&B1

At first this might appear to look exactly the same, however the line feed is there and will be used in a mail merge, for example.

It will have no effect on how it is displayed in Excel, until we switch Wrap Text on for the cell (it is not automatic in this case). We can now see that a line break has been forced where we put CHAR(10).

You can use this to create quite complicated sections of text, built up from multiple paragraphs selected using IF statements and or lookups.



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

Excel Tip: Free Excel Dashboards Webinar

In place of my usual tip this week, I wanted to tell you about a free webinar that the excellent Mynda Treacy is running each day this week.

Mynda is the author of the Excel Dashboards course that many of you have already purchased and speak very highly of.

If you haven't already purchased the course, this webinar is a great chance to learn some of the tips completely free of charge.

You can choose which day and time suits you, and book your place, at the link below:

Free Excel Dashboards Webinar

Alternatively, the dashboards course itself is once again open for new delegates for a limited time, with a 20% discount if you register by Thursday 23rd April:

Register for Excel Dashboards 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".

Excel Tip: Showing the weekday for a date

I hope everyone who celebrates it has had a good Easter break. I've just got a quick post to ease you back into work this week.

Have you ever wanted to show the weekday's name in a date? Or, show the name of the weekday for any particular date. Excel makes this much easier than you might think.

You can do this using number formats.

If you right-click a cell with a date in it and select Format Cells, Number Format, then select Custom, you can design your own number format by typing into the Type: box.

A typical UK date format, might be dd/mm/yyyy. This will show the day and month as a two digit number and the year as a four digit number.

So, for example, the 7th April 2015 would be 07/04/2015.

The day, could be shown as ddd or dddd, and these work as follows:

ddd gives the three letter version of the day, e.g. Tue

whereas dddd gives the full name of the day, e.g. Tuesday

The month works similarly, so mmm would return Apr and mmmm would return April.

We could type a custom format such as:

dddd, dd mmmm yyyy

This would show the same date as:

Tuesday, 07 April 2015

Alternatively, you could simply show the day itself by using a format as:

dddd

Using this format, the cell will still hold the full date, but will just display the day, e.g. Tuesday.

You can also use this format in the TEXT function, to return the name of the day as text in another cell.

Say that the date (07/04/2015) is in cell A1, then:

=TEXT(A1,"dddd")

will return the text Tuesday.



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

Excel Tip: You don't know what you don't know - dollar signs revisited

We're all learning, all of the time, when it comes to Excel. This week, I thought I would cover a topic I've covered before, but from a different angle.

Using the dollar sign in an Excel formula is one of those simple, but powerful, things that I constantly find that people have different layers of understanding of.

Part of the problem, is that once we think we know something, we are much less likely to learn any more about it.

I see this regarding the dollar sign all of the time, and although there is nothing complicated about it, I typically see four different levels of understanding. There may even be a fifth, as I'm still learning too!

As this is such a powerful tool, you might want to check to make sure that you're not missing any of them.

Level One
"I've seen them in other people's formulae, and wondered what they did."

I would certainly recommend reading my earlier post on them, as you are missing out on a really powerful (and simple) tool.

Level Two
"I use them to fix a reference to a cell, for when I copy a formula"

Yes, you can fix a cell by using two dollar signs, e.g. $A$1, but did you know that each dollar sign has its own purpose. The one before the column fixes the column, and the one before the row fixes the row.

So $A1 will always refer to column A (and the row will change relatively), whereas A$1 will always refer to row 1 (and the column will change relatively).

Again, I would recommend reading my earlier post for a fuller explanation.

Level Three
"I use them in almost every formula to fix cells, rows or columns. I don't know what I'd do without them!"

I was at this level for years. In fact I was at this level when I wrote the blog post referred to above! Then one day, I was looking over a client's shoulder and saw the dollar signs changing at the press of a key. It was one of those "Hang on! Show me what you did there." moments.

Using the function key F4 while on a cell reference in a formula (whether it already has dollars or not), will toggle through all of the options, e.g. if the reference is A1, then repeatedly pressing F4 will have the following effect:

First press:   $A$1
Second press:   A$1
Third press:   $A1
Fourth press:   A1

Keep pressing and it will go through that loop each time.

When I discovered this, I wrote a follow-up post. Keep learning!

Level Four
"I press F4, probably more than any other key!"

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