Formatting data in Google Sheets
When working with data in Google Sheets, formatting cells is key to making your spreadsheet clear and visually appealing. Whether you’re dealing with currency (e.g., £500 for rent), percentages (e.g., 15% for a discount), dates (e.g., 9/9/2024 for a deadline), or plain numbers (e.g., 123 for product quantities), applying the right formatting will help make your data more understandable. Here’s a quick guide to the most common formatting options.
Currency Formatting (£)
Need to format your numbers as currency? Simply select the cells and click the currency symbol in the toolbar. This is perfect for financial data, like your monthly budget. (For example, Rent, Electricity, and Water).
Percentages (%)
When working with percentages, it’s important to apply the format before entering the numbers. If you don’t, Google Sheets will multiply the values by 100, giving you unexpected results. For example, entering “50” without formatting will display “5000%” instead of “50%.”
Decimal Places (.00)
By default, percentages are shown with two decimal places, but you can easily adjust this by clicking the decimal place icons (.00 and .0) to increase or decrease the number of decimal places as needed, which is especially useful for financial or scientific data that requires precision.
Number Formatting (123)
To remove specific formatting like currency or percentages and revert numbers back to their standard format, use the 123 button. This is helpful when you want your data displayed as plain numbers without any special formatting.
Additionally, the 123 button gives you access to other useful formats such as:
• Date and Time: If you’re working with dates or timestamps, you can format cells to show these values in a clear, readable format. Google Sheets recognizes a variety of date and time inputs and allows you to choose from multiple formats like “DD/MM/YYYY” or “MM/DD/YYYY” for dates and “HH:MM:SS” for time.
For example, if you input “10/19/2024” into a cell, Google Sheets will automatically apply a default date format, but you can easily customize this using the 123 button to suit your regional or project needs.
By using these number formats, you can make your data easier to interpret and keep it consistent across your spreadsheet.
For a more detailed walkthrough, check out my video on YouTube:
Find out how to format your cells here.
Learn more on my Google Sheets course 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).
a