Formatting a spreadsheet is really about adding style so that your data is easy to read and interpret. When a spreadsheet is properly formatted, you can glance over the data and understand it much more quickly than flat data alone.
Use formatting in Google Sheets to make it easy to read and collaborate on spreadsheets, right inside of a web browser. Here are just a few of the formatting tools that we'll cover in this tutorial:
- Bold cells draw a viewer's attention and let them know that the data in a selected cell is more important than the surrounding Google cells.
Alignment, which allows you to put the content to the left, center, or right side of a cell, creates natural whitespace that makes a spreadsheet easier to read.
- Text wrapping can help oversized blocks of text fit in a single cell without wrapping and extending to additional cells.
In this tutorial, I'm going to jump around and show you all of the essential skills you need to format Google spreadsheets. You'll learn how to edit Google Sheets with essential formatting for easy reading. Let's jump in!
How to Edit Google Sheets (Watch & Learn)
In the screencast below, I'll give you an "over the shoulder" look at how I handle formatting a raw spreadsheet in Google Sheets. You'll get a chance to see how I think about adding styles and formatting to emphasize cells.
Keep reading to see an illustrated guide for creating great-looking Sheets.
How to Format Data
One of the most important parts of formatting Google cells is making sure that your data has the correct format for the type of data it holds. Even though they're both numerical values, you would a dollar amount, and the number of items your purchased to be formatted differently.
This is why formatting matters. When I glance at a number, I should at least have a basic idea of what type of data we might be talking about. Let's look at some tweaks you can apply to data to make it more understandable.
1. Currency Formatting
Spreadsheets often hold financial information, so it makes sense that formatting should show that. While "$504.53" clearly refers to a currency amount, "504" in a cell isn't as intuitive. That's why formatting numbers as currency is so useful.
To format data, always start off by clicking and highlighting the data that you want to apply the format to. In the example screenshot below, both the "Amt" and "Hourly" columns would benefit from currency formatting. Then, click on the dollar sign just above the data to apply currency formatting to the entire selection.
This is a perfect example of how formatting makes a spreadsheet more meaningful. The currency symbols quickly give the clues to the user that these amounts are more than simple numbers.
For our readers in other regions, don't miss out on the fact that you can change the currency symbol that's presented. With your data highlighted, click on the 123 icon, and then choose More Formats > More Currencies as you can see in the screenshot below.
Choose another currency symbol to localize your Google spreadsheets. Just remember that this doesn't automatically convert the amounts, it simply changes the currency symbol.
For our readers in other regions, don't miss out on the fact that you can change the currency symbol that's presented.
2. Decimal Points
You might also want to tweak the number of decimal points that show in your spreadsheet. There are really two key scenarios that this comes in handy:
- Your data has decimal amounts in it, but you don't want to show them in the cell for simplicity's sake. (For example, who typically cares about 24 cents at the end of $1,000,000 in a cell?)
- Decimal points are showing in your data, but you're working with data that shouldn't be fractional. (0.4 people can't be finished with a project, but someone could be 40% finished)
In either case, you can tweak the number of decimal points that are showing in the spreadsheet.
This illustrates an important concept about spreadsheets: the data that's behind the scenes can differ from how you're viewing it. You can always click in a cell and check the formula bar, as you can see in the screenshot below, to check the real value of a cell.
To change the number of decimal points that you see showing in the cell, find the icons highlighted in the screenshot below, just above the spreadsheet. Highlight the cells you want to tweak, then click the appropriate cell to either increase or decrease the number of decimal points showing.
Don't forget that you can also use one of the starter styles from the number dropdown to skip styling each cell one-by-one.
3. Date Formats
Let's say that you've got dates in a format like "2/10/2018." What if you wanted to show that as "February 10th, 2018?"
You don't have to re-type the data as simple text in the desired format. You can simply change up the formatting and how the data is presented. Highlight your date data, and then access the Format > Number > More Formats > More date and time formats... option as you can see in the screenshot below.
This pop-up menu really allows you to customize the way that you'll format your date data. There are plenty of pre-built options that you can choose from the list as the screenshot below shows.
Use one of the date formats as a starting point, then tweak it using a dropdown for the exact style you're looking for.
How to Align Data in Google Sheets
Text alignment describes what edge of the cell that the content aligns with. To apply alignment, simply highlight the text, and use the alignment dropdown that I show in the screenshot below.
Numeric values are often easier to read when right-aligned, while text is usually easier when it's left-aligned.
How to Format Google Spreadsheets With Borders
Borders, lines applied to the edge of Google cells, can really help you separate your data and make it stand out from the crowd.
In the example below, you'll see me apply borders by highlighting data and choosing a border from the dropdown option. The icons each represent a border option you can apply to data, whether you want to draw lines between each and every cell, or simply surrounding your data on the outer edges.
I think that borders have a great way of really setting data off and make it readable. I also like to use borders when I have a standalone data box separate from my data, such as an "inputs" box where I set the key data used in formulas.
Bonus Tip: How to Use the Format Painter
So far, you might have been applying formats one-by-one, cell-by-cell. A solution that will save you many hours of applying formats is the format painter. Thanks to this feature, you can easily take a format and apply it to other cells.
To start using the format painter, start off by clicking in a cell that you want to copy the format from. Next, find the paint roller icon on the menu, and click on it.
Now, simply click and drag over any Google cells that you want to take on the same style. Those cells will take on the same style as the source.
Recap & Keep Learning
Google Sheets proves once again that it's a more-than-capable replacement for spreadsheet apps with plenty of formatting options. You've just learned how to edit Google Sheets and format Google spreadsheet cells. Remember while you're applying formatting, that it's all about the content. Formatting is just the icing on the cake that draws attention to the right places.
If this tutorial got you interested in using Sheets, don't miss the tutorials below to learn more:
- How to Use AI in Google Sheets in 60 SecondsAndrew Childress29 Sep 2017
- How to Create Web Input Forms With Google SheetsAndrew Childress17 May 2017
- How to Make Professional Charts in Google SheetsAndrew Childress04 Apr 2017
What are your favorite formatting tweaks to apply? Let me know in the comments section below if you've got any tips on how to format Google spreadsheets to share.