It is common to have a lot of input cells in an Excel file. It is best practice to use the same format for all the input cells. This makes it easier to identify input cells for you and the user. Here is a technique using Conditional Formats that may be useful.
(more…)
One of the problems with Excel’s Data Validation is that it is possible to have an invalid entry in a data validation cell. This can be caused by Paste Special Values or linked drop downs that don’t update if an earlier drop down is changed. To easily identify invalid cells you can use a macro.
(more…)
The UNIQUE function has a bit of an issue with blank cells, formulas that return blank cells and zeroes.
(more…)
If you need a logical test to determine if a list is unique you can use the MODE function with the ISNA function.
(more…)When you enter data into Excel you can format as you type. See how in this short video. https://vimeo.com/705601028
If you have a list of numbers that are a text numbers or a combination of text numbers with real numbers there is a technique I covered in this blog post to add them up. But if the range also contains text then the technique won’t work. There is the work around. The solutions below work in the subscription version of Excel. Check the comments section below for a solution for all versions.
(more…)