First, calculating interest on your bank account daily makes the most sense because your balance in a bank account typically fluctuates throughout the month: that is, you make deposits, and you make withdrawals.
If the bank calculated interest only at the end of the month, say, based on your balance at that point in time, then it might not be fair to either you or the bank. Depending on whether your end-of-month balance was higher than average, or lower than average, either you or the bank would come out ahead. So, by calculating interest daily the bank is, in effect, arriving at an amount of interest on some form of average balance, which is more fair to both of you.
However, even though interest may be calculated daily, it is typically only credited to your account once per month. Imagine the mess it would make on your statement if it were credited daily!
Regarding calculating interest in Excel, have a look at the EFFECT() function. See also How to calculate compound interest for an intra-year period in Excel. For instance, if the nominal annual interest rate were 5% and you wanted to know what the effective annual interest rate is with monthly compounding, you would write =EFFECT(0.05,12), which would yield 0.051161898, or ~5.116%.
A longer form in lieu of the Excel EFFECT() function is what you'll find explained at Wikipedia - Credit card interest - Calculation of interest rates, i.e. the EAR = (1 + APR/n)^n -1 formula. Or, in Excel, =POWER(1+0.05/12,12)-1 to match the example above. Also yields 0.051161898.
However, each of the methods above to compute the effective annual interest rate is only appropriate if you want to know the future value some years hence but without any inflows or outflows. Once you have a situation where you are making deposits or withdrawals, you'll want to create a spreadsheet that calculates the daily interest and adds it to the ongoing balance on a monthly frequency.
To arrive at the actual amount of interest you would need to accrue for a single day, you would divide the original interest rate by 360 or 365. (Bank rules on this may vary – I'm not exactly sure.) So, the daily interest on a balance of, say, $1000 would be =1000*0.05/365, yielding 0.13698630 or 14 cents if rounded up to the nearest penny. Of course, you need to know the rounding rules. Perhaps rounding is done on each day's resulting interest (before summing), or on the sum of the month's resulting interest. Plus, bankers can round different than you might expect. Again, I'm not exactly sure on this.
In constructing a spreadsheet to calculate interest this way, you should not be adding the daily interest to the ongoing balance directly, but rather accrue the interest in a separate spot off to the side somewhere until the end of the month. At which point, sum up all of the daily interest earned and add it to the ongoing balance. Consider: If you were to credit the ongoing balance each day with that day's interest, then you would, in effect, be performing daily compounding instead. By adding the interest to the ongoing balance only once per month, the compounding is in effect monthly, even though the interest is calculated on the daily balance.
Here's a link to a sample Excel spreadsheet (*.xlsx) I created to demonstrate the above.
