Excel is a great way to store, compute, and sort information; however, there may be times when you want to save the tables in Excel as images for use on the web, brand print material, or for training.
Table of Contents
Use the Copy Feature in Excel to Save an Excel Table as an Image
Highlight the table that you want to save as an image.
Click the down arrow next to the Copy button in the Clipboard group and click the “Copy as Picture…” selection.
Open up your favorite image editor. You can use Paint™, Paint 3D™, Photoshop™, GIMP™, or Photos™ on Mac™. For this tutorial, we will use Paint™.
Click the Paste option in Paint, and the image appears on the canvas.
Resize the canvas by moving the small white rectangles on the canvas with the mouse so that they match the size of the picture.
Edit the image as needed and save the file.
Use the Print Screen Button to Save an Excel Table as an Image
Open the worksheet you wish to save as an image and press the Print Screen (PrtScr) button on your keyboard.
Open a new canvas in your image editing software, and press CTRL + V or CMD + Vif you use a Mac.
The screenshot will appear on the canvas and will need editing to isolate the Excel table from the complete image.
Note: if your keyboard does not have a Print Screen button, Windows users can use the Fn + Windows Logo Key + Space Bar combination to take a screenshot. Mac users can take a screenshot with CMD + Shift + 3 together.
Use Shortcut Keys to Save an Excel Table as an Image
Once your worksheet is open in Excel, press CTRL + C or CMD + V for a Mac to store the picture in the computer’s memory.
Open your image software and press CTRL + V or CMD + V on a Mac to paste the picture on the blank canvas.
Edit the picture to fit your needs.
Conclusion
Now you have discovered the different methods for saving an Excel table as an image. Explore the many other topics on our blog to continue increasing your knowledge of Excel.
Have you ever imported data to Excel from an external source with many unwanted hyperlinks that came along?
Also, you might have come across multiple undesired hyperlinks automatically generated by Excel for emails and URLs inserted into a workbook.
Unwanted hyperlinks can cause undue trouble. Every time you select a cell containing a hyperlink, excel would automatically redirect you to the destination link.
Hyperlinks that are not needed can be removed from Excel with sheer ease.
Continue reading to know how you can instantly remove hyperlinks in Excel.
Remove Hyperlink in Excel
To better learn the easy two-step process of removing hyperlinks from selected cells in Excel, let’s stipulate an example below.
Remove hyperlinks from selected cells
The data set below collates different links containing information relevant to various topics. As soon as the links are input, Excel recognizes them as hyperlinks and formats them into blue color and underlines them.
To remove the hyperlinks from only some selected cells, here are the steps that you need to follow.
Step 1:
Select the cells containing the hyperlinks that you want to be removed.
Step 2:
Right-click by keeping the cursor on the selected cells to see the pop-up menu as below.
From the pop-up menu that opens up, select the option, ‘Remove Hyperlinks’.
Excel removes hyperlinks from the selected cells as evident below.
That is it. Removing hyperlinks from Excel is only that easy.
However, what if you have an Excel file that is densely packed with hyperlinks? Is it not hectic to select each cell to remove the hyperlink therefrom? It definitely is. Here is a shortcut that may help you with it.
Remove all hyperlinks together
Continuing with the same example stipulated above, to remove hyperlinks from the entire spreadsheet at the same time, follow the steps below.
Step 1:
Press ‘Ctrl+A’. This is the shortcut to select all the contents of an active sheet.
Step 2:
Right-click against the selected cells to have the pop-up menu opened. Opt for ‘Remove Hyperlinks’ as follows.
Excel removes hyperlinks from the entire spreadsheet as follows.
Pro Tip: Prevention of Automatic Navigation to the Destination Link
As soon as you select a cell that contains a hyperlink, you will be navigated to the destination link. Or in case of an email address, Excel would present you with the option to send an email to the subject address.
If Excel redirects you to the destination link automatically upon selecting a cell, you may turn it off as follows.
File > Options > Advanced > Editing options
Check the option for 'Use CTRL + Click to follow hyperlink'. Once this is done, Excel would present you with the option to press ‘Ctrl’ to follow a link. Merely selecting a cell in Excel wouldn’t navigate you to the destination page.
It is often the case that you’ve to work with Excel files containing excessive Hyperlinks. For example, the Human Resource department of a Company may have to maintain a record for all of its employees, including their names, numbers, and email addresses.
Whenever fed into Excel, email addresses are recognized by Excel as hyperlinks. If you don’t want the file to contain hyperlinks, such settings of Excel might prove problematic. This is because every time you feed the details of a new employee into the Excel sheet, Excel would automatically turn the email address into a hyperlink.
Removing each hyperlink time and time again is time-consuming and frustrating. To prevent Excel from generating hyperlinks automatically, you can disable the same from the settings. Here’s how you can do it.
Step 1:
Go to File > Options as follows.
Step 2:
Selecting options would open up a window of Excel options as follows.
From the said window, choose Proofing > AutoCorrect Optionsas highlighted above. This would open up a pop-up window.
Choose ‘AutoFormat as you type’ and uncheck the box for ‘Internet and network paths with hyperlinks’ as highlighted below.
This will turn off the automatic hyperlinking within Excel. Now, as you type or Copy / Paste links in Excel, those would remain as text, and Excel would not automatically turn them into Hyperlinks.
Pro tip: This setting will be applied to the entire Excel application. Any workbook that you create or edit with Excel will have the same settings applied.
However, after these settings are applied, you can still add hyperlinks to one or more cells by right-clicking on the desired cell. From the pop-up menu that then opens up, select ‘Link’. This would open up a dialogue box where you can insert the destination link to be added as hyperlink to that cell.
Conclusion:
Hyperlinks make an intelligent feature of Excel. However, undesired hyperlinks are often a nuisance. Hope this article helped you learn all that you needed to know how to remove hyperlinks in Excel.
If you’ve ever worked in an HR role, you know how challenging it can be to maintain a large volume of personal details for multiple employees, including their CNIC numbers, IBANs, Passport numbers, or even cellphone numbers.
All of these numbers are often accompanied by dashes that may appear in the center, at the beginning, or the end of the number.
Manually removing the dashes is definitely not an option as this could take hours or even days if dealing with a substantial amount of data. Additionally, there is a high chance of data loss or errors due to accidental backspacing and clicks.
Table of Contents
3 Methods to Remove Dashes in Excel
1. Find and Replace Function
The very well-known ‘Find and Replace’ function can help you remove dashes from Excel only in a few clicks. Take a look below to see how.
The data above represents the IBANs for a list of employees. As evident, these numbers are paired with multiple dashes that must be removed.
To do so, simply select the Column containing the dashes (i.e. Column B) and go to the Find and Replace function as follows.
Home > Editing > Find & Select > Replace
Pro Tip: Press Ctrl + H to instantly open up the Find and Replace dialogue box in Excel.
Against ‘Find’, enter dashes (-), whereas, leave the box against ‘Replace’ vacant as we want the dashes to be removed.
Next on the list is the Substitute Function that works on the pattern of the ‘Find and Replace’ Function. Syntax of the Substitute Function reads as follows.
Instance: Optional. The instance where changes are made. All instances are replaced if omitted.
You may access the Substitute function from the Functions Library as follows.
Home > Editing > Find & Select > Replace
In the example given above, to use the substitute function to remove dashes from Employee IBANs in Excel, compose it as follows.
=SUBSTITUTE (B2, “-“, “”)
Where B2 defines the cell containing dashes. The Old text is set as “-“, and the new text is left blank “” as we want the dashes to be replaced with nothing but removed.
Note: Both the strings for the Old and New text must be enclosed in double quotation marks.
Hit Enter to see the results and drag the fill-handle to yield similar results against the whole list of IBANs.
The third and last option on this list involves Kutools. Putting in formulas to remove dashes may prove a little hectic and technical for new Excel users. Kutools offer an in-built tool for character removal from Excel.
Continuing the same example as above, to remove dashes between IBANs from Excel, follow these simple steps.
Step 1:
Select the Column containing the dashes i.e. Column B and then,
Go toKutools > Text > Remove Characters
This opens up the ‘Remove characters’ window, as shown below.
Step 2:
Under the option to ‘Remove characters’, choose Custom and select dash (-) from the dropdown menu.
Take a look at the data post-dashes removal through the Preview Pane on the right and Hit ‘Okay’ if everything looks good.
Excel filters out dashes from the selected range, as shown below.
Note: Kutools is an add-in to Excel. If you do not see the tabs ‘Kutools’ or ‘Kutools Plus’ on your Excel Ribbon, you probably do not have it installed in your Excel. But no worries, you can easily download it from the web and have it added to your Excel.
Bottom Line
Try either of the above methods to remove unwanted dashes from your dataset in Excel. Or try installing Kutools to ease the job through an in-built character removal function.
Excel has many useful functions and tools that enable us to perform text manipulation. That’s why, when you have first and last names in separate columns in a spreadsheet, it’s extremely easy to combine them into a full name, whether you want it separated with a space or a comma.
In this tutorial, we will learn how to combine first and last names in Excel using the CONCATENATE function, the ampersand (&) operator, and Flash Fill.
Table of Contents
Using the CONCATENATE Function to Combine First and Last Name
The CONCATENATE function enables us to combine multiple cells or values. We can use this function to combine first and last names and separate them with a space or a comma. Let’s learn how…
Write the following formula in a new cell to combine first and last names with a space:
=CONCATENATE(A2, " ", B2)
The first cell name (A2) refers to the first name, and then we concatenate the space character (“ “), and the last name (B2).
You can drag the small green rectangle in the bottom right of the cell with the formula, and drag it downward to apply the concatenate operation to other cells.
If you want to combine the names with a comma with the format “LastName, FirstName”, use the following formula:
=CONCATENATE(B2, ", ", A2)
The CONCATENATE function can combine multiple text values or cells. Therefore, it can also be used to combine a first name, middle name, and last name:
=CONCATENATE(A2, " ", B2, " ", C2)
So, the trick is: to write all the cell values and separators inside the CONCATENATE formula and separate them with commas. When you learn this simple formula, you can combine anything!
Using the Ampersand (&) Operator Function to Combine First and Last Name
Text combination can also be achieved with the ampersand (&) operator. Write the following formula to combine first and last names with a space:
=A2&" "&B2
If you want to combine the names with a comma, type the following formula in a new cell:
=B2&", "&A2
The ampersand operator (&) works as follows: you need to start the formula with the equal (=) sign, and then write the cell names and separators, and type ampersand (&) between each of them. This is another elegant way of combining first and last names.
Using Flash Fill to Combine First and Last Name
Without using operators and formulas, it is perfectly possible to combine first and last names: with the help of Flash Fill. Flash Fill is a tool that learns and applies patterns. Follow the steps below to combine using Flash Fill.
Step 1: Next to the first and last names, write the full name in a new cell.
Step 2: Go to the cell below by pressing Enter, or clicking on the cell.
Step 3: In the Home tab, click on the Fill icon in the Excel ribbon, and then click on Flash Fill.
Flash Fill will automatically learn the pattern, and combine the first and last name for all cells.
You can apply the same steps if there are middle names in your data. As long as you type the full name exactly as in the original cells that contain the first, middle, and last names, the combination pattern will be learned and applied by Flash Fill.
In this tutorial, we learned how to combine first and last names in Excel using the CONCATENATE function, the ampersand (&) operator, and Flash Fill. You can choose whichever method suits you best.
Data exported from a system are usually in a fixed report format. Sometimes though, it even includes unnecessary rows in between the data.
In my example below, notice that there’s a total row added between each record.
These rows may be helpful in certain instances. But if you intend to create charts from these data or maybe add formulas in your file to do some calculations, these rows can be bothersome.
You can manually delete these rows, but if you’re working on large datasets, that would be too much work.
In this tutorial, I’ll show you ways to mass delete every other (or every nth) row in Excel.
Before we start, please ensure that there is no other data next to the dataset. Otherwise, they may get deleted in the process. You may want to create a backup of the file first or copy the dataset onto an empty sheet.
Delete every other row in Excel by filtering odd or even rows
This method is perfect if you want to delete odd or even rows in your dataset.
1. On a new column next to the dataset, type the following formula:
To get the odd rows:
=ISODD(ROW())
To get the even rows:
=ISEVEN(ROW())
These formulas will return TRUE if the current row is odd or even (depending on the function you have used).
We want to mark the rows we will delete with TRUE, so be sure to add the right formula.
2. Drag the Fill Handler until you reach the last row in your dataset. Doing so will copy the formula to the remaining rows.
3. Once you have added all formulas, check if all the rows you intend to delete are marked as TRUE.
If you see that the “row markers” are correct, it’s time to filter the data.
4. Select all the cells within your dataset (including the header).
You can do so by selecting one cell in the dataset and pressing CTRL + A to select all.
5. Go to the Data tab and click on the Filter button under the Sort & Filter section.
You should see the filter buttons added on the header row.
6. Click the filter button on top of the formula we have previously added, uncheck the FALSE option, and click OK.
This will filter the records to only display rows marked as TRUE.
7. Highlight all the cells in your dataset that are marked as TRUE.
8. From the Home tab, click the Find & Select button and click Go To Special.
9. In the Go To Special menu, select ‘Visible cells only’ and click OK.
10. Notice that the rows in your dataset are now individually selected.
11. Right-click on one of these rows and select Delete Row.
12. A prompt will appear to confirm the process. Click OK to continue.
Remember that this action deletes the entire row, which means any data next to the dataset gets deleted.
13. And that’s it! You have successfully removed all the selected rows.
14. Let’s now remove the filters to view the dataset again.
From the Data tab, click on the Filter button.
15. You now have successfully deleted the odd or even rows from your dataset.
Remove the formulas you have added as “row markers” and you’re all set.
Delete every nth row in Excel by filtering the nth row
If you intend to delete every third, fourth, fifth, etc., row in your dataset, then this method is for you.
We are going to follow the same steps listed in the previous method, except that we will use a different formula to mark the rows we are going to delete.
On a new column next to the dataset, type the following formula:
=MOD(ROW()-[header row],[nth row])
[header row] refers to the row where your header row is. If it’s in row 1, then type 1. Set this to 0 if your dataset doesn’t have a header.
[nth row] refers to the nth row in the dataset you would like to delete. So, if you’d like to delete every 3rd row in your dataset, set this to 3.
Examples:
=MOD(ROW()-1, 3)
This formula gets every third row in the dataset if the header row is in row 1.
=MOD(ROW()-2, 3)
This formula gets every third row in the dataset if the header row is in row 2.
In case you don’t know, MOD() is a function in Excel that returns the remainder of the number divided by a number.
With the formula above, we are looking at marking the rows that we intend to delete with 0 (which means that the row is divisible to the nth row we have set).
2. Copy the formula to the remaining cells in the worksheet.
See if all the rows you intend to delete are marked with zero. If not, you may need to adjust the formula.
3. Select a cell within the dataset and press CTRL + A. Doing so will select all items in the dataset.
4. Go to the Data tab and click on the Filter button under the Sort & Filter section.
The filter buttons will appear on the header row of your dataset.
5. Click the filter button on top of the formula we have just added.
Adjust the filters so that only zero (0) appears in the dataset.
6. Once the filters are applied, highlight all visible rows in the dataset (those that are marked with 0). Remember not to include the header in the selection.
7. Go to the Home tab, click the Find & Select button, and click Go To Special.
8. In the Go To Special menu, select ‘Visible cells only’ and click OK.
9. The rows in your dataset should now be individually selected.
10. Right-click on one of these rows and select Delete Row.
11. A prompt will appear warning you that this action will delete the entire row. Click OK to continue.
12. And that’s it! The rows that you have selected are now deleted.
13. To view the dataset again, remove the filters by going to the Data tab and clicking on the Filter button.
14. You now have a “clean” dataset. Remove the formulas you’ve previously added, and you’re all set.
If you regularly need to delete every other row in Excel, it may be best to have a macro do this for you.
1. While your Excel file is open, press ALT + F11. This should open the VBA Editor.
2. Go to the Insert menu and click on Module.
3. On the new module added, paste the following code:
Option Explicit
Sub DeleteEveryOtherRow()
Dim rng As Range, nthRow As Long, ctr As Long, lastrow As Long
On Error Resume Next
ThisWorkbook.ActiveSheet.ShowAllData
On Error GoTo 0
On Error Resume Next
Set rng = Application.InputBox(prompt:="Select your entire dataset (excluding the headers):", Title:="Range Selection", Type:=8)
If Err.Number > 0 Then Exit Sub
On Error GoTo 0
nthRow = Application.InputBox(prompt:="Every which row should be deleted? (e.g., 1 for first, 2 for second)", Title:="Row to be Deleted", Type:=1)
If nthRow <= 0 Then Exit Sub
With ThisWorkbook.ActiveSheet
lastrow = rng.Rows.Count
nthRow = nthRow * -1
For ctr = lastrow To 1 Step nthRow
rng.Rows(ctr).Delete
Next
End With
End Sub
Now, it’s time to test the code. But before we do that, please create a backup of your dataset. Remember that changes done by a macro cannot be undone.
To run the code:
1. Click on any line on the code and press F5.
2. A prompt will appear asking you to select your entire dataset.
3. Go back to your workbook and select from your worksheet the range that contains your dataset.
IMPORTANT: The headers should not be included in your selection.
4. Once you have selected the entire dataset, click OK.
5. Another prompt will appear asking you to enter ‘every which row you would like to be deleted’.
In my example above, I typed ‘3’ because I wanted to delete every third row in my dataset.
6. After typing your row number, click OK.
7. And that’s it! Every nth row in your dataset should now be deleted.
To reuse this code next time, remember to save the workbook in .xlsm or .xlsb format.
Also, for easier use, you may want to add a new button in your worksheet and link the button to the code that we have added. This way, you don’t need to open the code each time you want to run it.
Conclusion
As you can see, deleting every other row (or every nth row) is quite simple. You only need to add the correct formula for Excel to determine which row to delete. After that, you only need to filter the records based on these “row markers”, delete the visible rows, and you’re all set!
