Author: Zeynep

Excel is an excellent application for mathematical operations, but sometimes you just need to organize data in tables that have text labels in every other row. We will walk through different methods to complete this task.

For this tutorial, we will use a data set of “Name”, “Date”, and “Salary”.

Using Visual Basic for Applications Code to Insert the Same Text in Every Other Row

You must enable the Developer Tab in Excel before inserting the VBA code into the worksheet.

1. Tap on File and click on Options toward the bottom of the menu.

2. Click Customize Ribbon on the left-hand side.

3. Place a check in the box next to Developer in the Main Tabs Category and press the OK button.

4. The Developer Tab appears in the Ribbon.

5. Tap Visual Basic in the Developer menu to open the Visual Basic for Applications dialog box.

6. Tap the Insert menu and select Module.

7. In the blank Module window, paste the following code and press Run.

Sub inserttexteveryonerow()
Dim Last As Integer
Dim emptyRow As Integer
Last=Range(“A” & Rows.Count).End(xlUp).Row
For emptyRow=Last to 2 Step -1
If Not Cells(emptyRow,1).Value =”” Then
Rows(emptyRow).Resize(1).Insert
Range(Cells(emptyRow,”A”), Cells(emptyRow, “C”)).Value=Array(“Name”, “Start Date”, “Salary”)
End If
Next emptyRow
End Sub

Important Notes:

• The data in your worksheet must start in cell A1
• Change the text surrounded by quotations of the Array to whatever you want them to be.
• The letters “A” and “C” represent the range of cells where the text appears.

8. The text appears in every other row of the worksheet.

Use Keyboard Shortcuts to Insert the Same Text in Every Other Row in Excel

Using keyboard shortcuts is a quick and easy technique to insert the same text in every other row in Excel; however, keyboard shortcuts are time-consuming when dealing with large amounts of data.

1. Create blank rows for the new text.

2. Select all the blank cells in the column.
3. Press F2 on your keyboard to turn on Editing Mode in the last cell of the group. You will see the cursor flashing in the cell.

4. Type the text you want in the cell.

5. Press CTRL + Enter, and the text appears in the remaining blank cells.

6. Repeat these steps for any remaining columns in your worksheet.

Then you can remove the text before or after a character (if present) after.

Use the Fill Handle to Insert the Same Text in Every Other Row in Excel

The Fill Handle in Excel works by copying a pattern that you create and it works to insert the same text in every other row in a worksheet.

1. Create your text labels in the worksheet and highlight the row with the labels and the blank row beneath it.

2. Place the mouse over the square in the bottom right-hand corner of the selected area, so the cursor turns into a black cross.

3. Press the Fill Handle box and drag it down through the rows until you have the desired number of labels.

4. Now you can copy the data under the labels.

Conclusion

Now you have a couple of quick, easy ways to insert the same text in every other row of your worksheet. We encourage you to continue learning more Excel tricks from our blog.

This includes adding alternate row colors to make the entries pop out more.

Indenting text in Excel increases the readability of lists and assists with breaking large amounts of data into sections, making it easier for you to decipher. Excel provides a few methods for indenting text.

Let’s delve into the Excel world of indenting text!

Using the Tools in the Alignment Group to Indent in Excel

1. Highlight the cell(s) that you want to indent.

2. Click the Increase Indent button in the Alignment Group.

3. The list of apples is indented in the cell.

Using the Format Cells Dialog Box to Indent in Excel

1. Select the cell(s) that you want to indent.

2. Click the Dialog Box Launcher in the Alignment Group.

3. The Format Cells Dialog Box appears.

4. The Format Cells Dialog Box has many options for indenting text, including Left, Right, or Distributed, and you control the amount of indentation by increasing the Indent options, which go up to a maximum of 250.

5. The following example shows the three types of indentation as well as one group of cells with no indent applied.

You can then copy the formatting of these cells to not do it again manually after pasting.

Using Keyboard Shortcuts to Indent in Excel

1. Select the cells that you want indented.

2. Press ALT + H + 6 on the keyboard, one key after the other to indent the text by one space. You can repeatedly use this shortcut if you choose to indent your text more than one space.

Multiple Lines of Text to Indent in Excel

Note: The following steps might not work correctly if you are using an Excel version older than Excel 365.

1. Highlight the cells or the range of cells.

2. Press the Increase Indent button in the Alignment Group to indent the text.

Conclusion

This short tutorial provided a few techniques for indenting text in Excel. Now, formatting individual cells or even multiple lines of text in one cell is a breeze.

Excel is not just for mathematical calculations, the software also offers many powerful ways to manipulate data, including separating text strings. Manipulating text is accomplished through several methods and we walk through five options in this tutorial.

Use the Find and Replace Function to Remove Text Before or After a Specific Character in Excel

 For this tutorial, we will work with employee names and company roles separated by semicolons in individual cells in Excel.

1. Select all the cells in your worksheet.

2. Tap the down arrow to the right of Find & Select in the Editing Group on the Home Tab and then click Replace…

3. Type an asterisk (*) followed by a semicolon (;) in the Find What: portion. Leave the Replace text box empty and tap the Replace all button.

4. Excel deletes the employee names from the cells, leaving just the company roles.

Use Find and Replace to Remove Text After a Specific Character in Excel (H3)

1. Select all the cells in your worksheet.

2. Click the down arrow to the right of Find & Select in the Editing Group on the Home Tab and then tap Replace…

3. Insert a semicolon (;) accompanied by an asterisk (*) in the Find What: portion of the dialog box. Leave the Replace text box empty and press the Replace all button.

4. The company roles are deleted and only the employee names remain.

Use Formulas to Remove Text Before or After a Specific Character in Excel

Functions in Excel require arguments for them to work properly.

The TEXTBEFORE function has four arguments:

• Text: The text you are searching for. This is required.
• Delimiter: The character that marks the exact place in the text before the text you want to remove. This argument is required. This is also used to separate first and last names.
• Instance_Num: The number of instances in which the delimiter appears after the text you wish to extract. This argument is optional, but the default is equal to 1.
• Match_Mode: Can be used if the text is case-sensitive. This argument is optional, although if you choose to use it, 1=case-insensitive, 0=case-sensitive. 
• Match_End: Sees the end of the text string as a delimiter. This argument is also optional; however, you can enter 0 to not match the end of the string or 1 to match the end of the string.

Now, let’s use the TEXTBEFORE function in our example.

1. Place a new column next to your original data.

2. Type the following into the first cell of the new column: =TEXTBEFORE(A3,”;”,1,1,0).

We used some of the optional arguments in the formula so that you can see them in action.

• A3 is the cell where the search text is
• The semicolon is the delimiter and must be enclosed in quotation marks
• The first number 1 is the first and only instance of the delimiter
• The second number 1 tells Excel that the match_mode is not case sensitive
• The zero signals Excel to not match the delimiter at the end of the text.

3. Use the Fill Handle in the bottom right-handle corner of the cell to copy the formula through the remaining cells.

Then you can save the data afterwards without using formulas.

Use the TEXTAFTER Function to Remove Text After a Specific Character in Excel

The TEXTAFTER function has the same arguments as the TEXTBEFORE function, except for the delimiter marks the exact position in the text string after the text you want to extract. 

Let’s apply the TEXTAFTER Function to our example data.

1. Insert a new column beside your original dataset.

2. Type the following formula into the first cell of the new column. =TEXTAFTER(A3,”;”,1,0,0).

3. Use the Fill Handle to insert the formula into the remaining cells.

Use Flash Fill to Remove Text Before or After a Specific Character in Excel

Flash Fill recognizes a pattern of data and will automatically fill a column with new data based on that pattern.

1. Insert a new column next to your data.

2. Type the text that you want to appear in the first cell of the new column and press the ENTER key.

2. Start typing the data into the next cell. You see a dim list appear with the pattern for the existing data.

3. Press the ENTER key and Excel will finish the pattern for you.

Conclusion

Now, you have learned different ways of extracting data (and maybe URLs from hyperlinks, too). Also you can check other related tutorial that explains How to Remove a Specific Character from a String.

Excel can handle many complex calculations and formulas to assist you with datasets. There are times when you need to use Excel to open files that are not native to the Excel application.

This tutorial will walk you through the steps necessary to open your .dbf file in Excel.

Open a DBF File in Excel Using the Open File Command

1. Open Excel and navigate to the .dbf file. Excel will only show native files, so you must change the file type in the dialog box from “Excel Files” to dBase files.

2. Click the .dbf file in the dialog box and tap the “Open” button.

3. The file opens in Excel and you can edit it to fit your needs.

Open a DBF File in Excel Using the Get Data Command

1. Open Excel and tap “Data” in the top menu.

2. Click the down arrow next to Get Data to expand the options, select “From Other Sources…” and click “From Microsoft Query.”

3. Tap the “Options…” button in the “Choose Data Source” dialog box.

4. Position the mouse pointer directly after .oqy in the “File Name:” section and type: “ ;*.dbf.” Next, in the “Folders:” section, navigate to the drive where the DBF file exists and click the “OK” button.

5. Click the “OK” button in the “Data Source Options” dialog box.

6. Click the “OK” button in the “Choose Data Source” dialog box, and the “Create New Data Source” dialog box appears. Input a filename in the “What name do you want to give your data source?” box, and then choose “Microsoft Access dBase Driver” from the drop-down list under “Select a driver for the type of database you want to access:”

7. Click the “Connect…” button and the ODBC dBase Setup box appears. Leave the version as dBase 5.0 and tap the “OK” button.

8. The file name path to the DBF file appears next to the “Connect…” button. Click the “OK” button.

9. You will see the filename of your DBF file highlighted in the “Databases” tab. Click the “OK” button in the “Choose Data Source” dialog box.

10. The tables and columns appear on the left-hand side of the “Choose Columns” dialog box. Highlight the tables and columns to include in your query, and then click the chevron arrow to move them into the “Columns in your query” portion of the dialog box. Click the “Next” button.

11. The query wizard allows you to filter the data if you choose. For this tutorial, we will not be filtering data. Tap the “Next” button on the wizard.

12. The “Sort Order” dialog box appears. You can select how to sort the data from your table. Once you have made sorting selections, tap the “Next” button.

13. Make sure that the “Return data to Excel” radial button is selected, and tap the “Finish” button to complete the query.

14. Choose how to import the data into Excel in the “Import Data” dialog box. For this tutorial, we will import the data as a simple table. Click “OK” to finish the import.

15. The data from the DBF file is displayed in the worksheet.

Conclusion

Now you have learned two different methods for opening a .dbf file so that you can manipulate the data in Excel. Please feel free to take a look at other tutorials on our website.

Sorting information in Excel helps with classifying your data and arranging it in ascending or descending patterns. This tutorial walks you through several ways of sorting the data, including sorting statically or using formulas to keep the datasets dynamic.

Using the Sort Function to Sort by Last Name in Excel 

1. Highlight the table that you wish to Sort.

2. Click the down arrow in the Sort and Filter Group, and then choose “Custom Sort…“

3. The Sort dialog box opens, click the down arrow next to Sort By, and click “Last Name.”

4. Next, click the “Add Level” button and select “First Name.”

5. Continue adding levels for each data type in the table, so your Sort By dialog box looks similar to this.

Sometimes the data used in a sort is not separated into its own column, so you need to extract the data and place it into another column before sorting it.

Using Text to Columns to Sort by Last Name in Excel

1. Create a new column for the Last Name field.

2. Highlight the Name column.

3. Click the Data tab and then select “Text to Columns” in the Data Tools Group.

4. The Text to Columns dialog box appears.

5. Choose whether your data is “Fixed Width” or “Delimited.” Make sure you are happy with the preview of your data and then click the “Next” button.

6. Click the checkbox next to “Space,” to let Excel know that a space separates the first name from the last name. Make sure the data preview shows a vertical line between the first and last names and click the “Next” button.

7. The next screen allows you to set the formatting for your dataset.  Given that we are working with text, leave the option selected as “General” and click the “Finish” button to complete the Text to Columns Wizard.

Note: If Excel returns a message that data already exists in the original Name column, click the “OK” button to copy over it.

Using the Find and Select Function to Sort by Last Name in Excel

1. Insert an empty column next to the names in your data and copy the Names into it so that you have two columns with the same data.

2. Select all of the data in the second column and then click on Find & Select in the Editing group of the Excel toolbar and click “Replace” to open the dialog box.

3. Type an asterisk (*) and one blank space in the “Find what:” field and leave the “Replace” field blank.

4. Tap the “Replace All” button and then close the dialog box.

Note: Using the Find and replace option in Excel is fast and easy but the only problem is that it leaves the data static. When you add names to the list, you must go back and perform Find and Replace for each new name you add to the column. So, implementing a formula into the Last Name column will save you time because you will only have to copy the formula to each new addition.

Use the LEN, RIGHT, and SEARCH Functions to Sort by Last Name in Excel

The RIGHT and LEN Functions are used to count the characters in the text string while the SEARCH function finds a specific place in a string of text, which happens to be a space in our example.

1. Insert a new column next to the column of names in your table.

2. Type the following formula in the first cell of the new column: =RIGHT(A2,LEN(A2)-SEARCH(” “,A2,1))

3. Copy the formula from cell B2 through the remaining cells in the column using the Fill Handle, and your new column will look similar to the image below.

Using the Flash Fill Method to Sort by Last Name in Excel

The Flash Fill concept in Excel is programmed to recognize patterns in your data and predict the patterns and fill them in without much effort from you. Here is how the Flash Fill method works in Excel.

1. Create an empty column for the Last Names in Excel.

2. Type the Last Name of the first person in the dataset and then press the <ENTER> key.

3. Start typing the Last Name of the second person in the list and a list will appear for the remainder of the data set.

4. Press the <ENTER> key again and the last names appear in the column.

Using Power Query to Sort by Last Name in Excel

1. Click any cell in your data.
2. Click the “From Table” icon in the Get & Transform Group.

2. The Create Table dialog box appears. Make sure that you select the entire table and press the “OK” button.

Note: If Excel returns a Security Message, press “OK” to accept it.

4. The Power Query Editor opens.

5. Click the down arrow next to “Split Columns,” and tap the “By Delimiter” option.

6. The Split by Delimiter dialog box opens. Select “Space” as the delimiter and then select “Right-most delimiter” in the “Split at” section and then press the “OK” button.

7. The Power Query splits the Last Name from the First Name and places the Last Names in a new column.

8. Click “Close & Load” in the upper left-hand corner of the Power Query dialog box.

9. The dialog box closes and the new table appears in the Excel sheet.

10. You can rename the columns previously changed by the Power Query. Click the Name.1 cell and insert a new name in the Formula Bar. Repeat the process for the Name.2 cell. We will choose “First Name” and “Last Name” for our example.

Now that you have built a Power Query, the next time you need to separate First Names from Last Names, you can just connect your power query to the file and run the query to complete the process.

Conclusion

Hopefully, this tutorial taught you some different ways to separate and sort data and inspires you to continue learning the different aspects of working with data in Excel.

What makes Excel so flexible is the variety of formats you can use and the ability to format text, numbers, and dates any way you want. This tutorial will teach us how to convert fractions into percent format.

We will look at two ways of converting a fraction into a percentage. Let’s dive in…

Convert Fraction to Percent Using the Number Format Panel

When you go to the Home tab in Excel, you will see a Number Format panel:

You can change the format of the numbers in your spreadsheet using this panel. Now let’s see how we convert a fraction to a percent.

Step 1: Copy (Ctrl+C) the fraction numbers and paste (Ctrl+V) them to a new column/to new cells. You can also change their format without copying if you don’t want to preserve the fractions.

Step 2: Select the fraction numbers you want to convert.

Step 3: Under the Home tab in Excel, click on the Percent Style icon in the Number Format panel.

This will convert all fraction numbers under selection into a percentage.

However, the percentages are without decimal places. If you want more precision, you can increase the number of decimal places through the Increase Decimal icon.

Select the percentages, and then click on the Increase Decimal icon in the Number Format panel.

If you press twice on the icon, the percentages will look like this:

That’s enough precision for me. Nice job, we easily converted fractions into percentages with two decimal places.

Convert Fraction to Percent Using Format Cells

If you need more specific formatting, you can use the Format Cells dialog box to format the fractions, which has many different options.

Step 1: First copy the fraction numbers and paste them into a new column so as not to lose their original values.

Step 2: Select the cells with fractions, and then right-click. Click on Format Cells… to open the dialog box.

You can then copy the new formatting to another cell or table.

Step 3: Click on Percentage under Category, and then specify how many decimal places you want. Then, click on OK.

You can also click on Custom under Category and then type or select a specific, custom format. For example, 0.00% is the text version of the percentage format with two decimal places.

This method will also convert all fractions into percentages with two decimal places, which you can then convert to hours and minutes later on.

In this tutorial, we learned two different methods of converting a fraction number into a percentage. This way, you can easily display your numbers as a percent.

Hexadecimal numbers are in base 16 and are often used by computer programmers. Sometimes, you may need to convert hexadecimal numbers to binary in Excel. You can easily do so using the HEX2BIN function.

In this tutorial, we will learn how to perform Hexadecimal to binary conversion using the HEX2BIN function.

What are Hexadecimal Numbers?

We usually deal with decimal numbers that are in base 10. They start from 0 and end with 9:

0, 1, 2, 3, 4, 5, 6, 7, 8, 9

A hexadecimal number is in base 16. They can also be referred to as hex or hex numbers. These numbers start from 0 and end with 15:

0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15

Since writing numbers with two digits (10, 11) can cause confusion with other numbers (112 à is it “1 and 12”, “11 and 2”, or “1 and 1 and 2”?), the hexadecimal numbers starting from 10 are represented with English capital letters: 10=A, 11=B, 12=C, 13=D, 14=E, and 15=F.

Therefore, the hexadecimal numbers are as follows:

0, 1, 2, 3, 4, 5, 6, 7, 8, 9, A, B, C, D, E, F

Examples of hexadecimal numbers: 12A, AB45C, FFF, 908D

What are Binary Numbers?

Binary numbers are in base 2, therefore there are only two binary numbers: 0 and 1. Binary numbers are also often used by programmers and computer engineers, but there are many applications for them also in cryptography, digital encoding, or mathematics fields.

Examples of binary numbers: 100011101, 111, 1001, 10000

Convert from Hexadecimal to Binary Using HEX2BIN

Now that we know what hexadecimal and binary numbers are, we will learn how to convert the former to the latter using the HEX2BIN function in Excel.

The HEX2BIN function accepts two arguments: a hexadecimal number, and an optional argument called places that determines how many binary digits to produce.

=HEX2BIN(hexadecimal_number, places)

You can use this function for cells that contain hexadecimal values:

=HEX2BIN(A2)

If you want to apply the function to other cells, you can click on the small rectangle on the lower right of the cell with the HEX2BIN function, and drag in the desired direction:

This way, you can easily convert all of your hexadecimal numbers into binary.

You can also use the HEX2BIN function to convert a value that’s not present in any cell. But you first need to enclose the hexadecimal number with quotation marks:

=HEX2BIN("8A")

The second argument of the HEX2BIN function (places) that we omitted, is used for padding the binary outcome with zeros, such that all binary numbers you obtain have the same length.

Let’s say we want all of our binary numbers to have 9 digits. Then, we provide the second argument as 9:

=HEX2BIN(A3, 9)

This way, we standardized the binary numbers such that each of them has the same length.

In this tutorial, we learned what hexadecimal and binary numbers are, and how to convert a hex number into binary with a very useful function in Excel: HEX2BIN. Nice work!

Other numbers (decimals included) that you can convert in Excel include the following:

The clipboard is an excellent tool that stores the things you copy. At any time you want, you can go to the Excel clipboard and paste one of the saved items. But sometimes you may need to clear the clipboard in Excel. If that’s the case, you’re at the right place.

In this tutorial, we will learn why we may want to clear the clipboard, how to access the clipboard, and how to clear all or some items in the clipboard in Excel.

Why Clear the Clipboard in Excel?

The clipboard in Excel holds and stores copied information. So why should you clear it?

Here are some reasons:

• You may want to speed up the Excel program and only store relevant and important data.
• You may pass your computer onto someone else, and you don’t want them to access your copied items.
• You may worry about data safety, and protect confidential or private data.

If for these or any other reasons you want to clear the clipboard in Excel, first, you need to learn how to access it.

Access the Clipboard in Excel

1) Using the Clipboard launcher icon

If you want to access the clipboard, first go to the Home tab in Excel. On the left side of the Home tab, click on the diagonal arrow on the right of the Clipboard group name.

2) Using the Keyboard Shortcut

If you want to use the Ctrl+C keyboard shortcut to automatically open the clipboard in Excel, you first need to enable this option from inside the clipboard.

Step 1: Open the clipboard with the previous method, using the Clipboard launcher icon.

Step 2: Click on Options inside the Clipboard panel.

Step 3: Check the option that says “Show Office Clipboard When Ctrl+C Pressed Twice”.

Now, any time you press Ctrl+C twice on your keyboard, the clipboard will automatically open.

Clear All Items in the Clipboard

Now that we learned how to access the clipboard and check out its contents in Excel, we can learn how to clear the clipboard. There are three ways of clearing all the items in the clipboard.

This is also one of the methods you can use when fixing problems with the clipboard in Excel.

1) Clear All Items through the Clipboard panel in Excel

Step 1: Open the clipboard panel the way you learned in the previous section: using the clipboard launcher icon or the Ctrl+C keyboard shortcut.

Step 2: Click on Clear All to clear all items from the clipboard.

This will clear all copied items and freshen up your clipboard.

2) Clear All Items through the Clipboard Icon in Taskbar

If the “Show Office Clipboard Icon on Taskbar” option is checked in the Options of your clipboard, then the clipboard icon will appear whenever Excel is open and you start copying data.

If it doesn’t automatically appear, you can enable this option. Simply go into the clipboard panel within Excel, click on Options, and check this option.

If the option is enabled, you can clear the clipboard through here.

Step 1: Right-click the Office clipboard icon in the taskbar.

Step 2: Click on Clear All.

This method will clear all the items, and it doesn’t even require opening or finding the Clipboard panel in Excel. Simple, right?

3) Clear All Items by Closing Excel

If you want to clear all items from the clipboard, you can also save and close the Excel application. This is a very straightforward way of clearing the clipboard.

Important Note: If you have multiple Excel programs or any other Office programs open, the clipboard may not be cleared when you close one Excel window. You may need to close all open Office programs to clear the clipboard.

Clear Some Items in the Clipboard

If you don’t want to delete all the items in your clipboard, but only some items, you can partially clear the clipboard too.

Step 1: Open the clipboard panel using the clipboard launcher icon, or using the Ctrl+C keyboard shortcut as described above.

Step 2: Hover over the item you want to remove and click on the arrow that appears to the right of the item. Click on Delete to remove the item.

Step 3: Repeat Step 2 for all items you want to remove.

This way, you only get rid of the items you choose, instead of clearing the whole clipboard in Excel.

In this tutorial, we learned ways of accessing and clearing the clipboard and removing all or some items. Nice job! Now you can easily control the copied items you want to save or remove.