Author: Zeynep

Let’s say you have a spreadsheet with lots of names, and you want to make an analysis of the data.

When the first and last names are together in a cell, it makes it extremely difficult to analyze, sort, and find important information.

To eliminate this difficulty, we will learn in this tutorial to separate first and last names in Excel and also will learn how to separate a name that’s separated with a space, or a comma.

Separate First and Last Name

Splitting a full name into first and last names is extremely easy in Excel with the help of the Text to Columns feature. Let’s learn how…

Step 1: Choose the cells that contain a full name.

Step 2: In the Excel ribbon, go to the Data tab, and then click on Text to Columns.

The Text to Columns feature splits a single cell into columns when you choose a delimiter (separator) such as a space, tab, or a comma.

Step 3: In the Convert Text to Columns Wizard, choose the Delimited data type, and click on Next.

Step 4: Under the delimiters list, choose Space if the first and last names in your data are separated with a space. Then, click on Next.

When you choose space, the Convert Text to Columns Wizard will automatically give you a preview of the separated data under Data preview. This way, you can make sure that the data will be separated correctly.

Step 5: In the last step, you can select the data format of the newly generated columns. In the case of names, the General data format is perfectly suitable.

In addition, you can select the destination of the separated first and last names.

If you don’t specify a new destination, the separation will be made in place (will override your data). Instead, click on the upward arrow under Destination, and choose the destination cell.

Step 6: After completing all steps, click on Finish.

Perfect! Now, all the full names in your spreadsheet will be split into separate columns as first name and last name:

If your full names are separated with a comma, you only need to change the delimiter you selected in.

Go to Step 4. Let’s assume the format of your full name is: “LastName, FirstName”.

To separate full names with a comma, instead of Step 4, choose the Comma separator under the Delimiters list, and then click on Next:

The rest of the steps are identical. This way, your data can more easily be analyzed:

In this tutorial, we learned how to separate first and last names with a space or a comma in Excel with the assistance of the Text to Columns feature.

Also, you may want to check how to combine or switch first and last names in Excel and then sort by last name in Excel.

Related Tutorial: How to remove the middle initial from the full name

Opening sheets for different purposes within an Excel document is an excellent feature. However, sometimes you may accidentally open multiple unnecessary sheets, which can cause redundancy, especially if you don’t know how to delete multiple sheets in Excel at once.

In this tutorial, we will learn how to delete a single sheet and then to delete multiple sheets simultaneously. This way, you can clean your document from unnecessary sheets with a single click!

Delete a Single Sheet

To delete a single sheet, right-click on the name of the sheet you want to delete, and then click on Delete.

This will successfully delete a single sheet:

Delete Multiple Sheets

If you have more than one sheet you want to delete, you first need to select all sheets you want to delete.

• If the multiple sheets you want to delete are consecutive, first click on the name of the first (leftmost) sheet. Then, while pressing Shift on your keyboard, click on the last (rightmost) sheet you want to delete. This will select all sheets in between.

• If the multiple sheets you want to delete aren’t consecutive, click on the sheets individually while pressing Ctrl on your keyboard.

After selecting the sheets you want to delete, right-click on a sheet name within your selection, and click on Delete.

This way, multiple sheets are deleted in an instant!

In this tutorial, we learned how to delete a single sheet and multiple sheets at the same time.

Related Tutorials: 

Delete a Comment in Excel

Sum Across Multiple Sheets in Excel

Sorting is an extremely useful function of Excel. Excel sort enables many options and variations so that you can sort your data with any specification.

In this tutorial, we will learn how to sort by date in Excel: we will sort a single column or the entire data by date, and also learn how to perform custom sort.

Sort a Single Column by Date

If your dates are only in a single column, sorting by date is very straightforward. Let’s start…

Step 1: Select the dates in your column.

You can also select the entire column by clicking on the column name. Just make sure to remove any duplicates from that column.

Step 2: Click on Sort & Filter, which is on the top right of the Excel ribbon.

Step 3: If you want to sort lowest to highest, click on Sort A to Z.

This will sort the dates in an ascending fashion (lowest to highest):

If you want to sort from highest to lowest, click on Sort Z to A.

This will sort the dates in a descending fashion (highest to lowest):

This method works perfectly if the data you want to sort is independent of other cells or columns. Otherwise, you need to learn how to sort more than one column or the entire data.

The next section covers this case.

Also Learn: How to Sort by Last Name in Excel

Sort Entire Data by Date

Let’s say your data is spread over multiple columns, and you want to sort by date. If you sort by only selecting the date cells, the rest of your data will not be sorted, and irrelevant cells will stay in irrelevant rows. That’s why the sort selection needs to be extended to preserve data integrity.

Follow the steps below to sort your entire data by date.

Step 1: Select the column that contains the dates.

Step 2: Click on Sort & Filter, and then Sort A to Z or Sort Z to A.

Since there’s data in other columns, a pop-up will appear:

Step 3: While the “Expand the selection” option is selected, click on Sort.

Now, the entire data is sorted. As can be seen from the image below, the cells in the Name and Occupation columns followed the date cells. This way, you preserved the integrity of the data and successfully sorted it by date.

Sort Using Custom Sort

If the sort operation that you want to perform is more sophisticated than just sorting by date, then you can use Custom Sort. Custom Sort enables sorting on multiple columns and prioritizing the columns. Let’s see an example.

Step 1: Select the data you want to sort.

Step 2: Click on Sort & Filter.

Step 3: Click on Custom Sort…

The custom sort window will appear. In this window, you can choose:

• Column: which column to sort on (Date, Name, or Occupation)
• Sort On: which property to sort on (Cell Values, Cell or Font Color, Conditional Formatting Icon)
• Order: A to Z (ascending), Z to A (descending), or Custom List

If you check the “My data has headers” option, the header row (first row of each column) will not be included in the sort (freeze the header row if you want).

If you want to sort on multiple columns, you can click on “Add Level”:

Whenever you add a new level, you can choose the options mentioned above (Column, Sort On, and Order). In this example, we will first sort the Occupation column from lowest to highest.

Then, we will sort on the Date column from lowest to highest. The result is as follows:

Custom sort has first sorted the data with respect to occupation, then within each occupation, sorted with respect to the date. This is a more sophisticated version of sorting by date and shows the power of Excel.

Conclusion

In this tutorial, we learned how to sort data by date in Excel. We learned about different scenarios of data and sort options. Now you have the capability of sorting at an expert level!

If you did something wrong in the process, you can always revert to the original data and start from there.

But before you get started, learn everything that you can about dates in Excel from these articles:

Excel is very well known and very widely used for data filtering. It facilitates many aspects of data cleaning and filtering that can help you save big on your time.

A common way of data filtering in Excel is eliminating duplicate data. And at times, removing duplicate data is not that simple. This is particularly the case when you want to delete rows that contain duplicate data in the same column.

But no worries! This article should enlighten you on three common and easy ways of filtering out duplicates from your data based on one column. Continue reading to learn them all.

1. The ‘Remove Duplicate’ Function

This is the easiest way to sweep your worksheets clear of duplicate values based on one column. Take a look at the data in the example below.

The above data represents the details of different awards won by employees in different areas.

Now, to filter out a distinct list of employees who won an award in any department, we need to filter duplicates from the column containing employee names i.e., Column A.

To do so, follow the steps given below.

Step 1:

Select the data to be filtered out along with the headers.

Step 2:

Go to Data Tab > Remove Duplicates as shown below.

This opens the ‘Remove Duplicates’ Dialogues box as follows.

Step 3:

From the ‘Remove Duplicates’ Dialogues box, check the option ‘My data has headers’. This is because we selected the data including the headers.

Step 4:

Unselect all the column headers appearing under the ‘Remove Duplicates’ Dialogues box. Only select the header for the column where the values to be filtered appear.

Step 5:

Click ‘Ok’. Excel will remove the duplicate rows from the selected column.

The results are as follows.

Must note how Column A now only consists of a single instance for each employee name.

All duplicate or triplicate instances have been deleted from Column A and their corresponding entries from Columns B and C have also been deleted.

Pro Tip:
Unlike in this example, if you want to remove duplicates from more than one column, you can still use this method. The only difference to be made is of checking the boxes for other column heads.

2. Conditional Formatting

If you have a small dataset and you just don’t want to spend quite some time applying complex functions to your data, you can choose to go manual all the way.

An easy way to filter out duplicates based on one column is to conditionally format the duplicate values, filter them out, and delete them. Check out the example below.

Step 1:

Considering the same example as above. To identify the duplicates in Column A, select the said column and take the following route.

Home Tab > Styles > Conditional Formatting > Highlight Cells Rules > Duplicate Values

Step 2:

Set up conditional formatting options in the window that then opens up and click ‘Ok’. This would highlight the duplicate rows from Column A as follows.

Step 3:

Filter out the highlighted values by applying data filters to your data as follows.

Select the header of Column A > Go to Data Tab > Sort & filter > Filter

Step 4:

This would add a drop-down menu icon to the header of Column A. Click on it and select ‘Filter by Color’.

This would filter out highlighted values from Column A as shown below.

From these rows, select duplicate rows and delete them to keep back only one row for each value.

Now remove filters and conditional formatting from your data to have the final look of your data without any duplicate values.

3. Running a VBA Code

Both the above methods may seem easily accessible and convenient. However, if you have regular data-filtering to do, these methods may prove to be hectic and time-consuming. To counter the said problem, you may want to run a VBA Code.

You can set up a VBA code and add it to your Quick Access Toolbar to be able to readily access it only with a single click.

But you’re not a coder? No need to worry. You only need to copy-paste the following code, and you are done.

Note: The above code is specifically built for the instance above, whereby in the code we have specified that the data contains headers and that data filtration is to be done based on Column 1 i.e. Column A. If you want to filter your data based on any other column, say column C, you may alter the second last line of the above code as Columns:=Array (3).

To run the code in your Excel, here are some easy steps to follow.

Step 1:

Go to the Developer tab from your Ribbon.

Pro Tip: If you find it nowhere on the ribbon, there’s nothing to fret about. 

Go to File > Options > Customize Ribbon > Check Developer’s Tab as shown below

Step 2:

From the developer’s tab, go to Visual Basic, and a VBA Window will launch as follows.

From the above VBA window, go to Insert > New Module. In the new module that opens up, write the code specified above and close the VBA window.

Step 3:

This creates a macro that is now ready to use. To access it, take the following route.

Developer tab > Macros

From the list of Macros that then opens, select the ‘Delete_Duplicate_Rows’ macro and click Run. Excel would run the code, and the duplicates would be removed, as shown below.

Pro Tip: Deleting duplicate rows by running a VBA code has no turn backs or undo options. Be very careful before you run this code and preferably keep a backup of your data to save yourself of any undue data loss.

Conclusion

Big Data is a common thing in the revolutionizing tech world. With more and more data coming in, we need more sophisticated and better techniques to sort and analyze data.

Filtering data of any duplicate values is one of those most-wanted techniques, and doing it in Excel can save you much time. The article talks about three ways how you can engineer your data in Excel – practice them all and learn them better.

Have you ever seen ‘$’ in an excel sheet and wondered why is it so frequently used? To simplify things, ‘$’ means don’t change.

Sometimes, the $ in an excel sheet is just a dollar sign prefixing a currency value. Other times, it is used to lock a cell reference, often referred to as absolute referencing.

The article below covers all details on how and why a dollar sign is used in Excel.

What Means Dollar Sign in Excel?

A dollar sign in Excel comes in handy to prefix numbers (mostly currency) or to lock a cell for all changes of references.

It can be used as a simple currency symbol or for indicating absolute or mixed cell reference.

What that does is fix the value in a cell. With absolute reference, you lock the address of a cell.

However, with mixed cell reference, you have the option of locking any one of the two, the row or the column reference as per the needs.

A dollar sign is usually only used for indicating references in Excel. To understand what that means, let’s first learn about a cell reference.

What is a Cell Reference?

The address of a cell is referred to as cell reference. It tells Excel the location of the value that is to be put into the formula.

A cell reference is one of the most basic steps of learning Excel. First thing’s first – once you master the difference between references, you can master the rest of Excel in no time.

The formulas used can refer to multiple cells at one time and that makes Excel more powerful as the change of a single row or column can change the entire calculation.

Cell reference comes into use when a formula refers to a cell.

Here’s a quick example.

Whenever you select a cell in Excel, the ‘Name Box’ siding left to the formula bar displays the reference to that cell. In the above example, this turns out to be A2.

Note: The cell reference ‘A2’ is a combination of Column A and Row 2.

Alternatively, select any cell and add the operator ‘=’ to activate the cell for the formula. Click on any cell, and Excel would add the reference for that cell to the formula bar. Take a look below.

Let’s see the types of references in Excel and which one is related to the dollar sign as below.

Types of References

Excel offers three different types of cell references using which you can copy a formula to other cells. These are:

• Relative references: The one that does not use the dollar sign at all.
• Absolute references: The one that uses the dollar sign twice in a formula.
• Mixed references: The one that uses the dollar sign only once.

Relative References

A relative reference is a cell address in Excel that changes with respect to the row and column number.

When you apply a relative reference to a cell and change its position, the reference changes according to the new position of rows and columns and adjusts to the new cell.

In excel, the relative reference is used by default.

Moreover, unlike other references, the dollar sign is not followed by the cell address in relative reference. In this way, the addresses can easily change, and each time you change the cell, you get a new result based on the given data.

Hence, a relative reference is a convenient method in Excel to deal with data when you want the references to be automatically adjusted for position changes.

It lets you perform the same calculation for corresponding cells across the whole worksheet. Let’s see an example below to visualize a better picture of how relative references work.

As seen in the above example, the formula ‘= A1 + 5’ when applied to cell B1, adds up the value of cell A1 and 5. If you now copy the formula to cell B2, the cell reference will change as in the following image.

The formula in cell B2 changes to ‘= A2 + 5’ unlike cell B1 as per the relative reference. Similarly, if we copy the formula to cell C1, the formula changes as ‘= B1 + 5’.

This illustrates how the relative reference changes for each cell when the same formula is copied at different places.

Also, for column B, simply try using the drag and drop function and see how Excel updates the cell references for every single cell in a snap.

Absolute References 

An absolute reference is the cell address that uses two ‘$’ signs in one formula. One lets you lock the row and the other, the column.

When this reference is used, the cell reference remains the same no matter which cell of the worksheet it is copied to.

To use absolute reference for a cell, it needs to be applied manually to the cell in question.

Unlike relative reference, both the row and column names are preceded by dollar signs.

This is mostly used when you have large sets of data, and you must apply the same formula to all cells in a worksheet. So even if you change the position of the formulas, you will still have the absolute cell references unchanged.

Let’s see an example below to understand the concept better.

As visible, the absolute reference formula applied to B1 remains the same and will give the same result no matter which cell you copy it to.

The formula in cell C3 remains the same as in B1 because of the absolute reference.

There are only a handful of instances where you use absolute references independently in a worksheet. This is because most of the time, absolute and relative references are both used together.

Pro Tip: If you do want to change the absolute reference, add a row or column. Although absolute reference locks the cells and does not move from the origin, on the addition of a new row or column, it leaves the previous location and adjusts with the new row or column.

In the above image, the formula in cell B1 is =$A$1*10.

Upon adding a new column, the values shifted to the next column, and the cell reference in cell C1 became =$B$1*10.

Mixed References

A mixed cell reference is super simple and easy to use.

Unlike absolute reference, mixed reference locks only one coordinate, i.e., it uses only one dollar sign – either before the column name as $A1 or before the row number as A$1.

In $A1, the column name is locked and will not change but the row number can be adjusted. Similarly, in A$1, the column name can be adjusted but the row number won’t change as it is fixed.

Let’s see the image below to grasp the concept better.

=$A2*$B2+C$2

As seen in the image above, cell C3 contains mixed cell references. In the first two cell references, the column name is locked and in the third cell reference, the row is locked.

When you put $A2 in the formula, Excel will change the row number orderly, but the column name will remain the same.

Similarly, in $B2, the column remains the same, but the rows change for each cell where the formula is copied.

In C$2, the row will remain the same, but the three instances in three different columns will be used sequentially.

Shortcut to Add Dollar Sign

Adding or removing a dollar sign is easy. You can either do it manually or use a shortcut key to perform the task.

If you want to manually add a dollar sign, simply double-click on the cell to activate it (or press the F2 key). Place the cursor where you want to add the dollar sign and hit the dollar key.

Usually, when the shortcut is used, it adds or removes the dollar sign for references. The shortcut key used is F4. Single-click on the cell which contains the formula and press the F4 key. The key will let you toggle among the following four instances:  

• Upon pressing the F4 key once, a dollar sign is added to the cell reference.

• Upon pressing it the second time, another dollar sign is added.

• Upon pressing it the third time, a dollar sign is removed.

• Upon pressing it the fourth time, another dollar sign is removed.

In this way, you can activate relative, absolute, or mixed references as needed. Or, you can remove dollar sign to turn your cell reference from absolute to mixed to relative reference.

Pro Tip: Make sure to activate the cell, or else, upon pressing F4, Excel will automatically select the cell to the left of the cursor and change its reference type.

Conclusion

A dollar sign has much more uses in Excel than being prefixed to a number for representing currency. You may use it to turn cell references into absolute cell references or mixed references with sheer ease.

Practice a few examples stipulated above to master using the $ sign in Excel in no time. Happy Excel Exploring!