Sort and Filter Data

Spreadsheet tools help you delve into your data and lift its stories to the surface. A basic step in organizing your data is to sort a table by a particular column, to quickly view its minimum and maximum values, and the range that lies in between. A related method is to filter an entire table to display only rows that contain certain values, to help them stand out for further study among all of the other entries. Both of these methods become more powerful when your spreadsheets contain hundreds or thousands of rows of data.

To learn how to sort and filter, let’s explore a large dataset of around 3,000 readers of this book who responded to a quick public survey about their general location, prior level of experience and education, and goals for learning data visualization. If you haven’t already done so, fill out the quick survey form to contribute your own response, and also to give you a better sense of how the questions were posed.

  1. Open this Google Sheet of Hands-On Data Visualization reader public survey responses in a new tab in your browser.

  2. Login to your Google Sheets account, and go to File > Make a Copy to create your own version that you can edit.

  3. Before sorting, click the upper-left corner of the sheet to select all cells, as shown in Figure 2.18. The entire sheet should become light blue to show you’ve selected all cells.

Click the upper-left corner to select all cells before sorting.

Figure 2.18: Click the upper-left corner to select all cells before sorting.

Warning: If you forget to select all cells, you might accidentally sort one column independently of the others, which will scramble your dataset and make it meaningless. Always select all cells before sorting!

  1. Go to Data > Sort Range to review all of your sort options. In the next screen, check the Data has header row box to view the column headers in your data. Let’s sort the Experience with data visualization column in ascending order (from A-Z), as shown in Figure 2.19, to display the minimum at the top, the maximum at the bottom, and the range in between.
Go to Data - Sort Range, check the header row box, and sort by Experience with dataviz in ascending order.

Figure 2.19: Go to Data - Sort Range, check the header row box, and sort by Experience with dataviz in ascending order.

Scroll through your sorted data and you’ll see that over 1,000 readers rated themselves as beginners (level 1) with data visualization.

Tip: When working with large spreadsheets, you can “freeze” the first row so that column headers will still appear as you scroll downward. In Google Sheets, go to View > Freeze and select 1 row, as shown in Figure 2.20. You can also freeze one or more columns to continuously display when scrolling sideways. LibreOffice has a same option to View > Freeze Rows and Columns, but Excel has a different option called Window > Split.

In Google Sheets, go to View - Freeze to select the number of rows to continuously display when scrolling downward.

Figure 2.20: In Google Sheets, go to View - Freeze to select the number of rows to continuously display when scrolling downward.

  1. Now let’s try filtering your sheet. Go to Data > Create a Filter, which inserts downward arrows in each column header. Click on the downward arrow in the Occupation column, and see options to display or hide rows of data. For example, click the “Clear” button to undo all options, then click only educator to display only rows with that response, as shown in Figure 2.21. Click “OK”.
Go to Data - Create a Filter, click the downward arrow in the Occupation column, select only educator.

Figure 2.21: Go to Data - Create a Filter, click the downward arrow in the Occupation column, select only educator.

Now your view of reader responses is sorted by experience, and filtered to show only educators. Scroll through their one-sentence goals for learning about data visualization. How to do they compare to your own goals? In the next section, we’ll learn how to start analyzing your data with simple formulas and functions.