As a fluffy Archlinux user I use Excel via freeRDP from a work PC on my LAN. This is a recent development, I’ve never used Excel before. Of course I’ve long been in awe of Joel Spolsky and Martin Shkreli’s impressive excel analytical skills.

Inspired by @edent’s blog and … a Tiktok series by @acouplecents’s 1 2 3 4 short videos.

• Ctrl+←/→ move around a spread sheet quickly
• Esc / F2 – Toggle between editing, like Esc works in vim
• ⇪+Space – Highlight row
• Ctrl+Space – Highlight column
• Ctrl+⇪++ – Inserts row or column depending on context
• Ctrl+- – Deletes row or column
• Ctrl+PgUp/ PgDn – Move between sheets
• Ctrl+r – when highlighting cells to right, copy to right
• Ctrl+d – copy down
• Alt+= – Sum up something

Here is the authoritative list of shortcuts for Microsoft Excel, but they don’t highlight the productive ones.

Update: My dear old friend Philip pointed these nicely laid out Excel shortcuts, that you could conceivably print I suppose.

Filtering

Ctrl+⇪+L – Create filter tabs

For me, working with EC2InstanceRecommendations-DirectMatch exports from AWS Migration Hub, I need to be able to filter machines that are designated to migrate to the cloud.

A great tip I’ve found is to use COUNTIF for advanced filtering based on other cells. When filtering I’ve found two issues:

1. It’s not clear what is filtered out (collapsed?) Appears to be a common issue
2. Totals are not recalculated properly

I don’t know how to solve 1., but for 2. I’ve found using SUBTOTAL instead of SUM. I’m not sure why tbh.

Power Query for splitting into rows

Found this splitting tip useful from Excel Tech Community rather awesome.

History.. or lack there of

Wonder how much white collar crime could have been reduced if @msexcel had decent history functionality?

— Kai Hendry (@kaihendry) July 7, 2020

Like all Microsoft products I’ve seen, the history of the document is awful to work with. Managing different files and copies is a nightmare! Google Sheets is fantastic to track changes.

Reddit’s /r/excel has no real answer to this either..

Convert Megabyte into Gigabyte

=CONVERT(65536,"Mibyte","Gibyte")

Credit to this answer.

Look up data in another table

Use XLOOKUP!

Conclusion

My dream is for Pandas and Python notebooks to have better usability. Seems far off though.

So Excel via FreeRDP or AWS Workspaces seems like a reasonable workaround.