Excel tips
Use EDATE to move a date on by a full calendar month
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)
Use OFFSET for complicated calculations or formulas
OFFSET can be useful for things like calculating YTD numbers or creating formulas that take data in rows and using in columns.
Use ALT+Enter for more text within a cell
If you want to add a second line of text within a cell, use ALT+Enter.
Insert or delete column/row easily with the Ctrl key
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 + –.
Use filtering
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!
Choose visible cells with Alt
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.
Format cells with Ctrl+1
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!
Quick access to any function with Alt
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.
Copy stuff down the column without scrolling with Ctrl+D
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:
- go to the right to the column that has data (the column to the left of the new column with the formula);
- press Ctrl+Down – to get to bottom;
- move one cell to the right (with arrow key naturally);
- press Ctrl+Shift+Up to select the new column, at the top of which is the formula you just created;
- press Ctrl+D to fill down the formula.
Launch Function Arguments dialog easily with Ctrl+A
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.