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)