Sponsored by: Corsair’s TradeCraft (now in Early Access)
Practical Explainer — spreadsheets
Simplifying the landscape of analytics — one concept at a time
Do Spreadsheets Really Need An Explainer?
At their most basic level, perhaps not. But so many professionals fail to go beyond the most superficial levels of any spreadsheet software, we felt it was valuable to create something that could take everyone a step further. But let’s start with a simple and basic definition.
Spreadsheets are the digital equivalent of graph paper.
At some level, that is all you need to say. Much like graph paper, you can do a whole lot of free form work in a typical spreadsheet program. Unlike graph paper, you can actually even toggle the grid lines off.
Now let’s get truly dangerous. In reality -
Most spreadsheet software supports a complex array of activities from ETL, to data storage, reporting, advanced calculations, programming (via macros), data visualization, and even statistical modeling.
In reality, if you do too much of any of this you are liable to break something. That might be your file, your computer, or the good will of your IT department. Spreadsheets have become the Swiss Army Knives of basic analytics — especially on the financial side. That makes them both powerful and dangerous.
In practice -
Most spreadsheets come in one of two formats —
Excel) the market leader in this space, owned by Microsoft but so widely used that almost everything converts to a .XLS format.
Open) the scrappy alternative which occasionally steals market share but seems to exist mainly to emphasize that everything is in Excel.
Whether using Office (xls), Open-Office (ods), or Google Docs (converts to xls) — all spreadsheet programs share the vast majority of their functionality. This is only further reinforced by the ability of many products to install add-ons which effectively create a lot of missing functions. But if we are being completely honest — Microsoft Excel offers the most and typically by a good margin.
Breaking it down -
Spreadsheets are really about tables. Creating tables is the most basic and foundational activity you can perform. Tables are in turn composed of columns (A), rows (2), and cells (A2). Cells contain either values or functions (formulas). Everything else is just a build out on this most basic functionality.
Tables are then used to build charts (often called but never labeled graphs). Again, none of this being far beyond the reach of even the most basic users. But sadly, we have now hit that limit.
There are plenty of other very basic functions that nearly all spreadsheet systems can perform that remain just beyond the confidence levels of most casual users. These capabilities are very powerful and yet, don’t rise to the dangerous levels discussed above. Let’s close this article by iterating through some of the easiest and most powerful.
Higher Level Functions
Almost anyone who has spent some time in a spreadsheet has likely created a SUM function. But spreadsheets offer as many as a dozen variants of a SUM and very few people use them.
There is a literally an entire table (with many rows and multiple columns) of excel functions to use. Available here. Yet, few people invest much time in learning more than a handful. Truth be told — you can do a lot with that handful. But you could do a lot more with just a few more.
Sorts and Filters
Full disclosure, I have witnessed seasoned analysts destroy themselves with sorts and filters. You must use them carefully. And yet, they are both incredibly simple to use (or add) and incredibly powerful to boot.
Just be careful that you sort your entire table. And remember to check which filters are on when you are developing insights.
Pivot Tables
This topic deserves its own article (and it will get one soon enough). But perhaps nothing better defines the gap between master spreadsheet users and the casual or novice user than pivot tables. Pivot tables are powerful organizing and visualizing tools when dealing with multi-dimensional data. But what does that mean…?
Put simply -
Pivot Tables allow you take one giant table and reconfigure it as a large number of smaller tables. This simplifies while at the same time enhancing your view with groups, sorts, filters, and totals. Straight forward drag and drop interfaces allow the user to switch the way the data is displayed. It is the perfect tool for previewing, organizing, and ultimately analyzing your data. It is not the perfect tool for scaling, productionalizing, or even distributing your data.
Finally, Pivot Tables has a friendly cousin in most spreadsheets known as the Pivot Chart. Pivot Charts allow the same visualization as regular charts do for regular tables. It is a very quick and useful solution, but pivot charts can get a little clunky at times.
So just remember:
Spreadsheets are just digital graph paper. They are powerful ways to manipulate tables and build clean and insightful charts (graphs). Excel dominates the market, but other flavors exist. In the end, they are basically just knock-offs of Excel (though often useful ones). If you want to take your analysis and spreadsheet knowledge to the next level; learn complex functions, sort and filter, and spend time getting comfortable with pivot tables.
Thanks for reading. Stay tuned for more Practical Explainer.
Want to train, learn, and practice these more powerful spreadsheet abilities? Checkout our sponsor.
