Numbering rows in a Table

When you insert a table on your sheet (by going to Insert-Tables-Table), you may need to number the rows. When you create your table, it will look like this:

NumberingRowsTable1

In this case we want to put and order number on column B. For that we just need to add the following formula into cell B5:

=SUBTOTAL(3,C$2:C4)

The result will be like this:

NumberingRowsTable2

The formula uses  the SUBTOTAL function with the argument of 3 to COUNTA. This will give the count of all cells in column C, starting to count from the header (C2) and ending on the row that is one cell above the one with the formula. For instance, on cell B5, if you check the formula, it is counting the number of rows from C2 to C4, thus returning the result of 3.

This formula will be updated even if you sort or filter your table data.

Excel 2010–Watch Window

Ever needed to keep an eye on a single result of a formula on one sheet while working on multiple sheets? You needed to be constantly switching from sheet to sheet to check the result every time you changed a value, right? No need for that anymore because Excel 2010 introduces the new Watch Window.
The Watch Window button can be found on the Formula tab, Formula Audition group. Click it and a Watch Window dialog will open. Now click on the Add Watch button in the Watch Window dialog.
WatchWindow1
A new dialog window will open for you to select the cell that you want to watch. After you select the cell, click on the Add button to close the dialog window.
WatchWindow2
You can repeat theses steps for all the cells that you want to check. After that you position you watch window on top of your sheet or you can position it where it doesn’t cover your sheet, on the top of it, bottom, left or right.
This way you will have a Watch Window that will show you the result of the cells that you’re tracking, without having to switch to the sheet where the formula is to check is result.
WatchWindow3