Categories

# How to divide in excel

Multiplying and dividing in Excel is easy, but you need to create a simple formula to do it. Just remember that all formulas in Excel begin with an equal sign (=), and you can use the formula bar to create them.

## Multiply numbers

Let’s say you want to figure out how much bottled water that you need for a customer conference (total attendees × 4 days × 3 bottles per day) or the reimbursement travel cost for a business trip (total miles × 0.46). There are several ways to multiply numbers.

### Multiply numbers in a cell

To do this task, use the * (asterisk) arithmetic operator.

For example, if you type =5*10 in a cell, the cell displays the result, 50.

### Multiply a column of numbers by a constant number

Suppose you want to multiply each cell in a column of seven numbers by a number that is contained in another cell. In this example, the number you want to multiply by is 3, contained in cell C2.

Type =A2*\$B\$2 in a new column in your spreadsheet (the above example uses column D). Be sure to include a \$ symbol before B and before 2 in the formula, and press ENTER.

Note: Using \$ symbols tells Excel that the reference to B2 is “absolute,” which means that when you copy the formula to another cell, the reference will always be to cell B2. If you didn’t use \$ symbols in the formula and you dragged the formula down to cell B3, Excel would change the formula to =A3*C3, which wouldn’t work, because there is no value in B3.

Drag the formula down to the other cells in the column.

Note: In Excel 2016 for Windows, the cells are populated automatically.

### Multiply numbers in different cells by using a formula

You can use the PRODUCT function to multiply numbers, cells, and ranges.

You can use any combination of up to 255 numbers or cell references in the PRODUCT function. For example, the formula =PRODUCT(A2,A4:A15,12,E3:E5,150,G4,H4:J6) multiplies two single cells (A2 and G4), two numbers (12 and 150), and three ranges (A4:A15, E3:E5, and H4:J6).

## Divide numbers

Let’s say you want to find out how many person hours it took to finish a project (total project hours ÷ total people on project) or the actual miles per gallon rate for your recent cross-country trip (total miles ÷ total gallons). There are several ways to divide numbers.

### Divide numbers in a cell

To do this task, use the / (forward slash) arithmetic operator.

For example, if you type =10/5 in a cell, the cell displays 2.

Important: Be sure to type an equal sign ( =) in the cell before you type the numbers and the / operator; otherwise, Excel will interpret what you type as a date. For example, if you type 7/30, Excel may display 30-Jul in the cell. Or, if you type 12/36, Excel will first convert that value to 12/1/1936 and display 1-Dec in the cell.

Note: There is no DIVIDE function in Excel.

### Divide numbers by using cell references

Instead of typing numbers directly in a formula, you can use cell references, such as A2 and A3, to refer to the numbers that you want to divide and divide by.

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

Create a blank workbook or worksheet.

Select the example in the Help topic.

Note: Do not select the row or column headers.

Selecting an example from Help

In the worksheet, select cell A1, and press CTRL+V.

To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, click the Show Formulas button.

## Division formulas, #DIV/O! formula errors, and calculating percentages

• Tweet
• Share
• Email

### What to Know

• Always begin any formula with an equal sign ( = ).
• For division, use the forward slash ( / )
• After entering in a formula, press Enter to complete the process.

This article explains how to divide in Excel using a formula. Instructions apply to Excel 2016, 2013, 2010, Excel for Mac, Excel for Android, and Excel Online.

## Division in Excel

Here are some important points to remember about Excel formulas:

• Formulas begin with the equal sign ( = ).
• The equal sign goes in the cell where you want the answer to display.
• The division symbol is the forward slash ( / ).
• The formula is completed by pressing the Enter key on the keyboard.

## Use Cell References in Formulas

Although it is possible to enter numbers directly into a formula, it’s better to enter the data into worksheet cells and then use cell references as demonstrated in the example below. That way, if it becomes necessary to change the data, it is a simple matter of replacing the data in the cells rather than rewriting the formula. Normally, the results of the formula will update automatically once the data changes.

## Example Division Formula Example

Let’s create a formula in cell B2 that divides the data in cell A2 by the data in A3.

The finished formula in cell B2 will be:

## Enter the Data

Type the number 20 in cell A2 and press the Enter key.

To enter cell data in Excel for Android, tap the green check mark to the right of the text field or tap another cell.

Type the number 10 in cell A3 and press Enter.

## Enter the Formula Using Pointing

Although it is possible to type the formula (=A2/A3) into cell B2 and have the correct answer display in that cell, it’s preferable to use pointing to add the cell references to formulas. Pointing minimizes potential errors created by typing in the wrong cell reference. Pointing simply means selecting the cell containing the data with the mouse pointer (or your finger if you’re using Excel for Android) to add cell references to a formula.

To enter the formula:

Type an equal sign ( = ) in cell B2 to begin the formula.

Select cell A2 to add that cell reference to the formula after the equal sign.

Type the division sign ( / ) in cell B2 after the cell reference.

Select cell A3 to add that cell reference to the formula after the division sign.

Press Enter (in Excel for Android, select the green check mark beside the formula bar) to complete the formula.

The answer (2) appears in cell B2 (20 divided by 10 is equal to 2). Even though the answer is seen in cell B2, selecting that cell displays the formula =A2/A3 in the formula bar above the worksheet.

## Change the Formula Data

To test the value of using cell references in a formula, change the number in cell A3 from 10 to 5 and press Enter.

The answer in cell B2 automatically changes 4 to reflect the change in cell A3.

## #DIV/O! Formula Errors

The most common error associated with division operations in Excel is the #DIV/O! error value. This error displays when the denominator in the division formula is equal to zero, which is not allowed in ordinary arithmetic. You may see this error if an incorrect cell reference was entered into a formula, or if a formula was copied to another location using the fill handle.

## Calculate Percentages With Division Formulas

A percentage is a fraction or decimal that is calculated by dividing the numerator by the denominator and multiplying the result by 100. The general form of the equation is:

When the result, or quotient, of a division operation is less than one, Excel represents it as a decimal. That result can be represented as a percentage by changing the default formatting.

## Create More Complex Formulas

To expand formulas to include additional operations such as multiplication, continue adding the correct mathematical operator followed by the cell reference containing the new data. Before mixing different mathematical operations together, it is important to understand the order of operations that Excel follows when evaluating a formula.

You might want to split a cell into two smaller cells within a single column. Unfortunately, you can’t do this in Excel. Instead, create a new column next to the column that has the cell you want to split and then split the cell. You can also split the contents of a cell into multiple adjacent cells.

See the following screenshots for an example:

## Split the content from one cell into two or more cells

Note: Excel for the web doesn’t have the Text to Columns Wizard. Instead, you can Split text into different columns with functions.

Select the cell or cells whose contents you want to split.

Important: When you split the contents, they will overwrite the contents in the next cell to the right, so make sure to have empty space there.

On the Data tab, in the Data Tools group, click Text to Columns. The Convert Text to Columns Wizard opens.

Choose Delimited if it is not already selected, and then click Next.

Select the delimiter or delimiters to define the places where you want to split the cell content. The Data preview section shows you what your content would look like. Click Next.

In the Column data format area, select the data format for the new columns. By default, the columns have the same data format as the original cell. Click Finish.

Unlike the SUM function that can automatically add cells’ values in Excel, there is no predefined division function in Excel. However, there are also several ways to do this math operation in this program. To read more about how to divide in Excel, follow this post to learn basic to advanced division methods in Excel.

## How to divide in Excel

### Divide in Excel by using the divide symbol

The simplest way to divide in Excel is by using the divide symbol (/). You can divide numbers into one cell or divide the amounts of multiple cells. We are going to explain each of them as follow:

First, to divide two amounts in one cell:

• Select the cell that you want to apply the divide operation to.
• Every formula in Excel should start with “=” so, write this symbol first.
• Write the first number, and without any space, write / and then the second number.
• Press Enter to see the result.

Figure 1- Dividing two numbers in one cell in Excel

Remember, writing formulas with this method is not always useful. It is more practical to divide the amounts of two different cells. Consequently, if you change the value of each cell, the result will change.

First, write the number in different cells.

Figure 2- Dividing values of two cells in Excel

• Select the third cell where you want to display the result. It can be any cell on the sheet. (in our example we chose B5)
• Start the formula by writing an equal sign “=”.
• Select the first cell by clicking on it. (in our example it is B3)
• Write “/” and select the second cell. (in our example is B4)

Figure 3- Write the division formula in the third cell where you want to see the result

• Press Enter to see the result.

Figure 4- You can see the result of the division in the cell where you have written the formula

Whenever you modify the value of the cells (in our example B3 and B4), the number in the third cell (here B5) will change. But you can’t write anything in the cells that have formulas. If you double-click on it, you can only see the formula.

### Divide from one cell

Sometimes you may need to divide the value of different cells from the same number. For example, all the cells of one column should be divided into 3.

• Select the cell where you want to see the result. Write an equal symbol “=” and select the first cell.
• Add “/” to the formula and then select the cell that you have written the number. Then press Enter.

Figure 5- Dividing the values of a column from one cell

Although it’s possible to write the formula for each cell one by one, it’s easier to fix the cell where you input the number (in this example, D3). So, you can copy the formula for other cells.

• Go to the formula and put the cursor behind the cell name (D3) and press F4. You can see that two dollar signs appear, one before the cell name and the other after that. It shows that this cell (D3) is locked and won’t change by copying.

Figure 6- The dollar signs indicate that the cell is locked

• You can either double-click on the right corner of the cell to copy it to all the other cells which have values or simply drag it down.

Figure 7- Even after copying the cells, the D3 value remained the same

What happens if you don’t lock the cell in this formula?

Figure 8-DIV/O! Error in Excel!

If you don’t lock the cell you want to remain the same for all the formulas, you will face the DIV/O! Error, which displays when the value of the cell you want to divide from equals zero or is not a number at all. Read more on Excel Formula Errors and why they occur.

### Divide with Paste Special option

As mentioned earlier, if you write a formula in a cell, you’ll see the formula by clicking on it. But, it is also possible to see the value, not the formula. In this case, you should use the Paste Special option. Follow the instruction below:

1. In case you want to keep the original values secure, you can make a copy of them to another column. Therefore, select the column and copy it.
2. Write the divisor value in another cell. (in our example it’s D3)
3. Copy the divisor cell. (The shortcut is Ctrl + C)
4. Select the cells you want to divide to this divisor and then right-click and select Paste Special option from the list. (The shortcut is Ctrl + Alt + V) Figure 9- Right-click on the selected cells and choose Paste Special from the list
5. There are different options in the dialogue box which is opened. Select Divide, and click OK. Figure 10- Select Divide from the Paste Special options
6. The results will appear in the selected cells. Now, if you click on the result cells, you won’t see any formula.

There is one problem with this method of division in Excel. Since you don’t have any formula, if you change the value of the first cells, the result will not change. That makes it a first-time division method, and it’s not dynamic like the division formula in Excel.

## Bottom Line

The division is one of the main mathematical operations that are very useful in accounting functions or making different dynamic reports. We explained how to divide in Excel step by step. So, with a little creativity, you can easily use this operation in Excel.

Check out our other blogs, explaining the other three basic arithmetic operations in Excel:

Unlike the SUM function that can automatically add cells’ values in Excel, there is no predefined division function in Excel. However, there are also several ways to do this math operation in this program. To read more about how to divide in Excel, follow this post to learn basic to advanced division methods in Excel.

## How to divide in Excel

### Divide in Excel by using the divide symbol

The simplest way to divide in Excel is by using the divide symbol (/). You can divide numbers into one cell or divide the amounts of multiple cells. We are going to explain each of them as follow:

First, to divide two amounts in one cell:

• Select the cell that you want to apply the divide operation to.
• Every formula in Excel should start with “=” so, write this symbol first.
• Write the first number, and without any space, write / and then the second number.
• Press Enter to see the result.

Figure 1- Dividing two numbers in one cell in Excel

Remember, writing formulas with this method is not always useful. It is more practical to divide the amounts of two different cells. Consequently, if you change the value of each cell, the result will change.

First, write the number in different cells.

Figure 2- Dividing values of two cells in Excel

• Select the third cell where you want to display the result. It can be any cell on the sheet. (in our example we chose B5)
• Start the formula by writing an equal sign “=”.
• Select the first cell by clicking on it. (in our example it is B3)
• Write “/” and select the second cell. (in our example is B4)

Figure 3- Write the division formula in the third cell where you want to see the result

• Press Enter to see the result.

Figure 4- You can see the result of the division in the cell where you have written the formula

Whenever you modify the value of the cells (in our example B3 and B4), the number in the third cell (here B5) will change. But you can’t write anything in the cells that have formulas. If you double-click on it, you can only see the formula.

### Divide from one cell

Sometimes you may need to divide the value of different cells from the same number. For example, all the cells of one column should be divided into 3.

• Select the cell where you want to see the result. Write an equal symbol “=” and select the first cell.
• Add “/” to the formula and then select the cell that you have written the number. Then press Enter.

Figure 5- Dividing the values of a column from one cell

Although it’s possible to write the formula for each cell one by one, it’s easier to fix the cell where you input the number (in this example, D3). So, you can copy the formula for other cells.

• Go to the formula and put the cursor behind the cell name (D3) and press F4. You can see that two dollar signs appear, one before the cell name and the other after that. It shows that this cell (D3) is locked and won’t change by copying.

Figure 6- The dollar signs indicate that the cell is locked

• You can either double-click on the right corner of the cell to copy it to all the other cells which have values or simply drag it down.

Figure 7- Even after copying the cells, the D3 value remained the same

What happens if you don’t lock the cell in this formula?

Figure 8-DIV/O! Error in Excel!

If you don’t lock the cell you want to remain the same for all the formulas, you will face the DIV/O! Error, which displays when the value of the cell you want to divide from equals zero or is not a number at all. Read more on Excel Formula Errors and why they occur.

### Divide with Paste Special option

As mentioned earlier, if you write a formula in a cell, you’ll see the formula by clicking on it. But, it is also possible to see the value, not the formula. In this case, you should use the Paste Special option. Follow the instruction below:

1. In case you want to keep the original values secure, you can make a copy of them to another column. Therefore, select the column and copy it.
2. Write the divisor value in another cell. (in our example it’s D3)
3. Copy the divisor cell. (The shortcut is Ctrl + C)
4. Select the cells you want to divide to this divisor and then right-click and select Paste Special option from the list. (The shortcut is Ctrl + Alt + V) Figure 9- Right-click on the selected cells and choose Paste Special from the list
5. There are different options in the dialogue box which is opened. Select Divide, and click OK. Figure 10- Select Divide from the Paste Special options
6. The results will appear in the selected cells. Now, if you click on the result cells, you won’t see any formula.

There is one problem with this method of division in Excel. Since you don’t have any formula, if you change the value of the first cells, the result will not change. That makes it a first-time division method, and it’s not dynamic like the division formula in Excel.

## Bottom Line

The division is one of the main mathematical operations that are very useful in accounting functions or making different dynamic reports. We explained how to divide in Excel step by step. So, with a little creativity, you can easily use this operation in Excel.

Check out our other blogs, explaining the other three basic arithmetic operations in Excel:

To split a cell in Excel, add a new column, change the column widths and merge cells. To split the contents of a cell into multiple cells, use the Text to Columns wizard, flash fill or formulas.

### Split a Cell

Use the following trick to “split” a cell in Excel.

1. For example, task B starts at 13:00 and requires 2 hours to complete.

Suppose task B starts at 13:30. We would like to split cell B3 and color the right half.

2. Select column C.

3. Right click, and then click Insert.

4. The default width of a column is 64 pixels. Change the width of column B and C to 32 pixels.

5. Select cell B1 and cell C1.

6. On the Home tab, in the Alignment group, click the down arrow next to Merge & Center and click Merge Cells.

7. Repeat steps 5-6 for cell B2 and cell C2 (and cell B4 and cell C4).

8. Change the background color of cell B3 to No Fill.

Note: suppose task A ends at 15:30. Use the trick explained above to “split” cell E2. Download the Excel file and give it a try.

### Text to Columns

To split the contents of a cell into multiple cells, use the Text to Columns wizard. For example, let’s split full names into last and first names.

1. Select the range with full names.

2. On the Data tab, in the Data Tools group, click Text to Columns.

The following dialog box appears.

3. Choose Delimited and click Next.

4. Clear all the check boxes under Delimiters except for the Comma and Space check box.

Note: this example has commas and spaces as delimiters. You may have other delimiters in your data. Experiment by checking and unchecking the different check boxes. You get a live preview of how your data will be separated.

### Flash Fill

Do you like Magic? Instead of using the Text to Columns wizard, use flash fill to quickly split data into multiple columns.

1. First, split the contents of one cell into multiple cells.

2. Select cell B1 and press CTRL + E (flash fill shortcut).

3. Select cell C1 and press CTRL + E.

4. Select cell D1 and press CTRL + E.

### Formulas to Split Cells

One drawback when using these tools is that the output will not automatically update when the source data changes. Create formulas to overcome this limitation. Let’s split full names into first and last names.

1. The formula below returns the first name.

2. The formula below returns the last name.

3. Select the range B2:C2 and drag it down.

## How to mass multiply or divide all values in a column by a number in Excel?

For a list of numbers, you may need to multiply by a certain number for each of them as below screenshot shown. Normally, you need to calculate them one by one with formulas. In this article, we will show you two methods to quickly multiply or divide all values in a column by a number at once in details.

#### Mass multiply or divide all values in a column by a number in Excel

You can multiply or divide all values in a column by a certain number as follows.

1. Enter the certain number in a blank cell (for example, you need to multiply or divide all values by number 10, then enter number 10 into the blank cell). Copy this cell with pressing the Ctrl + C keys simultaneously.

2. Select the number list you need to batch multiply, then click Home > Paste > Paste Special. See screenshot:

3. In the Paste Special dialog box, select the Multiply or Divide option in the Operation section as you need (here we select the Multiply option), and then click the OK button.

You can see the operation result as below screenshot shown.

#### Easily multiply or divide all values in a column by a number with Kutools for Excel

The Operation tool of Kutools for Excel will help you easily mass multiply or divide all values in a column or range by a certain number in Excel. Please browse for more details.

1. Select the column with values you need to batch multiply or divide by a number, then click Kutools > More > Operation. See screenshot:

2. In the Operation Tools dialog box, select the operation type you need in the Operation box (here we select Multiplication option), enter the certain number for the multiplication operand into the textbox, and then click the OK button.

Then all selected cells are multiplied with a certain number at once.

Notes:

• You can create formulas for the list with checking the Create formulas box.
• If there already have formulas in the list, you can skip calculating these cells by checking the Skip formula cells box.

If you want to have a free trial ( 30-day) of this utility, please click to download it, and then go to apply the operation according above steps.

By Bryan Clark published 2 September 17

Doing basic math doesn’t have to involve breaking out the calculator and manually adding up cells, columns, or row. Excel has a useful feature called Formulas that allow you to do both basic math, such as addition and subtraction, or more complex items like finding averages, or even building your own formulas using super-advanced algorithms.

Today, we’re going to work through a variety of real-world uses for simple formulas in Excel. In this scenario, we’re going to tally up bills, subtract money owed, multiply bills over the course of a year, and divide them between three roommates each month.

1. Open an Excel workbook. For this example, we’re going to use a simple tally of expenses and add them. But based on the formula you choose in step TKTK, you can just as easily subtract, multiply, or divide the cells to create an average.

2. Choose the cell you want to use to display the solution to your simple formula.

3. For addition, the solution is an easy one. We simply need to tell Excel we’re adding, and then determine which cells we want to add up. Use this formula: =SUM(D2:D7)

4. For subtraction, it’s not quite as easy as we can’t subtract multiple cells with the same type of formula. Instead, we have to enter each cell by hand. So if we wanted to subtract the cell phone bill from the rent, for example, we would use this formula: =D5-D6

5. For multiplication, the formula is mainly the same as subtraction. To multiply the phone bill by 12, for example, use this formula: =D6*12 (or you can multiply two cells in the workbook much in the same way as you subtracted them, just an asterisk instead of a minus sign).

6. And for division, you can use this formula: =D9/3

In this guide, we’re going to show you how to split text in Excel by a delimiter.

We have a sample data which contains concatenated values separated by “|” characters. It is important that the data includes a specific delimiter character between each chunk of data to make splitting text easier.

## Text to Columns feature for splitting text

When splitting text in Excel, the Text to Columns is one of the most common methods. You can use the Text to Columns feature with all versions of Excel. This feature can split text by a specific character count or delimiter.

2. Click Data > Text to Columns in the Ribbon.
3. On the first step of the wizard, you have 2 options to choose from – these are slicing methods. Since our data in this example is split by delimiters, our choice is going to be Delimited.
4. Click Next to continue
5. Select the delimiters suitable to your data or choose a character length and click Next.
6. Choose corresponding data types for the columns and the destination cell. Please note that if the destination cell is the same cell as where your data is, the original data will be overwritten.
7. Click Finish to see the outcome.

## Using formulas to split text

Excel has a variety of text formulas that you can use to locate delimiters and parse data. When using formulas to do so, Excel automatically updates the parsed values when the source is updated.

The formula used in this example uses Microsoft 365’s dynamic array feature, which allows you to populate multiple cells without using array formulas. If you can see the SEQUENCE formula, you can use this method.

### How it works

The formula replaces each separator character with space characters first. (see SUBSTITUTE) The number of space characters will be equal to the original string’s character count, which is enough number of spaces to separate each string block (see REPT and LEN).

The SEQUENCE function generates an array of numbers starting with 1, up to the number of maximum columns. Multiplying these sequential numbers with the length of the original string returns character numbers indicating the start of each block.

This approach uses the MID function to parse each string block with given start character number and the number of characters to return. Since the separators are replaced with space characters, each parsed block includes these spaces around the actual string. The TRIM function then removes these spaces.

## Flash Fill

The Flash Fill is an Excel tool which can detect the pattern when entering data. A common example is to separate or merge first name and last names. Let’s say column A contains the first name – last name combinations. If you type in the corresponding first name in the same row for column B, Excel shows you a preview for the rest of the column.

You can see the same behavior with strings using delimiters to separate data. Just select a cell in the adjacent column and start typing. Occasionally Excel will display a preview. If not, press Ctrl + E like below to split your text.

## Power Query

Power Query is a powerful feature, not only for splitting text, but for data management in general. Power Query comes with its own text splitting tool which allows you to split text in multiple ways, like by delimiters, number of characters, or case of letters.

1. Select the cells containing the text.
2. Click Data >From Sheet. If the data is not in an Excel Table, Excel converts it into an Excel Table first.
3. Once the Power Query window is open, find the Split Column under the Transform tab and click to see the options.
4. Select the approach that fits your data layout. The data in our example is using By Delimiter since the data is separated by “|”.
5. Power Query will show the delimiter character. If you are not seeing the expected delimiter, choose from the list or enter it yourself.
6. Click OK to split text.
7. If your data includes headers in its first row, like our example does, click Transform > Use First Row as Headers to keep them as headers.
8. One you satisfied with the result, click the Home > Close & Load button to move the split data into your workbook.

VBA is the last text splitting option we want to show in this article. You can use VBA in two ways to split text:

1. By calling the Text to Columns feature using VBA,
2. Using VBA’s Split function to create array of sub-strings and using a loop to distribute the sub-strings into adjacent cells in the same row.

### Text to Columns by VBA

The following code can split data from selected cells into the adjacent columns. Note that each supported delimiter is listed as an argument which you can enable or disable by giving them either True or False. Briefly, True means that you want to set that argument as a delimiter and False means ignore that character.

An easy way to generate a VBA code to split text is to record a macro. Start a recording section using the Record Macro button in the Developer tab, and use the Text to Columns feature. Once the recording is stopped, Excel will save the code for what you did during recording.

Bryan has worked in journalism and publishing for more than 15 years. For the last 10 years, he’s covered the technology beat, including gadgets, social media, security, and web culture. Before working as a freelancer, Bryan was the Managing Editor for The Next Web. These days he spends his time at a number of publications, both online and off, including The New York Times, Popular Science, and The Next Web, among others. Read more.

If you start an Excel workbook by grouping data into the same cell and later decide to ungroup it, Excel has several easy functions that can split one spreadsheet column into two. Here’s how to use both “Text to Columns” and “Flash Fill.”

## How to Use “Text to Columns” in Excel

Select the cells you want to split by clicking the first cell and dragging down to the last cell in the column. In our example, we’ll split the first and last names listed in column A into two different columns, column B (last name) and column C (first name.)

Click the “Data” tab at the top of the Excel Ribbon.

Click the “Text to Columns” button in the Data Tools section.

In the Convert Text to Columns Wizard, select “Delimited” and then click “Next.” Delimited works great in our example, as the names are separated by commas. If the names were separated only by a space, you could select “Fixed width” instead.

Check both the “Comma” and “Space” delimiters and then the “Next” button. Delimiters are simply how the data is separated. In this case, we’re using comma and space because each cell in column A has a comma and a space separating the two. You can use any delimiter that fits your data set.

Next, we’re going to click the cell where we want to start adding the data—in this case B2—and click “Finish.” This will add the first and last names to their respective columns.

We could do this differently—for example, adding first names to column B and last names to column C. To do so, we’d highlight the first names in the wizard (notice the black highlight in the screenshot that signifies the active column) and then click the appropriate cell.

You may notice a chime and then an inability to select the cell you want to move the data into. If this happens, just click inside the “Destination” area within the wizard or add the information manually into the Destination field.

## How to Use “Flash Fill” in Excel

If you only have a few names, and you don’t want to mess with the Text to Columns Wizard, you can use Flash Fill instead. This, in essence, is a smarter way to copy and paste the data into new cells.

Click inside the first cell of the appropriate column—the one named “First, in our example—and type in the first name of the first person in your dataset.

Hit “Enter” on the keyboard to move to the next cell down. From the “Home” tab on the ribbon, click “Editing” and then “Flash Fill.”

Alternatively, you can press Ctrl+E on your keyboard.

Flash Fill will try to figure out what you’re trying to accomplish—adding only the first names in this example—and paste the results into the appropriate cells.

Second, click inside the first cell of the Last column and type in the last name of the appropriate person, and hit “Enter” on the keyboard.

From the “Home” tab, click “Editing” and then “Flash Fill.” Or, use the Ctrl + E keyboard shortcut.

Once again, Flash Fill will attempt to figure out the data you want to fill into the column.

If Flash Fill doesn’t work properly, there’s always Undo (Ctrl+Z).

If you have a long list of data needed to be split into multiple equal groups as following screenshot shown, how could you deal with this task quickly and easily in Excel?

#### Split a long list into multiple equal groups with VBA code

Excepting copy and paste the data one by one, the following VBA code also can do you a favor, please do with following steps:

1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following code in the Module Window.

VBA code: Split a long list into multiple equal groups

3. Then press F5 key to run this code, and in the popped out box, please select the column that you want to split into multiple groups, see screenshot:

4. And click OK button, then select a cell where you want to locate the result in the following prompt box, see screenshot:

5. Click OK, and please enter the number of cells that you want to split per column in the prompt box, see screenshot:

6. Finally, click OK to finish the code, and the selected list data has been split into multiple equal groups as you need, see screenshot:

#### Split a long list into multiple equal groups with Kutools for Excel

If you have installed Kutools for Excel, with its Transform Range feature, you can quickly split a long list into multiple columns and rows, moreover, you can also combine multiple columns into one long list.

After installing Kutools for Excel, please do as follows:

1. Select the long list that you want to split, and then click Kutools > Range > Transform Range, see screenshot:

2. In the Transform Range dialog box, select Single column to range under the Transform type section, and then check Fixed value and specify the number of cells per row in the box, see screenshot:

3. Then click Ok, and select a cell where you want to locate the result in the prompt box, see screenshot:

4. And click OK button, the data in the list has been split into multiple equal groups as you need.

In statistics, quintiles are numbers that split a dataset into five groups of equal frequency.

The first quintile is the point where 20% of all data values lie below it. The second quintile is the point where 40% of all data values lie below it, and so forth.

We can use the following function to calculate the quintiles for a dataset in Excel:

The following example shows how to use this function in practice.

### Example: Calculate Quintiles in Excel

Suppose we have the following dataset with 20 values:

The following image shows how to calculate the quintiles for the dataset:

The way to interpret the quintiles is as follows:

• 20% of all data values lie below 6.8.
• 40% of all data values lie below 14.
• 60% of all data values lie below 20.8.
• 80% of all data values lie below 26.2.

We can also use the following formula to calculate each quintile at the same time:

The following image shows how to do so:

Notice that the quintiles calculated here match the quintiles we calculated earlier.

## How to Split/Divide Excel Cells – 4 Methods with Examples

• Post author:Amos Gikunda
• Post published: June 14, 2021
• Post category:VBA

We are used to working with whole excel cells within columns and rows. At times, you find yourself working with data imported from other sources or even working with data that is not in the format you are used to. Such data can appear in one cell alone (especially when working with texts with limited commas). Most beginners using Microsoft Excel spreadsheets may not be aware that it is possible to split a cell into two or multiple smaller ones to solve such a dilemma. Unfortunately, I do not mean to split one cell into two or numerous cells figuratively. By dividing cells in Excel, we suggest adding a new column, changing the column widths, and merging two cells into one.

Splitting excels cells helps provide better sorting and filtering features for your data. Using the Unmerge Cells, Text to Column feature, and Flash Fill features, you will be able to split Excel cells. In this article, we learn how to separate Excel cells using different methods. Let’s get started.

Method 1: splitting cells using the Delimiter with Text to Column feature

1. In an open Excel workbook, click and select all the cells you want to split.

2. from the main menu ribbon, click on the Data tab.

3. Under the Data Tools tab, select the ‘Text to Columns’ option to display the ‘Convert Text to Columns Wizard – Step 1 to 3’ dialog box.

4. In the dialog box, for step 1, check on the ‘Delimited’ option checkbox. Click Next to proceed.

5. Under the’ Delimiters’ section, step 2 of the wizard dialog box, specify the delimiter you want to split. You will be able to see a preview of any applied delimiters you select in the ‘Data preview’ field. Click Next to proceed.

6. In the 3 rd step, select a Destination for your selected cells data.

7. Click Finish. Every text or value string in that cell will split into different cells.

Method 2: splitting cells using VBA macro

1. In this example, I will split the cells with the following names using VBA macro

1. Go to the Developers tab and click on the ‘Visual Basic’ option or (Press Alt+F11 to access the visual basic editor)

2. The ‘Visual Basic Editor’ window will be displayed.

3. Click on the Insert tab. From the given options, select Module to create a new module

4. Type in VBA code into the ‘Code Window.’

Public Sub SplitName()

X = Cells(Rows.Count, 1).End(xlUp).Row

For A = 1 To X

B = InStr(Cells(A, 1), ” “)

C = InStrRev(Cells(A, 1), ” “)

Cells(A, 2) = Left(Cells(A, 1), B)

Cells(A, 3) = Mid(Cells(A, 1), B, C – B)

Cells(A, 4) = Right(Cells(A, 1), Len(Cells(A, 1)) – C)

Next A

End Sub

5. Click the Run tab and select Run Macro to display a dialog box. (Or simply press F5 to run the code)

6. In the ‘Macro’ dialog box, click the Run button.

Method 3: using the flash fill feature

1. In your open workbook, select empty cell columns right beside the main column you want to split.

2. In the first cell of the first column, type in the first value or text you want to split and press Enter.

3. Type the second name in the second cell. Flash fill feature will appear. If not Go to the Data tab on the main ribbon, click on the Flash Fill option indicating the cell you had typed in. you will see that the first part (all values or texts) in the main column has been split automatically in the adjacent column.

4. Carry on the process for the other columns to separate all text or value strings

Method 4: splitting cells by using the unmerge cells option in Excel.

While working on imported Excel files, you may find the data merged, or some cells are combined. An easy way to split these cells is by clicking on the Unmerge Cells options. To do this;

1. In your open workbook, select all the cells you need to split.

2. In the Home tab, under the Alignment group, click on the Merge & Centre drop-down arrow.

3. Select Unmerge Cells, and this will divide your merged cells.

Conclusion

The article below gives you different methods on how to split or divide cells. As a constant Excel user who comes across cells you need to separate, you can select either of the methods above to achieve this.

This page describes how to split a string in Excel using Excel’s Left, Mid and Right functions.

Split String Index:

## Split a String at a Specified Position

There are three built-in Excel functions that are designed for splitting a string at a specified position. These are the Excel Left, Mid and Right functions. These functions are each described below:

### Excel Left, Mid and Right Functions

The Excel Left function returns a specified number of characters from the left (the beginning) of a supplied text string.

In the example below, the Left function returns the first two characters of the string “test string”:

The Excel Mid function returns a specified number of characters from the middle of a supplied text string, beginning at a specified character.

In the example below, the Mid function returns 3 characters from the middle of the string “test string”, starting from character number 6:

The Excel Right function returns a specified number of characters from the right (the end) of a supplied text string.

In the example below, the Right function returns the last two characters of the string “test string”:

## Split a String at the First Occurrence of a Specified Character

If you want to split an excel text string at the first occurrence of a specified character, (e.g. at the first space), there is no built-in Excel function to do this. However, you can perform this task using the Left, Mid or Right functions, combined with other built-in Excel functions.

The other Excel functions that you may find useful when splitting a string at a specified position are:

 Find – Returns the position of a sub-string within a supplied string (case-sensitive). Search – Returns the position of a sub-string within a supplied string (not case-sensitive). Len – Returns the length of a supplied text string.

Note that the only difference between the Find and Search functions is that the Find function is case-sensitive, while the Search function is not.

## Split String at a Specific Character – Examples

### Example 1 – Return Text From the Beginning of a Text String, Up to the First Space

If you want to use a formula to split a text string at the first space, and then return the left part of the split string, this can be done by combining the Left function with the Find function. This is shown in the example below:

A B
1 test string =LEFT( A1, FIND( ” “, A1 ) – 1 ) – returns the result “test”

In the above formula, the Find function returns the value 5 as the position of the space within the supplied text “test string”. Subtracting 1 from this value gives the value 4, which is then supplied to the Left function.

### Example 2 – Return Text From the End of a Text String

If you want to use a formula to split a text string at the first space, and then return the right (the end) part of the string, this can be done by combining the Right function with the Excel Find function and the Excel Len function. This is shown in the example below:

A B
1 test string =RIGHT( A1, LEN( A1 ) – FIND( ” “, A1 ) ) – returns the result “string”

In the above formula, the Len function returns the value 11, as the length of the string “test string” and the Find function returns the value 5 as the position of the space.

Therefore, the expression LEN( A1 ) – FIND( ” “, A1 ) evaluates to 6 (= 11 – 5), which is then supplied to the Right function.

Therefore, the Right function returns the last 6 characters of the supplied string.

## Split a String at the N’th Occurrence of a Specified Character

The problem with the Excel Find and Search functions is that they can only be used to find the first occurrence of a specified character (or string of characters), after a specified start position. So what can you do if you want to split your string at the N’th space?

One way to find the position of the N’th occurrence of a character is to use the Excel Substitute function, combined with the Excel Find or Search function.

The Substitute function substitutes the N’th occurrence of a specified string, with a second supplied string. The Find function can then be used to return the position of your substitute string, and this position can then be supplied to the Left, Mid or Right function.

An example of this is provided below.

### Split String at the N’th Occurrence of a Specified Character – Example

In this example, we return the left part of the original text string “An example text string”, up to the third space. For clarity, we will first break the formula down into 3 stages:

A
1 An example text string
2 =SUBSTITUTE( A1, ” “, “|”, 3 ) – returns the result “An example text|string”
3 =FIND( “|”, A2 ) – returns the result “16”
4 =LEFT( A1, A3 – 1 ) – returns the result “An example text”

In the first stage of the above formula, we have substituted the third space with the character “|”. The reason for choosing this character is that we know it does not occur in the original text.

The three stages shown in cells A2 – A4 of the above spreadsheet above return the left part of the original text string, up to the third space. If you are confident with Excel formulas, you may prefer to combine these three stages into a single formula, as shown below:

This Tutorial demonstrates how to use the Excel QUOTIENT Function in Excel to calculate the integer portion of division.

## QUOTIENT Function Overview

The QUOTIENT Function Returns the integer result of dividing two numbers.

To use the QUOTIENT Excel Worksheet Function, select a cell and type:

(Notice how the formula inputs appear)

## QUOTIENT Function Syntax and Inputs:

numerator – A number for the numerator.

denominator – A number for the denominator.

## QUOTIENT Function

The QUOTIENT Function divides the number entered and returns the integer portion of their division.

The QUOTIENT Function will divide 8 by 3 resulting in 2.66. Here the remainder of division is discarded and the Function returns 2.

## QUOTIENT Function – Decimal Numbers

The QUOTIENT Function can perform division of decimal numbers & returns the integer portion of their division.

## QUOTIENT Function – Negative Numbers

The QUOTIENT Function can perform division of negative numbers & returns the integer portion of their division.

## QUOTIENT Function – Divide by 0

The QUOTIENT Function returns an error when the second argument/denominator is zero.

## QUOTIENT Function – Error

The QUOTIENT Function returns an error when non-numeric arguments are entered.

## QUOTIENT Function – Finding the remainder of a division

The QUOTIENT Function can be used to find out the remainder of a division.

Here the formula multiples the divisor/denominator with the quotient (integer portion of the division). It then subtract it from the dividend/numerator to calculate the remainder.

The QUOTIENT Function works exactly the same in Google Sheets as in Excel:

Use the QUOTIENT Function to calculate the integer result after dividing two numbers.

## QUOTIENT Examples in VBA

You can also use the QUOTIENT function in VBA. Type:
Application.Worksheetfunction.Quotient(numerator,denominator)
For the function arguments (numerator, etc.), you can either enter them directly into the function, or define variables to use instead.

### Excel Practice Worksheet

Practice Excel functions and formulas with our 100% free practice worksheets!

• Learn Excel, Inside Excel!

You’ve learned how to add and subtract in Excel. Now let’s tackle multiplying and dividing. This tutorial provides clear and easy examples of the best ways to multiply and divide in an Excel worksheet.

First we’ll discuss two ways of multiplying in a worksheet—depending on how many numbers are being multiplied together. Then we’ll discuss how to divide numbers in a worksheet.

1) All Excel formulas must begin with an equal = sign
2) Excel automatically capitalizes letters in formulas for easier viewing
3) To specify a cell range, type the first and last cell address separated by a colon :

## How to Multiply Numbers

To have Excel calculate the product of two numbers, type =X*Y , where X and Y are numbers, cell addresses that contain numbers, or formulas that resolve to numbers. For example, if you enter =4*3 in a spreadsheet cell, Excel returns a value of 12 .

The asterisk * is Excel’s arithmetic operator for multiply.

Look at the sample worksheet below which contains multiplication formulas with cell references (addresses). The formula in cell D2 is =B2*C2 as seen in the formula bar.

In this formula and all those in Column D, we’re multiplying Units Sold (Column B) with Price per Unit (Column C) to calculate each product’s Revenue . The formula in cell D2 resolves to 4 x \$500 = \$2,000 .

## How to Multiply Many Numbers Together

A large quantity of numbers can be multiplied together in the above manner, but that might entail a lot of typing and really long formulas. Increase productivity by learning the PRODUCT function of Excel. It’s easy. Our formula in cell E2 is =PRODUCT(B2:D2) .

To calculate Commission Paid in cell E2, we multiply B2 x C2 x D2 : Units Sold x Price per Unit x Commission Rate . As the cells are contiguous, we can type their range B2:D2 in the function. We could also write =PRODUCT(B2,C2,D2) as we did in cell E6 on the worksheet.

And of course we could find the product with the simple formula =B2*C2*D2 as we did in cell E7. But the Product function saves time—use it when you can.

## How to Divide Numbers in Excel

To divide one number into another in Excel, use the formula =C/D , where C and D are numbers, cell references that contain numbers, or formulas that resolve to a number. For instance, if you enter =10/2 in a cell, Excel returns a value of 5 .

The forward slash / is Excel’s symbol for divide.

In the sample spreadsheet below, we want to calculate miles per hour for a list of drivers. The formula in cell D2 is =B2/C2 .

In the formula, we’re dividing Total Miles by Hours to calculate Miles per Hour as follows: 952 / 17 = 56 .

For a tutorial that discusses performing math functions in Excel worksheets, see our tutorial Excel Math Basics: Writing Formulas and Expressions. Cheers!

In the last lesson, you used variables to add and subtract with Excel VBA code. In this lesson, you’ll learn how to multiply and divide.

### Multiplication

In programing languages, the multiplication sign is the asterisk (*). So if you want to multiply 10 by 5 in VBA you could do it like this:

Dim Number_1 As Integer
Dim Number_2 As Integer

Number_1 = 10
Number_2 = 5

Try it out for yourself. Return to your coding window. Add another Sub and call it Multiply_Numbers. In between Sub and End Sub type the code above.

The code is more or less the same as before. The only differences are the cell references (A3 and B3) and the multiplication sign (*). Your coding window should look like this:

As with Addition and Subtraction, you can use more than two numbers or variables in your calculations. So these are fine:

Answer = Number_1 * Number_2 * Number_3
Answer = Number_1 * Number_2 * 10

You can mix the Addition, Subtraction and Multiplication, but you need to take care. For example, what is the correct answer to the sum below?

Answer = 10 * 2 + 5

If you do the sum from left to right you’d first multiply the 10 and the 2 to get 20. Now add the 5 to get and answer of 25. However, if you work form right to left, you’d first add the 5 and the 2 to get 7. Multiply 7 by 10 and you’d get 70, a totally different answer!

VBA works things out from left to right. But you can force the answer you need by using round brackets:

Answer = 10 * (2 + 5)

The round brackets above surround the 2 + 5. VBA takes this to mean you want to add these two numbers first. Once it has an answer it will then do the rest of the calculation. It’s a good idea to use round brackets to avoid any confusion.

### Division

The symbol to use when you want to divide numbers is the forward slash (/).

Try out some division for yourself. Return to your coding window and add a new Sub. Call it Divide_Numbers. In between Sub and End Sub, type the following code:

Dim Number_1 As Integer
Dim Number_2 As Integer

Number_1 = 10
Number_2 = 5

Your coding window will then look like this:

Return to Excel and add a new button to your spreadsheet. From the Assign Macro dialogue box select your Divide_Numbers Sub. Change the text on the button. When you click your new button, you should see a new line appear:

Now go back to your code. Change Number_2 from 5 to 4:

Number_1 = 10
Number_2 = 4

So we’re now dividing 10 by 4. Return to Excel and click your Division button. What answer do you get? Instead of the expected 2.5 you still get 2! The reason VBA has chopped off the .5 at the end is because we’re using As Integer in our code. When you use the As Integer variable type you only get whole numbers, not fractions. To get a “point something” you need to use a different variable type. You’ll learn more about the different variable types shortly. For now, change your code to this (the new lines are in bold):

Dim Number_1 As Integer
Dim Number_2 As Integer
Dim Number_3 As Integer

Number_1 = 8
Number_2 = 8
Number_3 = 4

Answer = Number_1 + Number_2 / Number_3

What we’re doing here is mixing some addition with division. Our sum is really this:

Answer = 8 + 8 / 4

You may think that this says “first add 8 and 8 then divide by 4”. The answer you’d be expecting is 16 / 4, which is 4. However, try out the code by clicking the button on your spreadsheet and you’ll find that the answer you actually get is not 4 but 10! So what’s going on?

The reason you get 10 and not 4 is because of something called operator precedence. All this means is which of the mathematical operators (+, -, * /) has priority. VBA sees division as more important than addition, so it does the dividing first. Replace the / symbol with the * symbol and you’ll find that multiplication is also more important than addition. So the answer to this sum:

Answer = 8 + 8 * 4

is 40, according to VBA, and not 64.

With operator precedence you have to take into account the following:

• Division and Multiplication are done before addition and subtraction
• Division and Multiplication have equal priority and so are calculated from left to right, as long as there’s no addition and subtraction to do
• Addition and subtraction have equal priority and so are calculated from left to right, as long as there’s no division and multiplication to do

If the above is somewhat confusing, just remember to use round brackets to make it clear to VBA what you want to do:

Answer = (8 + 8) / 4

In the above sum, VBA will now add 8 to 8, because of the round brackets. The answer to whatever is between the round brackets will then get divided by 4.

One last thing about the division symbol: don’t confuse it with the backslash character (\), which is used for something called Integer Division. This is when you take the whole number answer and throw away any “point something” at the end. For example, try this code:

As you expect, the answer in the message box is 5.5, because 11 divide by 2 is 5.5. However, change the forward slash into a backslash:

Now the answer you get in the message box is just 5: the point 5 at the end has been chopped off. So just be aware that the backslash in Excel VBA gets you Integer Division, not regular division.

In the next part, you’ll learn about some more variable types in Excel VBA.

Excel doesn’t have a divide function, so performing division in Excel requires you to create a formula. Let’s learn how to use formulas to divide numbers and calculate percentages in Excel.

dividing in Excel is easy, but you need to create a simple formula to do it. Just remember that all formulas in Excel begin with an equal sign (=), and you can use the formula bar to create them.

Division in Excel

Here are some important points to remember about Excel formulas:

• Formulas begin with the equal sign ( = ).
• The equal sign goes in the cell where you want the answer to display.
• The division symbol is the forward slash ( / ).
• The formula is completed by pressing the Enter key on the keyboard.

1. The formula below divides numbers in a cell. Use the forward slash (/) as the division operator. Don’t forget, always start a formula with an equal sign (=).

2. The formula below divides the value in cell A1 by the value in cell B1.

3. Excel displays the #DIV/0! error when a formula tries to divide a number by 0 or an empty cell.

4. The formula below divides 43 by 8. Nothing special.

5. You can use the QUOTIENT function in Excel to return the integer portion of a division. This function discards the remainder of a division.

6. The MOD function in Excel returns the remainder of a division.

Take a look at the screenshot below. To divide the numbers in one column by the numbers in another column, execute the following steps.

7a. First, divide the value in cell A1 by the value in cell B1.

7b. Next, select cell C1, click on the lower right corner of cell C1 and drag it down to cell C6.

Take a look at the screenshot below. To divide a column of numbers by a constant number, execute the following steps.

8a. First, divide the value in cell A1 by the value in cell A8. Fix the reference to cell A8 by placing a \$ symbol in front of the column letter and row number (\$A\$8).

8b. Next, select cell B1, click on the lower right corner of cell B1 and drag it down to cell B6.

Explanation: when we drag the formula down, the absolute reference (\$A\$8) stays the same, while the relative reference (A1) changes to A2, A3, A4, etc.

Updated November 1, 2021

## How To Perform Arithmetic Operations in Excel

In this tutorial, we are going to perform basic arithmetic operations i.e. addition, subtraction, division and multiplication. The following table shows the data that we will work with and the results that we should expect.

S/N ARITHMETIC OPERATOR FIRST NUMBER SECOND NUMBER RESULT
1 Addition (+) 13 3 16
2 Subtraction (-) 21 9 12
3 Division (/) 33 12 2.75
4 Multiplication (*) 7 3 21

Let’s now use Microsoft excel to achieve the above results

### Step 1) Create an Excel Sheet and Enter the Data

Create a folder on your computer in my documents folder and name it Guru99 Excel Tutorials

For this tutorial, we will be using Microsoft Excel 2013. The good news is even if you have Microsoft Excel 2007 or 2010, you will still be able to follow the tutorial and get the same result.

Open Excel. You will get a window similar to the one shown below. The outlook of Excel will depend on your version.

• Enter the data in your worksheet as shown in the image above.
• We will now perform the calculations using the respective arithmetic operators. When performing calculations in Excel, you should always start with the equal (=) sign.
• Let’s start with the one for addition. Write the following formula in E2 Excel (Result column)
• =C2+D2

HERE,

• “=” tells Excel to evaluate whatever follows after the equal sign
• “C2” is the cell address of the first number given by C representing the column letter and 2 representing the row number
• “D2” is the cell address of the second number given by D representing the column letter and 2 representing the row number

Press enter key on the keyboard when done. You should get 16 as the result.

Using the knowledge gained in the above example, try to write the formulas for subtraction, division, and multiplication.

### Step 2) Format Data in Microsoft Excel

We all love beautiful things don’t we? Formatting in Excel helps us achieve exactly that. We can make our spreadsheets more presentable. We will use the data in the arithmetic operations table. We will make the column names;

• Bold
• Align serial numbers to the left

### Step 3) Make Column Names Bold

• Highlight the cells that have the column names by dragging them.
• Click on the bold button represented by B command.
• Your workbook should now appear as follows

### Step 5) Enclose Data in Boxes

Highlight all the columns and rows with data

On the font ribbon bar, click on borders command as shown below.

You will get the following drop down menu

Select the option “All Borders”.

Your data should now look as follows

Using the knowledge gained above, try to change the font color and try out other options available on the Home tab.

### Step 6) Set the Print Area, Print Preview & Page Layout

The print area is the part of the worksheet that you would like to print out on paper. The quick and easy way of doing it is by using the following shortcut commands

You will get the following print preview.

Press Esc button to exit print preview mode

The page setup ribbon bar has a number of options i.e. orientation, size, etc. Try to apply the different settings and use Ctrl + P shortcut to preview the effects on the worksheet.

## Summary

In this article, we have learned how to perform basic arithmetic operations using Excel, Formatting data, How to Setting the print area, and printing (Print View).

For example, you want to demonstrate the changes in sales due to the company policy during 2009 – 2011 and 2013 – 2015:

To create one chart with separator, follow these steps:

1. Insert a blank row between separated parts of the chart (in this example – B14).

2. Select the data cells.

3. On the Insert tab, in the Charts group, choose the Column button:

Choose the Clustered Column chart:

4. Open Format Axis on the Format pane, in which:

In the Axis Options section, choose the field Text axis (to hide the gap period):

5. In the source data (B5:C23), clear the duplicate years. As you clear an entry in the worksheet, the corresponding entry along the chart axis disappears.

You can then make any other adjustments to get the look you desire.

6. Select the chart. On the Insert tab, in the Illustrations group, choose the type of object what you want to add in the chart:

If you have any questions or suggestions, please feel free to ask OfficeToolTips team.

### How to create a Marimekko chart or Mekko chart in Excel

• Pinterest
• Privacy
• Terms
• Contact

We use cookies to personalise content and ads, to provide social media features and to analyse our traffic. We also share information about your use of our site with our social media, advertising and analytics partners who may combine it with other information you’ve provided to them or they’ve collected from your use of their services.

Excel provides you different ways to calculate percentages. For example, you can use Excel to calculate the percentage of correct answers on a test, discount prices using various percent assumptions, or percent change between two values. Calculating a percentage in Excel is an easy two-step process. First, you format the cell to indicate the value is a percent, and then you build the percent formula in a cell.

### Microsoft Excel

Turn data into insights.

### Format values as percentages

To show a number as a percent in Excel, you need to apply the Percentage format to the cells. Simply select the cells to format, and then click the Percent Style (%) button in the Number group on the ribbon’s Home tab. You can then increase (or decrease) the the decimical place as needed. (See Rounding issues below for more information.)

In Excel, the underlying value is always stored in decimal form. So, even if you’ve used number formatting to display something as a percentage (10%), that’s just what it is—formatting, or a symbolic representation of the underlying value. Excel always performs calculations on that underlying value, which is a decimal (0.1). To double-check the underlying value, select the cell, press Ctrl + 1, and look in the Sample box on the General category.

Here are a few things to keep in mind when formatting percentages:

Format existing values—When you apply percentage formatting to a cell that already has a number in it, Excel multiplies that number by 100 and adds the % sign at the end. So for example, if you type 10 into cell A2 and then apply the percentage number format, Excel will multiply your number by 100 to show it as a percentage (remember that 1% is one part of one hundred), so you’ll see 1000% displayed in the cell, not 10%. To get around this, you can calculate your numbers as percentages first.

For example, if you type the formula =10/100 in cell A2, Excel will display the result as 0.1. If you then format that decimal as a percentage, the number will be displayed as 10%, as you ‘d expect. You can also just type the number in its decimal form directly into the cell—that is, type 0.1 and then apply percentage format.

Rounding issues—Sometimes what you see in a cell (e.g., 10%) doesn’t match the number you expected to see (e.g., 9.75%). To see the true percentage in the cell, rather than a rounded version, increase the decimal places. Again, Excel always uses the underlying value to perform calculations.

Format empty cells—Excel behaves differently when you pre-format empty cells with percentage formatting and then enter numbers. Numbers equal to and larger than 1 are converted to percentages by default; numbers smaller than 1 that are not preceded with a zero are multiplied by 100 to convert them to percentages. For example, if you type 10 or .1 in a preformatted cell, you’ll see 10% appear in the cell. Now, if you type 0.1 in the cell, Excel will return 0% or 0.10% depending on the decimal setting.

Format as you type—If you type 10% directly in the cell, Excel will automatically apply percentage formatting. This is useful when you want to type just a single percentage on your worksheet, such as a tax or commission rate.

Negative percentages—If you want negative percentages to be formatted differently—for example, to appear as red text or within parentheses—you can create a custom number format such as 0.00%;[Red]-0.00% or 0.00%_);(0.00%).

### Calculating percentages

As with any formula in Excel, you need to start by typing an equal sign (=) in the cell where you want your result, followed by the rest of the formula. The basic formula for calculating a percentage is =part/total.

In the example below, Actual Points/Possible Points = Grade %:

Say you want to reduce a particular amount by 25%, like when you’re trying to apply a discount. Here, the formula will be: =Price*1-Discount %. (Think of the “1” as a stand-in for 100%.)

To increase the amount by 25%, simply replace the minus sign in the formula above with a plus sign.

The next example is slightly more complicated. Say the earnings for your department are \$2,342 in November and \$2,500 in December and you want to find the percentage change in earnings between these two months. To find the answer, divide the difference between December and November earnings (\$158) by the value of the November earning (\$2,342).

Editor’s note 3/7/2019:
This blog post was updated to reflect the blog’s current editorial style.

Creating an Excel spreadsheet can be a formidable task if you’re not too familiar with excel formulas. Dividing in Excel is easiest when you use a formula. Don’t be overwhelmed though, formulas are simple to learn – you just need to know a few rules (kind of like math class). Once you learn those, you can go on to learn things like how to divide, how to multiply, how to subtract and how to add within your spreadsheet. These are great features to know when you’re trying to say, develop a financial forecast for your business, or create an invoice for a client. Excel has so many more great functions, but I can’t possibly cover them all here. So take some time to check out this Basic Excel course to get more familiar with the program.

## What’s a formula?

A formula are the equations that make things happen in Excel. If you want to see subtract two numbers in two different cells, you’re going to use an subtraction formula that then gives you the answer. You can choose which cells you wish to have involved in the formula and exactly what you want to happen with the data selected. Formulas are one of the most important features in Excel, along with functions.

## What are some formula rules?

For those of you just getting started with Excel formulas, there are a few things to keep in mind when writing out these equations. It’s kind of like learning a programming language, like HTML, there are rules you have to abide by in order to make the code do what you want. Here’s a few of those rules to get you started:

Formulas always begin with an equal sign ( = ). So an equation might be like =B2+B3.

The division symbol for these formulas is a slash ( / ), like =E2/F3.

Cells are identified by their column letter followed by their row number, so B1 or E8, for example.

Remember in math class how the order of the formula makes all the difference? There’s a mnemonic device to help you remember order. Please excuse my dear Aunt Sally which stands for parenthesis, exponents, multiplication and division, addition and subtraction. This is the order in which equations are calculated, so it’s important to know this and remember it when writing out formulas.

You can click on a cell to add it to an equation. To get an answer for a math formula, put the = sign in the cell you wish to have the answer in. Now click on the cell you wish to add to the equation to automatically enter its cell name into the equation. So if you want the answer in cell C3, type = in that cell and then click on A1. A1 is now a part of your equation. You can also just type A1.

You might be interested in learning how to do more than just divide, and if so, this Excel for Beginners/Intermediates training could be just what you’re looking for.

## How to Divide

Step 1

Open a new workbook in Excel by going to File–>New. If you have an existing workbook you would like to use, open it by going to File–>Open.

Step 2

Say we want to multiply cell D1 and E1 and put the answer in cell F1. Click on cell D1 and enter the number 100 in it. Now click on cell E1, right next to it, and enter the number 10. We want to divide 100 by 10.

If you already have a workbook created, figure out which cells you would like to divide.

Step 3

Click on cell F1. You can either type the formula directly into the cell, or type it in the formula bar at the top of your screen. Either way, in F1 you want to enter the formula that will divide cells D1 and E1, which is dividing 100 by 10. The equation looks like this: =D1/E1. Click away from the cell or press enter. You should see the number 10 appear, which, is the product of 100/10.

It may seem easier to just type =100/10 into cell F1, but you shouldn’t – and here’s why.

You can choose to create a formula that contains numbers like =100/10, but it’s much better if you reference the cell in the formula instead, like we did above with =D1/E1. Referencing the cells themselves allows the data within those cells to change and the answer to change accordingly.

For instance, if you specify that you want the total of D1/E1 to show up in F1, no matter what numbers are in cells D1 and E1, they will always be divided to get your answer in F1. But, if you put a formula like =100/10 in cell F1 and your data in D1 and E1 change, the answer in F1 will still be the product of the formula 100*10. This sounds more confusing than it is. Check out the pictures below to clear things up.

Here is the result with the formula =D1/E1 in cell F1:

If I change the data in cells D1 and E1 without changing the formula in F1, I’ll get the updated results.

Pretty easy, right?

However, if you were to not use the cell referenced formula, and use the numbers instead, you’ll get something like this:

See how the equation is now incorrect? 200/15 is not 10. But, 100/10 is. Since your formula used 100/10 instead of D1/E1, your answer in F1 will never change despite the change in data in cells D1 and E1.

So as you can see, sticking with the cell identifiers like D1 and E1 is going to provide you with a much more efficient and usable spreadsheet than would using integers. Dividing is much like other simple math functions in Excel, like multiply and addition. You just need to know what the symbol is for the kind of equation you want to do. Addition is a plus sign ( + ), multiplication is an asterisk ( * ), and subtraction is a minus sign ( – ). Play around with different formulas to see what you can accomplish! If you want learn some more complicated formulas, try this Excel Formulas course for starters.

If you are looking for how to divide numbers into Microsoft Excel, then you are at right place. Here, in this guide, we have mentioned about the simple and easy way to divide numbers into Excel sheet.

As you all know that, the MS Excel is spreadsheet program that allows you to organize, manage and store data. The Excel also can be used to make charts, graphs, etc. The main function of Excel sheet that is, it can divide, multiply, add and subtract numbers according to your choice. So let’s take a look at these below mentioned simple steps to know that how to divide numbers into Excel.

## How To Divide in Excel

Follow these simple and easy steps to divide numbers into Excel sheet:

Step 1: First of all, open the Microsoft Excel on your computer. (You can also select a saved spreadsheet or create a new spreadsheet)

Step 2: After you enter your data into excel sheet, select the cell where you would like the appear the answers of your division equation.

Step 3: Now, it’s time to type formula. So, you have to type an equal (=) sign into the cell.

Step 4: After that, Select the cell that you would like to use as your numerator. (For Example, “B3” This is the Value that will be divided)
Step 5: Now, you have to insert “/” Symbol, or a forward slash. (For Example, “B3/”)

Step 6: After that, select the cell that you would like to use as your denominator. (For Example, “B3/E5” This is the Value by which you will divide the first number)

Step 7: After all, press the “Enter” key, And your answer will appear in your selected cell. (For Example; Finally, your formula may look like this: “=B3/E5”)

if you have any problem in doing calculation in your MS excel program, feel free to share your views in the comments section below.

Tripboba.com – Excel or Microsoft Excel is a spreadsheet program. It’s used to create grids of text, numbers, and formulas specifying calculations. Microsoft Excel is essential for a lot of people especially those who run a business, as Excel can be used to record income and expenditures, plan budgeting, chart data, and present fiscal result.

One of the biggest benefits of using Excel is that it’s really organized. It has the ability to organize a large amount of data into a spreadsheet and charts in an orderly and logical manner. This way, people who read the data from Excel would not find difficulties in analyzing or digesting the data to be transformed into a decision for, say, their next budgeting plan.

It’s even easier when it’s used to create graphs and other visual data representation. As the most prominent spreadsheet program in the world, Microsoft Excel has features that allow its user to do a mathematical operation with certain formulas and functions.

There are many mathematical formulas offered by Excel, such as sum, average, multiplication, and division. In this article, we will talk about how to divide in Excel, as in dividing numbers. Scroll down to find out!

## 1. How to divide in Excel: what you need to know

Unsplash – Photo by Mika Baumeister

Before we dive deeper into the steps on how to divide in Excel, you need to know some basics. Basically, there is no divide function in Excel, or we can say that Excel does not offer a special formula for dividing numbers between cells. You can simply use the forward-slash symbol (/) to divide numbers in Excel.

Don’t forget to start typing in the formula bar with an equal sign (=). So, a really simple example is you place your active cell in A1 and you type =10/5 in the formula bar, the A1 cell would show you the result of the division, which is 2.

## 2. How to divide in Excel: using more than 1 cell

Tripboba – Photo by Tripboba

In cases where you have a number in one certain cell and wishes to divide it by another number in a different cell, you can also do the division operation in a really simple way.

For example, you want to have a division operation of 10/2. The number you want to divide (10) is in the cell A1 and the number you want it to divide by, or the divisor (2) is placed in cell A2. Here’s what you need to do next:

Step 1. Click an empty cell so it would be an active cell. For example, you can place it in cell A3

Step 2. Click the formula bar. Type the equal sign (=)

Step 3. Press Ctrl on your keyboard while clicking the A1 cell. The A1 cell should be activated with blue border. The formula bar will also add A1 automatically after the equal sign.

Step 4. Type the forward slash sign ( / ) at the formula bar after A1.

Step 5. Press Ctrl again while clicking the A2 cell. Now, the formula bar should show you a formula of =A1/A2

Step 6. Press enter. The A3 cell will show you the result of the division.

## 3. How to divide in Excel: What is #DIV/0! Error?

Tripboba – Photo by Tripboba

Since a number can’t be divided by zero, Excel will show you a message which shows that there’s an error on division operation with #DIV/0! in the cell where the result is supposed to be showing. This error message will also show when one of the cells (whether it’s the number you want to divide or the divisor) is empty. So, make sure you didn’t misclick while doing this operation.

Excel will also do the division operation even if it’s with numbers with decimals, so, you shouldn’t worry about it.

## 4. How to divide in Excel: the formulas

Tripboba – Photo by Tripboba

As we have mentioned before, you can simply use the forward slash sign to do division operation in Excel. But, there is a function related to division, which is QUOTIENT and MOD. QUOTIENT function will show you the integer portion of a division while discarding the remainder of a division.

Meanwhile, the MOD function returns the remainder of a division while discarding the integer. For example, the numbers are 33 in A1 cell and 6 in the A2 cell. When you use QUOTIENT, the result would show 5 as the integer portion of the division and for MOD, the result would show 3 as the remainder of the division.

With this simple example, here’s how you do it in Excel.

1. Click an empty cell to activate. For example, A3 cell.

2. Navigate to the formula bar. Type the equal sign ( = )

3. Type QUOTIENT or MOD, depending on what you want to find.

4. Type opening bracket sign ( ( ) in the formula bar

5. Press Ctrl on your keyboard while clicking A1 cell.

6. Type ( ; ) in the formula bar.

7. Press Ctrl on your keyboard while clicking A2 cell.

8. Type closing bracket sign ( ) ) in the formula bar. The formula now should be =QUOTIENT(A1,A2) or =MOD(A1, A2)

9. Type enter. The result will show in the A3 cell.