Google Sheets – Controlling Rows & Columns (Part 1)

In this post, let’s look at various ways you can modify the rows and columns on your sheet.

Part 1 (this post)

  • Changing the row size of rows and columns (to fit data / manually / enter size)
  • Selecting a whole row or column
  • Inserting and deleting a row or column
  • Inserting and deleting specific cells
  • Splitting text into columns (recently added)

Part 2 (next post)

  • Selecting multiple rows/columns
  • Deleting unwanted rows & columns
  • Hiding rows and columns
  • Freezing rows and columns

Changing row size of rows and columns

Here I have a set of data, the number of goals scored by some players in a team.

By default, the column size is 100, but I’d like to make the table smaller, as at the moment I think it’s a bit too wide. There are 3 main ways to change row or column size:

  1. Double clicking on the edge of it so it fits the size of the data in the cells
  2. Manually change the size of the row or column by dragging it
  3. Typing in the specific size you want

Changing size to fit

I want to reduce the width of column A to the width of the longest word (e.g. Total).

Go to the right-hand side of the column A block where it meets column B. Your cursor will change to a little arrow pointing to the right.  Double-click here.

This changes the column width to the size of the longest piece of data in that column.

By the way, I’ve had to take photos of the screens to be able to show you the cursor changes, hence why they are a little dark.

Manually changing the size of the column

In column H I want to reduce the width but I want to control the width I want.

So, similar to above, go to in between column H and column I, and when your cursor changes to a little right-facing arrow, hold down the mouse button, a long vertical line will appear and drag this to the left (or to the right to make it bigger) and let go of the mouse button when you get to the size you want.

Typing in the specific size you want

Sometimes you want an exact size to your column width or you may just want all your selected columns to be the exact same size. Let’s change that last Total column again.

Either right click in the middle of column H, or slightly to the right of the column H header, you will see a triangle, if you left-click that it will bring up the same menu as right-clicking. It’s personal preference which you use.

Now, click “Resize column”.

Enter the figure you want, e.g. 80.

You can do exactly the same for rows, but this time to resize to fit or manually change a row, click on the border between the row and the row below (an upwards-facing arrow will appear).


Selecting the whole row or column

Click on the middle of the row or column label to select the entire row or column.

To select the whole page, click on the top left where the rows and columns meet, just under the formula bar “Fx”. This is useful for things like change the font or font size of everything on the page.

Sheets5 - 14

Inserting a row or column

To add an extra row, select the whole row (as described above). Right click to bring up the menu and select “Insert above” or “Insert below”, which will do what it says, insert a row above the one selected or below it. In this example, Rox has joined the team, so I click on Jen’s row and choose “Insert 1 below”. Then I fill in her details.

It’s the same process for inserting extra columns, but this time the options in the menu change to, “Insert left” or “Insert right”, to add an extra column to the left or right of the one selected. In this example, the team has played an extra game, so I’ve added that after Game 7, by clicking on the Game 7 column and then selecting “Insert 1 right”.


Deleting a row or column

Similar to above, to delete a row or a column, select the whole row or column, right click to bring up the menu and select “Delete row” or “Delete column”.

A more recent addition to Sheets, is the ability to insert or delete a row or column, with just a cell highlighted. For example, Here I’ve got a row with comments that I want to remove.

I could right-click on the side and select delete row from the menu, or I can select any cell in that row and then go to the Edit menu and choose “Delete row”. This will delete the entire row. The same applies to inserting a row, and inserting or deleting a column.


Inserting specific cells

Sometimes you want to add some cells but not a whole row or column, as this may affect other data on your sheet. In this example, I want to add another student to a list of test results, without affecting the averages, etc on the right.

Highlight the cells where you want to insert the new ones.

Click the Insert menu then “Cells and shift down”.

This will move the data down without affecting the rest of the row.

Similarly, we can a specific column without affecting the data in the rest of that column. Here I want to add names before the maximum and minimum results. So, select the test results.

Then from the Insert menu, click “Cells and shift right”.

As you can see, it moves the data to the right.

I then add the names. As you can see, it hasn’t affected the column with the averages above.

Note, that in this particular example, my data includes some formulas to calculate the averages, etc. Inserting the cells doesn’t update the formulas, so these will need to be updated manually.


Deleting specific cells

Similar to inserting specific cells, we can delete specific ones. Here, I’m going to undo what I inserted earlier. Select the cells you want to delete. 

This time from the Edit menu, select “Delete cells and shift up”.

This deletes the selected cells and moves the data up without affecting the rest of the column.

Now, let’s delete the names next to the minimum and maximum results I added earlier. Select the names.

From the Edit menu, select “Delete cells and shift left”.

This deletes the names without affect the column above.


Splitting text into columns

Sometimes we have text which we want to split into columns. This could be where we want to split their first name and surname, a list of items we’ve imported from somewhere else and we want to break into their separate components. Sheets contains a simple tool to do this for you. Let’s start with an example:

Here we have some student names and the teacher wants to split these into separate columns, for example, maybe they want to then sort them by their surname.

Select the text you want to split.

Go to Data>Split text into columns.

You will then be asked for where you want to separate the text. By default, it’s by commas but if you click the menu you will be presented with other options.

So, we can split the text by commas, semicolons, full stops, or spaces, or in fact anything we want by clicking on “Custom”. The names of our students have a space separating the first name and surname. So, we click “Space”.

This automatically splits the names, putting the first name in one column and the surname in another.

It’s common to have names where their surname is first, then it’s followed by a comma and their first name.

When we go to Data>Split text into columns, as the default is a comma separator, it automatically has split the names into two columns.

We can also set the separator we want. Click on “Custom” then add the separator you want.

Here we have a list of names where each name is separated by a forward slash. Select the text.

Select “Custom” then type “/” in the box. As you can see the list has been divided into 4 different columns.


See part 2 for more ways you can control your rows and columns.

This post is taken from my book “Beginner’s Guide to Google Sheets“, available on Amazon here.

Want to learn more about Google Workspace and Apps Script? The books below are available on Amazon. Just click on a book! (Affiliate links).

JavaScript Fundamentals for Apps Script users

a

Google Apps Script Projects 1
Google Apps Script Projects 2