we use Kay vault, only if our application is getting deployed to cloud, where we need to avoid including service id password etc in the build package
Yes, basically correct, but if you want, you can also use keyvault when the app is deployed on-premise.
If the cloud here you mean azure web app/function, you can also use Key Vault references feature to store your secret/password/etc, it can hide your values in appsettings, it uses the MSI(managed identity) to access azure keyvault automatically.
Generally, you can store secret/key/certificate in the keyvualt, add your client to the access policy of the keyvualt, then you can use it to access the keyvault anywhere.
For more details, see - https://learn.microsoft.com/en-us/azure/key-vault/general/overview#why-use-azure-key-vault