Welcome to our fine community
Since you said you already looked into tutorials and did some spells, you probably read: Getting started in real-time VFX? Start here!
I always giggle when I read something like "realistic spells" because they're not real. And I think that's the hard part. Make something that doesn't exist, but make it look like it could exist in the real world.
Not knowing what you are capable of now, I'll just assume that you know the basic methods of creating magic effects (particle systems, textures, flipbooks, simple meshes, shaders / materials, possibly basic scripts). "All-in-one" tutorials aren't very expensive and cost a bit. But there are a ton of good topics that you can find if you know what to look for.
The next step would likely be learning:
learn to create more complex 3D objects.
Shards of ice, stalagmites, floating stones, the hand of a summoned demon … all of these things require you to create more complex meshes and understand shaders / materials better. The PBR workflow is critical to being "realistic" these days.
learn to use simulations.
Traces of smoke, splashes of liquid, or a stone that breaks apart … you need to learn to run such simulations and import them into the game (through flip books, baked animation, or VAT).
Understand how real things work.
Reference is the key. I bet most of the people here have a vault with a number of references from water, smoke, lava or whatever. If you look at these and analyze what is happening in motion, you will get a better eye for what the effect is missing to look more natural.
That being said, I'd recommend checking out some assets like that from kripto289: https://assetstore.unity.com/publishers/5224
These have been good learning resources for me as I've looked at all of the shaders, animations, and settings that went into these effects. And they're mostly very well optimized with some great scripts to even toggle fps caps! (Do not use the latest version of Unity, however. The creator updates it, but with each update of Unity, some effects are canceled as the scripts try to be used for HDRP and URP as well.)
Another hint: look for tutorials in UE4 as well. Even if the engine is different, the approaches and techniques are the same.