My Experience with #Excel

MMULT and MINVERSE

In 2008, I used these two to solve nonlinear equations. I didn't know how to use Matlab at the time. And I still don't.

I never used these two after that project, but since then I fell in love with Excel as it's so powerful.

INDEX and MATCH (and OFFSET)

I've never used VLOOKUP since I knew how to use the combination of INDEX and MATCH, because it's much more flexible and faster.

INDEX and OFFSET combination allows you to dynamically define a range, very useful.

ALT, N, V, T (This one may have retired...)

Similar to ALT, D, P, this one creates pivot tables. However, you have to option to check the box at the bottom, 'add to data model'. This allows you to use DISTINCTCOUNT.

You can still use COUNTIF, SUMPRODUCT, along with ABSOLUTE REFERENCE and SEMI-ABSOLUTE REFERENCE. However, pivot table is much easier and faster.

CTRL + ;

I didn't expect it to be very unpopular, but this is very useful to insert current date. Very handy, along with, CTRL + D, CTRL + R, and CTRL + ENTER .

(I once shared it with a warehouse staff when I saw her/ him typing dates. From then on, I received a lot of help from her/ him and the team. )

ALT, A, O

It's always a good idea to trace back to the query or connection.

ALT, A, T

Toggle filters. Very convenient but I don't normally leave the filters there in the final report.

ALT, A, S, A (or D)

Set the column in order, ascending or descending.

ALT, W, N

If you want to look at more than one worksheet from the same workbook, this shortcut is quite handy.

Quick Access Toolbar

Allow you to have an extra set of keyboard shortcuts. Normally, if I see customised QAT, I would consider the owner an advanced Excel user.

Solver

Basically, it's Linear Programming in Excel.