When creating data input sheets, it is a good idea to use a table layout. Sometimes they can end up looking a little bit busy, especially if you are repeating entries down rows. To help users focus on what they need to do, you can use a little formatting hack to make the layout look a little less cluttered.
One thing you learn quickly about Excel is that there are many ways to achieve the same outcome.
This is another example. In an earlier video I showed two separate ways to convert text numbers into real numbers.
Well, I have just learned another way. An Excel MVP Rick Rothstein shared a third way. I tweaked it and share a keyboard shortcut to do it as well.
Hope you enjoy it.
Added Nov 27, 2021
If you use Text to Columns for other conversion in the same session, you may need to use Alt A E W F as the Delimiter defaults may interfere with the conversion.
Would you like to display a pop-up message when a user enters a value into a cell? You don’t need a macro to achieve this.
I posted recently about how you can amend a custom list to change the sequence of a slicer – read it here. Here is another tweak I learned from Mr Excel (Bill Jelen).
Dynamic arrays allow you to use a function normally built to handle a cell, with a range of cells. The TRIM function can remove extra space characters in cells. So with dynamic arrays it can handle ranges.
Yes, I know you should use Power Query to clean data and I demonstrated how to do that in my previous post. Sometimes it is easier to record a macro because a macro can clean the data in place.