How to Rounding Data in Microsoft Excel

The ROUND, ROUNDUP and ROUNDDOWN functions are useful when rounding numbers in Excel. These functions are categorize Math and trigonometry.

The ROUND function in Excel

We use the ROUND function to work with any number of decimal places. Unlike when we apply a format on the cell to display more or fewer decimal, the ROUND function effectively modifies the stored value in the Excel cell. The syntax for the ROUND function is: ROUND(number, num_digits)

The function only requires two arguments, the first of them the number we want to round, the second is the number of decimal places that we need. Consider the following examples: = ROUND (3.1416, 0). It returns the value 3, because we are indicating zero as the number of decimals we need. = ROUND (3.1416, 3). It returns the value 3,142 because we specified three decimal places.

ROUNDUP and ROUNDDOWN functions in Excel

These functions have the same arguments that the ROUND function and the difference is that ROUNDUP always rounds up the specified number and the ROUNDDOWN function makes rounding down.

In the next picture you can see the difference between each of the three functions by applying rounding the value of the mathematical constant PI.

Each function applies a different rounding even if you applied the same number of decimal places. Notice how the result of the function ROUND sometimes agrees with the result and sometimes ROUNDUP agrees with the result ROUNDDOWN. The next time you need round in Excel know that there are three functions that can help you get the desired result. (Source: Excel Total)

How to Edit & Delete Data in Excel

Edit Cell Data: Once you enter text, a number, a date, or a time into a cell, that cell data is not set in stone.If the data you entered into a cell has changed or is incorrect, you can edit the data to the updated or correct value. You can edit cell data either directly in the cell or by using the Formula bar.

Delete Data from a Cell: If your worksheet has a cell that contains data you no longer need, you can delete that data. This helps to reduce worksheet clutter and makes your worksheet easier to read.

Two Variables Data Table in Excel

In a previous article I showed the benefits of the data tables (Data Table in Microsoft Excel) as an option of What-If analysis. This time we review a classic example of the use of a table of data for analysis of two variables.

The scenario is as follows. Suppose I want to apply for a personal loan from a bank, I’m doing a research with several banks and each of them has given me an interest rate as well as several different payment terms. The first case that I investigated is:

Two Variables Data Table Instead of making the calculation for each option interest rate and term, adequately I will accommodate the data to form a data table. In the rows I will specify different rates of interest and in the columns possible terms.

To create the data table with two variables I will select the range B4: G11 and I go to the Data tab and click the “What-If” button to select whether the “Data Table” option. Within the dialog I must choose each of the input cells for the data table as follows:

When click OK the data table is generated

In this data table can observe what we expected, the higher is the term the monthly payments are reduced and the greater is the interest rate your monthly payment increases. With this information I can properly analyze and choose the option that best fits my chances of monthly payment. (Source: Excel Total)

How to Use Subtotals in Microsoft Excel

When you have a data table with a lot of information, subtotals in Excel can help us understand and interpret the information. Excel lets you add subtotals in a very simple way.

Suppose the following data table where I want to subtotal sales for each month:

The first thing to do is sort the data by the column on which the subtotals are going to be obtained. For this example I will order the data for the “Month” column. To do this you have to click any cell and then choose the Sort Command in the Data Tab. Finally click in Descendent command. (In Windows click in Sort from older to newer).

The table is sorted

The Subtotals Button in Excel

For insertion of the subtotals in Excel we must press the Subtotal command from the Data tab.
Excel shows the Subtotals dialog box

Now we are going to explain each box. The first one is At each Change in: we are going to select Month, that means that every change in the column month excel is going to put the subtotal. The second is the Function, in this example we are going to use Sum function. Then select the Sales item in the Add subtotal to menu, this means that excel is going to use the function with the date of the selected column. Finally click OK and excel calculate the subtotals:

Notice how Excel has inserted a new row containing the subtotal for each month. In addition to the left of the spreadsheet Excel places additional controls are useful to hide or display groups of data according to the subtotals. Clicking on any of them the corresponding group expands or contracts:

Creating a Data Table in Microsoft Excel

Data tables are part of the What If analysis tools that allow us to change the value of some cells to see how it affected the original result. A data table analyzes a set of values and determines possible outcomes.

Data table of one variable

The simplest example of a data table is one that uses a variable for calculations. Assume the following scenario:

In this example I’m making a sales forecast for 2015 based on 2014 sales and expecting a growth rate of 3.2%. What I want to do is to know what the sales projection for 2015 if the growth rate was different. For this test I will place the growth rates that will be used as follows:

To create the data table I select the range of cells as shown in the previous image and then go to the Data tab, and in the Analysis group press the What-If button finally select the Data Table option.

The Table Data dialog box is displayed and in the text box Column Input Cell you must select the cell B2 that is the cell that contains the percentage of growth.

Clicking the OK button, adjacent cells will be filled with growth rates projected value corresponding to each of the sales rates.

Excel has created the data table in the range A4: B12 and thus can analyse different sales projections for different growth rate. Once you’ve finished analysing the information, if I try to remove some of the cells belonging to the range of the data table, Excel will display a warning message that you cannot change part of a data table. If you want to delete the data table you must first select the entire range before pressing the delete key. (Source: Excel Total)

Convert Time to Seconds in Microsoft Excel

Do you ever have had to show a data of time in seconds? In Excel Doing it is really simple and will take only a few seconds.

We are used to express the values of time in hours, minutes and seconds. But probably sometimes you need these values only time expressed in seconds. Suppose the following example.

In this example we have a data equivalent to 1 hour, 35 minutes and 15 seconds. To find quickly the number of seconds is sufficient to modify the format of the cell. Right click on the cell and select Format Cells option and should go to the Custom section.

In the Type text box you should specify the custom format [ss] and click OK

Excel will show the total of equivalent seconds of the time data in the cell.

Concatenate Functions in Microsoft Excel

There are some functions in Excel where any arguments allow to specify a condition so that if it is met will perform the action. Such is the case of COUNTIF function or SUMIF function.

Such functions are useful to apply an action a cause of certain condition. For example, if I want to count all the people in the range B1: B20 that are older than 30 years can apply the following formula: `=COUNTIF(B1:B20, ">30")`

But now, what if I want to count only those who are aged above average? One option would be to get the average in a separate cell and then make the comparison with the obtained value to determine whether or not you can count every item. Rather than make a separate calculation, we can make use a special quality of the COUNTIF function argument condition.

This quality is due to the argument condition is a text string and therefore can make a chain with the result of another function. Observe carefully how to perform this concatenation in the second argument of the function: `=COUNTIF(B2:B21, ">"&AVERAGE(B2:B21))` Concatenation in Excel is done with “&” and for this example I’m using the AVERAGE function so that Excel gets the average of the range and then the concatenation to get ready the argument condition.

After making the evaluation of this expression Excel shows the correct result.

For this example I used the AVERAGE function, but you can use any function that helps form the argument condition you need. (Source: Excel Total)

Create a Waterfall Chart in Excel

A waterfall chart helps to visualize the contribution made by each of the parties to the total. This chart type is distinguished by having “floating” columns because they give the impression of being suspended in the air.

This is the waterfall chart that we are going to do:

In our example we are showing the sales of a company in 4 countries where they have presence: Brazil, Colombia, Argentina and Mexico. Each country brings a portion of the total sales of the company. TO begin with the construction of the chart, we have to take in count 2 elements: – Base Values: These values are the blank spaces on which “will float” the columns. – Connectors: A series of data will be needed to show the connectors between the floating columns. Now we are going to see how the Data Table is.

Create the Waterfall Chart To create a waterfall chart select the data range to be graphed (A2: G6) and go to the Insert tab in the Charts Section and click the button to select Column and the Stacked Column option. As a result you have the following chart in Excel.

Now we swap the rows and columns. Right click on the graph and select the Select Data option, in the dialog box click on the Switch Row/Column button and accepts the changes. The graph will look as follows:

The upper columns are the corresponding connectors and now will become lines. To make the change, you need to right click on the first of the series of connectors (in this example the green columns). In the pop-up menu you must select Change Series Chart Type and go to the Line section and choose the Lines chart type. Repeat this process for all columns of connectors and you finally you will have the chart below.

To format each connector you need to right click on each line and select the Format Data Series and you should consider making the following changes: In the Line Color section select Solid Line option and change the color to black In the Line Style section sets a width of 0.25 pt. and in Dash type choose Square Dot. Apply these changes for each connector. Optionally remove the gridlines of the chart and have the following result.

It only remains to remove the fill color of the base values, for do this right-click on the corresponding data set (in this example the blue columns) and select the menu option Format Data Series and select in the Fill section No Fill option. When you close the dialog box will get the following result.

You need only to remove the legend to having a waterfall chart as shown the beginning of the article. (Source: Excel Total)

Learn About Goal Seek in Microsoft Excel

Excel includes several tools for data analysis and Goal Seek is a technique used to easily find the number that fulfills the necessary conditions to achieve a goal.

This tool will help you do many tests of values in a formula to find the exact value that meets the conditions. That is, if we know the result of a formula, but we are not sure of the value of one of its arguments, then Goal Seek will help us to test various scenarios to find the exact value needed. For example, suppose we have the following problem.

I have a number multiplied by another number X and this should give me the result shown. Although this is an easy problem to solve mathematically will be useful to show the functionality of Goal Seek. I begin by defining an important formula. In cell C3 will place the formula to be met to obtain the desired result, ie, = B1 * B2 it should result in 689

For now the result in C3 is zero, because there is no value in cell B2, however, when you go looking for the appropriate value for B2, the value of cell C3 will become the same as B3 when the correct value is found.

Goal Seek Command

Now you need to click the Data tab and select If Analysis where an options menu will be displayed and you must choose Goal Seek.

Immediately the dialog box Goal Seek is displayed, where you must fill in the following text boxes.

– Set cell: It is the cell that contains the formula – To Value: This is the number we want as a result of the formula. – By changing cell. The cell to be modified to have the desired result. Once the parameters have been set, you must click on the OK button and Excel will begin to enhance the necessary calculations. When have found a suitable result the Goal Seek Status dialog box is displayed.

This dialog box just notified that it has reached the target by changing the specified cell. When you close the dialog box you can see the result. For our example Excel was doing several tests until the result of 146,60 is the number multiplied by 4,7 will give the expected result.

(Source: Excel Total)

Excel Online Tutorial – Insert a Row & a Column in a Worksheet

You can insert a row or column into your existing worksheet data to accommodate more information. This is particularly useful if the information you need to add fits naturally within the existing data, rather than at the end.

When you insert a row, Excel shifts the existing rows down, so you must first determine the row above where you want your new row to appear. Similarly, when you insert a column, Excel shifts the existing columns to the right, so you must first determine the column to the left of where you want your new column to appear.