Format your Google Sheet tables so they look good GREAT

A collection of formatting tips for tables in your Google Sheets.

Google Sheets formatting for tables

Formatting Tables

Let’s start with a simple table, completely devoid of any formatting:

Table no format

Header rows

Go for bold, center-aligned and wrap the text, so it all shows.

Google Sheets formatting column headings

Aligning columns

Let’s align those columns, they’re messy!

Table alignment GIF

Center column headings, ID numbers, or other standardized entries.

Left align text.

Right align numbers (which they are by default). Really the only exception to this rule is for numbers that are not really numbers, i.e. they’re not being compared against each other or being used in any sort of calculations. For example, the ID numbers above can be center aligned.

Right align dates (which they are by default). However, if you’re working with just a year, as in the example above, you can get away with center aligning, just be consistent.

Formatting data

Choose appropriate formatting options for the data in your tables.

Add thousand separators to big numbers above a thousand. Add currency signs to financial numbers to add context.

Choose an appropriate number of decimal places. For example 2 decimal places if you need that level of detail to show cents on the dollar, but remove decimal places if they’re not needed for large numbers:

Google Sheets formatting format as currency

I could stop the article here, as the table is now sufficiently formatted to maximize legibility.

However, for tables that are part of dashboard reports or are being presented to clients, you can add colors to match your branding, or bring additional context to your numbers.

Alternating colors in tables

Sure you can do this manually, but it’s way easier and quicker to do with the Alternating Colors tool under the formatting menu.

Simply highlight your whole table and then open up the Alternating Colors option sidebar. Select the color scheme you want, whether you have a header or footer row, and even choose custom colors if you wish:

Google Sheets formatting alternating rows

Adding additional context with colors and indicator arrows

Hidden in the Custom Number Format menu is a conditional formatting option for setting different formats for numbers greater than 0, equal to 0 or less than zero.

It’s a great tool to apply to tables in your Google Sheets dashboards for example, where the data is changing. By changing the color of a table cell’s text as the data changes, you can bring it to the attention of your user.

Consider the following sales table which has a % change column:

Google Sheets percent table

Now take a look at the same table with colors and arrows added to call out the % change column:

Google Sheets table

It’s significantly easier/quicker to read and absorb that information.

How to add this custom formatting

1. Somewhere in your Sheet, or a new blank Sheet, copy these three CHAR formulas (you can delete them later):

Google Sheets Char formula

=char(A1)
=char(A2)
=char(A3)

Now, copy and paste them as values in your Sheet so they look like column C and are not formulas any longer.

(You copy as values by copying, then right clicking into a cell and select Paste special > Paste values only…)

You’ll need to copy these to your clipboard so you can paste them into the custom number format tool.

2. Highlight the % column and go to the custom number formatting menu:

Google Sheets custom number format menu

3. Change the 0.00% in the Custom number formats input box to this:

[color50]0% ▲;[color3]-0% ▼;[blue]0% ▬

as shown in this image:

Google Sheets custom number format

What you’re doing is specifying a number format for positive numbers first, then negative numbers and then zero values, each separated by a semi-colon.

Copy in the symbols from step 1 (you’ll have to do this separately for each one).

Use the square brackets to specify the color you want e.g. [color50] for green.

Read more about custom number formatting here: Excel custom number formats

(Yes, it’s an Excel article, but the rules are the same.)

Formatting Titles

I’d recommend resisting the urge to use fancy fonts, or cursive fonts designed to mimic handwriting, as they make your work look less professional.

I rarely look past the default Arial, which is fine in most cases, or Verdana as a fine alternative. If I’m looking for something to make my sheet or dashboard stand-out that bit more, then I use the Montserrat font.

Also, I wouldn’t use more than two different fonts in a sheet. Almost always I stick with one.

In dashboards, I often create “tiles” for headers or metrics. I’ll add the title (or metric), a background color to the cell that matches my color scheme and a thick border to the left or right with a contrasting color. ThenI can change the font and font-size to my needs, e.g.:

Title tile format

Updates to the Google Sheets Training Courses

Sep 18 announcement: Whew! This was a big task, but today I’m proud to announce that my video training courses are bang up-to-date! I sent out emails to students earlier today, announcing updates to 40 videos in my Google Sheets training courses.

Specifically:

Read on for more details.
Continue reading Updates to the Google Sheets Training Courses

How to apply conditional formatting across an entire row in Google Sheets

Conditional formatting is a super useful technique for formatting cells in your Google Sheets based on whether they meet certain conditions.

For example, you could use it to apply background colors to cells based on the value in the cell.

You can go further than this though, and apply the formatting across an entire row, based on the value in a single cell.

For example, if the continent is “Africa” in column C, you can apply the background formatting to the entire row (as shown by 1 and 2):

Conditional formatting across an entire row

Five steps to apply conditional formatting across an entire row

It’s actually relatively straightforward once you know the technique using the $ sign (Step 5).

Step 1. Highlight the data range you want to format

The first step is to highlight the range of data that you want to apply your conditional formatting to. In this case, I’ve selected A2:C13:

Conditional Formatting step 1

Step 2. Choose Format > Conditional formatting… in the top menu

Open the conditional format editing side-pane, shown in this image, by choosing Format > Conditional formatting… from the top menu:

Conditional Formatting menu

Step 3. Click on “Cell is not empty” in the sidebar

Google Sheets will default to applying the “Cell is not empty” rule, but we don’t want this here.

Click on the “Cell is not empty” to open the drop-down menu:

Conditional Formatting sidebar

Step 4. Choose “Custom formula is” at the end of the drop-down list

Scroll down to the end of the items in the drop-down list and choose “Custom formula is”. This will add a new input box in the Format cells if section of your editor:

Conditional Formatting custom formula

Step 5. Enter your formula, using the $ sign to lock your column reference

In this example, I want to highlight all the rows of data that have “West” in column A. In this new input box, enter the custom formula:

Conditional formatting across an entire row

The key point to understand is that you lock the column you want to base your conditional formatting on by adding a $ (dollar sign) to the column reference.

I start inputting the first cell of my highlighted range: = A2

Then I add the $ (dollar sign) in front of the A only: = $A2

Then I add the test condition, in this case whether the cell equals “West”: = $A2 = "West"

As the conditional formatting test is applied across each row, the value from the first cell in column A is used in the check.


More examples of conditional formatting across an entire row

Based on a threshold value

This is a super useful application of this technique, to dynamically highlight rows of data in your tables where a value exceeds some threshold.

In this example, I’ve highlighted all of the students who scored less than 60 in class, using this formula in the custom formula field:

= $C2 < 60

Conditional formatting across an entire row

Based on checkboxes

If you haven't heard of or used checkboxes yet you're missing out. They're relatively new to Google Sheets (mid 2018) and are super useful. Read more about them here.

When a checkbox is selected it has the value TRUE, and when it is not selected the cell has the value FALSE. So we can use that property in our custom formula:

= $B2 = TRUE

Conditional formatting across an entire row with checkboxes

My new course, Data Analysis with Google Sheets, is open for enrollment!

Data Analysis with Google Sheets course

Are you drowning in data, but not using it to make better decisions?

Do you run a small business and want to use your data to inform your strategy?

Has your business migrated from Excel to Google Sheets, and you’re looking to further your skills?

My new course, Data Analysis with Google Sheets, is now open for enrollment!

In this course, you’ll learn how to:

  • Understand and describe what your data tells you
  • Predict what things might look like in the future
  • Understand your customers to better serve them
  • Discover what factors affect your business
  • Best practices for doing all of this…
  • …and much, much more!

Working with real-world examples, including example datasets from industries like sales, marketing, real-estate and social media, you’ll learn how to make the most of your business data today.

Now you can learn data analysis, using the same techniques and best practices I’ve spent the past 15 years learning.

This course will teach you the key foundational techniques in data analysis, even if you’re new to this topic.

Here’s what the first batch of students had to say about the course:

What others say about the course:

Charlie Headshot“I will definitely be using the statistical functions to test whether the conclusions of my analysis are statistically significant or not.

I have also learnt a whole number of little tips and tricks that were dropped in throughout the course.

Ben is a seriously knowledgeable tutor, and presents the information in a way that is easy to follow.”

Charlie Pearson, PricewaterhouseCoopers


Geordie Headshot“I was really excited by the idea of using an application I was familiar with, Google Sheets, to complete data analysis that had seemed so challenging and foreign before.

I think people like me, who are not data scientists but have a passion for statistics and analytics, and want to find an easy entry level to start playing with data, would benefit from this course.

I absolutely love this course!”

Geordie Frost, Bang Digital


Devin Headshot“I wanted to learn more about what I can do with Google Sheets to get an idea about how my agency can benefit. My SEO company uses Google almost entirely for all our project management, etc.

I take every course from Ben Collins. I’m always looking to up my data game. If you can understand your Data you can make correct decisions about your business and *almost* guarantee success.”

– Devin Schumacher, The Search Marketing Company


Enroll now to get:

✅ Actionable, 5 – 10 minute video lessons teaching key data analysis topics

✅ All raw datasets for you to follow along

✅ All solution files for your own use

✅ Access to the private course Facebook group for Q&A

✅ Certificate upon completion

Are you ready to learn how to use Google Sheets to make data-driven decisions?

Any other questions? Leave a comment or drop me a line here.

Exciting new features coming to Google Sheets

26 July 2018: I’ve been at the Google Next cloud conference this week, in San Francisco. They announced a ton of exciting new features and products for both G Suite and the Google Cloud Platform.

Google Next conference

The Google Sheets product team announced a bunch of exciting new features coming soon to Google Sheets.

Here’s a brief recap:

5 million cells! (Sneak peek) 🔥

Nice! A big jump in the size of data we can work with in Sheets. This will open up Sheets for bigger data projects now.

This was shown as a “sneak peek” so I don’t have a date as to when this change will happen. Hopefully soon, since they announced it!

Slicers (sneak peek) 🔥

This is a great addition for those of us who build reports and dashboards. Slicers are like checkbox buttons we can add to pivot tables and charts to make them much more interactive from a user stand point.

You’ll be able to add a slicer for a given field so that a user can then filter to just see the data they want.

It’ll be much more functional and elegant than the data validation drop-down method or checkbox methods you can use at the moment.

Google Sheets slicers

Charting upgrades (sneak peek) 🔥

It’s great to see charts getting some love! It’s one area where Google Sheet has fairly limited functionality, but we’ll soon have much more granular control over how our charts look.

For example, the updates will include the option to color datapoints individually (as shown in this image):

Google Sheets chart update

Pivot Table upgrades (recently launched) 🔥

Pivot tables recently got a facelift, with a new, more user-friendly UX.

Even more useful though, pivot tables now have the option to group data (for example to group dates into months, or quarters etc.) and drill-down on data (so you can select an aggregated record and see all the data behind it with a single click).

These are really, really strong updates to Pivot Tables and dramatically increase the power of pivot tables.

Google Sheets Pivot Tables

I’ll be working on updating the Data Cleaning and Pivot Table course later this summer to showcase the new UX and features.

BigQuery Data Integration (sneak peek) 🔥

There’s been a huge buzz around BigQuery this week, so it was only natural that they announced a native connector for Sheets and BigQuery. It’s in beta pre-release at the moment.

I’ve enjoyed learning more about BigQuery this week and I’m really excited to start using it to build data pipelines involving Sheets and/or Data Studio.

BigQuery to Google Sheets connector

Partner Integrations (sneak peek) 🔥

The team announced several new data integrations during the session. They spent time discussing what they’re working on to bring data from web services into Sheets so you can analyze it.

Three new integrations were announced:

Salesforce and Sheets

You’ll soon be able to export Salesforce data into Sheets with a single click. Salesforce will also be rolling out a feature where you can work on your data in a Google Sheet that is embedded inside of Salesforce.

Sheets saved in Box

You’ll soon be able to work with Google Docs but save the files into your Box account, i.e. use Box instead of Drive as your cloud storage. This makes a lot of sense if you’re already setup on the Box platform.

The team did a live demo showing the collaborative features live from a Box hosted Google Slide deck. Super slick!

SAP to Sheets

You’ll soon be able to export directly from SAP to Sheets.

Other notable updates in the works 🔥

> Text to columns will soon support fixed width splits, which is a useful upgrade.

> Continuing improvement of the Explore feature, which lets users ask questions about their data and uses natural language machine learning to extract answers and suggest insights.

> Improved printing options to meet enterprise needs.

> Images in cells, which stay with that cell even when you move it or insert other rows or columns. Currently you can insert floating images or use the IMAGE function to insert into a cell. Neither is ideal however, so this is a nice touch.

The session recording

Check out the recording of the session from the Google Next 18 conference: