Welcome to Excel 3 tip Friday, DataMinded’s weekly Excel update where we cover a tip, a function, and a shortcut to help you build your Excel skills over time.
A quick win you can learn today -
When you’re dealing with objects in Excel - charts, diagrams, arrows, etc, you’ll find that the size of the shape will change when you change the width of columns or height of rows that it overlaps. This can be super frustrating, if you’ve already sized and positioned the object just how you want it!
To switch off the auto resizing, right click on the objects or chart, and go to Size and Properties. Select ‘Don’t move or size with cells’ - now your object will stay in the same place no matter what changes you make to the rest of your sheet.
A keyboard shortcut I'm adding to my list -
Surprisingly, I only learned this one recently (yes, even I don’t know every keyboard shortcut offhand!). Ctrl + Shift + Spacebar will select all the cells on the current sheet. It’s a bit like Ctrl+A - but rather than selecting all the cells in the current table, it selects all in the current sheet. Pretty useful if you’re doing things like unhiding all hidden rows or columns, or setting a number format across your entire sheet.
A function I've found super useful this week -
With these emails, I usually stick with functions that you’d tend to use in a work setting. But sometimes it’s good to cover ones that can be useful outside of work. What about using Excel for your personal finances? There are some super useful financial functions in Excel that are often overlooked. One of these is ACCRINT, which returns the accrued interest of an investment.
So, say you have a fixed investment in a bond or a term savings account, and you want to calculate the amount of interest you’ll earn over the period. Just type =ACCRINT( followed by the following arguments:
The issue date/start date of the investment
The payment date of the first interest payment
The settlement date of the investment (i.e. when you began to own it). This can be the same as the issue date.
The interest or coupon rate
The security or investment par value (its start value)
The number of interest payments made per year.
So it’s not the easiest function in Excel to use, but it can be super useful for working out how much interest you’re going to earn on an investment!
As always, I hope you find these tips useful, and please do reply if you have any comments or questions!