There are two different situations you need to handle: Successful save, and validation/error. These are quite different so you should treat each solution independently.
On successful save
Typically, this is going to be your most common scenario, especially once the users are familiar with the data they are entering. This is your 99% scenario, so you want this functionality to be subtle.
I recommend two key points:
- Include a short message to inform the user that data is saved automatically when "entered" (feel free to change the wording).
- Make it very obvious what data is being edited, and what data is set/persisted.
Something like the following should achieve these aims.
As you can see, once the user enters the "9" and moves to the next cell (mouse click, tab, enter), it is obvious that the value is "set" and is now part of the stored data.
On validation error (or other error)
Unless the user is having trouble understanding the data they should enter, this is your 1% scenario. That means we can afford to be more intrusive with our messages, after all, we want to make sure the user notices the problem so they can fix it.
I suggest this is handled as follows:
- Prevent the cell from losing focus while there is a validation problem (user cannot select another cell)
- Change the colour so it stands out
- Add an "error" icon
- Allow the user to see more specific information by hovering/clicking the icon (via a tooltip perhaps)
- Don't forget you need a method for the user to revert the data back to the original. This could be the "ESC" key, or even a dedicated button that appears on the error message tool tip.
Something like this:
Or with a more interactive tooltip message:
These were just some quick mock-ups I threw together. You will probably want to put a bit more care into them, particularly the error tooltip, but hopefully it's enough to get you moving in the right direction.