Select your Spreadsheet Tools

Which spreadsheet tools should you use? As we describe in more detail in Chapter 1: Choose Tools to Tell Your Data Story, the answer depends on how you respond to different questions about your work. First, is your data public or private? If private, consider using a downloadable spreadsheet tool that runs on your computer, to reduce the risk of an accidental data breach that might happen when using an online spreadsheet tool that automatically stores your data in the cloud. Second, will you be working solo or with other people? For collaborative projects, consider using an online spreadsheet tool that’s designed to allow other team members to simultaneously view or edit data. Third, do you need to import or export data in any specific format (which we’ll describe in the next section), such as Comma Separated Values (CSV)? If yes, then choose a spreadsheet tool that supports that format. Finally, do you prefer a free tool, or are you willing to pay for it, or donate funds to support open-source development?

Here’s how three common spreadsheet tools compare on these questions:

  • Google Sheets is a free online spreadsheet tool that works in any modern web browser, and automatically stores your data in the cloud. While data you upload is private by default, you can choose to share it with specific individuals or anyone on the internet, and allow them to view or edit for real-time collaboration, similar to Google Documents. Google Sheets also imports and exports data in CSV, ODS, Excel, and other formats. You can sign up for a free personal Google Drive account with the same username as your Google Mail account, or create a separate account under a new username to reduce Google’s invasion into your private life. Another option is to pay for a Google Suite business account subscription, which offers nearly identical tools, but with sharing settings designed for larger organizations or educational institutions.
  • LibreOffice is a free downloadable suite of tools, including its Calc spreadsheet, available for Mac, Windows, and Linux computers, and is an increasingly popular alternative to Microsoft Office. When you download LibreOffice, its sponsor organization, The Document Foundation, requests a donation to continue its open-source software development. The Calc spreadsheet tool imports and exports data in its native ODS format, as well as CSV, Excel, and others. While an online collaborative platform is under development, it is not yet available for broad usage.
  • Microsoft Excel is the spreadsheet tool in the Microsoft Office suite, which is available in different versions, though commonly confused as the company has changed its product names over time. A paid subscription to Microsoft 365 provides you with two versions: the full-featured downloadable version of Excel (which is what most people mean when they simply say “Excel”) for Windows or Mac computers and other devices, and access to a simpler online Excel through your browser, including file sharing with collaborators through Microsoft’s online hosting service. If you do not wish to pay for a subscription, anyone can sign up for a free version of online Excel at Microsoft’s Office on the Web, but this does not include the full-featured downloadable version. The online Excel tool has limitations. For example, neither the paid nor the free version of online Excel allows you to save files in the single-sheet generic Comma Separated Values (.csv) format, an important featured required by some data visualization tools in later chapters of this book. You can only export to CSV format using the downloadable Excel tool, which is now available only with a paid Microsoft 365 subscription.

Deciding which spreadsheet tools to use is not a simple choice. Sometimes our decisions change from project to project, depending on costs, data formats, privacy concerns, and the personal preferences of any collaborators. Occasionally we’ve also had co-workers or clients specifically request that we send them non-sensitive spreadsheet data attached to an email, rather than sharing it through a spreadsheet tool platform that was designed for collaboration. So it’s best to be familiar with all three commonly-used spreadsheet tools above, and to understand their respective strengths and weaknesses.

In this book, we primarily use Google Sheets for most of our examples. All of the data we distribute through this book is public. Also, we wanted a spreadsheet tool designed for collaboration, so that we can share links to data files with readers like you, so that you can view our original version, and either make a copy to edit in your own Google Drive, or download in a different format to use in LibreOffice or Excel. Most of the spreadsheet methods we teach look the same across all spreadsheet tools, and we point out exceptions when relevant.

Sidebar: Common data formats

Spreadsheet tools organize data in different formats. When you download spreadsheet data to your computer, you typically see its filename, followed by a period and a 3- or 4-character abbreviated extension, which represents the data format, as shown in Figure 2.2. The most common data formats we use in this book are:

  • .csv means Comma Separated Values, a generic format for a single sheet of simple data, which saves no formulas nor styling.
  • .ods means OpenDocument Spreadsheet, a standardized open format that saves multi-tabbed sheets, formulas, styling, etc.
  • .xlsx or the older .xls means Excel, a Microsoft format that supports multi-tabbed sheets, formulas, styling, etc.
  • .gsheet means Google Sheets, which also supports multi-tabbed sheets, formulas, styling, etc., but you don’t normally see these on your computer because they are primarily designed to exist online.
Three data formats commonly seen on your computer—csv, ods, and xlsx—when displayed properly in the Mac Finder.

Figure 2.2: Three data formats commonly seen on your computer—csv, ods, and xlsx—when displayed properly in the Mac Finder.

Warning: Several tools in this book may not work properly on a Mac computer that does not display the filename extensions, meaning the abbreviated file format after the period, such as data.csv or map.geojson. The Mac operating system hides these by default, so you need to turn them on by going to Finder > Preferences > Advanced, and check the box to Show all filename extensions, as shown in Figure 2.3.

On a Mac, go to Finder-Preferences-Advanced and check the box to Show all filename extensions.

Figure 2.3: On a Mac, go to Finder-Preferences-Advanced and check the box to Show all filename extensions.