Excel tips
Use Scenario Manager to generate summary outputs of a spreadsheet
Scenario Manager (under “What-if Analysis”) enables users to generate high-level, summary outputs of a spreadsheet – without the need to replicate the entire workbook. It will present multiple scenarios of a spreadsheet in a succinct, high-level summary worksheet.
Use formula auditing
This is a great tool to analyze and trace precedent or dependent cells, check errors and evaluate formulas. The “Watch Window” is a feature to keep a snapshot of an area of the spreadsheet, and then move to another area of the workbook – particularly valuable if you’re managing large spreadsheets or don’t have a second screen.
Use conditional formatting
It can be used for various purposes such as color format or cell format of cells, rows or columns based on dependent cell values or formats.
Use data validation
This is an amazing but underutilized tool in Excel, which can be used for a variety of things:
- Create dependent drop-down lists;
- Create drop-down lists;
- Protect/restrict data input of specific cells (without the need for VBA macros).
Use autosum shortcut for sum function anywhere
Autosum shortcut – use Alt =. It is a “magic” shortcut of Excel to automatically insert a sum function. You can use this shortcut to sum rows, columns, or even an entire table in one step without wasting your time.
Use Ctrl+ to create a timestamp
If you need a date stamp and/or a timestamp in your document, there is no need to type a date and time! Use shortcuts Ctrl+ ; (date) Ctrl+Shift+ : (time). It works like a magic and helps to save your time and nerves.
Quick access to cells with the Ctrl key and Shift key
When you need to go to the first or last cell of a worksheet, no matter where you are, use Ctrl+Home, Ctrl+End combinations. And here is a pleasant bonus for you: add the Shift key to select everything on the way!
Repeat commands with F4 or Ctrl+Y
In many cases, you may need to repeat your last action. Use F4 or Ctrl+Y; you can repeat many commands like applying the same borders, format, or insert a worksheet again.
Remove spaces with TRIM
TRIM is a useful function known by few people. It removes any spaces at the beginning of a value. This is useful if you are pulling in values from somewhere else.
Use EOMONTH to move a date onto the end of the month
Here’s how to use EMONTH: =EOMONTH(15/01/2016,0) = 31/01/2016 (31st Jan 2106) =EOMONTH (15/01/2016,-2) = 30/11/2015 (30th Nov 2015)