Profile cover photo
Profile photo
AuditExcel Advanced Excel and Financial Model Training and Consulting
Advanced Excel & Financial Model training and consulting
Advanced Excel & Financial Model training and consulting


Post has attachment
Add a comment...

Post has attachment
New post (Green triangle in excel cell- understand, control, remove) has been published

If you work with Excel regularly, you may notice a green triangle in the excel cell and wonder what it does, if you can control it and how to remove it.

The simple answer is that it is an error checking feature in Excel. Whenever you see the green triangles it means that Excel has found something in the cell that could be a risk.

In order to see what it is indicating you need to click in the cell and click on the exclamation mark (!) button that appears. In the case below you will see that the first words are 'Inconsistent Formula' which is Excel's way of telling you that something is different in this cell compared to the cells around it.

You will see you have a number of options after that which you can use. In the case of inconsistent formula the options are:

Copy Formula from the Left- if clicked it does a copy paste from the cell on the left
Help on this error- Goes into the help files and explains the error
Ignore Error- Ignores the error (and removes the green triangle from that cell only)
Edit in formula bar- takes you to the formula bar so that you can make a change
Error Checking Options- takes you into the Error checking options where you can control the green triangles and what they highlight, or switch them off completely.

Green Triangle Options
We will look at some of the uses for this later, but if you click on the 5th item you go straight into the Excel options under the Formula tab.

The part we are looking at is the Error Checking section in the red box below

First you will see that you can 'Enable background error checking' or not. If you want to remove the green triangles in excel cells then you need to come here and switch it off. You will also see that you can change the default colour from green to whatever you want.

Below that you have the error checking rules.

This is where you can control when the green triangles should appear in the Excel cells.

Below an overview of what they do and the ones that we think are useful to leave on:

Cells containing formulas that result in an error-NO- tells you that there is a #VALUE or #DIV error- you can normally see it anyway
Inconsistent calculated formula in a table-MAYBE- if you use the table feature it will point out formula that are not consistent
Cells containing year's represented as 2 digits- YES - if the year of the date is entered as 16 for 2016, depending on the year Excel may see it as 1916 or 2016. Important if you have dates going 15- 20 years into the future
Number formatted as text or preceded by an apostrophe- YES - warns you that a 'number' is not being seen as a number by excel and won't be included in any additions. Also has a convenient option in the dropdown to convert the cells into proper numbers
Formulas inconsistent with other formulas in the region- MAYBE - looks at the other cells and checks if the formula is consistent (as per spreadsheet best practice). Sometimes looks up when it should look left and vica versa
Formulas that omit cells in a range- YES - will point out if you have a SUM formula and the like that are working on a range but missing out cells that are immediately adjacent.
Unlocked cells containing formulas- MAYBE - formulas that are set to be unprotected if you use Excel's protect cells features
Formulas referring to empty cells- MAYBE - as it says, the formula refers to a cell that is currently blank. May mean you are accidentally pointing to a blank cell or the input is missing
Data entered in a cell is invalid- MAYBE- used in tables where data differs from the type of data it expects e.g. it is text but the column has numbers in it

Although we sometimes find this feature annoying (in fact you can become blind to the green triangles), if you switch on the options that really concern you, you will pay more attention to the green triangles and may find your spreadsheet errors easier.
Add a comment...

Post has attachment
New post (Converting numbers formatted as text) has been published

You may notice that Excel sometimes does not 'see' your numbers as numbers. So even though you can see it, when you try and add it up, Excel ignores it. You may notice that sometimes these numbers have a green triangle on the top left hand side of the cells. You can use these green triangles to help in converting numbers formatted as text into Excel acceptable numbers.

As shown below in cell B12 we are trying to add up the column yet the answer is obviously wrong. Notice the green triangles in the Excel cells. This is an error checking feature in Excel. Most people are desperate to remove the green triangles but it can be useful.

If you click on one of the cells you will see an exclamation mark button and when you click on that you get the following

Notice it tells you that the number is stored as text (the issue) but just below that is a Convert to Number option. If you click this it does what it says.

But you won't want to do this with every cell individually, so rather highlight the column of cells BUT make sure your first cell contains the error (otherwise you don't get the Exclamation button). Click on the button and choose Convert to Number and it will convert most numbers that are a problem into a number

Our Data Cleanup course addresses all these types of issues including where all these standard options don't work.
Add a comment...

Post has attachment
New post (AuditExcel sponsors the Financial Modelling in Excel Meetup Group in South Africa) has been published

We recently held our first Meetup group for South Africa. If you don't know what Meetup is, it is a site to arrange 'meetups' for like minded people. In this case it is for everyone who loves all things Excel and Financial Models in particular.

Our next Meetup is on 15th Nov 2016 at Stanlib. The presentation will include free talks on:

Financial Modelling Best Practice, and
the intricacies in project finance modelling.

In order to attend, you need to join the group.
Invite to the next Financial Modelling in Excel Meetup
See some more details on the upcoming meetup
Join the Financial Modelling in Excel Meetup Group
Join the group to be able to attend.

Add a comment...

Post has attachment
New post (The risks of Excel's auto format feature) has been published

Everyone loves that way Excel can second guess what you mean when you type something into a cell and it (correctly) changes the format. Type 2 Sept and it will automatically format it as a date. But there are some users for whom this represents a major risk.

As discussed in a recent report from the Washington Post, scientists have problems representing certain genes accurately. When they try and enter the gene name, Excel may convert it into a date.
South African specific Auto format risks
Alphanumerics that start with an R
Closer to home we have a client who needs to enter the name of a product as R001. As we are based in South Africa and the currency is the Rand represented by an R in the front of the number, South African based machines assume you mean R1 (One rand).

Not only does it lose the R in the cell (you see it as the format is set to currency but in the actual cell, Excel only shows a 1), but Excel assumes you were a bit absent minded and gets rid of the 00 in the middle. This makes it very difficult to look up this product.
Refering to cells in column R
Also, if you want to refer to any cell in the R column you need to be careful how you do it. If you start your formula with a + instead of an = it gives the wrong answer.

As shown below all the cells in Q4 to Q7 were entered as +R4 etc (so NOT starting with the = sign). Instead of Excel telling me what is in cell R4, as it would if you referred to any other cell, it changes the cell to the number 4 and formats as the Rand currency.

Auto Complete Risks
Another risk is where Excel auto completes what you type based on what is in the cells above. If you don't look at what is in the cell when you push enter, it may add some extra text to it.

So as shown below, if in the cells above there is a "Manager- Senior", but we want to enter Manager, as you start typing Manager, Excel will suggest you use Manager- Senior.

If you type like us and watch the keyboard rather then the screen, you may push enter while not looking at the screen and Excel will assume you wanted the auto complete version.

Add a comment...

Post has attachment
New post (Slicer greyed out in Excel) has been published

If you have started using the slicer feature you may find that you get annoyed when the slicer is grayed out in Excel. This can happen when you receive a file from someone else, or if you download data from a source system and try and create it directly in the downloaded file.

The most likely issue is that Excel has decided that the spreadsheet you are working with may need to be used on a version of Excel that doesn't have the slicer option (e.g. Excel 2003, csv's etc). Signs that this is the issue is:

the file is saved as .xls
It mentions compatibility mode in title (or anywhere for that matter) as shown below.

Given that Excel 2003 is no longer supported there should be less and less of this version out there so it is highly unlikely that you need to keep it in this mode. Unless you really need to be compatible with older versions, it would be better to convert to the newer file format to be able to access all the new features.
Enabling the Slicer in Excel
To change it is relatively easy. If you have opened a file like this, when you click on FILE or the Office icon (depending on which version you use) you will see some more options.

One of the options is the CONVERT button (will only appear if the spreadsheet is an older version). If you click this button, it converts the file into the newer version which then enables all the latest features.
Add a comment...

Post has attachment
New post (Excel training dates for 2017) has been published

Below is a full list of the public Excel training dates for 2017 in South Africa. We have courses running in Johannesburg, Cape Town and Durban, covering Intermediate to Advanced Excel and Financial Modelling, budgeting and forecasting.

For information on the prices and venues request details via the button below.

[email-button-orange link=" send me prices, dates and venue details for the Excel courses"]Request info on training dates, prices and venue[/email-button-orange]
3 Day Intermediate to Advanced Excel
Johannesburg and Pretoria

16-18 Jan 2017
13-15 Feb 2017
13-15 Mar 2017
3-5 Apr 2017
15-17 May 2017
5-7 Jun 2017
10-12 Jul 2017
14-16 Aug 2017
11-13 Sep 2017
16-18 Oct 2017
13-15 Nov 2017

Cape Town

8-10 Mar 2017
17-19 Jul 2017
11-13 Oct 2017


20-22 Feb 2017
12-14 Jun 2017
18-20 Sep 2017

2 Day Financial Modelling, Budgeting and Forecasting
Johannesburg and Pretoria

16-17 Feb 2017
16-17 Mar 2017
6-7 Apr 2017
18-19 May 2017
8-9 Jun 2017
13-14 Jul 2017
17-18 Aug 2017
14-15 Sep 2017
19-20 Oct 2017
16-17 Nov 2017

5 Day Corporate Financial Modelling
Johannesburg and Pretoria

13-17 Feb 2017
13-17 Mar 2017
3-7 Apr 2017
15-19 May 2017
5-9 Jun 2017
10-14 Jul 2017
14-18 Aug 2017
11-15 Sep 2017
16-20 Oct 2017
13-17 Nov 2017
4-6 Dec 2017
Add a comment...

Post has attachment
New post (Excel training dates and prices for 2017) has been published

Thank you for being a subscriber.

Click on the link below to get the prices for the 2017 public courses.

[download-button-red link="" blank="false"]Download 2017 prices[/download-button-red]
Add a comment...

Post has attachment
New post (Tracing errors like #VALUE! or #DIV/0! or #NUM! in Excel) has been published

If you have an #VALUE, #DIV/0! or #NUM! error in Excel, it could be as a result of a direct link to the cell, or it could be that somewhere right in the beginning there is a problem. You may need to track back through all the cells to find where the problem cell is. There is a tool for tracing errors like #VALUE!, #DIV/0! or #NUM! direct to the problem cell.
#VALUE! or #DIV/0! or #NUM! source in the same sheet
So below, we have a #VALUE error in the IRR calc, but where is the real problem? You can click on the cell and then CLICK FORMULAS.ERROR CHECKING and then TRACE ERROR.

You will see that a red line appears and it takes you straight to the source of the error. In this case one of the inputs has a divide by zero in it. You can correct this.

#VALUE! or #DIV/0! or #NUM! source in a different sheet
In the above case, the problem cell is in the same sheet, so Excel takes you straight to it. However, if the problem is in a separate sheet you need to trace a bit back yourself.

As shown below, the trace error ends at a cell that is linked to another sheet (note the dashed line showing a link to another sheet). You need to follow these links to see where the problem is (learn how to navigate using this dashed line in the Fundamentals course). You may need to run the trace error again on the other sheet but it will be quicker than following everything manually.

Add a comment...

Post has attachment
New post (Speed up the opening or closing of an Excel file that recalculates) has been published

If you have really big files that you are opening or closing and they seem to take a lot more time than you have (because they are running the calculations again), you can get Excel to just open or close without doing the recalculations. Some ideas below to speed up the opening or closing of an Excel file.
Open files quickly by stopping the calculations
If you want to open a file to get some information that doesn't rely on the calculations e.g. you just need a list of the names of the clients or you want to look at the structure of the spreadsheet, you can stop the calculation process by pushing the ESC button while it is opening.

Note that this should only be done when the spreadsheet says it is calculating (look at the bottom right of the screen, it will say opening or calculating).

Also be careful that the information you are going to use with this quick open does not need to have a full re calculation to be correct.
Close/ Save Excel files quickly by stopping the calculations
When you close an Excel file, you may also find that it takes longer than you feel it should. This is often because it needs to do a full recalculation including any tables.

You can stop this process by clicking ESC while it is in calculation mode (again look at the bottom right to see whether it is saving or calculating). Excel will check that you are happy to stop the re calculation and if you agree then it will stop the calculations and just save the file.

Note that this means that the spreadsheet is saved without a recalculation so make sure it recalculates at some point before you rely on it again.
Add a comment...
Wait while more posts are being loaded