Technical Interview Question

I've thought about this too as our team tends to face similar circumstances in terms of supporting data import, or other needs to do excel report manipulation. Have you tried framing the question in the general interview as a scenario? I think it's possible to explain the circumstance, ie the customer giving you data in a format incompatible with how you need it, with an explanation of the final goal, and see if they can map out steps in between? I think you should be able to lead them to share with you whether they have a workable comprehension of excel and whatever formulas you may need them to have an understanding of, be it lookup formulas, concats, or others.

We ultimately try to avoid having anyone "take a test" for the job, as we find it to be a barrier to hiring. You can also check their LinkedIn to see if they have any "badges" showing excel competency or any other tools you may require them to have understanding of.

Best of luck!

We do a lot of data cleaning as a part of the new client implementation process. The client sends us their data and we need to clean it up to match our import templates. So it involves excel and formulas. I know you can just look up stuff online, which I still to this day do but it would be a good exercise for the candidate to get an idea of what they'll be doing. We don't expect them to get it 100% accurate but see what their thought process is.

As for email templates, I don't know exactly what I'm trying to accomplish. I've done them before when I was a candidate. 

I also thought of maybe like a mock call as an upset client, not getting too detailed into the product but seeing how they would handle that situation.

What is your goal? What are you trying to establish and why?