Google Sheets – Formatting your data

Once you’ve added your data onto a sheet, you’ll probably want to format it in some way. In this post, we’ll look at formatting data in Sheets.

By default, numbers entered are considered just that, normal numbers, but sometimes you want specific types of numbers, e.g. percentages, currency, dates, times, scientific, numbers to certain decimal points.  Changing the format is easy and can be as easy as a single click.

On the menu shortcut toolbar, you’ll find buttons to change your numbers into a default currency, a percentage, to increase/decrease the number of decimal points, and the shortcut to the number format menu.

Changing to currency format

In this example, I have a simple home budget, which I want to change to pounds.

1) Select the data you want to change.

2) Click on the currency symbol (yours may be different depending on your language settings).

This changes the format of the numbers to the default currency.

---

Changing to percentages

In this example, I have a table where I want to add some exam marks which are percentages.

1) Select the cells where your percentage marks are going to go, including any formulas you may have, for example, here the last row works out the average mark, which of course I also want to be a percentage. Then click on the % sign in the toolbar.

2) Type in your marks (just the numbers, don’t type the percentage signs or decimal places), and as you’ll see they appear automatically as percentages.

By default, the percentages are to 2 decimal places, which is a little excessive in this case. So, let’s reduce the number of decimal places by 1.

3) Select the data and click on the reduce decimal point button on the toolbar once.

The numbers are now to one decimal place. If I wanted to increase the decimal place I would have used the button to the right with the double 00.

You may be wondering why I selected and formatted the empty cells first and not after the numbers have been entered. Let’s see what happens if you do that.

Clicking on the numbers then choosing the percentage format, produces numbers that are 100x too big.

In order to produce the correct percentages, the original numbers need to be divided by 100, e.g. Fred’s mark would be 0.76, changing this to a percentage would then produce the correct 76%. It’s just something to watch out for, as it’s a common mistake.

---

Dates and times

There are more formats available and these are accessible via the Numbers menu, by clicking on the Numbers button. (This menu is also available via the menu Format>Number).

I have a sheet set up to work out the hours worked by a teacher. Here we’re going to work with dates, times and hours.

Here’s the whole process, then below are the step-by-step instructions.

1) First, let’s populate the sheet with the dates worked. If I type in just the day and month and press Enter. The sheet will automatically add the year, as this is the default format. It will also always put the current year, if not otherwise stated.

Let’s say I’m not interested in the year. So, let’s change the format to just day and month.

2) Select the dates and click the number button (123) in the toolbar. The numbers menu will appear, then select “More Formats” and then “More date and time formats”.

There are lots of formats here and you also have the option of creating your own, but for now let’s choose the day/month one at the top.

3) Click on “05/08” and the details of the format will appear at the top. In this case, it will produce a day with double digits, followed by a forward slash, followed by a month with two digits. Click on “Apply”.

The dates are now formatted (DD/MM).

---

Now let’s add the times.

4) Type in the times using this format HH:MM, i.e. 12:30. If you use, for example, 12.30, the sheet will think it’s a normal number.

Typing in the number using : means the sheet will automatically recognize it’s a time. In this example, I’ve used the 24h clock which is the most common format and is the easiest to work with on a sheet, but you can use a 12h format, but you will have to format it as that.

---

I now want to work out the hours worked, subtracting the start times in column C from the finish times in column D. I’m going to add a simple formula to work this out for me.

5) Type in the formula =d3-c3 in cell E3 and press Enter.

Fortunately, Sheets is clever enough to realize that the numbers in column C and D are times and it puts the answer in column E in terms of hours and minutes.

Now we’re going to copy the formula in E3 across cells E4 to E7. There’s a quick shortcut to do this, which beats typing in the formula every time.

6) Click on cell E3 and then click on the little square on the bottom right of the cell. The cursor will change to a little black cross. Hold it down and drag it to cell E7. The cells should fill with the times as below.

Note, what dragging the formula down actually did was copy the formula to each cell, but also move the references down a row each time. So, =D3-C3 became =D4-C4 and so on. That’s why it works! More on formulas and cell referencing in a future post.

---

Accounting and financial formats

When working with financial figures, using the normal number format sometimes isn’t very clear, as we can see in the table below:

We can easily remedy that. Go to Format>Number or click on the numbers icon on the toolbar as before. You will have various formats presented to you. First, let’s select “Accounting”.

As you can see it’s changed the figures to the currency my Sheets are defaulted to. It also, puts the negative figures in brackets.

Choosing “Financial” will do the same except add the currency symbol.

Below is an example of “currency (rounded)” which removes the decimals and rounds to the nearest whole unit, e.g. pounds.