Pretty Excel Views with Formatted Table — Kenneth Lo, PMP

The desire of writing this article stems from my present circumstances during shelter-in-place. Few things I struggle with working remotely include:

• Running out of monitor real estate on a single monitor.
• Inability to print with a laser printer and company-supplied ink cartridges.
• Exhaustion from scrolling endlessly in Excel files that have way too many columns.

The last pain point manifests daily since my numerous projects mandate frequent interactions with large Excel files. Not having the usual office setup creates additional challenges. When a spreadsheet grows, it invariably becomes wider (more columns) and taller (more rows). That means lots of scrolling.

Grouping or hiding columns alleviates the issues somewhat. Let’s go over another approach to generate a view that contains a subset of columns based on the source data. The output is merely a view and not a copy of the original source. Any change made to the source are faithfully rendered in the corresponding views.

The dataset illustrated in this article comes from Kaggle: Startup Investments via Crunchbase. It lists 50,000 startup companies and their fundraising metrics. The raw data contains 39 columns and 50k rows. The thought of looking at this long list by market sectors, funding rounds, funding amounts and other dimensions make my wrists ache already.

Some prerequisite knowledge is required to maximize understanding of what follows. Have a quick peek at these how-to posts:

Format an Excel Table

A formatted Excel table has many benefits. Automatic styling and enhanced readability are most apparent. Another pivotal benefit is the added support of Structured References, the powerhouse behind the technique explained this article.

Once the table is created, make sure to name it accordingly. In this instance, the table is named _crunchbase. The name will be used in Structured References later. Best practice is to be descriptive without being verbose.

Create a Print-Only View

Excel does not truly support “views”, which essentially are representations of the source table. One prime use case is a print-ready view that has far fewer columns. Another example is a role-based working view for investment, marketing, or legal team.

On a new worksheet named , insert =_crunchbase in A1.

With virtually no effort, you have a view of the entire table without any columns header. Not too shabby for few keystrokes.

Now change A1 to =_crunchbabse[#All]. You get a view of the entire table with the headers.

Pretty cool. Let’s tackle the objective of displaying a subset of columns. Make the following modifications:

• A1 to =_crunchbase[[#All], [name]]
• B1 to =_crunchbase[[#All], [market]]

Translation of the two formulas above: show the headers and data in and columns from the source table. Let’s add a few more columns and a filter to wrap things up.

Takeaways

When working with a larger, especially column-wise, spreadsheet, you can effortlessly generate a filtered view with far fewer columns. Less scrolling, more mental focus, more time for leisurely activities.

Lots more can be done to further enhance this Pretty view. Beauty is in the eyes of the beholder. Feel free to take the sample file on my Github and make your own Pretty views.

Originally published at https://klopmp.com on June 8, 2020.