Excel shortcuts for Archers

Published: Thursday, Jun 4, 2020 Last modified: Thursday, Jul 2, 2020

As an 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.

Excel's bottom right summary

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

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.

Excel support

Via Twitter they referred me to a TechNet forum which looks like a train wreck to me.

History.. or lack there of

Like all Microsoft products I’ve seen, the history of the document is awful to work with. One is left having to manage different files and copies, and oh gosh, what a nightmare! Google Sheets is better in this regard.

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

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.