Sheet Smarts (Part I)

In the last chapter, we explored data analysis, from the analyst’s mindset to responsible data use. Now, it’s time to explore the one tool we’ll be using throughout this course: Google Sheets.

Think of a Google Sheet as a giant grid, like a massive table or a digital ledger. Understanding how to navigate and interact with this grid is our first big step. Every piece of information we enter, from a name to a number, lives in its own specific spot. Learning how to locate and refer to these spots is key to making our data work for us. Once we know our way around the sheet, we can begin transforming raw data into real insights.

Let’s start by exploring the fundamental building blocks of this grid.

Cells, rows, and columns

When we open a Google Sheet, we’ll see a vast collection of “boxes.” Each of these boxes is a cell. These cells are organized in a structured way:

• Columns: These are the vertical lines of cells, identified by letters at the top (A, B, C, and so on). Imagine them as categories or types of information, like a column for “Product Name,” “Price,” or “Date.”

• Rows: These are the horizontal lines of cells, identified by numbers on the left (1, 2, 3, and so forth). Each row is a complete record or entry for one item. For instance, a row might contain all the details for a single customer or a single sales transaction.

• Cells: The intersection of a column and a row forms a single cell. Every cell has a unique address, or cell reference, made up of its column letter followed by its row number (e.g., A1, B5, C10). This address helps us pinpoint exactly where a piece of information is located on our sheet.

Let’s try it out!

1. Open a new Google Sheet. The quickest way to do this is to type sheet.new into the web browser’s address bar and press “Enter.”

2. Once the new sheet loads, click any cell. As we click, notice how its cell reference (like A1, B3, C6) appears in the Name Box located just above column A. This Name Box always shows us the address of the currently selected cell.

3. Now, click cell A1. Let’s type the name in this cell and press “Enter.”

4. Next, click cell B2. Let’s type the number 500 in this cell and press “Enter.”

5. Now, try clicking on cell A1 again. Notice how the content (“Your Name”) appears both in the cell and in the Formula Bar above the column letters. The Formula Bar is where we’ll type and edit content, including formulas, later on.

With some initial data in place, we can now move on to making our sheet look more organized and professional. Let’s explore basic formatting.

Basic formatting

Think of a spreadsheet as a data-driven report. If it’s poorly formatted, with no clear headings, inconsistent spacing, and plain text throughout, it becomes difficult to navigate and interpret. By using basic formatting tools in Google Sheets such as bold text, color coding, and font size adjustments, we’re not just improving appearance. We’re visually organizing information, making it easier to scan, highlighting what matters most, and giving the sheet a clean, professional look that’s easier to understand at a glance.

Bold, italics, strikethrough

We can apply bold, italic, and strikethrough formatting using the toolbar in Google Sheets.

1. Click on any cell where we’ve entered some text.

2. Now, look for the Bold (B), Italic (I), and strikethrough ($\sout{S}$) icons in the toolbar, usually located at the top of the sheet.

3. Click the ‘B’ icon to make the text bold. Then try the ‘I’ and ‘$\sout{S}$’ icons to see how italic and strikethrough formatting work.

Font size

We change the font size by clicking on a cell with text, and using the drop-down in the toolbar (default is 10). Try increasing it to 14 or 18 to see the difference.

Font type

Next to the font size, we’ll find the font type drop-down, which is usually set to “Arial” by default. Let’s click it and explore other fonts like “Roboto,” “Century Gothic,” or “Georgia,” and choose one we like.

Text color and fill color

We’ll find the text color icon marked by an “A” with a color underline, and the fill color icon shown as a paint bucket in the toolbar. By clicking the drop-down arrow next to each, we can open the color palettes and choose different colors for the text and the cell background. This is a simple way to make key information stand out, or organize data visually.

Effective formatting in data analysis goes beyond aesthetics; it plays a critical role in enhancing clarity and communication. By strategically using elements such as bold headers and color highlights, we can draw attention to key figures and insights. This not only improves readability but also ensures that important information stands out, enabling both analysts and stakeholders to quickly interpret and act on the data.

The range operator

Sometimes, we don’t just want to refer to a single cell, but a group of cells that form a continuous block. This is where the range operator : comes in incredibly handy. It allows us to define a rectangular block of cells, much like selecting a section of text in a document or highlighting a group of items on a list.

Let’s look at a few examples to see how this works in action:

• A1:A4 refers to all cells from A1 down to A4 in column A. This is a single column range.

• A1:C1 refers to all cells from A1 across to C1 in row 1. This is a single row range.

• A1:D4 refers to all cells from A1 to D4, forming a 4-column by 4-row rectangle.

Let’s try it out!

1. We’ll start by entering some numbers in cells A1 through A5. To select the full range, click on cell A1 and drag down to A5, as this will lead to the entire range being highlighted.

2. With the range A1:A5 selected, let’s try applying a background color using the “Fill color” bucket icon in the toolbar.

Sheets and tabs

In Google Sheets, organization is key. As our analysis evolves, we often need a way to separate different pieces of data without juggling multiple files. That’s where sheets and tabs come in.

Just like a physical notebook has multiple pages, a single Google Sheets file, often called a workbook, can contain multiple sheets. Each sheet appears as a tab at the bottom of the window. This simple feature helps keep different datasets, reports, or calculations organized in one convenient place.

Imagine having one sheet for Sales Data, another for Customer Info, and a third one just for your Summary report. This way, everything stays connected and easy to find.

Adding a new sheet

When we open a new Google Sheets file, it starts us off with a single sheet, usually named Sheet1. But rarely do we work with just one table or dataset. Let’s say we want to begin a fresh analysis or store calculations separately. How do we create space for it?

Here’s how we do it:

1. Look at the bottom-left corner of the Google Sheets window. We’ll see a “+” icon next to the sheet tabs; this is the add sheet button.

2. Click the “+” and a new sheet will be added to the workbook.

Renaming a sheet

Now that we’ve added a new sheet, let’s make our workbook easier to navigate.

By default, Google Sheets names new sheets something generic like Sheet1, Sheet2, and so on. That might work for small projects, but as our workbook grows, these names can quickly become confusing. Imagine trying to remember whether our calculations are in Sheet3 or Sheet7; it’s simply not efficient.

To stay organized, we should rename our sheets to something meaningful. This way, we can tell at a glance where our raw data is, where our charts live, and where we’ve done our calculations.

Here’s how we can rename a sheet:

1. Locate the tab at the bottom of the workbook (e.g., Sheet5). Double-click on the tab name.

2. It will become editable, type a new name like Raw data or SalesQ1.

3. Press “Enter” to save the new name.

Navigating between sheets

Now that we’ve added and renamed multiple sheets, it’s time to learn how to move between them efficiently.

When we’re working with just two or three sheets, it’s pretty straightforward. We can simply click the sheet tab at the bottom of the window, say, click Sales Data to review the data, then click Customer info to jump into the analysis.

But what happens when our workbook starts growing, maybe 10, 20, or even more sheets? Scrolling back and forth just to find the right one can be time-consuming. That’s why it helps to know a smart navigation method.

Smart navigation using hyperlinks

To move around more quickly, especially in structured reports or dashboards, we can create clickable links that jump from one sheet to another.

Here’s how to do it:

1. In any sheet, click into an empty cell (e.g., A1) and type: Go to Customer Info Sheet.

2. Right-click the cell and select “Insert link” or press Ctrl + K (Windows) / Cmd + K (Mac).

3. In the link box, begin typing the name of the target sheet, and select the correct sheet from the suggestions.

Now that cell works like a hyperlink. Clicking it will take us directly to the sheet we linked. This feature is incredibly useful for creating interactive dashboards or easy-to-navigate reports, allowing users to quickly jump to relevant sections without manually searching through tabs.

Wrap up

We’ve just taken our first big steps into understanding the fundamental layout and navigation of Google Sheets. We’ve learned how cells, rows, columns, and sheets work together, and how to apply basic formatting to make our data presentable. In our next lesson, we’ll look into making these cells truly powerful by performing calculations.