Here’s how to use EDATE: =EDATE(15/01/16,+1) = 15/02/2016 (15th Feb 2016) =EDATE (15/01/2016,-2) = 15/11/2015 (15th Nov 2016)
OFFSET can be useful for things like calculating YTD numbers or creating formulas that take data in rows and using in columns.
If you want to add a second line of text within a cell, use ALT+Enter.
Some people waste a lot of time even for simple operations, for example, when they need to insert/delete columns and rows in Excel. Use this shortcut to insert: with an entire row or column selected, use Ctrl+Shift ++. To delete: with an entire row or column selected, use Ctrl + –.
Filtering – it is a powerful way to slice, dice, and sort through a huge table of information. It’s amazingly effective when you’re participating in a meeting to discuss something like a sales forecast, and everyone is looking in real-time at your spreadsheet projected on a screen (or on their monitors). To some people, you will be seen as the God of Spreadsheets, and this is not a joke!
When you need to choose visible cells only – use Alt+. This is the trick to copy only what you see. It is a priceless shortcut when you’re manually hiding rows and columns in the table.
When you need to format cells, use Ctrl+1. Most people know this as the shortcut for the Format Cells dialog, but you can also use it to format almost anything in Excel, without a care about the state of the ribbon. Try this amazing and simple shortcut!
By customizing the quick access toolbar, you can create simple shortcuts to commands that you would otherwise have to find in the Ribbon tabs, or macros you have created yourself. The keyboard shortcut is simply selecting Alt+ (the number of the command you wish to select). For example, if you have customized your quick access toolbar to have Calc Sheet, Save, Open. To calculate sheet you would hit Alt+1, for save Alt+2, and for open Alt+3. A lot of people are unaware of this useful function, and it’s a great time saver.
If you added a formula in a new column on the right of a huge dataset, and you want to copy that formula down without scrolling, do these steps:
Suppose you want to check the help topic for a worksheet function. For example, you want to read about the MATCH function. In a cell, type… =match( …and then press Ctrl+A, or click the Insert Function (“fx“) button to the left of the formula bar. When you do so, Excel displays the Function Arguments dialog, which might offer all the help you need. But if you still want to see the complete help topic, click the blue “Help on this function” hyperlink in the lower-left corner of the dialog. This technique works with all documented Excel functions.