How to Switch First and Last Name in Excel

Data manipulation is one of the most powerful abilities of Excel. Flash Fill is a tool in Excel that enables you to manipulate data in a cell according to your needs.

In this tutorial, we will learn how to switch first and last names in Excel with the help of Flash Fill.

Switch First and Last Name

Now we will walk through the steps of switching the first and last names in Excel, and we will use Flash Fill for this purpose.

Flash Fill works as follows: you define a new pattern, and Flash Fill imitates this pattern for all other cells. In this case, the pattern that we will create is going to be “LastName, FirstName”.

Step 1: Go to a cell that contains a full name. In the cell right next to it, write the pattern you want in a reverse fashion: “LastName, FirstName”.

Write the new pattern with swapped full name

Be careful that the first and last names are written correctly, just like the original cell.

Step 2: Press Enter on your keyboard to go to the cell below, or click on the cell below the new pattern.

Go to the cell below

Step 3: In the Home tab, click on the Fill icon in the Editing part in the Excel ribbon, and then click on Flash Fill.

Click on Fill and then Flash Fill

This will automatically fill out all cells next to a full name, and your names will successfully be switched:

Full names swapped

Using this method, you can use any pattern that includes a first name and a last name. For example, you can also abbreviate a first name:

First name abbreviated with Flash Fill

As long as you provide a new name format, Flash Fill will automatically learn the pattern and apply it to other cells. It’s like magic, right?

This extremely easy method can enable you to perform all kinds of text manipulation. Learning to use Flash Fill is an excellent skill. Nice job!

You may want to check how to separate or combine first and last names in Excel and then sort by last name in Excel.

How to Select Only Filtered Cells in Excel?

Copying filtered data onto a new sheet is a great way to segregate related data rows.

It allows us to have the extracted rows available anytime without re-applying the same filters.

We have to be careful, though — the hidden rows within the dataset might get included in the cells copied if we miss the crucial step of selecting only the visible cells.

In this tutorial, I’ll show you ways to select only the visible cells within a filtered dataset so that you can easily copy and paste them onto a different sheet.

1. Using a keyboard shortcut

1. Select all the cells within your dataset.

You can do this by first selecting one of the cells within the filtered records and pressing CTRL + A to select all.

Steps to select all cells in a filtered data set

Your entire filtered dataset should be highlighted (as shown in the image above).

2. Next, press the keyboard shortcut that selects only the visible cells.

Note that the keyboard shortcut varies for Mac and Windows. Please use the table below for reference.

Computer SystemKeyboard ShortcutNotes
WindowsALT + ;ALT key and semicolon (;).
MacCMD + SHIFT + ZCommand and shift keys and letter Z.

After pressing the appropriate keyboard shortcut, notice that the hidden rows are excluded from the selection. You will see a white demarcation line between rows that have filtered-out rows that follow them.

How the filtered cells look like after selecting only the visible cells

4. You can now copy the selected cells and paste them somewhere else.

Press CTRL + C to copy.

Copy visible cells on a filtered dataset

Notice that the broken lines appear in between rows that have hidden cells after them. These lines let you know that only the visible cells are selected and copied.

5. Open the worksheet (or workbook) where you would like to paste the filtered cells.

Select a cell and press CTRL + V to paste.

Filtered cells copied onto a new worksheet or workbook

And that’s it! You have your filtered cells copied onto a new worksheet or workbook.

2. Using the Go To Special menu

If you are not much of a fan of keyboard shortcuts and prefer doing tasks with your mouse, you can also select visible cells using the Go To Special menu.

1. Highlight all the cells within your filtered dataset. (Select one cell within the dataset and press CTRL + A to select all).

Highlight all cells within the filtered dataset

2. From the Home tab, go to Find & Select and click on Go To Special.

Steps to access the "Go To Special" menu

3. The Go To Special menu should appear.

From the list of options, select Visible cells only, then click OK.

Select the "Visible cells only" option from the Go To Special menu

4. That’s it! You now have only the filtered cells selected.

How the filtered cells look like after selecting only the visible cells

5. You can now proceed with copying it to a new worksheet or workbook.

3. Using the QAT command

If you regularly copy filtred cells, there is an even quicker method for you to select visible cells. All you need to do is add a Quick Access Toolbar (QAT) command that you can readily click whenever you need to select the visible cells.

To do this:

1. Click on the Customize Quick Access Toolbar button right above the Home tab.

This button has an inverted triangle with a thin line on top (as shown in the image below).

How to access the "Customize Quick Access Toolbar" button

2. From the list of options that appear, select More Commands.

Select "More Commands..." from the list of options under the "Customize Quick Access Toolbar"

3. The Excel Options menu will appear with the Quick Access Toolbar tab opened.

Select All Commands from the “Choose commands from” dropdown menu.

Select "All Commands" from the "Choose commands from" dropdown menu in the Quick Access Toolbar

4. Wait a bit until the list of commands under the dropdown menu gets updated and looks something like this:

How the "Customize Quick Access Toolbar" menu looks like after selecting "All Commands" in the list of commands to choose from

5. Scroll down until you find the Select Visible Cells from the list of commands.

Once you find it, select it, and click on the Add >> button.

From the list of commands, look for "Select Visible Cells". Once you find it, click on the Add>> button.

6. You should now see the Select Visible Cells command added to the list box on the right.

This list box contains all the commands added to your QAT.

How the Customize Quick Access Toolbar look like after adding the "Select Visible Cells" command

After adding the command, click OK.

7. That’s it! You now have the Select Visible Cells in your top left toolbar.

The "Select Visible Cells" button added in the Quick Access Toolbar (QAT)

Click on this button whenever you want to only select the visible cells in your worksheet.

To use it in your filtered dataset:

  1. Select all the cells in your dataset.
  2. Click on this button. Notice that Excel immediately selects the visible cells.
  3. You can now press CTRL + C to copy the visible cells and paste them onto a new sheet.

Conclusion

Excel’s data filter is a great way to sift through your records and only capture the set of data that meets the criteria that you have set. But please remember that when you copy a filtered data set, you must ensure that only the visible cells are selected. You can choose from any of the methods above to do this.

How to Make a Scatter Plot in Excel (2024)

Do you have columns of quantitative data in your Excel sheet that only consist of a list of figures? How can you visualize and analyze the relationship between this quantitative data at a glance? Through a scatter plot.

Scatter plots are the best when it comes to comparison between two sets of quantitative data. The article that follows will help you learn all about scatter plots in Excel.

What is a Scatter Plot?

A scatter plot is a two-dimensional graph that pictures the relationship between two variables.

It has two axes; the horizontal axis is known as the X-axis, whereas the vertical axis is known as the Y-axis. For the reason of having these two axes, it is also known as the XY graph.

One of the variables (mostly the independent variable) is plotted against the X-axis and the other variable (mostly the dependent variable) is plotted against the Y-axis.

After the data points are plotted, the relationship between both the variables is depicted through the intersection points of the x and y-axis.

Before we move forward, here’s a sneak peek into what a scatter plot looks like.

A scatter plot in Excel

When to Use a Scatter Plot in Excel?

Scatter plots are designed to visualize the relationship between two variables. It is best used to show how strongly two variables are related or correlated to each other.

Visually, the closer the data points lie to each other on a scatter plot, the stronger the relationship between them.

A scatter plot will be of great use when two variables are to be compared and the relationship between them is to be studied.

For a quick example, if you want to study the relationship between the price and demand of a product, you may plot a scatter plot with data points for both variables.

This will let you visualize how the demand for a product (the dependent variable) fluctuates in the market as the price of the product (the independent variable) is increased or decreased.

A scatter plot created to visualize the relationship between the price and demand of a product

The above image shows the relationship between five data points for both the variables, price, and demand. As the price increases from $100 to $150, the demand for the product falls from 70 units to 60 units. This shows that there exists a negative correlation between both variables.

Scatter plots can be used for many other similar comparisons where quick visual analysis of the data is sought.

How to Create a Scatter Plot in Excel?

Surprising to know, but scatter plots are one of the easiest graphs of Excel in terms of creation. Creating a scatter plot in Excel is all about two steps, both of which are explained hereunder through an example.

Data for promotional expense and related sales of a Company

The above data represents the promotional expense of a Company along with the resultant sales won by the Company over 5 months. How can this be plotted in a scatter diagram?

Step 1:

Scatter plot creation in Excel begins with organized source data. It is pertinent to note that scatter plots are designed for two-dimensional data sets, only.

As the first step, select the two columns of your quantitative data set i.e. the column for promotional expense and sales.

Selection of the two columns from the quantitative data sets

Step 2:

With both the columns selected, follow the path below to insert a scatter plot.

Insert Tab > Charts > Scatter Plot > Scatter with Smooth Lines and Markers

This inserts a scatter plot to your Excel, as shown below.

Alt-text: Excel converts the source data into a scatter plot

In the image above, Excel has converted the underlying dataset for promotional expense and sales into a scatter plot. The plot shows a right-upward trend line that demonstrates a direct relationship between both variables.

As the independent variable on X-axis (the promotional expense) continues to increase, the sales must also increase.

Must know!

You can replace the scatter plot type ‘Scatter with Smooth Lines and Markers’ selected above with any other type of scatter chart by selecting a different type of scatter chart. Excel offers 5 different types of scatter charts that are discussed in detail below!

Pro-tip:

It is advisable to plot the independent variable on the X-axis and the dependent variable on the Y-axis for a better analysis of the relationship between them both.

For excel to plot so, the independent variable should be placed as the first column in the data set, and the dependent variable should come next. The example above demonstrates a similar positioning of the numerical columns in the source data.

Types of Scatter Plot

In addition to the smooth line scatter plot in the example above, Excel has 4 other types of scatters plot to offer.

Even with different scatter plot types, the purpose for each type of scatter plot remains the same i.e. to visualize the relationship between two variables. However, you may choose different scatter plot types for different presentation styles.

The 5 types of scatter plots offered by Excel are listed below.

        i.          Scatter

The first and the most basic type of scatter plot is a simple scatter plot with markers and no lines. This plot only adds markers (dots) to each intersection point of both variables.

Scatter’ type of scatter plot in Excel

      ii.            Scatter with smooth lines and markers

This type of scatter chart adds a smooth line (also a slightly curved line) that traces the markers placed at each intersection point of both the variables.

Alt-text: ‘Scatter with smooth lines and markers’ type of scatter plot in Excel

    iii.            Scatter with smooth lines

Similar to ‘Scatter with smooth lines and markers’, this scatter plot type removes the markers from the plot, and only a smooth line draws the relationship between both the variables.

Scatter with smooth lines’ type of scatter plot in Excel

    iv.            Scatter with straight lines and markers

This type of scatter plot is very similar to the scatter plot with smooth lines and markers, with the only exception that the relationship line in this plot is not smooth or curved but straight and sharp.

Scatter with straight lines and markers’ type of scatter plot in Excel

      v.            Scatter with straight lines

Under this type of scatter plot type, the straight line of the relationship between both the variables is not accompanied by markers at the intersection points of both the variables.

Scatter with straight lines’ type of scatter plot in Excel

Pro Tip:

When deciding about the type of scatter plot that best suits your data set, do not waste time inserting and deleting each different scatter plot type.

Instead, select the data and launch the ‘See all charts’ option by clicking on the small arrow to the right of the Chats section on the Insert tab.

Launching the ‘See all charts’ in Excel

This would open up the chart menu bar. From the chart types in the left pane, select XY (scatter).

Consequently, from all 5 different scatter chart types that appear on the top, click on different types to preview how the chart would look like when plotted.

Previewing different scatter plot types under one window

Adding a Trendline

After we have learned to construct a scatter plot in Excel, it’s time we learn how a trendline can be added to a scatter plot.

A trendline is added to scatter plots to enhance the visualization and analysis of the relationship between the two variables. It connects a series of data points and is also known as the ‘Line of best fist’.

Here’s how you can add it to a scatter plot in Excel.

A simple scatter plot in Excel

To add a trendline to the scatter plot above, right-click on any of the markers (the blue little crosses) on the plot. This should open the drop-down menu as shown below. Select the option ‘Add trendline’ from the dropdown menu.

Selecting ‘Add a trendline’ from the drop-down menu

This adds a dotted blue trendline to your scatter plot.

Excel adds a trendline to the scatter plot.

This also opens up the ‘Format Trendline’ window on the right, from where you can choose different formatting options for the trend line.

Under these options, you can customize how the line looks through the color, thickness, and transparency of the line.

You may also add a 3D effect to it through shadows, glow effects, and much more.

Trendline formatting options

Visualization Options

Excel offers 5 different visualization options for scatter plots. These range from Classic Scatter plots to scatter plots with smooth to straight lines with or without markers.

In addition to these, there are further different presentation styles for each chart type.

For example, you may remove the gridlines from your chart, if desired. From the ‘Chart Elements’ button, uncheck the option ‘Gridlines’ to have a blank background for your chart.

Gridlines removed from the scatter plot

Similarly, click anywhere on the chart to launch the ‘Design’ tab in the Ribbon and go to:

Design Tab > Chart Layouts > Quick Layouts

Excel offers 11 different layout options for scatter plots. Here’s how Layout 10 looks when applied to the scatter plot created in the example above.

Layout 10 from Quick Layouts applied to the scatter plot

Customization Options

It’s not over after you have plotted a scatter diagram in Excel. In fact, Excel offers a number of options to users for customizing the scatter plot once created. Some of these customization options are discussed below.

1.      Chart Labels

A chart without labels? Even sounds boring.

Once you have constructed a scatter plot, adding chart labels to it is only a matter of a few keystrokes.

  1. Select the scatter plot by clicking anywhere on it to launch the three icons on the right of the plot.

The Chart Elements Option

  1. Click on the Chart Elements button as shown above. From the list of Chart elements that open up, check the box for ‘Chart Title’. This would bring a small text box to the top of your chart. Click on it to edit it and add the chart title as desired.
  2. Clicking on the text box for ‘Chart Title’ launches the ‘Format Chart Title’ pane on the right of the worksheet, where you can edit the Chart Title in several ways.
The ‘Format Chart Title’ Pane

In addition to Chart Title, you may also want to add labels to both the axes.

  1. To do so, go to the ‘Chart Elements’ button and from the list of ‘Chart elements’ that opens up, check the box for ‘Axes Titles’. This would bring two small text boxes parallel to each of the axes. Click on each box to edit it and add the axis title as desired.
Axes Titles added to the scatter plot

  1. To launch the ‘Format Axes Titles’ pane, you may launch ‘More options’ by clicking on the small arrow that appears to the right of ‘Axes Titles’ in the Chart Elements drop-down menu.
The ‘Format Axes Titles’ Pane

2.      Data Labels

With a few data points, it is often feasible to label the data points for a more appealing presentation of the relationship between both variables.

Here is how you can add data labels to your scatter plot.

  1. Select the scatter plot by clicking anywhere on it. Upon being selected, click on the Chart Elements button (the plus icon) as shown below.
The Chart Elements Option

  1. From the list of ‘Chart elements’ that opens up, check the box for ‘Data Labels’ and click on the small arrow that appears to the right for more options.
  2. This would take you to the Format Data Labels pane, wherefrom you can select the labels along with other formatting positions.
The Format ‘Data Labels’ Pane

  1. For instance, we want each intersection point in the scatter plot of our example above to be labeled by the month name. We have selected the option ‘value from cells’ and with the cell range containing the month names (A2:A6 in our example) defined.
Data Labels added to the scatter plot

3.      Chart Design

Who says charts are only supposed to be like old monochrome pictures? In Excel, you can customize your scatter plot to different designs and colors.

Simply select the chart and go to the ‘Design’ tab on the Ribbon.

Design’ tab on the Ribbon.

This offers a wide variety of chart designing options to Excel users, where you change the overall chart style, chart colors, chart layout, and much more.

For instance, here’s what the scatter plot looks like after we’ve applied the dark chart style to it.

Chart style changed from simple to dark

Bottom Line:

Creating a simple scatter plot in Excel is super easy and practicing the above-explained steps, and pro tips can help you master the creation of scatter plots in Excel in a relatively shorter time. Keep practicing!

How to Convert Month and Year to Date in Excel?

When working with exported data, there will be times when we will have only the month and year included in the dataset (the day is nonexistent).

It was okay until you realized that, for very particular reasons, you must turn these two bits of information into an actual date.

In this tutorial, I will teach you how you could convert your month and year to an actual date. Also, in the latter part of this article, I will teach you how to do it in reverse – from date to month and year.

Steps to convert month and year to date in Excel

Since I have no idea how your month and year are currently formatted, we have to do some data prep to ensure that we are working on the same data format.

Our goal is to have our month and year look something like this:

Target output of data prep

* The month is in numerical format (1 to 12).

* The month and year are on separate cells.

If your data already looks something like the image above, you can skip the following data prep sections.

IMPORTANT:

Before doing the data prep, please copy your Year and Month column to a new sheet and perform the data prep steps there. We want to ensure that your dataset is safe from accidental alterations.

Data Prep: Split Month and Year and place them on separate cells using the “Text to Columns” option

We have to split our Month and Year and place them in separate cells.

If they are joined together by a space, a dash (-), a slash (/), or any other symbol (similar to the image below), the fastest way to split them is by using the “Text to Columns” option.

Sample Year and Month that are merged with a space or symbol in between

To do this:

1. Highlight all the cells containing the year and month. Do not include the header.

Highlight the cells in the Year and Month column

2. From the Data menu, click on Text to Columns.

Steps to access the "Text to Columns" option

3. The Convert Text to Columns Wizard will appear. From the list of options, select Delimited. Then, click Next >.

Select "Delimited" from the list of options in Convert Text to Columns Wizard

4. Now, select the appropriate delimiter for your data.

In my example, a space separates the month and year (e.g., Jan 2012).

Since this is the case, I have unchecked all other delimiters and only ticked the Space checkbox.

Select "Space" as the delimiter

You know you have selected the correct delimiter once you see your month and year on separate columns in the Data preview.

If your delimiter doesn’t exist in the list of available options, tick the Other checkbox and type the symbol that separates your month and year.

Add slash (/) as delimiter

Once you’re happy with the result, click Next >.

5. You should now reach the final step in the Wizard.

Last step in the Convert Text to Columns Wizard

We are not going to change anything on this step. Just click the Finish button.

That’s it! You should now see your month and year in separate columns.

Sample output when year and month are separated by the Text to Columns option

Data Prep: Split Month and Year and place them on separate cells using the LEFT() and RIGHT() Excel formulas

If your month and year are on a single cell but don’t have a space or any symbol in between them, we are to split them using Excel formulas.

Sample Year and Month merged without any space or symbol in between

Below are some of the sample formulas that you can use. Note that the following formulas assume that your month and year are on cell A2.

SAMPLE YEAR AND MONTHFORMULA TO EXTRACT MONTHFORMULA TO EXTRACT YEARNOTES
201201=RIGHT(A2,2)=LEFT(A2,4)The year and the month have a fixed number of digits (4 and 2, respectively).  
20121=RIGHT(A2, LEN(A2)-4)=LEFT(A2,4)The year has a fixed number of digits (4), while the month has either 1 or 2.  

If your month comes before your year, you only need to swap the LEFT() and RIGHT() functions and adjust the character length accordingly.

Once you’ve added the appropriate formulas, copy them and paste them as values.

To do this, highlight all cells containing formulas and press CTRL + C. Once the cells are enclosed with broken lines, press CTRL + ALT + V. From the Paste Special menu, select Values, and click OK.

Copy and paste the resulting month and year as values

Data Prep: Convert the Month in Text Format (e.g., February or Feb) to Numerical Format (e.g., 2)

Now that we have the month and year in separate columns, we move on to the last step for Data Prep.

You may skip this step if your month is already in numerical format (1 to 12).

But if it is in text format (e.g., February, Feb), please follow these steps:

1. Insert a new sheet.

2. Copy your Month column and paste it into column A of the new sheet.

Copy Month column and paste it into column A of the new sheet

3. On cell B2, add the following formula:

=IF(LEFT(A2,3)="JAN", 1, IF(LEFT(A2,3)="FEB", 2, IF(LEFT(A2,3)="MAR", 3, IF(LEFT(A2,3)="APR", 4, IF(LEFT(A2,3)="MAY", 5, IF(LEFT(A2,3)="JUN", 6, IF(LEFT(A2,3)="JUL", 7, IF(LEFT(A2,3)="AUG", 8, IF(LEFT(A2,3)="SEP", 9, IF(LEFT(A2,3)="OCT", 10, IF(LEFT(A2,3)="NOV", 11, IF(LEFT(A2,3)="DEC", 12))))))))))))

This formula will get the numerical value of the month.

Add the formula for getting the numerical value of the month

4. Copy cell B2 and paste it onto the remaining rows.

5. You should now see the corresponding numerical values of your months.

6. Once you’re happy with the result, highlight all the cells with formulas in column B and copy them (press CTRL + C).

Copy all cells with formula

7. Go back to your original sheet. Click on the first cell in your Month column and press CTRL + ALT + V. The Paste Special menu will appear. Select Values and click OK.

Steps to paste as values

8. That’s it! Your month should now be in numerical format.

Month in numerical format

Using the DATE() Formula

Once our Month and Year columns resemble the image below, we have completed the data prep.

Target output of the data prep

We will now proceed to the main course – the actual conversion of month and year to date.

1. Add another column beside YEAR. We can name it DAY.

In this column, enter the number you intend to set as the day for the dates. It could be any number from 1 to 31.

Enter the number you intend to set as Day for the dates

In my example above, I’ve added the first and last day of the month as my days.

2. Next, add another column after DAY. We can name it DATE.

This is where we’ll add the DATE() formulas to generate the dates based on the month, day, and year specified. 

3. In cell D2, add this formula:

=DATE(B2, A2, C2)

Don’t worry if you have sorted your fields in a different order. You can change the formula accordingly.

You only have to remember that the DATE() function gets the following parameters (in this order): year, month, and day.

4. Copy cell D2 to the remaining cells in the DATE column.

5. That’s it! You should now have your actual dates.

Sample output after applying the DATE() formula

Remember to choose the number for your DAY wisely if you want to have the dates correctly reflect the Month and Year.

It may not be ideal to choose 31 as the day of the dates, considering that not all months have 31 days.

In my example above, notice that in cell D8, the result was 7/1/2010 even if the month, day, and year are 6, 31, and 2010 respectively. It should have resulted in 6/31/2010, but since this is not a valid date, Excel automatically gets the next closest day — 7/1/2010.

6. Once you’re happy with the outcome, copy the DATE column and paste it as values in your original dataset.

Steps to convert date to month and year in Excel

To reverse the process and convert the date to month and year, you can do either of the following options.

If you want to have month and year in separate columns, use the following formulas:

(Note that the sample formulas below assume that your date is in cell A2). 

MONTHYEAR
=MONTH(A2)
Results in the numerical value of the month.  
=YEAR(A2)
Results in the 4-digit value of the year.
=TEXT(A2, “mmm”)
Results in the first three letters of the month (e.g., Jan, Feb).  
=TEXT(A2, “yyyy”)
Results in the 4-digit value of the year.
=TEXT(A2, “mmmm”)
Results in the complete name of the month (e.g., January, February).  
=TEXT(A2, “yy”)
Results in the last two digits of the year (e.g., 22 for 2022)

If you want to have the month and year merge and share the same column:

MONTH AND YEARRESULT
=YEAR(A2) & “-“ & MONTH(A2)
Combine the Year and Month with a dash (-).  
2022-01
=MONTH(A2) & “/” & YEAR(A2)
Combine the Month and Year with a slash(/).  
01/2022
=TEXT(A2, “yyyymm”)
Combine the Year and Month without a space or symbol in between.  
202201

Conclusion

Converting dates can be tricky if you’re not adept with the options available in Excel. But I hope the suggestions above will help you easily convert dates without much hassle.

How to Unsort in Excel: Revert to Original Data

Data sorting (or the process of arranging data based on a field or a set of fields) allows us to analyze data more effectively.

If you want to temporarily sort your data in a particular order but want to be able to revert to the original data arrangement anytime, you’re in the right place.

In this tutorial, I’ll show you ways to unsort or revert to the original data order in Excel.

Revert to the Original Data by Undoing the Data Sort

If you have just applied data sorting to your data by dates (and have not closed the file yet), you can go back to the original data arrangement by simply undoing the steps.

To do so, just press CTRL + Z (or Command + Z for Mac).

You may need to do it a number of times until you see your data sorted back to its original state.

IMPORTANT:

  • If you have performed other tasks in the file after data sorting, these actions will have to be undone too.
  • Please note that by default, Excel only allows up to 16 actions that can be undone. If you have done more than 16 actions post-data sorting, then this method may not work.
  • You may need to find a way to acquire a copy of the data in its original arrangement or manually re-arrange the data. Once you do so, you can refer to the succeeding methods to revert to the original order anytime.

PRO TIP:

If you want to view again the data sort that you have previously applied, you can press CTRL + Y to redo the steps.

Revert to the Original Data using a “Sort Order” Column

This method will work if you have not sorted your data yet – meaning it’s still in its original state.

1. Before applying any data sort, insert a new column next to your data.

We’ll name this helper column ‘Sort Order’.

If your data is on a table, the new column will automatically be added as part of the table (as shown below).

Add the 'Sort Order' column in your dataset.

If not, you must reselect the entire data (including the new column) before sorting.

2. Next, we’ll add sequential numbers to this column – starting with 1.

By now, you may notice that we are adding this column to record the current order of the data rows.

Type ‘1’ in the first row and drag the fill handler down (up until the last row).

Drag the Fill Handler down (up to the last row).

Notice that the column is now filled with 1’s.

Click on the Auto Fill Option that appears and select Fill Series.

Click on the Auto Fill Option that appears and select Fill Series.

The sequential numbers should now appear in the column.

Sequential numbers appear after selecting Fill Series in the Auto Fill Option.

Now, you’re all set!

3. You can now continue sorting the data however you like – you can sort them alphabetically, sort them by last name, or in ascending or descending order based on the numbers.

4. Once done, you can revert to the original data order by simply sorting the data using the Sort Order column we have added. That’s it!

You can hide this column (or delete it) when you no longer need it.

Revert to the Original Data using a Data Backup

If you don’t like adding a new column to your dataset, you can do the most basic technique to easily revert to the original data – create a copy of the worksheet or the file prior to data sorting.

To create a copy of the worksheet:

1. Right-click on the sheet and select Move or Copy.

Right-click on the sheet and select 'Move or Copy'.

2. The Move or Copy menu will appear.

Steps to create a copy of the selected worksheet.

Select where you intend to insert the copied sheet (see Before sheet).

Tick the Create a copy textbox and click OK.

3. And that’s it! You have successfully created a copy of your dataset.

You can now proceed with applying your desired data sort.

IMPORTANT: Remember to apply the data sort on the duplicate copy (not the original one).

To create a copy of the file:

1. Close the workbook.

2. Right-click on the file and select Copy.

Right-click on file and select Copy.

3. Paste it on the same folder (or on a different folder, if you like).

Right-click on a blank space inside the folder and select Paste.

Right-click on a blank space inside the folder and select Paste.

You can rename the file if you want.

And that’s it! You can now proceed with applying your desired data sort.

Just remember to do it on the duplicate file, not on the original one.

Conclusion

Sorting data has many uses – it helps us understand, for example, which group performed better based on the numbers. We can also use it to quickly find records after sorting them alphabetically using custom autofill.

Whatever the purpose, there will be instances when we also need to go back to the original arrangement of the data. I hope the suggested methods above help you do so with ease.