Most users don’t know what an array formula is and many people avoid them, but they can stop rows being inserted.
Monthly Archives: March 2016
Display a Ratio
To have a value show as a ratio 5.0:1 you can use a custom number function.
The value in the cell is 5, but it displays as 5.0:1. This means you can still do calculations and comparisons with the value.
The format is
0.0":1"_)
PowerPivot and Power Query
How to get and install
I have run two introductory webinars in 2016 on these two topics. You may need to download them and install them before the webinars so you can use them during the webinars. The Add-ins are free from Microsoft.
Clearing Input Cells with Colour
Let’s say you have a file that has numerous input cells, in numerous sheets. Each month you must clear the input cells and start the new month. There is a technique that allows you to clear the input cells if you have used a dedicated colour for those cells.
Find Errors
Excel has seven error messages that can be displayed when something goes wrong with a formula.
To find errors you can use the Go To Special dialog.
Press F5 then click the Special button.
Click Formulas and then uncheck Numbers, Text and Logicals. Click OK. See image below.
Now all the error cells are selected you could apply a fill colour at this stage to make then stand out.
To move between the selected cells press the Enter or Tab keys. DON’T use the arrow keys, as this will deselect the error cells.
VBA to Open CSV and Avoid Date Error
You can create a macro to open a CSV file. One problem you may face is that dates are treated as US dates. A simple change can fix this.
Day Of The Week
A quick and easy way to find out the day of the week for a specific date is to use the Long Date format from the drop down in the middle of the Home ribbon (in the Number section) – see below.
