In this week’s Excel tips we cover how to customise date formats in Excel, how show and hide the menu ribbon, and how to use the IFERROR function to determine if a cell or function produces an error.
Custom format dates in excel
If you enter a date in Excel, it will automatically recognise it and format the cell as a date. The default format of a date in excel (English UK) is dd/mm/yyyy.
You can test this by entering a date in a cell, you will see that the number format of this cell (under Home –Number section) will automatically change from General to Date.
But we might have situations where we would rather show the date in a different format to fit our purpose.
For example, if we are trying to label a product and the best before date must have the format MMM DD, excel can help with this by formatting the date exactly as you need it.
We will try to display the date 25/04/2019 in the MMM DD format:
First select the cell you want to format and in the Number Format drop down under Home in your toolbar, select More Number Formats:
This will open the Format Cells window:
You will see that the Date format is automatically selected by excel and a list of other date formats is available for you to choose. If any of this is what you need, you can just click on it and press OK.
For our example, none of this dates is suitable so we will skip this and click on Custom:
You will see here as well you have more ways to format the date, fell free to choose any of the options and press OK if you find what you need.
Again for our example, none of them are suitable so we will write our own format.
In the Type area in the middle section, delete what is already there and write mmm dd:
You can see above it under Sample, how this format will look:
This is what we need so we will click OK and you will see your cell change format:
Please see below a few more date formats, you can create your own as well:
You can use the Custom area of the cell Format window to format any contents of the cells, not only dates!
Please note: the date itself doesn’t change no matter what format you give it, the only thing changing is how it date is displayed. Excel will still recognise the date with the full details, should you need to use this date in a formula for example.
Shortcut to Show/Hide the ribbon
You can temporarily hide the ribbon menu in excel. You can do this quickly by using the shortcut: CTRL+F1. This will hide the ribbon and give you more screen space.
To unhide the ribbon, press CTRL+F1 again.
IFERROR is used to specify what should be displayed in a cell should the formula in that cell evaluate to error.
This function is used when we know that there is a possibility that a certain formula might evaluate to an error value and we don’t want this error to be displayed. This is quite common and we can use the IFERROR function to decide what we want to be displayed instead of the error.
You will find the IFERROR function is very useful on reports/dashboards. Sometimes you will have a product without sales for example, and the formula that calculates this sales might evaluate to an error. It wouldn’t look very professional if the dashboard displayed the sales as #N/A, we would rather this sales display as 0.
Another use for this function is to avoid a macro from stopping half way. VBA doesn’t really like error cells, so depending on what you are trying to achieve, it will crash when it encounters a cell with an error. There is ways to avoid this with VBA but you are better off “tidying up “ your sheet before running the macro and make sure no formula displays an error.
The syntax for this function is:
value: this is the value that we expect to be an error. Very often this value is a formula or a cell which contains a formula
value_if_error – this is the value that we want to display if the value argument is an error
If you look at the logic of this function is very similar to an If function, except we don’t need to specify a value if not error:
If the value is error, display the value_if_error argument, else display the value itself.
This formula evaluates errors such as #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, #NULL!
Let’s take an example:
We will try and divide a number by the number 0, so to deliberately produce an error:
To avoid this error from being displayed we will instruct excel to display “You cannot divide a number by 0” every time this formula evaluates to error.
Enter the following formula in cell L2:
You will see that the cell will display:
We have done this in two steps: first entered the division formula in cell K2, then in cell L2 we specify what value should be displayed if cell K2 evaluates to error.
We can do this in one step as well by entering the division itself instead of K2 in our second formula as follows:
Please note: This formula doesn’t fix the error, it merely hides it. Avoid using this function when you are trying to fix the errors in your formulas/sheet as this function will hide the errors.