Dear Analyst

Excel tables are a really powerful feature, but there are pros and cons to the feature depending on if you are an advanced or beginner Excel user. From what I’ve seen, most people who use Excel tables just to get the nice color formatting on the header row and the alternating banded rows color formatting. At the end of the day, if you are managing and analyzing a group of cells that are related to each other, turning this range of data into a table will make it easier to maintain the data going forward. I also wrote extensively about turning your data into tables generally in this post on the Coda blog.

Pros for using Excel tables:

* Structured references to columns in the table over cell references* Formulas automatically “fill down” the entire column so you write the formula once* New rows that get added to the table automatically expand the borders of the table (no more writing OFFSET formulas!)

Cons for using Excel tables:

* The name “tables” is confusing, people already have tables/lists of data in their Excel files* The syntax for structured references is really difficult to understand (especially for a new Excel user)* Editing table names is cumbersome

In the 2nd half of the episode, I talk about some episodes from other podcasts I found interesting:

* The Startup Chat: Episode 391 – How to Create Effective Comparison Pages* Numbers Geek – Steve Ballmer on the U.S. trade deficit* Product Hunt Radio: Episode 158 – Ventura Capital 101 with Eric Bahn

PivotTables in Excel are a great way to visualize your data. However, in order to easily query and summarize your data in the PivotTable, you need to properly structure your data set before building the PivotTable. Not only is the practice of streamlining your dataset into fewer columns good for PivotTables for different dimensions, this is a good practice for putting your data in a regular database down the line. The dataset I used in this episode is the Gender, Institutions and Development Database 2014: Violence against women from the OECD.

In the 2nd half of the episode, I talk about some episodes from other podcasts I found interesting:

* The Tim Ferriss Show: Episode 359 – Tobi Lütke – From Snowboard Shop to Billion-Dollar Company* JS Party: Episode 64 – TensorFlow.js and Machine Learning in JavaScript* Syntax: Episode 121 – Hasty Treat – Tips to Succeed on YouTube

What are your favorite Excel keyboard shortcuts? I talk about my top 5 and why I think they are useful for all analysts. The key takeaway is to stop using your mouse or trackpad on your laptop, and commit to learning these keyboard shortcuts to be more efficient and productive in Excel. My #1 Excel keyboard shortcut may surprise you since it basically goes against what I talk about in episode 1 :).

I wrote a few articles on the KeyCuts blog about keyboard shortcuts. One of the more popular ones is this one about the top 5 keyboard shortcuts in Mac Excel. A lot of people think you can only use keyboard shortcuts on the PC, but fear not–they also exist on the Mac!

In the 2nd half of the episode, I talk about some episodes from other podcasts I found interesting:

* The Changelog: Episode 205 – A protocol for dying* The Tim Ferriss Show: Episode 353 – Patrick Collison, CEO of Stripe* The Changelog: Episode 331 – GitHub Actions is the next big thing * The Pylon Show: Episode 36

This is the first episode of Dear Analyst, a podcast about Excel, data analysis, software, and everything in between. In this episode, I talk about the Copy and Paste Special Values feature in Excel, and why it’s not a good habit to get into when it comes to structuring the data in your Excel files. The original blog post I wrote about this topic is on the Coda blog here.

In each episode, I’ll also do a recap of other podcasts I’m listening to and take out the most interesting ideas and concepts (at least the ones I find interesting). Here are the podcasts and episodes I refer to in this episode:

* This Week In Startups: Episode 885 – Year-end special TWIST!* Acquired: Season 3 Episode 10 – Tencent* Unchained: Episode 98 – Mike Novogratz on Why Bitcoin, “a Big FU to the System,” Needs Institutions* Changelog: Episode 328 – State of the “log” (2018)