Trending December 2023 # How To Round Numbers In Google Sheets # Suggested January 2024 # Top 12 Popular

You are reading the article How To Round Numbers In Google Sheets updated in December 2023 on the website Achiashop.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested January 2024 How To Round Numbers In Google Sheets

How to Round Numbers in Google Sheets

You can round numbers in Google Sheets using the following methods:

Round numbers using the ROUND function.

Round numbers using the ROUNDUP function.

Round numbers using the ROUNDDOWN function.

Round numbers using the MROUND function.

Round numbers using the INT function.

Round numbers using the FLOOR function.

Round numbers using the CEILING function.

Let us have a detailed look at each of these methods.

1] Round numbers using the ROUND function

The ROUND function rounds a number to a specific number of decimal places as per the standard rules, which are as follows:

If the digit to the right of the rounding digit is less than 5, the rounding digit is left unchanged (rounded down).

If the digit to the right of the rounding digit is greater than or equal to 5, the digit is increased by 1 (rounded up).

The syntax of the Round function is:

ROUND(value, [places])

Where value refers to the number that needs to be rounded, and

[places] refers to the number of decimal places to which the number should be rounded. It is an optional argument. If not specified by the user, it takes the value zero (0).

Now let us understand how to round numbers in Google Sheets using the ROUND function.

A] Round numbers to the right of the decimal point

Place the cursor in cell C3 and type the following function:

=ROUND(A3)

Since the number of decimal places to which the number needs to be rounded is not specified for cell A3, it will take the default value (0). This means there is no rounding digit, or in other words, the number needs to be rounded off to the nearest integer. Now since the digit to the right of the decimal point is 0 which is less than 5, the number to the left of the decimal point is left unchanged. So the resultant value will be 0, as displayed in cell C3.

For the next value (cell A4), the rounding place is 2. So the number needs to be rounded off to 2 decimal places. So the rounding digit is 2. The digit to the right of the rounding digit is 3, which is less than 5. So the rounding digit will be left unchanged. Therefore, the resultant rounded value will be 1.62, as shown in cell C4.

For the next value (cell A5), the rounding place is 0. Again, the number will be rounded off to the nearest integer which is 11, as displayed in cell C5. Here, since the digit to the right of the decimal point is equal to 5, the digit to the left is raised by 1.

Now for the next 2 values (in cells A6 and A7) you can easily determine how the ROUND function is rounding the values.

B] Round numbers to the left of the decimal point

Suppose you need to round the number to the left of the decimal point instead of the right. For that, you need to pass a negative value in the places argument.

A negative value in the places argument will remove all the digits to the right of the decimal point and round the number to the left of the decimal point to the nearest tens, hundreds, thousands, and so on.

For example, look at the above image. We have passed negative values as places in the ROUND function. Here,

-1 will round the number to the left of the decimal point to the nearest tens.

-2 will round the number to the left of the decimal point to the nearest hundreds.

-3 will round the number to the left of the decimal point to the nearest thousands, and so on.

Following this, the number in cell D3 (421.352) becomes 420 when rounded to the nearest tens, becomes 400 when rounded to the nearest hundreds, and becomes 0 when rounded to the nearest thousands.

Similarly, the number in cell D6 (669.005) becomes 1000 when rounded to the nearest thousands and becomes 700 when rounded to the nearest hundreds.

2] Round numbers using the ROUNDUP function

The ROUNDUP function works just like the ROUND function except that it always rounds the number upwards. The syntax of the ROUNDUP function is:

ROUNDUP(value, [places])

Where value is the number that needs to be rounded upward, and

[places] refers to the number of decimal places to which the number should be rounded. It is an optional argument and its default value is zero (0). If a negative value is passed in the places argument, the number to the left of the decimal point is rounded upward.

Now have a look at the above image. As you may see, all the numbers have been rounded upward to a certain number of decimal places either to the right of the decimal point or to the left of the decimal point, depending on whether the value of the places argument is positive or negative.

For example, the value in cell G4 (1.623) has been rounded up to 2 decimal places. Here, the rounding place is 2, which is digit 2, and the digit next to 2 is 3, which is less than 5. Still, since this is a ROUNDUP function, the resultant value will be 1.63 and not 1.62.

Similarly, the value in cell G8 (426.352) becomes 430 (not 420) when rounded up to the nearest tens.

3] Round numbers using the ROUNDDOWN function

The ROUNDDOWN function also works like the ROUND function except that it always rounds the number downwards.

The syntax of the ROUNDDOWN function is:

ROUNDDOWN (value, [places])

Where value is the number that needs to be rounded downwards, and

[places] refers to the number of decimal places to which the number should be rounded. It is an optional argument and takes the value zero (0) if not specified by the user. The number to the left of the decimal point is rounded downward if a negative value is passed in the places argument.

Now have a look at the above image. Looking at the figures, you may easily understand how the ROUNDDOWN function is rounding the numbers down to a certain number of decimal places. The numbers are rounded either to the right of the decimal point or to the left of the decimal point based on the value of the places argument (positive or negative).

For example, the value in cell J7 (74.496) has been rounded down to 1 decimal place. Here the rounding place is 1, which is digit 4. The digit to the right of 4 is 9, which is greater than 5. Still, the rounded value will be 74.4 and not 74.5, since the ROUNDDOWN function has been applied to the cell value.

4] Round numbers using the MROUND function

The MROUND function rounds a number to the closest multiple of another number, such as 2, 3, 5, etc.

The syntax of the MROUND function is:

MROUND(value,factor)

Where value is the number that needs to be rounded, and

factor is the number whose multiple becomes the closest number to which the given number should be rounded.

Notes:

While using the MROUND function, you can pass a negative value in the factor argument only if the value argument is also negative.

Both value and factor arguments can be non-integral.

If 0 is passed in the factor argument, MROUND function will return 0.

If 2 multiples of factor are equally nearest to the value, multiple with the higher absolute value will be returned.

To understand this, have a look at the above image. The value in cell M7 (3.28) has been rounded to 3.3. Here, the factor value is 0.05. If we keep on multiplying the factor with 1, 2, 3 and so on, we will find the following numbers nearest to 3.28:

0.05 x 64 = 3.2

0.05 x 65 = 3.25

0.05 x 66 = 3.3

0.05x 67 = 3.35

Out of all these, the closest is 3.3. So the MROUND function has returned 3.3 as a result.

5] Round numbers using the INT function

The INT function is used to round a decimal number downwards. It always rounds the number down to the nearest integer which is less than or equal to it.

The syntax of the INT function is:

INT(value)

Where value is the number that needs to be rounded off.

To understand this, have a look at the above image. The value in cell P6 (24.8) has been rounded to 24, which is the nearest integer lesser than 24.8. Similarly, the value in cell P7 (-16.1) has been rounded to -17, which is the nearest integer lesser than -16.1.

The main difference between the INT function and the ROUNDDOWN function is that the INT function rounds the value of a given number downwards, whereas the ROUNDDOWN function rounds the ‘absolute’ value of a given number downwards. So if we apply the ROUNDDOWN function to cell P7, the result will be -16, not -17.

6] Round numbers using the FLOOR function

The FLOOR function rounds a given number down to the closest multiple of another number.

The syntax of the FLOOR function is:

FLOOR(value, [factor])

Where value is the number that needs to be rounded, and

factor is the number (positive only) whose multiple is the closest number to which the value should be rounded. It is an optional argument and its default value is 1.

To understand the FLOOR function, have a look at the above image. The value in cell R5 (-17) has been rounded down to -20, which is a multiple of 4, closest to -17. Similarly, the value in cell R3 (19) has been rounded down to 18, which is a multiple of 3 closest to 19.

Also Read: How to Highlight Duplicates in Google Sheets.

7] Round numbers using the CEILING function

The CEILING function rounds a given number upward to the closest multiple of another number.

The syntax of the CEILING function is:

CEILING(value, [factor])

Where value is the number that needs to be rounded, and

factor is the number (positive or negative) whose multiple is the closest number to which the value should be rounded. It is an optional argument that takes the value 1 if not specified by the user.

If the value is positive, the factor must also be positive. But if the value is negative, the factor may either be positive or negative to determine in which direction the values should be rounded.

For example, look at the above image. The value in cell U5 (-17) has been rounded upward to -16, which is a multiple of 4 closest to -17. Similarly, the value in cell U3 (19) has been rounded upward to 21, which is a multiple of 3 closest to 19.

So this winds up how you may round numbers in Google Sheets. Hope you find this useful.

Read Next: How to stop Excel from rounding numbers.

You're reading How To Round Numbers In Google Sheets

How To Make A Histogram In Google Sheets

For many of us, life as we now know it would be almost impossible without Google Drive. Of course, Microsoft Office loyalists will disagree and justifiably so. However, with work from home becoming a norm and professionals having to make the shift to Google Sheets, Docs, and practically everything Drive related, whether willingly or not, we must make the move now.

This brings us to the subject of the article, i.e, how to make a histogram on Google Sheets. We suspect the reason you’re here is that you’ve realized/discovered that things work a little differently on Google Sheets as compared to the way they did with Excel or whichever software you used before for number crunching and analyzing data. One thing you can be assured of is that Google Sheets is very easy to use and navigate. Just focus on letting go of Excel tendencies and this tutorial will do the rest for you. Let’s dive in!

Related: How to get Bitmoji in Google Classroom

How to construct a Histogram chart/graph

The premise is more or less the same with Google Sheets just like it was for Excel. You need to apply the relevant cell formula to manipulate data and calculate numbers if you want your chart to be really thorough. The next steps will elaborate on how to add a Histogram chart.

Create a data range

A data range is basically a cell grid that you create with relevant numeric information that is to be represented on the Histogram. For the purpose of this article, we’ve created two data cells ranging between values 43 and 95. Here’s a look at our data range allocated to columns A and B.

We will be using these columns to create our Histogram. You can create your Histogram using this one or use data of your own.

Related: How to use Google Meet in Google Classroom

How to add a Histogram Chart

Great! The histogram chart has now been added to your sheet. It should look similar to this image:

Related: 16 cool Google Meet Ideas for Teachers

How to edit the Histogram chart- Setup Tab

The Setup Tab is where you will find the options and settings that are required to add the relevant information from the columns to the chart. This is where you set the base for the histogram. Basically, the Setup Tab allows you to incorporate the data sets into it.

Now, you can choose one of two formats. Either club both the columns together to form a single panel chart. In which case add all the cells (A1: B15) in one range.

Or, you can create another range for the next column as well.

In any case, your Histogram will look like this:

In the Series section, you can create one entire set of series which would then be A1:B15 or you can also separate the two columns.

In which case your Histogram will look like this:

If you choose to club all the columns into one series, then the histogram chart won’t look and different from what it did before.

Finally, in the last section of the Setup Tab, you will see the following options with checkboxes. You can select them if you wish to incorporate those settings in the chart.

Great! now that you have set up your histogram, let’s look at how you can customize it.

Related: Google Meet for Teachers: A Complete Tutorial and 8 Useful Tips

How to edit the Histogram chart- Customize Tab

In the customize tab, you can change the look and feel as well as add features to your histogram.

Let’s look at each section in the Customize Tab starting with Chart Style.

In the Chart Style section, you change the font, background, and chart border color of the chart.

Next, you can set the bucket size of the chart in case you want to increase it. Sheets offers a bucket size of up to 50. You can also show the x-axis in outliers.

In the next section, you can name your chart and change the font and font size.

You can even set the position of the legend.

When customized, our histogram came out looking like the image below. We’re

There you go! You now know how to create a histogram graph on Google Sheets. Let us know what you want to learn next. Take care and stay safe.

Xmatch Function In Google Sheets

Learn more about working with Lambda Functions, Named Functions, and X-Functions in the FREE Lambda Functions 10-Day Challenge course

The XMATCH function in Google Sheets is a new lookup function in Google Sheets that finds the relative position of a search term within an array or range. It’s an evolution of the original MATCH function.

Here’s a simple XMATCH function that finds the position of the search term “Cho Oyu” in the list of the highest mountains in the world:

=XMATCH(

A2:A15

)

In the Sheet:

And here’s how it works:

It looks for the search term from cell E2 (“Cho Oyu”) in the range A2:A15, then returns the position of the search text within this range. Note that the result is relative to the range, irrespective of the row number.

Notice how, unlike a regular MATCH function, you don’t have to specify the “0” search type for an exact match. It chooses the exact match, which is by far the most common use case, by default (in contrast to the MATCH function where you have to add the 0 to explicitly confirm exact matching). More on the search types below.

🔗 Get this example and others in the template at the bottom of this article.

XMATCH Function Syntax

=XMATCH(search_key, lookup_range, [match_mode], [search_mode])

It takes 4 arguments in total, the last 2 being optional:

search_key

This is the value you want to search for.

lookup_range

The range to search. It must be a single row or column.

[match_mode]

This optional argument lets you specify what match mode to use. If unspecified, an exact match is used.

The options are:

Option Match Mode Behavior

0 Exact match search

1 Exact match or next value that is bigger than the search key

-1 Exact match or next value that is lower than the search key

2 Wildcard match

[search_mode]

The different search options are:

Option Search Mode Behavior

-1

2 Search through the range using binary search and assuming the range is sorted in ascending order

-2 Search through the range using binary search and assuming the range is sorted in descending order

XMATCH Function Notes

The lookup range can only be either a single row or a single column. It cannot be an array with multiple rows AND columns.

If an exact search mode is used but no match is found, the XMATCH function returns a #N/A error.

XMATCH Function Examples

For the following examples, let’s use this dataset of the highest mountains in the world:

(This is available in the template, linked at the bottom of this article.)

Example 1: Basic Exact Match

If you omit the optional match mode argument, or set it to 0, the XMATCH function will perform an exact match. This is the example we saw at the top of this blog post:

Example 2: Next Value XMATCH

The third argument of the XMATCH function determines the matching mode. As you saw above, in example 1, if it is omitted or set to 0, then an exact match is performed.

However, if we set it to 1 or -1, then the approximate matching mode is used.

Consider the case when our search key falls between two values in the lookup range. It’s not an exact match, but we might still want to return a result that is higher or lower than it.

Setting the match mode to 1 will find an exact match (if it exists) or the next value that is bigger than the search key.

Setting to -1 will find an exact match (if it exists) or the next value that is lower than the search key.

Consider this example:

The search term is 8,500 which does not occur in the search range (C2:C15).

=XMATCH(

C18

C2:C15

)

Since 8,500 is not found exactly, it finds the next largest value (8,516) and returns the relative position of that item, i.e. position 4 in this example.

The formula to find the next value that is lower than the search term (i.e. =XMATCH(C18,C2:C15,-1)

How To Remove Duplicates In Google Sheets In Five Different Ways

In this post, you’ll see how to highlight duplicates in Google Sheets and how to remove duplicates in Google Sheets.

Duplicates are instances of the same record appearing in your data more than once.

They’re a huge problem and it’s critical to find duplicates in Google Sheets before any data analysis is performed.

Imagine you had two instances of the same client transaction for $5,000 in your database. When you summarize your data, you might think you have $10,000 in revenue from that client when in fact you only have $5,000. You’ll make decisions based on the wrong data.

Contents

However, here’s a rundown of when to use the different methods:

Method 1: Remove Duplicates tool is the easiest method of removing duplicates.

Method 2: Formulas The UNIQUE function is great for small, simple datasets or when you need to remove duplicates inside a nested formula.

Method 3: Pivot Tables are a great way to highlight duplicates in Google Sheets. Pivot Tables are extremely flexible and fast to use, so they’re a great tool to use when you’re unsure if you have duplicates and want to check your data.

Method 4: Conditional Formatting is a great way to highlight duplicates in Google Sheets.

Method 5: Apps Script is useful for developers who want to remove duplicates from Sheets as part of their apps, or someone who needs to repeatedly and automatically de-duplicate their data.

Method 1: How to remove duplicates in Google Sheets with the Remove Duplicates tool

The new feature is super easy to use. You find this feature under the menu:

You may want to remove duplicates where the rows entirely match, or you may wish to choose a specific column, such as an invoice number, regardless of what data is in the other columns.

The duplicates will then be removed and you’ll be presented with a summary report, telling you how many duplicates were removed:

Method 2: How to remove duplicates in Google Sheets using formulas 2.1 Use the UNIQUE formula

This method deletes duplicates in the range of data you select.

The UNIQUE function considers all the columns of your data range when determining the duplicates. In other words, it compares each row of data and removes any rows that are duplicates (identical to any others across the whole row).

It’s very easy to implement as it involves a single formula with a single argument — the range you want to de-duplicate (remove duplicates from).

=UNIQUE(

A1:D11

)

Here’s an example of the UNIQUE function in action. The function is in cell F1 and looks for duplicates in the data range in A1:D11:

And this is the result:

You can see the table on the right has fewer rows, because the duplicate rows have been removed.

2.2 Highlight duplicate values with COUNTIF

This method uses the COUNTIF function to highlight duplicates in Google Sheets.

First, create a new column next to the data column you want to check for duplicates (e.g. invoice number).

Then use this formula in cell B2 to highlight the duplicates in column A:

You’ll notice the range is A$2:A2

The $ sign is key here because it locks the range to the top of the column, even as you copy the formula down column B. So this formula checks for duplicates in the current row back up to the top.

When a value shows up for the first time, the count will be 1, so the formula result will be false. But when the value shows up a second time, the count will be 2, so the formula result will be TRUE.

The final step is to select the rows with TRUE values (the duplicates) and delete them.

Method 3: How to find duplicates in Google Sheets using Pivot Tables

If you’re new to Pivot Tables, check out my article Pivot Tables 101: A Beginner’s Guide.

Pivot Tables are a great tool to use to search for duplicates in Google Sheets. They’re extremely flexible and fast to use, so they’re often a great place to start if you’re unsure whether you have any duplicates in your data.

Step 1:

Highlight your dataset and create a Pivot Table (under the Data menu).

A new tab opens with the Pivot Table editor.

Step 2:

Under ROWS, choose the column you want to check for duplicates (e.g. invoice number).

Step 3:

Then in VALUES, choose another column (I often use the same one) and make sure it’s set to summarize by COUNT or COUNTA (if your column contains text), like this:

The Pivot Table will then look like this:

You can see that duplicates values (for example 196-X) will have a count greater than 1.

From here you can find these duplicate values in your original dataset and decide how to proceed.

As you can see, this method is most suitable to find duplicates in Google Sheets.

Method 4: How to highlight duplicates in Google Sheets using Conditional Formatting

Select your dataset and open the conditional formatting sidebar (under the Format menu).

Under the “Format cells if…” option, choose custom formula is (the last option) and enter the following formula:

This formula checks for duplicates in a single column only, in this case column A.

The output is highlighting applied to the duplicate values:

What if you want to apply the highlight to the whole row?

You need to make one small tweak to the formula (highlighted in red) by adding a $ sign in front of the final A:

Now your output will look like this, with the whole row highlighted:

Check out this article for a more detailed look at how to highlight a whole row using conditional formatting.

Method 5: How to remove duplicates in Google Sheets using Apps Script

(New to Apps Script? Read my starter guide to Apps Script for a primer.)

It’s relatively straightforward to create a small script file that can remove duplicate rows from your datasets.

Sample Apps script program: How to remove duplicates in Google Sheets

This program removes duplicates from a dataset in Sheet 1. It’s very specific to the Sheet and data range, but it’s easy to create and modify.

It works as follows:

Get the values from the data range in Sheet1, using Apps Script

Turn the array rows into strings (blocks of text) for comparison

Filter out any duplicate rows

Check whether a de-duplicate sheet exists

If it does, clear out the old data and paste in the new de-duplicated data

If it does not exist, create a new sheet and paste in the new de-duplicated data

Add a custom menu to run from the Google Sheet

So it’s very specific to this use case, but it could be easily adapted if necessary for different datasets. Here it is in action:

And here’s the Apps Script code for this program:

/** * remove duplicate rows from Google Sheets data range */ function removeDupRows() { var sheet = ss.getSheetByName('Sheet1'); var startRow = 7; var range = sheet.getRange(startRow,1,sheet.getLastRow(),sheet.getLastColumn()).getValues(); var dedupRange = arrayUnique(range); Logger.log(dedupRange); if (ss.getSheetByName('Sheet1 Duplicates Removed')) { var dedupSheet = ss.getSheetByName('Sheet1 Duplicates Removed'); var lastRow = Math.max(dedupSheet.getLastRow(),1); var lastColumn = Math.max(dedupSheet.getLastColumn(),1); dedupSheet.getRange(1,1,dedupSheet.getLastRow(),dedupSheet.getLastColumn()).clear(); dedupSheet.getRange(1,1,dedupRange.length,sheet.getLastColumn()).setValues(dedupRange); } else { var dedupSheet = ss.insertSheet('Sheet1 Duplicates Removed',0); dedupSheet.getRange(1,1,dedupRange.length,dedupRange[0].length).setValues(dedupRange); } dedupSheet.activate(); } /** * helper function returns unique array */ function arrayUnique(arr) { var tmp = []; return arr.filter(function(item, index){ var stringItem = item.toString(); tmp.push(stringItem); return tmp.indexOf(stringItem) >= index; }); }

And you can also add a custom menu to run it from your Google Sheet rather than the script editor window:

/** * add menu to run function from Sheet */ function onOpen() { ui.createMenu('Remove duplicates') .addItem('Highlight duplicate rows','highlightDupRows') .addItem('Remove duplicate rows','removeDupRows') .addToUi(); }

The code for this simple duplicate program is also here on GitHub.

Suggestions for improvement

Set triggers to run the duplicate remover on certain conditions (e.g. once a day, when new data is added)

Better control over selecting the data (i.e. which Sheet, what range etc.)

Whether to consider all of the columns or not for duplicates

Better control over the output

I started coding something along these lines, but it gets complicated as you start to pile on more edge-cases and user options. I realized pretty quickly that all I was doing was reinventing the wheel, since a perfectly fine tool exists already (the built-in one!).

The best thing about Apps Script is that it lets you build minimal viable products specific to your situation very quickly.

Once you’re familiar with Apps Script, it only takes 15 – 30 minutes to build custom scripts, like this one to remove duplicates in Google Sheets.

Now you know how to remove duplicates in Google Sheets with five different techniques, go forth and banish those duplicates from your datasets!

How To Put A Diagonal Line Through A Cell In Excel Or Google Sheets

How to put a Diagonal Line through a Cell in Excel or Google Sheets

There could be many use cases of having a diagonal line to split data in a cell; nevertheless, learning different ways of doing this can prove to be useful if you’re a regular user of Microsoft Excel or Google Sheets.

Microsoft Excel makes it easier than ever, as it provides a feature using which you can quickly add a diagonal line to a cell. Unfortunately, no such feature is available in Google Sheets as of now. But you can still do this using some techniques we are going to explain in this article.

Split a cell diagonally in Microsoft Excel

As stated above, there’s a built-in option to diagonally split a cell in Excel. However, you can also use ‘Shapes’ to insert a diagonal line into a cell. So there are 2 ways of doing this in Excel:

Using the Border feature

Using Shapes

Let us see these in detail.

A] Using the Border feature

The diagonal line will be added to the cell. Now enter the 2 headers giving some space in between, and wrap text within the cell. After this, use the space bar or backspace to adjust the positions of both headers in accordance with the diagonal line. You may also press Alt+Enter, keeping the mouse cursor before the second header to stack it below the first header.

To remove the diagonal line, select None under Presets in the Format Cells window.

B] Using Shapes

Now add the headers as described above.

This is how you insert a diagonal line to a cell in Microsoft Excel. Now let’s see how to do this in Google Sheets.

How to Insert a diagonal line to a cell in Google Sheets Split a cell diagonally in Google Sheets

In Google Sheets, you can use the following features to insert a diagonal line to a cell:

Using the Text Rotation feature

Using the Drawing feature

Using the SPARKLINE function

Let us see these in detail.

A] Using the Text Rotation feature

Text Rotation is a feature of Google Sheets that allows rotating the content of a cell. It not just tilts the text, but also offers a custom angle option to define the degree of rotation. You can use this feature to add a diagonal line to a cell.

Select the cell where you’d like to insert a diagonal line. Then enter the first header. Press the Alt key, hold it, and press Enter. The cursor will come to the beginning of the next line within the same cell. Draw a line using dashes (——-). Again press Alt+Enter to bring the cursor to the next line and then type the second header.

This will rotate the line diagonally, along with the text. By default, the content of the cell will be rotated at 45 degrees. You can change this and apply a different angle of rotation to the cell using the Custom angle option.

Please note that if the cell has borders, they will also be rotated along with the cell content and make the whole arrangement look displaced. In that case, you may use the next method to diagonally split a cell in Google Sheets.

B] Using the Drawing feature

Select the cell which needs to be split diagonally. Enter the 2 headers using Alt+Enter as described above, and use the space bar to adjust their position within the cell.

Read: How to insert WordArt in Google Sheets using Google Drawings.

C] Using the SPARKLINE function

The Sparkline function is used to create miniature charts that can fit within single cells. It represents a trend based on the numerical data of a selected range of cells. This trend can take up 3 different forms: line, column, and bar. We can use the ‘line’ form to create a diagonal line within a cell in Google Sheets.

SPARKLINE function has the following syntax:

= SPARKLINE(data, [options])

Where data refers to the cell range containing the numeric data, and options are additional settings used to customize the sparkline chart.

SPARKLINE

(

{

}

)

Press the Enter key. The cell will split diagonally. In the above function, we have passed 2 datapoints as 1 and 0. So the function will create a diagonal line as a result.

To draw a diagonal line from bottom to top, type the following function in the cell:

SPARKLINE

(

{0,1

}

)

You can change the color of the line using the color argument as follows:

SPARKLINE

(

{0,1

}, {"color","blue"}

)

You can also adjust the height and width of the cell to adjust the size of the line.

Read Next: How to add a Tooltip in Excel and Google Sheets.

How To Round Off Invoice And Ledger Values In Tallyprime

PDF

Round Off Invoice and Ledger Values

Rounding off values in Invoices or Ledgers helps in viewing the important digits in the financial statements. The need for rounding off may arise when the transactions are recorded together with different rates, discount percentages and even with tax rates. To round off the value of the invoice or the value of the tax ledger in the invoice, you can use the round-off feature in TallyPrime. You can also create a voucher class and when you are recording the voucher using that voucher class, the round-off ledger automatically appears in the voucher.

In your business, while recording the transactions depending on the rates of the goods or the percentage of discount given or even at the time of charging tax percentage the total invoice value may appear in decimal. You can continue to have the invoice value in decimal, however, while viewing the financial statements such values with decimals may become difficult to read. To simplify the view of financial statements TallyPrime provides an option to round off such values and emphasize the important digit.

You may want to maintain a ledger exclusively to round off the invoice value when you record an invoice. For this, you can create an Invoice Rounding ledger under Indirect expenses in TallyPrime.

Create a ledger for round-off.

Create the ledger under Indirect Expenses.

Type of Ledger – Select Invoice Rounding.

In Rounding method, select the appropriate rounding method.

Not Applicable – Keep the invoice value as is.

For example, if the invoice value is 125.60, the invoice value is rounded off to 125 (when the Rounding limit is set to 1), and the round-off ledger will have the value (-)0.60.

Normal Rounding – Round off the invoice value to the following:

Nearest lower number that is a multiple of the rounding limit specified if the decimal value is less than 0.5. For example, if the invoice value is 125.30, the value is rounded off to 125, and the round-off ledger shows the value (-)0.30.

Nearest higher number that is a multiple of the rounding limit specified if the decimal value is more than or equal to 0.5. For example, if the value is 125.60, the value is rounded off to 126, and the round-off ledger shows the value 0.40 (when the Rounding limit is set to 1).

Upward Rounding – Round off the invoice value to the nearest higher number, a multiple of the specified rounding limit. For example, if the invoice value is 125.30, the value is rounded off to 126, and the round-off ledger shows the value of 0.70 (when the Rounding limit is set to 1).

For Rounding limit, enter the number to calculate the nearest number as a multiple of this number to round off. For example, if you mention 1, the invoice value is rounded off to the nearest number which is a multiple of 1. In this case, if the original invoice value is 12.30 and the Rounding Method is set to:

Normal Rounding: The invoice value will be rounded to 12.

Upward Rounding: The invoice value will be rounded to 13.

Downward Rounding: The invoice value will be rounded to 12.

As always, press Ctrl+A to save.

Use the round-off ledger in the sales invoice. The invoice value is rounded off, and the difference in value due to rounding off is displayed as a positive or negative number.

As always, press Ctrl+A to save.

Similarly, if you mention 5 as the Rounding Limit, the invoice value is rounded off to the nearest number which is a multiple of 5. For example, if the original invoice value is 14 and the Rounding Method is set to:

Normal Rounding: The invoice value will be rounded to 15.

Upward Rounding: The invoice value will be rounded to 20.

Downward Rounding: The invoice value will be rounded to 10.

You may want the round-off ledger to appear automatically in the invoice to avoid selecting it every time you record a transaction. For this, you can create a voucher class in TallyPrime with the round-off ledger and use this voucher class to automatically display the round-off ledger in the invoice.

Create a voucher class for sales.

Enter a name for the voucher class under Name of Class.

Press Enter to view the Voucher Type Class screen.

Under Additional Accounting Entries, select a ledger created under Indirect Expenses for accounting invoice round-off. In this ledger, set Use ledger for auto calculation of to Not Applicable.

For the Type of Calculation, select As Total Amount Rounding.

In Rounding method, select the appropriate rounding method.

Not Applicable – Keep the invoice value as is.

Downward Rounding – Round off the invoice value to the nearest lower number, a multiple of the specified rounding limit. For example, if the invoice value is 125.60, the invoice value is rounded off to 125 (when the Rounding limit is set to 1), and the round-off ledger shows the value (-)0.60.

Normal Rounding – Round off the invoice value to the following:

Nearest higher number that is a multiple of the rounding limit specified if the decimal value is more than or equal to 0.5. For example, if the value is 125.60, it is rounded off to 126, and the round-off ledger shows the value 0.40 (when the Rounding limit is set to 1).

For Rounding Limit, enter the number to calculate the nearest number as a multiple of this number to round off. For example, if you mention 5, the invoice value is rounded off to the nearest number which is a multiple of 5. To look at a few more detailed examples, refer to the Invoice Round Off – Using Predefined Ledger section.

As always, press Ctrl+A to save.

Use the voucher class in the sales invoice.

The round-off ledger appears automatically.

As always, press Ctrl+A to save.

By using the Voucher Class you don’t have to add the rounding off ledger every time you record a transaction. It automatically gets added to the voucher once the voucher class is selected.

You can round off a particular ledger value instead of using the rounding off ledger to round off the invoice value. To do so you need to configure the specific ledger with the rounding off details. When you record an invoice, you may want to round off the value of the tax ledger. You need to configure the tax ledger created under Duties and Taxes with the appropriate rounding method.

Alter the ledger created under Duties & Taxes.

For the Rounding method, select the appropriate rounding method.

Not Applicable – Keep the invoice value as is.

Normal Rounding -Round off the invoice value to the following:

For Rounding limit, enter the number to calculate the nearest number as a multiple of this number to round off. For example, if you mention 5, the invoice value is rounded off to the nearest number which is a multiple of 5. To look at a few more detailed examples, refer to the Invoice Round Off – Using Predefined Ledger section.

As always, press Ctrl+A to save.

As always, press Ctrl+A to save the Sales Voucher.

The invoice values in decimal can be a bit difficult to read while viewing the financial statements. To simplify the view of financial statements and ensure maximum readability to the users, TallyPrime helps you round off such values.

Update the detailed information about How To Round Numbers In Google Sheets on the Achiashop.com website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!